Oracle存储过程包含三部分：

• 过程声明
• 执行过程部分
• 存储过程异常（可写可不写，要增强脚本的容错性和调试的方便性那就写上异常处理）

无参存储过程

CREATE OR REPLACE PROCEDURE demo AS/IS
	变量2 DATE;
	变量3 NUMBER;
BEGIN
	--要处理的业务逻辑
	EXCEPTION    --存储过程异常
END

带参数的存储过程

CREATE OR REPLACE PROCEDURE 存储过程名称(param1 student.id%TYPE)
AS/IS
name student.name%TYPE;
age number :=20;
BEGIN
  --业务处理.....
END

带参数的存储过程并且进行赋值

CREATE OR REPLACE PROCEDURE 存储过程名称(
       s_no in varchar,
       s_name out varchar,
       s_age number) AS
total NUMBER := 0;
BEGIN
  SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age;
  dbms_output.put_line('符合该年龄的学生有'||total||'人');
  EXCEPTION
    WHEN too_many_rows THEN 
    DBMS_OUTPUT.PUT_LINE('返回值多于1行'); 
END

--其中参数IN表示输入参数，是参数的默认模式。
--OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。
--OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程
--IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去

测试脚本

--新建存储过程
create or replace procedure jony_demo(param1 jony_outinfo.policy_no%type,
s_age out number) as
policy_no jony_outinfo.policy_no%type;
age number :=20;
begin
  select t.policy_no into policy_no from jony_outinfo t where t.uniqueid = '1';
  s_age :=age;
  dbms_output.put_line('查询到的保单号为：'||policy_no);
  return;
  exception
    when too_many_rows then 
      dbms_output.put_line('返回值多于1行');
end jony_demo;
--测试脚本
declare
  s_age number;
begin
  -- Call the procedure
  jony_demo('123',s_age);
  dbms_output.put_line('查询到的age为：'||s_age);
end;

存储过程语法

运算符

:=	赋值	s := 0;
||	字符串连接	‘hello’||’world’

SELECT INTO STATEMENT语句

选择语句

• a.IF..END IF
• IF..ELSE..END IF

循环语句

• 基本循环

LOOP
  IF 表达式 THEN
    EXIT;
  END IF
END LOOP;

• while循环

WHILE 表达式 LOOP
  dbms_output.put_line('haha');
END LOOP;

• for循环

FOR a in 10 .. 20 LOOP
  dbms_output.put_line('value of a: ' || a);
END LOOP;

实例

create or replace procedure jony_demo(param1 jony_outinfo.policy_no%type,
s_age out number) as
policy_no jony_outinfo.policy_no%type;
bank_no jony_outinfo.bank_no%type;
age number :=20;
begin
  ---select into 
  select t.policy_no,t.bank_no into policy_no,bank_no from jony_outinfo t where t.uniqueid = '1';
  s_age :=age;
  dbms_output.put_line('查询到的序号号为：'||policy_no||'银行编码：'||bank_no);
  --选择语句
  if bank_no like '%JS%' then
    dbms_output.put_line('江苏银行');
  else 
    dbms_output.put_line('不是江苏银行');  
  end if;
  --循环语句
  /*
  loop
    if age = 21 then
       dbms_output.put_line('loop 退出');
       exit;
    end if;
  end loop;
  */
  while age < 21 loop
    dbms_output.put_line('age:'||age);
    age :=age +1;
  end loop;
  
  for age in 10 .. 23 loop
    dbms_output.put_line('age:'||age);
  end loop;
 
  dbms_output.put_line('age last value:'||age);
  
  exception
    when too_many_rows then 
      dbms_output.put_line('返回值多于1行');
end jony_demo;

游标

定义

Oracle会创建一个存储区域，被称为上下文区域，用于处理SQL语句，其中包含需要处理的语句，例如所有的信息，行数处理，等等。 游标是指向这一上下文的区域。 PL/SQL通过控制光标在上下文区域。游标持有的行(一个或多个)由SQL语句返回。行集合光标保持的被称为活动集合。

%FOUND	如果DML语句执行后影响有数据被更新或DQL查到了结果，返回true。否则，返回false
%NOTFOUND	如果DML语句执行后影响有数据被更新或DQL查到了结果，返回false。否则，返回true。
%ISOPEN	游标打开时返回true，反之，返回false。
%ROWCOUNT	返回DML执行后影响的行数。

create or replace procedure JONY_DEMO_02 is
v_jony_detail jony_outinfo%rowtype;
i number :=0;
Cursor c_jony IS
select t.*
from jony_outinfo t
where t.uniqueid in ('1', '2', '3');
begin
  open c_jony ;
  Fetch c_jony 
   into v_jony_detail;
  while c_jony %Found LOOP
      dbms_output.put_line('序号：'||v_jony_detail.policy_no||',姓名为：'||v_jony_detail.cust_name);
      update jony_outinfo t set t.premiumdue_st = ADD_MONTHS(t.premiumdue_st,1),t.premiumdue_end = ADD_MONTHS(t.premiumdue_end,1)
      where t.uniqueid = v_jony_detail.uniqueid;
      i := i+1;
      if i > 2 then 
        commit;
        i := 0;
      end if;    
      Fetch c_jony 
      Into v_jony_detail;
  END LOOP;
  close c_jony ;
end JONY_DEMO_02;

存储过程进阶

Pl/SQL与SQL引擎之间的通信则称之为上下文切换，过多的上下文切换将带来过量的性能负载。最终导致效率降低，处理速度缓慢。 从Oracle8i开始PL/SQL引入了两个新的数据操纵语句：FORALL、BUIK COLLECT，这些语句大大滴减少了上下文切换次数（一次切换多次执行），同时提高DML性能。

BUIK COLLECT

• SELECT INTO

SELECT s_name BULK COLLECT INTO arr_name FROM s_age < 10;

• FETCH INTO

FETCH cur1 BULK COLLECT INTO var_collect [LIMIT rows]

• RETURNING

DMLStatement
RETURNING field BULK COLLECT INTO var_field;

FORALL

FORALL index IN bounds [SAVE EXCEPTIONS]     
     sqlStatement;

实例

• 存储过程1

create or replace procedure JONY_DEMO_03 is
CURSOR CUR_policy_no IS select t.policy_no from jony_outinfo t where t.uniqueid in ('1', '2', '3');
    --声明了一个联合数组类型
    TYPE REC_policy_no IS VARRAY(10) OF VARCHAR2(64);
    policys REC_policy_no;
BEGIN
  OPEN CUR_policy_no;
  WHILE (TRUE) LOOP
    FETCH CUR_policy_no BULK COLLECT INTO policys LIMIT 10;
    --下标从1开始
    FORALL i IN 1..policys.count SAVE EXCEPTIONS
      update jony_outinfo t set t.premiumdue_st = ADD_MONTHS(t.premiumdue_st,1),t.premiumdue_end = ADD_MONTHS(t.premiumdue_end,1)
      where t.policy_no=policys(i);
    COMMIT;
    EXIT WHEN CUR_policy_no%NOTFOUND OR CUR_policy_no%NOTFOUND IS NULL;
  END LOOP;
  dbms_output.put_line('finished');
end JONY_DEMO_03;

• 存储过程2（ROWID）

如果student表没有主键

ROWNUM是伪列，每次获取结果后，然后在结果集里会产生一列，从1开始排，每次都是从1开始排。

ROWID在每个表中，每条记录的ROWID都是唯一的。在这种情况下，我们可以用ROWID。但要注意的是，ROWID是一个类型，注意它和VARCHAR2之间的转换。有两个方法：ROWIDTOCHAR（）是把ROWID类型转换为CHAR类型；CHARTOROWID()是把CAHR类型转换为ROWID类型。

CREATE OR REPLACE PROCEDURE process_student1 AS
    CURSOR CUR_STUDENT IS SELECT ROWIDTOCHAR(ROWID) FROM student;
    TYPE REC_STUDENT IS VARRAY(100000) OF VARCHAR2(16);
    students REC_STUDENT;
BEGIN
  OPEN CUR_STUDENT;
  WHILE (TRUE) LOOP
    FETCH CUR_STUDENT BULK COLLECT INTO students LIMIT 100000;
    FORALL i IN 1..students.count SAVE EXCEPTIONS
      UPDATE student SET s_grade=s_grade+1 WHERE ROWID=CHARTOROWID(students(i));
    COMMIT;
    EXIT WHEN CUR_STUDENT%NOTFOUND OR CUR_STUDENT%NOTFOUND IS NULL;
  END LOO;
  dbms_output.put_line('finished');
END;

--我们首先查到记录的ROWID并把它转换为CHAR类型，存放到游标CUR_STUDENT里，
--再每次抓取10万条数据赋值给数组进行更新，更新语句的WHERE条件时，又把数组元素是CAHR类型的rowid串转换为ROWID类型。

轮询查询T_CODE

create or replace procedure jony_demo_02(branch_code in varchar,output out Varchar2) is
v_policyBranchT9 VARCHAR2(20);
sub_num number :=15;
begin
  WHILE sub_num >=12 ANd v_policyBranchT9 is null  LOOP
    SELECT max(SUN_TCODE)
          INTO V_POLICYBRANCHT9
          FROM TBUFF_TCODEMAP T
         WHERE T.FF_NEWTYPE = 'T9'
           AND T.STATUS = 'Active'
           AND T.FF_TCODE = SUBSTR(branch_code,0,sub_num);
  dbms_output.put_line('T9='||v_policyBranchT9);
  sub_num := sub_num - 3;
  END LOOP;
  IF v_policyBranchT9 is null THEN
     output :='ERROR：T9 查询为空';--||in_dis_id;
    RETURN;
  END IF;
end jony_demo_02;



----Test
declare
  out_info VARCHAR2(300); 
begin
  -- Call the procedure
  jony_demo_02('001005555555555',out_info);
  dbms_output.put_line(out_info);
end;

如何在PL/SQL中完成存储过程的调试？

在左侧列表中，你先点add debug informaintion（添加调试信息），点击 package—》 recompile , package body —》recompile —- 》 view package and body —> recompile — > test — > 等一会在执行测试