plsql 以及存储过程

plsql 编程

概念和目的

什么是PL/SQL

1. PL/SQL：procedure language/SQL
2. PL/SQL 是 Oracle 对 sql 语言的过程化扩展
3. 指在 sql 命令语言中增加了过程处理语句（如分支、循环等），使得 sql 语句具有过程处理能力。

PL/SQL的好处

1. 将复杂的业务逻辑写在数据库中，而不用写在服务器中，减少了服务器与数据库的交互；
2. 提高了执行效率

程序结构

• 通过 plsql 工具的 test window 创建编写
• plsql 语言的大小写是不区分的
• plsql 可以分为三个部分：声明部分、可执行部分、异常处理部分

declare
	-- 声明变量 游标，没有的话可以省略
begin
	-- 执行语句
	
	-- 处理异常
end;

• 在command 中，得先set serveroutput on，再通过 / 来表示plsql 结束并运行，才会显示出结果

变量的声明及使用

• 变量名 变量类型（变量长度）

• name varchar2（20）

• 变量赋值的两种方式：

  1. 直接赋值语句 := 。 例如 name ：=‘zhangsan’；
  2. 语句赋值 select 值 into 变量 from dual （注：这个dual 是 oracle 数据库中最小的一张表，里面永远都只有一条记录，因为select 语句不能没有from 使用，所以这个dual 一般用于 select 没有 from 时使用。

• 打印：dbms_output.put_line(’‘姓名：’ || name); 这个 || 的作用是字符串拼接

• 变量的类型

  • 普通变量 ： varchar2，boolean，number等声明变量类型

    例如：name varchar2（20）

  • 引用型变量：变量的类型和长度由表中字段的类型和长度决定。

    语法： 表明.列名%TYPE 指定。

    例如：name user.username%TYPE;

  • 记录型变量：表示接收表中的一行记录，相当于 java 的一个对象。这个变量只能接收一个结果，查询结果有多行是不行的，查询的值不是这个表的所有字段也是不行的，即必须是select *

    语法：变量名 表明%ROWTYPE

    例如：v_emp emp%type

流程控制

条件分支

• 需要注意的
  • elsif 少一个e
  • if 最后得有 end if结尾

begin
if 条件  then 执行1
elsif 条件2 then 执行2
else 执行3
end if;
end;

循环

• 在 oracle 中有三种循环的方式

begin
loop
exit when 条件;
执行语句
end loop;
end;

集合类型的变量 游标

• 之前的记录型变量只能接收一行数据

• 游标用于临时存储一个查询返回的多行数据（结果集，类似于 java 的 jdbc 连接返回的 resultset 集合）。

• 通过遍历游标，可以逐行访问处理该结果集的数据

• 使用步骤：声明 - 打开 - 读取 - 关闭

  • 声明

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

  • 打开

    open 游标名

  • 读取

    fetch 游标名 into 边练列表

  • 关闭

    close 游标名

• 游标的属性

  游标的属性	返回值类型	说明
  %ROWCOUNT	整形	获取fetch语句返回的数据行数
  %FOUND	布尔型	最近的fetch语句返回一行数据为真，否则为假
  %NOTFOUND	布尔型	与上明刚好相反
  %ISOPEN	布尔型	游标已经打开则为真

• declare
  cursor mycursor is select name,salary from emp;
  v_name emp.name%type;
  v_salary emp.salary%type;
  bigin
  open mycursor;
  loop
  fetch mycursor into v_name.v_sarlary;  // 将游标中的一行数据fetch 到name 和 salary 中
  exit when mycursor%notfound;
  dbms_output.put_line('v_name,v_sarlary');
  end loop;
  close mycursor;
  end;
  

带参数的游标

declare
-- 打开游标的时候进行传值
cursor mycursor(v_dept emp.dept%type) is select name,salary from emp where dept = v_dept;
v_name emp.name%type;
v_salary emp.salary%type;
bigin
open mycursor(10);  -- 说明 v_dept = 10
loop
fetch mycursor into v_name.v_sarlary;  // 将游标中的一行数据fetch 到name 和 salary 中
exit when mycursor%notfound;
dbms_output.put_line('v_name,v_sarlary');
end loop;
close mycursor;
end;

存储过程

概念与介绍

• 之前介绍的 plsql 程序可以进行表的操作，判断，循环逻辑处理的工作，但是无法重复调用。

  可以理解为之前的代码全都编写在了 main 方法中，是匿名程序，java 可以通过封装对象和方法来解决复用问题。

  PLSQL 是将一个个 PLSQL 业务处理过程存储起来进行复用，这些被存储起来的 PLSQL 程序称之为存储过程

存储过程的作用

1. 在开发程序中，为了一个特定的业务功能，会向数据库进行多次连接关闭（连接关闭很耗费资源），需要对数据库进进行多次 I/O 读写，性能比较低。如果把这些业务放在 PLSQL 中，在应用程序中只需要调用 PLSQL 就可以实现连接关闭一次数据库就可以实现我们的业务，可以大大提升效率。
2. oracle 官方提出的建议是：能够让数据库操作的不要放在程序中。在数据库中实现基本不会出现错误，在程序中操作可能会出现错误。（如果在数据库中操作数据，可以有一定的日志恢复等功能）

语法

create or replace procedure 过程名称[(参数列表)] IS(as)
-- 参数声明
BEGIN

END [过程名称];

根据参数的不同，可以分为三类讲解：

• 不带参数
• 带输入参数
• 带输入输出参数

program window - procedure

• 不带参数

create or replace procedure p_hello is
begin
dbms_output.put_line('hello');
end p_hello;



--在plsql程序中调用
begin

p_hello;

end;


--sqlplus 中调用  executor 缩写
sql> exec p_hello;

• 带输入参数

create or replace procedure p_query(in_empno in emp.empno%type) as
//打印使用
v_name emp.name%type;
v_salary emp.sal%type;

begin
	select name,sal into v_name,v_salary from emp where empno=in_empno;
end p_query;

1. 存储过程没有declare 关键字，plsql 程序中才有。
2. 存储过程声明变量直接在is 后面声明即可
3. end 后面的名称可以省略，默认是前面的
4. 调用方法有两种：在plsql 程序中直接输入名字调用；在sqlplus 中用exec 命令调用
5. plsql 软件中执行execute 是创建一个存储过程。

• 带输入和输出的存储过程

create or replace procedure p_query(in_empno in emp.empno%type,out_sal out emp.sal%type) as
--打印使用，现在不需要打印，只需要输出，所以删除
--v_name emp.name%type;
--v_salary emp.sal%type;

begin
--将查询到的sal 赋值给输出的变量
	select sal into out_sal from emp where empno=in_empno;
end p_query;

declare
v_sal emp.sal%type
begin
p_query(1234,v_sal);
-- v_sal就是返回的结果
end;

Java 程序调用存储过程

• 需求：如果一条语句无法实现结果集，比如需要多表查询，或者需要复杂逻辑查询，我们可以选择调用存储过程查询出你的结果。