oracle存储过程procedure实例、调用 sql/plus调用存储过程 java调用存储过程

0.1 删除存储过程
drop procedure test_xg_pt;

0.2 sql/plus中调用存储过程，显示结果
set serveoutput on  --打开输出
var info1 number;  --输出1
var info2 number;  --输出2
declare
var1 varchar2(20); --输入1
var2 varchar2(20);--输入2
begin
pro(var1,var2,:info1,:info2);
end;
/
print info1;
print info2;

0.3 java调用
                        //查询列表
   proc = con.prepareCall("{call scott.testc(?)}");
   proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
   proc.execute();
   System.out.println("创建CallableStatement成功!");

   rs = (ResultSet) proc.getObject(1);
   System.out.println("操作数据表成功!");

   while (rs.next()) {
    System.out.print(rs.getLong("DEPTNO") + "    ");
    System.out.print(rs.getString("DNAME") + "    ");
    System.out.print(rs.getString("LOC") + "    ");
    System.out.println();

   }
   
   //插入
   proc = con.prepareCall("{call scott.testa(?,?,?)}");
   proc.setInt(1, 13);
   proc.setString(2, "com");
   proc.setString(3, "www");
   proc.execute();

 

1、最简单的存储过程
create or replace procedure test_xg_pl is
begin
dbms_output.put_line('Hello world!this is the first procedure');
end;

2、建立一个带输入输出参数的存储过程：把输入的数据传给输出参数
create or replace procedure test_xg_p2(a in number,x out number) is
begin
x:=a;
end test_xg_p2;

3、建立一个逻辑判断的存储过程，并包含输入输出参数：近似分数的登记判断
create or replace procedure test_xg_p3(a in number,x out varchar2) is
begin

if a>=90 then
begin
x:='A';
end;
end if;

if a<90 then
begin
x:='B';
end;
end if;

if a<80 then
begin
x:='C';
end;
end if;

if a<70 then
begin
x:='D';
end;
end if;

if a<60 then
begin
x:='E';
end;
end if;

end test_xg_p3;

4、建立一个带循环的存储过程：近似累加函数
create or replace procedure test_xg_p4(a in number,x out varchar2) is
tempresult number(16);
begin
tempresult:=0;
for tempa in 0..a loop
begin
tempresult:=tempresult + tempa;
end;
end loop;
x:=tempresult;
end test_xg_p4;

5、建立一个能从数据库中特定表中返回数据的存储过程
create or replace procedure test_xg_p5(x out varchar2) is
tempresult varchar2(1024);
begin
tempresult:='start->';
select loc into tempresult from dept where deptno=12;
x:=tempresult;
end test_xg_p5;

sql/plus调用
SQL> var info1 varchar2;
SQL> declare
  2    begin
  3     test_xg_p5(:info1);
  4    end;
  5  /
 
PL/SQL procedure successfully completed
info1
---------
南京
 
SQL>

6、建立一个能使用游标的带循环的存储过程
create or replace procedure test_xg_p6(x out varchar2) is
tempresult varchar2(10240);
cursor cursor1 is select * from dept;
begin
  tempresult:='start->';
    for cursor_result in cursor1 loop
      begin
      tempresult:=tempresult||cursor_result.deptno||cursor_result.dname||cursor_result.loc;
      end;
    end loop;
  x:=tempresult;
end test_xg_p6;

sql/plus调用
SQL> var info1 varchar2;
SQL> declare
  2  begin
  3  test_xg_p6(:info1);
  4  end;
  5  /
 
PL/SQL procedure successfully completed
info1
---------
start->15ffgg13comwww10ACCOUNTINGNEW YORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON5bbnn12开发南京
 
SQL>

7、插入数据
create or replace procedure testa(para0 in number,para1 in varchar2,para2 in varchar2)
 as
 begin
    insert into scott.dept(deptno,dname,loc) values(para0,para1,para2);
 end testa;

//java调用插入
   proc = con.prepareCall("{call scott.testa(?,?,?)}");
   proc.setInt(1, 13);
   proc.setString(2, "com");
   proc.setString(3, "www");
   proc.execute();

8、查询数据列表
8.1建立一个程序包
create or replace package testpackage as
 type test_cursor is ref cursor;
end testpackage;

8.2建立存储过程
create or replace procedure testc(p_cursor out testpackage.test_cursor) is
begin
open p_cursor for
     select * from scott.dept order by deptno asc;
end testc;

//java调用查询列表
   proc = con.prepareCall("{call scott.testc(?)}");
   proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
   proc.execute();
   System.out.println("创建CallableStatement成功!");

   rs = (ResultSet) proc.getObject(1);
   System.out.println("操作数据表成功!");

   while (rs.next()) {
    System.out.print(rs.getLong("DEPTNO") + "    ");
    System.out.print(rs.getString("DNAME") + "    ");
    System.out.print(rs.getString("LOC") + "    ");
    System.out.println();

   }