1、BLOCK块是PL/SQL的基本程序单元,编写PL/SQL程序实际就是编写PL/SQL块。
2、PL/SQL块由三部分组成:定义部分、执行部分、例外处理部分。
定义部分:定义常量、变量、游标、例外、复杂数据类型等;
执行部分:是想应用模块功能,包含要执行的PL/SQL/语句和SQL语句;
例外处理部分:处理执行部分可能出现的运行错误。
PL/SQL块的基本结构:
DECLARE
/*
* 定义常量、变量、游标、例外
*/
BEGIN
/*
* PL/SQL语句和SQL语句
*/
EXCEPTION
/*
*处理运行错误
*/
END;/*块结束标记*/
例:
set serveroutput on
BEGIN
dbms_output.put_line('Hello,everyone!');
END;
/
hello,everyone!
执行这该PL/SQL快时,会输出消息“Hello,everyone!”.其中dbms_output是ORACLE所提供的系统包,put_line是该包所包含的过程,用于输出字符串信息。注意,当使用dbms_output包输出数据或消息时,必须要将SQL*PLUS的环境变量serveroutput设置为on.
例2:包含定义部分和执行部分的PL/SQL块
为了在PL/SQL块中使用变量、常量、例外和显式游标,开发人员必须要在定义部分定义变量、常量、例外和显示游标。如下:
DECLARE
v_ename VARCHAR2(5);
BEGIN
SELECT ename INTO v_ename FROM emp
WHERE empno=&no;
dbms_output.put_line('雇员名:'||v_ename);
EXCEPTION
WHEN NO_DATE_FOUND THEN
dbms_output.put_line('请输入正确的雇员号!');
END;
/
输入no的值:7788
雇员名:SCOTT
当执行该PL/SQL块时,会根据输入的雇员号显示雇员号。为了临时存放雇员名,就必须要定义变量。&no为SQL*PLUS的替代变量。替代变量的作用参加附录B.
PL/SQL块分类
1、匿名块(没有名称的PL/sql块,可以内嵌到应用程序(例如PRO*c/C++)中,也可以在交互式环境中(例如SQL*PLUS)直接使用。)
例:DECLARE
v_avgsal NUMBER(6,2);
BEGIN
SELECT avg(sal) INTO v_avgsal FROM emp
WHERE deptno=&no;
dbms_output.put_line('平均工资:'||v_avgsal);
END;
/
输入no的值:10
平均工资:2916.67
2、命名块(具有特定名称标识的PL/SQL块,与匿名块非常类似,只不过在PL/SQL块前使用<<>>加以标记。当使用嵌套块时,为了区分多级嵌套层次关系,可以使用命名块加以区分。)
例:<<outer>>
DECLARE
v_deptno NUMBER(2);
v_dname VARCHAR(10);
BEGIN
<<inner>>
BEGIN
SELECT deptno INTO v_deptno FROM emp
WHERE lower(ename)=lower('&name');
END;--<<inner>>
SELECT dname INTO v_dname FROM dept
WHERE deptno=v_deptno;
dbms_output.put_line('部门名:'||v_dname);
END;--<<outer>>
/
输入name的值:scott
部门名:RESEARCH
<<outer>>和<<inner>>分别是主块(外层块)和子快(内层块)的标记。
3、子程序
包括过程、函数和包。
过程:用于执行特定操作。当建立过程时,既可以指定输入参数IN,也可以指定输出参数OUT。
CREATE PROCEDUCRE update_sal(name VARCHAR2,newsal NUMBER)
IS
BEGIN
UPDATE emp SET sal=newsal
WHERE lower(ename)=lower(name);
END;
/
过程update_Sal用于更新雇员工资。可以使用execute命令或call命令。
如:SQL>exec update_sal('scott',2000)
SQL>call update_sal('scott',2000)
函数:用于返回特定数据。
CREATE FUNCTION annual_income (name VARCHAR2)
RETURN NUMBER IS
annual_slary NUMBER(7,2);
BEGIN
SELECT sal*12+nvl(comm,0) INTO annual_salary
FROM emp WHERE lower(ename)=lower(name);
RETURN annual_salary;
END;
/
nvl(comm,0),如果comm为空,程序自动使用0值来表示该值,如果不空就使用该值。
上例,函数annual_income用于返回雇员的全年收入(包括工资和奖金)。
调用函数:SQL>VAR income NUMBER
SQL>CALL annual_income('scott') INTO income;
SQL>PRINT income
INCOME
--------------------
24000
包:用于逻辑组合相关的过程和函数,它由包规范和包体两部分组成。包规范用于定义公用的常量、变量、过程和函数。使用CREATE PACKAGE命令
CREATE PACKAGE emp_pkg IS
PROCEDURE update_sal(name VARCHAR2,newsal NUMBER);
FUNCTION annual_income(name VACHAR2) RETURN NUMBER;
END;
/
包规范之包含了过程和函数说明,而没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。使用CREATE PACKAGE BODY命令。
CREATE PACKAGE BODY emp_pkg IS
PROCEDURE update_sal(name VARCHAR2,newsal NUMBER);
IS
BEGIN
UPDATE emp SET sal=newsal
WHERE lower(ename)=lower(name);
END;
FUNCTION annual_income(name VACHAR2) RETURN NUMBER
IS
annual_salary NUMBER(7,2);
BEGIN
SELECT sal*12++nvl(comm,0) INTO annual_salary
FROM emp WHERE lower(ename)=lower(name);
RETURN annual_salary;
END;
END;
/
当调用包的过程和函数时,在过程和函数名之前必须要带有包名作为前缀(包名.子程序名),而如果要访问其他方案的包,还必须要加方案名作为前缀(方案名.包名.子程序名)。
例如:SQL> call emp_pkg.update_sal('scott',1500);
SQL>VAR income NUMBER
SQL>CALL emp_pkg.annual_income('scott') INTO:income;
SQL>PRINT income
INCOME
------------
18000
触发器
触发器是指隐含执行的存储过程。当定义触发器时,必须要指定触发事件以及触发操作,常用的触发事件包括INSERT,UPDATE,DELETE语句,而触发操作实际上是一个PL/SQL块。建立触发器使用CREATE TRIGGER命令来完成。
例:CREATE TRIGGER update_cascade
AFTER UPDATE OF deptno ON dept FOR EACH ROW
BEGIN
UPDATE emp SET deptno=:new.deptno
WHERE deptno=:old.deptno;
END;
/
如上例所示,触发器update_cascade用于实现级联更新,如果不建立该触发器,那么当更新dept表中 的deptno列数据时就会显示错误“ORA-02292:违反完整约束条件(SCOTT.FK_DEPTNO)-已找到子记录日志”,而在建立了该触发器之后,当更新deptno列时,就会级联更新emp表中的deptno列的相关数据。