oracle（sql）基础篇系列（五）——PLSQL、游标、存储过程、触发器
PL/SQL
PL/SQL 简介
每一种数据库都有这样的一种语言，PL/SQL 是在Oracle里面的一种编程语言，在Oracle内部使用的编程语言。我们知道SQL语言是没有分支和循环的，而PL语言是为了补充SQL语言的，是带有了分支和循环的语言。
PL/SQL 语法
基本数据类型声明
declare
v_name varchar2(20);
v_temp number(1);
v_count binary_integer := 0;
v_sal number(7,2) := 4000.00;
v_date date := sysdate;
v_pi constant number(3,2) := 3.14;
v_valid boolean := false;
v_name varchar2(20) not null := 'myname';
declare开头声明变量，v_name表示变量名字，通常以v_xxx这种格式命名变量，varchar2(20)表示变量类型， :=为赋值操作符。
在PL/SQL里面boolean类型变量在定义的时候一定要给初始值，Oracle 里面的put_line()不能打印boolean类型的值。
%type属性声明
还有一种声明变量的方法：使用%type属性。
declare
v_empno number(4);
v_empno2 emp.empno%type;
v_empno3 v_empno2%type;
begin
dbms_output.put_line('Test');
end;
使用emp表中empno字段的类型，这种方式的好处是PL/SQL声明的变量类型随着表字段类型的变化而变化。dbms_output.put_line('Test');表示输出Test。
table类型声明
table类型类似java中的数组类型。
declare
type type_table_emp_empno is table of emp.empno%type index by binary_integer;
v_empnos type_table_emp_empno；
begin
v_empnos(0) := 7369;
v_empnos(2) := 7839;
v_empnos(-1) := 9999;
dbms_output.put_line(v_empnos(-1));
end;
--type开头
--类型名type_table_emp_empno
--is table of 表示table数据类型，相当于java里面的数组Array[]
--emp.empno%type表示该类型数据装的是emp表中empno类型的数据
--index by binary_integer 表示下表
--v_empnos type_table_emp_empno；使用type_table_emp_empno类型声明变量v_empnos
record数据类型声明
declare
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
v_temp type_record_dept;
begin
v_temp.deptno := 50;
v_temp.dname := 'aaaa';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
/
--type type_record_dept is record声明type_record_dept是record类型。
--该类型中有三个属性deptno ，dname ，loc ，类型分别为dept表中deptno ，dname ，loc 三个字段的类型。record类型类似java中的类，record类型可以存储一条记录。
-- v_temp type_record_dept;使用type_record_dept类型声明变量v_temp。
%rowtype属性声明
另一种声明record变量的方法：
declare
v_temp dept%rowtype;
begin
v_temp.deptno := 50;
v_temp.dname := 'aaaa';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
/
使用%rowtype声明record类型的变量，v_temp 的属性和dept表的字段保持一致，这种方式的好处是PL/SQL声明的变量类型随着表字段类型的变化而变化。
异常处理
begin
v_num := 2/v_num;
dbms_output.put_line(v_num);
exception
when others then//固定写法
dbms_output.put_line('error');
end;
exception定义异常处理，紧跟“when others then”为固定写法。
SQL> declare
2 v_num number := 0;
3 begin
4 v_num := 2/v_num;
5 dbms_output.put_line(v_num);
6 exception
7 when others then
8 dbms_output.put_line('error');
9 end;
10 /
error
PL/SQL 过程已成功完成。
SQL>
其他类型的异常
--返回记录太多异常
declare
v_temp number(4);
begin
select empno into v_temp from emp where deptno = 10;
exception
when too_many_rows then
dbms_output.put_line('太多记录了');
when others then
dbms_output.put_line('error');
end;
--没有记录异常
declare
v_temp number(4);
begin
select empno into v_temp from emp where empno = 2222;
exception
when no_data_found then
dbms_output.put_line('没数据');
end;
PL/SQL的DML语句
select语句
PL/SQL里面的selec t语句必须和into语句一块用并且有且只有一条记录。
--将编号为7369的员工的员工编号和薪水查询出来并存储到v_empno，v_sal并输出
declare
v_empno emp.empno%type;
v_sal emp.sal%type;
begin
select empno,sal into v_empno,v_sal from emp where empno=7369;
dbms_output.put_line(v_empno || '-' || v_sal);
end;
--将编号为7369的员工的记录查询出来并存储到v_emp，并输出员工编号和薪水
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno=7369;
dbms_output.put_line(v_emp.empno || '-' || v_emp.sal);
end;
insert语句
--向dept表中插入一条数据
declare
v_deptno dept2.deptno%type := 50;
v_dname dept2.dname%type := 'dname';
v_loc dept2.loc%type := ‘ckg’;
begin
insert into dept2 values (v_deptno, v_dname, v_loc);
commit;
end;
和sql语句唯一不同的是采用了pl/sql变量。update与delete语句和sql语句一样。
PL/SQL的DDL语句
create语句
begin
execute immediate 'create table stu2(id number(10),name varchar2(20) default ''zhangsan'')';
end;
PL/SQL编写ddl语句和SQL语句不同的是需要加execute immediate ，单引号中的sql语句使用双单引号指定缺省值，如''zhangsan''。alter语句，drop语句同理。
PL/SQL的分支循环语句
判断语句
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno = 7369;
if(v_sal < 1200) then
dbms_output.put_line('low');
elsif(v_sal < 2000) then
dbms_output.put_line('middle');
else
dbms_output.put_line('high');
end if;
end;
注意红色语法部分即可。
循环语句
declare
i binary_integer := 1;
begin
loop
dbms_output.put_line(i);
i := i+1;
exit when ( i>=11);
end loop;
end;
注意红色语法部分即可。上面的循环相当于java里的 do-while 循环。
declare
j binary_integer := 1;
begin
while j < 11 loop
dbms_output.put_line(j);
j := j + 1;
end loop;
end;
注意红色语法部分即可。上面的循环相当于java里的while 循环。
begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
--逆序
for k in reverse 1..10 loop
dbms_output.put_line(k);
end loop;
end;
注意红色语法部分即可。上面的循环相当于java里的增强 for 循环。
游标
我们知道，select语句的结果集是一张表，如果我们想对结果集逐条记录遍历该如何实现，就像java中的迭代器一样？PL/SQL提供了解决遍历结果集的的功能：游标。游标是指在结果集上的指针，通过游标可以对select语句的结果集逐条记录遍历。
显示游标与隐式游标
oracle中的游标分为显示游标和隐式游标。显示游标是用cursor...is..命令定义的游标，它可以对查询语句(select)返回的多条记录进行处理。显式游标的操作：打开游标、操作游标、关闭游标。
隐式游标由Oracle数据库自动创建，名称是sql ,主要用途是可以返回一个操作是否成功或失败，只能用于DML语句。PL/SQL隐式地打开SQL游标，并在它内部处理SQL语句，然后关闭它。
游标属性
访问游标对象的属性方法：游标对象%游标属性。游标具有的属性如下：
%notfound 没有结果集
%found存在结果集
%rowcount 返回受影响的行数
%isopen询问游标是否已经打开
sql%rowcount可以统计刚执行的sql语句影响了多少条记录。
declare
v_deptno dept2.deptno%type := 50;
v_dname dept2.dname%type := 'dname';
v_loc dept2.loc%type := ‘can’;
begin
update dept2 set loc = 'sha' where deptno = 10;
dbms_output.put_line (sql%rowcount || '条记录被影响');
commit;
end;
--输出
1条记录被影响
循环游标
declare
cursor c is --声明游标指向select的结果集
select * from emp;
v_emp c%rowtype;
begin
open c; --打开游标
loop
fetch c into v_emp; --取出游标当前执向的值存入v_emp，每fetch一次，游标指向下一条记录
exit when (c%notfound); --找不到就退出
dbms_output.put_line(v_emp.ename);
end loop;
close c; --关闭游标
end;
--输出
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
可见，使用PL/SQL的游标和循环的结合，实现了对select结果集的遍历。
for循环游标
declare
cursor c is
select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
end loop;
end;
for循环中使用游标，不需要声明 v_emp变量，for开始的时候自动声明v_emp；不需要打开关闭游标；不需要每次fetch。因此，通常采用此写法。
带参数的游标
declare
cursor c(v_deptno emp.deptno%type, v_job emp.job%type) is
select ename, sal from emp where deptno = v_deptno and job = v_job;
begin
for v_temp in c (30, 'CLERK') loop
dbms_output.put_line(v_temp.ename);
end loop;
end;
注意红色语法部分即可。从这里我们也可以看出，实际上，真正fetch的时候，PL/SQL才回去数据库查询数据。
可更新的游标
declare
cursor c is
select * from emp2 for update;
begin
for v_temp in c loop
if(v_temp.sal < 2000) then
update emp2 set sal = sal * 2 where current of c;
elsif (v_temp.sal = 5000) then
delete from emp2 where current of c;
end if;
end loop;
注意红色语法部分即可。for update声明游标是更新用的，current of c 更新或者删除时指明是当前游标指向的记录。
存储过程
存储过程的创建
declare
cursor c is
select * from emp2 for update;
begin
for v_temp in c loop
if(v_temp.sal < 2000) then
update emp2 set sal = sal * 2 where current of c;
elsif (v_temp.sal > 2000) then
update emp2 set sal = sal / 2 where current of c;
end if;
end loop;
end;
对于上面这段PL/SQL代码，如果我们需要经常执行，可以将这段代码创建成存储过程，如下：
create or replace procedure p is
cursor c is
select * from emp2 for update ;
begin
for e in c loop
if(e.sal < 2000) then
update emp2 set sal = sal * 2 where current of c;
elsif(e.sal > 2000) then
update emp2 set sal = sal / 2 where current of c;
end if;
end loop;
end;
创建存储过程和普通的PL/SQL代码不同的是将“declare”改为“create or replace procedure p is ”，其他保持不变。
调用存储过程
--命令方式
Procedure created
SQL> exec p;
PL/SQL procedure successfully completed
SQL>
--另一种方式
begin
p;
end;
带参数的存储过程
create or replace procedure p
(v_a in number, v_b number, v_ret out number, v_temp in out number)
is
begin
if(v_a > v_b) then
v_ret := v_a;
else
v_ret := v_b;
end if;
v_temp := v_temp + v_a;
end;
--in 叫做传入参数，调用者负责给v_a赋值
--out 叫做传出参数，存储过程是没有返回值的,它就借助于传出参数
-- v_b 中间什么都没写，默认是in，是接收参数用的
-- v_temp 既可以接收，又可以传出
调用过程
declare
v_a number := 3;
v_b number := 4;
v_ret number;
v_temp number := 5;
begin
p(v_a, v_b, v_ret, v_temp);
dbms_output.put_line(v_ret);
dbms_output.put_line(v_temp);
end;
需要注意的是，执行存储过程并不会直接显示错误，可以使用show error命令显示编译错误。
删除存储过程
drop procedure p;
使用存储过程求emp表的树状结构
求出每个员工的上级（经理）并输出
create or replace procedure p_emp(v_empno emp.empno%type,v_level binary_integer) is
cursor c is
select * from emp where mgr = v_empno;
v_str varchar2(256) := '';
begin
for i in 1..v_level loop
v_str := v_str || ' ';
end loop;
for emp in c loop
dbms_output.put_line(v_str || emp.ename);
p_emp(emp.empno,v_level+1);--递归调用存储过程
end loop;
end;
--求出没有经理的员工
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where mgr is null;
dbms_output.put_line(v_emp.ename);
p_emp(v_emp.empno, 1);
end;
--或者我们已经知道7839是没有上级的
begin
p_emp(7839, 1);
end;
--输出
KING
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER
触发器
当对某一张表进行增删改查操作的时候，触发其他操作。
触发器的创建
--创建一张记录操作表
create table emp2_log-- emp2这张表的操作记录
(
uname varchar2(20),--用户
action varchar2(10),--操作
atime date--操作时间
);
--创建一个队emp2表操作的触发器
create or replace trigger trig
after insert or delete or update on emp2 for each row --表示每更新一条记录都会生成一条操作记录
--after可以改为before
begin
if inserting then
insert into emp2_log values(USER, 'insert', sysdate);--USER关键字,代表当前用户是谁
elsif updating then
insert into emp2_log values(USER, 'update', sysdate);
elsif deleting then
insert into emp2_log values(USER, 'delete', sysdate);--记录到log文件中
end if;
end;
触发器的执行
触发器不能直接执行，必须指明在哪张表上面执行哪些操作的时候才能触发触发器。现在对emp2表进行插入操作并查看emp2_log表
SQL> insert into emp2(ename,deptno) values('lisi',20);
SQL> select * from emp2_log;
UNAME ACTION ATIME
-------------------- ---------- -----------
SCOTT insert 2016/10/10
SCOTT insert 2016/10/10
SQL>
可见对emp2表的插入操作触发了日志记录操作。
触发器的删除
drop trigger trig;
基础篇到此。
