ORACLE Stored Procedure learning, oracle Stored Procedure

ORACLE Stored Procedure learning, oracle Stored Procedure

Stored Procedure
1 create or replace procedure stored PROCEDURE name

2 IS

3 BEGIN

4 NULL;

5 END;

 

Row 1:

Create or replace procedure is an SQL statement that notifies the Oracle database to CREATE a stored PROCEDURE called skeleton, and overwrites it if it exists;

Row 2:

The IS keyword indicates that a PL/SQL body will be followed.

Row 3:

The BEGIN keyword indicates the start of the PL/SQL body.

Row 4:

The null pl/SQL statement indicates that nothing is done. This statement cannot be deleted because at least one sentence is required in the PL/SQL body;

Row 5:

The END keyword indicates the END 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 );

BEGIN
Select count (*) INTO variable 1 FROM Table a where column name = PARAM1;
If (condition) then
Select column name into variable 2 from Table A where column name = param1;
Dbms_output. Put_line ('print info ');
Elsif (Judgment condition) then
Dbms_output. Put_line ('print info ');
Else
Raise Exception name (NO_DATA_FOUND );
End if;
Exception
When others then
Rollback;
End;

 

Note:

1. stored procedure parameters do not have a value range. in indicates input, and out indicates output.

Type can use the legal type in any Oracle.

2. The variable has a value range followed by a semicolon

3. Use the count (*) function to determine whether the operation record exists before determining the statement.

4. select... Into... Assign values to variables

5. If an exception is thrown in the code, use raise + Exception name.

 

Create or replace procedure stored PROCEDURE name
(
-- Define parameters
Is_ym in char (6 ),
The_count out number,
)
AS
-- Define variables
Vs_msg VARCHAR2 (4000); -- error message variable
Vs_ym_beg CHAR (6); -- start month
Vs_ym_end CHAR (6); -- end month
Vs_ym_sn_beg CHAR (6); -- start month of the same period
Vs_ym_sn_end CHAR (6); -- end month of the same period

-- Define a cursor (simply put, a result set that can be traversed)

CURSOR cur_1 IS
SELECT...
FROM...
WHERE...
Group ...;

BEGIN

-- Use the input parameter to assign the initial value to the variable, and use the SUBSTR TO_CHAR ADD_MONTHS of Oralce.

 

TO_DATE and other common functions.
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, 'yyymm'),-12), 'yyymm ');
Vs_ym_sn_end: = TO_CHAR (ADD_MONTHS (TO_DATE (vs_ym_end, 'yyymm'),-12), 'yyymm ');

-- Delete data with specific conditions in the table first.

Delete from table name WHERE ym = is_ym;

-- Then use the put_line method of the built-in DBMS_OUTPUT object to print the number of affected records. One system variable SQL % rowcount is used.

DBMS_OUTPUT.put_line ('del last month record = '| SQL % rowcount |' bar ');

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 Record of the current month = '| SQL % rowcount |' bar ');
-- The traversal cursor is processed and updated to the table. You can use the for statement to traverse a cursor in several ways.

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 indicates any errors except declarations. SQLERRM is a built-in variable that saves detailed information about the current error.

EXCEPTION

WHEN OTHERS THEN
Vs_msg: = 'error IN xxxxxxxxxxx_p ('| is_ym |'): '| SUBSTR (SQLERRM, 1,500 );

ROLLBACK;

-- Record the current error to 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;

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... in... LOOP

-- Execute the statement

End LOOP;

(1) looping cursor

Create or replace procedure test ()

Cursor cursor is select name from student; name varchar (20 );

Begin

For name in cursor LOOP

Begin

Dbms_output.putline (name );

End;

End LOOP;

End test;

(2) traverse arrays cyclically

Create or replace procedure test (varArray in myPackage. TestArray)

-- (The input parameter varArray is a custom array type. For the definition method, see heading 6)

I number;

Begin

I: = 1; -- the starting position of the stored procedure array is from 1, which is different from java, C, C ++ and other languages. Because there is no array concept in Oracle, the array is actually

-- Table, each array element is a record in the Table. Therefore, when traversing an array, it is equivalent to traversing from the first record in the Table.

For I in 1 .. varArray. count LOOP

Dbms_output.putline ('the No. '| I | 'record in varArray is:' | varArray (I ));

End LOOP;

End test;

3. While Loop

While Condition Statement LOOP

Begin

End;

End LOOP;

E. g

Create or replace procedure test (I in number)

Begin

While I <10 LOOP

Begin

I: = I + 1;

End;

End LOOP;

End test;

4. Array

First, let's clarify a concept: Oracle does not have an array. arrays are actually a Table, and each array element is a record in the Table.

When using arrays, you can use the array types defined in Oracle, or you can define the array types as needed.

(1) Use the array type that comes with Oracle

X array; -- Initialization is required for use.

E. g:

Create or replace procedure test (y out array) is

X array;

Begin

X: = new array ();

Y: = x;

End test;

(2) custom array types (we recommend that you create a Package to facilitate management of custom data types)

Create or replace package myPackage is

Public type declarations type info is record (name varchar (20), y number );

Type TestArray is table of info index by binary_integer;

-- A TestArray data type is declared here. It is actually a Table that stores Info Data. TestArray is a Table with two fields: name and y. Note that Index by binary_integer is used to compile the Index of the Table. You can also write it as type TestArray is.

Table of info. If no data is written, Initialization is required when array is used: varArray myPackage. TestArray; varArray: = new myPackage. TestArray ();

End TestArray;

5. Using Cursor in Oracle for Cursor is very useful and used to traverse the query results in the temporary table. There are also many related methods and attributes. Currently, we will only introduce the common usage:

(1) Cursor type Cursor (cannot be used for parameter transfer)

Create or replace procedure test () is

Cusor_1 Cursor is select std_name from student where...; -- usage of Cursor 1 cursor_2 Cursor;

Begin

Select class_name into cursor_2 from class where...; -- usage of Cursor 2

You can use For x in cursor LOOP... end LOOP; to traverse Cursor.

End test;

(2) SYS_REFCURSOR type cursor, which is a pre-defined Oracle cursor and can be passed through 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 through the OPEN Method

LOOP

Fetch cursor into name -- SYS_REFCURSOR can only open and traverse exit when cursor % NOTFOUND through fetch into; -- SYS_REFCURSOR can use three State attributes: --- % NOTFOUND (record information not found) % FOUND (record information FOUND) --- % ROWCOUNT (then the row position pointed to by the current cursor)

Dbms_output.putline (name );

End LOOP;

RsCursor: = cursor;

End test;

 

 

 

Instance

The following is a simple example to apply the usage of the above stored procedure:

Assume that two tables exist, one of which is studnet. The fields are stdId, math, article, language, music, sport, total, average, and step.

One is the out-of-class student quota table (out_school) with the following fields: stdId, parctice, and comment.

The storage process automatically calculates the total score and average score of each student. At the same time, if the score obtained by the student in the extracurricular course is A, the total score is increased to 20 points.

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 obtain the student's out-of-class 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: = total + 20;

Go to next; -- use go to jump out of the 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 procedure 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 the array type myArray

Create or replace package myPackage is begin

Type stdInfo is record (stdId varchar (30), comment varchar (1 ));

Type myArray is table of stdInfo index by binary_integer;

End myPackage;