盐析白兔

这个屌丝很懒，什么也没留下！

热门标签

oracle PL-SQL块与深入例子_使用游标存储7788雇员的所有信息并定义一个记录型变量存储,分别输出各项信息

作者：盐析白兔 | 2024-05-17 18:18:57

踩

使用游标存储7788雇员的所有信息并定义一个记录型变量存储,分别输出各项信息

【示例1.1】查询雇员编号为7788的雇员姓名和工资。

步骤1：用SCOTT/TIGER账户登录SQL*Plus。

步骤2：在输入区输入以下程序：

SET SERVEROUTPUT ON

DECLARE--定义部分标识

v_name VARCHAR2(10); --定义字符串变量v_name

v_sal NUMBER(5); --定义数值变量v_sal

BEGIN --可执行部分标识

SELECT ename,sal

INTO v_name,v_sal

FROM emp

WHERE empno=7788;

--在程序中插入的SQL语句

DBMS_OUTPUT.PUT_LINE('7788号雇员是：'||v_name||'，工资为：'||to_char(v_sal));

--输出雇员名和工资

END; --结束标识

步骤3：按执行按钮或F5快捷键执行程序。

输出的结果是：

7788号雇员是：SCOTT，工资为：3000

PL/SQL 过程已成功完成。

======================================================================================================================

【示例2.1】变量的定义和初始化。

输入和运行以下程序：

SET SERVEROUTPUT ON

DECLARE --声明部分标识

v_job VARCHAR2(9);

v_count BINARY_INTEGER DEFAULT 0;

v_total_sal NUMBER(9,2) := 0;

v_date DATE := SYSDATE + 7;

c_tax_rate CONSTANT NUMBER(3,2) := 8.25;

v_valid BOOLEAN NOT NULL := TRUE;

BEGIN

v_job:='MANAGER';

--在程序中赋值

DBMS_OUTPUT.PUT_LINE(v_job);

--输出变量v_job的值

DBMS_OUTPUT.PUT_LINE(v_count);

--输出变量v_count的值

DBMS_OUTPUT.PUT_LINE(v_date);

--输出变量v_date的值

DBMS_OUTPUT.PUT_LINE(c_tax_rate);

--输出变量c_tax_rate的值

END;

执行结果：

MANAGER

18-4月 -03

8.25

PL/SQL 过程已成功完成。

说明：示例2共定义了6个变量，分别用“:=”赋值运算符或DEFAULT 关键字对变量进行了初始化或赋值。

其中：c_tax_rate为常量，在数据类型前加了 “CONSTANT”

关键字；v_valid变量在赋值运算符前面加了关键字“NOT NULL”，强制不能为空。

如果变量是布尔型，它的值只能是“TRUE”、“FALSE”或“NULL”。

本练习中的变量v_valid布尔变量的值只能取“TRUE”或“FALSE”。

=============================================================

【训练2.2】根据表的字段定义变量。

输入并执行以下程序：

SET SERVEROUTPUT ON

DECLARE

v_ename emp.ename%TYPE;--根据字段定义变量

BEGIN

SELECT ename

INTO v_ename

FROM emp

WHERE empno = 7788;

DBMS_OUTPUT.PUT_LINE(v_ename);

--输出变量的值

END;

执行结果：

SCOTT

PL/SQL 过程已成功完成。

说明：变量v_ename是根据表emp的ename字段定义的，两者的数据类型总是一致的。

=============================================================================

【训练2.3】定义并使用结合变量。

步骤1：输入和执行下列命令，定义结合变量g_ename：

VARIABLE g_ename VARCHAR2(100)

步骤2：输入和执行下列程序：

SET SERVEROUTPUT ON

BEGIN

:g_ename:=:g_ename|| 'Hello~ ';

--在程序中使用结合变量

DBMS_OUTPUT.PUT_LINE(:g_ename);

--输出结合变量的值

END;

输出结果：

Hello~

PL/SQL 过程已成功完成。

步骤3：重新执行程序。

输出结果：

Hello~ Hello~

PL/SQL 过程已成功完成。

步骤4：程序结束后用命令显示结合变量的内容：

PRINT g_ename

输出结果：

G_ENAME

-----------------------------------------------

Hello~ Hello~ ?

说明：g_ename为结合变量，可以在程序中引用或赋值，引用时在结合变量前面要加上“∶”。在程序结束后该变量的值仍然存在，其他程序可以继续引用。

=====================================================================================

【训练2.4】根据表定义记录变量。

输入并执行如下程序：

SET SERVEROUTPUT ON

DECLARE

emp_record emp%ROWTYPE;--定义记录变量

BEGIN

SELECT * INTO emp_record

FROM emp

WHERE mpno = 7788;--取出一条记录

DBMS_OUTPUT.PUT_LINE(emp_record.ename); --输出记录变量的某个字段

END;

执行结果为：

SCOTT

PL/SQL 过程已成功完成。?

说明：在以上的练习中定义了记录变量emp_record，它是根据表emp的全部字段定义的。SELECT语句将编号为7788的雇员的全部字段对应地存入该记录变量，最后输出记录变量的雇员名称字段emp_record.ename的内容。如果要获得其他字段的内容，比如要获得编号为7788的雇员的工资，可以通过变量emp_record.sal获得，依此类推。

=====================================================================================

varray(10)

=====================================================================================

【训练2.5】定义和使用TABLE变量：

SET SERVEROUTPUT ON

DECLARE

TYPE type_table IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; --类型说明

v_t type_table; --定义TABLE变量

BEGIN

v_t(1):='MONDAY';

v_t(2):='TUESDAY';

v_t(3):='WEDNESDAY';

v_t(4):='THURSDAY';

v_t(5):='FRIDAY';

DBMS_OUTPUT.PUT_LINE(v_t(3)); --输出变量的内容

END;

执行结果为：

WEDNESDAY

PL/SQL 过程已成功完成。

说明：本例定义了长度为10的字符型TABLE变量，通过赋值语句为前五个元素赋值，最后输出第三个元素。

==========================================================================================

【训练3.1】如果温度大于30℃，则显示“温度偏高”。

输入并执行以下程序：

SET SERVEROUTPUT ON

DECLARE

V_temprature NUMBER(5):=32;

V_result BOOLEAN:=false;

BEGIN

V_result:= v_temprature >30;

IF V_result THEN

DBMS_OUTPUT.PUT_LINE('温度'|| V_temprature ||'度,偏高');

END IF;

END;

执行结果为：

温度32度，偏高

PL/SQL过程已成功完成。

说明：该程序中使用了布尔变量，初值为false，表示温度低于30℃。表达式v_temprature >30返回值为布尔型，赋给逻辑变量V_result。如果变量v_temprature的值大于30，则返回值为真，否则为假。V_result值为真就会执行IF到 END IF之间的输出语句，否则没有输出结果。

试修改温度的初值为25℃，重新执行，观察结果。

==================================================================================

【训练3.2】根据性别，显示尊称。

输入并执行以下程序：

SET SERVEROUTPUT ON

DECLARE

v_sex VARCHAR2(2);

v_titil VARCHAR2(10);

BEGIN

v_sex:='男';

IF v_sex ='男' THEN

v_titil:='先生';

ELSE

v_titil:='女士';

END IF;

DBMS_OUTPUT.PUT_LINE(v_titil||'您好！');

END;

执行结果为：

先生您好！

PL/SQL 过程已成功完成。

说明：该程序根据性别显示尊称和问候，无论性别的值为何，总会有显示结果输出。如果V_sex的值不是‘男’和‘女’，那么输出结果会是什么？

【练习1】对以上程序进行补充修改，在ELSE部分嵌入一个IF结构，如果V_sex的值不是'女'，则显示“朋友你好”。

==================================================================================

【训练3.3】根据雇员工资分级显示税金。

输入并运行以下程序：

SET SERVEROUTPUT ON

DECLARE

v_sal NUMBER(5);

v_tax NUMBER(5,2);

BEGIN

SELECT sal INTO v_sal

FROM emp

WHERE empno=7788;

IF v_sal >=3000 THEN

V_tax:= v_sal*0.08;--税率8%

ELSIF v_sal>=1500 THEN

V_tax:= v_sal*0.06; --税率6%

ELSE

V_tax:= v_sal*0.04; --税率4%

END IF;

DBMS_OUTPUT.PUT_LINE('应缴税金:'||V_tax);

END;

执行结果为：

应缴税金:240

PL/SQL 过程已成功完成。

说明：该程序根据工资计算7788号雇员应缴税金，不同工资级别的税率不同。

==================================================

【训练4.1】使用CASE结构实现职务转换。

输入并执行程序：

SET SERVEROUTPUT ON

DECLARE

v_job VARCHAR2(10);

BEGIN

SELECT job INTO v_job

FROM emp

WHERE empno=7788;

CASE v_job

WHEN 'PRESIDENT' THEN

DBMS_OUTPUT.PUT_LINE('雇员职务:总裁');

WHEN 'MANAGER' THEN

DBMS_OUTPUT.PUT_LINE('雇员职务:经理');

WHEN 'SALESMAN' THEN

DBMS_OUTPUT.PUT_LINE('雇员职务:推销员');

WHEN 'ANALYST' THEN

DBMS_OUTPUT.PUT_LINE('雇员职务:系统分析员');

WHEN 'CLERK' THEN

DBMS_OUTPUT.PUT_LINE('雇员职务:职员');

ELSE

DBMS_OUTPUT.PUT_LINE('雇员职务:未知');

END CASE;

END;

执行结果：

雇员职务:系统分析员

PL/SQL 过程已成功完成。

说明：以上实例检索雇员7788的职务，通过CASE结构转换成中文输出。

【练习1】将雇员号修改成其他已知雇员号，重新执行。

=======================================================

【训练4.2】使用CASE的表达式结构。

DECLARE

v_grade VARCHAR2(10);

v_result VARCHAR2(10);

BEGIN

v_grade:='B';

v_result:=CASE v_grade

WHEN 'A' THEN '优'

WHEN 'B' THEN '良'

WHEN 'C' THEN '中'

WHEN 'D' THEN '差'

ELSE '未知'

END;

DBMS_OUTPUT.PUT_LINE('评价等级:'||V_result);

END;

执行结果为：

评价等级:良

PL/SQL 过程已成功完成。

说明：该CASE表达式通过判断变量v_grade的值，对变量V_result赋予不同的值。

============================================================

【训练4.3】使用CASE的搜索结构。

DECLARE

v_sal NUMBER(5);

BEGIN

SELECT sal INTO v_sal FROM emp

WHERE empno=7788;

CASE

WHEN v_sal>=3000 THEN

DBMS_OUTPUT.PUT_LINE('工资等级:高');

WHEN v_sal>=1500 THEN

DBMS_OUTPUT.PUT_LINE('工资等级:中');

ELSE

DBMS_OUTPUT.PUT_LINE('工资等级:低');

END CASE;

END;

执行结果为：

工资等级：高

PL/SQL 过程已成功完成。

说明：此结构类似于IF-THEN-ELSIF-ELSE-END IF结构。本训练判断7788雇员的工资等级。

============================================================

【训练5.1】　求：1２+3２+5２+...+15２的值。

输入并执行以下程序：

DECLARE

v_total NUMBER(5):=0;

v_count NUMBER(5):=1;

BEGIN

LOOP

v_total:=v_total+v_count**2;

EXIT WHEN v_count=15;--条件退出

v_count:=v_count+2;

END LOOP;

DBMS_OUTPUT.PUT_LINE(v_total);

END;

输出结果为：

680

PL/SQL 过程已成功完成。

说明：基本循环一定要使用EXIT退出，否则就会成为死循环。

【练习1】求1*2*3*4*...*10的值。

===============================================================

【训练5.2】用FOR循环输出图形。

BEGIN

FOR I IN 1..8

LOOP

DBMS_OUTPUT.PUT_LINE(to_char(i)||rpad('*',I,'*'));

END LOOP;

END;

输出结果为：

2**

3***

4****

5*****

6******

7*******

8********

PL/SQL 过程已成功完成。

说明：该程序在循环中使用了循环控制变量I，该变量隐含定义。在每次循环中根据循环控制变量I的值，使用RPAD函数控制显示相应个数的“*”。

【练习2】为以上程序增加REVERSE关键字，观察执行结果。

==============================================================

【训练5.2.2】输出一个空心三角形。

BEGIN

FOR I IN 1..9

LOOP

IF I=1 OR I=9 THEN

DBMS_OUTPUT.PUT_LINE(to_char(I)||rpad(' ',12-I,' ')||rpad('*',2*i-1,'*'));

ELSE

DBMS_OUTPUT.PUT_LINE(to_char(I)||rpad(' ',12-I,' ')||'*'||rpad(' ',I*2-3,' ')||'*');

END IF;

END LOOP;

END;

输出结果为：

1 *

2 * *

3 * *

4 * *

5 * *

6 * *

7 * *

8 * *

9 *****************

PL/SQL 过程已成功完成。

【练习3】修改程序，输出一个实心三角形。

说明：该实例采用循环和IF结构相结合，对第1行和第9行(I=1 OR I=9)执行同样的输出语句，其他行执行另外的输出语句。

=====================================================================

【训练5.3】　使用WHILE 循环向emp表连续插入5个记录。

步骤1：执行下面的程序：

DECLARE

v_count NUMBER(2) := 1;

BEGIN

WHILE v_count <6 LOOP

INSERT INTO emp(empno, ename)

VALUES (5000+v_count, '临时');

v_count := v_count + 1;

END LOOP;

COMMIT;

END;

输出结果为：

PL/SQL 过程已成功完成。

-----------------------------------------------------------------

步骤2：显示插入的记录：

SELECT empno,ename FROM emp WHERE ename='临时';

输出结果为：

EMPNO ENAME

------------------ ----------

5001 临时

5002 临时

5003 临时

5004 临时

5005 临时

已选择5行。

----------------------------------------------------------------

步骤3：删除插入的记录：

DELETE FROM emp WHERE ename='临时';

COMMIT;

输出结果为：

已删除5行。

提交完成。

说明：该练习使用WHILE循环向emp表插入5个新记录(雇员编号根据循环变量生成)，并通过查询语句显示新插入的记录，然后删除。

============================================================================

【训练5.4】　使用二重循环求1！+2！+...+10！的值。

步骤1：第1种算法：

DECLARE

v_total NUMBER(8):=0;

v_ni NUMBER(8):=0;

J NUMBER(5);

BEGIN

FOR I IN 1..10

LOOP

J:=1;

v_ni:=1;

WHILE J<=I

LOOP

v_ni:= v_ni*J;

J:=J+1;

END LOOP;--内循环求n!

v_total:=v_total+v_ni;

END LOOP;--外循环求总和

DBMS_OUTPUT.PUT_LINE(v_total);

END;

输出结果为：

4037913

PL/SQL 过程已成功完成。

-----------------------------------------------------------------

步骤2：第2种算法：

SET SERVEROUTPUT ON

DECLARE

v_total NUMBER(8):=0;

v_ni NUMBER(8):=1;

BEGIN

FOR I IN 1..10

LOOP

v_ni:= v_ni*I; --求n!

v_total:= v_total+v_ni;

END LOOP; --循环求总和

DBMS_OUTPUT.PUT_LINE(v_total);

END;

输出结果为：

409114

PL/SQL 过程已成功完成。

说明：第1种算法的程序内循环使用WHILE循环求阶层，外循环使用FOR循环求总和。

第2种算法是简化的算法，根据是：n!=n*(n?1)!。

=================================================================

阶段训练

=================================================================

【训练1】插入雇员，如果雇员已经存在，则输出提示信息。

SET SERVEROUTPUT ON

DECLARE

v_empno NUMBER(5):=7788;

v_num VARCHAR2(10);

i NUMBER(3):=0;

BEGIN

SELECT count(*) INTO v_num FROM SCOTT.emp WHERE empno=v_empno;

=================================================================

IF v_num=1 THEN

DBMS_OUTPUT.PUT_LINE('雇员'||v_empno||'已经存在！');

ELSE

INSERT INTO emp(empno,ename) VALUES(v_empno,'TOM');

COMMIT;

DBMS_OUTPUT.PUT_LINE('成功插入新雇员！');

END IF;

END;

=================================================================

说明：在本程序中，使用了一个技巧来判断一个雇员是否存在。

如果一个雇员不存在，那么使用SELECT...INTO来获取雇员信息就会失败，

因为SELECT...INTO形式要求查询必须返回一行。但如果使用COUNT统计查询，

返回满足条件的雇员人数，则该查询总是返回一行，所以任何情况都不会失败。

COUNT返回的统计人数为0说明雇员不存在，返回的统计人数为1说明雇员存在，

返回的统计人数大于1说明有多个满足条件的雇员存在。

本例在雇员不存在时进行插入操作，如果雇员已经存在则不进行插入。

=================================================================

【训练2】输出由符号“*”构成的正弦曲线的一个周期(0～360°)。

SET SERVEROUTPUT ON SIZE 10000

SET LINESIZE 100

SET PAGESIZE 100

DECLARE

v_a NUMBER(8,3);

v_p NUMBER(8,3);

BEGIN

FOR I IN 1..18

LOOP

v_a:=I*20*3.14159/180;--生成角度，并转换为弧度

v_p:=SIN(v_a)*20+25;--求SIN函数值，20为放大倍数，25为水平位移

DBMS_OUTPUT.PUT_LINE(to_char(i)||lpad('*',v_p,' '));--输出记录变量的某个字段

END LOOP;

END;

输出结果如下：

1 *

2 *

3 *

4 *

5 *

6 *

7 *

8 *

9 *

10 *

11 *

12 *

13 *

14 *

15 *

16 *

17 *

18 *

PL/SQL 过程已成功完成。

说明：在本程序中使用到了固定次数的循环以及SIN和LPAD函数，通过正确地设置步长、幅度和位移的参数，在屏幕上可正确地显示图形

=================================================================

6.4 练习

=================================================================

1. 用来存放可变长度字符串的函数是：

A. CHAR B. VARCHAR2

C. NUMBER D. BOOLEAN

2. 在程序中必须书写的语句是：

A. SET SERVEROUTPUT ON

B. DECLARE

C. BEGIN D. EXCEPTION

=================================================================

3. 在程序中正确的变量定义语句是：

A. emp_record emp.ename%ROWTYPE

B. emp_record emp%ROWTYPE

C. v_ename emp%TYPE

D. v_ename ename%TYPE

4. 在程序中最有可能发生错误的语句是：

A. INSERT INTO emp(empno,ename) VALUES(8888,'Jone')

B. UPDATE emp SET sal=sal+100

C. DELETE FROM emp

D. SELECT * FROM emp

=================================================================

5. 关于以下分支结构，如果i的初值是15，环循结束后j的值是：

IF i>20 THEN

j:= i*2;

ELSIF i>15 THEN

j:= i*3;

ELSE

j:= i*4;

END IF;

A. 15 B. 30 C. 45 D. 60

=================================================================

6. 关于以下循环，如果I的初值是3，则循环的次数是：

WHILE I<6 LOOP

I:= I + 1;

END LOOP;

A. 3 B. 4 C. 5 D. 6

7. 以下表达式的结果非空的是：

A. NULL||NULL B. 'NULL'||NULL

C. 3+NULL D. (5>NULL)

===============================================================

【训练6.1】　使用隐式游标的属性，判断对雇员工资的修改是否成功。

步骤1：输入和运行以下程序：

BEGIN

UPDATE emp SET sal=sal+100 WHERE empno=1234;

IF SQL%FOUND THEN

DBMS_OUTPUT.PUT_LINE('成功修改雇员工资！');

ROLLBACK;

ELSE

DBMS_OUTPUT.PUT_LINE('修改雇员工资失败！');

END IF;

END;

运行结果为：

修改雇员工资失败！

PL/SQL 过程已成功完成。

步骤2：将雇员编号1234改为7788，重新执行以上程序：

运行结果为：

成功修改雇员工资！

PL/SQL 过程已成功完成。

说明：本例中，通过SQL%FOUND属性判断修改是否成功，并给出相应信息。

=================================================================

【训练7.1】用游标提取emp表中7788雇员的名称和职务。

DECLARE

v_ename VARCHAR2(10);

v_job VARCHAR2(10);

CURSOR emp_cursor IS

SELECT ename,job FROM emp WHERE empno=7788;

BEGIN

OPEN emp_cursor;

FETCH emp_cursor INTO v_ename,v_job;

DBMS_OUTPUT.PUT_LINE(v_ename||','||v_job);

CLOSE emp_cursor;

END;

执行结果为:

SCOTT,ANALYST

PL/SQL 过程已成功完成。?

说明：该程序通过定义游标emp_cursor，提取并显示雇员7788的名称和职务。

作为对以上例子的改进，在以下训练中采用了记录变量。

================================================================

【训练7.2】用游标提取emp表中7788雇员的姓名、职务和工资。

DECLARE

CURSOR emp_cursor IS SELECT ename,job,sal FROM emp WHERE empno=7788;

emp_record emp_cursor%ROWTYPE;

BEGIN

OPEN emp_cursor;

FETCH emp_cursor INTO emp_record;

DBMS_OUTPUT.PUT_LINE(emp_record.ename||','|| emp_record.job||','|| emp_record.sal);

CLOSE emp_cursor;

END;

执行结果为:

SCOTT,ANALYST,3000

PL/SQL 过程已成功完成。?

说明：实例中使用记录变量来接收数据，记录变量由游标变量定义，需要出现在游标定义之后。

注意：可通过以下形式获得记录变量的内容：

记录变量名.字段名。

==================================================================

【训练7.3】显示工资最高的前3名雇员的名称和工资。

DECLARE

V_ename VARCHAR2(10);

V_sal NUMBER(5);

CURSOR emp_cursor IS SELECT ename,sal FROM emp ORDER BY sal DESC;

BEGIN

OPEN emp_cursor;

FOR I IN 1..3 LOOP

FETCH emp_cursor INTO v_ename,v_sal;

DBMS_OUTPUT.PUT_LINE(v_ename||','||v_sal);

END LOOP;

CLOSE emp_cursor;

END;

执行结果为:

KING,5000

SCOTT,3000

FORD,3000

PL/SQL 过程已成功完成。

说明：该程序在游标定义中使用了ORDER BY子句进行排序，并使用循环语句来提取多行数据。

============================================================================

【训练8.1】使用特殊的FOR循环形式显示全部雇员的编号和名称。

DECLARE

CURSOR emp_cursor IS

SELECT empno, ename FROM emp;

BEGIN

FOR Emp_record IN emp_cursor LOOP

DBMS_OUTPUT.PUT_LINE(Emp_record.empno|| Emp_record.ename);

END LOOP;

END;

执行结果为：

7369SMITH

7499ALLEN

7521WARD

7566JONES

PL/SQL 过程已成功完成。

? 说明：可以看到该循环形式非常简单，隐含了记录变量的定义、游标的打开、提取和关闭过程。Emp_record为隐含定义的记录变量，循环的执行次数与游标取得的数据的行数相一致。

==============================================================================

【训练8.2】另一种形式的游标循环。

BEGIN

FOR re IN (SELECT ename FROM EMP) LOOP

DBMS_OUTPUT.PUT_LINE(re.ename);

END LOOP;

END;

执行结果为：

SMITH

ALLEN

WARD

JONES

说明：该种形式更为简单，省略了游标的定义，游标的SELECT查询语句在循环中直接出现。

==================================================================================

【训练9.1】使用游标的属性练习。

DECLARE

V_ename VARCHAR2(10);

CURSOR emp_cursor IS

SELECT ename FROM emp;

BEGIN

OPEN emp_cursor;

IF emp_cursor%ISOPEN THEN

LOOP

FETCH emp_cursor INTO v_ename;

EXIT WHEN emp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(to_char(emp_cursor%ROWCOUNT)||'-'||v_ename);

END LOOP;

ELSE

DBMS_OUTPUT.PUT_LINE('用户信息：游标没有打开！');

END IF;

CLOSE emp_cursor;

END;

执行结果为：

1-SMITH

2-ALLEN

3-WARD

PL/SQL 过程已成功完成。

? 说明：本例使用emp_cursor%ISOPEN判断游标是否打开；使用emp_cursor%ROWCOUNT获得到目前为止FETCH语句返回的数据行数并输出；使用循环来获取数据，在循环体中使用FETCH语句；使用emp_cursor%NOTFOUND判断FETCH语句是否成功执行，当FETCH语句失败时说明数据已经取完，退出循环。

【练习1】去掉OPEN emp_cursor;语句，重新执行以上程序。

=========================================================

【训练10.1】带参数的游标。

DECLARE

V_empno NUMBER(5);

V_ename VARCHAR2(10);

CURSOR emp_cursor(p_deptno NUMBER, p_job VARCHAR2) IS

SELECT empno, ename FROM emp

WHERE deptno = p_deptno AND job = p_job;

BEGIN

OPEN emp_cursor(10, 'CLERK');

LOOP

FETCH emp_cursor INTO v_empno,v_ename;

EXIT WHEN emp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename);

END LOOP;

END;

执行结果为：

7934,MILLER

PL/SQL 过程已成功完成。

说明：游标emp_cursor定义了两个参数：p_deptno代表部门编号，p_job代表职务。语句OPEN emp_cursor(10, 'CLERK')传递了两个参数值给游标，即部门为10、职务为CLERK，所以游标查询的内容是部门10的职务为CLERK的雇员。循环部分用于显示查询的内容。

【练习1】修改Open语句的参数：部门号为20、职务为ANALYST，并重新执行。

================================================================

也可以通过变量向游标传递参数，但变量需要先于游标定义，并在游标打开之前赋值。对以上例子重新改动如下：

【训练10.2】通过变量传递参数给游标。

SET SERVEROUTPUT ON

DECLARE

v_empno NUMBER(5);

v_ename VARCHAR2(10);

v_deptno NUMBER(5);

v_job VARCHAR2(10);

CURSOR emp_cursor IS

SELECT empno, ename FROM emp

WHERE deptno = v_deptno AND job = v_job;

BEGIN

v_deptno:=10;

v_job:='CLERK';

OPEN emp_cursor;

LOOP

FETCH emp_cursor INTO v_empno,v_ename;

EXIT WHEN emp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename);

END LOOP;

END;

执行结果为：

7934,MILLER

PL/SQL 过程已成功完成。

说明：该程序与前一程序实现相同的功能。

===============================================================================

【训练11.1】动态SELECT查询。

DECLARE

str varchar2(100);

v_ename varchar2(10);

begin

str:='select ename from scott.emp where empno=7788';

execute immediate str into v_ename;

dbms_output.put_line(v_ename);

END;

执行结果为：

SCOTT

PL/SQL 过程已成功完成。

说明：SELECT...INTO...语句存放在STR字符串中，通过EXECUTE语句执行。

===============================================================================

【训练11.2】按名字中包含的字母顺序分组显示雇员信息。

输入并运行以下程序：

declare

type cur_type is ref cursor;

cur cur_type;

rec scott.emp%rowtype;

str varchar2(50);

letter char:= 'A';

begin

loop

str:= 'select ename from emp where ename like ''%'||letter||'%''';

open cur for str;

dbms_output.put_line('包含字母'||letter||'的名字：');

loop

fetch cur into rec.ename;

exit when cur%notfound;

dbms_output.put_line(rec.ename);

end loop;

exit when letter='Z';

letter:=chr(ascii(letter)+1);

end loop;

end;

运行结果为：

包含字母A的名字：

ALLEN

WARD

MARTIN

BLAKE

CLARK

ADAMS

JAMES

包含字母B的名字：

BLAKE

包含字母C的名字：

CLARK

SCOTT

说明：使用了二重循环，在外循环体中，动态生成游标的SELECT语句，然后打开。

通过语句letter:=chr(ascii(letter)+1)可获得字母表中的下一个字母。

==========================================================================

【训练12.1】查询编号为1234的雇员名字。

DECLARE

v_name VARCHAR2(10);

BEGIN

SELECT ename

INTO v_name

FROM emp

WHERE empno = 1234;

DBMS_OUTPUT.PUT_LINE('该雇员名字为：'|| v_name);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('编号错误，没有找到相应雇员！');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('发生其他错误！');

END;

执行结果为：

编号错误，没有找到相应雇员！

PL/SQL 过程已成功完成。

说明：在以上查询中，因为编号为1234的雇员不存在，所以将发生类型为“NO_DATA_FOUND”的异常。

“NO_DATA_FOUND”是系统预定义的错误类型，

EXCEPTION部分下的WHEN语句将捕捉到该异常，

并执行相应代码部分。在本例中，输出用户自定义的错误信息“编号错误，

没有找到相应雇员!”。如果发生其他类型的错误，

将执行OTHERS条件下的代码部分，显示“发生其他错误!”

==========================================================================

【训练12.2】由程序代码显示系统错误。

DECLARE

v_temp NUMBER(5):=1;

BEGIN

v_temp:=v_temp/0;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('发生系统错误！');

DBMS_OUTPUT.PUT_LINE('错误代码：'|| SQLCODE( ));

DBMS_OUTPUT.PUT_LINE('错误信息：' ||SQLERRM( ));

END;

执行结果为：

发生系统错误！

错误代码：?1476

错误信息：ORA-01476: 除数为 0

PL/SQL 过程已成功完成。

说明：程序运行中发生除零错误，由WHEN OTHERS捕捉到，执行用户自己的输出语句显示错误信息，然后正常结束。

在错误处理部分使用了预定义函数SQLCODE( )和SQLERRM( )来进一步获得错误的代码和种类信息。

==========================================================================

【训练13.1】定义新的系统错误类型。

DECLARE

V_ENAME VARCHAR2(10);

NULL_INSERT_ERROR EXCEPTION;

PRAGMA EXCEPTION_INIT(NULL_INSERT_ERROR,-1400);

BEGIN

INSERT INTO EMP(EMPNO) VALUES(NULL);

EXCEPTION

WHEN NULL_INSERT_ERROR THEN

DBMS_OUTPUT.PUT_LINE('无法插入NULL值！');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('发生其他系统错误！');

END;

执行结果为：

无法插入NULL值！

PL/SQL 过程已成功完成。

说明：NULL_INSERT_ERROR是自定义异常，同系统错误1400相关联。

======================================================================

【训练14.1】插入新雇员，限定插入雇员的编号在7000～8000之间。

SET SERVEROUTPUT ON

执行结果为：

雇员编号小于7000的下限！

PL/SQL 过程已成功完成

说明：在此例中，自定义了两个异常：new_excp1和new_excp2，

分别代表编号小于7000和编号大于8000的错误。

在程序中通过判断编号大小，产生对应的异常，

并在异常处理部分回退插入操作，然后显示相应的错误信息。

===============================================================

【训练14.2】使用RAISE_APPLICATION_ERROR函数引发系统异常。

DECLARE

new_no NUMBER(10);

new_excp1 EXCEPTION;

new_excp2 EXCEPTION;

BEGIN

new_no:=6789;

INSERT INTO emp(empno,ename)

VALUES(new_no, '小郑');

IF new_no<7000 THEN

RAISE new_excp1;

END IF;

IF new_no>8000 THEN

RAISE new_excp2;

END IF;

COMMIT;

EXCEPTION

WHEN new_excp1 THEN

ROLLBACK;

DBMS_OUTPUT.PUT_LINE('雇员编号小于7000的下限！');

WHEN new_excp2 THEN

ROLLBACK;

DBMS_OUTPUT.PUT_LINE('雇员编号超过8000的上限！');

END;

--------------------------------------------

执行结果为：

DECLARE

ERROR 位于第 1 行:

ORA-20001: 编号小于7000的下限！

ORA-06512: 在line 9

说明：在本训练中，使用RAISE_APPLICATION_ERROR引发自定义异常，并以系统错误的方式进行显示。错误编号为20001和20002。

注意：同上一个训练比较，此种方法不需要事先定义异常，可直接引发。

=========================================================================

【练习1】修改雇员的工资，通过引发异常控制修改范围在600～6000之间。

=========================================================================

阶段训练

=========================================================================

可以参考下面的程序片断将出错信息记录到表中，其中，errors为记录错误信息的表，SQLCODE为发生异常的错误编号，SQLERRM为发生异常的错误信息。

DECLARE

v_error_code NUMBER;

v_error_message VARCHAR2(255);

BEGIN

...

EXCEPTION

...

WHEN OTHERS THEN

v_error_code := SQLCODE ;

v_error_message := SQLERRM ;

INSERT INTO errors

VALUES(v_error_code, v_error_message);

END;

【训练1】将雇员从一个表复制到另一个表。

步骤1：创建一个结构同EMP表一样的新表EMP1：

CREATE TABLE emp1 AS SELECT * FROM SCOTT.EMP WHERE 1=2;

步骤2：通过指定雇员编号，将雇员由EMP表移动到EMP1表：

SET SERVEROUTPUT ON

DECLARE

v_empno NUMBER(5):=7788;

emp_rec emp%ROWTYPE;

BEGIN

SELECT * INTO emp_rec FROM emp WHERE empno=v_empno;

DELETE FROM emp WHERE empno=v_empno;

INSERT INTO emp1 VALUES emp_rec;

IF SQL%FOUND THEN

COMMIT;

DBMS_OUTPUT.PUT_LINE('雇员复制成功！');

ELSE

ROLLBACK;

DBMS_OUTPUT.PUT_LINE('雇员复制失败！');

END IF;

END;

执行结果为：

雇员复制成功！

PL/SQL 过程已成功完成。

步骤2：显示复制结果：

SELECT empno,ename,job FROM emp1;

执行结果为：

EMPNO ENAME JOB

-------------- -------------- ----------------

7788 SCOTT ANALYST

说明：emp_rec变量是根据emp表定义的记录变量，

SELECT...INTO...语句将整个记录传给该变量。

INSERT语句将整个记录变量插入emp1表，如果插入成功(SQL%FOUND为真)，则提交事务，否则回滚撤销事务。

试修改雇员编号为7902，重新执行以上程序。

==============================================================

【训练2】输出雇员工资，雇员工资用不同高度的*表示。

输入并执行以下程序：

SET SERVEROUTPUT ON

BEGIN

FOR re IN (SELECT ename,sal FROM EMP) LOOP

DBMS_OUTPUT.PUT_LINE(rpad(re.ename,12,' ')||rpad('*',re.sal/100,'*'));

END LOOP;

END;

输出结果为：

SMITH ********

ALLEN ****************

WARD *************

JONES ******************************

MARTIN *************

BLAKE *****************************

CLARK *****************************

SCOTT ******************************

KING **************************************************

TURNER ***************

ADAMS ***********

JAMES **********

FORD ******************************

MILLER *************

执行结果为：

PL/SQL 过程已成功完成。

? 说明：第一个rpad函数产生对齐效果，第二个rpad函数根据工资额产生不同数目的*。该程序采用了隐式的简略游标循环形式。

==========================================================================

【训练3】编写程序，格式化输出部门信息。

输入并执行如下程序：

SET SERVEROUTPUT ON

DECLARE

v_count number:=0;

CURSOR dept_cursor IS SELECT * FROM dept;

BEGIN

DBMS_OUTPUT.PUT_LINE('部门列表');

DBMS_OUTPUT.PUT_LINE('---------------------------------');

FOR Dept_record IN dept_cursor LOOP

DBMS_OUTPUT.PUT_LINE('部门编号：'|| Dept_record.deptno);

DBMS_OUTPUT.PUT_LINE('部门名称：'|| Dept_record.dname);

DBMS_OUTPUT.PUT_LINE('所在城市：'|| Dept_record.loc);

DBMS_OUTPUT.PUT_LINE('---------------------------------');

v_count:= v_count+1;

END LOOP;

DBMS_OUTPUT.PUT_LINE('共有'||to_char(v_count)||'个部门！');

END;

输出结果为：

部门列表

------------------------------------

部门编号：10

部门名称：ACCOUNTING

所在城市：NEW YORK

------------------------------------

部门编号：20

部门名称：RESEARCH

所在城市：DALLAS

...

共有4个部门！

PL/SQL 过程已成功完成。

说明：该程序中将字段内容垂直排列。V_count变量记录循环次数，即部门个数

==========================================================================

【训练4】已知每个部门有一个经理，编写程序，统计输出部门名称、部门总人数、总工资和部门经理。

输入并执行如下程序：

SET SERVEROUTPUT ON

DECLARE

v_deptno number(8);

v_count number(3);

v_sumsal number(6);

v_dname varchar2(15);

v_manager varchar2(15);

CURSOR list_cursor IS

SELECT deptno,count(*),sum(sal) FROM emp group by deptno;

BEGIN

OPEN list_cursor;

DBMS_OUTPUT.PUT_LINE('----------- 部门统计表 -----------');

DBMS_OUTPUT.PUT_LINE('部门名称总人数总工资部门经理');

FETCH list_cursor INTO v_deptno,v_count,v_sumsal;

WHILE list_cursor%found LOOP

SELECT dname INTO v_dname FROM dept

WHERE deptno=v_deptno;

SELECT ename INTO v_manager FROM emp

WHERE deptno=v_deptno and job='MANAGER';

DBMS_OUTPUT.PUT_LINE(rpad(v_dname,13)||rpad(to_char(v_count),8)

||rpad(to_char(v_sumsal),9)||v_manager);

FETCH list_cursor INTO v_deptno,v_count,v_sumsal;

END LOOP;

DBMS_OUTPUT.PUT_LINE('--------------------------------------');

CLOSE list_cursor;

END;

输出结果为：

-------------------- 部门统计表 -----------------

部门名称总人数总工资部门经理

ACCOUNTING 3 8750 CLARK

RESEARCH 5 10875 JONES

SALES 6 9400 BLAKE

-------------------------------------------------------------

PL/SQL 过程已成功完成。?

说明：游标中使用到了起分组功能的SELECT语句，统计出各部门的总人数和总工资。

再根据部门编号和职务找到部门的经理。该程序假定每个部门有一个经理。

==========================================================================

【训练5】为雇员增加工资，从工资低的雇员开始，为每个人增加原工资的10%，限定所增加的工资总额为800元，显示增加工资的人数和余额。

输入并调试以下程序：

SET SERVEROUTPUT ON

DECLARE

V_NAME CHAR(10);

V_EMPNO NUMBER(5);

V_SAL NUMBER(8);

V_SAL1 NUMBER(8);

V_TOTAL NUMBER(8) := 800; --增加工资的总额

V_NUM NUMBER(5):=0; --增加工资的人数

CURSOR emp_cursor IS

SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL ASC;

BEGIN

OPEN emp_cursor;

DBMS_OUTPUT.PUT_LINE('姓名原工资新工资');

DBMS_OUTPUT.PUT_LINE('---------------------------');

LOOP

FETCH emp_cursor INTO V_EMPNO,V_NAME,V_SAL;

EXIT WHEN emp_cursor%NOTFOUND;

V_SAL1:= V_SAL*0.1;

IF V_TOTAL>V_SAL1 THEN

V_TOTAL := V_TOTAL - V_SAL1;

V_NUM:=V_NUM+1;

DBMS_OUTPUT.PUT_LINE(V_NAME||TO_CHAR(V_SAL,'99999')||

TO_CHAR(V_SAL+V_SAL1,'99999'));

UPDATE EMP SET SAL=SAL+V_SAL1

WHERE EMPNO=V_EMPNO;

ELSE

DBMS_OUTPUT.PUT_LINE(V_NAME||TO_CHAR(V_SAL,'99999')||TO_CHAR(V_SAL,'99999'));

END IF;

END LOOP;

DBMS_OUTPUT.PUT_LINE('---------------------------');

DBMS_OUTPUT.PUT_LINE('增加工资人数:'||V_NUM||' 剩余工资：'||V_TOTAL);

CLOSE emp_cursor;

COMMIT;

END;

输出结果为：

姓名原工资新工资

---------------------------------------------

SMITH 1289 1418

JAMES 1531 1684

MARTIN 1664 1830

MILLER 1730 1903

ALLEN 1760 1936

ADAMS 1771 1771

TURNER 1815 1815

WARD 1830 1830

BLAKE 2850 2850

CLARK 2850 2850

JONES 2975 2975

FORD 3000 3000

KING 5000 5000

-----------------------------------------------

增加工资人数：5 剩余工资：3

PL/SQL 过程已成功完成。

【练习1】按部门编号从小到大的顺序输出雇员名字、工资以及工资与平均工资的差。

【练习2】为所有雇员增加工资，工资在1000以内的增加30%，工资在1000～2000之间的增加20%，2000以上的增加10%。

==========================================================================

练习

==========================================================================

1. 关于显式游标的错误说法是：

A. 使用显式游标必须先定义

B. 游标是一个内存区域

C. 游标对应一个SELECT 语句

D. FETCH 语句用来从数据库中读出一行数据到游标

2. 有4条与游标有关的语句，它们在程序中出现的正确顺序是：

1) OPEN abc

2) CURSOR abc IS SELECT ename FROM emp

3) FETCH abc INTO vname

4) CLOSE abc

A. 1、2、3、4 B. 2、1、3、4

C. 2、3、1、4 D. 1、3、2、4

3. 用来判断FETCH语句是否成功，并且在FETCH语句失败时返回逻辑真的属性是：

A. %ROWCOUNT B. %NOTFOUND

C. %FOUND D. %ISOPEN

4. 在程序中执行语句SELECT ename FROM emp WHERE job='CLERK' 可能引发的异常类型是:

A. NO_DATA_FOUND

B. TOO_MANY_ROWS

C. INVALID_CURSOR

D. OTHERS

5. 有关游标的论述，正确的是：

A. 隐式游标属性%FOUND代表操作成功

B. 显式游标的名称为SQL

C. 隐式游标也能返回多行查询结果

D. 可以为UPDATE语句定义一个显式游标

==========================================================================

存储过程

==========================================================================

【训练15.1】创建一个显示雇员总人数的存储过程。

步骤1：登录SCOTT账户(或学生个人账户)。

步骤2：在SQL*Plus输入区中，输入以下存储过程：

CREATE OR REPLACE PROCEDURE EMP_COUNT

V_TOTAL NUMBER(10);

BEGIN

SELECT COUNT(*) INTO V_TOTAL FROM EMP;

DBMS_OUTPUT.PUT_LINE('雇员总人数为：'||V_TOTAL);

END;

步骤3：按“执行”按钮进行编译。

如果存在错误，就会显示:

警告: 创建的过程带有编译错误。

如果存在错误，对脚本进行修改，直到没有错误产生。

如果编译结果正确，将显示：

过程已创建。

步骤4：调用存储过程，在输入区中输入以下语句并执行：

显示结果为：

雇员总人数为：14

PL/SQL 过程已成功完成。

说明：在该训练中，V_TOTAL变量是存储过程定义的局部变量，用于接收查询到的雇员总人数。

注意：在SQL*Plus中输入存储过程，按“执行”按钮是进行编译，不是执行存储过程。

==========================================================================

【训练15.2】在PL/SQL程序中调用存储过程。

步骤1：登录SCOTT账户。

步骤2：授权STUDENT账户使用该存储过程，即在SQL*Plus输入区中，输入以下的命令：

GRANT EXECUTE ON EMP_COUNT TO STUDENT

授权成功。

步骤3：登录STUDENT账户，在SQL*Plus输入区中输入以下程序：

SET SERVEROUTPUT ON

BEGIN

SCOTT.EMP_COUNT;

END;

步骤4：执行以上程序，结果为：

雇员总人数为：14

PL/SQL 过程已成功完成。?

说明：在本例中，存储过程是由SCOTT账户创建的，STUDEN账户获得SCOTT账户的授权后，才能调用该存储过程。

注意：在程序中调用存储过程，使用了第二种语法。

==========================================================================

【训练15.3】编写显示雇员信息的存储过程EMP_LIST，并引用EMP_COUNT存储过程。

步骤1：在SQL*Plus输入区中输入并编译以下存储过程：

CREATE OR REPLACE PROCEDURE EMP_LIST

CURSOR emp_cursor IS

SELECT empno,ename FROM emp;

BEGIN

FOR Emp_record IN emp_cursor LOOP

DBMS_OUTPUT.PUT_LINE(Emp_record.empno||Emp_record.ename);

END LOOP;

EMP_COUNT;

END;

执行结果：

过程已创建。

步骤2：调用存储过程，在输入区中输入以下语句并执行：

EXECUTE EMP_LIST

显示结果为：

7369SMITH

7499ALLEN

7521WARD

7566JONES

执行结果：

雇员总人数为：14

PL/SQL 过程已成功完成。

说明：以上的EMP_LIST存储过程中定义并使用了游标，用来循环显示所有雇员的信息。然后调用已经成功编译的存储过程EMP_COUNT，用来附加显示雇员总人数。通过EXECUTE命令来执行EMP_LIST存储过程。

【练习1】编写显示部门信息的存储过程DEPT_LIST，要求统计出部门个数。

==========================================================================

【训练16.1】编写给雇员增加工资的存储过程CHANGE_SALARY，通过IN类型的参数传递要增加工资的雇员编号和增加的工资额。

步骤1：登录SCOTT账户。

步骤2：在SQL*Plus输入区中输入以下存储过程并执行：

CREATE OR REPLACE PROCEDURE CHANGE_SALARY(P_EMPNO IN NUMBER DEFAULT 7788,P_RAISE NUMBER DEFAULT 10)

V_ENAME VARCHAR2(10);

V_SAL NUMBER(5);

BEGIN

SELECT ENAME,SAL INTO V_ENAME,V_SAL FROM EMP WHERE EMPNO=P_EMPNO;

UPDATE EMP SET SAL=SAL+P_RAISE WHERE EMPNO=P_EMPNO;

DBMS_OUTPUT.PUT_LINE('雇员'||V_ENAME||'的工资被改为'||TO_CHAR(V_SAL+P_RAISE));

COMMIT;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('发生错误，修改失败！');

ROLLBACK;

END;

执行结果为：

过程已创建。

步骤3：调用存储过程，在输入区中输入以下语句并执行：

EXECUTE CHANGE_SALARY(7788,80)

显示结果为：

雇员SCOTT的工资被改为3080?

说明：从执行结果可以看到，雇员SCOTT的工资已由原来的3000改为3080。

==========================================================================

【训练16.2】调用存储过程CHANGE_SALARY，不传递参数，使用默认参数值。

在SQL*Plus输入区中输入以下命令并执行：

EXECUTE CHANGE_SALARY

显示结果为：

雇员SCOTT的工资被改为3090

说明：在存储过程的调用中没有传递参数，而是采用了默认值7788和10，即默认雇员号为7788，增加的工资为10。

参数的值由调用者传递，传递的参数的个数、类型和顺序应该和定义的一致。如果顺序不一致，可以采用以下调用方法。如上例，执行语句可以改为：

EXECUTE CHANGE_SALARY(P_RAISE=>80,P_EMPNO=>7788);

可以看出传递参数的顺序发生了变化，并且明确指出了参数名和要传递的值，=>运算符左侧是参数名，右侧是参数表达式，这种赋值方法的意义较清楚。

【练习1】创建插入雇员的存储过程INSERT_EMP，并将雇员编号等作为参数。

在设计存储过程的时候，也可以为参数设定默认值，这样调用者就可以不传递或少传递参数了。

==========================================================================

【训练16.3】使用OUT类型的参数返回存储过程的结果。

步骤1：登录SCOTT账户。

步骤2：在SQL*Plus输入区中输入并编译以下存储过程：

CREATE OR REPLACE PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)

BEGIN

SELECT COUNT(*) INTO P_TOTAL FROM EMP;

END;

执行结果为：

过程已创建。

步骤3：输入以下程序并执行：

DECLARE

V_EMPCOUNT NUMBER;

BEGIN

EMP_COUNT(V_EMPCOUNT);

DBMS_OUTPUT.PUT_LINE('雇员总人数为：'||V_EMPCOUNT);

END;

显示结果为：

雇员总人数为：14

PL/SQL 过程已成功完成。

说明：在存储过程中定义了OUT类型的参数P_TOTAL，在主程序调用该存储过程时，传递了参数V_EMPCOUNT。在存储过程中的SELECT...INTO...语句中对P_TOTAL进行赋值，赋值结果由V_EMPCOUNT变量带回给主程序并显示。

以上程序要覆盖同名的EMP_COUNT存储过程，如果不使用OR REPLACE选项，就会出现以下错误：

ERROR 位于第 1 行:

ORA-00955: 名称已由现有对象使用。

【练习2】创建存储过程，使用OUT类型参数获得雇员经理名。

==========================================================================

【训练16.4】使用IN OUT类型的参数，给电话号码增加区码。

步骤1：登录SCOTT账户。

步骤2：在SQL*Plus输入区中输入并编译以下存储过程：

CREATE OR REPLACE PROCEDURE ADD_REGION(P_HPONE_NUM IN OUT VARCHAR2)

BEGIN

P_HPONE_NUM:='0755-'||P_HPONE_NUM;

END;

执行结果为：

过程已创建。

步骤3：输入以下程序并执行：

DECLARE

V_PHONE_NUM VARCHAR2(15);

BEGIN

V_PHONE_NUM:='26731092';

ADD_REGION(V_PHONE_NUM);

DBMS_OUTPUT.PUT_LINE('新的电话号码：'||V_PHONE_NUM);

END;

显示结果为：

新的电话号码：0755-26731092

PL/SQL 过程已成功完成。

说明：变量V_HPONE_NUM既用来向存储过程传递旧电话号码，也用来向主程序返回新号码。新的号码在原来基础上增加了区号0755和-。

==========================================================================

【训练17.1】创建一个通过雇员编号返回雇员名称的函数GET_EMP_NAME。

步骤1：登录SCOTT账户。

步骤2：在SQL*Plus输入区中输入以下存储函数并编译：

CREATE OR REPLACE FUNCTION GET_EMP_NAME(P_EMPNO NUMBER DEFAULT 7788)

RETURN VARCHAR2

V_ENAME VARCHAR2(10);

BEGIN

SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=P_EMPNO;

RETURN(V_ENAME);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('没有该编号雇员！');

RETURN (NULL);

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE('有重复雇员编号！');

RETURN (NULL);

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('发生其他错误！');

RETURN (NULL);

END;

步骤3：调用该存储函数，输入并执行以下程序：

BEGIN

DBMS_OUTPUT.PUT_LINE('雇员7369的名称是：'|| GET_EMP_NAME(7369));

DBMS_OUTPUT.PUT_LINE('雇员7839的名称是：'|| GET_EMP_NAME(7839));

END;

显示结果为：

雇员7369的名称是：SMITH

雇员7839的名称是：KING

PL/SQL 过程已成功完成。

说明：函数的调用直接出现在程序的DBMS_OUTPUT.PUT_LINE语句中，作为字符串表达式的一部分。如果输入了错误的雇员编号，就会在函数的错误处理部分输出错误信息。试修改雇员编号，重新运行调用部分。

【练习1】创建一个通过部门编号返回部门名称的存储函数GET_DEPT_NAME。

【练习2】将函数的执行权限授予STUDENT账户，然后登录STUDENT账户调用

=============================================================================

【训练18.1】查询过程EMP_COUNT的脚本。

在SQL*Plus中输入并执行如下查询：

select TEXT from user_source WHERE NAME='EMP_COUNT';

结果为：

TEXT

--------------------------------------------------------------------------------

PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)

BEGIN

SELECT COUNT(*) INTO P_TOTAL FROM EMP;

END;

===================================================================================

【训练18.2】查询过程GET_EMP_NAME的参数。

在SQL*Plus中输入并执行如下查询：

DESCRIBE GET_EMP_NAME

结果为：

FUNCTION GET_EMP_NAME RETURNS VARCHAR2

参数名称类型输入/输出默认值？

----------------------------------------- ----------------------------------- ----------------- -------------

P_EMPNO NUMBER(4) IN DEFAULT

==================================================================================

【训练18.3】在发生编译错误时，显示错误。

SHOW ERRORS

以下是一段编译错误显示：

LINE/COL ERROR

------------- -----------------------------------------------------------------

4/2 PL/SQL: SQL Statement ignored

4/36 PLS-00201: 必须说明标识符 'EMPP'

说明：查询一个存储过程或函数是否是有效状态(即编译成功)，可以使用数据字典USER_OBJECTS的STATUS列。

==================================================================================

【训练18.4】查询EMP_LIST存储过程是否可用：

SELECT STATUS FROM USER_OBJECTS WHERE OBJECT_NAME='EMP_LIST';

结果为：

STATUS

------------

VALID

说明：VALID表示该存储过程有效(即通过编译)，INVALID表示存储过程无效或需要重新编译。当Oracle调用一个无效的存储过程或函数时，首先试图对其进行编译，如果编译成功则将状态置成VALID并执行，否则给出错误信息。

==================================================================================

【训练18.5】查询EMP_LIST存储过程的依赖性。

SELECT REFERENCED_NAME,REFERENCED_TYPE FROM USER_DEPENDENCIES WHERE NAME='EMP_LIST';

执行结果：

REFERENCED_NAME REFERENCED_TYPE

------------------------------------------------------------------------------------------ ----------------------------

STANDARD PACKAGE

SYS_STUB_FOR_PURITY_ANALYSIS PACKAGE

DBMS_OUTPUT PACKAGE

DBMS_OUTPUT SYNONYM

DBMS_OUTPUT NON-EXISTENT

EMP TABLE

EMP_COUNT PROCEDURE

说明：可以看出存储过程EMP_LIST依赖一些系统包、EMP表和EMP_COUNT存储过程。如果删除了EMP表或EMP_COUNT存储过程，EMP_LIST将变成无效。

===================================================================================

==================================================================================

【训练19.1】创建管理雇员信息的包EMPLOYE，它具有从EMP表获得雇员信息，修改雇员名称，修改雇员工资和写回EMP表的功能。

步骤1：登录SCOTT账户，输入以下代码并编译：

CREATE OR REPLACE PACKAGE EMPLOYE --包头部分

PROCEDURE SHOW_DETAIL;

PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER);

PROCEDURE SAVE_EMPLOYE;

PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2);

PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER);

END EMPLOYE;

==============================================================

CREATE OR REPLACE PACKAGE BODY EMPLOYE --包体部分

EMPLOYE EMP%ROWTYPE;

-------------- 显示雇员信息 ---------------

PROCEDURE SHOW_DETAIL

BEGIN

DBMS_OUTPUT.PUT_LINE('--===雇员信息 ====');

DBMS_OUTPUT.PUT_LINE('雇员编号：'||EMPLOYE.EMPNO);

DBMS_OUTPUT.PUT_LINE('雇员名称：'||EMPLOYE.ENAME);

DBMS_OUTPUT.PUT_LINE('雇员职务：'||EMPLOYE.JOB);

DBMS_OUTPUT.PUT_LINE('雇员工资：'||EMPLOYE.SAL);

DBMS_OUTPUT.PUT_LINE('部门编号：'||EMPLOYE.DEPTNO);

END SHOW_DETAIL;

----------------- 从EMP表取得一个雇员 --------------------

PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER)

BEGIN

SELECT * INTO EMPLOYE FROM EMP WHERE EMPNO=P_EMPNO;

DBMS_OUTPUT.PUT_LINE('获取雇员'||EMPLOYE.ENAME||'信息成功');

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('获取雇员信息发生错误！');

END GET_EMPLOYE;

---------------------- 保存雇员到EMP表 --------------------------

PROCEDURE SAVE_EMPLOYE

BEGIN

UPDATE EMP SET ENAME=EMPLOYE.ENAME, SAL=EMPLOYE.SAL WHERE EMPNO=

EMPLOYE.EMPNO;

DBMS_OUTPUT.PUT_LINE('雇员信息保存完成！');

END SAVE_EMPLOYE;

---------------------------- 修改雇员名称 ------------------------------

PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2)

BEGIN

EMPLOYE.ENAME:=P_NEWNAME;

DBMS_OUTPUT.PUT_LINE('修改名称完成！');

END CHANGE_NAME;

---------------------------- 修改雇员工资 --------------------------

PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER)

BEGIN

EMPLOYE.SAL:=P_NEWSAL;

DBMS_OUTPUT.PUT_LINE('修改工资完成！');

END CHANGE_SAL;

END EMPLOYE;

步骤2：获取雇员7788的信息:

SET SERVEROUTPUT ON

EXECUTE EMPLOYE.GET_EMPLOYE(7788);

结果为：

获取雇员SCOTT信息成功

PL/SQL 过程已成功完成。

步骤3：显示雇员信息：

EXECUTE EMPLOYE.SHOW_DETAIL;

结果为：

------------------ 雇员信息 ------------------

雇员编号：7788

雇员名称：SCOTT

雇员职务：ANALYST

雇员工资：3800

部门编号：20

PL/SQL 过程已成功完成。

步骤4：修改雇员工资：

EXECUTE EMPLOYE.CHANGE_SAL(4000);

结果为：

修改工资完成！

PL/SQL 过程已成功完成。

步骤5：将修改的雇员信息存入EMP表

EXECUTE EMPLOYE.SAVE_EMPLOYE;

结果为：

雇员信息保存完成！

PL/SQL 过程已成功完成。

说明：该包完成将EMP表中的某个雇员的信息取入内存记录变量，在记录变量中进行修改编辑，在确认显示信息正确后写回EMP表的功能。记录变量EMPLOYE用来存储取得的雇员信息，定义为私有变量，只能被包的内部模块访问。

【练习1】为包增加修改雇员职务和部门编号的功能。

===================================================================================

8.阶段训练

===================================================================================

下面的训练通过定义和创建完整的包EMP_PK并综合运用本章的知识，完成对雇员表的插入、删除等功能，包中的主要元素解释如表所示。

程序结构类型说明

V_EMP_COUNT 公有变量跟踪雇员的总人数变化，插入、删除雇员的同时修改该变量的值

INIT 公有过程对包进行初始化，初始化雇员人数和工资修改的上、下限

LIST_EMP 公有过程显示雇员列表

INSERT_EMP 公有过程通过编号插入新雇员

DELETE_EMP 公有过程通过编号删除雇员

CHANGE_EMP_SAL 公有过程通过编号修改雇员工资

V_MESSAGE 私有变量存放准备输出的信息

C_MAX_SAL 私有变量对工资修改的上限

C_MIN_SAL 私有变量对工资修改的下限

SHOW_MESSAGE 私有过程显示私有变量V_MESSAGE中的信息

EXIST_EMP 私有函数判断某个编号的雇员是否存在，该函数被INSERT_EMP、DELETE_EMP和CHANGE_EMP_SAL等过程调用

===================================================================================

【训练1】完整的雇员包EMP_PK的创建和应用。

步骤1：在SQL*Plus中登录SCOTT账户，输入以下包头和包体部分，按“执行”按钮编译：

CREATE OR REPLACE PACKAGE EMP_PK

--包头部分

V_EMP_COUNT NUMBER(5);

--雇员人数

PROCEDURE INIT(P_MAX NUMBER,P_MIN NUMBER); --初始化

PROCEDURE LIST_EMP;

--显示雇员列表

PROCEDURE INSERT_EMP(P_EMPNO NUMBER,P_ENAMEVARCHAR2,P_JOB VARCHAR2,

P_SAL NUMBER);

--插入雇员

PROCEDURE DELETE_EMP(P_EMPNO NUMBER); --删除雇员

PROCEDURE CHANGE_EMP_SAL(P_EMPNO NUMBER,P_SAL NUMBER);

--修改雇员工资

END EMP_PK;

/CREATE OR REPLACE PACKAGE BODY EMP_PK

--包体部分

V_MESSAGE VARCHAR2(50); --显示信息

V_MAX_SAL NUMBER(7); --工资上限

V_MIN_SAL NUMBER(7); --工资下限

FUNCTION EXIST_EMP(P_EMPNO NUMBER) RETURN BOOLEAN; --判断雇员是否存在函数

PROCEDURE SHOW_MESSAGE; --显示信息过程

------------------------------- 初始化过程 ----------------------------

PROCEDURE INIT(P_MAX NUMBER,P_MIN NUMBER)

BEGIN

SELECT COUNT(*) INTO V_EMP_COUNT FROM EMP;

V_MAX_SAL:=P_MAX;

V_MIN_SAL:=P_MIN;

V_MESSAGE:='初始化过程已经完成！';

SHOW_MESSAGE;

END INIT;

---------------------------- 显示雇员列表过程 ---------------------

PROCEDURE LIST_EMP

BEGIN

DBMS_OUTPUT.PUT_LINE('姓名职务工资');

FOR emp_rec IN (SELECT * FROM EMP)

LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(emp_rec.ename,10,'')||RPAD(emp_rec.job,10,' ')||TO_CHAR(emp_rec.sal));

END LOOP;

DBMS_OUTPUT.PUT_LINE('雇员总人数'||V_EMP_COUNT);

END LIST_EMP;

----------------------------- 插入雇员过程 -----------------------------

PROCEDUREINSERT_EMP(P_EMPNO NUMBER,P_ENAMEVARCHAR2,P_JOB VARCHAR2,P_SAL NUMBER)

BEGIN

IF NOT EXIST_EMP(P_EMPNO) THEN

INSERT INTO EMP(EMPNO,ENAME,JOB,SAL) VALUES(P_EMPNO,P_ENAME,P_JOB,P_SAL);

COMMIT;

V_EMP_COUNT:=V_EMP_COUNT+1;

V_MESSAGE:='雇员'||P_EMPNO||'已插入!';

ELSE

V_MESSAGE:='雇员'||P_EMPNO||'已存在,不能插入!';

END IF;

SHOW_MESSAGE;

EXCEPTION

WHEN OTHERS THEN

V_MESSAGE:='雇员'||P_EMPNO||'插入失败!';

SHOW_MESSAGE;

END INSERT_EMP;

--------------------------- 删除雇员过程 --------------------

PROCEDURE DELETE_EMP(P_EMPNO NUMBER)

BEGIN

IF EXIST_EMP(P_EMPNO) THEN

DELETE FROM EMP WHERE EMPNO=P_EMPNO;

COMMIT;

V_EMP_COUNT:=V_EMP_COUNT-1;

V_MESSAGE:='雇员'||P_EMPNO||'已删除!';

ELSE

V_MESSAGE:='雇员'||P_EMPNO||'不存在，不能删除!';

END IF;

SHOW_MESSAGE;

EXCEPTION

WHEN OTHERS THEN

V_MESSAGE:='雇员'||P_EMPNO||'删除失败!';

SHOW_MESSAGE;

END DELETE_EMP;

--------------------------------------- 修改雇员工资过程 ------------------------------------

PROCEDURE CHANGE_EMP_SAL(P_EMPNO NUMBER,P_SAL NUMBER)

BEGIN

IF (P_SAL>V_MAX_SAL OR P_SAL<V_MIN_SAL) THEN

V_MESSAGE:='工资超出修改范围!';

ELSIF NOT EXIST_EMP(P_EMPNO) THEN

V_MESSAGE:='雇员'||P_EMPNO||'不存在，不能修改工资!';

ELSE

UPDATE EMP SET SAL=P_SAL WHERE EMPNO=P_EMPNO;

COMMIT;

V_MESSAGE:='雇员'||P_EMPNO||'工资已经修改!';

END IF;

SHOW_MESSAGE;

EXCEPTION

WHEN OTHERS THEN

V_MESSAGE:='雇员'||P_EMPNO||'工资修改失败!';

SHOW_MESSAGE;

END CHANGE_EMP_SAL;

---------------------------- 显示信息过程 ----------------------------

PROCEDURE SHOW_MESSAGE

BEGIN

DBMS_OUTPUT.PUT_LINE('提示信息：'||V_MESSAGE);

END SHOW_MESSAGE;

------------------------ 判断雇员是否存在函数 -------------------

FUNCTION EXIST_EMP(P_EMPNO NUMBER)

RETURN BOOLEAN

V_NUM NUMBER; --局部变量

BEGIN

SELECT COUNT(*) INTO V_NUM FROM EMP WHERE EMPNO=P_EMPNO;

IF V_NUM=1 THEN

RETURN TRUE;

ELSE

RETURN FALSE;

END IF;

END EXIST_EMP;

-----------------------------

END EMP_PK;

结果为：

程序包已创建。

程序包主体已创建。

步骤2：初始化包：

SET SERVEROUTPUT ON

EXECUTE EMP_PK.INIT(6000,600);

显示为：

提示信息：初始化过程已经完成！

步骤3：显示雇员列表：

EXECUTE EMP_PK.LIST_EMP;

显示为：

姓名职务工资

SMITH CLERK 1560

ALLEN SALESMAN 1936

WARD SALESMAN 1830

JONES MANAGER 2975

...

雇员总人数：14

PL/SQL 过程已成功完成。

步骤4：插入一个新记录：

EXECUTE EMP_PK.INSERT_EMP(8001,'小王','CLERK',1000);

显示结果为：

提示信息：雇员8001已插入!

PL/SQL 过程已成功完成。

步骤5：通过全局变量V_EMP_COUNT查看雇员人数：

BEGIN

DBMS_OUTPUT.PUT_LINE(EMP_PK.V_EMP_COUNT);

END;

显示结果为：

PL/SQL 过程已成功完成。

步骤6：删除新插入记录：

EXECUTE EMP_PK.DELETE_EMP(8001);

显示结果为：

提示信息：雇员8001已删除!

PL/SQL 过程已成功完成。

再次删除该雇员：

EXECUTE EMP_PK.DELETE_EMP(8001);

结果为：

提示信息：雇员8001不存在，不能删除!

步骤7：修改雇员工资：

EXECUTE EMP_PK.CHANGE_EMP_SAL(7788,8000);

显示结果为：

提示信息：工资超出修改范围!

PL/SQL 过程已成功完成。

步骤8：授权其他用户调用包：

如果是另外一个用户要使用该包，必须由包的所有者授权，下面授予STUDEN账户对该包的使用权：

GRANT EXECUTE ON EMP_PK TO STUDENT;

每一个新的会话要为包中的公用变量开辟新的存储空间，所以需要重新执行初始化过程。两个会话的进程互不影响。

步骤9：其他用户调用包。

启动另外一个SQL*Plus，登录STUDENT账户，执行以下过程：

SET SERVEROUTPUT ON

EXECUTE SCOTT.EMP_PK. EMP_PK.INIT(5000,700);

结果为：

提示信息：初始化过程已经完成！

PL/SQL 过程已成功完成。

说明：在初始化中设置雇员的总人数和修改工资的上、下限，初始化后V_EMP_COUNT为14人，插入雇员后V_EMP_COUNT为15人。V_EMP_COUNT为公有变量，所以可以在外部程序中使用DBMS_OUTPUT.PUT_LINE输出，引用时用EMP_PK.V_EMP_COUNT的形式，说明所属的包。而私有变量V_MAX_SAL和V_MIN_SAL不能被外部访问，只能通过内部过程来修改。同样，EXIST_EMP和SHOW_MESSAGE也是私有过程，也只能在过程体内被其他模块引用。

注意：在最后一个步骤中，因为STUDENT模式调用了SCOTT模式的包，所以包名前要增加模式名SCOTT。不同的会话对包的调用属于不同的应用，所以需要重新进行初始化。

==============================================================================================

8 练习

==============================================================================================

1．如果存储过程的参数类型为OUT，那么调用时传递的参数应该为：

A．常量 B．表达式 C．变量 D．都可以

2．下列有关存储过程的特点说法错误的是：

A．存储过程不能将值传回调用的主程序

B．存储过程是一个命名的模块

C．编译的存储过程存放在数据库中

D．一个存储过程可以调用另一个存储过程

3．下列有关函数的特点说法错误的是：

A．函数必须定义返回类型

B．函数参数的类型只能是IN

C．在函数体内可以多次使用RETURN语句

D．函数的调用应使用EXECUTE命令

4．包中不能包含的元素为:

A．存储过程 B．存储函数

C．游标 D．表

5．下列有关包的使用说法错误的是：

A．在不同的包内模块可以重名

B．包的私有过程不能被外部程序调用

C．包体中的过程和函数必须在包头部分说明

D．必须先创建包头，然后创建包体

==============================================================================================

9.触发器

==============================================================================================

【训练20.1】创建包含插入、删除、修改多种触发事件的触发器DML_LOG，对EMP表的操作进行记录。用INSERTING、DELETING、UPDATING谓词来区别不同的DML操作。

在创建触发器之前，需要先创建事件记录表LOGS，该表用来对操作进行记录。该表的字段含义解释如下：

LOG_ID：操作记录的编号，数值型，它是该表的主键，由序列自动生成。

LOG_TABLE：进行操作的表名，字符型，非空，该表设计成可以由多个触发器共享使用。比如我们可以为dept表创建类似的触发器，同样将操作记录到该表。

LOG_DML：操作的动作，即INSERT、DELETE或UPDATE三种之一。

LOG_KEY_ID：操作时表的主键值，数值型。之所以记录表的主键，是因为主键是表的记录的惟一标识，可以识别是对哪一条记录进行了操作。对于emp表，主键是empno。

LOG_DATE：操作的日期，日期型，取当前的系统时间。

LOG_USER：操作者，字符型，取当时的操作者账户名。比如登录SCOTT账户进行操作，在该字段中，记录账户名为SCOTT

步骤1：在SQL*Plus中登录STUDENT账户，创建如下的记录表LOGS：

CREATE TABLE logs(

LOG_ID NUMBER(10) PRIMARY KEY,

LOG_TABLE VARCHAR2(10) NOT NULL,

LOG_DML VARCHAR2(10),

LOG_KEY_ID NUMBER(10),

LOG_DATE DATE,

LOG_USER VARCHAR2(15)

);

执行结果：

表已创建。

步骤2：创建一个LOGS表的主键序列LOGS_ID_SEQ：

CREATE SEQUENCE logs_id_squ INCREMENT BY 1

START WITH 1 MAXVALUE 9999999 NOCYCLE NOCACHE;

执行结果：

序列已创建。

步骤3：创建和编译以下触发器：

CREATE OR REPLACE TRIGGER DML_LOG

BEFORE --触发时间为操作前

DELETE OR INSERT OR UPDATE -- 由三种事件触发

ON scott.emp

FOR EACH ROW -- 行级触发器

BEGIN

IF INSERTING THEN

INSERT INTO logs VALUES(logs_id_squ.NEXTVAL,'EMP','INSERT',:new.empno,SYSDATE,USER);

ELSIF DELETING THEN

INSERT INTO logs VALUES(logs_id_squ.NEXTVAL,'EMP','DELETE',:old.empno,SYSDATE,USER);

ELSE

INSERT INTO logs VALUES(logs_id_squ.NEXTVAL,'EMP','UPDATE',:new.empno,SYSDATE,USER);

END IF;

END;

执行结果：

触发器已创建

步骤4：在EMP表中插入记录：

INSERT INTO scott.emp(empno,ename,job,sal) VALUES(8001,'MARY','CLERK',1000);

COMMIT;

执行结果：

已创建1行。

提交完成。

步骤5：检查LOGS表中记录的信息：

SELECT * FROM LOGS;

执行结果为：

LOG_ID LOG_TABLE LOG_DML LOG_KEY_ID LOG_DATE LOG_USER

----------------- ----------------- ------------------ ----------------------- ---------------- -------------------

1 EMP INSERT 8001 29-3月 -04 STUDENT

已选择 1 行。

说明：本例中在emp表上创建了一个由INSERT或DELETE或UPDATE事件触发的行级触发器，触发器的名称是LOG_EMP。对于不同的操作，记录的内容不同。本例中只插入了一条记录，如果用一条不带WHERE条件的UPDATE语句来修改所有雇员的工资，则将逐行触发触发器。

INSERT、DELETE和UPDATE都能引发触发器动作，在分支语句中使用INSERTING、DELETING和UPDATING来区别是由哪种操作引发的触发器动作。

在本例的插入动作中，LOG_ID字段由序列LOG_ID_SQU自动填充为1；LOGS表LOG_KEY_ID字段记录的是新插入记录的主键8001；LOD_DML字段记录的是插入动作INSERT；LOG_TABLE字段记录当前表名EMP；LOG_DATE字段记录插入的时间04年3月1日；LOG_USER字段记录插入者STUDENT。

【练习1】修改、删除刚刚插入的雇员记录，提交后检查LOGS表的结果。

【练习2】为DEPT表创建同样的触发器，使用LOGS表进行记录，并检验结果。

==============================================================================================

【训练20.2】创建一个行级触发器LOG_SAL，记录对职务为CLERK的雇员工资的修改，且当修改幅度超过200时才进行记录。用WHEN条件限定触发器。

在创建触发器之前，需要先创建事件记录表LOGERR，该表用来对操作进行记录。该表的字段含义解释如下：

NUM：数值型，用于记录序号。

MESSAGE：字符型，用于记录错误信息。

步骤1：在SQL*Plus中登录STUDENT账户，创建如下的记录表LOGERR：

CREATE TABLE logerr(

NUM NUMBER(10) NOT NULL,

MESSAGE VARCHAR2(50) NOT NULL

);

执行结果：

表已创建。

步骤2：创建和编译以下触发器：

CREATE OR REPLACE TRIGGER log_sal

BEFORE

UPDATE OF sal

ON scott.emp

FOR EACH ROW

WHEN (new.job='CLERK' AND (ABS(new.sal-old.sal)>200))

DECLARE

v_no NUMBER;

BEGIN

SELECT COUNT(*) INTO v_no FROM logerr;

INSERT INTO logerr VALUES(v_no+1,'雇员'||:new.ename||'的原工资：'||:old.sal||'新工资：'||:new.sal);

END;

执行结果：

触发器已创建。

步骤3：在EMP表中更新记录：

UPDATE emp SET sal=sal+550 WHERE empno=7788;

UPDATE emp SET sal=sal+500 WHERE empno=7369;

UPDATE emp SET sal=sal+50 WHERE empno=7876;

COMMIT;

执行结果：

已更新 1 行。

提交完成。

步骤4：检查LOGSAL表中记录的信息：

SELECT * FROM logerr;

执行结果为：

NUM MESSAGE

------------------ --------------------------------------------------------

1 雇员SMITH的原工资：800新工资：1300

已选择 1 行。

说明：本例中，在emp表的sal列上创建了一个由UPDATE事件触发的行级触发器，触发器的名称是LOG_SAL。该触发器由WHEN语句限定，只有当被修改工资的雇员职务为CLERK，且修改的工资超过200时才进行触发，否则不进行触发。

所以在验证过程中，虽然修改了3条记录，但通过查询语句发现：第一条修改语句修改编号为7788的SCOTT记录，因为SCOTT的职务是ANALYST，不符合WHEN条件，没有引起触发器动作；第二条修改语句修改编号为7369的SMITH的记录，职务为CLERK，因为增加的工资(500)超过了200，所以引起触发器动作，并在LOGERR表中进行了记录；第三条修改语句修改编号为7876的雇员ADAMS的记录，虽然ADAMS的职务为CLERK，但修改的工资(50)没有超过200，所以没有引起触发器动作。

在以上实例中，记录了对工资的修改超出范围的信息，但没有限制对工资的修改。那么当对雇员工资的修改幅度不满足条件时，能否直接限制对工资的修改呢？答案是肯定的。

==============================================================================================

【训练20.3】创建触发器CHECK_SAL，当对职务为CLERK的雇员的工资修改超出500至2000的范围时，进行限制。

步骤1：创建和编译以下触发器：

CREATE OR REPLACE TRIGGER CHECK_SAL

BEFORE

UPDATE

ON emp

FOR EACH ROW

BEGIN

IF :new.job='CLERK' AND (:new.sal<500 OR :new.sal>2000) THEN

RAISE_APPLICATION_ERROR(-20001, '工资修改超出范围,操作取消！');

END IF;

END;

执行结果：

触发器已创建。

步骤2：在EMP表中插入记录：

UPDATE emp SET sal=800 WHERE empno=7876;

UPDATE emp SET sal=450 WHERE empno=7876;

COMMIT;

执行结果：

UPDATE emp SET sal=450 WHERE empno=7876

ERROR 位于第 1 行:

ORA-20001: 工资修改超出范围,操作取消！

ORA-06512: 在"STUDENT.CHECK_SAL", line 3

ORA-04088: 触发器 'STUDENT.CHECK_SAL' 执行过程中出错提交完成。

步骤3：检查工资的修改结果：

SELECT empno,ename,job,sal FROM emp WHERE empno=7876;

执行结果为：

EMPNO ENAME JOB SAL

------------------ ------------- ------------- ------------------------

7876 ADAMS CLERK 800

说明：在触发器中，当IF语句的条件满足时，即对职务为CLERK的雇员工资的修改超出指定范围时，用RAISE_APPLICATION_ERROR语句来定义一个临时定义的异常，并立即引发异常。由于触发器是BEFORE类型，因此触发器先执行，触发器因异常而终止，SQL语句的执行就会取消。

通过步骤2的执行信息可以看到，第一条语句修改编号为7876的雇员ADAMS的工资为800，成功执行。第二条语句修改雇员ADAMS的工资为450，发生异常，执行失败。这样就阻止了不符合条件的工资的修改。通过步骤3的查询可以看到，雇员ADAMS最后的工资是800，即发生异常之前的修改结果。

【练习3】限定对emp表的修改，只能修改部门10的雇员工资。

=================================================================================

【训练20.4】创建一个行级触发器CASCADE_UPDATE，当修改部门编号时，EMP表的相关行的部门编号也自动修改。该触发器称为级联修改触发器。

步骤1：创建和编译以下触发器：

CREATE OR REPLACE TRIGGER CASCADE_UPDATE

AFTER

UPDATE OF deptno

ON DEPT

FOR EACH ROW

BEGIN

UPDATE EMP SET EMP.DEPTNO=:NEW.DEPTNO

WHERE EMP.DEPTNO=:OLD.DEPTNO;

END;

执行结果：

触发器已创建

步骤2：验证触发器：

UPDATE dept SET deptno=11 WHERE deptno=10;

COMMIT;

执行结果：

已更新 1 行。

执行查询：

SELECT empno,ename,deptno FROM emp;

执行结果：

EMPNO ENAME DEPTNO

----------------- ----------- -------------------------

7369 SMITH 20

7499 ALLEN 30

7521 WARD 30

7566 JONES 20

7654 MARTIN 30

7698 BLAKE 30

7782 CLARK 11

7839 KING 11

7844 TURNER 30

7876 ADAMS 20

7900 JAMES 30

7902 FORD 20

7934 MILLER 11

7788 SCOTT 20

说明：通过检查雇员的部门编号，发现原来编号为10的部门编号被修改为11。

本例中的UPDATE OF deptno表示只有在修改表的DEPTNO列时才引发触发器，对其他列的修改不会引起触发器的动作。在触发器中，对雇员表的部门编号与修改之前的部门编号一样的雇员，修改其部门编号为新的部门编号。注意，在语句中同时用到了:new和:old来引用修改部门编号前后的部门编号。

【练习4】建立级联删除触发器CASCADE_DELETE，当删除部门时，级联删除EMP表的雇员记录。

利用触发器还可以修改数据。

========================================================================

【训练20.5】将插入的雇员的名字变成以大写字母开头。

步骤1：创建和编译以下触发器：

CREATE OR REPLACE TRIGGER INITCAP

BEFORE INSERT

ON EMP

FOR EACH ROW

BEGIN

:new.ename:=INITCAP(:new.ename);

END;

执行结果：

触发器已创建。

步骤2：验证运行结果：

INSERT INTO emp(empno,ename,job,sal) VALUES(1000,'BILL','CLERK',1500);

执行结果：

已创建 1 行。

执行查询：

SELECT ename,job,sal FROM emp WHERE empno=1000;

执行结果：

ENAME JOB SAL

------------- ------------- ------------------------

Bill CLERK 1500

说明：在本例中，通过直接为:new.ename进行赋值，修改了插入的值，但是这种用法只能在BEFORE型触发器中使用。验证结果为，在插入语句中雇员名称为大写的BILL，查询结果中雇员名称已经转换成以大写开头的Bill。

【练习5】限定一次对雇员的工资修改不超过原工资的10%。

=====================================================================

【训练21.1】创建一个语句级触发器CHECK_TIME，限定对表EMP的修改时间为周一至周五的早8点至晚5点。

步骤1：创建和编译以下触发器：

CREATE OR REPLACE TRIGGER CHECK_TIME

BEFORE

UPDATE OR INSERT OR DELETE

ON EMP

BEGIN

IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN'))

OR TO_CHAR(SYSDATE,'HH24')< '08'

OR TO_CHAR(SYSDATE,'HH24')>='17' THEN

RAISE_APPLICATION_ERROR(-20500,'非法时间修改表错误！');

END IF;

END;

执行结果：

触发器已创建。

步骤2：当前时间为18点50分，在EMP表中插入记录：

UPDATE EMP SET SAL=3000 WHERE EMPNO=7369;

显示结果为：

UPDATE EMP SET SAL=3000 WHERE EMPNO=7369

ERROR 位于第 1 行:

ORA-20500: 非法时间修改表错误！

ORA-06512: 在"STUDENT.CHECK_TIME", line 5

ORA-04088: 触发器 'STUDENT.CHECK_TIME' 执行过程中出错

说明：通过引发异常限制对数据库进行的插入、删除和修改操作的时间。SYSDATE用来获取系统当前时间，并按不同的格式字符串进行转换。“DY”表示获取英文表示的星期简写，“HH24”表示获取24小时制时间的小时。

当在18点50分修改表中的数据时，由于时间在8点至17点(晚5点)之外，所以产生“非法时间修改表错误”的用户自定义错误，修改操作终止。

【练习1】设计一个语句级触发器，限定只能对数据库进行修改操作，不能对数据库进行插入和删除操作。在需要进行插入和删除时，将触发器设置为无效状态，完成后重新设置为生效状态。

===========================================================================

【训练22.1】创建触发器，对本次数据库启动以来的用户登录时间进行记录，每次数据库启动后，先清空该表。

步骤1：创建登录事件记录表：

CREATE TABLE userlog (

USERNAME VARCHAR2(20),

LOGON_TIME DATE);

执行结果：

表已创建。

步骤2：创建数据库STARTUP事件触发器：

CREATE OR REPLACE TRIGGER INIT_LOGON

AFTER

STARTUP

ON DATABASE

BEGIN

DELETE FROM userlog;

END;

执行结果：

触发器已创建。

步骤3：创建数据库LOGON事件触发器：

CREATE OR REPLACE TRIGGER DATABASE_LOGON

AFTER

LOGON

ON DATABASE

BEGIN

INSERT INTO userlog

VALUES(sys.login_user,sysdate);

END;

执行结果：

触发器已创建。

步骤4：验证DATABASE_LOGON触发器：

CONNECT SCOTT/TIGER@LOCAL;

CONNECT STUDENT/test@LOCAL;

CONNECT HMISW2003/H13584@LOCAL;

执行结果：

已连接。

执行查询：

SELECT username,TO_CHAR(logon_time,'YYYY/MM/DD HH24:MI:SS') FROM userlog;

执行结果：

USERNAME TO_CHAR(LOGON_TIME,

----------------------------- -----------------------------------------

SCOTT 2004/03/29 22:42:20

STUDENT 2004/03/29 22:42:20

步骤5：验证INIT_LOGON触发器。

重新启动数据库，登录STUDENT账户：

SELECT username,TO_CHAR(logon_time,'YYYY/MM/DD HH24:MI:SS') FROM userlog;

执行结果：

USERNAME TO_CHAR(LOGON_TIME,

-------------------------------- ---------------------------------------

STUDENT 2004/03/29 22:43:59

已选择 1 行

说明：本例中共创建了两个数据库级事件触发器。DATABASE_LOGON在用户登录时触发，向表userlog中增加一条记录，记录登录用户名和登录时间。INIT_LOGON在数据库启动时触发，清除userlog表中记录的数据。所以当数据库重新启动后，重新登录STUDENT账户，此时userlog表中只有一条记录。

===========================================================================

【训练22.2】创建STUDENT_LOGON模式级触发器，专门记录STUDENT账户的登录时间：

CREATE OR REPLACE TRIGGER STUDENT_LOGON

AFTER

LOGON ON STUDENT.SCHEMA

BEGIN

INSERT INTO userlog

VALUES(sys.login_user,sysdate);

END;

执行结果：

触发器已创建。

说明：为当前模式创建触发器，可以省略SCHEMA前面的模式名。

【练习1】修改DATABASE_LOGON触发器和userlog表，增加对退出时间的记录。

=============================================================================

【训练23.1】通过触发器阻止对emp表的删除。

步骤1：创建DDL触发器：

CREATE OR REPLACE TRIGGER NODROP_EMP

BEFORE

DROP ON SCHEMA

BEGIN

IF Sys.Dictionary_obj_name='EMP' THEN

RAISE_APPLICATION_ERROR(-20005,'错误信息：不能删除emp表！');

END IF;

END;

执行结果：

触发器已创建。

步骤2：通过删除emp表验证触发器：

DROP TABLE emp;

执行结果：

DROP TABLE emp

ERROR 位于第 1 行:

ORA-00604: 递归 SQL 层 1 出现错误

ORA-20005: 错误信息：不能删除emp表！

ORA-06512: 在line 3

说明：该触发器阻止在当前模式下对emp表的删除，但不阻止删除其他对象。Sys.Dictionary_obj_name属性返回要删除的对象名称。

============================================================================

【训练24.1】在emp表的视图上，通过触发器修改emp表。

步骤1：创建视图emp_name：

CREATE OR REPLACE VIEW emp_name AS SELECT ename FROM emp;

执行结果：

视图已建立。

步骤1：创建替代触发器：

CREATE OR REPLACE TRIGGER change_name

INSTEAD OF INSERT ON emp_name

DECLARE

V_EMPNO NUMBER(4);

BEGIN

SELECT MAX(EMPNO)+1 INTO V_EMPNO FROM EMP;

INSERT INTO emp(empno,ename)

VALUES(V_EMPNO,:new.ename);

END;

执行结果：

触发器已创建。

步骤2：向emp_name视图插入记录：

INSERT INTO emp_name VALUES('BROWN');

COMMIT;

执行结果：

已创建 1 行。

提交完成。

说明：向视图直接插入雇员名将会发生错误，因为emp表的雇员编号列不允许为空。通过创建替代触发器，将向视图插入雇员名称转换为向emp表插入雇员编号和雇员名称，雇员编号取当前的最大雇员编号加1。试检查emp表的雇员列表。

=================================================================================================================================

【训练24.2】在emp表的视图emp_name上，通过触发器阻止对emp表的删除。

步骤1：阻止通过视图删除雇员，并显示用户自定义错误信息：

CREATE OR REPLACE TRIGGER delete_from_ename

INSTEAD OF DELETE ON emp_name

BEGIN

RAISE_APPLICATION_ERROR(-20006,'错误信息：不能在视图中删除emp表的雇员！');

END;

执行结果：

触发器已创建。

步骤2：通过对视图进行删除来验证触发器：

DELETE FROM emp_name;

执行结果：

DELETE FROM emp_name

ERROR 位于第 1 行:

ORA-20006: 错误信息：不能在视图中删除emp表的雇员！

ORA-06512:

在"STUDENT.DELETE_FROM_ENAME", line 2

ORA-04088: 触发器 'STUDENT.DELETE_FROM_ENAME' 执行过程中出错

说明：可以通过视图emp_name对雇员进行删除，比如执行DELETE FROM emp_name语句将删除雇员表的全部雇员。但是由于在emp_name视图中只能看到一部分雇员信息，所以删除可能会产生误操作。通过定义一个替代触发器，可阻止通过emp_name视图对emp表雇员进行删除，但不阻止直接对emp表进行删除

===========================================================================================

【训练1】显示触发器CHECK_TIME的体部分：

SELECT TRIGGER_BODY FROM USER_TRIGGERS WHERE TRIGGER_NAME='CHECK_TIME';

结果为：

TRIGGER_BODY

----------------------------------------------------------------------------------------

BEGIN

IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN'))

OR TO_CHAR(SYSDATE,'HH24')<

TRIGGER_BODY字段为LONG类型，只显示出脚本的一部分内容。

===========================================================================================

阶段训练

===========================================================================================

【训练1】创建触发器，进行表的同步复制。

步骤1：创建emp表的复本employee:

CREATE TABLE employee AS SELECT * FROM emp;

执行结果：

表已创建。

步骤2：创建和编译以下触发器:

CREATE OR REPLACE TRIGGER DUPLICATE_EMP

AFTER

UPDATE OR INSERT OR DELETE

ON EMP

FOR EACH ROW

BEGIN

IF INSERTING THEN

INSERT INTO employee

VALUES(:new.empno,:new.ename,:new.job,:new.mgr,

:new.hiredate,:new.sal,:new.comm,:new.deptno);

ELSIF DELETING THEN

DELETE FROM employee

WHERE empno=:old.empno;

ELSE

UPDATE employee SET

empno=:new.empno,

ename=:new.ename,

job=:new.job,

mgr=:new.mgr,

hiredate=:new.hiredate,

sal=:new.sal,

comm=:new.comm,

deptno=:new.deptno

WHERE empno=:old.empno;

END IF;

END;

执行结果：

触发器已创建。

步骤3：对emp表进行插入、删除和更新：

DELETE FROM emp WHERE empno=7934;

INSERT INTO emp(empno,ename,job,sal) VALUES(8888,'ROBERT','ANALYST',2900);

UPDATE emp SET sal=3900 WHERE empno=7788;

COMMIT;

执行结果：

已删除 1 行。

已创建 1 行。

已更新 1 行。

提交完成。

步骤4：检查emp表和employee表中被插入、删除和更新的雇员。

运行结果略，请自行验证。

说明：在触发器中判断触发事件，根据不同的事件对employee表进行不同的操作。

【练习1】创建一个emp表的触发器EMP_TOTAL，每当向雇员表插入、删除或更新雇员信息时，将新的统计信息存入统计表EMPTOTAL，使统计表总能够反映最新的统计信息。

统计表是记录各部门雇员总人数、总工资的统计表，结构如下：

部门编号 number(2)

总人数 number(5)

总工资 number(10,2)

===========================================================================================

练习

===========================================================================================

1. 下列有关触发器和存储过程的描述，正确的是：

A. 两者都可以传递参数

B. 两者都可以被其他程序调用

C. 两种模块中都可以包含数据库事务语句

D. 两者创建的系统权限不同

2. 下列事件，属于DDL事件的是：

A. INSERT B. LOGON

C. DROP D. SERVERERROR

3. 假定在一个表上同时定义了行级和语句级触发器，在一次触发当中，下列说法正确的是：

A. 语句级触发器只执行一次

B. 语句级触发器先于行级触发器执行

C. 行级触发器先于语句级触发器执行

D. 行级触发器对表的每一行都会执行一次

4. 有关行级触发器的伪记录，下列说法正确的是：

A. INSERT事件触发器中，可以使用:old伪记录。

B. DELETE事件触发器中，可以使用:new伪记录。

C. UPDATA事件触发器中，只能使用:new伪记录。

D. UPDATA事件触发器中，可以使用:old伪记录。

5. 下列有关替代触发器的描述，正确的是：

A. 替代触发器创建在表上

B. 替代触发器创建在数据库上

C. 通过替代触发器可以向基表插入数据

D. 通过替代触发器可以向视图插入数据

保存点

SELECT SUM(SAL) FROM EMP;

UPDATE EMP

SET SAL = 7000

WHERE eNAME = 'SMITH';

SAVEPOINT banda_sal1;

SELECT SUM(SAL) FROM EMP;

UPDATE EMP

SET SAL = 12000

WHERE eNAME = 'ALLEN';

SAVEPOINT greene_sal2;

SELECT SUM(SAL) FROM EMP;

ROLLBACK TO SAVEPOINT banda_sal1;

SELECT SUM(SAL) FROM EMP;

ROLLBACK;

SELECT SUM(SAL) FROM EMP;

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/盐析白兔/article/detail/584716