Oracle stored procedure and function example

1. 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
 

Simple instance. The result is displayed through 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;

The following is a call:

Begin

-- Call the procedure

Bb (lic_para =>: lic_para,

Out_para =>: out_para );

End;

For example, the cursor if, for example

Create or replace procedure peace_if
Is
Cursor var_c is select * from grade;
Begin
For temp in var_c loop
If temp. course_name = 'OS' then
Dbms_output.put_line ('Stu _ name = '| temp. stu_name );
Elsif temp. course_name = 'DB' then
Dbms_output.put_line ('DB ');
Else
Dbms_output.put_line ('Feng la feng LA ');
End if;
End loop;
End;

--- Example 1 of cursor for and case

Create or replace procedure peace_case1
Is
Cursor var_c is select * from test_case;
Begin
For temp in var_c loop
Case temp. vol
When 1 then
Dbms_output.put_line ('haha1 ');
When 2 then
Dbms_output.put_line ('haha2 ');
When 3 then
Dbms_output.put_line ('haha3 ');
When 4 then
Dbms_output.put_line ('haha4 ');
Else
Dbms_output.put_line ('qita ');
End case;
End loop;
End;

--- Example 2 of cursor for and case

Create or replace procedure peace_case2
Is
Cursor var_c is select * from test_case;
Begin
For temp in var_c loop
Case
When temp. vol = 1 then
Dbms_output.put_line ('haha1 ');
When temp. vol = 2 then
Dbms_output.put_line ('haha2 ');
When temp. vol = 3 then
Dbms_output.put_line ('haha3 ');
When temp. vol = 4 then
Dbms_output.put_line ('haha4 ');
Else
Dbms_output.put_line ('qita ');
End case;
End loop;
End;

--- Example of a for loop

Create or replace procedure peace_for
Is
Sum1 number: = 0;
Temp varchar2 (500 );
Begin
For I in 1 .. 9 loop
Temp: = '';
For j in 1. I
Loop
Sum1: = I * j;
Temp: = temp | to_char (I) | '*' | to_char (j) | '=' | to_char (sum1) | '';
End loop;
Dbms_output.put_line (temp );
End loop;

End;

--- Example of loop

Create or replace procedure peace_loop
Is
Sum1 number: = 0;
Temp number: = 0;
Begin
Loop
Exit when temp> = 10;
Sum1: = sum1 + temp;
Temp: = temp + 1;
End loop;
Dbms_output.put_line (sum1 );
End;

--- Examples of cursors and loop loops

Create or replace procedure loop_cur
Is
Stu_name varchar2 (100 );
Course_name varchar2 (100 );
Cursor var_cur is select * from grade;
Begin
Open var_cur;
Loop
Fetch var_cur into stu_name, course_name;
Exit when var_cur % notfound;
Dbms_output.put_line (stu_name | course_name );
End loop;
Close var_cur;
End;

--- Exception handling example

Create or replace procedure peace_exp (in1 in varchar2)
Is
C_n varchar2 (100 );
Begin
Select course_name into c_n from grade where stu_name = in1;
Dbms_output.put_line (c_n );
Exception
When no_data_found
Then
Dbms_output.put_line ('try ');
When TOO_MANY_ROWS
Then
Dbms_output.put_line ('More ');
End;

--- Example 2 of exception handling

Create or replace procedure peace_insert (c_n in varchar2)
Is
Error EXCEPTION;
Begin
If c_n = 'OK'
Then
Insert into course (course_name) values (c_n );
Elsif c_n = 'NG 'then
Insert into course (course_name) values (c_n );
Raise error;
Else
Dbms_Output.put_line ('c _ n' | c_n );
End if;
Commit;
Exception
When error then
Rollback;
Dbms_Output.put_line ('erro ');
End;

--- Package example definition package

Create or replace package peace_pkg
As
Function test1 (in1 in varchar2)
Return number;
Procedure test2 (in2 in varchar2 );
End peace_pkg;

--- The package example defines the package body

Create or replace package body peace_pkg
As
Function test1 (in1 in varchar2)
Return number
As
Temp number;
Begin
Temp: = 0;
Return temp;
End;
Procedure test2 (in2 in varchar2)
Is
Begin
Dbms_output.put_line (in2 );
End;
End peace_pkg;