Basic syntax for Oracle stored procedures

Oracle Stored Procedures Basic syntax Stored procedures
1 CREATE OR REPLACE PROCEDURE Stored procedure name
2 IS
3 BEGIN
4 NULL;
5 END;

Line 1:
CREATE OR REPLACE PROCEDURE is an SQL statement that notifies the Oracle database to create a stored procedure called skeleton and overwrite it if it exists.
Line 2:
The IS keyword indicates that an PL/SQL body will be followed.
Line 3:
The BEGIN keyword indicates the beginning of the PL/SQL body.
Line 4:
The NULL PL/SQL statement indicates that nothing is to be done and this sentence cannot be deleted because at least one sentence is required in the body of PL/SQL;
Line 5:
The END keyword indicates the end of the PL/SQL body
Stored procedure creation syntax:
create or 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);

 
Begin 
Select count(*) into  variable 1 from  table A where The column name =param1 ;  

If ( Judge conditions ) then 
Select  The column name  into  variable 2 from  table A where The column name =param1 ;  
Dbms_output . Put_line( 'Print information '); 
Elsif ( Judge conditions ) then 
Dbms_output . Put_line( 'Print information '); 
Else 
Raise  Exception names ( NO_DATA_FOUND ) ; 
End if; 
Exception 
When others then 
Rollback; 
End; 

Notes:
1. Stored procedure parameters have no value range, in means incoming, out means output
A type can use any valid type in Oracle.
2. Variable with value range, followed by semicolon
3. It is better to use count (*) function to determine whether the operation record exists before judging the statement
4, using select... into... Assign a value to a variable
5. raise+ exception name is used to throw exceptions in the code

 
CREATE OR REPLACE PROCEDURE Stored procedure name  
( 
-- Define the parameters  
is_ym IN CHAR(6) , 
the_count OUT NUMBER, 
) 
AS 
-- Define variables  
vs_msg VARCHAR2(4000); -- Error message variable  
vs_ym_beg CHAR(6); -- Starting in  
vs_ym_end CHAR(6); -- Termination of the month  
vs_ym_sn_beg CHAR(6); -- Contemporaneous beginning month  
vs_ym_sn_end CHAR(6); -- Corresponding termination month  
-- Define the cursor ( In a nutshell 1 A result set that can be traversed ) 

CURSOR cur_1 IS 
SELECT  .  
FROM  .  
WHERE  .  
GROUP BY  . ; 
BEGIN 

-- Give an initial value to a variable with an input parameter Oralce the SUBSTR TO_CHAR ADD_MONTHS 

TO_DATE  And so on.  
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 particular condition in the table.  

DELETE FROM  The name of the table  WHERE ym = is_ym; 

-- Then use the built-in DBMS_OUTPUT The object's put_line Method to print out the number of affected record lines 1 System variables SQL%rowcount 

DBMS_OUTPUT.put_line('del Last month's record ='||SQL%rowcount||' article '); 
INSERT INTO The name of the table (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 Records of the month ='||SQL%rowcount||' article '); 
-- The traversal cursor is processed and updated to the table. There are several ways to traverse a cursor for The statement is one of the more intuitive 1 Kind of.  

FOR rec IN cur_1 LOOP 
UPDATE  The name of the table  
SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn 
WHERE area_code = rec.area_code 
AND CMCODE = rec.CMCODE 
AND ym = is_ym; 
END LOOP; 
COMMIT; 

-- Error handling section. OTHERS Represents any error other than a declaration. SQLERRM It is the system built-in variable that holds the details of the current error.  

EXCEPTION 

WHEN OTHERS THEN 
vs_msg := 'ERROR IN xxxxxxxxxxx_p('||is_ym||'):'||SUBSTR(SQLERRM,1,500); 

ROLLBACK; 

-- Log 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:
then begin end; end if;

 
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; 

2. For cycle
For ... in ... LOOP
-- Execute statement
end LOOP;
(1) Loop 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) Iterate through several groups

 
create or replace procedure test(varArray in myPackage.TestArray) as 
--(  The input parameters varArray  Is a custom array type, defined in the title 6) 
i number; 
begin 
i := 1; --  The stored procedure array is the starting position from 1  At the beginning, and java  , C  , C++  And so on. Because in the Oracle  There is no notion of an array, but an array is 1 zhang  
--  table (Table),  Each array element is in the table 1 , so when iterating through groups, it is equivalent to the first from the table 1 The bar record starts traversal  
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 cycle
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 
i:= i + 1; 
end; 
end LOOP; 
end test; 

4, arrays,
First of all, it is clear that there is no concept of array in Oracle. Array is actually a table (Table), and each array element is a record in the table.
When working with arrays, users can use the array types already defined by Oracle, or they can define array types according to their needs.
(1) Use the array type provided by Oracle
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 (when customizing data types, it is recommended to create Package to facilitate 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;
-- The declared TestArray is actually an Table that stores the Info data type, and TestArray is a table with two fields, one name and one y. It should be noted that Index by binary_integer is used here to compile the index item of this Table. It can also be written as type TestArray is without writing
table of info, if you don't write it, you need to initialize the array: varArray ES169en.TestArray; varArray := new myPackage.TestArray();
end TestArray;
Cursor is very useful in Oracle for traversing query results in temporary tables. There are also a lot of related methods and attributes. Here we only introduce the common usages:
(1)Cursor cursor (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 of 1 cursor_2 Cursor; 
begin 
select class_name into cursor_2 from class where ...; --Cursor  Use mode of 2 
 You can use For x in cursor LOOP .... end LOOP;  To implement the Cursor  The traversal  
end test; 
(2)SYS_REFCURSOR  Type, the cursor is Oracle  A predefined cursor that can be passed as a 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  Only through OPEN  Method to open and assign  
LOOP 
fetch cursor into name --SYS_REFCURSOR  Only through fetch into  To open and traverse  exit when cursor%NOTFOUND; --SYS_REFCURSOR  Can be used in 3 State properties:  ---%NOTFOUND(  No record information was found ) %FOUND(  Find record information ) ---%ROWCOUNT(  Then the position of the row that the current cursor is pointing to ) 
dbms_output.putline(name); 
end LOOP; 
rsCursor := cursor; 
end test; 

The instance
Here is a simple example of how to apply the above stored procedure usage:
Now suppose there are two tables, one is the student transcript (studnet), field is: stdId, math, article, language, music, sport, total, average, step
One is the student extracurricular achievement table (out_school) with fields of stdId,parctice,comment
The stored procedure automatically calculates each student's total score and average score, and adds 20 points to the total score if the student has scored A in an 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, get_comment()  The stored procedure for obtaining student 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; --  use go to  Jump out of the for  cycle  
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; 
--  A stored procedure 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(30),comment varchar(1)); 
type myArray is table of stdInfo index by binary_integer; 
end myPackage;