Introduction to Oracle Stored procedures

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, overwriting it if it exists;       The line 2:is keyword indicates that a PL/SQL body is followed.       The line 3:begin keyword indicates the start of the PL/SQL body.       The line 4:null PL/SQL statements indicate that nothing is done, and this sentence cannot be deleted because there is at least one sentence in the PL/SQL body; The 5:end keyword indicates the end stored procedure creation syntax for the PL/SQL body: 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);       Beginselect COUNT (*) into variable 1 from table A where column name =param1; If (judging condition) then Select column name into Variable 2 from table a where column name =param1; Dbms_output.    Put_Line (' Print information '); elsif (judging condition) then dbms_output.    Put_Line (' Print information ');    Else Raise exception name (no_data_found); End if; Exception when others and then Rollback; End; Note: 1, stored procedure parameters do not take a range of values, in represents incoming, out indicates that the output type can use a legitimate type in any Oracle. 2, the variable with the value range, followed by a semicolon 3, before judging the statement is best to use the count (*) function to determine whether there is a record 4 of the operation, with SELECT.。。 Into ... Assign a value of 5 to a variable, throw an exception in the code with raise+ exception name create OR REPLACE procedure Stored procedure name (---Define parameter Is_ym in CHAR (6), the_count out number,) as--Define variable Vs_   MSG VARCHAR2 (4000);      --Error message variable Vs_ym_beg CHAR (6);      --Starting month Vs_ym_end CHAR (6);     --End Month Vs_ym_sn_beg CHAR (6);     --Vs_ym_sn_end CHAR (6) for the same period of the month;   --The month of the same period--define the cursor (simply a result set that can be traversed) cursor cur_1 is SELECT ...     From ...   WHERE ... GROUP by ...; BEGIN--Assigning an initial value to a variable with input parameters, uses a function commonly used by Oralce substr To_char add_months to_date. 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, ' yyyymm '), -12), ' yyyymm '); Vs_ym_sn_end: = To_char (Add_months (To_date (vs_ym_end, ' yyyymm '), -12), ' yyyymm '); --First delete the data for a specific condition in the table.   DELETE from table name WHERE ym = Is_ym; --then prints the number of rows affected by the Put_Line method of the built-in Dbms_output object, which uses a system variable Sql%rowcount dbms_output.put_line (' del last month record = ' | | sql%rowcount| | ' Article '); 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 month record = ' | | sql%rowcount| | ' Article '); --Update to table after traversing cursor processing. There are several ways to traverse a cursor, which is more intuitive with a for statement. 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.a Rea_code and Cmcode = Rec. Cmcode and ym = Is_ym; END LOOP; COMMIT; --Error Handling section. Others represents an arbitrary error except for declarations. SQLERRM is a system built-in variable that holds the details of the current error. EXCEPTION when OTHERS and vs_msg: = ' ERROR in xxxxxxxxxxx_p (' | | is_ym| | '): ' | |    SUBSTR (sqlerrm,1,500);    ROLLBACK;    --Record the current error into 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 a 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 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) Loop through 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 Nu Mber;  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, and each element of the array is a record in the table, so traversing the array is equivalent to starting the first record in the table to traverse the for I in 1..vararray.count loop Dbms_output.putline (' the No. ' | | | | | ' Record in Vararray is: ' | |     Vararray (i)); End LOOP; End test; 3, while loop, while conditional statement loop begin end; End LOOP; E.g create or replace procedure test (I in number) as begin while I < LOOP begin i:= i + 1; End  End LOOP; End test; 4, the array first clear a concept: Oracle is not an array of concepts, the array is actually a table (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 an array type of Oracle-band x array; --requires initialization of e.g:create or replace procedure test (Y out arra) when usedY) is x array; Begin x: = new Array (); Y: = x; End test; (2) Custom array types (custom data types are recommended by creating a package for ease of administration) Create or replace mypackage is public type declarations Ty   PE 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, one name and one 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 you do not write, you need to initialize the array when you use it: Vararray Mypackage.testarray; Vararray: = new Mypackage.testarray (); End Testarray; 5. Cursor use in Oracle cursor is useful for traversing query results in temporal tables. Its related methods and properties are also many, now only for common usage do one or two introduction: (1) Cursor type cursor (cannot be used for parameter passing) Create or replace procedure test () is cusor_1 cursor is select Std_n  Ame from student where ...; Use of--cursor 1 cursor_2 Cursor;  Begin select Class_name into Cursor_2 from class where ...; How to use the--cursor 2 you can use the for x in Cursor loop .... end Loop; To implement the traversal of the cursor end test; (2) Sys_refcursor cursor, which is Oracle's pre-definedParameters can be passed to create or replace procedure test (Rscursor out sys_refcursor) is the cursor sys_refcursor; Name Varhcar (20);   Begin open Cursor for select name the from student where ...--sys_refcursor can only be opened and assigned by opening method. LOOP fetch cursor into name              --sys_refcursor can only open and traverse exit when cursor%notfound by fetch into;                                         Three state attributes can be used in--sys_refcursor:---%notfound (record information not found)%found (records information found) The---%rowcount (then the row position that the current cursor points to) Dbms_output.putline (name); End LOOP; Rscursor: = cursor; End test; Example below is a simple example of the use of stored procedures described above to make an application: 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 automatically calculates the total and average scores of each student through the stored procedure, and, if the student is evaluated in an extracurricular course A, just add 20 points to the total score. 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 scoring information OPEN rscursor for select Stdid,math,article,language,music,sport from student t wher e 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 ' and begin total: = all + 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; --Get Student comment information stored procedure 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 (1), comment varchar) ; Type MyArray is table of Stdinfo index by Binary_integer; End Mypackage;byebye