【PL/SQL開發】-----詭異啊

月初醫保對醫院藥店進行批量結算，在該過程中發現一個問題，在此記錄一下，當一個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部落格 ” ，連結：http://blog.itpub.net/13726712/viewspace-715666/，如需轉載，請註明出處，否則將追究法律責任。

【PL/SQL開發】-----詭異啊

相關文章