oracle 包，函数，过程，块的创建和执行及在java中执行(转)
SQL> create or replace procedure sp_guocheng1 is--如果有这个名字就替换
2 begin--执行部分
3 insert into guocheng values('liyifeng','liyifeng');
4 end;
5 / --执行的意思
Procedure created
显示错误
SQL> show error;
Errors for PROCEDURE LIYIFENG.SP_GUOCHENG1:
LINE/COL ERROR
-------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------
3/22 PL/SQL: ORA-00928: 缺失 SELECT 关键字
3/1 PL/SQL: SQL Statement ignored
6/0 PLS-00103: 出现符号 "end-of-file"在需要下列之一时： ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while
with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock insert open rollback savepoint set sql
execute commit forall merge pipe purge
执行过程
SQL> exec sp_guocheng1;
PL/SQL procedure successfully completed
SQL> commit;
一个新的快
SQL> set serveroutput on;--打开输出选项
SQL>
SQL> begin
2 dbms_output.put_line('hello,world');--dbms_output是包.put_line是过程
3 end;
4 /
hello,world
PL/SQL procedure successfully completed
带有变量的查询
SQL> declare
2 v_ename varchar2(25);
3 v_id number;
4 begin
5 select name,id into v_ename,v_id from stu where name=&aa;
6 dbms_output.put_line('用户名是：'||v_ename||v_id );
7 end;
8 /
用户名是：liyifeng 0
PL/SQL procedure successfully completed
带有类外的查询
SQL> declare
2 v_ename varchar2(25);
3 v_id number;
4 begin
5 select name,id into v_ename,v_id from stu where name=&aa;
6 dbms_output.put_line('用户名是：'||v_ename||v_id );
7 exception
8 when no_data_found then
9 dbms_output.put_line('您输入的数据不存在！');
10 end;
11 /
您输入的数据不存在！
PL/SQL procedure successfully completed
--传入参数的过程 注意，定义参数的时候不能带上长度。
create procedure sp_gc1(v_id number,v_name varchar2) is
begin
update stu set name=v_name where id=v_id;
end;
过程在java中运行。。注意jdbc_oracle架包。。
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class testExec {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection ct=null;
try{
//加载驱动
// Class.forName("com.hxtt.sql.access.AccessDriver");//连接access的
Class.forName("oracle.jdbc.driver.OracleDriver");
//创建连接
ct=DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.9:1521:orcl","liyifeng","liyifeng");
//创建 callablestatement
CallableStatement cs=ct.prepareCall("{call sp_gc1(?,?)}");
//给？复制
cs.setInt(1, 0);
cs.setString(2, "mountLee");
//设置不自动提交事务
ct.setAutoCommit(false);
cs.execute();
//提交事务
ct.commit();
//关闭资源
cs.close();
ct.close();
}
catch (Exception e){
try {
//如果失败，就回滚
ct.rollback();
}
catch(Exception ex){ex.printStackTrace();}
e.printStackTrace();
}
}
}
--传入参数并有返回值的函数
create or replace function sp_guoc(v_id number)
return varchar2 is v_name varchar2(20);
begin
select name into v_name from stu where id=v_id;
return v_name;
end;
java中调用函数
Statement sm=ct.createStatement();
ResultSet rs=sm.executeQuery("select sp_guoc(0) from stu");
if(rs.next()){
System.out.print(rs.getString(1));}
--创建包
create or replace package sp_pack is
procedure sp_pd (v_id number,v_name varchar2);
function sp_ft (v_id number) return number;
end;
--创建包体
create or replace package body sp_pack is
procedure sp_pd(v_id number,v_name varchar2) is
begin
update stu set name=v_name where id=v_id;
end;
function sp_ft(v_id number)
return number is v_name number;
begin
select id into v_name from stu where id=v_id;
return v_name;
end;
end;
执行包中的过程
SQL> exec sp_pack.sp_pd(0,'lee');
PL/SQL procedure successfully completed
SQL> commit;
Commit complete
java中执行包中函数的方法。
Statement sm=ct.createStatement();
ResultSet rs=sm.executeQuery("select sp_pack.sp_ft(0) from stu");
if(rs.next()){
System.out.print(rs.getString(1));}
http://blog.163.com/mount_lee/blog/static/20202509520122292827986/
