Use of Oracle stored procedures

I. Using a FOR loop cursor: Traverse all positions as manager's employees

1. Define a cursor (a cursor is a small collection)

2. Defining cursor Variables

3. Using a For loop cursor

Declare  --Define cursor c_job cursor  c_job is    select Empno, ename, Job, Sal from emp where job = ' MANAGER ';      ---Define cursor variable c_row  c_row c_job%rowtype;begin  -loop cursor, c_row the value of the loop with the cursor variable 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;

Two. Fetch cursors: traverse all positions as manager's employees

Use must be understood when opening and closing

Declare  --Define cursor c_job cursor  c_job is    select Empno, ename, Job, Sal from emp where job = ' MANAGER ';  --Define cursor variable c_row  c_row c_job%rowtype;begin  open c_job;  Loop    -Extracts a row of data to C_row    fetch c_job into C_row;        --the interpretation is extracted to a value. Exit when the value is not taken    c_job%notfound;    Dbms_output.put_line (C_row.empno | | '-' | | C_row.ename | | '-' | |                         C_row.job | | '-' | | C_row.sal);  End Loop;    --Closing the cursor close  c_job;end;

Three. Using cursors and while loops: Traverse the geographic location of all departments

--3, using cursors and while loops to show the geographic location of all departments (with the%found attribute) declare  --declares the cursor cursor  csr_testwhile is select loc from dept;  --Specify the line pointer  row_loc csr_testwhile%rowtype;begin  open csr_testwhile;  --to the first row of data  fetch csr_testwhile into Row_loc;    --Test if there is data, and run the loop while  Csr_testwhile%found loop    dbms_output.put_line (' Department location: ' | | row_loc. LOC);    --Give the next row of data    fetch csr_testwhile into Row_loc;  End Loop;  Close csr_testwhile;end;

Four. Cursor: The department number that accepts user input

Declare  --cursors cursor  c_dept (p_deptno number) is a    select * from emp where emp.deptno = P_deptno;      R_emp Emp%rowtype;begin for  r_emp in C_dept loop    dbms_output.put_line (' Employee number: ' | | r_emp. EMPNO | | ' Employee Name: '                          | | r_emp. ename | | ' Wages: ' | | R_emp. SAL);  End Loop;end;

Five. Lock cursor: Add commission to all salesman 500

Declare  --query data, locking (for update of)  cursor Csr_addcomm (p_job nvarchar2) are    select * from emp where job = P_job fo R update of Comm;  R_addcomm Emp%rowtype;  Comminfo  Emp.comm%type;begin for  R_addcomm in Csr_addcomm (' salesman ') loop    comminfo: = R_addcomm.comm + ;        --Update data (where current of)    update emp Set comm = comminfo where CURRENT of Csr_addcomm;  End Loop;end;

six. Use counters: Find two employees with the longest working hours

Declare  cursor Crs_testcomput is    SELECT * from emp ORDER by hiredate ASC;      --Counter  top_two number      : = 2;  R_testcomput crs_testcomput%rowtype;begin  open crs_testcomput;  Fetch crs_testcomput into r_testcomput;  While Top_two > 0 loop    dbms_output.put_line (' Employee Name: ' | | r_testcomput.ename | |                         ' working hours: ' | | r_ Testcomput.hiredate);    --Speed reducer minus 1    top_two: = top_two-1;    Fetch crs_testcomput into r_testcomput;  End Loop;  Close crs_testcomput;end;

Seven. If/else inference: A salary increase of 20% for all employees at a basic salary. If you add a salary greater than 300, you cancel the raise.

Declare  cursor crs_upadatesal is a    select * from emp for update of Sal;  R_updatesal Crs_upadatesal%rowtype;  Saladd      Emp.sal%type;  Salinfo     Emp.sal%type;begin for  r_updatesal in Crs_upadatesal loop    saladd: = r_updatesal.sal * 0.2;    If Saladd > Then      salinfo: = r_updatesal.sal;      Dbms_output.put_line (R_updatesal.ename | | ': The  pay rise failed. ' | |                           ' Salary maintained in: ' | | R_updatesal.sal);    else      Salinfo: = R_updatesal.sal + saladd;      Dbms_output.put_line (R_updatesal.ename | | ': The  pay rise is successful. ' | |                           ' Salary changed to: ' | | Salinfo);    End If;    Update emp Set sal = salinfo where CURRENT of crs_upadatesal;  End Loop;end;

Eight. When to use case: Pay by Department

Declare  cursor crs_casetest is a    select * from emp for update of Sal;  R_casetest Crs_casetest%rowtype;  Salinfo    Emp.sal%type;begin for  r_casetest in Crs_casetest loop case when      R_casetest.deptno = Ten Then        Salinfo: = r_casetest.sal * 1.05;      When r_casetest.deptno =        Salinfo: = r_casetest.sal * 1.1;      When r_casetest.deptno =        Salinfo: = r_casetest.sal * 1.15;      When r_casetest.deptno =        Salinfo: = r_casetest.sal * 1.2;    End case;    Update emp Set sal = salinfo where CURRENT of crs_casetest;  End Loop;end;

Nine. Exception Handling: Data rollback

Set Serveroutput on;declare  d_name varchar2; begin  D_name: = ' developer ';    SavePoint A;  INSERT into DEPT values (d_name, ' Beijing ');  SavePoint B;  INSERT into DEPT values (d_name, ' Shanghai ');  SavePoint C;    Exception when others then    dbms_output.put_line (' Error happens ');   Rollback to A;  commit;end;/

10. Basic instructions:

Set serveroutput on size 1000000 format wrapped; --Make dbms_output effective, and set to maximum buffer, prevent "eat" the front space set linesize 256; --Set the number of characters a row can hold set pagesize 50; --Set the number of rows on a page set ArraySize 5000; --Set the amount of data displayed back and forth, this value will affect autotrace when the consistency of reading data set newpage none; --the page is not set between pages, no matter what interval set long 5000; --long or CLOB Displays the length set trimspool on; --Spool The extra space behind each line in the output to remove set timing on; --Set query time consuming Col plan_plus_exp format a120; --autotrace after the explain plan output format set Termout off; --The contents of the output are not displayed on the screen and are prepared for the following SQL Setup alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss '; --Set the time format

Small Knowledge:

The following statement must be in command window to print out the content

Set Serveroutput on;begin dbms_output.put_line (' hello! '); end;/

Use of Oracle stored procedures