The execution and exception parts of PL/SQL in Oracle
I. The executive section of PL/SQL.
Assignment statements are divided into two types, one defining a variable and then receiving the user's IO assignment, and the other is assigning a value to the SQL query result.
Examples of user assignments:
Set serveroutput on;accept ABC prompt ' Please enter the value of ABC ';D eclare a int:= &abc; BEGIN Dbms_output.put_line (a); END;
Example of query assignment:
Set serveroutput on;declare str varchar2 (20); BEGIN Select ename to str from emp where empno= ' 7369 '; Dbms_output.put_line (str); END;
- Process Control statements.
The main control statements for PL/SQL are as follows:
1.if...then elsif ... then end if;
Execute then if correct, otherwise execute else (elsif is nested judgment)
Notice elsif, there's less e.
2.Case var when ... then when ... then end
Logically select from a numeric value
3.Loop Exit End Loop
loop control, execute exit with judgment statement
4.Loop exit when ... end Loop
Ditto, execute exit when the When is true
5.while.. Loop End Loop
Loop when while is true
6.for...in ... Loop End Loop
Cycles of known cycles
Because the process control is more, here is a few examples, write a few more classic procedures for everyone to see.
Print 9*9 multiplication Table *********
For in.. Loop
Set 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 loop
Set 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+. Value of +10 *********
Loop exit & Loop Exit when
Set 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 simply puts the output in the buffer until it encounters put_line. So only put, the result of I_sum will not be displayed.
You can leave if i>10 then exit; End If; Switch to exit when i>10; this becomes loop. Exit When: End loop structure.
Enter a character to determine whether it is a letter, a number, or another character ****************
If then elsif else
Set Serveroutput on;declare I_char varchar2 (2); Begin i_char:=&abc; If Regexp_like (I_char, ' ^[a-z]$ ') then dbms_output.put_line (' input is the letter '); elsif regexp_like (I_char, ' ^[0-9]$ ') then dbms_output.put_line (' input is a number '); else Dbms_output.put_line (' other characters entered '); End If;end;
Case
Set Serveroutput on;declare I_char varchar2 (2); I_result varchar2 (+); Begin i_char:=&abc; i_result:= case when regexp_like (I_char, ' ^[a-z]$ ') then ' entered the letter ' when regexp_like (I_char, ' ^[ 0-9]$ ') then ' input is the number ' else ' input is the other character ' end; Dbms_output.put_line (i_result); end;
Note: In the prompt window input, the string must be added single quotation marks, otherwise it will be wrong, I have to mix each time here.
SQL statement is a database query statement, relatively simple is not here to say.
Two. Exception section.
Exceptions in the database are the same as the mechanism and principle of exceptions in Java, except that the code and format are different, and exceptions in the database fall into three categories: System exceptions (pre-defined exceptions), custom exceptions, and throwing application exceptions.
PL/SQL programs may cause errors or anomalies during operation
For example, you cannot establish a connection to Oracle or divide by 0. A good program should handle the exceptions that might occur, and the exception handling code is implemented in the exception block.
You can use the When statement to define an exception. When statements are used in the following ways:
EXCEPTION
When < exception name > Then
< exception handling code >
When < exception name > Then
< exception handling code >
...
When OTHERS Then
< exception handling code >
Common system Exceptions
Exception name |
Exception code |
Description |
No_data_found |
ORA-01403 |
No records were returned in the SELECT INTO statement |
Too_many_rows |
ORA-01422 |
Multiple rows of data are returned in the SELECT INTO statement. |
Zero_divde |
ORA-01476 |
Try to use 0 as the divisor. |
Dup_val_on_index |
ORA-00001 |
Attempting to insert duplicate values into a unique index column |
Invalid_cursor |
ORA-01001 |
An attempt was made to perform an illegal cursor operation. |
Cursor_already_open |
ORA-06511 |
An attempt was made to open a cursor that is already open. |
Invalid_number |
ORA-01722 |
An attempt was made to convert a number to a string. |
Value_error |
|
Occurs when a size limit error is generated. For example, the value of a column in a variable exceeds the size of the variable |
Login_denied |
|
Occurs when the user name or password entered is invalid. |
When an error related to an exception error occurs, it is implicitly triggered by the exception error. User-defined exception errors are triggered by explicitly using the RAISE statement. When an exception error is thrown, the control moves to the exception block exception error section and executes the error-handling code.
For handling this type of exception, the steps are as follows:
Define exception conditions in the definition section of the PL/SQL block:
< exception name > exception;
Raise exception in the execution section of the PL/SQL block:
RAISE < exception name >;
In the exception handling section of the PL/SQL block, the exception is handled accordingly.
When < exception name > Then
Code...
- Throwing Application Exceptions
Execution parts and various process controls for PL/SQL in Oracle