Oracle Stored Procedure Summary (first, basic application) _oracle

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 time information entered
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. Variable Assignment value
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 type 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 statement
End LOOP;
(1) Loop 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) looping through the array
Create or Replace procedure test (Vararray in Mypackage.testarray) as
--(input parameter vararray is a custom array type, as defined by heading 6)
I number;
Begin
I: = 1; --The stored procedure array starts at 1 and differs from languages such as Java, C, and C + +. Because there is no concept of an array in Oracle, the array is actually a
--table, each array element is a record in the table, so traversing the array is equivalent to starting 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;
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, array
First, it is clear that there is no concept of an array in Oracle, the array is actually a table (table), and each array element is a record in the table.
When using arrays, users can use an array type that Oracle has already defined, or you can define an array type to suit your needs.
(1) using an Oracle-band 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 types (custom data types are recommended to be implemented by creating package for ease of management)
e.g (custom use see heading 4.2) Create or replace package MyPackage is
--Public type declarations type info are record (name varchar, y number);
Type Testarray is table of the info index by Binary_integer; --a Testarray type of data is declared here, in fact, it is a table that stores the info data type, and Testarray is a chart with two fields, one is
Name, and one is Y. It is important to note that the index by Binary_integer for this table is used here, or it can be written directly: type Testarray is
Table of info, if not written, use an array to initialize: Vararray Mypackage.testarray; Vararray: = new Mypackage.testarray ();
End Testarray;
7. Use of CursorsCursor in Oracle is useful for traversing query results in a temporary table. Its related methods and properties are also many, now only used to do one or two introduction:
(1) Cursor-type cursors (cannot be used for parameter passing)
Create or Replace procedure test () is
Cusor_1 Cursor is select Std_name from student where ...; --cursor Use mode 1 cursor_2 cursor;
Begin
Select Class_name into cursor_2 from class where ...; How to use--cursor 2
You can use the for x in cursor loop ... end loop; To realize the traversal of the cursor.
End test;
(2) Sys_refcursor-type cursor, which is Oracle's predefined cursor, that can be passed by parameter
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 by fetch into; Three status properties can be used in--sys_refcursor:---%notfound (no record information found)%found (locate record information)---%rowcount (and then the row position to which the current cursor points)
Dbms_output.putline (name);
End LOOP;
Rscursor: = cursor;
End test;
Here is a simple example to apply to the usage of the stored procedure described above:
Now assume that there are two tables, one is the Student performance table (studnet), field: Stdid,math,article,language,music,sport,total,average,step One is a student's extracurricular performance table (Out_school ), field: Stdid,parctice,comment
The total score and average score of each student are calculated automatically through the stored procedure, and if the student gets a rating of a in the extracurricular course, add 20 points to the total score.
Create or Replace procedure Autocomputer (step into 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); --Invoke a stored procedure named Get_comment () to get students ' extracurricular grading 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; --Jump out for loop with Go
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;