PL/Sql学习笔记

声明变量 一、概述：
1、PLSQL 块结构：
DECLARE --- 可选
变量声明定义
BEGIN ---- 必选
SQL 和PLSQL 语句
EXCEPTION ---- 可选
错误处理
END；---- 必选

constant ----表时声明的是常量
:= ----赋值

二、实例：
declare
vjob varchar(9);
v_count number:=0;
vtotal date:=sysdate +7;
c_tax constant number(3,2):=8.25;
v_valid boolean not null:=true;
begin
select sysdate into vtotal from dual;
end;
/
上例中，如果没有这个SELECT语句，会如何？
出错，说明必须有STATEMENTS
如果： select sysdate from dual into vtotal ;
同样，也不行。而且变量与赋值的类型要匹配。
三、%TYPE的属性
声明一个变量使之与数据库某个列的定义相同或与另一个已经定义过的变量相同
所以%TYPE要作为列名的后缀：如：
v_last_name s_emp.last_name%TYPE;
v_first_name s_emp.first_name%TYPE; --这样做的好处是我们不必去知晓此列的类型与定义
或：v_balance NUMBER(7,2);
v_minimum_balance v_balance%TYPE := 10;
四、声明一个布尔类型的变量
1 只有TRUE、FALSE、NULL可以赋值给BOOLEAN变量
2 此变量可以接逻辑运算符NOT、AND、OR。
3、变量只能产生TRUE、FALSE、NULL。
实例：
VSAL1：=50000；
VSQL2：=60000；
VCOMMSAL BOOLEAN：=(VSAL1--其实是把TRUE赋值给此变量。
五、LOB 类型的变量
共有CLOB、BLOB、BFILE、NCLOB几种，这里不做为重点。
六：使用HOST VARIABLES
SQL> variable n number
SQL> print n
：n=v_sal /12;
:n这个加了：前缀的变量不是PLSQL变量，而是HOST。

控制结构

一。IF..THEN
语法：
IF condition THEN
Statements 1;
Statements 2;
....
END IF

二。 IF..THEN...ELSE
语法:
IF condition THEN
Statements 1;
Statements 2;
....
ELSE
Statements 1;
Statements 2;
....
END IF

三。 IF..THEN..ELSIF
语法：
IF condition1 THEN
statement1;
ELSIF condition2 THEN
statement2;
ELSIF condition3 THEN
statement3;
ELSE
statement4;
END IF;
statement5;

循环控制

一。循环控制的基本形式是LOOP语句，LOOP和END LOOP之间的语句将无限次的执行。在使用LOOP语句时必须使用EXIT语句，强制循环结束。

LOOP语句的语法如下：

LOOP
statements;
END LOOP

例如：
X:=100;
LOOP
X:=X+10;
IF X>1000 THEN
EXIT;
END IF
END LOOP;
Y:=X;

此时Y的值是1010.

WHILE..LOOP

WHILE..LOOP有一个条件与循环相联系，如果条件为TRUE,则执行循环体内的语句，如果结果为FALSE,则结束循环。

X:=100;
WHILE X<=1000 LOOP
X:=X+10;
END LOOP;
Y=X;

FOR...LOOP

语法：

FOR counter IN [REVERSE] start_range....end_range LOOP
statements;
END LOOP;

LOOP和WHILE循环的循环次数都是不确定的，FOR循环的循环次数是固定的，counter是一个隐式声明的变量，他的初始值是start_range,第二个值是start_range+1,直到end_range,如果start_range等于end _range,那么循环将执行一次。如果使用了REVERSE关键字，那么范围将是一个降序。

X:=100;
FOR v_counter in 1..10 loop
x:=x+10;

end loop
y:=x;

如果要退出for循环可以使用EXIT语句。

标签

用户可以使用标签使程序获得更好的可读性。程序块或循环都可以被标记。标签的形式是<>。

标记程序块

<>
[DECLARE]
... ... ...
BEGIN
........
[EXCEPTION]
.......
END label_name

标记循环

<>
LOOP
.........
<>
loop
..........
<>
loop
....

EXIT outer_loop WHEN v_condition=0;
end loop innermost_loop;
..........
END LOOP inner_loop;
END LOOP outer_loop;

GOTO语句

语法： GOTO LABEL;

执行GOTO语句时，控制会立即转到由标签标记的语句。PL/SQL中对GOTO语句有一些限制，对于块、循环、IF语句而言，从外层跳转到内层是非法的。

X ：=100;
FOR V_COUNTER IN 1..10 LOOP
IF V_COUNTER =4 THEN
GOTO end_of_loop
END IF
X:=X+10;
<>
NULL
END LOOP
Y:=X;

注意：NULL是一个合法的可执行语句。

嵌套

程序块的内部可以有另一个程序块这种情况称为嵌套。嵌套要注意的是变量，定义在最外部程序块中的变量可以在所有子块中使用，如果在子块中定义了与外部程序块变量相同的变量名，在执行子块时将使用子块中定义的变量。子块中定义的变量不能被父块引用。同样GOTO语句不能由父块跳转道子块中，反之则是合法的。

=================================================

set serveroutput on --设置环境变量serveroutput为打开状态，以便可以在SQL*Plus等中输出结果
dbms_output.PUT_LINE() --输出变量等的值

=================================================

存储过程

CREATE[OR REPLACE] PROCEDURE<过程名>
[<参数列表>]IS|AS
[<局部变量声明>]
BEGIN
<过程体>
END[<过程名>];
参数声明的格式如下：
<参数名>[IN|OUT|IN OUT]<数据类型>[:=<初始值>]
IN表示此参数接受过程外传递来的值；
OUT表示此参数将在过程中被赋值，并传递到过程体外；
INT OUT表示此参数同时具有IN和OUT的特性。
前面已经了解了关于PL/SQL编程的基础，本文将结合一个案例来加深对这些知识点的理解。

　　一． 案例介绍（转自网络）

　　某数据库有两张表，是关于某公司员工资料、薪水和部门信息的，它们分别是emp表和dept表，两张表的结构如下：

　　要求如下：

　　1、按照上表结构建立相应的表，并每张表写入5组合法数据。

　　2、操纵相关表，使得“技术部”的员工的薪水上涨20%。

　　3、建立日志，追踪薪水变动情况。

　　4、建立测试包。

　　二． 案例的分析与实现

　　从前面案例的介绍不难看出，要求1考察点为基本SQL语句；要求2主要考察复合查询；要求3是考察触发器的应用；要求4的考察面相对多一些，不仅考察了包的创建，而且也考察了在PL/SQL中的测试方法。了解了这些考察的知识点，就可以一一去解决。

　　要求1：

　　首先根据前面表的结构可以创建两张表：

　　——创建员工表

create table emp (emp_id number(5), emp_name varchar2(20), emp_salary number(4));

　　——部门表

create table dept (dept_id number(3), dept_name varchar2(20), emp_id number(5));

　　建立了表之后就可以往表里面写数据了，这里把添加表记录的代码写入到相应的存储过程。

create or replace procedure ins_table_emp(p_emp_id number,p_emp_name varchar2,p_emp_salary number) as
v_emp_id number:=p_emp_id;
v_emp_name varchar2(20):=p_emp_name;
v_emp_salary number:=p_emp_salary;
begin
　insert into emp values (v_emp_id,v_emp_name,v_emp_salary);
end ins_table_emp;

create or replace procedure ins_table_dept(p_dept_id number,p_dept_name varchar2,p_emp_id number) as
　v_dept_id number:=p_dept_id;
　v_dept_name varchar2(20):=p_dept_name;
　v_emp_id number:=p_emp_id;
begin
　insert into dept values (v_dept_id,v_dept_name,v_emp_id);
end ins_table_emp;

begin
　ins_table_emp(10000,'',4000);
　ins_table_emp(10001,'??èy',2300);
　ins_table_emp(10002,'3?t',3500);
　ins_table_emp(10003,'à???',3500);
　ins_table_emp(10004,'á?ò?',3500);

　ins_table_dept(111,'DD?t2?',10000);
　ins_table_dept(111,'DD?t2?',10001);
　ins_table_dept(111,'DD?t2?',10002);
　ins_table_dept(112,'??ê?2?',10003);
　ins_table_dept(113,'êD3?2?',10004);
end;

　　要求2：

　　给指定部门的员工加薪，这实际上是一个复合查询，首先需要把所有该部门的员工塞选出来，然后对这些员工的薪水进行相应的改动。依照这一思路，代码如下：（需要注意的是：将要加薪的部门作为参数，这样的存储过程更有灵活性。）

create or replace procedure add_salary(p_dept_name varchar2) as
v_dept_name varchar2(20):=p_dept_name;
begin
　update emp set emp.EMP_SALARY=emp.EMP_SALARY*1.2 where emp.EMP_ID in (select emp.EMP_ID

from emp,dept where emp.EMP_ID=dept.EMP_ID and dept.DEPT_ID='??ê?2?');
end add_salary;

　　要求3：

　　建立日志对薪水的变动情况形成一个追踪，也就是说，如果对某个职员的薪水进行变更就应该将其相应的变更记录全部记下来。如果对emp表的salary字段创建一个触发器，来监视对salary的更改，把每次更改进行记录，这样就达到了要求3的目的了。

create or replace trigger print_salary_change 　
before delete or insert or update on emp　　--触发事件
for each row　　　　　　　　　　　　 -- 每修改一行都需要调用此过程

declare 　　　　　　　　　　--只有触发器的声明需要declare，过程和函数都不需要
salary_balance number;
begin
--:new 与:old分别代表该行在修改前和修改后的记录
salary_balance=:new.salary=:old.salary;
dbms_output.PUT_LINE('old salary is: '|| :old.salary);
dbms_output.PUT_LINE('old salary is: '|| :new.salary);
dbms_output.PUT_LINE('old salary is: '|| to_char(salary_balance));
end print_salary_change;

　　要求4：

　　与其他语言（c/c++等）相比，PL/SQL的测试有其不同之处，归纳下来有三种方法：

　　1、使用DBMS_OUTPUT包的PUT_LINE方法来显示中间变量，以此来观察程序是否存在逻辑错误。

　　2、插入测试表的方法。即创建一个临时的中间表，然后把所有涉及到的中间变量的结果都作为记录插入到中间表中，这样可以查询表中的结果来观察程序的执行情况。

　　3、使用异常处理手段，对可疑的程序段使用begin … end ，然后可以在exception里进行异常捕获处理。

　　这里准备使用第二种方法来建立一个测试包，PL/SQL里包的概念类似于面向对象里的类的概念，包将一组操作和属性封装在一起，不仅增强了程序的模块化，而且由于封装了更多的操作和属性而提高了执行效能。建立一个PL/SQL需要两个步骤：首先要建立包头，类似于建立一个类的头文件，里面主要对包中的过程，函数和变量的声明；第二部分主要是包体部分，实现前面声明的过程和函数，另外还需要对包进行初始化等工作。

　　根据这一思路，建立测试包如下：

create or replace package debug as
procedure debug(v_description varchar2,v_valueOfvariable varchar2)
　procedure reset;
　v_numberOfLine number;
end debug;

create or replace package body debug as
procedure debug(v_description varchar2,v_valueOfvariable varchar2) is
begin
　insert into debugtable
　values(v_numberOfLine,v_description,v_valueOfvariable);
　v_numberOfLine:=v_numberOfLine+1;
end debug;
procedure reset is
begin
　v_numberOfLine:=1;
　delete from debugtable;
end reset;

begin
　reset;
end debug;

　　三．小结

　　综合前面对4个问题的解答，基本把PL/SQL的主要部分融会进来了，虽然很多地方只是涉及到比较粗浅的层次，但是有了这一基础，深入下去也是不难的。

　　总之，PL/SQL编程与其他语言编程有一定的区别，读者只有把握好其特点才能更好的掌握数据库开发

的方面知识。

触发器(Triggers)

可以设置为在触发器事件之前或之后触发或执行。能够触发触发器事件的事件包括下面几种：

DML事件 DDL事件 数据库事件
DML事件触发器可以是语句或行级触发器。DML语句触发器在触发语句之前或之后触发DML行级触发器在语句影响的行变化之前或之后触发。用户可以给单一事件和类型定义多个触发器，但没有任何方法可以增强多触发器触发的命令。

触发器事件：
INSERT 当向表或视图插入一行时触发触发器
UPDATE 更新表或视图中的某一行时触发触发器
DELETE 从表或视图中删除某一行时触发触发器
CREATE 当使用CREATE语句为数据库或项目增加一个对象时触发触发器
ALTER 当使用ALTER语句为更改一个数据库或项目的对象时触发触发器
DROP 当使用DROP语句删除一个数据库或项目的对象时触发触发器
START 打开数据库时触发触发器，在事件后触发
SHUTDOWN 关闭数据库时触发，事件前触发
LOGON 当一个会话建立时触发，事件前触发
LOGOFF 当关闭会话时触发，事件前触发
SERVER 服务器错误发生时触发触发器，事件后触发

创建触发器的语法如下：
CREATE [OR REPLACE] TRIGGER trigger_name
{before|after|instead of} event
ON {table_or_view_name|DATABASE}
[FOR EACH ROW[WHEN condition]]
trigger_body
trigger_name ---触发器名
event ---触发事件
table_or_view_name ---表名/视图名
condition ---条件达式
trigger_body---PL-SQL程序主体
只有DML触发器（INSERT、UPDATE、DELETE)语句可以使用INSTEAD OF触发器并且只有表的DML触发器可以是BEFORE或AFTER触发器。

将触发器设置为禁用或启用使用ALTER TRIGGER语句：
ALTER TRIGGER trigger_name ENABLE;
ALTER TRIGGER trigger_name DISABLE;
要禁用或启用表的所有触发器，使用ALTER TABLE语句
ALTER TRIGGER table_name DISABLE ALL TRIGGER;
ALTER TRIGGER table_name ENABLE ALL TRIGGER;
删除触发器使用DROP TRIGGER
DROP TRIGGER trigger_name;

实例：
创建一个触发器MyTrigger,它的作用是当表HOTELMAN.RoomType中TypeId列的值发生改变时，自动更新表HOTELMAN.Room中的TypeId列的值，从而保证数据的完整性。

CREATE OR REPLACE TRIGGER HOTELMAN.MyTrigger
AFTER UPDATE ON HOTELMAN.RoomType
FOR EACH ROW
BEGIN
UPDATE HOTELMAN.Room SET TypeId = :new.TypeId
WHERE TypeId = :old.TypeId;
END;

这里的:new和:old分别是两个虚拟的表，分别表示执行INSERT,UPDATE,DELETE等操作后的新表和执行这些操作之前的旧表。