存储过程备忘

    不得不感慨逝者如斯夫，就这样，转眼间在公司呆了3年之多。3年前常用的sql proc相关知识，在今天接手离职同事工作时发现突然间无法完全忆起。于是，迫使自己找出3年之前的资料，罗列一下存储过程的常用方式，以此备案！

   

标准式游标用法和静态sql

CREATE OR REPLACE PROCEDURE p_use_cursor(
   i_seq_ids   IN       VARCHAR2,
   o_code      OUT      INT,
   o_msg       OUT      VARCHAR2
)
AS
/***********************************************************
注释区:采用标准式游标用法和静态sql
***********************************************************/
   TYPE t_cur IS REF CURSOR;

   v_count             INT;
   c_check             t_cur;
   sqlstr              VARCHAR2 (5000);
   v_code     VARCHAR2 (4);
   v_sid         VARCHAR2 (5);
BEGIN
   v_count := 0;
   o_code := 0;
   o_msg := 'success';
   sqlstr :=
         ' select LOCALNET_CODE,ORG_SID from xtable where seq_id in '
      || i_seq_ids;

   OPEN c_check FOR sqlstr;

   LOOP
      FETCH c_check
       INTO v_code, v_sid;

      EXIT WHEN c_check%NOTFOUND;

      --校验本地网区号
      SELECT COUNT (*)
        INTO v_count
        FROM localnet
       WHERE design_code = v_code;

      IF (v_count = 0)
      THEN
         o_code := 1;
         o_msg := '代码 ' || v_code || ' 不存在!';

         CLOSE c_check;

         RETURN;
      END IF;

      --ORG_SID定长5位
      IF (LENGTH (v_sid) <> 5)
      THEN
         o_code := 2;
         o_msg := 'ORG_SID ' || v_sid || ' 的长度必须为5位!';

         CLOSE c_check;

         RETURN;
      END IF;

      v_count := 0;

      --ORG_SID必须是数字
      SELECT NVL2 (TRANSLATE (v_sid, '\1234567890', '\'), 2, 1)
        INTO v_count
        FROM DUAL;

      IF (v_count = 2)
      THEN
         o_code := 3;
         o_msg := 'ORG_SID ' || v_sid || ' 必须为数字!';

         CLOSE c_check;

         RETURN;
      END IF;

   END LOOP;

   CLOSE c_check;
   
END p_use_cursor;
/

  

直接采用游标和静态sql

CREATE OR REPLACE PROCEDURE p_use_cursor (
   i_batch_id   IN       VARCHAR2,
   i_user_id    IN       VARCHAR2,
   o_code       OUT      INT,
   o_msg        OUT      VARCHAR2
)
AS
/***********************************************************
注释区:直接采用游标和静态sql
***********************************************************/
   CURSOR cur_adjust
   IS
      SELECT *
        FROM table a, table b
       WHERE a.user_id = i_user_id
         AND a.status = 1;

   l_count     INT;
   err_count   INT;
   ok_count    INT;
   now_date    VARCHAR2 (14);
   comp_date   VARCHAR2 (14);
BEGIN
   l_count := 0;
   err_count := 0;
   ok_count := 0;
   now_date := TO_CHAR (SYSDATE, 'YYYYMMDDhh24miss');
   comp_date := i_batch_id || '000000';
   
   FOR c_r IN cur_adjust
   LOOP
     IF (UPPER (c_r.service_code) = 'IVR' OR UPPER (c_r.service_code) = 'PIVR')
      THEN
         o_code := 9;
         err_count := err_count + 1;
         o_msg := 'xxxxxx';

         INSERT INTO zzyw_adjust_error
                     (user_id, user_name, error_time, error_desc,
                      ori_record
                     )
              VALUES (c_r.user_id, c_r.user_name, now_date, o_msg,
                         c_r.service_code
                      || ','
                      || c_r.localnet_abb);

         DELETE FROM yuy_report
               WHERE ROWID = c_r.ROWID;

         GOTO CONTINUE;
      END IF;

--其它操作

      ok_count := ok_count + 1;

      <<continue>>
      NULL;
   END LOOP;

   --其它静态sql

   o_code := 0;
   o_msg :=
         '成功提交的记录数为'
      || ok_count
      || '，错单数为'
      || err_count
      || '，用户可以到本期数据查询菜单中查询错单记录';
   COMMIT;
END p_use_cursor;
/

  

 静态sql完整事务模式

CREATE OR REPLACE PROCEDURE P_TMP_TP_TRUNK (
   i_dealdate		In      tl_data_audit.data_date%TYPE,
   o_returncode   OUT   INTEGER,
   o_returnmsg    OUT   VARCHAR2
)
--******************************************************************************************************************
-- SQL 存储过程
-- 名称  ： P_TMP_TP_TRUNK
-- 注意  ：
-- 参数  ： i_dealdate:要统计的日期
-- 功能描述 ： 取开始时间最近的trunk_id到临时表
--******************************************************************************************************************
AS
BEGIN
   o_returncode := -1;
   o_returnmsg := 'TradeOk!';

   --/* 取开始时间最近的入中继信息到中继临时表
   INSERT INTO table_trunk
               (。。。)
        SELECT source_id, trunk_code, trunk_side, start_date
          FROM (SELECT source_id, trunk_code, trunk_side, start_date ,
                       rank() over (PARTITION BY source_Id, trunk_code ORDER BY start_date DESC ) rank
                 FROM (SELECT source_Id, trunk_code, trunk_side, MAX(start_date) start_date
                         FROM tp_trunk_js
                        WHERE substr(end_date,1,8) > i_dealdate
                        GROUP BY source_id, trunk_code , trunk_side)
                WHERE trunk_side IN (0,1)
                GROUP BY 。。。
                )
         WHERE rank = 1
          ;


   --/* 取开始时间最近的入中继信息到入中继信息临时表
   -- sql语句略
   --/*删除中继临时表中的数据
   p_sd_truncatetable ('tmp_tp_trunk', o_returncode, o_returnmsg);

   

   --/* 取开始时间最近的入中继信息到入中继信息临时表
   --略;

   o_returncode := 0;

   IF o_returncode = 0
   THEN
      COMMIT;
   ELSE
      ROLLBACK;
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      o_returncode := -1;
      o_returnmsg := SUBSTR ('[01]' || 'tp_trunk没有合适数据 ' || SQLERRM, 1, 255);
      ROLLBACK;

   WHEN OTHERS
   THEN
      o_returncode := -1;
      o_returnmsg :=
              SUBSTR ('[01]' || 'P_TMP_TP_TRUNK错误告警 ' || SQLERRM, 1, 255);
      ROLLBACK;
END;
/

 

CREATE OR REPLACE PROCEDURE P_EXE_MONTHPROC (
        O_ReturnCode    	Out     integer,
        O_ReturnMsg     	Out     varchar2
)
--*****************************************************************************************
-- SQL 存储过程
-- 名称  ： P_EXE_MONTHPROC
-- 注意  ：
-- 参数  ：
-- 功能描述 ： 调用月存储过程
--          ： 该程序由P_AFTER调用
-- 未完功能 ：
-- 返回值   ： 0：正确，-1：错误
--***********************************************************************************************
as
     v_data_flag         INT;
     v_deal_date	       number;

     cursor cur1 is
     select distinct sett_month from table_dealdate;
BEGIN
     o_returncode := -1;
     o_returnmsg := 'TradeOk!';

     select 1 into v_data_flag
     from dual
     where exists(
             select 1 from table_dealdate
            );

     for c1 in cur1 loop

    	select (c1.sett_month*100 + 20) into v_deal_date from dual;

    	p_fact_settle_rule(v_deal_date, O_ReturnCode, O_ReturnMsg);
     	If O_ReturnCode <> 0 Then
     		goto ER;
     	End If;

     	p_fact_interconnect_month(v_deal_date, O_ReturnCode, O_ReturnMsg);
     	If O_ReturnCode <> 0 Then
     		goto ER;
     	End If;
		
     end loop;

     p_sd_truncatetable ('tmp_dealdate', o_returncode, o_returnmsg);

<<ER>>
     If O_ReturnCode = 0 Then
     	commit;
     Else
    	rollback;
     END IF;

     EXCEPTION
     when no_data_found   then
          O_ReturnCode := -1;
          O_ReturnMsg := substr('[01]'||'tmp_dealdate表中没有数据 '||sqlerrm,1,255);
          ROLLBACK;

     WHEN OTHERS THEN
          O_ReturnCode := -1;
          O_ReturnMsg := substr('[01]'||'P_EXE_MONTHPROC错误告警 '||sqlerrm,1,255);
     ROLLBACK;

END;
/

动态sql完整事务模式

CREATE OR REPLACE PROCEDURE              P_TMP_P01(i_dealdate   IN TMP_OTHER_TODAY_SETT.statdate%TYPE,
                                                       O_ReturnCode OUT INTEGER,
                                                       O_ReturnMsg  OUT VARCHAR2)
 AS
  v_Cursor         NUMBER;
  v_rows           NUMBER;
  v_SQL            VARCHAR2(5000);
  v_SQL_data       VARCHAR2(400);
  v_insert_sql     VARCHAR2(1000);
  v_select_sql     VARCHAR2(2000);
  v_from_sql       VARCHAR2(200);
  v_where_sql      VARCHAR2(800);
  v_groupby_sql    VARCHAR2(1500);
  v_partition_name VARCHAR2(5);

  v_parm      NUMBER;
  v_data_flag INT;
  v_tablename VARCHAR2(20);
BEGIN
  o_returncode     := -1;
  o_returnmsg      := 'TradeOk!';
  v_partition_name := 'p1';

  --/*从参数表取得结果表中的省份代码
  SELECT par_value INTO v_parm FROM xtableWHERE par_code = 1;

  --/*获得要处理的清单表名
  SELECT 'xtable_' || SUBSTR(i_dealdate, 7) INTO v_tablename FROM dual;

  --/*查看接口表是否有当天的数据
  v_sql_data := 'select 1 from dual where exists( select 1 from ' ||
                v_tablename || ' partition (' || v_partition_name ||
                ') where end_datetime between to_date(''' || i_dealdate ||
                '000000''' || ',''yyyymmddhh24miss'') and to_date(''' ||
                i_dealdate || '235959''' || ',''yyyymmddhh24miss''))';
  EXECUTE IMMEDIATE v_sql_data
    INTO v_data_flag;

  --/*向临时表中插入主叫数据
  v_insert_sql := 'INSERT INTO TMP_table_SETT (....) ';

  v_select_sql  := ' select   
            TO_CHAR(end_datetime,''yyyymmdd'') ,
            ....,SUM(local_discount_fee) , 
            SUM(toll_discount_fee) , SUM(local_discount_fee + toll_discount_fee), 
            SUM(sett_fee)';
  v_from_sql    := ' from ' || v_tablename || ' partition (' ||
                   v_partition_name ||
                   ') a , s_area b , tpw_iden_settle_type  c ';
  v_where_sql   := ' where end_datetime between to_date(''' || i_dealdate ||
                   '000000''' || ',''yyyymmddhh24miss'') and to_date(''' ||
                   i_dealdate || '235959''' ||
                   ',''yyyymmddhh24miss'') and called_area_code = b.area_code and b.prov_code = ' ||
                   v_parm || ' and a.sett_type = c.sett_type ' ||
                   ' and a.called_tsp_code = 11';
  v_groupby_sql := ' Group by    
            TO_CHAR(end_datetime,''yyyymmdd'') ,
            a.calling_code  ';
  v_sql         := v_insert_sql || ' ' || v_select_sql || ' ' || v_from_sql || ' ' ||
                   v_where_sql || ' ' || v_groupby_sql;

  BEGIN
    -- 开始执行动态SQL
    v_cursor := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.v7);
    v_rows := DBMS_SQL.EXECUTE(v_cursor);
    DBMS_SQL.CLOSE_CURSOR(v_cursor);
  EXCEPTION
    WHEN OTHERS THEN
      o_returnmsg := 'P_TMP_OTHER_TODAY_SETT_'||v_partition_name||'发生未知错误 01@: ' ||
                     SQLERRM;
      ROLLBACK;
      RETURN;
  END;

 
  o_returncode := 0;

  IF o_returncode = 0 THEN
    COMMIT;
  ELSE
    ROLLBACK;
  END IF;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    O_ReturnCode := 1;
    O_ReturnMsg  := SUBSTR('[01]' ||
                           'P_TMP_OTHER_TODAY_SETT_'||v_partition_name||'发生未知错误' ||
                           SQLERRM,
                           1,
                           255);
    ROLLBACK;
  
  WHEN OTHERS THEN
    O_ReturnCode := -1;
    O_ReturnMsg  := SUBSTR('[01]' ||
                           'P_TMP_OTHER_TODAY_SETT_'||v_partition_name||'发生未知错误' ||
                           SQLERRM,
                           1,
                           255);
    ROLLBACK;
  
END;
/