匿名PL/SQL详述

144次阅读

共计 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;