Flow Control statements and Oracle temporary tables in PL/SQL

Flow Control statements in PL/SQL and temporary Oracle tables [SQL] create global temporary table temp_dept (dno number, dname varchar2 (10) on commit delete rows; insert into temp_dept values (10, 'abc'); drop table temp_dept -- the temporary table in Oracle is different from the temporary table in SQL Server. -- The temporary table of SQL Server is a table created by "temporary", which is no longer used up. -- In fact, Oracle's "temporary" table is "permanent" and data is "temporary. -- Condition control statement -- 1. Only IF condition control statement IF concition then statements; end if; www.2cto.com -- See the following example: DECLARE salaryAVG number (7,2 ); empSalary number (7,2); begin select avg (SAL) INTO salaryAVG FROM scott. emp; select sal into empSalary FROM scott. emp where empno = & eno; IF empSalary> salaryAVG THEN DBMS_OUTPUT.PUT_LINE ('the employee's salary has exceeded the average line' | salaryAVG); end if; END; -- 2. IF and else statements if condition then statements; else statements; end if; -- See the following example: DECLARE salaryAVG number (); empSalary number (); BEGIN www.2cto.com
Select avg (SAL) INTO salaryAVG FROM scott. emp; select sal into empSalary FROM scott. emp where empno = & eno; IF empSalary> salaryAVG THEN DBMS_OUTPUT.PUT_LINE ('the employee's salary has exceeded the average' | salaryAVG ); ELSE DBMS_OUTPUT.PUT_LINE ('the employee's salary has not exceeded the average' | salaryAVG); end if; END; -- 3. if esleif else statement IF condition then statements; ELSIF condition then statements; else statements; end if;
-- See the following example: DECLARE empSalary number (7,2); begin select sal into empSalary FROM scott. emp where empno = & eno; IF empSalary <2000 THEN DBMS_OUTPUT.PUT_LINE ('tax 0 RMB '); ELSIF empSalary <4000 THEN DBMS_OUTPUT.PUT_LINE ('tax payable '| (empSalary * 0.15-175) | 'meta '); ELSE DBMS_OUTPUT.PUT_LINE ('tax payable '| (empSalary * 0.25-375) | 'meta'); end if; END; www.2cto.com -- 4. Compare the CASE statement with a single value: CASE selector WHEN expression1 THEN result1; WHEN Expression2 THEN result2; WHEN expression3 THEN result3; ELSE result4; end case; -- See the following example: DECLARE eno NUMBER (2); BEGIN eno: = & no; CASE eno WHEN 10 THEN DBMS_OUTPUT.PUT_LINE ('department 1'); WHEN 20 THEN DBMS_OUTPUT.PUT_LINE ('department 2'); WHEN 30 THEN DBMS_OUTPUT.PUT_LINE ('department 3 '); ELSE DBMS_OUTPUT.PUT_LINE ('no Department '); end case; END; -- Comparison of multiple conditions: CASE www.2cto.com WHEN expression1 THEN result1; WHEN expression2 THEN r Esult2; WHEN expression3 THEN result3; ELSE result4; end case; -- 2. LOOP Control statement -- 1. loop statements; end loop; -- Example 1: DECLARE a int; BEGIN a: = 0; loop if a = 10 then exit; end if; DBMS_OUTPUT.PUT_LINE (a); a: = a + 1; end loop; END; -- Example 2: DECLARE a int; BEGIN a: = 0; loop exit when a = 10; DBMS_OUTPUT.PUT_LINE (a); a: = a + 1; end loop; END; www.2cto.com -- 2. WHILE loop -- Example 1: DECLARE a int; BEGIN a: = 0; WHILE a <10 L OOP DBMS_OUTPUT.PUT_LINE (a); a: = a + 1; end loop; END; -- 3. for loop -- Example 1: begin for a IN 0 .. 9 LOOP DBMS_OUTPUT.PUT_LINE (a); end loop; END; -- 4. multiple loops and labels -- Example 1: DECLARE result INT; BEGIN <outer> FOR I IN 1 .. 10 LOOP <inner> FOR j IN 1 .. 10 LOOP result: = I * j; EXIT outer WHEN result = 10; EXIT WHEN result = 5; DBMS_OUTPUT.PUT_LINE ('inner: '| result); END LOOP inner; DBMS_OUTPUT.PUT_LINE ('outer: '| result); end loop oute R; DBMS_OUTPUT.PUT_LINE ('Last: '| result); END; www.2cto.com -- 3. Sequential Control statement -- 1. GOTO statement -- Syntax: GOTO labelName; -- 2. NULL statement -- NULL; the statement is passed directly to the next statement without any operation.