Oracle_pl/sql (3) Cursors

Introduction: Plsql Data types
Scalar data types: Numeric class, character class, Date class, Boolean Class (Boolean),
Composite data types: Records (%rowtype), tables, arrays
Reference type: REF CURSOR
LOB type: BLOB, CLOB

1. System-Defined records:%rowtype
Use the%rowtype property to define a record variable: You can define a record variable based on a table or view
When you use the%rowtype property to define a record variable, the number of members, the name, the type, and the number of table or view columns
Name, exactly the same type.

1.1 Using PL/SQL records in a SELECT statement
Declare
V_emp Emp%rowtype;
Begin
SELECT * into V_emp from EMP where empno=7788;
Dbms_output.put_line (V_emp.ename);
End

1.2 Using PL/SQL records in INSERT statements
Declare
Dept_record Dept%rowtype;
Begin
dept_record.deptno:=50;
dept_record.dname:= ' Administrator ';
dept_record.loc:= ' Beijing ';
INSERT INTO dept values Dept_record;
End

1.3 Using a record member in the INSERT statement in the VALUES clause
Declare
Dept_record Dept%rowtype;
Begin
dept_record.deptno:=60;
dept_record.dname:= ' Sales ';
INSERT INTO Dept (deptno,dname) values
(Dept_record.deptno,dept_record.dname);
End

1.4 Using PL/SQL records in an UPDATE statement
Using record variables in the SET clause
Declare
Dept_record Dept%rowtype;
Begin
dept_record.deptno:=60;
dept_record.dname:= ' Sales ';
dept_record.loc:= ' Shanghai ';
Update Dept set Row=dept_record where deptno=60;
End

Using a record member in a SET clause
Declare
Dept_record Dept%rowtype;
Begin
dept_record.loc:= ' Guangzhou ';
Update Dept set loc=dept_record.loc where deptno=10;
End

1.5 uses PL/SQL records in a DELETE statement: You can only use a record member in the WHERE clause in the DELETE statement
declare
Dept_record dept%rowtype;
Begin
Dept_record.deptno:=50;
Delete from dept where Deptno=dept_record.deptno;
End

2. User-defined record
defines the type first, declaring the variable of that type.
2.1 Define record type
Declare
type Emp_record_type is record (
name Emp.ename%type,
Salary Emp.sal%type,
Dno E Mp.deptno%type);
begin
Null;
End;

Syntax:
The type record is the name of the (
property name data type,
...
);
2.2 Defines the record type variable
Emp_record emp_record_type;
Syntax:
variable name record type name;
Declare
--Custom type Emp_record_type
Type Emp_record_type is record (
ename emp.ename%type,
Sal Emp.sal%type);
--Define the variable, the type of the variable is emp_record_type
Emp_record emp_record_type;
Begin
--variable assignment
Select Ename,sal to Emp_record from EMP where empno=&no;
Dbms_output.put_line (Emp_record. ename| | ' ' | | Emp_record.sal);
End;

2.3 Using custom records in a SELECT INTO statement
--set Serveroutput on
Declare
Type Emp_record_type is record (
Name Emp.ename%type,
Salary Emp.sal%type,
DNO emp.deptno%type);
Emp_record Emp_record_type;
Begin
Select Ename,sal,deptno to Emp_record from EMP where empno=&no;
Dbms_output.put_line (Emp_record.name);
End

2.4 Using a record member in a SELECT INTO statement
Declare
Type Emp_record_type is record (
Name Emp.ename%type,
Salary Emp.sal%type,
DNA emp.deptno%type);
Emp_record Emp_record_type;
Begin
Select Ename,sal into Emp_record.name,emp_record.salary
From EMP where empno=&no;
Dbms_output.put_line (Emp_record.name);
End

3. Cursors
When you execute a query statement select and data manipulation statement DML in a PL/SQL block,
Oracle assigns it a context area, which is a pointer to the context area.
For data manipulation statements and single-line SELECT INTO statements, Oracle assigns them an implied cursor.
Use display cursors to process multiple rows of data.
3.1 Displaying cursor operations
3.1.1 Defining cursors
Syntax: Cursor cursor_name is sub-query;
Example 1:
Declare
Cursor Cursor_emp is a SELECT * from EMP;
Begin
Dbms_output.put_line (' defines a cursor ');
End

3.1.2 Open a cursor:
Executes the corresponding SELECT statement and temporarily stores the result of the SELECT statement in the result set.
Syntax: Open cursor_name;
Example 2:
Declare
Cursor Cursor_emp is a SELECT * from EMP;
Begin
Open cursor_emp;
Dbms_output.put_line (' Open a cursor ');
End

3.1.3 Extracting data
When the cursor is opened, the result of the SELECT statement is temporarily stored in the cursor result set.
Only one row of data can be fetched using a FETCH statement
Syntax: Fetch cursor_name into Variable1,varibale2,...;
Example 3:
Declare
Cursor Cursor_emp is a SELECT * from EMP;
V_emp Emp%rowtype;
V_counter number:=1;
Begin
Open cursor_emp;
Loop
Fetch cursor_emp into v_emp;
Dbms_output.put_line (' fetch a row of data ' | | V_emp.ename);
v_counter:=v_counter+1;
If V_counter>20 Then
Exit
End If;
End Loop;
End

3.1.4 Closing Cursors
Syntax: Close cursor_name;
Example 4:
Declare
Cursor Cursor_emp is a SELECT * from EMP;
V_emp Emp%rowtype;
V_counter number:=1;
Begin
Open cursor_emp;
Loop
Fetch cursor_emp into v_emp;
Dbms_output.put_line (' fetch a row of data ' | | V_emp.ename);
v_counter:=v_counter+1;
If V_counter>20 Then
Exit
End If;
End Loop;
Close cursor_emp;
End

3.2 Displaying cursor properties
Used to return execution information for a display cursor, including%isopen,%found,%notfound,%rowcount
3.2.1%isopen: Determines whether the cursor is open
Grammar:
If Cl%isopen Then
...
Else
Open C1;
End If;
Example 5:
Declare
Cursor Cursor_emp is a SELECT * from EMP;
V_emp Emp%rowtype;
V_counter number:=1;
Begin
If Cursor_emp%isopen Then
Null
Else
Open cursor_emp;
End If;
Loop
Fetch cursor_emp into v_emp;
Dbms_output.put_line (' fetch a row of data ' | | V_emp.ename);
v_counter:=v_counter+1;
If V_counter>20 Then
Exit
End If;
End Loop;
Close cursor_emp;
End

3.2.2%found: Checks whether data is extracted from the result set
Grammar:
Loop
Fetch C1 into VAR1,VAR2;
If C1%found then ... else exit;
End Loop;
Example 6:
Declare
Cursor Cursor_emp is a SELECT * from EMP;
V_emp Emp%rowtype;
Begin
Open cursor_emp;
Loop
Fetch cursor_emp into v_emp;
If Cursor_emp%found Then
Dbms_output.put_line (' fetch a row of data ' | | V_emp.ename);
Else
Exit
End If;
End Loop;
Close cursor_emp;
End

3.2.3%notfound Check whether data is not fetched from the result set
Grammar:
Loop
Fetch C1 into VAR1,VAR2;
Exit when C1%notfound;
...
End Loop;
Example 7:
Declare
Cursor Cursor_emp is a SELECT * from EMP;
V_emp Emp%rowtype;
Begin
Open cursor_emp;
Loop
Fetch cursor_emp into v_emp;
Exit when Cursor_emp%notfound;
Dbms_output.put_line (' fetch a row of data ' | | V_emp.ename);
End Loop;
Close cursor_emp;
End

3.2.4%rowcount: Returns the actual number of rows that have been fetched to the current behavior
Grammar:
Loop
V_1:=cursor_name%rowcount;
End Loop;

Example 8:
Declare
Cursor cursor_emp is select * from emp;
V_emp Emp%rowtype;
Begin
If not Cursor_emp%isopen then
Open cursor_emp;
End If;
Loop
Fetch cursor_emp into v_emp;
E XIT when Cursor_emp%notfound;
Dbms_output.put_line (' | | | cursor_emp%rowcount| | ' An employee: '
| | V_emp.ename);
End Loop;
Close cursor_emp;
End;
Example 9:
Declare
Cursor cursor_emp is select * from emp;
V_emp Emp%rowtype;
Begin
If not Cursor_emp%isopen then
Open cursor_emp;
End If;
Loop
Fetch cursor_emp into v_emp;
E XIT when Cursor_emp%notfound;
Dbms_output.put_line (' fetch a row of data ' | | V_emp.ename);
End Loop;
Dbms_output.put_line (' Total rows of cursors ' | | Cursor_emp%rowcount);
Close cursor_emp;
End;

3.3 Defining record variables based on cursors
Example 10:
Declare
Cursor Emp_cursor is a select ename,sal from EMP;
Emp_record Emp_cursor%rowtype;
Begin
Open emp_cursor;
Loop
Fetch emp_cursor into Emp_record;
Exit when Emp_cursor%notfound;
Dbms_output.put_line (' Employee Name: ' | | Emp_record.ename
| | ', employee wage: ' | | Emp_record.sal);
End Loop;
Close emp_cursor;
End

3.4 Parameter Cursors
When defining a parameter cursor, the cursor parameter can specify only the data type, not the length.
Syntax: Cursor cursor_name (parameter_name datatype) is select_statment;
Example 11:
Declare
Cursor Emp_cursor (no number) is a select ename from emp where deptno=no;
V_ename Emp.ename%type;
Begin
Open Emp_cursor (&no);
Loop
Fetch emp_cursor into v_ename;
Exit when Emp_cursor%notfound;
Dbms_output.put_line (V_ename);
End Loop;
Close emp_cursor;
End
Example 11-2: Character parameters
Declare
Cursor Emp_cursor (p_job varchar2) is a select ename from emp where job=p_job;
V_ename Emp.ename%type;
Begin
Open Emp_cursor (' &p_job ');
Loop
Fetch emp_cursor into v_ename;
Exit when Emp_cursor%notfound;
Dbms_output.put_line (V_ename);
End Loop;
Close emp_cursor;
End
Example 11-3: two parameters
Declare
Cursor Emp_cursor (p_no number,p_job varchar2) is
Select ename from emp where Deptno=p_no and job=p_job;
V_ename Emp.ename%type;
Begin
Open Emp_cursor (&p_no, ' &p_job ');
Loop
Fetch emp_cursor into v_ename;
Exit when Emp_cursor%notfound;
Dbms_output.put_line (V_ename);
End Loop;
Close emp_cursor;
End
--30 salesman

Begin
For RS in (select Ename from emp where deptno=&no and job= ' &job ') loop
Dbms_output.put_line (Rs.ename);
End Loop;
End

3.5 Updating or deleting data using cursors
To update or delete data through a cursor, you must have a FOR UPDATE clause when you define the cursor.
Grammar:
Cursor cursor_name (parameter_name datetype) is
select_statement for update;
The FOR UPDATE clause is used to lock the data on the cursor result set to prevent other users from performing DML operations on the corresponding rows.
When you use cursors to update or delete data,
The WHERE CURRENT OF clause can be referenced in the DELETE statement after the update.
Grammar:
UPDATE table_name SET Column name = value where CURRENT of cursor_name;
Delete table_name where CURRENT OF cursor_name;
3.5.1 Updating data using cursors
Example 12:
Declare
Cursor Emp_cursor is a select ename,sal from EMP for update;
V_ename Emp.ename%type;
V_sal Emp.sal%type;
Begin
Open emp_cursor;
Loop
Fetch emp_cursor into v_ename,v_sal;
Exit when Emp_cursor%notfound;
If v_sal<2000 Then
Update EMP Set sal=sal+100 where CURRENT of emp_cursor;
Dbms_output.put_line (v_ename| | ' Increase in wages by 100 ');
End If;
End Loop;
Close emp_cursor;
End
Example 12-2:
Declare
Cursor Emp_cursor is a select ename,sal from EMP;
V_ename Emp.ename%type;
V_sal Emp.sal%type;
Begin
Open emp_cursor;
Loop
Fetch emp_cursor into v_ename,v_sal;
Exit when Emp_cursor%notfound;
If v_sal<2000 Then
Update emp set sal=sal+100 where Ename=v_ename;
Dbms_output.put_line (v_ename| | ' Increase in wages by 100 ');
End If;
End Loop;
Close emp_cursor;
End

3.5.2 using cursors to delete data
Example:
Declare
Cursor emp_cursor is select Ename,sal,deptno from emp for update;
V_ename EMP . Ename%type;
V_sal Emp.sal%type;
V_deptno Emp.deptno%type;
Begin
Open emp_cursor;
Loop
Fetch emp_cursor to V_ename,v_sal,v_deptno;
Exit when emp_cursor% NotFound
If v_deptno=30 then
Delete the from EMP where current of emp_cursor;
Dbms_output.put_line (v_ename| | ' was removed ');
End If;
End Loop;
Close emp_cursor;
End;
Example 13-2:
Declare
Cursor Emp_cursor is a select Ename,sal,deptno from emp;
V_ename emp.ename%type;
v_sal em P.sal%type;
V_deptno Emp.deptno%type;
Begin
Open emp_cursor;
Loop
Fetch emp_cursor to V_ename,v_sal,v_deptno;
Exit when emp_cursor% NotFound
If v_deptno=30 then
Delete the from emp where ename=v_ename;
Dbms_output.put_line (v_ename| | ' was removed ');
End If;
End Loop;
Close emp_cursor;
End;

3.6 Cursor FOR loop
When using a For loop, Oracle implicitly opens the cursor, extracts the cursor data, and closes the cursor
Grammar:
For Record_name in Cursor_name Loop
Statement1;
Statement2;
...
End Loop;
Extracts data once per loop, automatically exits the loop and implicitly closes the cursor after all data has been extracted
3.6.1 using a cursor for loop
Example 14:
Declare
Cursor Emp_cursor is a select ename,sal from EMP;
Begin
For Emp_record in Emp_cursor loop
Dbms_output.put_line (' The ' | | emp_cursor%rowcount| | ' An employee: '
|| Emp_record.ename);
End Loop;
End

3.6.2 using subqueries directly in the cursor for loop
Example 15:
Declare
I number:=1;
Begin
For RS in (select Ename,sal from emp) loop
Dbms_output.put_line (' The ' | | i| | ' Employees: ' | | Rs.ename);
i:=i+1;
End Loop;
End

3.7 Using cursor variables
A cursor variable in PL/SQL holds a pointer to a memory address.
3.7.1 cursor Variable use steps
Four stages including defining cursor variables, opening cursors, extracting cursor data, closing cursors, etc.
1) define REF CURSOR type and cursor variable
Type ref_type_name is REF CURSOR [return return_type];
Cursor_varibale Ref_type_name;
When a return clause is specified, its data type must be a record type and the cursor variable cannot be defined within the package
2) Open cursor
Open cursor_variable for select_statement;
3) Extracting cursor data
Fetch cursor_varibale into Variable1,variable2,...;
4) Close cursor variable
Close Cursor_varibale;

3.7.2 cursor Variable Use example
1) Do not specify a return clause when defining a REF CURSOR type
Any SELECT statement can be specified when the cursor is opened
Example 16:
Declare
Type emp_cursor_type is REF CURSOR;
Emp_cursor Emp_cursor_type;
Emp_record Emp%rowtype;
Begin
Open Emp_cursor for SELECT * from EMP where deptno=10;
Loop
Fetch emp_cursor into Emp_record;
Exit when Emp_cursor%notfound;
Dbms_output.put_line (' The ' | | emp_cursor%rowcount| | ' Employees: ' | | Emp_record.ename);
End Loop;
Close emp_cursor;
End

2) Specify a return clause when defining a REF CURSOR type
When you open a cursor, the return result of the SELECT statement must match the record type specified by the return clause.
Example:
Declare
Type Emp_record_type is record (ename varchar2), salary number (6,2));
Type Emp_cursor_type is REF CURSOR return emp_record_type;
Emp_cursor Emp_cursor_type;
Emp_record Emp_record_type;
Begin
Open emp_cursor for select ename,sal from emp where deptno=20;
Loop
Fetch emp_cursor into Emp_record ;
Exit when Emp_cursor%notfound;
Dbms_output.put_line (' | | | emp_cursor%rowcount| | ' An employee: '
| | Emp_record.ename);
End Loop;
Close emp_cursor;
End;

3.8 Using the cursor expression
cursor expression to return a nested cursor
result set can contain not only normal data, but also data containing nested cursors
Syntax: cursor (subquery)
Example:
Declare
type refcursor is REF CURSOR;
Cursor dept_cursor (no number) is
Select A.dname,
cursor (select Enam E,sal from EMP where Deptno=a.deptno)
from dept a where A.deptno=no;
Empcur refcursor;
V_dname Dept.dname%typ E
V_ename Emp.ename%type;
V_sal Emp.sal%type;
Begin
Open Dept_cursor (&no),
Loop
Fetch dept_cursor into V_dname,empcur,
exit when dept_cursor% NotFound
Dbms_output.put_line (' Department Name: ' | | V_dname);
Loop
Fetch empcur into V_ename,v_sal,
exit when Empcur%notfound;
Dbms_output.put_line (' Employee Name: ' | | v_ename| | ', wages: ' | | V_sal);
End Loop;
End Loop;
Close dept_cursor;
End;

Example 19:
Begin
For RS in (select Dname,deptno from dept where Deptno=&no) loop
Dbms_output.put_line (' Department Name: ' | | Rs.dname);
For rs2 in (select Ename,sal from emp where deptno=rs.deptno) loop
Dbms_output.put_line (' Employee Name: ' | | rs2.ename| | ', wages: ' | | Rs2.sal);
End Loop;
End Loop;
End

Oracle_pl/sql (3) Cursors