`

存储过程备忘

SQLCC++C# 
阅读更多

    不得不感慨逝者如斯夫，就这样，转眼间在公司呆了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;
/

 

分享到：
| 将FML（FML32）应用于WTC
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    DotNet操作Oracle存储过程备忘[定义].pdf

    过程在概念上有所不同，函数需要返回一个值，而存储过程可以执行一系列操作但不一定返回值。在Oracle数据库中，两者都是预编译的SQL代码集合，可以在需要时快速执行。在.NET环境中，调用Oracle存储过程与调用函数的...

    oracle存储过程学习经典入门

    关于 Oracle 存储过程的若干问题备忘 在学习 Oracle 存储过程时，需要了解一些常见问题的解决方法。例如，在 Oracle 中，数据表别名不能加 as。在存储过程中，select 某一字段时，后面必须紧跟 into，如果 select ...

    Android案例：备忘录。利用数据存储的文件内部存储

    在Android应用开发中，数据存储是一项基础且重要的技术，它使得应用可以持久化地保存用户数据，例如备忘录应用中的记录。本案例是关于如何使用Android的数据存储机制，特别是文件内部存储，来创建一个简单的备忘录...

    oracle 存储过程学习经典

    #### 七、关于Oracle存储过程的若干问题备忘 1. **数据表别名不能加as**: 在Oracle中，定义表别名时不能使用关键字`AS`。 2. **SELECT INTO 的使用**: 当使用`SELECT INTO`时，必须确保数据库中存在对应的记录，...

    oracle存储过程学习经典

    #### 关于Oracle存储过程的若干问题备忘 1. **数据表别名限制**：在Oracle中，使用数据表别名时不能使用`AS`关键字，这与某些其他数据库系统有所不同。 2. **SELECT INTO语法**：当使用`SELECT INTO`语句时，必须...

    通过外部存储保存备忘录信息.zip

    在实际操作中，备忘录可能包含多个字段，如标题、内容、日期等，因此在序列化和反序列化过程中需要特别注意对象结构的完整性。此外，为了确保数据安全，可以考虑加密存储的数据。 在保存备忘录时，建议使用JSON格式...

    Oracle+PlSql存储过程

    **关于Oracle存储过程的若干问题备忘** 1. 数据表别名在Oracle中不使用`as`关键字，而是直接跟别名，如`table_name alias_name`。 2. 在存储过程中，`SELECT`语句通常需要与`INTO`关键字结合，将数据插入变量。如果...

    手机备忘录程序

    5. **gradle.properties**：这是一个全局或项目级的属性文件，用于存储构建过程中的配置变量，如签名信息、版本号等，以便在构建时使用。 6. **local.properties**：此文件通常包含特定于机器的配置，比如指向...

    Oracle_PLSQL_存储过程

    #### 关于Oracle存储过程的若干问题备忘 1. **别名限制：** 在Oracle中，数据表别名不能加`AS`关键字。 2. **SELECT INTO 语句：** 在存储过程中使用`SELECT INTO`语句时，必须确保数据库中有对应的记录，否则会抛...

    android 作业 备忘录

    备忘录应用通常会创建一个数据库表，包含标题、内容等字段，用于存储用户的备忘录。 5. **Content Provider**：在Android中，Content Provider是不同应用间共享数据的标准方式。虽然不是必需，但使用Content ...

    Java日历及备忘录

    Java日历及备忘录程序是一个实用的桌面应用程序...总的来说，"Java日历及备忘录"项目涵盖了Java编程、GUI设计、事件处理、时间管理、数据存储等多个方面，是一个综合性的实践项目，有助于提升开发者在这些领域的技能。

    Oracle PlSql 存储过程

    四、 关于 Oracle 存储过程的若干问题备忘 在使用 Oracle 存储过程时，需要注意以下几点： 1. 在 Oracle 中，数据表别名不能加 as。 在 Oracle 中，数据表别名不能加 as，这是因为 Oracle 的语法规则不允许这么做...

    oracle存储过程学习经典[语法+实例+调用].doc

    #### 关于Oracle存储过程的若干问题备忘 1. **数据表别名不能加as**：在Oracle中，为数据表定义别名时，不使用关键字`as`，例如：`SELECT * FROM table_name t`。 2. **使用SELECT INTO**：在存储过程中，使用`...

    html制作的备忘录html制作的备忘录

    在HTML备忘录的创建过程中，主要涉及以下几个关键知识点： 1. **HTML基础结构**：每个HTML文档都始于`&lt;!DOCTYPE html&gt;`声明，接着是`&lt;html&gt;`元素，它是整个文档的根元素。在`&lt;html&gt;`内，有两个重要的子元素——`...

    android studio 简单备忘录

    以上是关于“android studio 简单备忘录”项目的一些核心知识点，涵盖了从界面设计到数据管理的全过程。实际开发时，还需要考虑性能优化、异常处理、用户体验等因素，确保应用的质量和用户体验。

    桌面widget备忘录

    对于【桌面widget备忘录】，开发过程中可能涉及以下几个关键知识点： 1. **AppWidgetProvider**: 这是Android系统中的一个抽象类，用于监听和响应小部件的事件，如添加、删除或更新。开发者需要继承这个类，并重写...

    Android程序研发源码Android 备忘录源码.zip

    这份"Android程序研发源码Android 备忘录源码.zip"包含了一个完整的备忘录应用的源代码，可以帮助开发者深入理解Android应用的构建过程。下面我们将详细探讨其中涉及的关键技术和实践。 1. **AndroidManifest.xml**...

    ios 手机备忘录

    iOS备忘录提供了笔记本和标签功能，用户可以创建不同的笔记本来分类存储备忘录，比如工作、学习、旅行等。同时，标签功能允许用户按照主题或项目来组织备忘录，方便查找和管理。 七、待办事项与提醒 在Todolist子...

    DELPHI个人备忘录

    2. **数据存储**：备忘录中的数据需要持久化存储，通常可以使用本地文件（如文本文件、XML或JSON）或者数据库。DELPHI支持多种数据库接口，如BDE（Borland Database Engine）、ADO（ActiveX Data Objects）等，你...

    备忘录JAVA代码

    【标题】"备忘录JAVA代码"涉及到的核心知识点主要集中在Java编程语言以及备忘...通过实现这样的备忘录应用，开发者可以锻炼到实际项目开发中的诸多方面，从需求分析到代码实现，再到测试和调试，整个过程都能得到锻炼。

Magicbox
Global site tag (gtag.js) - Google Analytics