The executive part and various process controls for PL/SQL in Oracle

The execution and exception parts of PL/SQL in Oracle

I. The executive section of PL/SQL.

• An assignment statement.

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 statements

SQL statement is a database query statement, relatively simple is not here to say.

• Tour slogan sentence

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.

• System 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.

• Custom exceptions

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