Oracle之存储过程

1.基本介绍

所谓存储过程(Stored Procedure)，就是一组用于完成特定数据库功能的SQL语句集，该SQL语句集经过编译后存储在数据库系统中。在使用时候，用户通过指定已经定义的存储过程名字并给出相应的存储过程参数来调用并执行它，从而完成一个或一系列的数据库操作。

2.语法结构

2.1.基本语法

Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。

--创建语法
CREATE [OR REPLACE] PROCEDURE 存储过程名（PARAM1 IN TYPE，PARAM2 OUT TYPE）
AS   --as和is任选一个，在这没有区别
变量1 类型（值范围）;  
变量2 类型（值范围）;

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;

案例演示：

-- 创建存储过程

create or replace procedure test1 as 
begin
  dbms_output.put_line('hello world');
end;
-- 调用存储过程
call test1();
-- 删除存储过程
drop procedure test1;

2.2.变量

Oracle存储过程中的变量大致可以分为普通数据类型和特殊变量类型。普通数据类型就是Oracle中的基本数据类型，如：NUMBER、CHAR 、VARCHAR2 、VARCHAR、NCHAR 、NVARCHAR2 、LONG 、DATE 、TIMESTAMP等等；而特殊变量类型分为引用型变量和记录型变量。引用型变量的数据类型取决表中的数据类型；记录型变量不是接受一个值，是一行值。

声明变量语法：变量名 变量类型（变量长度）

---普通变量        
v_name     varchar2(20);
---引用型变量       
v_income   emp_test.income%TYPE;
---记录型变量，表示变量v_emp存的是表中emp_test一整行的数据       
v_emp      emp_test%ROWTYPE

2.2.1.普通变量

当我们想使用普通变量的时候，需要先给普通变量命名，制定变量的类型和长度。在赋值的时候可以在声明变量的时候直接赋值:=，也可以在程序中赋值，通过select ... into语句赋值。

create or replace procedure test1 as
-- 定义变量
v_number varchar2(20);       --工号
v_salary int  := 3000;       --声明变量直接赋值
v_depart varchar2(50);       --部门
begin
  -- 通过SELECT语句给变量赋值
  select '5号部门','P00007' into v_depart,v_number from dual;
​
  -- 打印输出
  dbms_output.put_line('工号：'||v_number); 
  dbms_output.put_line('工资：'||v_salary);
  dbms_output.put_line('部门：'||v_depart);
end ;

2.2.2.引用变量

引用变量变量的类型和长度取决于表中字段的类型和长度，通过表名.列名%TYPE指定变量的类型和长度。

例如： v_sid t_student.sid%TYPE

create or replace procedure test2 as
-- 定义变量
v_sid t_student.sid%TYPE;运行代码
v_sname t_student.sname%TYPE;
begin
  -- 通过SELECT语句给变量赋值
  select sid,sname into v_sid,v_sname from t_student where sid='01';
​
  -- 打印输出
  dbms_output.put_line('学号：'||v_sid||',姓名：'||v_sname); 
end;

2.2.3.记录型变量

记录型变量接受表中的一整行记录，语法：变量名称 表名%ROWTYPE

create or replace procedure test3 as
  -- 定义变量，记录型变量，接受表中的一整行记录
  v_stu t_student%ROWTYPE;
begin
  select * into v_stu from t_student where rownum=1;
  dbms_output.put_line('姓名：'||v_stu.sid||',年龄：'||to_char(v_stu.sage,'yyyy-MM-dd'));
end;

2.3.参数

基本语法：

create procedure 名称([IN|OUT|INOUT] 参数名 参数数据类型 )
begin
    ...
end

参数的几种类型:

• IN是参数的默认模式。

• OUT类型参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程。

• IN OUT表示高参数可以向该过程中传递值，也可以将某个值传出去。

2.3.1.传入参数IN

IN是参数的默认模式，表示该参数的值必须在调用存储过程时指定。IN类型参数一般只用于传入，在调用过程中一般不作为修改和返回。

CREATE OR REPLACE PROCEDURE test_sid(id varchar2) as
  v_name VARCHAR2(100); 
begin
  select sname into v_name from t_student where sid = id;
  dbms_output.put_line('姓名：'||v_name);
end;

2.3.2.传出参数OUT

OUT类型参数只能接收赋值，不能给其他变量赋值。输出模式的参数，用于输出值，会忽略传入的值，在子程序内部可以对其进行修改。

-- 创建存储过程

CREATE OR REPLACE PROCEDURE test_out(
       id in varchar2,
       v_sname out t_student.sname%TYPE) as
begin
       select sname into v_sname from t_student where sid = id;
       dbms_output.put_line('姓名：'||v_sname);
end;

-- 调用存储过程，并得到out输出参数值并打印
declare
  sname varchar2(200);
begin
  test_out('01',sname);
  dbms_output.put_line(sname);
end;

2.3.3.可变参数IN OUT

与OUT类型相比不同是默认初始化参数不为NULL，传的是什么就是什么。

-- 创建存储过程

create or replace procedure test_in_out(v_sid in out number)
is
begin
  dbms_output.put_line(v_sid);
  v_sid:=10;
end;

-- 调用存储过程
declare
  sid number:=1;
begin
  test_in_out(sid);
  dbms_output.put_line(sid);
end;

2.4.IF结构

所谓选择结构，就是指程序根据具体条件表达式来执行一组命令的结构。命令格式如下：

IF (条件表达式1) THEN
   {语句序列1;}
[ELSIF (条件表达式2) THEN
   {语句序列2;}]
[ELSE 
   {语句序列3;}]
END IF;

需要注意的是，上述命令格式中ELSIF的拼写里只有一个E，不是ELSEIF，并且没有空格。可以把这个语法分为3种情况来理解：

• 第一种情况：IF ... THEN 语句

• 第二种情况：IF ... THEN ... ELSE 语句

• 第三种情况：IF ... THEN ... ELSEIF 语句

案例演示：

-- 创建存储过程，根据输入参数性别来判断查询数据的结果

create or replace procedure test_if_sex(v_sex varchar2) as 
  my_name varchar2(200);
begin
  if(v_sex='男') then 
     select sname into my_name from t_student where sid='01';
  else
     select sname into my_name from t_student where sid='02';
  end if;
  dbms_output.put_line('性别：'||v_sex||'，姓名：'||my_name);
end;

-- 调用存储过程

call test_if_sex('男');
call test_if_sex('女');

2.5.循环

所谓循环结构，即指程序按照指定的逻辑条件循环执行一组命令的结构。在Oracle中的循环结构语句大致分为三种：While、For和Loop。

2.5.1.While语句

基本语法结构：

while(条件) loop
	...
end loop;

案例演示：

create or replace procedure test_while as 
  i int:=0;
begin 
  while i<10 loop
    -- 一定要记得累加值，不然死循环了
    i:=i+1;
    dbms_output.put_line(i);
  end loop;
end;

2.5.2.Loop语句

基本语法结构：

LOOP
  Exit When(退出条件);
  ...
END LOOP

案例演示：

create or replace procedure test_loop as 
  i number:=0;
begin
  loop
    exit when(i>10);
    dbms_output.put_line(i);
    i:=i+1;
  end loop;
end;

2.5.3.For语句

基本语法结构：

for （） in （）loop
  	...
end loop;

案例演示：

create or replace procedure test_for as 
  i number:=0;
begin
  for i in 1..5 loop
    dbms_output.put_line(i);
  end loop;
end;

3.综合案例

1.使用if结构，传入不同参数，完成对t_student表中的数据修改操作

2.使用循环结构（任意一种），完成对t_student表的循环数据添加

3.输入学生ID，输出该学生的课程成绩、平均成绩和总成绩信息