plsql 以及存储过程
- plsql 编程
- 概念和目的
- 什么是PL/SQL
- PL/SQL的好处
- 程序结构
- 变量的声明及使用
- 流程控制
- 条件分支
- 循环
- 集合类型的变量 游标
- 带参数的游标
- 存储过程
- 概念与介绍
- 存储过程的作用
- 语法
- Java 程序调用存储过程
plsql 编程
概念和目的
什么是PL/SQL
- PL/SQL：procedure language/SQL
- PL/SQL 是 Oracle 对 sql 语言的过程化扩展
- 指在 sql 命令语言中增加了过程处理语句（如分支、循环等），使得 sql 语句具有过程处理能力。
PL/SQL的好处
- 将复杂的业务逻辑写在数据库中，而不用写在服务器中，减少了服务器与数据库的交互；
- 提高了执行效率
程序结构
- 通过 plsql 工具的 test window 创建编写
- plsql 语言的大小写是不区分的
- plsql 可以分为三个部分：声明部分、可执行部分、异常处理部分
declare
-- 声明变量 游标，没有的话可以省略
begin
-- 执行语句
-- 处理异常
end;- 在command 中，得先set serveroutput on，再通过 / 来表示plsql 结束并运行，才会显示出结果
变量的声明及使用
- 变量名 变量类型（变量长度）
- name varchar2（20）
- 变量赋值的两种方式：
- 直接赋值语句 := 。 例如 name ：=‘zhangsan’；
- 语句赋值 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 程序称之为存储过程
存储过程的作用
- 在开发程序中，为了一个特定的业务功能，会向数据库进行多次连接关闭（连接关闭很耗费资源），需要对数据库进进行多次 I/O 读写，性能比较低。如果把这些业务放在 PLSQL 中，在应用程序中只需要调用 PLSQL 就可以实现连接关闭一次数据库就可以实现我们的业务，可以大大提升效率。
- 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;- 存储过程没有declare 关键字，plsql 程序中才有。
- 存储过程声明变量直接在is 后面声明即可
- end 后面的名称可以省略，默认是前面的
- 调用方法有两种：在plsql 程序中直接输入名字调用；在sqlplus 中用exec 命令调用
- 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 程序调用存储过程
- 需求：如果一条语句无法实现结果集，比如需要多表查询，或者需要复杂逻辑查询，我们可以选择调用存储过程查询出你的结果。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
