Oracle Stored Procedure

1. Basic Structure
Create or replace procedure
(
Parameter 1 in number,
Parameter 2 in number
) Is
Variable 1 integer: = 0;
Variable 2 date;
Begin

End stored procedure name

2. Select into statement
Save the result of the SELECT query to a variable. Multiple columns can be stored in multiple variables at the same time. One
Record; otherwise, an exception is thrown (if no record exists, no_data_found is thrown)
Example:
Begin
Select col1, col2 into variable 1, variable 2 from typestruct Where xxx;
Exception
When no_data_found then
Xxxx;
End;
...

3. If judgment
If v_test = 1 then
Begin
Do something
End;
End if;

4. While Loop
While v_test = 1 Loop
Begin
Xxxx
End;
End loop;

5. Variable assignment
V_test: = 123;

6. Use cursor with for in
...
Is
Cursor cur is select * from XXX;
Begin
For cur_result in cur Loop
Begin
V_sum: = cur_result. Column name 1 + cur_result. Column name 2
End;
End loop;
End;

7. cursor with Parameters
Cursor c_user (c_id number) is select name from user where typeid = c_id;
Open c_user (variable value );
Loop
Fetch c_user into v_name;
Exit fetch c_user % notfound;
Do something
End loop;
Close c_user;

8. Use PL/SQL developer debug
Create a test window after connecting to the database
Enter the SP call code in the window, F9 start debug, CTRL + n single-step debugging reprint: Oracle Stored Procedure

Keywords: Oracle Stored Procedure

Stored Procedure creation Syntax:

Create or replace procedure stored procedure name (param1 in type, param2 out type)

As

Variable 1 type (value range );

Variable 2 type (value range );

Begin

Select count (*) into variable 1 from Table A where column name = param1;

If (condition) then

Select column name into variable 2 from Table A where column name = param1;

Dbms_output. Put_line ('print info ');

Elsif (Judgment condition) then

Dbms_output. Put_line ('print info ');

Else

Raise Exception name (no_data_found );

End if;

Exception

When others then

Rollback;

End;

Note:

1. stored procedure parameters do not have a value range. In indicates input, and out indicates output.

2. The variable has a value range followed by a semicolon

3. Use the count (*) function to determine whether the operation record exists before determining the statement.

4. Select... Into... Assign values to variables

5. If an exception is thrown in the code, use raise + Exception name.

An exception named after

Cause of naming system exception

Access_into_null: Undefined object

If no corresponding when exists in case_not_found case

Else time

Collection_is_null collection element not initialized

Curser_already_open cursor opened

The column corresponding to the unique index of dup_val_on_index has duplicate values.

Invalid_cursor operates on an invalid cursor

Invalid_number embedded SQL statements cannot convert characters into numbers

No_data_found: If select into is used, no rows are returned, or the application index table is not initialized.

When too_many_rows executes select into, the result set exceeds one row.

The zero_divide divisor is 0.

Subscript_beyond_count element subscript exceeds the maximum value of nested tables or varray

When subscript_outside_limit uses a nested table or varray, the subscript is specified as a negative number.

When value_error is assigned a value, the variable length is insufficient to accommodate actual data.

When the login_denied PL/SQL application connects to the Oracle database

Correct user name or password

The not_logged_on PL/SQL application is not connected to the oralce database.

Access Data

Program_error PL/SQL internal problems, you may need to reinstall the data dictionary & pl./SQL

System package

Rowtype_mismatch: the host cursor variable is incompatible with the PL/SQL cursor variable return type

When self_is_null uses the object type, the object method is called on the null object.

When storage_error runs PL/SQL, the memory space is exceeded.

Invalid sys_invalid_id rowid string

Timeout_on_resource Oracle timeout while waiting for resources

From: http://lorry1113.javaeye.com/blog/513851