PLSQL教程-优快云博客

一、PLSQL

简单 PLSQL 输出：

set serveroutput on;

declare

begin

dbms_output.put_line('Hello World');

end;

在默认的情况下，屏幕输出开关是关闭的，需要手动打开输出开关：set serveroutput on;，PLSQL 程序中是以 declare 为开始标记，declare 后面跟变量的说明，变量必须先定义后使用，PLSQL 程序体是在 begin end 内编写。PLSQL 也提供了异常即例外处理语句，exception 也应放在 begin end 的内部。

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

2、单一sql优点：交互式非过程化，数据操纵功能强，自动导航语句简单，调试容易方便。例如：为职工涨工资，每人涨10%。

updpate emp set sal=sal*1.1;

简单sql是命令式的，而非过程式的，而且操作的是实际的数据，简单sql最强的功能是直接操作数据，所以叫 DML 数据操作语言。而在实际中，业务逻辑并非这么简单，我们需要按照职位来涨工资。例如：按职工的职称涨工资，总裁涨1000元，经理涨800元，其他涨400元。这时候简单sql就无法完成这项任务，所以我们要把sql语言的数据操作能力与过程语言的数据处理能力结合起来，使 PLSQL 面向过程但比过程语言简单、高效、灵活和实用。

3、PLSQL 中的变量

声明变量：

varl char(15);（说明变量名、数据类型和长度后用分号结束说明语句）

married boolean := true;（为变量赋值应使用 := 赋值符）

psal number(7,2);

my_name emp.ename%type（引用型变量，即my_name的类型与emp表中ename列的类型一样，即使用emp表中ename的类型作为my_name的类型）

emp_rec emp%rowtype（记录型变量，emp_rec记录了emp中一个行记录的所有列类型，可以把emp_rec看成是一个数组，对应着emp表中所有列的类型）

例一：查询7839员工的姓名和工资（引用型变量练习）。

set serveroutput on;

declare

pename emp.ename%type;

psal emp.sal%type;

begin

select ename,sal into pename,psal from emp where empno=7839;

dbms_output.put_line(pename || '的工资是：' || psal);

end;（在PLSQL中为变量赋值有两种，一种是‘:=’一种是在查询的时候使用 into 关键字）

例二：查询7839员工的所有信息（记录型变量练习）。

set serveroutput on;

declare

emp_rec emp%rowtype;

begin

select * into emp_rec from emp where empno=7839;

dbms_output.put_line(emp_rec.ename || '的薪水是：' || emp_rec.sal || '，职位是：' || emp_rec.job);

end;

二、IF 语句

语法：if 条件 then 语句...

elsif 条件 then 语句...

end if;

例：判断用户输入的数字（从键盘输入：accept num prompt，得到键盘输入值：pnum number := &num，因为接收到用户输入的变量并不是存储的真实值，而是存储的内存地址，当我们取出变量值的时候需要用指针找出真实值 &num。prompt是与用户的交互提示）

       set serveroutput on;
       accept num prompt '请输入一个数字';
       declare
        pnum number := &num;
       begin
         if pnum=1 then dbms_output.put_line('您输入的是1');
        elsif pnum=2 then dbms_output.put_line('您输入的是2');
          elsif pnum=3 then dbms_output.put_line('您输入的是3');
         else dbms_output.put_line('您输入的是其他数字');
          end if;
       end;

三、循环语句

在 PLSQL 中有三种循环语法

1、while 条件

loop

.....

end loop;

2、loop

exit [when 条件]

.....

end loop;

3、for i in 1...3

loop

.....

end loop;

例：输出1-10之间的数字

             set serveroutput on;
             declare
              num number := 1;
             begin
                loop
                 exit when num > 10;
                 dbms_output.put_line(num);
                 num:=num+1;
              end loop;
             end;

三、游标（Cursor）

游标又叫作光标，它是一个数据的集合，类似于JDBC中的ResultSet，使用查询语句查询到一组数据赋给游标，但后每次从游标中抓取一行数据到一个变量中。光标默认情况下是关闭的，需要手动打开，在使用完光标后还需手动关闭。

1、语法：

cursor 光标名 [(参数名数据类型....)]

is select 语句;

2、光标是用于存储一个查询返回的多行数据，例如：

cursor is select ename from emp;

3、打开光标：opent c1;

4、取出一行光标的值：fetch c1 into pjob（取一行到变量中）

5、关闭光标：close c1;（如果不关闭光标，不会立即出错，时间久了会有：error:too many open cursor，因为光标只能打开15个）

注意：上面的 pjob 必须与emp表中的 job 类型一致

例：使用游标查询员工姓名和工资，并打印。

set serveroutput on;

declare

cursor myc is select ename,sal from emp;

pname emp.ename%type;

psal emp.sal%type;

begin

open myc;

loop

fetch myc into pname,psal;

exit when myc%notfound;

dbms_output.put_line(pname||'的工资是：'||psal);

end loop;

close myc;

end;

四、带参数的光标

语法：cursor c(pjob varchar2)

select ename,sal from emp where job=pjob;

在打开光标的时候指定参数：open c(10);

例：查询10号部门的员工信息

set serveroutput on;

declare

cursor cemp(cdetpno emp.deptno%type)

select ename,sal,job from emp where deptno=cdetpno;

cname emp.ename%type;

csal emp.sal%type;

cjob emp.job%type;

begin

open cemp(10);

loop

fetch cemp into cname,csal,cjob;

fetch cemp into cname,csal,cjob;
exit when cemp%notfound;

dbms_output.put_line(cname||'工资：'||csal||'，职位：'||cjob);

end loop;

close cemp;

end;

五、例外

例外是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性。

语法：declare

.......

exception

when exception1 then ....

when exception2 then ....

endl

1、Oracle例外分为系统定义例外和用户定义例外

2、系统定义例外：no_data_found（没有找到数据）

too_many_rows（slect ...into 语句匹配多个行）

zero_divide（被零除）

value_error（算数转换错误）

timeout_on_resource（在等待资源时发生超时）

例：被0除例外

set serveroutput on;

declare

num number;

begin

num := 1/0;

exception

when zero_divide then dbms_output.put_line('被0除');

end;

六、存储过程和存储函数

指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。存储过程与存储函数的区别是：存储过程没有返回值，存储函数可以有返回值。

1、存储过程

（1）语法：create [or replace] procedure 过程名(参数列表)

PLSQL 子程序体

存储过程只能创建和替换，不能修改。

例：输出 Hello World

create procedure hello

begin

dbms_output.put_line('Hello World');

end;

调用存储过程：两种方式：

一：exec hello();

二：在另一个子程序中调用

declare

begin

hello();

end;

（2）带参数的存储过程：无论是存储过程还是存储函数，都有输入参数和输出参数，输入参数前应加 in 关键字，输出函数前应加 out 关键字

例：为指定的员工在原工资基础上增长10，并显示涨前和涨后的工资

create procedure raisesal(eid in number)
as
osal emp.sal%type;
nsal emp.sal%type;
begin
    select sal into osal from emp where empno=eid;
    update emp set sal=osal*1.1 where empno=eid;
    nsal:=osal*1.1;
    dbms_output.put_line('涨前工资：'||osal||'，涨后工资：'||nsal);
end;
set serveroutput on;
exec raisesal(7839)

（3）存储函数，函数为一命名的存储程序，可带参数，并返回一计算值，函数和过程的结构类似，但必须有有一个return子句，用于返回函数值。函数说明要指定函数名、结果的类型，以及参数类型等。

语法：

create or replace function 函数名（参数列表）

return 返回值类型

PLSQL 子程序体

例：查询某个员工的年收入

create or replace function showsal(eno emp.empno%type)
return emp.sal%type
as
income emp.sal%type;
begin
select sal*12+nvl(comm,0) into income from emp where empno=eno;
return income;
end;

set serveroutput on;
declare
begin
dbms_output.put_line(showsal(7839));
end;

（4）一般来说，过程和函数的区别在于函数可以有返回值，而过程没有返回值，但过程和函数都可以通过out指定一个或多个输出参数，我们可以利用out参数在过程和函数中实现返回多个值。存储过程和存储函数的选择原则是：如果只有一个返回值，用存储函数，否则就用存储过程。

七、触发器

数据库触发器是一个与表相关的、存储的PL/SQL 程序。每当一个特定的数据操作语句(insert、update、delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。

触发器分为：语句级触发器和行级触发器。语句级触发器是指在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行。行级触发器是指出发语句作用的每一条记录都被触发，在行级触发器中使用 old 和 new 伪记录变量识别值的状态。语句级触发器是针对表而言，行级触发器是针对行而言。

触发器可用于：数据确认，实施复杂的安全性检查，做审计跟踪表上所做的数据操作，数据备份和同步

语法：create or replace trigger 触发器名

{before|after}

{delete|insert|update[of列名]}

on 表名

[for each row [where 条件]]（行级触发器需要指定）

PLSQL

例一：限制在非工作时间插入数据

create or replace trigger myt
before insert on emp
declare
begin
if to_char(sysdate,'day') in ('星期三','星期六','星期日')
or
to_number(to_char(sysdate,'hh24')) not between 9 and 17
then raise_application_error(-20001,'不能在非工作时间插入数据');
end if;
end;

例二：数据确认。涨后的工资不能比涨前的少

create or replace trigger myt
before update on emp for each row
declare
begin
if :old.sal>:new.sal then
raise_application_error(-20002,'涨后工资不能比涨前的少');
end if;
end;

select * from user_riggers;（查询当前用户下的所有触发器）

select * from user_source;（可以查询触发器、存储过程、存储函数的源码，同样）

八、数据在字典

在Oracle数据库中有两类的表存在，一类是用户管理的表，对应着实际的商业信息等，由我们自己维护，另一类就是数据字段，是数据库自己维护的，包含数据库常用的一些信息，相当于JDBC中的元数据。

数据字典的规则：（dictionary是数据字典的总表）

user 用户自己的

all 用户可以访问的

dba 管理员视图

v$ 性能相关的数据