Oracle存储过程及调用
Oracle存储过程语法
Oracle的存储过程语法如下：
createprocedure存储过程名称（随便取） is 在这里可以定义常量、变量、游标、复杂数据类型这里可以定义变量、常量 begin 执行部分 end;
（2）带参数的存储过程语法：
createprocedure存储过程名称（随便取）(变量1数据类型,变量2数据类型,...,变量n数据类型) is 在这里可以定义常量、变量、游标、复杂数据类型这里可以定义变量、常量 begin 执行部分 end;
（3）带输入、输出参数的存储过程语法：
createprocedure存储过程名称（随便取）(变量1in(或out)数据类型,变量2in(或out)数据类型,...,变量nin(或out)数据类型) is 在这里可以定义常量、变量、游标、复杂数据类型这里可以定义变量、常量 begin 执行部分 end;
注意：用上面的语法创建存储过程时可能会碰到数据库中已经有了同名的存储过程，这样Oracle就会弹框报错，说名字已被现有对象使用。解决方法有两种：
方法一：换个存储过程名
方法二：在最开头的createprocedure之间加上orreplace关键字，例如：createorreplaceprocedure存储过程名称。但是这种方法不建议使用，因为这种方法会把之前同名的存储过程替换为你当前写的这个
存储过程案例一：没参数的存储过程
createreplaceprocedureprocedure_1
is
begin
dbms_output.put_line('procedure_1.......');
end;
存储过程案例二：带参数的的存储过程
createprocedureprocedure_2(v_inumber,v_jnumber)
is
v_mnumber(5);
begin
dbms_output.put_line('procedure_2.......');
v_m:=v_i+v_j;
dbms_output.put_line(v_i||'+'||v_j||'='||v_m);
end;
存储过程案例三：带输入、输出参数的存储过程
存储过程的参数分为输入参数和输出参数，
输入参数：输入参数一般会在变量名和数据类型之间加in来表示该参数是输入参数
输出参数：输出参数一般会在变量名和数据类型之间加out来表示该变量是输出参数
不写in和out的话，默认为输入参数
createprocedureprocedure_3(v_iinnumber,v_jinnumber,v_moutnumber)
is
begin
dbms_output.put_line('procedure_3.......');
v_m:=v_i-v_j;
dbms_output.put_line(v_i||'-'||v_j||'='||v_m);
end;
PL/SQL块中调用存储过程
下面以调用上面三个存储过程为例
declare v_param1number(5):=2; v_param2number(5):=8; v_resultnumber(5); begin --调用上面案例一的存储过程 procedure_1(); --调用上面案例二的存储过程 procedure_2(v_param1,v_param2); --调用上面案例三的存储过程 procedure_3(v_param1,v_param2,v_result); dbms_output.put_line(v_result); end; /*执行结果：*/ procedure_1....... procedure_2....... 2+8=10 procedure_3....... 2-8=-6 10
java调用存储过程
案例一：java调用没有返回值的存储过程
要求：编写一个像数据库emp表插入一条编号为6666，姓名为张三，职位为MANAGER的记录
/*存储过程*/
createprocedureprocedure_4(v_empnoemp.empno%type,v_enameemp.ename%type,v_jobemp.job%type)
is
begin
insertintoemp(empno,ename,job)values(v_empno,v_ename,v_job);
end;
//java调用存储过程
publicstaticvoidmain(String[]args){
Connectionconn=null;
CallableStatementcs=null;
ResultSetrs=null;
//java调用存储过程
try{
Class.forName("oracle.jdbc.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl","scott","tiger");
cs=conn.prepareCall("{callprocedure_4(?,?,?)}");
//给输入参数赋值
cs.setInt(1,6666);
cs.setString(2,"张三");
cs.setString(3,"MANAGER");
cs.execute();//执行
}catch(Exceptione){
e.printStackTrace();
}finally{
closeResource(conn,cs,rs);//关闭资源
}
}
//执行后就会向数据库的emp表中插入一条编号为6666，姓名为张三，职位为MANAGER的记录
案例二：java调用返回单列单行的存储过程
要求：编写一个根据员工编号查找员工姓名的存储过程，并用java调用该存储过程
/*存储过程*/
createprocedureprocedure_5(v_empnoinemp.empno%type,v_enameoutemp.ename%type)
is
begin
selectenameintov_enamefromempwhereempno=v_empno;
end;
//java调用存储过程
publicstaticvoidmain(String[]args){
Connectionconn=null;
CallableStatementcs=null;
ResultSetrs=null;
try{
Class.forName("oracle.jdbc.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl","scott","tiger");
cs=conn.prepareCall("{callprocedure_5(?,?)}");
cs.setInt(1,6666);//给输入参数赋值
/*指定输出参数的数据类型
语法：oracle.jdbc.OracleTypes.输出参数的数据类型
此例输出参数的数据类型是varchar，所以是oracle.jdbc.OracleTypes.VARCHAR*/
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
cs.execute();//执行
//获取输出参数的值，位置要和输出参数对应？的位置对应起来，该例输出参数对应第2个问号，而且输出参数的数据类型为字符型，所以是cs.getString(2)
Stringa=cs.getString(2);
System.out.println("员工姓名："+a);
}catch(Exceptione){
e.printStackTrace();
}finally{
closeResource(conn,cs,rs);//关闭资源
}
}
/*执行结果,控制台打印：*/
结果：员工姓名：张三
案例三：java调用返回单行多列的存储过程
要求：编写一个根据员工编号查找员工姓名、职位和工资的存储过程，并用java调用该存储过程
/*存储过程*/
createprocedureprocedure_6(v_empnoinemp.empno%type,v_enameoutemp.ename%type,v_joboutemp.job%type,v_saloutemp.sal%type)
is
begin
selectename,job,salintov_ename,v_job,v_salfromempwhereempno=v_empno;
end;
//java调用存储过程
publicstaticvoidmain(String[]args){
Connectionconn=null;
CallableStatementcs=null;
ResultSetrs=null;
try{
Class.forName("oracle.jdbc.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl","scott","tiger");
cs=conn.prepareCall("{callprocedure_6(?,?,?,?)}");
cs.setInt(1,7788);
//指定输出参数的数据类型，注意：顺序要对应起来
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
cs.registerOutParameter(3,oracle.jdbc.OracleTypes.VARCHAR);
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.DOUBLE);
cs.execute();//执行
//获取返回值
Stringename=cs.getString(2);//获取姓名
Stringjob=cs.getString(3);//获取职位
doublesal=cs.getDouble(4);//获取薪水
System.out.println("员工编号为7788的姓名为："+ename+"职位是："+job+"薪水是："+sal);
}catch(Exceptione){
e.printStackTrace();
}finally{
closeResource(conn,cs,rs);//关闭资源
}
}
/*执行结果，控制台打印：*/
员工编号为7788的姓名为：SCOTT职位是：ANALYST薪水是：3000.0
案例四：java调用返回多行多列（返回列表）的存储过程
要求：编写一个根据部门编号查找部门所有员工信息的存储过程，并用java调用该存储过程
/*定义游标*/
createpackagemy_packageas
typeemp_cursorisrefcursor;
endmy_package;
/*存储过程*/
createprocedureprocedure_7(v_deptnoinemp.deptno%type,emp_cursoroutmy_package.emp_cursor)
is
begin
openemp_cursorforselect*fromempwheredeptno=v_deptno;
end;
//java调用存储过程
publicstaticvoidmain(String[]args){
Connectionconn=null;
CallableStatementcs=null;
ResultSetrs=null;
try{
Class.forName("oracle.jdbc.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl","scott","tiger");
cs=conn.prepareCall("{callprocedure_7(?,?)}");
cs.setInt(1,20);//给输入参数赋值
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);//指定输出参数的数据类型
cs.execute();
rs=(ResultSet)cs.getObject(2);//获取输出参数的值
while(rs.next()){
//顺序为数据库中字段前后顺序，例如数据库emp表中第5列为hiredate，数据类型为Date，所以获取第5列值时就应该用rs.getDate(5)
System.out.println(rs.getInt(1)+""+rs.getString(2)+""+rs.getDate(5));
}
}catch(Exceptione){
e.printStackTrace();
}finally{
closeResource(conn,cs,rs);//关闭资源
}
}
/*以下就是20号部门所有员工的信息，这里为方便我们只打印了编号、姓名和入职时间
运行结果，控制台打印：*/
7369SMITH1980-12-17 7566JONES1981-04-02 7788SCOTT1987-04-19 7876ADAMS1987-05-23 7902FORD1981-12-03
这是上面java调用存储过程代码中关闭资源方法的代码
publicstaticvoidcloseResource(Connectionconn,CallableStatementcs,ResultSetrs){
if(rs!=null){
try{
rs.close();
}catch(SQLExceptione){
e.printStackTrace();
}
}
if(cs!=null){
try{
cs.close();
}catch(SQLExceptione){
e.printStackTrace();
}
}
if(conn!=null){
try{
conn.close();
}catch(SQLExceptione){
e.printStackTrace();
}
}
}
最后给个应用，分页的存储过程
分页存储过程：
/*定义游标*/
createpackagepage_packageas
typepage_cursorisrefcursor;
endpage_package;
/*存储过程*/
createprocedurepro_paging(
v_page_sizeinnumber,--每页显示多少条
v_page_countoutnumber,--总页数
v_current_pageinnumber,--当前页
v_total_countoutnumber,--记录总条数
emp_cursoroutpage_package.page_cursor--返回查询结果集的游标
)
is
v_beginnumber(5):=v_page_size*(v_current_page-1)+1;--查询起始位置
v_endnumber(5):=v_page_size*v_current_page;--查询结束位置
v_sqlvarchar2(1000):='selectempno,enamefrom
(selecta.empno,a.ename,rownumrnfrom
(selectempno,enamefromemp)a
whererownum<='||v_end||')b
whereb.rn>='||v_begin;
/*不能像下面这么写，不然调用该存储过程时会报类型不一致的错，因为最里面查的只有empno,ename，因此外面也要和里面保持一致
v_sqlvarchar2(1000):=\'select*from
(selecta.*,rownumrnfrom
(selectempno,enamefromemp)a
whererownum<=\'||v_end||\')b
whereb.rn>='||v_begin;*/
v_enamevarchar2(10);
v_empnonumber(4);
begin
openemp_cursorforv_sql;
loop
fetchemp_cursorintov_empno,v_ename;
exitwhenemp_cursor%notfound;
dbms_output.put_line(v_empno||''||v_ename);
endloop;
v_sql:='selectcount(empno)fromemp';
executeimmediatev_sqlintov_total_count;
if(mod(v_total_count,v_page_size)=0)then
v_page_count:=v_total_count/v_page_size;
else
v_page_count:=trunc(v_total_count/v_page_size)+1;
endif;
dbms_output.put_line('共'||v_total_count||'条记录');
dbms_output.put_line('共'||v_page_count||'页');
dbms_output.put_line('当前页：'||v_current_page);
dbms_output.put_line('每页显示'||v_page_size||'条');
end;
Java调用的话和上面java调用存储过程的例子一样。这里为了方便，就直接在pl/sql中调用了
/*调用分页存储过程*/
declare
v_page_countnumber(5);
v_cursorpage_package.page_cursor;
v_total_countnumber(5);
begin
dbms_output.put_line('第一页数据。。。。。。。。。');
pro_paging(5,--每页显示5条
v_page_count,--总页数
1,--当前页
v_total_count,--记录总条数
v_cursor--游标
);
dbms_output.put_line('--------------------------');
dbms_output.put_line('第二页数据。。。。。。。。。');
--显示第二页数据
pro_paging(5,--每页显示5条
v_page_count,--总页数
2,--当前页
v_total_count,--记录总条数
v_cursor--游标
);
end;
/*运行结果：*/
第一页数据。。。。。。。。。
6666张三
20empSu2
19empSave2
7369SMITH
7499ALLEN
共17条记录
共4页
当前页：1
每页显示5条
--------------------------
第二页数据。。。。。。。。。
7521WARD
7566JONES
7654MARTIN
7698BLAKE
7782CLARK
共17条记录
共4页
当前页：2
每页显示5条
以上所述是小编给大家介绍的Oracle存储过程及调用，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！