Shulou(Shulou.com)06/02 Report--

What are the basic syntax and basic tutorials of oracle stored procedures? for this question, this article introduces the corresponding analysis and solutions in detail, hoping to help more partners who want to solve this problem to find a simpler and easier way.

A basic grammar 1. Basic structure CREATE OR REPLACE PROCEDURE stored procedure name (parameter 1 IN NUMBER, parameter 2 IN NUMBER) IS variable 1 INTEGER: = 0; variable 2 DATE;BEGINEND stored procedure name 2.SELECT INTO STATEMENT

Save the results of a select query into variables. Multiple columns can be stored in multiple variables at the same time. There must be one.

Record, otherwise throw an exception (throw NO_DATA_FOUND if there is no record)

Example:

BEGIN SELECT col1,col2 into variable 1, variable 2 FROM typestruct where xxx; EXCEPTION WHEN NO_DATA_FOUND THEN xxxx; END;... 3.IF to judge IF V_TEST=1 THEN BEGIN do something END; END IF;4.while loop WHILE V_TEST=1 LOOP BEGIN XXXX END; END LOOP;5. Variable assignment

V_TEST: = 123

6. Use for in, use cursor... IS CURSOR cur IS SELECT * FROM xxx; BEGIN FOR cur_result in cur LOOP BEGIN V_SUM: = cur_result. Listed as 1+cur_result. List 2 END; END LOOP; END;7. Cursor CURSOR C_USER (C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID; OPEN C_USER (variable value) with parameters; LOOP FETCH C_USER INTO variable name; EXIT FETCH Category USER% NOTFOUND; do something END LOOP; CLOSE Cure USERX 8. Use pl/sql developer debug

Establish a Test WINDOW after connecting to the database

Enter the code to call SP in the window, and F9 starts debug,CTRL+N step debugging

For a simple example, view the result through DBMS_OUTPUT

CREATE OR REPLACE PROCEDURE bb (lic_para IN VARCHAR2,out_para OUT VARCHAR2) AS temp VARCHAR2; BEGIN SELECT lic_no INTO temp FROM t_vehicle_info WHERE lic_no = lic_para; out_para:=temp; DBMS_OUTPUT.put_line (out_para); END bb

The following is the call:

Begin-- Call the procedure_ bb (lic_para = >: lic_para, out_para = >: out_para); end; basic tutorial CREATE OR REPLACE PROCEDURE stored procedure name ISBEGINNULL;END

Line 1: CREATE OR REPLACE PROCEDURE is a SQL statement that tells the Oracle database to create a stored procedure called skeleton and overwrite it if it exists

The line 2:IS keyword indicates that it is followed by a PL/ SQL body.

The line 3:BEGIN keyword indicates the beginning of the PL/ SQL body.

The line 4:NULL PL/SQL statement indicates that nothing is done, which cannot be deleted because at least one sentence is needed in the body of PL/ SQL

The line 5:END keyword indicates the end of the PL/ SQL body

Stored procedure creation syntax: 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 (judgment condition) then Select column name into variable 2 from table A where column name = param1; Dbms_output. Put_line ('print message'); Elsif (judgment condition) then Dbms_output. Put_line ('print message'); Else Raise exception name (NO_DATA_FOUND); End if;Exception When others then Rollback;End

Note:

1. Stored procedure parameters do not have a value range. In means incoming, and out means output.

Types can use legal types in any Oracle.

2. Variable with a range of values, followed by a semicolon

3. It is best to use the count (*) function to determine whether the operation record exists before judging the statement.

4. Use select. Into . Assign a value to a variable

5. Throw an exception in the code with the name of the raise+ exception

CREATE OR REPLACE PROCEDURE stored procedure name (--definition parameters is_ym IN CHAR (6), the_count OUT NUMBER,) AS-- definition variable vs_msg VARCHAR2 (4000);-- error message variable vs_ym_beg CHAR (6);-- start month vs_ym_end CHAR (6);-- end month vs_ym_sn_beg CHAR (6) -- same period start month vs_ym_sn_end CHAR (6);-- same period end month-- define cursors (simply a result set that can be traversed) CURSOR cur_1 IS SELECT. FROM . WHERE . GROUP BY . ; BEGIN-- assign initial values to variables with input parameters, using commonly used functions such as Oralce's 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 specific conditions in the table. DELETE FROM table name WHERE ym = is_ym;-then use the put_line method of the built-in DBMS_OUTPUT object to print out the number of affected rows of records, in which a system variable SQL%rowcount DBMS_OUTPUT.put_line ('del records last month =' | | SQL%rowcount | | 'bars') INSERT INTO table name (area_code,ym,CMCODE,rmb_amt,usd_amt) SELECT area_code,is_ym,CMCODE,SUM (rmb_amt) / 10000 usd_amt) / 10000 FROM BGD_AREA_CM_M_BASE_T WHERE ym > = vs_ym_beg AND ym 0 then begin x: = 0-x; end; end if; if x = 0 then begin x: = 1; end End if; end test;2, For cycle For. In... The LOOP-- execution statement end LOOP; (1) iterates through the 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) iterates through the array create or replace procedure test (varArray in myPackage.TestArray) as-(the input parameter varArray is a custom array type, as defined in title 6) i number; begin i: = 1 -- the starting position of the stored procedure array starts at 1, which is different from java, C, C++ and other languages. -because there is no concept of an array in Oracle, an array is actually a Table, and each array element is a record in the table, so traversing the array is equivalent to traversing for i in 1..varArray.count LOOP dbms_output.putline ('The No.' | | I | |' 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 < 10 LOOP begin iRank = I + 1; end; end LOOP; end test;4, array

First of all, let's be clear about a concept: there is no concept of arrays in Oracle. An array is actually a Table, and each array element is a record in the table.

When working with arrays, users can use array types that Oracle has already defined, or they can define array types according to their own needs.

(1) use the array type that comes with Oracle

X array;-initialization is required when using

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

(when customizing data types, it is recommended to create a Package for easy management.)

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; end TestArray

-- A TestArray type data is declared here, which is actually a Table that stores the Info data type, and TestArray is a table with two fields, one is name and the other is y. It should be noted that Index by binary_integer is used here to compile the index entry of the Table, or it can be written directly as: type TestArray is table of info. If you don't write, you need to initialize the array: varArray myPackage.TestArray; varArray: = new myPackage.TestArray ()

5. The use of cursors

Cursor in Oracle is very useful for traversing query results in temporary tables. There are also many related methods and attributes, and now we will only introduce one or two commonly used uses:

(1) Cursor cursors (cannot be used for parameter passing) create or replace procedure test () is cusor_1 Cursor is select std_name from student where.;-- the use of Cursor 1 cursor_2 Cursor; begin select class_name into cursor_2 from class where.;-- the use of Cursor 2-you can use For x in cursor LOOP. End LOOP; to implement the traversal of Cursor end test; (2) SYS_ REFCURSOR cursors

The cursor is predefined by Oracle and can be passed as a parameter.

Create or replace procedure test (rsCursor out SYS_REFCURSOR) iscursor SYS_REFCURSOR; name varhcar (20); begin OPEN cursor FOR select name from student where...-- SYS_REFCURSOR can only open and assign LOOP fetch cursor into name through the OPEN method-- SYS_REFCURSOR can only open and traverse exit when cursor%NOTFOUND through fetch into. -- three state attributes are available in SYS_REFCURSOR:--% NOTFOUND (record information not found)% FOUND (record information found) -% ROWCOUNT (then the row location pointed to by the current cursor) dbms_output.putline (name); end LOOP; rsCursor: = cursor End test

Write a simple example below to make an application to the use of the stored procedure mentioned above:

Suppose there are two tables, one is the student report form (studnet), the field is: stdId,math,article,language,music,sport,total,average,step

One is the extracurricular grade sheet (out_school), the field is: stdId,parctice,comment

The total score and average score of each student are automatically calculated through the stored procedure, and 20 points are added to the total score if the student gets a score of An in the extracurricular course.

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 a stored procedure called get_comment () to get students' extracurricular scoring 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;-- use go to to jump out of the for loop end; end if; end; end if End LOOP; 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 create or replace procedure get_comment (commentArray out myPackage.myArray) is rs SYS_REFCURSOR for obtaining student comment information Record myPackage.stdInfo; stdId varchar (30); comment varchar (1); i number; begin open rs for select stdId,comment from out_school iLOOP fetch rs into stdId,comment; exit when rs%NOTFOUND; record.stdId = 1; LOOP fetch rs into stdId,comment; exit when rs%NOTFOUND; record.stdId: = stdId; record.comment: = comment; recommentArray (I): = record; iRanger I + 1; end LOOP; end get_comment;-defines 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

A stored procedure (Stored Procedure) is a set of SQL statements that are compiled and stored in the database in order to complete a specific function in a large database system. The user executes it by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters).

Stored procedures and triggers play an important role in large database systems. Both stored procedures and triggers are a collection of SQL statements and process control statements.

The answers to the questions about the basic syntax and basic tutorials of oracle stored procedures are shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.