oracle存储过程使用

之前项目写了一段时间存储过程，抽点时间做个简单的总结，与大家共享。

数据库使用：

--Oracle存储过程创建语法：

--创建存储过程 create or replace procedure 存储过程名(param1 in type,param2 out type)

create or replace procedure test_proc(v_type in varchar2)

as

--常用变量，格式-变量 类型（值范围）;

v_tsql        varchar2(2000); 

v_test_name  varchar2(50); 

v_id varchar2(50); 

v_rule varchar2(50); 

--游标声明及赋值 cursor 游标名（参数名 参数类型） is sql查询语句，条件可以包含参数，注：表名不可用参数代替，识别不出来。

CURSOR get_test_info(v_test_name varchar2) is

  select t.id,t.rule from testA t where t.status = 1 and t.test_name = v_test_name;

--开始存储过程

Begin

--执行游标，获取符合sql条件的数据列for 对象 in 游标名 loop

for job_row in get_test_info(v_type) loop

--开始循环

begin

--判断对象中属性rule(为游标sql查询列)不为空

if job_row.rule is not null then

--删除sql赋值

v_tsql:='delete from testA where id ='''||job_row.id||''' and trunc(d_day) = trunc(sysdate)';

--执行

         execute immediate v_tsql;

        --提交

         commit;

         --插入sql赋值，出现变量值:1,:2，为插入变量值，类似java执行sql语句中'?'

          v_tsql:= 'insert into testA (name) select name from testB where id = :1 ';

         --执行，using 变量值，顺序对应v_tsql中的:1,:2

          execute immediate v_tsql using job_row.id;

          commit;

          --输出sql，可以查看内容

          dbms_output.put_line(v_tsql);

       end if;

      EXCEPTION

         When others then

            begin

               dbms_output.put_line(SQLERRM);

             end ;

       end;

    end loop;

   

下面为存储过程其他循环方式，与上面for...loop等同 

   --游标循环 while写法

    --打开游标

    open get_test_info(v_type);

    fetch get_test_info into v_id,v_rule;

    while get_test_info%FOUND loop

    if job_row.rule is not null then

--删除sql赋值

v_tsql:='delete from testA where id ='''||job_row.id||''' and trunc(d_day) = trunc(sysdate)';

--执行

        execute immediate v_tsql;

       --提交

        commit;

        --插入sql赋值，出现变量值:1,:2，为插入变量值，类似java执行sql语句中'?'

         v_tsql:= 'insert into testA (name) select name from testB where id = :1 ';

        --执行，using 变量值，顺序对应v_tsql中的:1,:2

         execute immediate v_tsql using job_row.id;

         commit;

         --输出sql，可以查看内容

         dbms_output.put_line(v_tsql);

      end if;

      fetch get_test_info into v_id,v_rule;

       end loop;

  close  get_test_info;

 

  --游标循环 Loop写法

    --打开游标

    open get_test_info(v_type);

    loop

    fetch get_test_info into v_id,v_rule;

    EXIT WHEN get_test_info%NOTFOUND;

    if v_id is not null then

--删除sql赋值

v_tsql:='delete from testA where id ='''||v_id||''' and trunc(d_day) = trunc(sysdate)';

--执行

        execute immediate v_tsql;

       --提交

        commit;

        --插入sql赋值，出现变量值:1,:2，为插入变量值，类似java执行sql语句中'?'

         v_tsql:= 'insert into testA (name) select name from testB where id = :1 ';

        --执行，using 变量值，顺序对应v_tsql中的:1,:2

         execute immediate v_tsql using v_id;

         commit;

         exit;

         --输出sql，可以查看内容

         dbms_output.put_line(v_tsql);

      end if;

     end loop;

  close  get_test_info;

java调用：

String procedure = "{call test_proc(?) }";

//取得数据库连接

CallableStatement call = aaa.getConnection().prepareCall(procedure);

call.setString(1, opertype);

// 执行

call.execute();