Introduction to basic syntax of Oracle stored procedures

Oracle stored procedure basic syntax Stored procedure
　1 CREATE OR REPLACE PROCEDURE Stored procedure name
　2 IS
　3 BEGIN
　4 NULL;
　5 END;

Line 1:
　CREATE OR REPLACE PROCEDURE is a SQL statement that instructs the Oracle database to create a stored procedure called skeleton, and overwrite it if it exists;
Line 2:
　The IS keyword indicates that a PL/SQL body will follow.
Line 3:
　The BEGIN keyword indicates the beginning of the PL/SQL body.
Line 4:
　The NULL PL/SQL statement indicates to do nothing. This sentence cannot be deleted because there needs to be at least one sentence in the PL/SQL body;
Line 5:
　The 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);

Begin 
Select count(*) into 变量1 from 表A where列名=param1； 

If (判断条件) then 
Select 列名 into 变量2 from 表A where列名=param1； 
Dbms_output。Put_line(‘打印信息'); 
Elsif (判断条件) then 
Dbms_output。Put_line(‘打印信息'); 
Else 
Raise 异常名（NO_DATA_FOUND）; 
End if; 
Exception 
When others then 
Rollback; 
End;

Notes:
1. Stored procedure parameters do not have a value range, in means incoming, out means output
The type can use any legal value in Oracle type.
2, The variable has a value range, followed by a semicolon
3. Before judging the statement, it is best to use the count(*) function to determine whether the operation record exists
4. Use select . . . into. . . Assign a value to a variable
5, use raise + exception name to throw an exception in the code

CREATE OR REPLACE PROCEDURE存储过程名 
( 
--定义参数 
is_ym IN CHAR(6) , 
the_count OUT NUMBER, 
) 
AS 
--定义变量 
vs_msg VARCHAR2(4000); --错误信息变量 
vs_ym_beg CHAR(6); --起始月份 
vs_ym_end CHAR(6); --终止月份 
vs_ym_sn_beg CHAR(6); --同期起始月份 
vs_ym_sn_end CHAR(6); --同期终止月份 
--定义游标(简单的说就是一个可以遍历的结果集) 

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

--用输入参数给变量赋初值，用到了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'); 

--先删除表中特定条件的数据。 

DELETE FROM 表名 WHERE ym = is_ym; 

--然后用内置的DBMS_OUTPUT对象的put_line方法打印出影响的记录行数，其中用到一个系统变量SQL%rowcount 

DBMS_OUTPUT.put_line('del上月记录='||SQL%rowcount||'条'); 
INSERT INTO表名(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(&#39;ins当月记录=&#39;||SQL%rowcount||&#39;条&#39;); 
--遍历游标处理后更新到表。遍历游标有几种方法，用for语句是其中比较直观的一种。 

FOR rec IN cur_1 LOOP 
UPDATE 表名 
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; 

--错误处理部分。OTHERS表示除了声明外的任意错误。SQLERRM是系统内置变量保存了当前错误的详细信息。 

EXCEPTION 

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

ROLLBACK; 

--把当前错误记录进日志表。 

INSERT INTO LOG_INFO(proc_name,error_info,op_date) 
VALUES(&#39;xxxxxxxxxxx_p&#39;,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 
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 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) Loop through the array

create or replace procedure test(varArray in myPackage.TestArray) as 
--( 输入参数varArray 是自定义的数组类型，定义方式见标题6) 
i number; 
begin 
i := 1; -- 存储过程数组是起始位置是从1 开始的，与java 、C 、C++ 等语言不同。因为在Oracle 中本是没有数组的概念的，数组其实就是一张 
-- 表(Table), 每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历 
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 
i:= i + 1; 
end; 
end LOOP; 
end test;

4 , Array
First of all, let us clarify a concept: Oracle does not have the concept of array. An array is actually a table (Table), Each array element is a record in the table.
When using arrays, users can use the array types already defined by Oracle, or they can define array types according to their own needs.
(1) Use Oracle's own array type
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 easier 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;
-- A TestArray type data is declared here. In fact, it is just a Table that stores the Info data type, and TestArray is a table with two fields, one is name. , one is y. It should be noted that Index by binary_integer is used here to compile the index item of the Table. You can also write it directly as: type without writing it. TestArray is
table of info. If you don’t write it, you need to initialize it when using the array: varArray myPackage.TestArray; varArray := new myPackage.TestArray();
end TestArray;
5. Use of Cursor Cursor in Oracle Is very useful for iterating over query results in a temporary table. There are also many related methods and attributes. Now I will only introduce the common usage:
(1) Cursor type cursor (cannot be used for parameter transfer)

create or replace procedure test() is 
cusor_1 Cursor is select std_name from student where ...; --Cursor 的使用方式1 cursor_2 Cursor; 
begin 
select class_name into cursor_2 from class where ...; --Cursor 的使用方式2 
可使用For x in cursor LOOP .... end LOOP; 来实现对Cursor 的遍历 
end test; 
(2)SYS_REFCURSOR 型游标，该游标是Oracle 以预先定义的游标，可作出参数进行传递 
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 只能通过OPEN 方法来打开和赋值 
LOOP 
fetch cursor into name --SYS_REFCURSOR 只能通过fetch into 来打开和遍历 exit when cursor%NOTFOUND; --SYS_REFCURSOR 中可使用三个状态属性： ---%NOTFOUND( 未找到记录信息) %FOUND( 找到记录信息) ---%ROWCOUNT( 然后当前游标所指向的行位置) 
dbms_output.putline(name); 
end LOOP; 
rsCursor := cursor; 
end test;

Example
Write a simple example below to illustrate the above. How to use stored procedures to make an application:
Now assume that there are two tables, one is the student performance table (studnet) , the fields are: stdId, math, article, language, music, sport, total, average, step
One is the student extracurricular performance table (out_school), the fields are: stdId, parctice, comment
The total score and average score of each student are automatically calculated through the stored procedure. At the same time, if the student receives an A in the extracurricular course, 20 points will be added 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); -- 调用名为get_comment() 的存储过程获取学生课外评分信息 
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; -- 使用go to 跳出for 循环 
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; 
-- 取得学生评论信息的存储过程 
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; 
-- 定义数组类型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;