【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块调用,存储过程也可以调用存储过程/存储函数;
  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值