Oracle Learning (11): PL/SQL

1. knowledge point: you can read the following screen recording:

PL/SQL program structure declare description (variable description, cursor declaration, exception description) begin statement sequence (DML statement 〕... Exception Processing statement End;/-------------------------------------------------------------------- first PL/SQL program: HelloWorldset serveroutput on -- if you want to output information on the screen, you need to switch the serveroutput switch to the declare -- variable description begin -- program body -- package dbms_output.put_line ('Hello World'); end;/begin reference variable: query and print the name and salary of 7839 set serveroutput ondeclare -- Define the variable, pename emp. ename % type; -- convert penam Set the e type to psal emp of the ename type in the emp table. sal % type; -- set the psal type to the sal type in the emp table begin -- Query: -- ename, sal into pename, psal: Assign the queried ename and sal to pename, respectively, psal values select ename, sal into pename, psal from emp where empno = 7839; -- print dbms_output.put_line (pename | 'salary:' | psal); end; /-------------------------------------------------------------------- query record variables and print the name and salary of 7839 set serveroutput ondeclare -- define variables, representing a row of emp_rec emp % rowtype; Begin -- get a row and assign it to emp_rec select * into emp_rec from emp where empno = 7839; dbms_output.put_line (emp_rec.ename | 'salary is '| emp_rec.sal); end; /-------------------------------------------------------------------- if statement: determines the number set serveroutput on/* 1 entered by the user. prompt Message 2. the input num on the receiving keyboard is an address value. SQL optimization: num binds the variable (use the Bind Variable whenever possible) select * from emp where deptno = 10; --> execution plan select * from emp where deptno = 20; --> execution plan --> select * from emp where Deptno = & num; */accept num prompt 'enter a number'; declare -- variable to save the input number pnum number: = & num; begin -- determine if pnum = 0 then dbms_output.put_line ('your input is 0'); elsif pnum = 1 then dbms_output.put_line ('your input is 1 '); elsif pnum = 2 then dbms_output.put_line ('your input is 2'); else dbms_output.put_line ('other number'); end if; end;/else loop: Print 1 ~ 10, there are 3 methods, see set serveroutput ondeclare pnum number: = 1; -- assign 1 to pnum, pnum type is numberbegin loop -- exit: True to exit, exit when pnum> 10; -- implicit conversion; number is converted to varchar2 dbms_output.put_line (pnum); pnum: = pnum + 1; end loop; end;/Cursor (Cursor) = ResultSet indicates the 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 select ename fr Om emp; open the cursor: open c1; (open the cursor to execute the query) Take the value of a row of the cursor: fetch c1 into pjob; (take a row into the variable) close the cursor: close c1; (close the cursor to release resources) Note: The above pjob must be of the same type as the job column in the emp table: Definition: pjob emp. empjob % type; cursor: Use the cursor to query the employee name and salary, and print the three attributes of the/* cursor: % isopen enabled: true enabled, false disabled; % rowcount rows % notfound value: no value true, value false; */set serveroutput ondeclare -- cursor cemp is select ename, sal from emp; pename Emp. ename % type; -- set the type of the variable used for receiving to the same psal emp as the query type. sal % type; begin open cemp; -- open the cursor and execute the query loop -- fetch cemp into pename, psal; -- exit when there is no value in the cursor when exit when cemp % notfound; dbms_output.put_line (pename | 'salary: '| psal); end loop; close cemp; end;/cursor exercise: raise the employee's salary, President, 1000 manager, 800 other 400 -- the maximum number of employees that the cursor can open at the same time is 300 by default/* SQL> show parameters cursorNAME TYPE VALUE -- Export ----------- -------- cursor_sharing string EXACTcursor_space_for_time boolean FALSEopen_cursors integer limit integer 20 */set serveroutput ondeclare -- the cursor represents the employee's cursor cemp is select empno, job from emp. empno % type; pjob emp. job % type; begin open cemp; loop fetch cemp into pempno, pjob; exit when cemp % notfound; -- determine if pjob = 'President 'th En update emp set sal = sal + 1000 where empno = pempno; -- the president rose 1000 elsif pjob = 'manager' then update emp set sal = sal + 800 where empno = pempno; -- manager up 800 else update emp set sal = sal + 400 where empno = pempno; -- other increases by 400 end if; end loop; close cemp; -- submit: isolation level commit; dbms_output.put_line ('complete'); end;/------------------------------------------------------------ cursor with parameters: query the employee name of a department set serveroutput ondeclare- -Defining a parameter-based cursor cemp (pdno number) is select ename from emp where deptno = pdno; pename emp. ename % type; begin open cemp (20); -- open the cursor and pass the parameter loop fetch cemp into pename; exit when cemp % notfound; dbms_output.put_line (pename); end loop; close cemp; end;/------------------------------------------------------------------ Oracle Exception Handling 1. system definition exception No_data_found (no data found) Too_many_rows (select... The into statement matches multiple rows.) Zero_Divide (Division by zero) Value_error (arithmetic or conversion error) Timeout_on_resource (timeout occurred while waiting for resources) -- Example: Zero_Divide (Division by zero) set serveroutput ondeclare pnum number; begin pnum: = 1/0; exception when Zero_Divide then dbms_output.put_line ('1: 0 cannot be divisor '); divide ('2: 0 cannot be divisor '); when Value_error then dbms_output.put_line ('Arithmetic error'); when others then dbms_output.put_line ('other exception'); end;/custom exceptions-custom exceptions: query the employee name set serveroutput ondeclare cursor cemp is select ename from emp where deptno = 50; pename emp. ename % type; -- custom exception no_emp_found exception; begin open cemp; -- get 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 ('employee not found '); when others then dbms_output.put_line ('other exception'); end ;/

 

Figure: three statements of loop: the method in the upper right corner.

2. screen recording of actual execution results under Sqlplus

SQL> -- first PL/SQL program: HelloWorldSQL> set serveroutput on -- to output information on the screen, switch serveroutput to SQL> declare 2 -- variable description 3 begin 4 -- program body 5 -- package 6 dbms_output.put_line ('Hello World'); 7 end; 8/Hello World PL/SQL procedure successfully completed SQL> -- reference variable: Query and print the name and salary of 7839 SQL> set serveroutput onSQL> declare 2 -- Define the variable, 3 pename emp. ename % type; -- set the pename type to the type 4 psal emp of the ename In the emp table. sal % type; -- convert ps Set the al type to sal type 5 6 begin 7 8 in the emp table -- Query: 9 -- ename, sal into pename, psal: Assign the queried ename and sal to pename, respectively, psal Values 10 select ename, sal into pename, psal from emp where empno = 7839; 11 12 -- print 13 dbms_output.put_line (pename | 'salary is '| psal ); 14 15 end; 16/KING's salary is 5000 PL/SQL procedure successfully completed SQL>-record-type variable query and print 7839 name and salary SQL> set serveroutput onSQL> declare 2 -- define variables, represents a row of 3 emp_rec emp % rowtyp E; 4 begin 5 -- get a row and assign it to emp_rec 6 select * into emp_rec from emp where empno = 7839; 7 8 dbms_output.put_line (emp_rec.ename | 'salary: '| emp_rec.sal); 9 10 end; 11/KING's salary is 5000 PL/SQL procedure successfully completed SQL> -- if statement: determines the number of user input SQL> set serveroutput onSQL>/* 2 1. tip 3 2. receiving keyboard input 4 num is an address value 5 6 SQL optimization: num Bind Variable (try to use Bind Variable) 7 select * from emp where deptno = 10; --> Execution Plan 8 select * from emp w Here deptno = 20; --> Execution Plan 9 --> 10 select * from emp where deptno = & num; 11 12 */13 accept num prompt 'enter a number '; SQL> declare 2 -- variable save input number 3 pnum number: = & num; 4 begin 5 -- Judge 6 if pnum = 0 then 7 dbms_output.put_line ('your input is 0 '); 8 elsif pnum = 1 then 9 dbms_output.put_line ('your input is 1'); 10 elsif pnum = 2 then 11 dbms_output.put_line ('your input is 2 '); 12 else 13 dbms_output.put_line ('other number'); 14 end if; 15 en D; 16/you Entered 2 PL/SQL procedure successfully completed SQL> -- loop: Print 1 ~ 10SQL> set serveroutput onSQL> declare 2 pnum number: = 1; -- assign 1 to pnum, pnum type: number 3 begin 4 loop 5 -- exit: True and exit, 6 exit when pnum> 10; 7 8 -- implicit conversion, number to varchar2 9 dbms_output.put_line (pnum); 10 11 pnum: = pnum + 1; 12 13 end loop; 14 end; 15/12345678910 PL/SQL procedure successfully completed SQL>-cursor: Use cursor to query employee name and salary, and print SQL>/* 2 three attributes of cursor: 3% whether isopen is Enabled: true is enabled, false is disabled; 4% whether the number of rowcount rows is 5% notfound; whether the value is true or false; 6 */SQL> set serveroutput onSQL> declare 2 -- cursor 3 cursor cemp is select ename, sal from emp; 4 pename emp. ename % type; -- set the type of the variable to be received to the same as the query type. 5 psal emp. sal % type; 6 begin 7 open cemp; -- open the cursor and execute query 8 loop 9 -- value 10 fetch cemp into pename and psal from the set; 11 -- exit when there is no value in the cursor 12 exit when cemp % notfound; 13 14 dbms_output.put_line (pename | 'salary:' | psal); 15 16 end loop; 17 close cemp; 18 end; 19/SMITH's salary is 800ALLEN's salary is 1600WARD's salary is 1250JONES's salary is 2975MARTIN's salary is 1250blke's salary is 2850CLARK's salary is 2450SCOTT's salary is 3000KING's salary is 5000TURNER's salary the salary is 1300 Adams. The salary is 1100JAMES. The salary is 950FORD. The salary is Miller. The salary is PL/SQL procedure successfully completed SQL> -- cursor exercise: to raise the employee's salary, President 1000 manager 800 other 400SQL> -- first query the SQL statement before the increase> select * from emp; empno ename job mgr hiredate sal comm deptno ----- ---------- --------- ----------- --------- ------ 7369 smith clerk 7902 1980/12/17 800.00 20 7499 allen salesman 7698 CLERK 1600.00 300.00 7521 7698 jones manager 7839 1981/4/2 2975.00 20 7654 martin salesman 7698 1981/9/28 1250.00 1400.00 30 7698 blake manager 7839 1981/5/1 2850.00 30 7782 clark manager 7839 Jun 2450.00 10 7788 scott analyst 7566 1987/4/19 3000.00 20 7839 king president 1981/11/17 10 7844 turner salesman 7698 CLERK 1500.00 30 0.00 adams clerk 7876 1987/5/23 7788 20 1100.00 james clerk 7900 1981/12/3 7698 30 950.00 ford analyst 7902 1981/12/3 7566 20 3000.00 miller clerk 7934 1982/1/23 7782 10 14 rows selected SQL> -- execute PLSQL statement SQL> set serveroutput onSQL> declare 2 -- the 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; 10 exit when cemp % notfound; 11 12 -- Judge 13 if pjob = 'President 'then update emp set sal = sal + 1000 where empno = pempno; -- the president rose 1000 14. elsif pjob = 'manager' then update emp set sal = sal + 800 where empno = pempno; -- manager up 800 15 else update emp set sal = sal + 400 where empno = pempno; -- others up 400 16 end if; 17 end loop; 18 close cemp; 19 20 -- submit: isolation level 21 commit; 22 23 dbms_output.put_line ('complete'); 24 end; 25/PL/SQL procedure successfully completed SQL> -- query the salary SQL statement after the PLSQL statement is executed> select * from emp; empno ename job mgr hiredate sal comm deptno ----- ---------- --------- ----------- --------- ------ 7369 smith clerk 7902 1980/12/17 1200.00 20 7499 allen salesman 7698 CLERK 2000.00 300.00 7521 7698 jones manager 7839 1981/4/2 3775.00 20 7654 martin salesman 7698 1981/9/28 1650.00 1400.00 30 7698 blake manager 7839 1981/5/1 3650.00 30 7782 clark manager 7839 Jun 3250.00 10 7788 scott analyst 7566 1987/4/19 3400.00 20 7839 king president 1981/11/17 10 7844 turner salesman 7698 CLERK 1900.00 30 0.00 adams clerk 7876 1987/5/23 7788 20 1500.00 james clerk 7900 1981/12/3 7698 30 1350.00 ford analyst 7902 1981/12/3 7566 20 3400.00 miller clerk 7934 1982/1/23 7782 10 14 rows selected SQL> -- before comparison, you can see that the salary increase is successful SQL> -- the cursor with parameters: query the employee name of a department SQL> set serveroutput onSQL> declare 2 -- Define a parameter with the cursor 3 cursor cemp (pdno number) is select ename from emp where deptno = pdno; 4 pename emp. ename % type; 5 begin 6 open cemp (20); -- open the cursor and pass the parameter 7 loop 8 fetch cemp into pename; 9 exit when cemp % notfound; 10 11 dbms_output.put_line (pename); 12 13 14 end loop; 15 close cemp; 16 end; 17/smithjonesscottadamsford pl/SQL procedure successfully completed SQL> -- Example: Zero_Divide SQL> set 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 be used as the divisor '); 9 dbms_output.put_line ('2: 0 cannot be used as the divisor '); 10 when Value_error then dbms_output.put_line ('Arithmetic error'); 11 when others then dbms_output.put_line ('other exception'); 12 end; 13/1: 0 cannot perform divisor 2: 0 cannot be used as the dividend PL/SQL procedure successfully completed SQL> -- custom exceptions: query employee name SQL> set serveroutput onSQL> declare 2 cursor cemp is 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 -- take an employee 10 fetch cemp into pename; 11 if cemp % notfound then 12 raise no_emp_found; 13 end if; 14 15 close cemp; 16 17 exception 18 when no_emp_found then dbms_output.put_line ('employee not found '); 19 when others then dbms_output.put_line ('other exception'); 20 21 end; 22/No employee PL/SQL procedure successfully completed SQL> spool offStopped spooling to c: \ PLSQL.txt found