Oracle stored procedure Basic syntax stored procedure

Oracle stored procedure Basic syntax stored procedure
1 CREATE OR REPLACE PROCEDURE Stored procedure name
2 is
3 BEGIN
4 NULL;
5 END;

Line 1:
Create OR REPLACE PROCEDURE is an SQL statement that notifies the Oracle database to create a stored procedure called skeleton, overwriting it if it exists;
Line 2:
The IS keyword indicates that a PL/SQL body is followed.
Line 3:
The BEGIN keyword indicates the beginning of the PL/SQL body.
Line 4:
The NULL PL/SQL statement indicates that nothing is done, and this sentence cannot be deleted because there is at least one sentence in the PL/SQL body;
Line 5:
End keyword indicates the termination of the PL/SQL body
Stored Procedure creation Syntax:
Create or Replace procedure stored procedure name (param1 in type,param2 out type)
As
Variable 1 type (value range); --VS_MSG VARCHAR2 (4000);
Variable 2 type (value range);

Copy CodeThe code is as follows:
Begin
Select COUNT (*) into variable 1 from table A where column name =param1;

If (judging condition) then
Select column name into Variable 2 from table a where column name =param1;
Dbms_output. Put_Line (' Print information ');
elsif (judging condition) then
Dbms_output. Put_Line (' Print information ');
Else
Raise exception name (no_data_found);
End if;
Exception
When others then
Rollback;
End;

Precautions:
1, the stored procedure parameter does not take a range of values, in represents the incoming, out represents the output
Type can use a legitimate type in any Oracle.
2, variable with value range, followed by semicolon
3, it is best to use the count (*) function to determine if there is a record of the operation before judging the statement.
4, with Select ... Into ... Assigning values to variables
5, throwing exceptions in code with raise+ exception name

Copy CodeThe code is as follows:
CREATE OR REPLACE Procedure Stored procedure name
(
--Defining parameters
Is_ym in CHAR (6),
The_count out number,
)
As
--Defining variables
VS_MSG VARCHAR2 (4000); --Error message variable
Vs_ym_beg CHAR (6); --Start month
Vs_ym_end CHAR (6); --End of month
Vs_ym_sn_beg CHAR (6); --month of the same period
Vs_ym_sn_end CHAR (6); --month ending in the same period
--Define a cursor (simply a result set that can be traversed)

CURSOR cur_1 is
SELECT ...
From ...
WHERE ...
GROUP by ...;
BEGIN

--Using the input parameters to assign the initial value to the variable, using the Oralce substr to_char add_months

To_date and other functions that are commonly used.
Vs_ym_beg: = SUBSTR (is_ym,1,6);
Vs_ym_end: = SUBSTR (is_ym,7,6);
Vs_ym_sn_beg: = To_char (Add_months (To_date (Vs_ym_beg, ' yyyymm '), -12), ' yyyymm ');
Vs_ym_sn_end: = To_char (Add_months (To_date (vs_ym_end, ' yyyymm '), -12), ' yyyymm ');

--First delete the data for a specific condition in the table.

DELETE from table name WHERE ym = Is_ym;

--Then use the Put_Line method of the built-in Dbms_output object to print out the number of recorded rows affected, which uses a system variable Sql%rowcount

Dbms_output.put_line (' del last month record = ' | | sql%rowcount| | ' Article ');
INSERT into table name (Area_code,ym,cmcode,rmb_amt,usd_amt)
SELECT area_code,is_ym,cmcode,sum (Rmb_amt)/10000,sum (Usd_amt)/10000
From bgd_area_cm_m_base_t
WHERE ym >= Vs_ym_beg
and YM <= Vs_ym_end
GROUP by Area_code,cmcode;
Dbms_output.put_line (' ins month record = ' | | sql%rowcount| | ' Article ');
--Update to table after traversing cursor processing. There are several ways to traverse a cursor, which is more intuitive with a for statement.

For rec in cur_1 LOOP
UPDATE Table Name
SET rmb_amt_sn = REC.RMB_AMT_SN,USD_AMT_SN = Rec.usd_amt_sn
WHERE Area_code = Rec.area_code
and Cmcode = Rec. Cmcode
and ym = Is_ym;
END LOOP;
COMMIT;

--Error Handling section. Others represents an arbitrary error except for declarations. SQLERRM is a system built-in variable that holds the details of the current error.

EXCEPTION

When OTHERS Then
Vs_msg: = ' ERROR in xxxxxxxxxxx_p (' | | | is_ym| | '): ' | | SUBSTR (sqlerrm,1,500);

ROLLBACK;

--Record the current error into the log table.

INSERT into Log_info (proc_name,error_info,op_date)
VALUES (' xxxxxxxxxxx_p ', vs_msg,sysdate);
COMMIT;
RETURN;

END;

Oracle Stored Procedure Syntax
1. Judgment statement:
If comparison then BEGIN end; End If;

Copy CodeThe code is as follows:
Create or Replace procedure test (x in number) is
Begin
If x >0 Then
Begin
x: = 0-x;
End
End If;
If x = 0 Then
Begin
x: = 1;
End
End If;
End test;

2. For Loop
For ... LOOP
--EXECUTE statement
End LOOP;
(1) looping through cursors

Copy CodeThe code is as follows:
Create or Replace procedure test () as
Cursor cursor is the select name from student; Name varchar (20);
Begin
For name in cursor LOOP
Begin
Dbms_output.putline (name);
End
End LOOP;
End test;

(2) iterating through an array

Copy CodeThe code is as follows:
Create or Replace procedure test (Vararray in Mypackage.testarray) as
--(input parameter vararray is a custom array type, as defined in heading 6)
I number;
Begin
I: = 1; --The stored procedure array is starting from 1, and differs from Java, C, C + +, and other languages. Since there is no concept of an array in Oracle, the array is actually a
--table, where each array element is a record in the table, so iterating through the array is the equivalent of traversing from the first record in the table
For I in 1..vararray.count LOOP
Dbms_output.putline (' the No. ' | | | | | ' Record in Vararray is: ' | | Vararray (i));
End LOOP;
End test;

3. While Loop
While conditional statement LOOP

Copy CodeThe code is as follows:
Begin
End
End LOOP;
e.g
Create or Replace procedure test (I in number) as
Begin
While I < ten loops
Begin
i:= i + 1;
End
End LOOP;
End test;

4. Arrays
First, the concept is clear: Oracle is not an array concept, the array is actually a table, each array element is a record in the table.
When using an array, the user can use an array type that is already defined by Oracle, or can define an array type according to its own needs.
(1) Using Oracle's own array type
x array; --needs to be initialized when used
e.g:
Create or Replace procedure test (y out array) is
x array;
Begin
x: = new Array ();
Y: = x;
End test;
(2) custom array type(When customizing data types, it is recommended to create a package to facilitate management)
Create or Replace package MyPackage is
Public type declarations type info is record (name varchar (), y number);
Type Testarray is table of info index by Binary_integer;
--This declares a Testarray type data, which is actually a table that stores the info data type, and Testarray is a table with two fields, one name and one Y. It is important to note that index by Binary_integer compiles the table entry, or it can be written directly as: Type Testarray is
Table of info, if not written, requires initialization when using an array: Vararray Mypackage.testarray; Vararray: = new Mypackage.testarray ();
End Testarray;
5. Use of CursorsCursor in Oracle is useful for traversing query results in temporal tables. Its related methods and properties are also many, now only the use of the usual one or two introduction:
(1) Cursor-type cursors (cannot be used for parameter passing)

Copy CodeThe code is as follows:
Create or Replace procedure test () is
Cusor_1 Cursor is a select std_name from student where ...; Use of--cursor 1 cursor_2 Cursor;
Begin
Select Class_name into cursor_2 from class where ...; How to use--cursor 2
You can use the for × in cursor loop ... end loop; To implement a traversal of the cursor
End test;
(2) Sys_refcursor cursor, which is an oracle-defined cursor that can be passed with parameters
Create or Replace procedure test (Rscursor out sys_refcursor) is
Cursor sys_refcursor;
Name Varhcar (20);
Begin
Open cursor for select name from student where ...--sys_refcursor can only be opened and assigned by the Open method
LOOP
The fetch cursor into name--sys_refcursor can only be opened and traversed by a fetch into to the exit when Cursor%notfound; Three state properties can be used in--sys_refcursor:---%notfound (record information not found)%found (find record information)---%rowcount (then the row position that the current cursor points to)
Dbms_output.putline (name);
End LOOP;
Rscursor: = cursor;
End test;

Instance
Here is a simple example of how to use the stored procedure as described above:
Now assume that there are two tables, one is the Student score table (studnet), field: Stdid,math,article,language,music,sport,total,average,step
One is the student's Extracurricular score table (out_school), field: Stdid,parctice,comment
Each student's total and average scores are calculated automatically through the stored procedure, plus 20 points in the overall score if the student gets a rating of a in the extracurricular class.

Copy CodeThe code is as follows:

Create or Replace procedure Autocomputer (step in number) is
Rscursor Sys_refcursor;
Commentarray Mypackage.myarray;
Math number;
article number;
Language number;
Music number;
Sport number;
Total number;
Average number;
StdId varchar (30);
Record Mypackage.stdinfo;
I number;
Begin
I: = 1;
Get_comment (Commentarray); --Call the stored procedure named Get_comment () to get students ' extracurricular rating information
OPEN rscursor for select Stdid,math,article,language,music,sport from student t where t.step = step;
LOOP
Fetch rscursor into Stdid,math,article,language,music,sport; Exit when Rscursor%notfound;
Total: = math + article + language + music + sport;
For I in 1..commentarray.count LOOP
Record: = Commentarray (i);
If stdId = Record.stdid Then
Begin
If record.comment = ' A ' Then
Begin
Total: = all + 20;
Go to Next; --use go to jump out for loop
End
End If;
End
End If;
End LOOP;
<<continue>> average: = TOTAL/5;
Update Student T set t.total=total and t.average = average where t.stdid = stdId;
End LOOP;
End
End Autocomputer;
--Stored procedures for obtaining student comment information
Create or Replace procedure get_comment (Commentarray out Mypackage.myarray) is
RS Sys_refcursor;
Record Mypackage.stdinfo;
StdId varchar (30);
Comment varchar (1);
I number;
Begin
Open RS for select stdid,comment from Out_school
I: = 1;
LOOP
Fetch RS into stdid,comment; Exit when Rs%notfound;
Record.stdid: = StdId;
Record.comment: = comment;
Recommentarray (i): = record;
I:=i + 1;
End LOOP;
End Get_comment;
--Define array type MyArray
Create or Replace package mypackage is begin
Type Stdinfo is record (stdId varchar (), comment varchar (1));
Type MyArray is table of Stdinfo index by Binary_integer;
End MyPackage;

Oracle stored procedure Basic syntax stored procedure