Oracle中存储过程的使用

时间:2022-03-13 03:54:27

   存储过程是写在数据库中的sql语句,它相比写在后台代码中的sql语句速度更快。因为它只需要连接一次数据库,就可以执行较为复杂的sql,不需要多次连接数据库导致系统性能下降,只需要一次连接数据库,然后在

数据库中执行一系列复杂的sql,因为已经在数据库中执行sql了,所以不用再去连接数据库,执行起来速度更快。

Oracle中存储过程的结构:

创建存储过程:

CREATE OR REPLACE PACKAGE mes_bs_pqc_pkg AS

存储过程方法头:

  procedure query_checktype(p_orgid  number    
                             ,p_rescode varchar2              
                             ,p_msg out nvarchar2
                             ,p_data out sys_refcursor);  

存储过程方法体:

procedure query_checktype(p_orgid  number 
                             ,p_rescode varchar2               
                             ,p_msg out nvarchar2
                             ,p_data out sys_refcursor) is
       p_sql    varchar2(1000);                     
    begin
       p_sql := select b.opcode,b.opdesc from tblres_b t left join tblop2res a on a.rescode = t.rescode and a.orgid = t.orgid
                 left join tblop_b b on b.opcode = a.opcode and b.orgid = a.orgid where t.orgid =||p_orgid|| and t.rescode = ‘‘‘||p_rescode||‘‘‘‘;
       
       open p_data for p_sql;
       p_msg := success;                                          
         EXCEPTION
           WHEN OTHERS THEN
             p_msg := SQLERRM || [||FAILED || ];
             RETURN;     
    end; 

p_sql为变量,用于存放sql

p_data为输出参数,sys_refcursor为参数类型,使用游标输出

 

下面分享一些常见的存储过程写法:

1.执行sql,并且把结果赋给变量

v_opcode     varchar2(120);

execute immediate select opcode from tblop2res where orgid = :1 and rescode = :2
                         into v_opcode
                         using p_orgid,p_rescode;

p_orgid、p_rescode为传入参数,v_opcode为输出结果。


2.传入数组类型的参数,并获取数据中的值

   procedure post_pqc_check_applynew(p_orgid  number
                                    ,p_rcard  nvarchar2
                                    ,p_bgentask  nvarchar2 --是否产生任务
                                    ,p_arr_name mes_vs_prd_pub_pkg.normal_str_array
                                    ,p_arr_value mes_vs_prd_pub_pkg.normal_str_array
                                    ,x_ret_code                OUT VARCHAR2
                                    ,x_ret_msg                 OUT VARCHAR2
                                    ,x_ret_data                OUT SYS_REFCURSOR)                               

p_arr_name,p_arr_value为数组类型的参数

验证两个数组的长度是否一直,不一致则抛出异常:

--传入参数名称与值长度不一致
      IF p_arr_name.count != p_arr_value.count THEN
        x_ret_code := $lf_error_array_length;
        x_ret_msg  := mes_vs_sys_pub_pkg.get_err_desc(x_ret_code);
        RETURN;
      END IF;

 循环获取数组中的数据并赋值给变量:

  TYPE normal_str_array IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;  --可变数组

--获取数组中值
 FOR v_i in 1 .. p_arr_name.count LOOP
      IF p_arr_name(v_i) = DSSCODE THEN
        v_dsscode := p_arr_value(v_i);
      ELSIF p_arr_name(v_i) = MODELNAME THEN
        v_model := p_arr_value(v_i);
      ELSIF p_arr_name(v_i) = SHIFTCODE THEN
        v_shiftcode := p_arr_value(v_i);
      ELSIF p_arr_name(v_i) = APPDATE THEN
        v_appdate := p_arr_value(v_i);
      ELSIF p_arr_name(v_i) = APPTIME THEN
        v_apptime := p_arr_value(v_i);
      ELSIF p_arr_name(v_i) = FMLFLAG THEN
        v_fmlflag := to_number(p_arr_value(v_i));
      ELSIF p_arr_name(v_i) = CHKTYPE THEN
       v_ckktype := p_arr_value(v_i);
       ELSIF p_arr_name(v_i) = CHKGROUP THEN
            v_chkgroup := to_number(p_arr_value(v_i));
       ELSIF p_arr_name(v_i) = TORESCODE THEN
            v_torescode := p_arr_value(v_i);
       ELSIF p_arr_name(v_i) = MITEMCODE THEN
            v_mitemcode := to_number(p_arr_value(v_i));
       ELSIF p_arr_name(v_i) = CUSER THEN
            v_cuser := p_arr_value(v_i);
       ELSIF p_arr_name(v_i) = FROMRESCODE THEN
          v_rescode := p_arr_value(v_i);           
       ELSIF p_arr_name(v_i) = TOTEMPLATEID THEN
          v_templateid := p_arr_value(v_i);   
       else
          null;
      end if;
      end loop;

使用Type来定义一个数组:TYPE normal_str_array IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;

把normal_str_array 作为一个Table类型。

3.使用游标循环,处理数据                             

declare cursor task_ops is select templateid,opcode,version from tblpqccheckconf where orgid = p_orgid and templateid in (select * 
from table(cast(fn_split(v_templateid,chr(44)) AS ty_str_split))); task_op task_ops%rowtype; begin open task_ops; loop fetch task_ops into task_op; exit when task_ops%notfound; --生成taskid
select genuknum() into v_taskid from dual; begin insert into ro_tasklist(taskid,orgid,formid,version,rescode,piecetype,taskstart,taskstate,sampleno,sampleuser, cuser,cdate,ctime,muser,mdate,mtime,actor,actortype,templateid) values (v_taskid,p_orgid,v_id,task_op.version,v_rescode, to_number(v_fmlflag),1,3,upper(p_rcard),v_cuser,v_cuser,mes_vs_lg_utl_pkg.get_sysdate, mes_vs_lg_utl_pkg.get_systime,v_cuser,mes_vs_lg_utl_pkg.get_sysdate,mes_vs_lg_utl_pkg.get_systime, v_checkiegroup,1,task_op.templateid); EXCEPTION WHEN OTHERS THEN x_ret_code := SQLCODE; x_ret_msg := SQLERRM || [ || v_proc_name || ]; RETURN; END; end loop; close task_ops;

4.在procedure调用其他的procedure

mes_bs_pqc_pkg.save_pqc_record_dtl(p_templateid,p_orgid,p_arrdtl_value(v_i),p_isconfirm,x_ret_code,x_ret_msg,x_ret_data);

 mes_bs_pqc_pkg为package名称,save_pqc_record_dtl为procedure名称。