Oracle cursor (cursor)

 

SQL code

-- Ref cursor

/*

Show multiple rows of a cursor

Implicit cursor data manipulation language DML and single-line select into statement

1. Define a cursor

Select result set statement corresponding to the cursor

CURSOR cursor_name IS select_statement;

Select_statement: query statement

2. Open the cursor

OPEN cursor_name;

Execute the select_statement corresponding to the cursor and temporarily store the result in the cursor result set.

3. extract data

Use the fetch statement to extract cursor data. fetch can extract only one row at a time. fetch .. bulk collect into can extract multiple rows.

FETCH cursor_name into variable ...;

Variable that accepts cursor data

FETCH cursor_name bulk collect into collection ...;

Collection variable for receiving cursor data

4. Close the cursor

Extract data, close the cursor, and release the result set.

CLOSE cursor_name;

* Cursor attributes

Cursor_name % ISOPEN cursor Enabled

After the cursor_name % found fetch statement is executed, it is used to determine whether data is extracted.

Cursor_name % NOTFOUND same as above

Cursor_name % ROWCOUNT number of rows already fetch

*/

-- Implicit cursor

DECLARE

V_name varchar2 (30 );

BEGIN

Select ename into v_name from emp where empno = 7788;

CASE

When SQL % FOUND THEN

Dbms_output.put_line ('rows of invisible cursors must be '| SQL % ROWCOUNT |', query result: '| v_name );

-- The rows of the stealth cursor must be: 1. the query result is SCOTT.

End case;

END;

 

-- Display cursor

 

-- Fetch: extract a row of data

DECLARE

CURSOR emp_cs is select ename, job from emp; -- declare CURSOR

V_name emp. ename % type;

V_job emp. job % type;

BEGIN

OPEN emp_cs;

LOOP -- Because fetch only extracts one piece of data, it is extracted multiple times in a LOOP.

FETCH emp_cs INTO v_name, v_job;

Exit when emp_cs % NOTFOUND; -- EXIT the loop WHEN there is no data in the cursor

Dbms_output.put_line (emp_cs % ROWCOUNT | '--' | v_name | '--' | v_job );

End loop;

CLOSE emp_cs;

END;

 

-- FETCH... bulk collection to extract multiple rows of data

DECLARE

-- Declare the collection type for storing multi-row data

TYPE v_table_type is table of VARCHAR2 (30 );

Name_table v_table_type;

Job_table v_table_type;

CURSOR emp_cs IS select ename, job from emp;

BEGIN

OPEN emp_cs;

FETCH emp_cs bulk collect into name_table, job_table; -- two nested table types do not need to be initialized

FOR I IN 1 .. name_table.count LOOP

Dbms_output.put_line (name_table (I) | '--' | job_table (I ));

End loop;

CLOSE emp_cs;

END;

 

-- Extract by page. A fixed number of entries are extracted each time.

DECLARE

Type v_table_type is table of int;

No_tab v_table_type;

PageNo int: = 5; -- 5 entries per page

Nums int: = 0; -- number of output items

CURSOR emp_cs IS select empno from emp;

BEGIN

-- OPEN emp_cs;

If not emp_cs % ISOPEN THEN

OPEN emp_cs;

End if;

The LOOP-LIMIT statement limits the number of records requested each time, starting from the rowcount position of the previous cursor.

FETCH emp_cs bulk collect into no_tab LIMIT pageNo;

-- Print the obtained data (5 rows). Calculation Method on the Right Border: Total number of fetch rows for the cursor-number of output rows

For I IN 1 .. (emp_cs % ROWCOUNT-nums) LOOP

Dbms_output.put_line (no_tab (I ));

End loop;

Dbms_output.new_line;

Nums: = emp_cs % ROWCOUNT; -- reset the number of output items

/*

-- Put this sentence to the end, because we know that there are 14 records in the emp table, which can be divided into 3 pages and there are only 4 data records in the 3rd Fecth table,

-- At this time, it is NOTFOUND. If it is placed in front of the output, the last four will not be output.

*/

Exit when emp_cs % NOTFOUND;

End loop;

CLOSE emp_cs;

END;

 

 

-- Cursor record variable

DECLARE

CURSOR emp_cs IS select ename, job from emp;

V_record emp_cs % ROWTYPE; -- cursor row type definition record

BEGIN

OPEN emp_cs;

LOOP

FETCH emp_cs INTO v_record;

Exit when emp_cs % NOTFOUND;

Dbms_output.put_line (v_record.ename | '--' | v_record.job );

End loop;

CLOSE emp_cs;

END;

 

 

-- Parameter cursors are similar to functions that have different cursor data based on different parameter values.

-- The parameter is generally used in the select statement of the cursor, otherwise it will be meaningless.

/*

CURSOR cursor_name (parametr_name datatype...) IS select_statement;

* Parameters can only define types, but cannot define length,

*/

 

DECLARE

CURSOR emp_cs (param1 varchar2) IS select ename from emp where job = param1;

V_name emp. ename % type;

V_job varchar2 (30): = '& job ';

BEGIN

OPEN emp_cs (v_job );

LOOP

FETCH emp_cs INTO v_name;

Exit when emp_cs % NOTFOUND;

Dbms_output.put_line (v_name );

End loop;

CLOSE emp_cs;

END;

 

 

 

-- Update or delete data using a cursor

/*

A cursor not only queries data, but also modifies data. Because a cursor is an update of multiple data entries

To guarantee the atomicity of things, I do not know what to call it. It is to prevent the data rows in the cursor from being modified.

Other transactions must use the for update clause to modify these columns.

For example, if there are 5 data records in the cursor, you are modifying 3rd data records, and another transaction modifies 4th data records.

The cursor is the result set of the temporary cache, and the modification of 4 will not be seen. This is a mess.

CURSOR cursor_name (param datatype) IS

Select_statement for update [OF column_reference] [NOWAIT];

For update adds a row share lock to the rows in the cursor result set to prevent other users from

Execute the DML operation on the row.

*/

DECLARE

CURSOR emp_cs IS select comm from emp for update;

V_comm number (7,2 );

BEGIN

OPEN emp_cs;

LOOP

FETCH emp_cs INTO v_comm;

Exit when emp_cs % NOTFOUND;

IF v_comm is null THEN

UPDATE emp SET comm = 1 where current of emp_cs;

End if;

End loop;

CLOSE emp_cs;

END;

Commit;

 

/*

Cursor for Loop

This simplifies the processing of cursors. When a cursor for loop is used, oracle implicitly opens the cursor,

Extract data and close the cursor

*/

-- 1

DECLARE

CURSOR emp_cs IS select ename, job from emp;

BEGIN

FOR emp_record IN emp_cs LOOP -- emp_record is the implicitly defined record variable name.

Dbms_output.put_line (emp_cs % ROWCOUNT | '-' | emp_record.ename );

End loop;

END;

 

-- 2 in the cursor for loop, the select statement defines the cursor.

-- In this case, the cursor attribute cannot be used because there is no cursor name.

BEGIN

FOR emp_record IN (select ename, job from emp) LOOP

Dbms_output.put_line (emp_record.ename );

End loop;

END;

 

 

/*

Use cursor variable

When using the display cursor, you must specify the corresponding static select statement in the definition section;

When using a cursor variable, you can specify the select statement corresponding to the variable when opening the cursor variable.

Define cursor Variables

TYPE ref_type is ref cursor [RETURN return_type];

Cursor_var ref_type;

*/

-- Exp1

DECLARE

TYPE emp_cursor_type is ref cursor; -- defines the TYPE of a CURSOR variable.

Emp_cs emp_cursor_type; -- use this cursor type to define a cursor variable

Emp_record emp % ROWTYPE;

BEGIN

OPEN emp_cs FOR select * from emp; -- specifies the select statement corresponding to the cursor variable when it is opened.

LOOP

FETCH emp_cs INTO emp_record; -- the data is the same as the normal cursor.

Exit when emp_cs % NOTFOUND;

Dbms_output.put_line (emp_record.ename );

End loop;

CLOSE emp_cs;

END;

 

-- Exp2 defines that the cursor variable is the specified return type (intelligence is record)

DECLARE

TYPE v_record_type is record (

Name varchar2 (10), sal number

);

TYPE v_cursor_type is ref cursor return v_record_type; -- RETURN the record TYPE defined above

-- Declare a record variable and a cursor variable

V_record v_record_type;

V_cursor v_cursor_type;

BEGIN

OPEN v_cursor FOR select ename, sal from emp where deptno = 10;

LOOP

FETCH v_cursor INTO v_record;

Exit when v_cursor % NOTFOUND;

Dbms_output.put_line (v_record.name | '-' | v_record.sal );

End loop;

CLOSE v_cursor;

END;