【Oracle】常用对象之存储过程

目录

一、什么是存储过程？

二、为什么要用存储过程？

三、Oracle怎么使用存储过程？

四、存储函数？

---

一、什么是存储过程？

先来了解下定义

存储过程(procedure)：是完成特定功能的一段sql代码，经编译后存储在数据库中，我们通过指定存储过程名并给出参数来调用执行它。

接着，了解一下存储过程的语法和基本操作：

1. 创建存储过程（存储过程定义）

-- 存储过程名(参数列表)：参数列表可有可无，无得话称为无参存储过程，有的话成为有参存储过程
create [or replace] procedure 存储过程名(参数列表)
    as 或 is  --声明部分：定义变量
    begin
    --执行部分
    end;

注意：

（1）.带参数的存储过程，有三种模式：

• in模式：默认模式，可不写，表示输入参数，该参数可以向过程（指的是begin...and执行块）传递值。
• out模式：表示输出参数，类似于返回值。
• in out 模式：表示既有输入参数，也有输出参数。

（2）.is和as区别：

• 使用游标（cursor）的话，只能用is；
• 在存储过程（procedure）和函数（function）中两者都可以使用，但使用is将无法使用调试模式调试该存储函数。

2. 调用存储过程（有三种方式调用）

--调用存储过程方式(第三种方式适用于sqlplus)
1. call 存储过程名() 或 存储过程名(有参)  

2. declare
      变量说明
    begin  
       存储过程名() 或 存储过程名(有参) 
    end 

3. exec 存储过程名() 或 存储过程名(有参)  

3. 其他操作（可以删除，显示，但不能修改存储过程）

-- 注意：在一个存储过程中不能去删另一个存储过程
drop procedure 存储过程名 
-- 显示存储过程
show procedure status
show create procedure 存储过程名

二、为什么要用存储过程？

这要从执行一条sql语句说起，从上图可以看到，一条sql的执行需要经过许多流程（SQL引擎分析，语法检查，命令执行，返回结果等 ），对于数据量较大的查询操作势必是一个耗时的过程。因此，从中省略一些步骤或者说可以把一些查询到数据暂时存储起来，我们在下次使用的时候，就直接使用存储起来的数据，这样数据的加载就块很多了，这就是使用存储过程的原因。

三、Oracle怎么使用存储过程？

存储过程增强了sql语句的功能和使用灵活性，我们第一次查询的时候就把数据存储起来，或者说将一些数据查询出来之后在存储，当我们再次调用的时候就直接使用上一次存储的数据，就避免了重复的去请求同一个操作，这样对带宽的消耗也少了很多。那么，怎么使用呢，下面是自己练习的例子：

1、无参存储过程：

SQL> set serveroutput on
-- 创建存储过程，调用时打印helloworld提示语。
SQL> create or replace procedure psite
as
begin
    dbms_output.put_line('helloworld');
end;
/
过程已创建。

-- 三种调用存储过程的方式
SQL> exec psite();
helloworld

PL/SQL 过程已成功完成。

SQL> declare
begin
     psite();
end;
/
helloworld

PL/SQL 过程已成功完成。

SQL> call psite();
helloworld

调用完成。

2、有参存储过程：

SQL> create or replace procedure precord(recordId in number) as
       icarryCnt number;
    begin
     select "carryNbr" into icarryCnt from ai_carsharing_record where "record_id"=recordId;
     if icarryCnt is null or icarryCnt!=4
     then
        update ai_carsharing_record set "realLoadNbr"=0,"carryNbr"=4,"passenger_source"=3,"oil_count"=0,"red_count"=4 where "record_id"=recordId;
      dbms_output.put_line('查询时表中存在carryNbr为null或不为4的记录修改成默认值！');
    end if;
   end;
   /

-- 测试存储过程执行情况1
SQL> exec precord(8);
    查询时表中存在carryNbr为null或不为4的记录修改成默认值！
PL/SQL 过程已成功完成。

-- 测试存储过程执行情况2（赋值情况）
SQL> declare
    irecordId number;
    begin
      irecordId:=11;
      precord(recordId=>irecordId);  -- 多个参数时，可以不按变量顺序赋值，使用=>对应好变量即可
    end;
    /
查询时表中存在carryNbr为null或不为4的记录修改成默认值！
PL/SQL 过程已成功完成。

3、分页查询的存储过程：

--模糊分页查询
create or replace procedure sel_pro(d_name varchar2,cp number,ps number,mycursor out sys_refcursor) is
begin
  open mycursor for 
  select * from (select d.*,rownum rn from (select * from depart where dept_name like '%'||d_name||'%') d where rownum<=cp*ps) where rn>=(cp-1)*ps+1;
end;

--模糊分页查询,返回总记录数
create or replace procedure sel_pro(d_name varchar2,cp number,ps number,mycursor out sys_refcursor,total out number) is
begin
  open mycursor for 
  select * from (select d.*,rownum rn from (select * from depart where dept_name like '%'||d_name||'%') d where rownum<=cp*ps) where rn>=(cp-1)*ps+1;
  select count(*) into total from depart where dept_name like '%'||d_name||'%';
end;

四、存储函数？

提及Oracle存储过程，不得不说下存储函数function了。我们可以简单的理解为：存储函数是一种自定义函数，经过编译在数据库服务端以pl/sql块的形式存在。自定义函数也是一种封装的思想，与Oracle自带的系统函数一样，用户在需要的地方直接调用即可。

存储函数的语法：

create [or replace] function 函数名[(参数名 in|out 参数类型)]   return 返回值数据类型 
  as 或 is
	声明变量部分;
　begin
　	代码块;
　	return 结果变量;
　end [函数名];

举例说明：

1.创建存储函数：

-- 让快递员帮我查一下我有几个快递到了
create or replace function  findOrderFun(name varchar2) return number as  orderTotal number
    begin
        select count(*) into orderTotal from t_order_consumer where c_name = name;
        return (orderTotal );
    end   findOrderFun;

2.调用函数：

-- 直接调用
select findOrderFun('魏详详') from dual

-- 也可在存储过程中调用存储函数使用

最后，总结一下，存储过程与存储函数二者的异同点吧：

相同点：

• 语法结构相似，本质上都是一段特定功能的sql片段，一次编译，放在数据库服务端可被随时调用。

不同点：

• 语法上：标识的关键字不同，存储函数必须且只能通过return一个返回值，而存储过程无return，可通过out模式传递多个返回值；
• 使用上：存储函数可在select语句/存储过程等处被调用，而存储过程通过call或pl/sql块调用，存储过程也可以调用存储过程/存储函数；