Oracle cursor and temporary table usage exercises

Oracle cursor and temporary table usage exercises

1. Create a session-level temporary table:

 

create global temporary table pact_test(   pact_id varchar2(100),   pact_code varchar2(100),   pact_name varchar2(800)) on commit preserve rows;

 

2. Create a master table and insert data:

 

create table PACT_BASEINFO(  pact_id           VARCHAR2(30) not null,  pact_code         VARCHAR2(150) not null,  pact_name         VARCHAR2(1000) not null )

 

Insert data:

 

Prompt Importing table pact_test... set feedback offset define offinsert into pact_test (PACT_ID, PACT_CODE, PACT_NAME) values ('2013', 'z0zb', 'tianyi Telecom '); insert into pact_test (PACT_ID, PACT_CODE, PACT_NAME) values ('200', 'hisg', 'hainan Telecom '); insert into pact_test (PACT_ID, PACT_CODE, PACT_NAME) values ('200', 'jssg ', 'jiangsu Telecom '); insert into pact_test (PACT_ID, PACT_CODE, PACT_NAME) values ('123', 'cu12-', 'China Unicom Gansu business '); insert into pact_test (PACT_ID, PACT_CODE, PACT_NAME) values ('000000', 'nsg', 'China Telecom Yunnan company '); prompt Done.

 

3. Write a cursor query and insert

 

declare  v_cnt number :=5;  cursor c_pact is select pact_id,pact_code,pact_name  from (select rownum rn,pact_id,pact_code,pact_name from pact_baseinfo) a where a.rn 
 

4. Verify whether data is inserted in the temporary table:

 

 

select * from pact_test

5. Conclusion:

 

1) temporary tables are divided into two types:

Session-level temporary table: The data in this temporary table is related to your current SESSION. When your current SESSION does not exit, the data in the temporary table still exists. When you exit the current SESSION, no data is in the temporary table.

Transaction-level temporary table: When a transaction is committed or rolled back, the data in the temporary table will be truncated by itself. The other content is consistent with the SESSION-level temporary table (including when the SESSION is exited, temporary tables at the transaction level will also be automatically truncated)

This experiment uses a session-level temporary table. The following describes how to create a transaction-level temporary table:

 

create global temporary table pact_test(   pact_id varchar2(100),   pact_code varchar2(100),   pact_name varchar2(800)) on commit delete rows;

2) Use of the cursor:

 

(1) cursors in Oracle are divided into explicit and implicit cursors:
Explicit cursor: A cursor defined by the cursor... is command. It can process multiple records returned by the select statement.
Implicit cursor: pl/SQL automatically defines the insert, delete, update, and select into statements that return a single record. Implicit cursor is also called an SQL cursor.


(2) Oracle explicit cursors include common cursors, parameter cursors, and cursor variables.
Common cursors and parameter cursors are also called static cursors, and cursor variables are called dynamic cursors.


Generally, a cursor refers to an explicit cursor. Because it is not implicitly controlled by the operator, only its attributes can be obtained. This article describes the most common explicit cursor, and the implicit cursor is briefly described at the end.


(3) general steps for using an explicit cursor
Define the cursor: cursor cursor_name is select_ SQL; (Note that the cursor can only be defined with the keyword IS, which is not universal with)
Open cursor: open cursor_name;
Extract data:
Syntax 1 fetch cursor_name into variable1 [, variable2,...];
Here, fetch into can only extract one row of data at a time, and loop is required for batch data. variable specifies the variable for receiving cursor data. There are several select_ SQL fields, and there are several variable.
Syntax 2 fetch cursor_name bulk collect into collect1, collect2,... [limit rows];
Among them, collect specifies the collection variable that receives the cursor result. This syntax is supported after 9i, and memory consumption is compared.


Display cursor (four attributes of the cursor ):
% Isopen: checks whether the cursor has been opened. If yes, true is returned.
% Found: checks whether the cursor result set has data. If yes, true is returned. If the cursor is not promoted using fetch after being opened, null is returned.
% Notfound: checks whether the cursor result set does not contain data. If no data exists, true is returned.
% Rowcount: returns the actual number of extracted rows until the current row.

Close cursor: close cursor_name;