Summary of Oracle stored procedure (a. Basic applications)

Jul 27 posted in Oracle (RSS), 205 views.

1, create a stored procedure

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

- A statement variable (variable type variable name)

begin

- The implementation of stored procedure body

end test;

Print out the input of time information

Eg:

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, variable assignment

Variable name: = value;

Eg:

create or replace procedure test (workDate in Date) is

x number (4,2);

begin

x: = 1;

end test;

3, to determine statement:

if comparison then begin end; end if;

Eg

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 cycle

For ... in ... LOOP

- The implementation of statements

end LOOP;

(1) cycle traversal cursor

create or replace procedure test () as

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) Ergodic circle array

create or replace procedure test (varArray in myPackage.TestArray) as

- (VarArray input parameters is an array of custom types, see the definition of the way the title of 6)

i number;

begin

i: = 1; - storage array is the beginning of the process is started from 1, with java, C, C + + and other languages. Because Oracle is not in the concept of the array, the array is actually a

- Table (Table), each array element is a record in the table, so when it is equivalent to traverse the array from the table records the beginning of the first traversal

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 cycle

while conditional statements LOOP

begin

end;

end LOOP;

Eg

create or replace procedure test (i in number) as

begin

while i <10 LOOP

begin

i: = i + 1;

end;

end LOOP;

end test;

6, the array

First of all, a concept clearly: Oracle is not in the concept of the array, the array is actually a table (Table), each array element is a record in the table.

The use of the array, the user can use the Oracle array type has been defined, or can be defined according to their own needs array type.

(1) the use of Oracle built-in array type

x array; - the need to use the need to initialize

eg:

create or replace procedure test (y out array) is

x array;

begin

x: = new array ();

y: = x;

end test;

(2) the definition of the array type (self-defined data type, it is proposed through the creation of Package way to manage)

Eg (from the definition of the use of the title refer to 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 statement here TestArray the type of data, in fact, Info storage for a data type of Table only, and is a form TestArray, there are two fields, one is

name, a y. It should be noted that here the use of the Index by binary_integer the preparation of the Table of the index, you can not write directly into: type TestArray is

table of info, so if we do not write on the need to use an array to initialize: varArray myPackage.TestArray; varArray: = new myPackage.TestArray ();

end TestArray;

7. Cursor in Cursor use Oracle is very useful for the temporary table traversal of query results. Methods and properties associated with many common usage is to do only one or two to introduce:

(1) Cursor cursor-type (can not be used for parameter passing)

create or replace procedure test () is

cusor_1 Cursor is select std_name from student where ...; - Cursor use 1 cursor_2 Cursor;

begin

select class_name into cursor_2 from class where ...; - Cursor used 2

Can use the For x in cursor LOOP .... end LOOP; to achieve the traversal of the Cursor

end test;

(2) SYS_REFCURSOR type cursor, the cursor is to pre-defined Oracle cursor, passing parameters can be made

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 only through the OPEN method to open and assignment

LOOP

fetch cursor into name - SYS_REFCURSOR only to open and fetch into ergodic exit when cursor% NOTFOUND; - SYS_REFCURSOR in three state properties can be used: ---% NOTFOUND (recorded information not found)% FOUND (to find records information) ---% ROWCOUNT (and then by the current cursor position to the line)

dbms_output.putline (name);

end LOOP;

rsCursor: = cursor;

end test;

Following a simple example to write to the above-mentioned use of stored procedure to do an application:

It is assumed that the existence of two forms, one is a student card (studnet), field: stdId, math, article, language, music, sport, total, average, step one is extra-curricular performance of students (out_school), the word paragraphs: stdId, parctice, comment

Through the stored procedure automatically calculates the total score of each student and the average score, at the same time, if the students in extra-curricular evaluation of curricula for the A, on the increase in the total score of 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 called get_comment () stored procedure to obtain the score of students in extra-curricular 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; - the use of go to out for recycling

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;

- Get students to comment on the information stored procedures

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;

- The definition of an 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;

This article comes from CSDN blog, reprint, please indicate the source: http://blog.csdn.net/squirrelRao/archive/2008/07/11/2639571.aspx