本人最近在学PL/SQL，闲来无事做了一套练习题，写了点笔记，供大家参考。
第一题，要求选出不合法的声明。A多余声明，C缺少冒号，D类型不匹配，只有B是正确的的。
第二题，要求给出变量类型。答案如下，不多解释。
a. CHAR / VARCHAR2
b. DATE
c. NUMBER
d. BOOLEAN
e. BOOLEAN
f. DATE
第三题，要求分别给出在1和2位置，每个变量的取值。这里要注意每个变量的作用域，看清楚一个匿名块里套着另一个匿名块，以及匿名块的范围，很容易得到答案。
我的测试代码如下：
DECLARE
v_custid NUMBER(4) := 1600;
v_custname VARCHAR2(300) := 'Women Sports Club';
v_new_custid NUMBER(3) := 500;
BEGIN
DECLARE
v_custid NUMBER(4) := 0;
v_custname VARCHAR2(300) := 'Shape up Sports Club';
v_new_custid NUMBER(3) := 300;
v_new_custname VARCHAR2(300) := 'Jansports Club';
BEGIN
v_custid := v_new_custid;
v_custname := v_custname || ' ' || v_new_custname;
DBMS_OUTPUT.put_line('(1) ' || v_custid || '——' || v_custname || '——' ||
v_new_custid || '——' || v_new_custname);
END;
v_custid := (v_custid * 12) / 10;
DBMS_OUTPUT.put_line('(2) ' || v_custid || '——' || v_custname || '——' ||
v_new_custid);
END;
屏幕上的输出如图：
因此，可以得出答案：
a. The value of V_CUSTID at position 1 is: 300
b. The value of V_CUSTNAME at position 1 is: Shape up Sports Club Jansports Club
c. The value of V_NEW_CUSTID at position 2 is: 500
d. The value of V_NEW_CUSTNAME at position 1 is: Jansports Club
e. The value of V_CUSTID at position 2 is: 1920
f. The value of V_CUSTNAME at position 2 is: Women Sports Club
第四题，要求写一个匿名块，根据输入的年份判断是否是闰年。
输入使用替代变量，IF...THEN的条件判断不多说，这里提一下那一长串的条件。
判断闰年的标准是：(1) 能被4整除且不被100整除；(2) 能被400整除。
条件(1)中是通过AND连接的，条件(2)是通过OR连接的。初步确定条件应该这么写： ((= 4 AND <> 100) OR (= 400))
由于AND的优先级是高于OR的，这里可以省去一层括号，即(= 4 AND <> 100 OR = 400)。
给出代码如下，测试通过。
DECLARE
v_year INTEGER(4);
BEGIN
v_year := '&请输入年份：';
IF MOD(v_year, 4) = 0 AND MOD(v_year, 100) <> 0 OR MOD(v_year, 400) = 0 THEN
DBMS_OUTPUT.put_line(v_year || ' is a leap year.');
ELSE
DBMS_OUTPUT.put_line(v_year || ' is not a leap year.');
END IF;
END;
第五题，建一张临时表，通过匿名块插入数据。
这里需要声明两个变量，一个VARCHAR2类型的message，一个Date类型的date_written，分别赋值。
【这张表里没有主键、外键这些约束，INSERT一般不会报错吧……简单抛个异常好了】
处理EXCEPTION中的OTHERS表示任何类型的异常，当需要处理多个异常时，OTHERS一定放在最后；SQLCODE和SQLERRM分别表示错误代码和错误信息，下面给出一个例子。
其中100是错误代码，'ORA-01403：未找到任何数据'是错误信息。
DECLARE
message VARCHAR2(35) := 'This is my first PL/SQL program';
date_written DATE := SYSDATE;
BEGIN
INSERT INTO Temp_22481(num_store, char_store, date_store)
VALUES(NULL, message, date_written);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ':' || SQLERRM);
END;
第六题，输入一个部门ID，输出该部门的人数。
这里声明了三个变量，分别存储部门ID，部门名称和员工人数。
通过两个SELECT语句查询结果，第一个从departments表（部门表）获取部门名称，第二个从employees表（雇员表）获取员工人数。
因为用到了SELECT INTO，可能会有查不到的情况，因此，这里需要捕获一个no_data_found的异常。
DECLARE
v_dept_no departments.department_id%TYPE := &v_dept_no;
v_dept_name departments.department_name%TYPE;
v_employees_num INTEGER;
BEGIN
SELECT department_name
INTO v_dept_name
FROM departments d
WHERE department_id = v_dept_no;
SELECT COUNT(1)
INTO v_employees_num
FROM employees e
WHERE department_id = v_dept_no;
DBMS_OUTPUT.put_line('部门 ' || v_dept_name || ' 的员工人数为 ' || v_employees_num );
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.put_line('There''s no such department...');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ':' || SQLERRM);
END;
※由于接下来涉及到employees表的修改，因此，这里拷贝employees表和departments表，后面在复制的表中进行操作。※
复制表的代码如下：
CREATE TABLE copy_emp AS
SELECT * FROM employees;
CREATE TABLE copy_dept AS
SELECT * FROM departments;
第七题，输入员工的姓（last_name），查询对应的薪水。如果薪水高于3000，输出'<员工名> earns <薪水>'；如果薪水低于3000，涨薪500并输出'<员工名> 's salary updated'。最后的表格是四组测试数据。
通过输入接收员工的姓，再通过SELECT获取对应的salary，用IF来做判断……
因为题里没说要COMMIT，我也就没COMMIT……
-- 计算工资
DECLARE
v_last_name copy_emp.last_name%TYPE;
v_salary copy_emp.salary%TYPE;
BEGIN
v_last_name := INITCAP('&请输入lastname：');
SELECT salary
INTO v_salary
FROM copy_emp e
WHERE e.last_name = INITCAP(v_last_name);
IF v_salary < 3000 THEN
UPDATE copy_emp e SET salary = salary + 500 WHERE e.last_name = v_last_name;
-- COMMIT;
DBMS_OUTPUT.put_line(v_last_name || ' ''s salary updated ' || (v_salary + 500));
ELSE
DBMS_OUTPUT.put_line(v_last_name || ' earns ' || v_salary);
END IF;
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.put_line('There''s no such employee...');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ':' || SQLERRM);
END;
最后附上一句查表的语句，用于验证结果。
-- 查表验证
SELECT * FROM copy_emp;
第八题，输入月薪，乘以12得到年薪，然后根据表1的对应关系，输出奖金。
没有SELECT INTO也没有UPDATE，不需要处理异常，甚至运算逻辑比第四题的闰年还简单……我一开始还以为这道题跟其他的题有联系，都做了一遍后，发现是我想多了……嗯，练习控制语句用的吧。
DECLARE
v_salary copy_emp.salary%TYPE;
v_annual_salary NUMBER;
v_bonus NUMBER;
BEGIN
v_salary := '&请输入月薪';
v_annual_salary := v_salary * 12;
IF v_annual_salary >= 20000 THEN
v_bonus := 2000;
ELSIF v_annual_salary >= 10000 AND v_annual_salary < 20000 THEN
v_bonus := 1000;
ELSE
v_bonus := 500;
END IF;
DBMS_OUTPUT.put_line('The bonus is $' || v_bonus);
END;
第九题，输入员工ID，新部门ID，以及薪水涨幅，更新employee表（雇员表）对应员工的部门和薪水，如果查不到，返回No Data Found。
这是第一个练习Exception异常的题（6 7都可以不写的）。故事背景是，假设一位员工因业绩优秀，不仅涨了薪还换到了新的部门，很快就可以出任CEO，迎娶白富美，走上人生巅峰……
嗯，扯远了。由于之前已经用过了Exception，这里不多介绍。通过输入获取员工ID、部门ID和涨幅三个变量，如果找不到对应的员工ID，抛出异常。
Exception中的RAISE_APPLICATION_ERROR，可以用我们自己定义的代码替换Oracle通用的报错信息，同时停止进程。在后面的触发器中，由于需要在UPDATE前拦截住不合法的修改，因此在Exception里写DBMS_OUTPUT是没用的，需要用RAISE_APPLICATION_ERROR停止进程。
RAISE_APPLICATION_ERROR有两个参数。第一个是错误代码，取值-20000~-20999，可以保证和Oracle已有的所有错误代码不冲突；第二个是报错信息，即一段自定义的文字。
DECLARE
v_employee_id copy_emp.employee_id%TYPE;
new_department_id copy_dept.department_id%TYPE;
increase NUMBER;
v_emp_temp copy_emp%ROWTYPE;
BEGIN
v_employee_id := '&请输入employee_id：';
new_department_id := '&请输入new_department_id：';
increase := '&请输入increase：';
SELECT *
INTO v_emp_temp
FROM copy_emp e
WHERE e.employee_id = v_employee_id;
UPDATE copy_emp e
SET e.department_id = new_department_id, salary = ROUND(salary * (1 + increase/100), 2)
WHERE e.employee_id = v_employee_id;
EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR(-20101, 'There''s no such employee!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ':' || SQLERRM);
END;
最后给出一个查表语句，用于验证。
-- 查表语句
SELECT * FROM copy_emp WHERE employee_id = 111;
第十题……终于开始游标了。定义一个游标，选出月薪高于15000且在1988年2月1日后入职的员工。
这里用了FOR循环的方式使用游标，省去了OPEN和CLOSE的过程，同时也不用专门声明一组变量用于保存列值了（这里就用 i 遍历游标的每一行）。
我写了两种方法。首先想到的是方法1，查询employee表（员工表）的所有行，然后在遍历游标过程中判断是否满足月薪高于15000，入职时间晚于1988年2月1日；后来改成了方法2，即在创建游标的过程中设置筛选条件。这样一方面节省了空间，同时减少了遍历次数，也节省了时间。
-- 方法 1
DECLARE
CURSOR emp_cursor1 IS
SELECT last_name, salary, hire_date
FROM copy_emp e;
BEGIN
FOR i IN emp_cursor1 LOOP
IF i.salary > 15000 AND i.hire_date > TO_DATE('1988-02-01', 'YYYY-MM-DD') THEN
DBMS_OUTPUT.put_line(i.last_name || ' earns ' || i.salary
|| ' and joined the organization on '
|| TO_CHAR(i.hire_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'));
END IF;
END LOOP;
END ;
-- 方法 2
DECLARE
CURSOR emp_cursor1 IS
SELECT last_name, salary, hire_date
FROM copy_emp e
WHERE e.salary > 15000 AND e.hire_date > TO_DATE('1988-02-01', 'YYYY-MM-DD');
BEGIN
FOR i IN emp_cursor1 LOOP
DBMS_OUTPUT.put_line(i.last_name || ' earns ' || i.salary
|| ' and joined the organization on '
|| TO_CHAR(i.hire_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'));
END LOOP;
END;