Oracle cursor use full solution

This lt; Oracle cursor uses full-solution gt; the document covers almost all aspects of Oracle cursor use, all passed the test

This lt; Oracle cursor uses full-solution gt; the document covers almost all aspects of Oracle cursor use, all passed the test

This This document covers almost all aspects of Oracle cursor usage and has passed the test.

-- Declare the CURSOR; CURSOR cursor_name IS select_statement

-- For Loop cursor
-- (1) define a cursor
-- (2) define the cursor variable
-- (3) use the cursor for a for Loop
Declare
-- Type Definition
Cursor c_job
Is
Select empno, ename, job, sal
From emp
Where job = 'manager ';
-- Defines a cursor variable v_cinfo c_emp % ROWTYPE, which is a row of data type in the cursor c_emp.
C_row c_job % rowtype;
Begin
For c_row in c_job loop
Dbms_output.put_line (c_row.empno | '-' | c_row.ename | '-' | c_row.job | '-' | c_row.sal );
End loop;
End;

-- Fetch cursor
-- You must enable and disable it explicitly when using it.

Declare
-- Type Definition
Cursor c_job
Is
Select empno, ename, job, sal
From emp
Where job = 'manager ';
-- Define a cursor variable
C_row c_job % rowtype;
Begin
Open c_job;
Loop
-- Extract a row of data to c_row
Fetch c_job into c_row;
-- Determine whether to extract the value. Exit if the value is not obtained.
-- The value c_job % notfound is false.
-- The value c_job % notfound is not obtained. true.
Exit when c_job % notfound;
Dbms_output.put_line (c_row.empno | '-' | c_row.ename | '-' | c_row.job | '-' | c_row.sal );
End loop;
-- Close the cursor
Close c_job;
End;

-- 1: execute any update operation. Use the attributes % found, % notfound, % rowcount, and % isopen of the implicit cursor SQL statement to observe the execution of the update statement.
Begin
Update emp set ENAME = 'eark' where empno = 7469;
If SQL % isopen then
Dbms_output.put_line ('openging ');
Else
Dbms_output.put_line ('closing ');
End if;
If SQL % found then
Dbms_output.put_line ('cursor points to valid row'); -- determines whether the cursor points to a valid row.
Else
Dbms_output.put_line ('sorry ');
End if;
If SQL % notfound then
Dbms_output.put_line ('also sorry ');
Else
Dbms_output.put_line ('hahaha ');
End if;
Dbms_output.put_line (SQL % rowcount );
Exception
When no_data_found then
Dbms_output.put_line ('Sorry No data ');
When too_many_rows then
Dbms_output.put_line ('too writable rows ');
End;
Declare
EmpNumber emp. EMPNO % TYPE;
EmpName emp. ENAME % TYPE;
Begin
If SQL % isopen then
Dbms_output.put_line ('cursor is opinging ');
Else
Dbms_output.put_line ('cursor is close ');
End if;
If SQL % notfound then
Dbms_output.put_line ('no value ');
Else
Dbms_output.put_line (empNumber );
End if;
Dbms_output.put_line (SQL % rowcount );
Dbms_output.put_line ('-------------');

Select EMPNO, ENAME into empNumber, empName from emp where EMPNO = 7499;
Dbms_output.put_line (SQL % rowcount );

If SQL % isopen then
Dbms_output.put_line ('cursor is opinging ');
Else
Dbms_output.put_line ('cursor is closing ');
End if;
If SQL % notfound then
Dbms_output.put_line ('no value ');
Else
Dbms_output.put_line (empNumber );
End if;
Exception
When no_data_found then
Dbms_output.put_line ('no value ');
When too_many_rows then
Dbms_output.put_line ('too writable rows ');
End;



-- 2. The cursor and loop are used to display the names of all departments.
-- Cursor Declaration
Declare
Cursor csr_dept
Is
-- Select statement
Select DNAME
From Depth;
-- Specifies the row pointer. This statement should be a variable of the same type as the csr_dept row.
Row_dept csr_dept % rowtype;
Begin
-- For Loop
For row_dept in csr_dept loop
Dbms_output.put_line ('department name: '| row_dept.DNAME );
End loop;
End;