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

Source: Internet
Author: User
Tags define exception exception handling numeric value

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.