Oracle cursors, temporary table usage exercises

1. Create a session-level staging table:

<span style= "FONT-SIZE:14PX;" >create Global temporary table pact_test (   pact_id varchar2 (+),   pact_code varchar2 (+),   pact_name VARCHAR2 (+)) on commit preserve rows;</span>

2. Create a primary table and insert the data:

<span style= "FONT-SIZE:14PX;" >create table Pact_baseinfo (  pact_id           VARCHAR2 () not NULL,  Pact_code         VARCHAR2 () is not NULL,  Pact_name         VARCHAR2 (+) NOT null) </span>

Insert data:

<span style= "FONT-SIZE:14PX;" >prompt Importing Table Pact_test...set feedback offset define Offinsert into Pact_test (pact_id, Pact_code, Pact_name) VALUES (' n ', ' Zd0zb ', ' Sky Wing Telecom '); INSERT into Pact_test (pact_id, Pact_code, Pact_name) VALUES (' n ', ' hisgs ', ' Hainan Telecom '); in Sert into Pact_test (pact_id, Pact_code, Pact_name) VALUES (' All ', ' jssgs ', ' Jiangsu Telecom '); INSERT into Pact_test (pact_id, Pact_c ODE, Pact_name) VALUES (' n ', ' cu12-', ' China unicom Gansu Business '); insert into Pact_test (pact_id, Pact_code, Pact_name) VALUES (' 2014 ', ' Ynsgs ', ' China Telecom Yunnan Company ');p rompt done.</span>

3. Write a cursor query and insert

<span style= "FONT-SIZE:14PX;" >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 <v_cnt;  v_pact_id Pact_baseinfo.pact_id%type;  V_pact_code Pact_baseinfo.pact_code%type;  V_pact_name pact_baseinfo.pact_name%type;begin  open c_pact;  Loop    fetch c_pact into v_pact_id,v_pact_code,v_pact_name;    Exit when C_pact%notfound;    INSERT INTO  pact_test (pact_id,pact_code,pact_name)    values (v_pact_id,v_pact_code,v_pact_name);    commit;  End Loop;  Close c_pact;end;</span>

4. Verify that the data is inserted in the staging table:

<span style= "FONT-SIZE:14PX;" >select * from pact_test</span>

5. Summary:

1) Temporary tables are divided into two types:

Session-Level temp table : The data in this temporary table is related to your current session, and the data in the temporary table is still present when you do not exit, and when you exit the current session, the data in the temporary table is all gone.

transaction-level temporary tables : When a transaction is committed or a transaction is rolled back, the data in the temporary table is truncated itself, and the other content is consistent with the session-level temporal table (including when the session is exited, the transaction-level temporary table is automatically truncated)

This experiment uses a session-level temporal table, which records how the transaction-level temporary tables are built:

<span style= "FONT-SIZE:14PX;" >create Global temporary table pact_test (   pact_id varchar2 (+),   pact_code varchar2 (+),   pact_name VARCHAR2 (+)) on commit delete rows;</span>

2) Use of cursors:

(1) Cursors in Oracle are divided into explicit and implicit cursors:
Explicit cursor: A cursor defined with the cursor...is command that can handle multiple records returned by a SELECT statement.
Implicit cursors: When you perform an INSERT, delete, UPDATE, and a query that returns a single record, the SELECT INTO statement is automatically defined by PL/SQL, and an implicit cursor is also called a SQL cursor.


(2)Oracle explicit cursors are divided into normal cursors, parameter cursors, and cursor variables.
Common cursors and parameter cursors are also known as static cursors, and cursor variables are called dynamic cursors.


Generally speaking, the cursor refers to an explicit cursor, because the implicit is not controlled by the operator, can only get his properties, this article first describes the most commonly used explicit cursors, implicit cursors in the final summary.


(3)General use steps for explicit cursors
Defining cursors: Cursor cursor_name is select_sql; (Note that the cursor definition can only be used to make the keyword is, which is not common with AS)
Opening cursors: Open cursor_name;
Extract data:
Syntax 1 fetch cursor_name into Variable1[,variable2,...];
In which, fetch into can only fetch one row of data at a time, the batch data need to use the loop; variable specifies the variable that receives the cursor data, Select_sql has several fields, and there are several variable.
Syntax 2 FETCH cursor_name bulk collect into Collect1,collect2,... [Limit rows];
Where collect specifies a collection variable that receives cursor results, which is supported after 9i and consumes memory.


To display a cursor (four properties of a cursor):
%isopen: Detects if the cursor is open and returns True if it is already open.
%found: Detects whether the cursor result set has data and returns true. Returns NULL if no fetch propulsion is used after the cursor is opened.
%notfound: Detects whether the cursor result set does not exist and returns true.
%rowcount: Returns the actual number of rows that have been fetched until the current row.

Close cursor: close cursor_name;

Http://blog.csdn.net/yuzongtao

Oracle cursors, temporary table usage exercises