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;