月初医保对医院药店进行批量结算，在该过程中发现一个问题，在此记录一下，当一个session执行一个包的时候，该包有两个过程，先执行过程一将计算结果insert到表test中，然后过程二开始被执行insert select 进行表的复制。

但过程二select该表无返回值。奇诡。。。

将其代码逻辑摘出，实验不以重现，有遇到的朋友留个言，谢了。

exec test_pkg.TEST_MAIN

CREATE OR REPLACE PACKAGE thsimis.test_pkg
AS
/******************************************************************************
NAME: test_pkg
PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   --------- ---------- --------------- ------------------------------------
   1.0        2012-2-2             1. Created this package.
******************************************************************************/
   PROCEDURE test_prc;

PROCEDURE test_prc2;

PROCEDURE test_main;
END test_pkg;
/

CREATE OR REPLACE PACKAGE BODY test_pkg
IS
   sqlstr     VARCHAR2 (300);
   vn_count   NUMBER (6)     := 0;
   vs_name    VARCHAR2 (18) := '12';

   PROCEDURE test_prc
   IS
      tmpvar   NUMBER;
/******************************************************************************
   NAME:       test
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   --------- ---------- --------------- ------------------------------------
   1.0        2012-2-2          1. Created this procedure.

NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     test
      Sysdate:         2012-2-2
      Date and Time:   2012-2-2, 22:49:36, and 2012-2-2 22:49:36
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
BEGIN
tmpvar := 0;

      BEGIN
         INSERT INTO TEST
              VALUES (12, '12');
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         NULL;
      WHEN OTHERS
      THEN
         -- Consider logging the error and then re-raise
         RAISE;
   END test_prc;

   PROCEDURE test_prc2
   IS
      tmpvar     NUMBER;
      n_cursor   PLS_INTEGER    := 0;
      n_ret      PLS_INTEGER    := 0;
      sqlstr     VARCHAR2 (300);
      sqlstr1    VARCHAR2 (300);
      vn_count   NUMBER (6)     := 0;
      vs_name    VARCHAR2 (18) := '12';
/******************************************************************************
   NAME:       test
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   --------- ---------- --------------- ------------------------------------
   1.0        2012-2-2          1. Created this procedure.

NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     test
      Sysdate:         2012-2-2
      Date and Time:   2012-2-2, 22:49:36, and 2012-2-2 22:49:36
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
   BEGIN
      tmpvar := 0;
      sqlstr := 'select id from test where name = ' || vs_name;

EXECUTE IMMEDIATE sqlstr
INTO vn_count;

      DBMS_OUTPUT.put_line (vn_count);
      sqlstr1 :=
            'insert into test_ll select id ,name from test where name = '
         || vs_name
         || ';';
      n_cursor := DBMS_SQL.open_cursor ();
      sqlstr1 := 'begin ' || sqlstr1 || ' end;';
      DBMS_SQL.parse (n_cursor, sqlstr1, DBMS_SQL.native);
      n_ret := DBMS_SQL.EXECUTE (n_cursor);

      -- 关闭游标
      IF DBMS_SQL.is_open (n_cursor)
      THEN
         DBMS_SQL.close_cursor (n_cursor);
      END IF;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         NULL;
      WHEN OTHERS
      THEN
         -- Consider logging the error and then re-raise
         RAISE;
   END test_prc2;

   PROCEDURE test_main
   IS
      sqlstr     VARCHAR2 (300);
      vn_count   NUMBER (6)     := 0;
      vs_name    VARCHAR2 (18) := '12';
   BEGIN
      test_prc;

      FOR i IN 1 .. 9
      LOOP
         test_prc2;
      END LOOP;
   END test_main;
END;

ITPub博客

【PL/SQL开发】-----诡异啊

最新文章