Oracle PL/SQL/System Packages/DBMS SESSION

Материал из SQL эксперт
Перейти к: навигация, поиск

This script illustrates DBMS_SESSION.RESET_PACKAGE.

   <source lang="sql">

SQL> SQL> SQL> CREATE TABLE MyTable (

 2    num_col    NUMBER,
 3    char_col   VARCHAR2(60)
 4    );

Table created. SQL> SQL> set serveroutput on SQL> SQL> CREATE OR REPLACE PROCEDURE InsertTemp(

 2    p_NumCol MyTable.num_col%TYPE,
 3    p_CharCol MyTable.char_col%TYPE) AS
 4  BEGIN
 5    INSERT INTO MyTable VALUES (p_NumCol, p_CharCol);
 6  END InsertTemp;
 7  /

Procedure created. SQL> show errors No errors. SQL>

First create a package with some initial state.

SQL> CREATE OR REPLACE PACKAGE MyPackage AS

 2    v_NumVar NUMBER := 10;
 3    v_StringVar VARCHAR2(50) := "Hello World!";
 4  END MyPackage;
 5  /

Package created. SQL> show errors No errors. SQL> SQL> BEGIN

 2    InsertTemp(1, "Initial values:");
 3    InsertTemp(2, "v_NumVar = " || MyPackage.v_NumVar);
 4    InsertTemp(3, "v_StringVar = " || MyPackage.v_StringVar);
 5  END;
 6  /

PL/SQL procedure successfully completed. SQL> SQL> BEGIN

 2    MyPackage.v_NumVar := 20;
 3    MyPackage.v_StringVar := "Goodbye World!";
 4    InsertTemp(4, "Changed values:");
 5    InsertTemp(5, "v_NumVar = " || MyPackage.v_NumVar);
 6    InsertTemp(6, "v_StringVar = " || MyPackage.v_StringVar);
 7
 8    DBMS_SESSION.RESET_PACKAGE;
 9
10    InsertTemp(7, "After RESET_PACKAGE:");
11    InsertTemp(8, "v_NumVar = " || MyPackage.v_NumVar);
12    InsertTemp(9, "v_StringVar = " || MyPackage.v_StringVar);
13  END;
14  /

PL/SQL procedure successfully completed. SQL> SQL> BEGIN

 2    InsertTemp(10, "After RESET_PACKAGE and call:");
 3    InsertTemp(11, "v_NumVar = " || MyPackage.v_NumVar);
 4    InsertTemp(12, "v_StringVar = " || MyPackage.v_StringVar);
 5  END;
 6  /

PL/SQL procedure successfully completed. SQL> SQL> SELECT char_col

 2    FROM MyTable
 3    ORDER BY num_col;

CHAR_COL


Initial values: v_NumVar = 10 v_StringVar = Hello World! Changed values: v_NumVar = 20 v_StringVar = Goodbye World! After RESET_PACKAGE: v_NumVar = 20 v_StringVar = Goodbye World! After RESET_PACKAGE and call: v_NumVar = 10 CHAR_COL


v_StringVar = Hello World! 12 rows selected. SQL> SQL>

</source>