Exceptions in PL/SQL and control statements in PL/SQL

PL/SQL exceptions and control statements in PL/SQL [/color] [color = orange] SQL> set serveroutput on SQL> remark control statement SQL> remark Exception Handling www.2cto.com SQL> REMARK ........................................ .................. SQL> REMARK IF... THEN judgment syntax SQL> remark IF condition-marked WHEN execution statement end if; example: SQL> REMARK uses grouping function conditions to judge by column SQL> EDIT Wrote file afiedt. buf 1 DECLARE 2 sumsal scott. EMP. SAL % TYPE; 3 BEGIN 4 select sum (SAL) INTO SUMSAL FRO M scott. emp group by deptno having deptno = 30; 5 if sumsal> 5000 THEN 6 DBMS_OUTPUT.PUT_LINE ('department 30's total salary has exceeded the budget for '| SUMSAL); 7 end if; 8 * END; SQL>/The total salary of Department 30 has exceeded the budget for 9400 PL/SQL procedure successfully completed. SQL> IF the REMARK has a branch condition, use IF... ELSIF .. ELSE... end if; SQL> REMARK multi-branch condition control case SQL> REMARK connects to scott account SQL> conn scott/tiger; Connected. SQL> DECLARE 2 THESAL EMP. SAL % TYPE; 3 BEGIN 4 SELECT SAL Into thesal from emp where empno = 7369; 5 CASE 6 when thesal <1000 THEN 7 DBMS_OUTPUT.PUT_LINE ('low wage '); 8 when thesal> = 1000 THEN 9 DBMS_OUTPUT.PUT_LINE ('General sale'); 10 ELSE 11 DBMS_OUTPUT.PUT_LINE ('high sale'); 12 end case; 13 END; 14/PL/SQL procedure successfully completed. SQL> set serveroutput on; SQL> REMARK loop control loop and end loop; the statements between them are executed infinitely. If you want to exit, use exit. SQL> remark Syntax: loop exit when conditional expression; execution statement; end lo Op; SQL> remark case: accumulative example SQL> declare 2 cou int default 10; 3 result int: = 0; 4 begin 5 DBMS_OUTPUT.PUT_LINE ('cycle starts ..... '); 6 LOOP 7 exit when cou> 20; 8 result: = result + cou; 9 10 DBMS_OUTPUT.PUT_LINE (' enters the LOOP .. '| cou |' result: '| result); 11 cou: = cou + 1; 12 end loop; 13 dbms_output.put_line (' the final result is: '| result); 14 end; 15/loop start ..... enter loop .. result 10: 10 enters the loop .. 11 The result is: 21 enters the loop .. 12. The result is: 33 .. 13. The result is: 46 .. 14. The result is: 60 enters the loop .. result 15: 75 enters the loop .. 16 results: 91 enters the loop .. 17. The result is: 108 enters the loop .. 18. The result is: 126 enters the loop .. 19. The result is: 145 enters the loop .. 20 results: 165 the final result is: 165 PL/SQL procedure successfully completed. SQL> remark loop control can also use for loop and while for loop. Both loops Use loop as the basis SQL> remark Syntax: for variable in start value .. end value loop execution statement; end loop; SQL> remark note: In the syntax CONTROL OF THE for loop, variable definitions are not declared, the variable's boundary value enters a loop ;....... case: SQL> declare 2 result int: = 0; 3 begin 4 dbms_output.put_line ('start loop... '); 5 For cou in 10 .. 20 6 loop 7 result: = result + cou; 8 dbms_output.put_line ('enters the loop .. '| cou |' result: '| result); 9 end loop; 10 dbms_output.put_line ('loop end:' | result); 11 end; 12/loop start... enter loop .. result 10: 10 enters the loop .. 11 The result is: 21 enters the loop .. 12. The result is: 33 .. 13. The result is: 46 .. 14. The result is: 60 .. result 15: 75 enters the loop .. 16 results: 91 enters the loop .. 17. The result is: 108 enters the loop .. 18. The result is: 126 enters the loop .. 19. The result is: 145 enters the loop .. 20 results: 165 loop ended: 165 PL/SQL procedure successfully completed. SQL> Remark while loop usage SQL> remark Syntax: while condition expression loop execution statement; end loop; SQL> remark ..................................... ........................... SQL> remark Jump Control: the redirection command is goto SQL> remark Syntax: execution statement <section name> execution statement; SQL> remark if you only want the Section to act as a jump point and do not execute any statements, you can use null and use return to end the program SQL> remark jump case: SQL> declare 2 sumsal emp. sal % type; 3 begin 4 select sum (sal) into sumsal from emp where deptno = 30; 5 if sum Sal> 2000 then 6 goto first; 7 elsif sumsal> 3000 then 8 goto second; 9 else 10 goto third; 11 end if; 12 13 <first> 14 dbms_output.put_line ('first' | sumsal); 15 return; 16 <second> 17 dbms_output.put_line ('second' | sumsal ); 18 return; 19 <third> 20 null; 21 end; 22/first9400 PL/SQL procedure successfully completed. SQL> remark ..................................... ........................... SQL> remark record: the PS record has only one row, but it consists of multiple columns. It is a bit of type in the structure of the program. SQL> remark record definition: type <type Name> is record <column name 1 type 1 ,.... case study of column name n type n,> SQL> remark: SQL> edit; Wrote file afiedt. buf 1 declare 2 type empsub is record (myeno emp. empno % type, myename emp. ename % type, mysa emp. sal % type); 3 mycord empsub; 4 begin 5 select empno, ename, sal into mycord from emp where empno = 7369; 6 dbms_output.put_line ('1: '| mycord. myeno); 7 * end; SQL>/1: 7369 PL/SQL procedure successfully completed. SQL> remark empsub defines a data type SQL> remark ........................... ....................................... SQL> remark exception: either internal or user-defined exception. To capture exceptions in SQL> remark PS, you must use the Exception name given by the system, instead of the exception number; SQL> declare 2 newsal emp. sal % type; 3 begin 4 select sal into newsal from emp where deptno = 30; 5 dbms_output.put_line ('salary: '| newsal); 6 exception 7 when Too_many_rows then 8 dbms_output.put_line ('Too many data insertion records '); 9 end; 10/too many data insertion records PL/SQL procedure successfully completed. SQL> remark custom exception SQL> remark declare exception name exception; ---- declare an exception SQL> remark raise exception name; -- throw an exception SQL> remark exception when exception name then ..... -- Capture and handle exceptions. SQL> remark RAISE_APPLICATION_ERROR (error code, error message); SQL> remark error code: the number specified by the user for throwing an exception, -SQL> remark error message between 20000 and-20999: a custom error message with a length of 2048; SQL> declare 2 thesal emp. sal % type; 3 begin 4 select sal into thesal from emp where empno = 7369; 5 if thesal> 2000 then 6 raise_application_error (-20001, 'salary no longer adjusted range '); 7 end if; 8 end; 9/PL/SQL procedure successfully completed. SQL> remark ..................................... ........................... SQL> remark dynamic SQL: constructs a query statement dynamically. Syntax: SQL> remark execute immediate 'SQL statement string'; SQL> remark creates a table: SQL> begin 2 execute immediate 3 'create table hopecalss (claid number, calName char (6) '; 4 end; 5/PL/SQL procedure successfully completed. SQL> remark dynamic SQL parameter passing and sometimes result variables; SQL> edit Wrote file afiedt. buf 1 declare 2 sqlstring varchar2 (200); 3 dept_id number (2): = 50; 4 dept_name varchar (14): = 'personnel '; 5 location varchar (13 ): = 'develop'; 6 begin 7 -- demonstrate execute immediate 8 sqlstring: = 'insert into dept values (: 1,: 2,: 3) 'of usingredients )'; 9 execute immediate sqlstring using dept_id, dept_name, location; 10 * end; 11/PL/SQL procedure successfully completed. SQL> spool off; [color = orange] [/color] [size = x-small] [/size]