Basic syntax for Oracle stored procedures

oracle| Stored Procedures | syntax

Basic syntax for Oracle stored procedures

1. Basic Structure
CREATE OR REPLACE PROCEDURE Stored procedure name
(
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
By saving the results of a select query into a variable, you can store multiple columns in multiple variables at the same time, you must have a
Record, otherwise throw an exception (if no record is thrown no_data_found)
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 value
V_test: = 123;

6. Use cursor with for
...
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 code for the calling SP in the window, F9 start debug,ctrl+n step