Oracle PL/SQL (10) - 存储过程的创建和调用

子程序包括存储过程和函数。
子程序包括：
　　1、声明部分：声明部分包括类型、游标、常量、变量、异常和嵌套子程序的声明。这些项都是局部的，在退出后就不复存在。
　　2、可执行部分：可执行部分包括赋值、控制执行过程以及操纵ORacle数据的语句。
　　3、异常处理部分：　　异常处理部分包括异常处理程序，负责处理执行存储过程中出现的异常。

存储过程是执行某些操作的子程序，是执行特定任务的模块。从根本上讲，存储过程就是PLSQL块，它可以被赋予参数，存储在数据库中，然后由一个应用程序或其他PLSQL程序调用。

存储过程存放在数据库服务器中，而且是已经编译好的，且在服务器端执行，因此执行效率高；
存储过程把商业逻辑固化在存储过程中，还隐藏了数据，因此增强了数据安全性；
存储过程增加了程序开发的灵活性和模块化；
存储过程是存储在服务器端，且在服务器端执行，减少了网络通信量。

create or replace procedure 存储过程名称
(
--定义输入、输出参数--
参数名1 in 参数类型,
参数名2 in 参数类型,
参数名3 in 参数类型,
参数名4 out 参数类型
)
as
--定义变量--
--变量名 变量数据类型；如：
-- numCount integer; 
begin   
    --处理方法-
end;

CREATE OR REPLACE PROCEDURE 存储过程名(param1 in type,param2 out type)
　IS
　变量1 类型(值范围);
　变量2 类型(值范围);　　
　BEGIN
　　　select count(*) into 变量1 from 表名 where 列名=param1;
　　　if (判断条件) then
　　　　　select 列名 into 变量2 from 表名 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 pro_1(id varchar2)
is
   name varchar2(20); -- 声明一个变量
begin
   -- 以下就是存储过程的主体部分
   dbms_output.put_line('id:' || id || ' name:' || name);
end;

1、create or replace表示如果这个存储过程不存在就创建一个新的存储过程，而如果这个存储过程存在了，就覆盖这个存储过程；
2、id varchar2是传递的一个参数，默认是IN类型。
3、在存储过程（PROCEDURE）和函数(FUNCTION)中使用IS和AS并没有太大区别，在视图（VIEW）中只能用AS不能用IS，在游标(CURSOR)中只能用IS不能用AS。后面一般跟变量声明。
4、begin和end之间是PL/SQL程序体，其中exception来指定失败处理流程。
调用存储过程。
一般使用的比较多的是在PL/SQL中调用存储过程，在PL/SQL调用存储过程就好比调用一个函数一样，例如：

begin
   pro_1(00813045);
end;

查询存储过程

select * from user_source where name='pro_1';

查看存储过程的状态

select * from user_objects where object_name = 'pro_1';

删除存储过程

drop procedure pro_1;

对于参数的模式有以下三种：
IN参数
语法：参数名 IN 数据类型 DEFAULT 值;
定义一个输入参数变量，用于传递参数给存储过程。在调用存储过程时，主程序的实际参数可以是常量、有值变量或表达式等。DEFAULT 关键字为可选项，用来设定参数的默认值。如果在调用存储过程时不指明参数，则参数变量取默认值。在存储过程中，输入变量接收主程序传递的值，但不能对其进行赋值。

OUT参数
语法：参数名 OUT 数据类型;
定义一个输出参数变量，用于从存储过程获取数据，即变量从存储过程中返回值给主程序。
在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。在存储过程中，参数变量只能被赋值而不能将其用于赋值，在存储过程中必须给输出变量至少赋值一次。

IN OUT参数
语法：参数名 IN OUT 数据类型 DEFAULT 值;
定义一个输入、输出参数变量，兼有以上两者的功能。在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。DEFAULT 关键字为可选项，用来设定参数的默认值。在存储过程中，变量接收主程序传递的值，同时可以参加赋值运算，也可以对其进行赋值。在存储过程中必须给变量至少赋值一次。

实例1，没有参数的存储过程
根据不同的厂牌 把对应的车系数据输出

create or replace procedure series_cur_prc
 as
  v_sql long;
  v_vehicle_make_name   t_md_vehicle_make.vehicle_manuf_make_name%type;

 cursor cur_make is 
 select distinct s.vehicle_make_id  from t_md_vehicle_series s ;
 
 cursor cur_seriesinfo(v_make_id varchar2) is 
    select s.vehicle_series_name 
    from t_md_vehicle_series s where s.vehicle_make_id=v_make_id ;

begin
      for  M_CUR IN cur_make LOOP
        v_sql:='select m.vehicle_manuf_make_name  from t_md_vehicle_make m where m.vehicle_make_id='''||M_CUR.vehicle_make_id||'''';
        EXECUTE IMMEDIATE v_sql into  v_vehicle_make_name;
        DBMS_OUTPUT.PUT_LINE('--------------------------------------');
        DBMS_OUTPUT.PUT_LINE(v_vehicle_make_name || ':');
        for P_CUR IN cur_seriesinfo(M_CUR.vehicle_make_id) LOOP
              DBMS_OUTPUT.PUT_LINE('车系名称： ' || P_CUR.vehicle_series_name );
        end loop; 
      END LOOP;
end ;
--存储过程调用(下面只是调用存储过程语法)
BEGIN 
   series_cur_prc;
END;

image.png

输出结果：

image.png

实例2，仅带传入参数的过程
根据输入的车辆品牌 从表车系表中搜索符合要求的数据，并将其输出

create or replace procedure series_intype_pro(parm_make_name in t_md_vehicle_make.vehicle_manuf_make_name%type)
 as
  v_vehicle_make_name   t_md_vehicle_make.vehicle_manuf_make_name%type;

 cursor cur_make is 
 select  m.vehicle_make_id  from t_md_vehicle_make m where m.vehicle_manuf_make_name=parm_make_name;
 
 cursor cur_seriesinfo(v_make_id varchar2) is 
    select s.vehicle_series_id,s.vehicle_series_name 
    from t_md_vehicle_series s where s.vehicle_make_id=v_make_id ;

begin
      for  M_CUR IN cur_make LOOP
       
        DBMS_OUTPUT.PUT_LINE('--------------------------------------');
        DBMS_OUTPUT.PUT_LINE(parm_make_name || ':');
        for P_CUR IN cur_seriesinfo(M_CUR.vehicle_make_id) LOOP
              DBMS_OUTPUT.PUT_LINE('车系名称： ' || P_CUR.vehicle_series_name||'车系id： ' || P_CUR.vehicle_series_id);
        end loop; 
      END LOOP;
end ;

存储过程调用

declare
 v_vehicle_make_name   t_md_vehicle_make.vehicle_manuf_make_name%type; 
begin
 v_vehicle_make_name:='一汽大众';
 series_intype_pro(v_vehicle_make_name);
end;

image.png

输出结果：

image.png

代码解析：
第1～2行表示创建存储过程。存储过程包括IN类型的参数，表示该参数为输入类型的参数。此时可以省略关键字IN。
第3行表示声明存储过程的内部变量。其中，v_vehicle_make_name表示品牌名称。
第5～6行表示创建cur_make游标,通过品牌名称查询品牌ID。
第8～10行表示创建cur_seriesinfo游标,带参游标，通过传入的品牌ID查询车系表中对应的车系ID,名称
第12～16行表示循环cur_make游标，并输出品牌名称。
第17～19行表示cur_seriesinfo游标，并将品牌ID传入，根据品牌ID从车系表中中查询数据，并把得到的数据输出到屏幕。

实例3，带输入，输出参数的存储过程
根据输入的车辆品牌查询出对应的车辆品牌id，并将得到车辆品牌id放到输出参数中

create or replace procedure make_outype_pro
(
  parm_make_name in t_md_vehicle_make.vehicle_manuf_make_name%type,
  parm_make_id out t_md_vehicle_make.vehicle_make_id%type
)
 as
begin
        select  m.vehicle_make_id into parm_make_id from t_md_vehicle_make m where m.vehicle_manuf_make_name=parm_make_name; 
        DBMS_OUTPUT.PUT_LINE('车辆品牌： ' ||parm_make_name||'车辆品牌id： ' ||parm_make_id);
end ;

存储过程调用
根据输入的车辆品牌以及 车系id的部分 从表车系表 中查询符合要求的数据并输出到屏幕

create or replace procedure serise_clouttype_pro(
    parm_make_name in t_md_vehicle_make.vehicle_manuf_make_name%type,
    parm_series  varchar2
)
as  
  cursor cur_series(v_series_id t_md_vehicle_series.vehicle_series_id%type) is
    select s.vehicle_series_name from t_md_vehicle_series s where s.vehicle_series_id like v_series_id||'%';
  parm_make_id    t_md_vehicle_make.vehicle_make_id%type;
begin
   make_outype_pro(parm_make_name,parm_make_id);

   DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------');
  for S_CUR in cur_series(parm_series) loop
      DBMS_OUTPUT.PUT_LINE('车系名称： ' ||S_CUR.vehicle_series_name);
  end loop;

end;

测试调用

image.png

输出结果：

image.png

实例4，在存储过程调用函数
创建加、减、乘、除计算的存储过程
输入参数： 数字1，数字2，计算类型
输出参数： 数字3

create or replace function fun_Test(num_1  in number, num_2 in number, num_3 in number)
return number
as
 num_A   number:=num_1;
 num_B   number:=num_2;
 numType number:=num_3;
 num_C   number;
begin
if numType=1 then
      return num_C := num_A + num_B;
    elsif numType=2 then
      return num_C := num_A - num_B;
    elsif numType=3 then
     return  num_C := num_A * num_B; 
    elsif numType=4 then
     return  num_C := num_A / num_B; 
    end if;
END fun_Test;

create or replace procedure Proc_Test1
(
--定义输入、输出参数--
num_A in integer,
num_B in integer,
numType in integer,
num_C out integer
)
as
--定义变量--
-- numStr varchar(20);  
begin   
    --调用函数 并赋值到num_C
    num_C:=fun_Test(num_A,num_B,numType);
end;

--调用存储过程---
declare 
  num_C integer;
begin
  Proc_Test1(12,2,4,num_C);
  dbms_output.put_line('输出结果：'|| num_C);
end;