什么是PL/SQL
PL/SQL(Procedure Language/SQL)
PLSQL 是 Oracle 对 sql 语言的过程化扩展,指在 SQL 命令语言中增加了过程处理语句(如分
支、循环等),使 SQL 语言具有过程处理能力。把 SQL 语言的数据操纵能力与过程语言的数
据处理能力结合起来,使得 PLSQL 面向过程但比过程语言简单、高效、灵活和实用。
PL/SQL的基本语法
DECLARE
声明部分(变量声明,光标声明,例外声明)
BEGIN
语句序列(DML语句...)
EXCEPTION
例外处理语句
END;
声明部分
再把数据类型贴一下
变量的声明方式为先变量名,再变量类型
普通数据类型
--类型后面要跟上长度
var char(15);
-- :=就是赋值的意思,后面可以直接跟要赋的值
married boolean :=true;
-- number类型第一个参数为精度,包括小数点后面的位数,第二个参数为小数点后面的位数
psal number(7,2)
除了普通数据类型,还有引用数据类型
-- 引用型变量,即 my_name 的类型与 emp 表中 ename 列的类型一样
emprec emp.ename%type;
--使用into对它进行赋值
declare
v_ename emp.ename%type;
begin
select e.ename into v_ename from emp e where e.empno = 7369;
dbms_output.put_line(v_ename);
end;
-- 记录型变量,为可以接受emp表一样所有数据的对象
v_obj_emp emp%rowtype
-- 也是使用into进行赋值,注意,它只能接收到一行的数据,如果查询结果为多行或空,就会报错
declare
v_obj_emp emp%rowtype;
begin
select * into v_obj_emp from emp e where e.empno = 7369;
dbms_output.put_line(v_obj_emp.ename || ' ' || v_obj_emp.sal);
end;
逻辑控制语句
if语句
-- if语句
declare
pnum number := # --&的功能为从控制台输入 后面的num显示在控制台
begin
if pnum = 1 then
dbms_output.put_line('我是 1');
end if;
end;
-- if else 语句
declare
mynum number := #
begin
if mynum = 1 then
dbms_output.put_line('我是 1');
else
dbms_output.put_line('我不是 1');
end if;
end;
-- if elseif 语句
declare
mynum number := #
begin
if mynum < 18 then
dbms_output.put_line('未成年人');
elsif mynum >= 18 and mynum < 40 then
dbms_output.put_line('中年人');
elsif mynum >= 40 then
dbms_output.put_line('老年人');
end if;
end;
循环
-- while loop循环
declare
step number := 1;
begin
while step <= 10
loop
dbms_output.put_line(step);
step := step + 1;
end loop;
end;
-- loop exit when 循环
declare
step number := 1;
begin
loop
exit when step > 10;
dbms_output.put_line(step);
step := step + 1;
end loop;
end;
-- for循环
declare
step number := 1;
begin
for step in 1 .. 10
loop
dbms_output.put_line(step);
end loop;
end;
游标
游标可以理解为java中的集合,用来储存查询返回的多条数据
语法
CURSOR 游标名 [(参数名 数据类型,参数名 数据类型,...)] IS SELECT语句;
--中括号内为可选,一般不用
游标的使用步骤
0. 定义游标和变量 cursor c1 is select ename from emp;
pjob emp.empjob%type;
- 打开游标
open c1;
- 取一行游标的值
fetch c1 into pjob;
- 当讲所有值取出后关闭循环
exit when c1%notfound
- 关闭游标,释放资源
close c1;
例子,用游标的方式输出emp表中的员工编号和姓名
declare
cursor pc is select * from emp; --声明游标
pemp emp%rowtype; --声明记录型变量
begin
open pc; --开启游标
loop
fetch pc into pemp; --取出游标每一行的数据
exit when pc%notfound; --当游标所有数据被取出后,跳出循环
dbms_output.put_line(pemp.empno || ' ' || pemp.ename); -- 执行逻辑操作
end loop;
close pc;
end;
另一个例子,为部门号为10的员工涨工资
declare
cursor pc(dno myemp.deptno%type) is
select empno from myemp where deptno = dno; --声明游标
pno myemp.empno%type; --声明记录型变量
begin
open pc(20); --开启游标
loop
fetch pc into pno; --取出游标的每一行数据赋值给变量
exit when pc%notfound; --当游标所有值被取出,跳出循环
update myemp t set t.sal = t.sal + 1000 where t.empno =
pno; --执行逻辑命令
end loop;
close pc; --关闭游标,释放资源
end;
例外
异常是程序设计语言提供的一种功能,用来增强程序的健壮性和容错性。Oracle PL/SQL中的异常就是例外
Oracle中内置了20000个例外,绝大多数需求都可以被满足
比较常用的有
no_data_found (没有找到数据)
too_many_rows (select …into 语句匹配多个行)
zero_divide ( 被零除)
value_error (算术或转换错误)
timeout_on_resource (在等待资源时发生超时)
举个栗子
declare
pnum number;
begin
pnum := 1 / 0;
exception
when zero_divide then
dbms_output.put_line('被0除');
when value_error then
dbms_output.put_line('数值转换错误');
when others then
dbms_output.put_line('其他错误');
end;
此外,我们可以自定义例外,处理业务异常
看下面这个例子
declare
no_emp_found exception; --声明一个例外
cursor pemp is
select t.ename from emp t where t.deptno = 50; --声明了一个游标
pename emp.ename%type; --声明了一个记录型变量
begin
open pemp;
fetch pemp into pename;
if pemp%notfound then
raise no_emp_found; --触发在下面定义的 no_emp_found
end if;
close pemp;
exception
when no_emp_found then
dbms_output.put_line('没有找到员工');
when others then
dbms_output.put_line('其他错误');
end;
需要注意的是,没事使用自定义例外都要重新编写,声明
储存过程
存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的 SQL 语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。
储存过程就相当于java中封装的方法;
创建格式如下
create [or replace] PROCEDURE 过程名[(名 参数名 in/out 数据类型)] AS
begin
PLSQL 子程序体; 子程序体;
End;
--或者
create [or replace] PROCEDURE 过程名[(名 参数名 in/out 数据类型)] is
定义储存过程需要用到的变量
begin
PLSQL 子程序体; 子程序体;
End 过程名 过程名;
看这个例子 给员工变更工资 并打印 前后工资的情况
--参数要注意下,in为输入的参数,out为输出的参数,这个场景下,我们只需要进行数据输入
--而且参数不需要写数据类型的参数
--一般尽量使用CREATE OR REPLACE这样就算改储存过程已经存在,也可以进行更改
CREATE OR REPLACE PROCEDURE CHANGEEMPSALBYENPNO(ENO IN NUMBER,MON IN NUMBER)
IS
V_OLD_SAL EMP.SAL%TYPE;
V_NEW_SAL EMP.SAL%TYPE;
BEGIN
--查询涨工资之前的工资情况,赋值给变量V_OLD_SAL;
SELECT E.SAL INTO V_OLD_SAL FROM EMP E
WHERE E.EMPNO=ENO;
--对该员工工资数据进行操作,并提交
UPDATE EMP SET SAL=SAL+MON where empno=eno;
COMMIT;
--再查询一下变更之后的该员工工资
SELECT E.SAL INTO V_NEW_SAL FROM EMP E
WHERE E.EMPNO=ENO;
DBMS_OUTPUT.put_line('操作完成!');
dbms_output.put_line('涨工资之前:'||V_OLD_SAL);
dbms_output.put_line('涨工资之后'||V_NEW_SAL);
exception
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('没有查询到数据!');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.put_line('查询返回多条结果!');
WHEN OTHERS THEN
dbms_output.put_line('系统未知错误!');
end;
--调用这个储存过程非常简单
begin
CHANGEEMPSALBYENPNO(7369,100);
end;
===========输出结果==============
操作完成!
涨工资之前:800
涨工资之后900
储存函数
储存函数和储存过程本质上没区别。只是函数有限制只能返回一个标量,而存储过程可以返回多个。并且函数是可以嵌入在SQL中使用的,可以在SELECT等SQL语句中调用,而存储过程不行。执行的本质都一样。
create or replace function 函数名(Name in type, Name out type, ...) return 型 数据类型 is
结果变量 数据类型;
begin
return( 结果变量);
end[ 函数名];