PL/SQL

  • 数据类型
  • 隐式类型声明
  • 操作符
  • 条件控制语句
  • 迭代控制语句
  • Cursors
  • Cursor的显式声明和处理
  • Cursor的隐式声明和处理
  • Cursor 属性
  • Exceptions 异常
  • References


接上篇：

数据类型

PL/SQL中有一些已被定义的数据类型：

NTEGER, DECIMAL, NUMBER, CHAR, DATE, VARCHAR, VARCHAR2, LONG,
BOOLEAN, ROWID, EXCEPTION
--例如我们声明一个变量average
average NUMBER(8,2); --变量average，它的数据类型是NUMBER，有效位数是8，精确到小数点后两位

括号里小数点前代表有效位数是，逗号后代表精确位数

隐式类型声明

DECLARE
student_no STUDENT.snum%TYPE;--student_no 这个变量的数据类型与 STUDENT表中的snum类型一致
student_name STUDENT.name%TYPE;--student_name 这个变量的数据类型与 STUDENT表中的name类型一致

student_row STUDENT%ROWTYPE;

BEGIN
student_no := 1234567;

SELECT name FROM STUDENT INTO student_name WHERE snum = student_no;

student_row.snum := 1234567;
student_row.name := 'James';
student_rec.dob := TO_DATE('01-DEC-1994', 'DD-MON-YYYY');
INSERT INTO STUDENT VALUES(student_row.snum, student_row.name, student_row.dob);

操作符

算术运算符
+, -, *, /, **
关系运算符
<, >, >=, <=, =, !=, <>, ~=
比较操作符
LIKE, BETWEEN, IN, IS NULL, =, !=, <>, ~=
布尔运算符
AND, OR, NOT
字符串运算
||
运算符的优先级
(**),(unary +,-),(*,/),(+,-,||),(comparison),(NOT),(AND),(OR)

条件控制语句

使用结构如下所示：

--第一种情况
IF condition THEN
statement;
...
ELSE
statement;
...
END IF;
--第二种情况
IF condition THEN
statement;
...
ELSIF condition THEN
statement;
...
ELSIF condition THEN
statement;
...
ELSE
statement;
...
END IF;

迭代控制语句

使用结构如下所示：

--第一种情况
LOOP
statement;
...
IF condition THEN EXIT;
statement;
...
END IF;
statement;
...
END LOOP;
--第二种情况
FOR variable IN scope
LOOP
statement;
...
END LOOP;
FOR variable IN REVERSE scope
LOOP
statement;
...
END LOOP;
--第三种情况
WHILE (condition)
LOOP
statement;
...
END LOOP;
--第四种情况
LOOP
statement;
...
EXIT WHEN condition;
statement;
...
END LOOP;

Cursors

当SELECT语句返回多于一行时会发生什么?

DECLARE
student_no STUDENT.snum%TYPE;
BEGIN
SELECT snum
INTO student_no
FROM STUDENT
WHERE name = 'Pam';

如果学生中名字叫Pam的不止一个，那么程序将SELECT出多行，而变量student_no不能用于存储从关系表中检索到的多行，因此如果运行上述代码，程序会报错。

解决这个问题的一种解决方案是以逐行的处理模式来处理多行。
Cursors这一种构造，它允许以逐行模式处理从关系表中检索到的行。

Cursor的显式声明和处理

DECLARE
student_no STUDENT.snum%TYPE;

CURSOR Q IS
SELECT snum
FROM STUDENT
WHERE name = 'Pam';

BEGIN
OPEN Q;
LOOP
FETCH Q INTO student_no;
IF Q %NOTFOUND THEN
EXIT;
END IF;
INSERT INTO PAM VALUES(student_no)
END LOOP;
CLOSE Q;
COMMIT;
END;

Cursor的隐式声明和处理

BEGIN
FOR Q_row IN (SELECT snum
FROM STUDENT
WHERE name = 'Pam')
LOOP
INSERT INTO PAM VALUES(Q_row.snum);
END LOOP;
COMMIT;
END;

在这个例子中，cursor是隐式声明的，隐式打开的。行是隐式获取的，结束表的条件是隐式检查的并且cursor是隐式关闭的。

Cursor 属性

Cursor属性决定游标的状态。

如果最后一次获取失败，因为没有更多行可用，则Cursor属性%NOTFOUND的计算结果为true。
如果最后一次提取成功，则找到的Cursor属性%FOUND的计算结果为true。
Cursor属性%ROWCOUNT的计算结果是到目前为止FETCH到的总行数。
如果Cursor被打开，则光标属性%ISOPEN的计算结果为true。

Exceptions 异常

异常是内部定义或用户定义的错误条件。
例如：
除以零、使用SELECT语句后使用 INTO子句其中没有行被选出、FETCH语句失败、使用尚未打开的cursor等。
SELECT语句不返回任何行时，将引发异常NO_DATA_FOUND。
SELECT语句返回多行时引发异常“TOO_MANY_ROWS。
当PL/SQL调用指定无效游标时，会引发异常“INVALID_CURSOR”，例如关闭未打开的游标。
当发生任何其他未显式命名的异常时，将引发异常“OTHERS”等等
举个例子：

DECLARE
student_name STUDENT.name%TYPE;
BEGIN
SELECT name
INTO student_name
FROM STUDENT
WHERE snum = 1234567;
...--省略
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO MESSAGES VALUES( 'Student not found')>
COMMIT;
END;

References

  1. T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapter 8 Advanced SQL, Pearson Education Ltd, 2015.