【示例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
0
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】 求:12+32+52+...+152 的值。
输入并执行以下程序:
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;
输出结果为:
1*
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)