Basic Oracle Stored Procedure syntax
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