共计 8788 个字符，预计需要花费 22 分钟才能阅读完成。
匿名 PL/SQL
语法结构：PL/SQL 是一种块结构的语言，组成 PL/SQL 程序的单元是逻辑块，一个 PL/SQL 程序包含了一个或多个逻辑块，每一块都可以划分 3 个部分。变量在使用前必须声明，PL/SQL 提供了独立的专门用于处理异常的部分。
在 PL/SQL 块中可以使用 SELECT INSERT UPDATE DELETE 等 DML 语句、事务控制语句以及 SQL 函数等，不允许直接使用 CREATE DROP 或者 ALERT 等 DDL 语句，但可以通过动态 SQL 来执行。
三个部分为：
1、声明部分：声明部分包含了块中使用的变量、游标、自定义异常，由关键字 DECLARE 开始。如果不需要声明变量或常量，可以忽略这部分。
2、执行部分：是 PL/SQL 块中的指令部分，由关键字 BEGIN 开始，END 结束，这部分是必选项。
3、异常处理部分：可选，处理异常或错误。
语法：
[DECLARE]
DECLARATION STATEMENTS
BEGIN
EXECUTABLE STATEMENTS
[EXCEPTION]
EXCEPTION STATEMENTS
END;
变量的声明和赋值 ——— PL/SQL 是强类型语言，变量在使用前必须声明。
声明语法：VAR_NAME [CONSTANT] DATETYPE [NOT NULL] [:=|DEFAULT VALUE]
VAR_NAME: 表示变量名称 datetype：表示变量的 SQL 或 PL/SQL 数据类型 VAULE：表示变量的初始值
NOT NULL 可选，表示给变量强制地加约束条件，此时变量必须初始化。
CONSTANT 可选，表示常量。VALUE 在声明时必须初始化。且常量的值在程序内部不能改变。
赋值：1、varname:=expression; 2、通过 SELECT INTO 给变量赋值
示例：
DECLARE
SDNAME VARCHAR2(20);
BEGIN
SDNAME:=’JOHN’;
END;
示例：
DECLARE
SDNAME VARCHAR2(20);
BEGIN
SELECT ENAME INTO SDNAME FROM EMP WHERE EMPNO=’7902’;
END;
属性用于引用变量或数据库列的数据类型，以及表中的一行数据。
例: 属性用于引用变量的数据类型 %TYPE
– 查询员工 SMITH 的所有信息
DECLARE
BIANHAO EMP.EMPNO%TYPE;
XINGMING EMP.ENAME%TYPE;
GONGZUO EMP.JOB%TYPE;
LINGDAO EMP.ENAME%TYPE;
RUZHISHIJIAN EMP.HIREDATE%TYPE;
GONGZI EMP.SAL%TYPE;
JIANGJIN EMP.COMM%TYPE;
BUMENG DEPT.DNAME%TYPE;
BEGIN
SELECT E.EMPNO,E.ENAME,E.JOB,(SELECT ENAME FROM EMP WHERE EMPNO=E.MGR),
E.HIREDATE,E.SAL,NVL(E.COMM,0),(SELECT DNAME FROM DEPT WHERE E.DEPTNO=DEPT.DEPTNO)
INTO BIANHAO,XINGMING,GONGZUO,LINGDAO,RUZHISHIJIAN,GONGZI,JIANGJIN,BUMENG FROM EMP E WHERE ENAME=’SMITH’;
DBMS_OUTPUT.PUT_LINE(‘ 员工编号:’||BIANHAO);
DBMS_OUTPUT.PUT_LINE(‘ 姓名:’||XINGMING);
DBMS_OUTPUT.PUT_LINE(‘ 工种:’||GONGZUO);
DBMS_OUTPUT.PUT_LINE(‘ 领导:’||LINGDAO);
DBMS_OUTPUT.PUT_LINE(‘ 入职时间:’||RUZHISHIJIAN);
DBMS_OUTPUT.PUT_LINE(‘ 工资:’||GONGZI);
DBMS_OUTPUT.PUT_LINE(‘ 奖金:’||JIANGJIN);
DBMS_OUTPUT.PUT_LINE(‘ 所属部门:’||BUMENG);
END;
另一种属性类型 %ROWTYPE
例：
– 创建视图
CREATE VIEW EMP_VIEW AS
SELECT EMPNO,ENAME,JOB,(SELECT ENAME FROM EMP WHERE EMPNO=E.MGR) MGR,HIREDATE,SAL,NVL(COMM,0) COMMM,
(SELECT DNAME FROM DEPT WHERE E.DEPTNO=DEPT.DEPTNO) DNAME FROM EMP E;
– 查询视图
SELECT * FROM EMP_VIEW;
– 使用 %ROWTYPE 属性
DECLARE
EMP_SOURCE EMP_VIEW%ROWTYPE;– 声明变量 EMP_SOURCE 变量保存的是视图某一行的所有对象
BEGIN
SELECT * INTO EMP_SOURCE FROM EMP_VIEW WHERE EMP_VIEW.ENAME=’SMITH’;
DBMS_OUTPUT.PUT_LINE(‘ 员工编号:’||EMP_SOURCE.EMPNO);
DBMS_OUTPUT.PUT_LINE(‘ 姓名:’||EMP_SOURCE.ENAME);
DBMS_OUTPUT.PUT_LINE(‘ 工种:’||EMP_SOURCE.JOB);
DBMS_OUTPUT.PUT_LINE(‘ 领导:’||EMP_SOURCE.MGR);
DBMS_OUTPUT.PUT_LINE(‘ 入职时间:’||EMP_SOURCE.HIREDATE);
DBMS_OUTPUT.PUT_LINE(‘ 工资:’||EMP_SOURCE.SAL);
DBMS_OUTPUT.PUT_LINE(‘ 奖金:’||EMP_SOURCE.COMMM);
DBMS_OUTPUT.PUT_LINE(‘ 所属部门:’||EMP_SOURCE.DNAME);
END;
– 变量直接赋值
– 部门编号 名称 地址 使用 insert 语句
CREATE VIEW DEPT_VIEW AS SELECT * FROM DEPT;– 创建视图
SELECT * FROM DEPT_VIEW;– 查询视图
– 向视图里插入数据
DECLARE
DEPTNO DEPT_VIEW.DEPTNO%TYPE;
DNAME DEPT_VIEW.DNAME%TYPE;
LOC DEPT_VIEW.LOC%TYPE;
BEGIN
DEPTNO:=50;
DNAME:=’JISHUBU’;
LOC:=’SHANGHAI’;
INSERT INTO DEPT_VIEW VALUES (DEPTNO,DNAME,LOC);
END;
循环控制语句用于重复执行一系列语句，包括 loop 和 exit 语句，使用 exit 语句可以立即退出循环，使用 exit when 语句可以根据条件结束循环。
循环共分 3 种类型：loop 循环：在 loop 和 end loop 之间的一系列语句，为避免陷入无限循环，loop 循环中必须使用 exit 和 exit when 语句。
语法：loop
seq_of_statements;
exit(exit when);
end loop;
—loop 循环
DECLARE
X NUMBER:=1000;
Y NUMBER;
BEGIN
LOOP
X:=X-10;
Y:=X;
DBMS_OUTPUT.put_line(Y);
DBMS_OUTPUT.PUT_LINE(X);
EXIT WHEN X<100;
END LOOP;
END;
PL/SQL 支持的内置数据类型：
数据类型 标量类型：数字 字符 布尔型 日期时间
LOB类型 BFILE BLOB CLOB NCLOB
属性类型 %type 提供某个变量或数据库表列的数据类型 %rowtype 提供表示表中一行的记录类型
使用属性类型的优点：1、不需要知道被引用的列或表的具体数据类型
2、如果更改了被引用对象的数据库定义，那么 PL/SQL 在运行时变量的数据类型也随之更改
名称 | 类型 | 说明 |
NUMBER | 数字型 | 用于存储整数、定点数和浮点数，可以定义精度和取值范围 |
BINARY_INTEGER | 数字型 | 用于存储带符号整数 |
INTEGER | 数字型 | NUMBER 的子类型，用于声明高精度为 38 位的十进制整数 |
REAL | 数字型 | NUMBER 的子类型，用于声明高精度为 63 位的二进制浮点数 |
FLOAT | 数字型 | NUMBER 的子类型，用于声明高精度为 126 位的二进制浮点数 |
CHAR | 字符型 | 用于存储固定长度字符，指定不超过 32767 个字节的长度 |
VARCHAR2 | 字符型 | 用于存储可变长度字符，指定不超过 32767 个字节的长度 |
DATE | 日期型 | 用于存储日期和时间值 |
BOOLEAN | 布尔型 | 用于存储逻辑值，TRUE、FALSE 和 NULL |
BFILE | LOB 类型 | 该数据类型用于将大型二进制对象存储在系统文件中，最大存储 4GB |
BLOB | LOB 类型 | 该数据类型用于将大型二进制对象存储在数据库中，最大存储 4GB |
CLOB | LOB 类型 | 该数据类型用于将大型字符数据存储在数据库中，最大存储 4GB |
NCLOB | LOB 类型 | 该数据类型用于将大型 NCHAR 数据存储在数据库中，最大存储 4GB |
运算符
运算符类型 | 运算符 | 示例 | 说明 |
算术运算符 | +、- | a+b，a-b | 分别计算 a 和 b 的和与 a 和 b 的差 |
*、/ | a*b,a/b | 分别计算 a 和 b 的乘积与 a 和 b 的商 | |
关系运算符 | = | num1=num2 | 如果 num1 等于 num2，则返回 true |
<>,!= | num1<>num2 | 如果 num1 不等于 num2，则返回 true | |
< | num1<num2 | 如果 num1 小于 num2，则返回 true | |
> | num1>num2 | 如果 num1 大于 num2，则返回 true | |
<= | num1<=num2 | 如果 num1 小于等于 num2，则返回 true | |
>= | num1>=num2 | 如果 num1 大于等于 num2，则返回 true | |
逻辑运算符 | NOT | NOT True | 取反的逻辑值 |
AND | TRUE AND TRUE | 两个为真则结果为真 | |
OR | TRUE OR FALSE | 只要一个为真则结果为真 | |
其他 | /* */ | 多行注释符 | |
— | 单行注释符 | ||
|| | ‘abc’||’def’ | 连接运算符 | |
:= | a:=10 | 赋值运算符 |
流程控制语句：条件控制、循环控制、顺序控制
条件控制语句包括 IF 语句和 CASE 语句
IF 语句主要有 3 种形式：IF-THEN IF-THEN-ELSE IF-THEN-ELSIF
例 1：
DECLARE
DEPTNO EMP.DEPTNO%TYPE;
BEGIN
SELECT DEPTNO INTO DEPTNO FROM EMP WHERE EMPNO=’7902’;
IF DEPTNO=30 THEN
UPDATE EMP SET SAL=SAL*1.1
WHERE EMPNO=’7902’;
DBMS_OUTPUT.PUT_LINE(‘薪水上升 10%’);
ELSE ——–关键字 ELSE后面不能加 THEN条件语句
DBMS_OUTPUT.PUT_LINE(‘薪水保存不变’);
END IF;
END;
例 2：
DECLARE
deptno emp.deptno%type;
BEGIN
SELECT deptno INTO deptno FROM emp
WHERE empno=’7902′;
IF deptno=30 THEN
UPDATE emp SET sal=sal*1.1
WHERE empno=’7902′;
DBMS_OUTPUT.PUT_LINE(‘ 薪水上升 10%’);
ELSIF deptno=20 THEN —- 关键字 ELSIF 后面要跟 THEN 语句
UPDATE emp SET sal=sal*1.2
WHERE empno=’7902′;
DBMS_OUTPUT.PUT_LINE(‘ 薪水上升 20%’);
ELSE
UPDATE emp SET sal=sal*1.3
WHERE empno=’7902′;
DBMS_OUTPUT.PUT_LINE(‘ 薪水上升 30%’);
END IF;
END;
CASE 语句 用于根据单个变量或表达式与多个值进行比较
1、执行 CASE 语句前，先计算选择器的值
BEGIN
CASE &deptno —-& 表示接收一个输入的数据
WHEN 10 THEN DBMS_OUTPUT.PUT_LINE(‘ACCOUNTING’);
WHEN 20 THEN DBMS_OUTPUT.PUT_LINE(‘RESEARCH’);
WHEN 30 THEN DBMS_OUTPUT.PUT_LINE(‘SALES’);
WHEN 40 THEN DBMS_OUTPUT.PUT_LINE(‘OPERATIONS’);
ELSE DBMS_OUTPUT.PUT_LINE(‘ 此部门编号不存在 ’);
END CASE;
END;
2、CASE 另一种用法 – 没有选择器
DECLARE
deptno number;
BEGIN
deptno:=&deptno; —– 不加单引号时，默认为数字类型，加单引号才能输入字符’&string’
CASE
WHEN deptno=10 THEN DBMS_OUTPUT.PUT_LINE(‘ACCOUNTING’);
WHEN deptno=20 THEN DBMS_OUTPUT.PUT_LINE(‘RESEARCH’);
WHEN deptno=30 THEN DBMS_OUTPUT.PUT_LINE(‘SALES’);
WHEN deptno=40 THEN DBMS_OUTPUT.PUT_LINE(‘OPERATIONS’);
ELSE DBMS_OUTPUT.PUT_LINE(‘ 此部门编号不存在 ’);
END CASE;
END;
FOR 循环中循环次数是已知的。循环计数器变量需要事先声明，可将循环计数器作为常量引用。在 for 循环语句序列中不能给计数器变量赋值。
for counter in [reverse] value1..value2 loop seq_of_statements; end loop;
关键字 reverse 只有在需要从大到小执行循环时才会使用。
–FOR 循环
BEGIN
FOR COUNTS IN 1..10
LOOP
DBMS_OUTPUT.PUT_LINE(COUNTS);
END LOOP;
END;
while 循环：将条件与一系列语句结合在一起，根据条件执行语句，此系列语句包含在关键字 loop 和 end loop 之中。
–while
DECLARE
VOU NUMBER:=1;
BEGIN
WHILE VOU <= 10
LOOP
DBMS_OUTPUT.PUT_LINE(VOU);
VOU:=VOU+1;
END LOOP;
END;
异常：在运行程序时出现的错误叫做异常，异常有两种类型：
预定义异常：当 PL/SQL 程序违反了 Oracle 规则或超越系统限制时隐式引发异常
DECLARE
empno emp.empno%type;
empname emp.ename%type;
BEGIN
SELECT empno,empname INTO empno,empname FROM emp WHERE empno=’8888′;
DBMS_OUTPUT.PUT_LINE(‘ 员工编号：’|| empno);
DBMS_OUTPUT.PUT_LINE(‘ 员工姓名：’|| empname);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001,’ 此编号员工不存在！’);
END;
例 2 ：
DECLARE
VAR_NAME EMP.ENAME%TYPE;
BEGIN
SELECT ENAME INTO VAR_NAME FROM EMP WHERE EMPNO=’7902′;
DBMS_OUTPUT.PUT_LINE(VAR_NAME);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(‘ 查询返回不止一行 ’);
END;
用户定义异常：用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发
– 输入成绩 如果不在正常范围则 RAISE 异常
DECLARE
VAR_SCORE NUMBER;
OUT_OF_RANGE EXCEPTION;
BEGIN
VAR_SCORE :=&VAR_SCORE;
CASE
WHEN VAR_SCORE BETWEEN 90 AND 100 ——BETWEEN number1 AND number2 只能从小到大
THEN DBMS_OUTPUT.PUT_LINE(‘ 优秀 ’);
WHEN VAR_SCORE BETWEEN 80 AND 89
THEN DBMS_OUTPUT.PUT_LINE(‘ 良好 ’);
WHEN VAR_SCORE BETWEEN 70 AND 79
THEN DBMS_OUTPUT.PUT_LINE(‘ 一般 ’);
WHEN VAR_SCORE BETWEEN 60 AND 69
THEN DBMS_OUTPUT.PUT_LINE(‘ 及格 ’);
WHEN VAR_SCORE BETWEEN 0 AND 59
THEN DBMS_OUTPUT.PUT_LINE(‘ 不及格 ’);
ELSE RAISE OUT_OF_RANGE;
END CASE;
EXCEPTION WHEN OUT_OF_RANGE THEN
DBMS_OUTPUT.PUT_LINE(‘ 该成绩无效 ’);
END;
RAISE_APPLICATION_ERROR 过程：用于创建用户定义的错误信息，可以在可执行部分和异常处理部分使用
RAISE_APPLICATION_ERROR(ERROR_NUMBER,ERROR_MESSAGE)
ERROR_NUMBER: 是用户为异常指定的编号，该编号必须介于 -20000~-20999 之间
ERROR_MESSAGE: 是用户为异常指定的消息，消息的长度可达 2048 字节。
示例：
DECLARE
empno emp.empno%type;
empname emp.ename%type;
BEGIN
SELECT empno,empname INTO empno,empname FROM emp
WHERE empno=’8888′;
DBMS_OUTPUT.PUT_LINE(‘ 员工编号：’|| empno);
DBMS_OUTPUT.PUT_LINE(‘ 员工姓名：’|| empname);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001,’ 此编号员工不存在！’);
END;
静态 SQL
静态 SQL 是直接嵌到 PL/SQL 块中的 SQL 语句，用于完成特定或固定的任务。静态 SQL 的性能要优于动态 SQL，因此在编写 PL/SQL 块时，如果功能完全确定，应使用静态 SQL。
动态 SQL：
动态 SQL 是指在运行时动态形成的 SQL 语句。如果需要在 PL/SQL 中执行 DDL 语句（如 CREATE、ALTER、DROP）、DCL 语句（GRANT、REVOKE），或者在 PL/SQL 块中需要执行更加灵活的 SQL 语句（如在 SELECT 语句中使用不同的 WHERE 条件），那么就必须借助于动态 SQL。
动态 SQL 的执行
在大部分情况下，可以使用 EXECUTE IMMEDIATE 来执行动态 SQL 语句，语法如下：
EXECUTE IMMEDIATE dynamic_sql_string
[INTO variable_list]
[USING bind_argument_list];
示例：
DECLARE
sql_string VARCHAR2(200);
emp_rec emp%ROWTYPE;
BEGIN
sql_string:=‘SELECT * FROM emp WHERE empno=:id’;
EXECUTE IMMEDIATE sql_string INTO emp_rec USING &emp_id;
DBMS_OUTPUT.PUT_LINE(‘ 查询出的员工姓名是：’||emp_rec.ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘ 该编号的员工不存在!’);
END;
本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-08/146339.htm
