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
Storing the results of a select query in a variable allows multiple columns to be stored in multiple variables at the same time and 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 Cycle
While V_test=1 LOOP
BEGIN
Xxxx
END;
END LOOP;

5. Assigning values to variables
V_test: = 123;

6. Using the 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 the 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. Developer debug with PL/SQL
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

Simple example, see results by Dbms_output

CREATE OR REPLACE PROCEDURE bb (Lic_para in VARCHAR2, Out_para out VARCHAR2)
As
Temp VARCHAR2 (100);
BEGIN
SELECT Lic_no
into Temp
from T_vehicle_info
WHERE lic_no = Lic_para;
Out_para:=temp;
Dbms_output.put_line (Out_para);
END BB;

Here is the call:

Begin

-- Call the procedure

bb (Lic_para = Lic_para,

Out_para =: Out_para);

End

You can hit Sqlplus "yonghuming/[email protected]" on the command line and then debug the stored procedure. But the best use is in the PL/SQL Developer tool with the point stored procedure test to call, it can automatically generate the call statement and have columns let you input parameter values, including input parameters and output parameters, and the results returned to the output parameters, visible in the result bar, this PL/SQL Developer than Toad, toad in the stored procedure right click on Execute procedure can also execute, but the results in that look I do not know, and in PL/SQL Developer Press F9 can be debugged, CTRL + N can be single-step tracking, indeed cool.

Basic syntax for Oracle stored procedures