Oracle stored procedure, declaration variable, for loop explanation, oracle Stored Procedure

Oracle stored procedure, declaration variable, for loop explanation, oracle Stored Procedure

Oracle stored procedures, declared variables, and for Loops

1. Create a stored procedure

Create or replace procedure test (var_name_1 in type, var_name_2 out type)

-- Declare a variable (variable name and variable type)

Begin

-- Execution body of the stored procedure

End test;

Print the input time information

E. g:

Create or replace procedure test (workDate in Date) is

Begin

Dbms_output.putline (& apos; The input date is: & apos; | to_date (workDate, & apos; yyyy-mm-dd & apos ;));

End test;

2. Variable assignment

Variable name: = value;

E. g:

Create or replace procedure test (workDate in Date) is

X number (4, 2 );

Begin

X: = 1;

End test;

3. Judgment statement:

If comparison then begin end; end if;

E. g

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;

4. 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 (& apos; The No. & apos; | I | & apos; record in varArray is: & apos; | varArray (I ));

End LOOP;

End test;

5. 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;

6. 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)

E. g (for custom usage, see title 4.2) 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 type, and TestArray is a table, there are two fields, one is

Name, one is 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;

7. Use of cursors

Cursor is very useful in Oracle and used to traverse the query results in a 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;

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, step: the student's out-of-class Partition Table (out_school) with the 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 = & apos; A & apos; 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;