一文详解oracle存储过程

简介

oracle 存储过程是 oracle 数据库中的一种数据处理对象，它可以在数据库中定义一组预定义的 sql 语句，用于完成特定的数据库操作。存储过程可以被授权的用户调用，并且可以执行多个语句，这些语句可以被视为一个单独的操作，也可以被视为一系列的操作。

使用存储过程可以大大提高数据库的性能和安全性。存储过程可以减少网络流量和请求，同时也可以减少与应用程序之间的接口调用，从而提高了数据库的性能和可靠性。

1、基本语法

1.1 新建测试窗口

• new test window

1.2程序结构

• 在java编程中是区分大小写，这里不用区分大小写。
• declare部分声明变量或游标（结果集类型变量)，程序没有变量声明的可以省略或删除。
• plsql可分为三个部分: 变量声明部分，执行部分，异常处理部分。

-- created on 2023/5/10 by 肖 
declare 
--声明变量 游标

begin
-- 执行语句

-- 异常处理
end;

1.3打印输出

• dbms_output为oracle内置程序包，类似java中的system.out,而put_line() 是调用的方法,相当于println()方法。
• 需要注意的是: put_line('hello mr.xiao'); 中 一定是 ' ' 号，否则会报错的。

begin

--打印 hello mr.xiao
dbms_output.put_line('hello mr.xiao');

end;

执行结果

• 如果你不能打印输出，需要开启 set serveroutput on 因为默认情况下，输出选项是关闭状态。

1.4 变量

变量分两大类如:

• 普通数据类型（char,varchar2, date, number, boolean, long）
• 特殊变量类型（引用型变量、记录型变量）

声明变量的方式如:

• 变量名 变量类型（变量长度） 例如： v_name varchar2(30);

1.4.1普通变量

变量赋值的方式有两种如：

• 直接赋值语句 := 比如: v_name := '你才是臭弟弟'
• 语句赋值，使用select …into … 赋值：（语法 select 值 into 变量）

  -- 打印个人信息，包括： 姓名、薪水、地址
declare
-- 姓名
v_name varchar2(30) := '你才是臭弟弟'; -- 声明变量直接赋值
--薪水
v_sal number;
--地址
v_addr varchar2(200);

begin

--在程序中直接赋值
v_sal := 1800; --工资每月1800 每天笑哈哈

--语句赋值
select 'csdn你才是臭弟弟' into v_addr from dual; --不会有人不知道dual吧，dual 是一个用于描述 oracle 数据库中的虚拟表的关键字

--打印变量  注意 || 是拼接
dbms_output.put_line('姓名：' || v_name || ',薪水：' || v_sal || ',地址：' ||v_addr);

end;

执行结果:

1.4.2引用型变量

• 变量的类型和长度取决于表中字段的类型和长度
• 通过 表名.列名%type 指定变量的类型和长度，例如： v_name emp.ename%type

  -- 查询emp表中1001号员工的个人信息，打印姓名和薪水
declare
-- 姓名
v_name emp.ename%type; -- 声明变量直接赋值
--薪水
v_sal  emp.esalary%type;

begin
--查询表中的姓名和薪水并赋值给变量
--注意查询的字段和赋值的变量的顺序、个数、类型要一致
select ename, esalary into v_name, v_sal from emp where employeeid = 1001;

--打印变量
dbms_output.put_line('姓名：' || v_name || ',薪水：' || v_sal);

end;

执行结果:

推荐大家使用引用型变量区别:

• 普通型变量: v_name varchar2(30); 你怎么知道一定varchar2类型，你又怎么知道长度一定是30呢，假设v_name varchar2(1); 就对应不上select ename, esalary into v_name, v_sal from emp 这条语句中的 ename 的长度了，也就接收不到值了，就会报错。如果要使用普通变量前提是 ，了解查询表中对应字段的 类型 及 长度， 才能基于他们来确定类型长度 这比较繁琐。
• 引用型变量: 声明一个变量 不再定义类型长度，而是基于接收表字段的类型及长度 来定义。如:v_name emp.ename%type;

总结:

使用普通变量定义方式，需要知道表中列的类型，而使用引用类型，不需要考虑列的类型，使用%type是非常好的编程风格，因为引用型变量更加灵活。

1.4.3 记录型变量

• 记录型变量 接受表中的一整行记录，相当于java中的一个对象
• 语法： 变量名称 表名%rowtype， 例如：v_emp emp%rowtype;

  -- 查询emp表中1001号员工的个人信息，打印姓名和薪水
declare
-- 记录型变量接受一行
v_emp emp%rowtype;

begin
--记录型变量默认接受表中的一行数据，不能指定字段。
select * into v_emp from emp where employeeid = 1001;

--打印变量，通过变量名.属性的方式获取变量中的值
dbms_output.put_line('姓名：' || v_emp.ename || ',薪水：' || v_emp.esalary);

end;

总结:

• 如果有一张表，有50个字段，那么你程序如果要使用这50字段话，如果你使用引用型变量一个个声明，会特别繁琐，记录型变量可以方便的解决这个问题。

注意错误的使用案例如下:

• 记录型变量只能存储一个完整的行数据

我把 * 换成 单个字段执行报错，因为上面的变量定义的是一行，而现在只给一个是不行的。

• 返回的行太多了，记录型变量也接收不了

现在这条sql 为什么报错，因为现在是全表查询 返回的行数超出了 一个变量只能接收一行，这就和java 类似了，应该用集合去装才可以 ，装进集合在取出来 是不是就跟java 中循环取值差不多。

1.5 流程控制

1.5.1 条件分支

• if条件判断~语法

begin

if 条件 then
    执行语句
end if;

end;

• if...else 条件判断~语法

begin

if 条件 then
  执行语句
else
  执行语句
end if;

end;

• if...elsif...else条件判断~语法，注意关键字：elsif。

begin

if 条件1 then 执行1
  
 elsif 条件2 then 执行2

 else 执行3
  
end if; 

end;

案例:

  --判断emp表中记录是否超过20条，10-20之间，或者10条以下
declare
--声明变量接受emp表中的记录数
v_count number;

begin

--查询emp表中的记录数赋值给变量

select count(1) into v_count from emp;

--判断打印

if v_count > 20 then
  dbms_output.put_line('emp表中的记录数超过了20条为：' || v_count || '条。');

elsif v_count >= 10 then
  dbms_output.put_line('emp表中的记录数在10~20条之间为：' || v_count || '条。');

else
  dbms_output.put_line('emp表中的记录数在10条以下为：' || v_count || '条。');

end if;

end;

执行结果:

1.5.2 循环

• loop 语法

begin
loop
	exit when 退出循环条件  
end loop;
end;

loop语法案例:

  --循环打印 1-5
declare
--声明循环变量并赋初值
v_num number := 1;

begin

loop

  exit when v_num > 5;
  
  dbms_output.put_line(v_num);

  --循环变量自增
  v_num := v_num + 1;

end loop;

end;

• while 语法

while(判断循环的条件) loop
	循环的语句;
end loop;

while语法案例:

declare
  --声明循环变量
  v_num number;
begin
  -- 必须给一个初始值
  v_num := 1;
  while(v_num < 10) loop
      dbms_output.put_line('值为: ' || v_num);
      v_num := v_num + 1;
  end loop;
end;

--此循环会先判断再执行语句

• for循环 语法

for 变量名 in 变量的初始值..结束值 loop
	循环语句;
end loop;

for循环语法 案例:

    --for循环打印 1-10
declare
  --声明循环变量并赋初值
  v_num number ;
begin
	  --此语句会自动将1到10赋值给v_num
  for v_num in 1..10 loop
      dbms_output.put_line('值为: ' || v_num);
  end loop;
end;

2、游标

2.1游标说明

• 用于临时存储一个查询返回的多行数据，通过遍历游标，可以逐行访问处理该结果集的数据。
• 游标的使用方式：声明→打开→读取→关闭 2.2 语法

游标声明：

cursor 游标名[(参数列表)] is 查询语句;

游标的打开：

open 游标名;

游标的取值：

fetch 游标名 into 变量列表;

游标的关闭：

close 游标名;

注意: 游标名自身是可以带参数的，如果有参数、参数会带入到查询语句中进行查询，游标本质 就是 一个 is 查询语句，也就是说查询结果被放置到游标中。

2.3 游标属性

游标的属性

2.4无参数游标

• 使用游标查询emp表中所有员工的姓名和工资，loop循环依次打印结果集。

--使用游标查询emp表中所有员工的姓名和工资，依次打印结果集。
declare
--声明游标
cursor c_emp is
  select ename, esalary from emp;

--声明变量用来接受游标中的元素
v_ename emp.ename%type;

v_sal emp.esalary%type;

begin

--打开游标
open c_emp;

--遍历游标中的值
loop

  --通过fetch语句获取游标中的值并赋值给变量
  fetch c_emp
    into v_ename, v_sal;

  --通过%notfound判断是否有值,有值打印,没有则退出循环
  exit when c_emp%notfound;

  dbms_output.put_line('姓名:' || v_ename || ',薪水:' || v_sal);

end loop;

--关闭游标
close c_emp;

end;

执行结果:

2.5带参数的游标

• 使用游标查询并打印某部门的员工的姓名和薪资，部门编号为运行时手动输入。

 --使用游标查询并打印某部门的员工的姓名和薪资，部门编号为运行时手动输入。
declare
--声明游标传递参数
cursor c_emp(v_emploid emp.employeeid%type) is
  select ename, esalary from emp where employeeid = v_emploid; 

--声明变量用来接受游标中的元素
v_ename emp.ename%type;

v_sal emp.esalary%type;

begin

--打开游标并传递参数
open c_emp(1001);

--遍历游标中的值
loop

--通过fetch语句获取游标中的值并赋值给变量
  fetch c_emp
    into v_ename, v_sal;


     --通过%notfound判断是否有值,有值打印,没有则退出循环
     exit when c_emp%notfound;
     
     

  dbms_output.put_line('姓名:' || v_ename || ',薪水:' || v_sal);


end loop;

--关闭游标
close c_emp;

end;

执行结果:

注意:%notfound属性默认值为flase,所以在循环中要注意判断条件的位置.如果先判断在fetch会导致最后一条记录的值被打印两次(多循环一次默认);

错误反例演示:

反例执行结果:

原因:%notfound 默认值是 false，false意味着游标里面默认是有值，到底有值还是没值 需要fetch 好之后才知道有没有值，%notfound 默认做了一个有值的假设 ，看下面代码:

loop

--通过%notfound判断是否有值,有值打印,没有则退出循环
exit when c_emp%notfound;

--通过fetch语句获取游标中的值并赋值给变量
fetch c_emp
into v_ename, v_sal;

dbms_output.put_line('姓名:' || v_ename || ',薪水:' || v_sal);

end loop;

exit when c_emp%notfound; 判断有值打印，出去之后 又带着有值的进入到循环中exit when c_emp%notfound;判断没值打印， 所以这次打印的是上次值的。注意存放的位置。

3、存储过程

3.1 概念

• 之前编写的plsql程序可以进行表的操作、判断、循环等逻辑处理的工作，但无法重复调用。可以理解为代码编写在了java的main方法中，java可以通过封装对象和方法来解决复用问题
• plsql是将一个个plsql的业务处理过程存储起来进行复用,这些被存储起来的plsql程序称之为存储过程

3.2 语法

参数的类型分为:

• 不带参数的。
• 带输入参数的。
• 带输入输出参数(返回值)的。

create or replace procedure 过程名称[(参数列表)] is
begin

end 过程名称;

3.3 无参存储

3.3.1创建存储

• 第一种方式: new→program window→ procedure

• 第二种方式: new → sql window

• 创建存储过程语法

--通过调用存储过程打印hello 臭弟弟
create or replace procedure p_xiao is
--声明变量
begin

dbms_output.put_line('hello 臭弟弟');

end p_xiao ;

1、is和as都可以用。

2、存储过程中没有declare关键字，declare用在语句块中。也就是说匿名程序才需要， 存储过程没有可以直接带上方--声明变量。

• 注意点击△执行后 会进行存储 ，procedures 中会以p_xiao 这个名称进行存储。

• 通过plsql工具查看创建好的存储过程

3.3.2调用存储过程

• 通过plsql程序调用new → text window

begin
--输入调用存储过程的名称
p_xiao;

end;

查看结果:

3.4带输入参数的存储过程 in

说明:

• 带参数的存储过程跟我们在java中的方法就可以对应上，比如查询并打印某个员工姓名薪水 ，在调用存储过程的时候自己指定传参， 比如我传一个员工编号，基于传的编号将结果返回。
• 实现查询并打印某个员工(如:编号1001)的姓名和薪水， 调用存储过程的时候传入员工编号，自动控制台打印。

3.4.1 创建带参数存储过程

• 第一种方式: 重新编辑存储过程右击 → edit(这是基于之前创建的，还可以进行编辑)

• 需要注意的是如果有or replace当存储过程名字被更改时，如果plsql中存在此存储过程名称会被删除替换创建当前的，果不存在则创建一个新的存储过程。

执行结果:

• 如果没有or replace语句plsql也不存相同的名字在则会新创建。如果存在则会报错。

• 第二种方式: new → sql window

• 查询并打印某个员工(如:编号1001)的姓名和薪水， 要调用存储过程的时候传入员工编号，自动控制台打印。
• 注意:参数要与定义的参数的顺序和类型一致

--查询并打印某个员工(如:编号1001)的姓名和薪水， 要调用存储过程的时候传入员工编号，自动控制台打印。
create or replace procedure p_xiao_jian(in_employeeid in emp.employeeid%type) as
--声明变量接受查询结果
v_ename emp.ename%type;
v_sal   emp.esalary%type;

begin

--根据用户传递的员工号查询姓名和薪水   
--注意:参数要与定义的参数的顺序和类型一致 如: ename  into  v_ename 
select ename, esalary into v_ename, v_sal from emp where employeeid = in_employeeid;

--打印结果
dbms_output.put_line('姓名:' || v_ename || ',薪水:' || v_sal);

end p_xiao_jian;

3.4.2 调用带参数存储过程

• new → text window 直接赋值

-- created on 2023/5/13 by 肖 
declare 
-- local variables here
i integer;
begin
-- test statements here
p_xiao_jian(1001);--直接赋值  做一个值的传递

end;

• 也可以 声明变量 赋值

-- created on 2023/5/13 by 肖 
declare 
-- local variables here
 v_param number :=1001; --声明变量  赋值
begin
-- test statements here
p_xiao_jian(v_param);

end;

查看执行结果:

3.5 带输入输出参数的存储过程 out

说明:

• 带输入输出的这种存储过程通常是给第三方程序调用的，就比如java或其他编程语言，也就是说把这个存储过程的 计算结果进行返回不是在数据库打印打印就完事了
• 举例说明比如输入员工编号查询某个员工信息，要求将薪水作为返回值输出，给调用的程序使用。这个调用的程序可以是plsq自身程序，也可以是第三方比如java 程序。

3.5.1 创建带输入输出参数的存储过程

• 参数传递方式分三类: in，out，in out
• in 表示输入参数
• out 表示输出参数
• in out 即可作输入参数，也可作输出参数。

--输入员工号查询某个员工(如:编号1001)信息，要求将薪水作为返回值输出，给调用的程序使用。
create or replace procedure p_xiao_jian(in_employeeid in emp.employeeid%type,out_esalary out emp.esalary%type) as

begin

--查询 esalary into 给 out_esalary 输出变量
select esalary into out_esalary from emp where employeeid = in_employeeid;


end p_xiao_jian;

3.5.2调用带输入输出参数存储过程

declare
--声明一个变量接受存储过程的输出参数
v_esalary emp.esalary%type;

begin

p_xiao_jian(1001, v_esalary); --注意参数的顺序

dbms_output.put_line('工资:'||v_esalary);

end; 

执行结果:

3.6带输入输出参数的存储过程 in out

• in out 即可作输入参数，也可作输出参数。

3.6.1 创建带输入输出参数的存储过程

--输入员工号查询某个员工(如:编号1001)信息，要求将薪水作为返回值输出，给调用的程序使用。
create or replace procedure p_xiao_jian(in_employeeid in  emp.employeeid%type,out_esalary  out emp.esalary%type,in_out_param in out number) as

begin


--查询 esalary into 给 out_esalary 输出变量
select esalary into out_esalary from emp where employeeid = in_employeeid;
--打印被传入的值
dbms_output.put_line('我是被传入的值'||in_out_param);
--in_out_param赋值默认值为10
in_out_param:=10;

end p_xiao_jian;

3.6.2调用带输入输出参数存储过程

-- created on 2023/5/16 by 肖 
declare 
-- local variables here

v_esalary emp.esalary%type;

v_in_out_param number:=6;--传入的值


begin
-- test statements here
p_xiao_jian(1001,v_esalary,v_in_out_param);

dbms_output.put_line('薪水:'||v_esalary||'原始默认值: '||v_in_out_param);


end;

执行结果:

以上就是一文详解oracle存储过程的详细内容，更多关于oracle存储过程的资料请关注硕编程其它相关文章！