Execution of PL/SQL in Oracle and various Process Control

Execution of PL/SQL in Oracle and various Process Control
PL/SQL Execution and exception in Oracle I. PL/SQL Execution. Value assignment statement. There are two assignment statements: one is to define a variable and then receive the user's IO assignment; the other is to assign values through the SQL query results. User assignment example:

Set serveroutput on; accept abc prompt 'Enter the value of abc '; DECLARE a int: = & abc; BEGIN dbms_output.put_line (a); END;

 

Query assignment example:

set serveroutput on;DECLARE  str varchar2(20);BEGIN  select ename into str from emp where empno='7369';  dbms_output.put_line(str);END;

 

Process control statement. The main control statements of PL/SQL are as follows: 1. if... then elsif... Then end if; if it is correct, then is executed; otherwise, else (elsif is nested) is executed. Pay attention to elsif, which should be omitted from e. 2. Case var when... Then when... The then end logic selects from the value 3. Loop exit end loop Loop control, and uses the judgment statement to execute exit 4. Loop exit when... End loop is the same as above. when the when is true, exit 5 is executed. while .. loop end loop 6. for... in... loop end loop is known to have a loop of the number of cycles because the process control is relatively large, here it is not a single instance, write a few more classic programs for you to see.

* ******* Print 9*9 multiplication table ********** for in .. loopset serveroutput on; DECLARE I number; j number; BEGIN for I in 1 .. 9 loop for j in 1 .. I loop dbms_output.put (I | '*' | j | '=' | (I * j) | ''); end loop; dbms_output.put_line (''); end loop; END; while loopset serveroutput on; declare I number: = 1; j number: = 1; begin while I <10 loop while j <= I loop dbms_output.put (I | '*' | j | '=' | (I * j) | ''); j: = j + 1; end loop; I: = I + 1; j: = 1; dbms_output.put_line (''); end loop; end;

 

* ******** Calculate 1 + 2 + .. + 10 value ********* loop exit & loop exit whenset serveroutput on; declare I number: = 1; I _sum number: = 0; begin loop I _sum: = I _sum + I; I: = I + 1; if I> 10 then exit; end if; end loop; dbms_output.put (I _sum); dbms_output.put_line (''); end;

 

Note: put only puts the output result into the buffer until it is output in the case of put_line. Therefore, the I _sum result is not displayed only when put is used. You can replace if I> 10 then exit; end if; with exit when I> 10; then it becomes the loop... exit when... end loop structure.

* ******** Enter a character, determines whether it is a letter, number, or other character ************** if then elsif elseset serveroutput on; declare I _char varchar2 (2 ); begin I _char: = & abc; if regexp_like (I _char, '^ [a-z] $') then dbms_output.put_line ('Enter the letter '); elsif regexp_like (I _char, '^ [0-9] $') then dbms_output.put_line ('Number entered '); else dbms_output.put_line ('other characters entered'); end if; end; case whenset serveroutput on; declare I _char varchar2 (2); I _result varchar2 (30); begin I _char :=& abc; I _result: = case when regexp_like (I _char, '^ [a-z] $') then' indicates the letter 'when regexp_like (I _char, '^ [0-9] $ ') then' inputs the number 'else' and other characters 'end; dbms_output.put_line (I _result); end;

 

Note: When entering the string in the prompt window, you must add single quotation marks. Otherwise, an error will occur. I need to mix it here every time. An SQL statement is a database query statement. Cursor Statement 2. Exception section. The mechanism and function of exceptions in the database are the same as those in java, but the code and format are different. Exceptions in the database are classified into three types: system exceptions (predefined exceptions) custom exceptions and application exceptions. A system exception occurs when the PL/SQL program is running. For example, a connection to Oracle cannot be established or a division of 0 is used. A good program should handle possible exceptions. The EXCEPTION handling code can be implemented in the EXCEPTION block using the WHEN statement to define exceptions. The usage of the WHEN statement is as follows: exception when <EXCEPTION name> THEN <EXCEPTION handling code>... when others then <exception handling code> common system exceptions custom exceptions WHEN an error related to an exception occurs, it will be implicitly triggered. A user-defined exception error is triggered by explicitly using the RAISE statement. When an EXCEPTION error is thrown, the control switches to the EXCEPTION part of the EXCEPTION block and runs the error handling code. To handle such exceptions, follow these steps: define exceptions in the definition section of PL/SQL blocks: <exception name> exception; RAISE exceptions in the execution section of PL/SQL blocks: RAISE <Exception name>; handle exceptions in PL/SQL blocks. WHEN <Exception name> THEN code... application exception thrown