Oracle Procedure Stored Procedures

1. Basic structure

 create  or  replace  procedure   stored procedure name (parameter 1  in  number   in  number  )  is  / as   variable 1  integer : =  0   begin  ... end  stored procedure name 

2. SELECT into STATEMENT

　　Depositing the results of a select query into a variable, you can store multiple columns (fields) in multiple variables at the same time, you must have one record, otherwise throw an exception (if no record, throw No_data_found)

Example:

BEGIN     SELECT  into  from where xxx;     EXCEPTION       when and thenROLLBACK;        END

3. If judgment

IF    Condition 1 Then do         someting    elsif    condition 2    then do        someting    elsif    Condition 3    then does        someting    ELSEdo        someting  ENDIF

4. While loop

 while V_test=1  LOOP     BEGIN         XXXX      END  END

5. Assigning values to variables

V_test:=123

6. Use the cursor (cursor) with for in

...      is         CURSORCur is SELECT *  fromxxx; BEGIN          forCur_resultinchcur LOOPBEGINv_sum:=Cur_result. Column Name 1+cur_result. Column Name 2END; ENDLOOP; END;

7, with PL/SQL developer debug

Create a test Window after connecting to the database

Enter the code for the calling SP in the window, F9 start debug,ctrl+n single-Step debugging

Precautions:

1) The stored procedure parameter does not take the value range, in indicates the incoming, out represents the output;

2) variable can take the value range, followed by a semicolon;

3) Before judging a statement, it is best to use the count (*) function to determine if there is a record of the operation

4) with Select...into ... Assigning values to variables

5) throw exceptions in code with raise; General usage:

EXCEPTION      when  then        ROLLBACK ;        RAISE; /* exceptions are divided into classes, such as No_data_found. Others should write the exception name, if you do not want to divide the exception so fine, you can use a general others to capture, that is, all the anomalies are captured with others. When the OTHERS then indicates a different exception. Raise indicates that an exception was thrown so that user can see it. */

6) The difference between as/is

In an attempt (VIEW) only with the IS

Only is cannot be used as in cursor

7) There are two kinds of output variable assignment, one is direct: =, and the other is select INTO

8) Display Debug information

A. Adding a stored procedure in the necessary location

Dbms_output.put_line ('HI');

B. Open Dbms_output

show Serveroutput; Set on;

9) Delete procedure

DROP PROCEDURE procedure_name;

Oracle Procedure Stored Procedures