PL/SQL基础
一、PL/SQL快结构
PL/SQL块由三个部分组成:定义部分、执行部分、异常处理部分。定义部分用于定义常量、变量、游标、异常、复杂数据类型等;
执行部分用于实现应用模块功能,该部分包含了要执行的PL/SQL语句和SQL语句;异常处理部分用于处理执行部分可能出现的运行错误。
PL/SQL块的基本结构如下所示
DECLARE
/*
* 定义部分————定义常量、变量、复杂数据类型、游标
*/
BEGIN
/*
* 执行部分————PL/SQL语句和SQL语句
*/
EXCEPTION
/*
* 异常处理部分————处理运行错误
*/
END; /*快结束标记*/
如上所示,DECLARE关键字用于开始定义部分(可选),BEGIN关键字用于开始执行部分(必需),EXCEPTION关键字用于开始异常处理部分(可选),
END为PL/SQL块的结束标记。注意,DECLARE、BEGIN、EXCEPTION后面没有分号(;),而END后则必须带有分号(;)。
1.示例一,只包含执行部分的PL/SQL块
当编写PL/SQL块时,执行部分是必需的,而其他俩个部分是可选的。执行部分用于编写要执行的PL/SQL语句和SQL语句。
例:
set serveroutput on
BEGIN
dbms_output.put_line('Hello,everyone!');
END;
/
如上所示,dbms_ouput是用于输出信息的PL/SQL系统包,put_line是该包所包含的过程。
注意,当使用dbms_output包输出信息时,必需将SQL*PLUS环境变量serveroutput设置为on。
2.示例二,包含定义部分和执行部分的PL/SQL块
为了在PL/SQL块中使用变量、常量、异常和显示游标,应用开发人员就必须在定义部分定义变量、常量、异常和显示游标。
例:
set verify off
DECLARE
v_ename VARCHAR2(5);
BEGIN
SELECT ename INTO v_ename FROM emp
WHERE empno=&no;
dbms_output.put_line('雇员名:'||v_ename);
END;
/
3.示例三,包含定义部分、执行部分和异常处理部分的PL/SQL块
为了避免PL/SQL的运行错误,提高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_DATA_FOUND THEN
dbms_output.put_line('请输入正确的雇员号!');
END;
/
二、PL/SQL块分类
当使用PL/SQL开发应用模块时,根据需要实现的应用模块功能,可以将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;
/
2.命令块
命令块是指具有特定名称标识的PL/SQL块,命名块与匿名块非常类似,只不过在PL/SQL块前使用<<>>加以标记。
当使用嵌套快时,为了区分多级嵌套层次关系,可以使用命名块加以区分。
例:
<<outer>>
DECLARE
v_deptno NUMBER(2);
v_dname VARCHAR2(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>>
/
3.子程序
子程序是指存储在数据库或者客户端的PL/SQL过程、函数和包。
通过将商业逻辑和企业规则集成到PL/SQL子程序,不仅可以简化客户端程序的开发和维护,而且可以提高应用程序的性能
①过程。过程用于执行特定操作。下面以建立用于更新雇员工资的过程update_sal,并使用该过程修改SCOTT工资为例,
说明在SQL*Plus中建立和使用过程的方法。
例:
CREATE PROCEDURE update_sal1(name VARCHAR2,newsal NUMBER)
IS
BEGIN
UPDATE emp SET sal=newsal WHERE lower(ename)=lower(name);
END;
/
exec update_sal1('scott',2000)
②函数。函数用于返回特定数据。当建立函数时,函数头部必须包含RETURN子句,并且函数体必须包含RETURN语句。
例:
CREATE FUNCTION annual_income(name VARCHAR2) 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;
/
select annual_income('SCOTT') 年收入 FROM dual;
③包。包用于逻辑组合相关的过程和函数,它由包规范和包体两部分组成。包规范用于定义公用的常量、变量、过程和函数。
而包体则用于实现包规范中的过程和函数。
CREATE PACKAGE emp_pkg IS
PROCEDURE update_sal(name VARCHAR2,newsal NUMBER);
FUNCTION annual_income(name VARCHAR2) RETURN NUMBER;
END;
/
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 VARCHAR2) 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;
/
exec emp_pkg.update_sal('scott',1500)
SELECT emp_pkg.annual_income('scott') 年收入 FROM dual;
4.触发器
触发器是指被隐含执行的PL/SQL块。当触发了与触发器相关的事件之后,Oracle会隐含执行触发器的PL/SQL块。
例:
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 dept SET deptno=60 WHERE dname='ACCOUNTING';
SELECT ename FROM emp WHERE deptno=60;