Oracle Package的应用示例

--标准包
CREATE OR REPLACE PACKAGE PSNINFO IS
  Procedure Next_Seed
  ( ttt varchar2 , next_value out number);
END;

CREATE OR REPLACE PACKAGE BODY PSNINFO
IS
    Procedure Next_Seed
     ( ttt varchar2 , next_value out number)
     IS
     
      Begin
             insert into SEED (category, next) values (1, 1);
    end Next_Seed;
END;

-------------------------------------------------------------------------
--取得自增数字

CREATE OR REPLACE PACKAGE PSNINFO2 IS
  Procedure Next_Seed
  ( category varchar2 , next_value out number);
END;

CREATE OR REPLACE PACKAGE BODY PSNINFO2
IS
    Procedure Next_Seed
     ( category varchar2, next_value out number)
     IS

      CURSOR cursor_sequence
        IS
           Select SEED.next From SEED Where SEED.category = Next_Seed.category
            FOR UPDATE;
        NextSeed number;
      Begin
        OPEN cursor_sequence;

        FETCH cursor_sequence INTO NextSeed;
        IF cursor_sequence%NOTFOUND
        THEN
            LOCK TABLE SEED IN EXCLUSIVE MODE;
            NextSeed := 1;
            insert into SEED (category, next) values (category, 1);
        ELSE
            NextSeed := NextSeed + 1;
            Update SEED set next = NextSeed Where SEED.category = Next_Seed.category;
        END IF;
        next_value := NextSeed;

    end Next_Seed;
END;

-------------------------------------------------------------------------
--带游标返回的包

CREATE OR REPLACE PACKAGE PSNINFO4 IS
  TYPE type_resource_cursor IS REF CURSOR;
  Procedure GETPSN
  (PSNDATA out type_resource_cursor);
END;

CREATE OR REPLACE PACKAGE BODY PSNINFO4
IS
    Procedure GETPSN
     (PSNDATA out type_resource_cursor)
     IS

      Begin
        OPEN PSNDATA FOR
            Select * From PSNACCOUNT;
       
    end GETPSN;
END;