Tips for using temporary tables in Oracle stored procedures

Oracle Temporary table Knowledge

In Oracle, temporary tables are divided into sessions, transaction two, and the session-level temporary table data exists throughout the sessions until the end of sessions; Temporary table data at the transaction level disappears after Tranaction ends, that is, Commit/rollback or end session clears tranaction temporary table data.

1 Session-level temporary representation example

1 Creating SQL code create global temporary table temp_tbl (col_a varchar2) on commit preserve rows

2 Insert data SQL code INSERT into TEMP_TBL values (' Test session table ')

3 Commit SQL code commit;

4 Query SQL code SELECT *from TEMP_TBL

You can see that the data ' test session table ' record is still in.
Ends the session, logs back in, queries the data select *from Temp_tbl, and the record no longer exists because the system automatically clears the record when the session is closed.

2 Transaction-level temporary representation example

1 Creating SQL code create global temporary table temp_tbl (col_a varchar2) on commit delete rows

2 Insert data SQL code INSERT into TEMP_TBL values (' Test Transaction table ')

3 Commit SQL code commit;

4 Query SQL code SELECT *from TEMP_TBL

At this point you can see that the record you just inserted ' Test transaction table ' no longer exists because the database was already clear when it was submitted; Similarly, if you do not commit and end the session directly, the login record does not exist.

Second, an example of using temporary tables in Oracle storage

Description: When you borrow books, you need to split the books into detailed documents, split according to the Billcodes (several comma-separated document numbers) field in the Book table, which is used to hold the split document information. Returns the data for the temporary table directly after the split has ended. SQL code    create or replace package ams_pkg as       type refcursortype is ref cursor;      procedure split_ volumes  (p_corp_name in varchar2,p_year in varchar2,p_month in varchar2,p_ Vol_type_code in varchar2,p_bill_num in varchar2,p_volume_num in varchar2,p_area _codes in varchar2,p_query_sql out varchar2,p_outcursor out refcursortype);    end ams_pkg;  /   create or replace package body  "Ams_pkg"  as     procedure split_volumes (p_corp_name       in   varchar2,         --query conditions, company name                               p_YEAR           IN    varchar2,         --query criteria, fiscal year                                p_MONTH          IN    varchar2,         --query conditions, period                                p_VOL_TYPE_CODE  IN   varchar2,          --query criteria, voucher category Coding                               p_bill_num        in   varchar2,         -- Enquiry criteria, information number                               p_VOLUME_NUM      in   varchar2,         --query conditions, book number                               p_AREA_CODES     IN    varchar2,         --query conditions, the region is encoded (the area of the production volume), comma split.                                                                         --forms such as   ' 12c01 ', ' 12201 ', ' 12d01 ', ' 12E01 ', ' 12601 ', stored procedures will be used in the way to filter                               p_QUERY_SQL     out    varchar2,         --return query string                                p_OutCursor      out   refcursortype --return value                               )  is           V_SQL   VARCHAR2 (3000);       v_sql_WHERE    VARCHAR2 (3000);       v_temp1   varchar2 (+);        V_TEMP2   VARCHAR2 (;  )     v_tempbillcodes  VARCHAR2 (3000