Oracle Learning (11): PL/SQL

1. Knowledge Points: Can be read against the following record screen

PL/SQL Program structure declare description section (variable description, cursor declaration, exception Description)begin statement sequence (DML statement) ... exception exception handling statement end;/------------------ --------------------------------------------------the first PL/SQL program: Helloworldset serveroutput on--If you want to output information on the screen, The serveroutput switch needs to be opened declare--the description of the variable begin--The program body--Package Packages dbms_output.put_line (' Hello World '); end;/---------------  -----------------------------------------------------Reference variable: Query and print 7839 of the name and salary set Serveroutput ondeclare--Define variables, Pename emp.ename%type;--Sets the type of pename to the type of ename in the EMP table psal emp.sal%type;--Sets the type of psal to the type of Sal in the EMP table Begin-Query:--ename,sa    L into Pename,psal: assigns the ename,sal of the query to Pename,psal in order to assign a value of select Ename,sal into Pename,psal from EMP where empno=7839; --Print dbms_output.put_line (pename| | ' The salary is ' | | PSAL); end;/--------------------------------------------------------------------record type variable query and print 7839 name and salary set Serveroutput Ondeclare--Define a variable, represent a row Emp_rec emp%rowtype;begin--Get a row, assign a value to Emp_rec select * to Emp_rec from EMP where E  mpno=7839; dbms_output.put_lINE (emp_rec.ename| | ' The salary is ' | | emp_rec.sal); end;/--------------------------------------------------------------------If statement: Determining the number set for user input Serveroutput on/*1. Hint Information 2.  Receive keyboard input num is an address value for SQL optimization: NUM bound variable (use bound variable as far as possible) SELECT * from EMP where deptno=10;  --Execution Plan select * from EMP where deptno=20;  --Execution Plan-->select * from EMP where deptno=#*/accept num prompt ' Please enter a number ';d eclare--variable to save the input numbers pnum number  : = #begin--determine if Pnum = 0 Then Dbms_output.put_line (' You have entered 0 ');  elsif pnum = 1 Then dbms_output.put_line (' You have entered 1 ');  elsif pnum = 2 Then Dbms_output.put_line (' You have entered 2 ');  else Dbms_output.put_line (' other numbers ');    End If; end;/--------------------------------------------------------------------cycle: Print 1~10, 3 methods, see Set Serveroutput        Ondeclare pnum Number: = 1;--to Pnum assignment 1,pnum type Numberbegin loop--exit: Set up exit, do not set loop exit when Pnum > 10;        --implicit conversion, number turn varchar2 dbms_output.put_line (pnum);  Pnum: = Pnum + 1; End loop;end;/------------------------------------------------------------------cursor (CURSOR) ==resultset description cursor Syntax: cursor name [(parameter name data type [, parameter name data type] ...)]    is SELECT statement; used to store multiple rows of data returned by a query for example: Cursor C1 is a select ename from emp; open cursor: opening C1; (Open cursor Execution query) takes one line of cursor values: Fetch C1 into pjob; (take row to variable) close cursor: close C1; (Close cursor frees resources) Note: The above pjob must match the job column type in the EMP table: definition: pjob emp.empjob%type;--------------------------- -----------------------------------------cursor: Use a cursor to query the employee's name and salary and print the 3 properties of the/* cursor:%isopen is open: Turn on true to close the number of false;%rowcount rows  %notfound whether there is a value: No value of true, there is a value false;*/set serveroutput ondeclare-cursors cursor cemp is a select ename,sal from EMP; Pename emp.ename%type;--will be used to receive the variable type set to the same type as the query psal Emp.sal%type;begin open cemp;--opening cursor execution query loop-fetch value from the collection    Cemp into Pename,psal;        --Exit when the cursor does not have a value when cemp%notfound; Dbms_output.put_line (pename| | ' The salary is ' | |  PSAL);  End Loop; Close cemp;end;/--------------------------------------------------------------------cursor Exercise: Giving employees a raise in wages President 1000 Manager 800  The maximum number of other 400--cursors that can be opened by default is 300/*sql> show parameters Cursorname                               TYPE VALUE-------------------------------------------------------cursor_sharing                         String Exactcursor_space_for_time Boolean Falseopen_cursors Integer 300session_cached_cursors integer 20*/set serveroutput ondeclare--cursor represents employee cursor CEM  P is select Empno,job from EMP;  Pempno Emp.empno%type;  Pjob Emp.job%type;begin Open cemp;    Loop fetch cemp into pempno,pjob;        Exit when Cemp%notfound; --to determine if Pjob = ' president ' then update EMP set sal=sal+1000 where empno=pempno;--president rose elsif pjob = ' MANAGER ' th En update emp set sal=sal+800 where Empno=pempno;  --Manager up, else update emp set sal=sal+400 where empno=pempno;--other Rose end if;  End Loop;    Close Cemp;    --Commit: Isolation level commit; Dbms_output.put_line (' done '); end;/-------------------------------------------------------------------- Cursors with parameters: Querying the employee name of a department set serveroutput Ondeclare--Define a cursor with parameters  Cursor Cemp (pdno number) is a select ename from emp where Deptno=pdno;    Pename Emp.ename%type;begin Open Cemp (20);--Open the cursor and pass the parameters loop fetch cemp into pename;        Exit when Cemp%notfound;  Dbms_output.put_line (Pename);  End Loop; Close cemp;end;/------------------------------------------------------------------exception handling for Oracle 1. System Definition Exceptions No_data_ Found (no data found) too_many_rows (select ... into statement matches multiple rows) zero_divide (divide by 0) value_error (arithmetic or conversion error) Timeout_on_reso Urce (timed out while waiting for the resource)--example zero_divide (0 apart) set serveroutput ondeclare pnum number;begin pnum: = 1/0;exception when                        Zero_divide then Dbms_output.put_line (' 1:0 cannot do dividend ');  Dbms_output.put_line (' 2:0 cannot do dividend ');  When Value_error then Dbms_output.put_line (' arithmetic error '); When others and then Dbms_output.put_line (' other exceptions '); end;/user-defined exceptions-Custom exceptions: Query Employee Name set serveroutput ondeclare cursor in Department number 50th  Cemp is the select ename from emp where deptno=50;    Pename Emp.ename%type;  --Custom Exception no_emp_found Exception;begin open cemp; --Take an employee fetch cemp into pename;  If Cemp%notfound then raise No_emp_found;  End if;/* If Cemp%isopen then close no_emp_found;  End if;*/Close cemp;  Exception when No_emp_found then Dbms_output.put_line (' No employees found ');  When others and then Dbms_output.put_line (' other exceptions '); end;/

Figure: 3 Kinds of notation of the loop, the method of the upper right corner is adopted in the paper.

2. Actual execution of the results screen under Sqlplus

Sql>--the first PL/SQL program:helloworldsql> set Serveroutput on--if you want to output information on the screen, you need to open the serveroutput switch sql> declare 2--description of the variable  3 begin 4-Program Body 5-Package Packages 6 Dbms_output.put_line (' Hello world ');  7 End; 8/hello World PL/SQL procedure successfully completed sql> sql>--Reference variable: Query and print 7839 name and salary sql> set Serveroutput Onsql> declare 2--Define variables, 3 pename emp.ename%type;--set the type of pename to the type of ename in the EMP table 4 psal emp.sal%type;--will Psal     Type set to the type of Sal in the EMP table 5 6 begin 7 8--query: 9--ename,sal into Pename,psal: Assigns the ename,sal of the query to Pename,psal in order to assign a value of 10 Select Ename,sal to Pename,psal from EMP where empno=7839; 11 12--Print dbms_output.put_line (pename| | ' The salary is ' | | PSAL); The end; 16/king's salary is procedure successfully completed sql> sql>-record variable query and print 7839 name and salary sql> set Serveroutput  Onsql> declare 2--Define the variable, representing a row of 3 Emp_rec Emp%rowtype; 4 Begin 5--Get a row, assign value to Emp_rec 6 select * into Emp_rec from EMP where empno=7839; 7 8 dbms_output.put_line (emp_rec.ename| | ' The salary is ' | |  Emp_rec.sal); 9 End; 11/king's salary is procedure successfully completed sql> sql>--if statement: Determine the number of user input sql> set serveroutput onsql& Gt /* 2 1. Hint Information 3 2.  Receive keyboard input 4 num is an address value of 5 6 SQL optimized: NUM bound variable (use bound variable as far as possible) 7 SELECT * from EMP where deptno=10;  --Execution Plan 8 select * from EMP where deptno=20; --Execution Plan 9--and select * from EMP where deptno=# * * * Accept num prompt ' Please enter a number ';  Sql> declare 2--variable to save the input number 3 Pnum numbers: = #  4 Begin 5--Judge 6 if Pnum = 0 Then 7 dbms_output.put_line (' You have entered 0 '); 8 elsif pnum = 1 Then 9 dbms_output.put_line (' You have entered 1 '); Ten elsif pnum = 2 Then one dbms_output.put_line (' You have entered 2 '); else Dbms_output.put_line (' other numbers '); + End If; The end; 16/You entered 2 PL/SQL procedure successfully completed sql> sql>--loop: Print 1~10sql> set serveroutput onsql> declar E 2 pnum Number: = 1;--to PNUm assignment 1,pnum type number 3 begin 4 Loop 5--exit: Set up exit, not set loop 6 exit when Pnum > 10; 7 8--implicit conversion, number turn VARCHAR2 9 dbms_output.put_line (pnum); Ten Pnum: = Pnum + 1; The end loop; The end;   15/12345678910 PL/SQL procedure successfully completed sql> sql>-cursor: Use cursors to query employee names and wages, and print sql>/* 2 Cursor 3 properties: 3 %isopen Open: Turns true, turns off false; 4%rowcount rows 5%notfound whether there are values: No value True, there is a value of false; 6 */sql> set Serveroutput onsql& Gt  Declare 2--cursors 3 cursor cemp is select ename,sal from EMP;  4 Pename emp.ename%type;--will be used to receive the variable type set to the same type as the query 5 psal emp.sal%type; 6 begin 7 Open cemp;--cursor execution Query 8 loop 9-Fetch value from collection cemp into Pename,psal; 11--Exit when no value is in the cursor when cemp%notfound; Dbms_output.put_line (pename| | ' The salary is ' | | PSAL); The end loop; -Close cemp; The end; 19/ Smith's salary is 800ALLEN salary is 1600WARD salary is 1250JONES salary is 2975MARTIN salary is 1250BLAKE salary is 2850CLARK salary is 2450SCOTT salary is 3000KING salaryIs the salary of 5000TURNER is 1500ADAMS salary is 1100JAMES salary is 950FORD salary is 3000MILLER salary is 1300 PL/SQL procedure successfully completed >--cursor Exercise: Giving employees a raise in wages President 1000 Manager 800 other 400sql>--first check the sql> select * from EMP before the rise; EMPNO ename JOB MGR hiredate SAL COMM DEPTNO---------------------------------------------- ------------------7369 SMITH Clerk 7902 1980/12/17 800.00 7499 ALLEN salesman 769       8 1981/2/20 1600.00 300.00 7521 WARD salesman 7698 1981/2/22 1250.00 500.00-7566 JONES MANAGER 7839 1981/4/2 2975.00 7654 MARTIN salesman 7698 1981/9/28 1250.00 1400.      7698 BLAKE Manager 7839 1981/5/1 2850.00 7782 CLARK manager 7839 1981/6/9 2450.00 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 7839 KING presi DENT 1981/11/17 5000.00 7844 TURNER SALESMan 7698 1981/9/8 1500.00 0.00 7876 ADAMS Clerk 7788 1987/5/23 1100.00 20 7 7698 JAMES Clerk 1981/12/3 950.00 7902 FORD ANALYST 7566 1981/12/3 3000.0 0 7934 MILLER Clerk 7782 1982/1/23 1300.00 rows selected sql>-perform pls  QL Statement sql> sql> set Serveroutput onsql> declare 2--cursor represents employee 3 cursor cemp is select empno,job from EMP;  4 Pempno Emp.empno%type;  5 Pjob Emp.job%type;  6 begin 7 Open cemp; 8 Loop 9 fetch cemp into pempno,pjob; Ten exit when Cemp%notfound; 11 12--Judge if pjob = ' president ' then update EMP set sal=sal+1000 where empno=pempno;--president rose to El Sif pjob = ' MANAGER ' then update emp set sal=sal+800 where Empno=pempno; --manager up to the other update emp set sal=sal+400 where empno=pempno;--other up + + End if; + END loop; Close Cemp;    19 20--Commit: Isolation Level 21Commit Dbms_output.put_line (' done '); The end; 25/Completed PL/SQL procedure successfully completed sql>--query execution Plsql statement after salary sql> select * from EMP; EMPNO ename JOB MGR hiredate SAL COMM DEPTNO---------------------------------------------- ------------------7369 SMITH Clerk 7902 1980/12/17 1200.00 7499 ALLEN salesman 769       8 1981/2/20 2000.00 300.00 7521 WARD salesman 7698 1981/2/22 1650.00 500.00-7566 JONES MANAGER 7839 1981/4/2 3775.00 7654 MARTIN salesman 7698 1981/9/28 1650.00 1400.      7698 BLAKE Manager 7839 1981/5/1 3650.00 7782 CLARK manager 7839 1981/6/9 3250.00 7788 SCOTT ANALYST 7566 1987/4/19 3400.00 7839 KING presi DENT 1981/11/17 6000.00 7844 TURNER salesman 7698 1981/9/8 1900.00 0.XX 7876 ADAMS clerk 7788 1987/5/23 1500.00 7698 7900 JAMES Clerk 1981/12/3       1350.00 7902 FORD ANALYST 7566 1981/12/3 3400.00 7934 MILLER Clerk 7782 1982/1/23 1700.00 rows selected Sql>-Before the rise, you can see the rise in wages sql> sql>--Cursors with parameters: querying a Department Door Employee Name Sql> set Serveroutput onsql> declare 2--Define a cursor with parameters 3 cursor cemp (pdno number) is a select ename from E  MP where Deptno=pdno;  4 Pename Emp.ename%type;  5 begin 6 Open cemp (20);--open cursor and pass parameter 7 Loop 8 fetch cemp into pename; 9 Exit when Cemp%notfound; Ten dbms_output.put_line (Pename); The end loop; Close Cemp; The end; 17/smithjonesscottadamsford PL/SQL procedure successfully completed sql> sql>--Example Zero_divide (removed by 0) sql> SE  T Serveroutput onsql> declare 2 pnum number;  3 begin 4 5 pnum: = 1/0; 6 7 Exception 8 when Zero_divIDE then Dbms_output.put_line (' 1:0 cannot do dividend '); 9 Dbms_output.put_line (' 2:0 cannot do dividend '); Value_error then Dbms_output.put_line (' arithmetic error '); Others then Dbms_output.put_line (' other exceptions '); The end; 13/1:0 cannot be divisor 2:0 cannot do dividend PL/SQL procedure successfully completed sql> sql>--Custom Exception: Query number 50th Employee name sql> set server  Output onsql> Declare 2 cursor cemp is a select ename from emp where deptno=50;  3 Pename Emp.ename%type;  4 5--Custom Exception 6 No_emp_found exception;  7 begin 8 Open cemp; 9--Fetch an employee of cemp into Pename; If Cemp%notfound then raise No_emp_found; End If; Close Cemp; Exception No_emp_found then Dbms_output.put_line (' No employees found '); Others then Dbms_output.put_line (' other exceptions '); The end; 22/No Employees found PL/SQL procedure successfully completed sql> spool offstopped spooling to C:\PLSQL.txt