[Go]oracle stored procedure, declare variable, for loop

Oracle stored procedures, declaring variables, for loops

1. Create a stored procedure

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

--Declaring variables (variable name variable type)

Begin

--The execution body of the stored procedure

End test;

Print out the input time information

e.g:

Create or Replace procedure test (workdate in Date) is

Begin

Dbms_output.putline (' The input date is:'| |to_date (workdate,'yyyy-mm-dd'));

End test;

2. Assigning values to variables

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

--EXECUTE statement

End LOOP;

(1) Looping through cursors

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

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.'| | I | | 'record in Vararray is:'| |vararray (i));

End LOOP;

End test;

5. While loop

While conditional statement LOOP

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;

6. 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 (custom data type, recommended by the way the package is created for easy management)

e.g (custom use see title 4.2) 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 and one

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

7. Use of Cursors

Cursor 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)

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 attributes can be used in--sys_refcursor:---%notfound (record information not found)%found (records information found) ---%rowcount (then the row position that the current cursor points to)

Dbms_output.putline (name);

End LOOP;

Rscursor: = cursor;

End test;

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), the field is: Stdid,math,article,language,music,sport,total,average,step One is the student's extracurricular performance 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.

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 = &apos; a&apos; 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;

This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/squirrelrao/archive/2008/07/11/2639571.aspx

Application:

Oracle cursors, stored procedures, for loops, loop-in

Create or Replace procedure Libsys.add_money (money out number) Ascursor cur is

Select amount from Libsys.money_record;

Begin

money:=0;

For amounts in cur

Loop

money:=money+amounts;

End Loop;

End Add_money;

[Go]oracle stored procedure, declare variable, for loop