Oracle Cursors (cursors)



watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvdgfuz2xpdxfpbmc=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">

Table, select statement, cursor: The return result can be a collection.

Note: The result of the cursor is a collection.

--Query and print employee's name and salary set Serveroutput on/* cursor: 1. Cursor properties:%isopen    %rowcount (number of rows returned)              %notfound  %found2. By default. Open 300 cursors at once sql> show parameter  cursorname                                 TYPE        VALUE--------------------------------------------- -----------------cursor_sharing                       string      exactcursor_space_for_time                boolean     falseopen_cursors                         integer     300session_cached_cursors               integer     20 change cursor: Alter system set Open_cursors=400;*/declare  --Defines a cursor  cemp is a select ename,sal from emp;  Pename emp.ename%type; reference variable  psal   emp.sal%type; reference variable begin  open cemp;  Loop    -Take a record    fetch cemp into pename,psal;    --Exit: Fetch is not taken to    exit when Cemp%notfound;        Dbms_output.put_line (pename| | ' The salary is ' | | PSAL);    End Loop;  Close cemp;end;/

--Query the employee name of a department set serveroutput ondeclare  cursor cemp (DNO number) is a select ename from emp where Deptno=dno;  Pename emp.ename%type;begin  Open Cemp ();  Loop    fetch cemp into pename;    Exit when Cemp%notfound;        Dbms_output.put_line (pename);  End Loop;  Close cemp;end;/

/*sql Statement Select To_char (hiredate, ' YYYY ') from emp--> cursor---loop--Exit condition Variable: count80 number: = 0;count81 Number: = 0;c Ount82 Number: = 0;count87 Number: = 0;*/set serveroutput ondeclare cursor cemp is select To_char (hiredate, ' YYYY ') from E  mp    Phiredate VARCHAR2 (4);  Count80 number: = 0;  Count81 number: = 0;  count82 number: = 0;  count87 Number: = 0;begin open cemp;    Loop-fetch cemp into phiredate the year of an employee;        Exit when Cemp%notfound;      --inferred year if phiredate = ' 1980 ' then count80: = count80+1;      elsif phiredate = ' 1981 ' then count81: = count81+1;      elsif phiredate = ' 1982 ' then count82: = count82+1;    else count87: = count87 + 1;  End If;  End Loop;    Close Cemp; --Output Dbms_output.put_line (' total: ' | |  (count80+count81+count82+count87)); Dbms_output.put_line (' 1980: ' | |  COUNT80); Dbms_output.put_line (' 1981: ' | |  COUNT81); Dbms_output.put_line (' 1982: ' | |  COUNT82); Dbms_output.put_line (' 1987: ' | | count87); end;/

/*sql statement: Select Empno,sal from emp ORDER by sal--> Cursor-and loop-out condition: 1. Total > 5w 2.                All the people up the variable: the number of wages: countemp numbers: = 0; The total wage after rise: saltotal;                1. Select sum (SAL) into the saltotal from EMP;  2. After the rise = before the rise + Sal * 0.1 Exercise: Payroll cannot exceed 5w */set serveroutput ondeclare cursor cemp is select Empno,sal from emp  Order BY Sal;  Pempno Emp.empno%type;  Psal Emp.sal%type;    -Salary Increase: countemp number: = 0;  --The gross salary after the rise: saltotal Number;begin--Get the wage initial value select SUM (SAL) into the saltotal from EMP;  Open cemp; Loop--1.    Total > 5w exit when Saltotal > 50000;    --Take an employee fetch cemp into pempno,psal; --2.        All the people rose after exit when Cemp%notfound;    --Raise the wage update EMP set sal=sal*1.1 where Empno=pempno;    --Number +1 countemp: = Countemp +1;  --Total Saltotal: = saltotal + psal * 0.1;  End Loop;    Close Cemp;  Commit Dbms_output.put_line (' Number of people: ' | |   countemp| | ' The total wage after the rise: ' | | Saltotal); end;/

/*sql Statement: Department: Select Deptno from Dept------loop----exit the salary of the employee in the condition department: Select Sal from emp where deptno=?? --and loop--and exit condition variables: Number of people per segment: Count1 numbers; Count2 number; COUNT3 number; Payroll of the Department: Saltotal Number;1. Cumulative 2. Select sum (SAL) into Saltotal from EMP where deptno=?
?
 */set serveroutput ondeclare-Department cursor cdept is select Deptno from dept;  Pdeptno Dept.deptno%type;  --Employee's salary in Department cursor cemp (DNO number) is a select Sal from EMP where Deptno=dno;    Psal Emp.sal%type; --Number of persons per segment: Count1 numbers; Count2 number;  COUNT3 number;  --the payroll of the Department: Saltotal Number;begin Open cdept;    Loop-Fetch a department fetch cdept into Pdeptno;    Exit when Cdept%notfound; --Initialization of count1:=0; count2:=0;    count3:=0;    --The total payroll of the Department select SUM (SAL) into the saltotal from EMP where Deptno=pdeptno;    -The salary of the employee in the department open Cemp (PDEPTNO);      Loop-Fetch an employee's salary by fetching Cemp into psal;            Exit when Cemp%notfound;        --Infer salary if psal < then count1:=count1+1;        elsif psal>=3000 and psal< 6000 then count2:=count2+1;      else count3:=count3+1;        End If;    End Loop;    Close Cemp;  --Save current department insert into MSG values (PDEPTNO,COUNT1,COUNT2,COUNT3,NVL (saltotal,0));  End Loop;  Close cdept;    Commit Dbms_output.put_line (' done '); end;/

Oracle Cursors (cursors)