第1页 / 共31页
第2页 / 共31页
第3页 / 共31页
第4页 / 共31页
第5页 / 共31页
第6页 / 共31页
第7页 / 共31页
第8页 / 共31页
PLSQL练习题及答案.doc
PL/SQL 简介
PL=PROCEDURAL LANGUAGE
PL/SQL 是对标准 SQL 语言的过程化扩展，引入过程化语言的元素，SQL 语言是非过程化的，可以使
用 c,java,pl/sql 编写数据库函数。
PL/SQL 分为：
匿名 PL/SQL 块，存储在客户端， 嵌入 C 语言 ,JAVA 语言中，直接在其他开发语言中写入
命名 PL/SQL 块 ，存储在数据库
PL/SQL 程序构造：
匿名 PL/SQL 的基本形式：
DECLARE
--声明部分
声明变量，常量，游标，自定义数据类型
例外，子程序, PRAGMA 语句... ...
BEGIN
--执行部分
赋值语句，SQL 语句,过程调用,引发例外
访问游标，控制结构(条件结构，循环结构...)
EXCEPTION
--例外处理部分
程序执行部分在运行时刻遇到错误时，
将控制权交给例外处理部分
例外处理部分结束，程序也结束
END;
变量
声明变量
变量名 数据类型(宽度);
赋值
变量名:=表达式;
不同块的变量可以同名
变量名不应该与表列同名
1 在 PL/SQL 中使用的变量、常量、游标和异常处理的名字都必须先定义后使用。
2 定义部分是包括在关键字 DECLARE 和 BEGIN 之间的部分，每条语句后用‘；’结束。
3 指定 NOT NULL 强制初始化变量
4 使用:=或 DEFAULT 可以初始化变量
5 每行只能定义一个变量
6 如果加上关键字 CONSTANT,则表示所定义的标识符为一个常量，必须为它赋初值。
7 变量的命名规则与 SQL 的规则基本相同，即每个标识符必须以字母开头，而且不分大小写。如果定义的
标识符不能为空，则必须加上关键字 NOT NULL,并赋初值。
SELECT 语句在 PL/SQL 中的形式：
SQL SELECT
SELECT select-list INTO 变量名列表
FROM ...
WHERE ...
例：
DECLARE
A NUMBER;
V_SAL NUMBER(7,2);
BEGIN
A:=7566;
SELECT SAL INTO V_SAL
FROM EMP WHERE EMPNO=A;
DBMS_OUTPUT.PUT_LINE(V_SAL);
END;
将输出信息显示在屏幕：
SET SERVEROUTPUT ON
练习 1，使用匿名 PL/SQL，打印 10 部门的职工的人数和平均工资到屏幕。
DECLARE
V_A NUMBER;
V_B NUMBER(7,2);
BEGIN
SELECT COUNT(*),AVG(SAL) INTO V_A,V_B
FROM EMP
WHERE DEPTNO=10;
DBMS_OUTPUT.PUT_LINE(V_A||' '||V_B);
END;
/
DECLARE
V_A NUMBER;
V_B NUMBER(7,2);
BEGIN
SELECT COUNT(*),AVG(SAL) INTO V_A,V_B
FROM EMP
WHERE DEPTNO=10;
DBMS_OUTPUT.PUT_LINE(V_A||' '||V_B);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有查询到数据');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('行数过多');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (....);
END;
/
练习 2，编写一个匿名块，查询工资超过 2000 的职务是 CLERK 职工的姓名，如果超过一人，则打印
错误信息，其他错误也要捕获。
DECLARE
V_ENAME VARCHAR2(10);
BEGIN
SELECT ENAME INTO V_ENAME
FROM EMP
WHERE SAL>2000 AND JOB='CLERK';
DBMS_OUTPUT.PUT_LINE(V_ENAME);
EXCEPTION WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('超过 1 人');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('查无此人');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('发生错误');
END;
变量类型的特殊声明方式：
变量名 表名.列名%TYPE;
变量名 2 变量名 1%TYPE;
例：
DECLARE
A CONSTANT NUMBER(7,2):= 0;
B A%TYPE := 0;
C A%TYPE DEFAULT 0;
BEGIN
....
例：员工编号为 7566 号的人的姓名。
DECLARE
V_ENAME EMP.ENAME%TYPE;
BEGIN
SELECT ENAME INTO V_ENAME
FROM EMP
WHERE EMPNO=7566;
DBMS_OUTPUT.PUT_LINE(V_ENAME);
EXCEPTION WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('超过 1 人');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('查无此人');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
参数的传递方法：
按照参数位置：在调用时，根据程序声明参数的顺序，传递参数
按照参数名称：使用=〉符号，前面写参数名，后面写值 ，OUT 型参数不写
混合：全面的按照顺序，后面按照名字
DECLARE
A NUMBER;
A NUMBER;
CREATE OR REPLACE PROCEDURE PA(A NUMBER,B NUMBER,C NUMBER)
IS
BEGIN
...
END;
--按照位置传递参数
PA(10,20,30);
PA(A=>10,C=>30,B=>20);
字。
--按名字传递参数可以先按位置再按名字传递参数，但不可以先按位子再按名
函数，过程，包，触发器，命名的 PL/SQL
函数：
1 一种模式对象，一种命名的 pl/sql 块
2 存储在数据字典中
3 带有 0 个或多个参数，不直接返回结果
4 有些可以在 SQL 语句中引用，有些不能
函数的语法格式：
CREATE OR REPLACE FUNCTION 函数名(参数定义列表)
RETURN 返回值数据类型
IS
声明部分
BEGIN
执行部分
EXCEPTION
例外处理部分
END;
参数定义::= 参数名称 数据类型
要求,1 参数定义中，不能说明宽度
2 返回值数据类型，也不可以说明宽度
3 在执行部分的任何出口，必须用
" return 返回值; "结束
例：
CREATE OR REPLACE FUNCTION FADD(A NUMBER,B NUMBER)
RETURN NUMBER
IS
C NUMBER;
BEGIN
C:=A+B;
RETURN C;
END;
调用自定义函数的位置：
Select list of a SELECT command
Condition of the WHERE and HAVING clauses
CONNECT BY, START WITH, ORDER BY, and GROUP BY clauses
VALUES clauses of the INSERT command
SET clause of the UPDATE command
在 SQL 表达式中调用函数的限制：
必须是存储函数
必须是 SINGLE-ROW function 而不是 GROUP 函数
只能含有 IN 参数，而不是 OUT /IN OUT.
必须是在 SQL 中有效的数据类型 CHAR,DATE, 或者 NUMBER.
BOOLEAN, RECORD,TABLE 等 PL/SQL 类型不可以.
过程不可以被调用
参量传递必须是按位置，按名字不可以
约束语句 check 中不可以用
必须有 execute 权限
只有 PL/SQL 2.1 之后的版本才支持
被 select 或者并行化的 INSERT , UPDATE , DELETE 语句调用的时候，不能有 DML
当在 INSERT, UPDATE, 或 DELETE 语句中调用时,函数不能查询或者修改被当前语句修改的任
何数据库表
当被 SELECT, INSERT, UPDATE, 或 DELETE 语句调用时, 函数不能执行 SQL 事务控制语句
(如 COMMIT), 会话控制语句(如 SET ROLE), 或系统控制语句(如 ALTER SYSTEM). 也不能执行
DDL 语句.
函数不能调用任何违反上述限制的其他子程序
练习 1，编写一个函数，输入两个数字，返回两个数字的乘积。
CREATE OR REPLACE FUNCTION FMUL(A NUMBER,B NUMBER) RETURN NUMBER
IS
BEGIN
RETURN A*B;
END;
注意：
组函数和少数几个单行函数(例如 decode)，
不可以直接用在 PL/SQL 表达式中
练习 2，编写一个函数，输入职工编号，返回职工姓名。（要求处理例外)
CREATE OR REPLACE
FUNCTION F_GETENAME(PNO EMP.EMPNO%TYPE)
RETURN EMP.ENAME%TYPE
IS
V_ENAME EMP.ENAME%TYPE;
BEGIN
SELECT ENAME INTO V_ENAME
WHERE EMPNO=PNO;
RETURN V_ENAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN '查无此人';
WHEN OTHERS THEN
RETURN SQLERRM;
END;
控制结构： 条件结构 ，循环结构，标号
条件结构：
……………………………………………………
IF 条件 THEN
...
END IF;
注意：在 PL/SQL,除了 SQL 语句和游标之外
不允许其他表达式直接引用列名
……………………………………………………
IF 条件 THEN
... ...
ELSE
... ...
END IF;
……………………………………………………
IF 条件 1 THEN
...
ELSIF 条件 2 THEN
...
ELSIF 条件 3 THEN
...
ELSIF 条件 n THEN
...
ELSE
END IF;
……………………………………………………
练习 1，编写一个函数，输入职工编号，返回该职工的新工资，假设，新的工资政策是 CLERK 增加
10%,MANAGER 增加 5%，其他人不变，但新的工资下限是 1000,工资低于 1000 的要涨到 1000。
CREATE OR REPLACE FUNCTION
F_GETNEWSAL(PNO EMP.EMPNO%TYPE) RETURN EMP.SAL%TYPE
IS
V_JOB EMP.JOB%TYPE;
V_SAL EMP.SAL%TYPE;
BEGIN
SELECT JOB,SAL INTO V_JOB,V_SAL FROM EMP
WHERE EMPNO=PNO;
IF V_JOB='CLERK' AND V_SAL*.1.1>1000 THEN
RETURN V_SAL*1.1;
ELSIF V_JOB='MANAGER' AND V_SAL*1.05>1000 THEN
RETURN V_SAL*1.05;
ELSIF V_SAL<1000 THEN
RETURN 1000;
ELSE
RETURN V_SAL;
END IF;
EXCEPTION WHEN NO_DATA_FOUND THEN
... ...
RAISE;
END;
……………………………………………………
CREATE OR REPLACE FUNCTION
F_GETNEWSAL(PNO EMP.EMPNO%TYPE) RETURN EMP.SAL%TYPE
IS
V_JOB EMP.JOB%TYPE;
V_SAL EMP.SAL%TYPE;
BEGIN
SELECT JOB,SAL INTO V_JOB,V_SAL FROM EMP
WHERE EMPNO=PNO;
V_SAL:=CASE WHEN V_JOB='CLERK' AND V_SAL*1.1>1000 THEN V_SAL*1.1
WHEN V_JOB='MANAGER' AND V_SAL*1.05>1000
THEN V_SAL*1.05
WHEN V_SAL<1000 THEN 1000
ELSE V_SAL
END;
EXCEPTION WHEN NO_DATA_FOUND THEN
... ...
RAISE;
END;
练习 2，编写一个函数，输入一个身份证号，返回该身份证号的合法性。
要求：长度必须是 15 位或 18 位，并且 15 位身份证的第 7 位开始的连续 6 位是 yymmdd 格式的出生日期 ，
18 位身份证的第 7 位开始的连续 8 位是 yyyymmdd 格式的出生日期，如果合法，返回字符串 OK, 如果
非法，返回原因。
CREATE OR REPLACE FUNCTION F_IDCHECK(PID VARCHAR2)
RETURN VARCHAR2
IS
V_LEN NUMBER:=LENGTH(PID);
BEGIN
IF V_LEN NOT IN (15,18) THEN
RETURN '长度错误';
END IF;
DECLARE
D DATE;
BEGIN
IF V_LEN = 15 THEN
D:=TO_DATE(SUBSTR(PID,7,6),'YYMMDD');
ELSE
D:=TO_DATE(SUBSTR(PID,7,8),'YYYYMMDD');
END IF;
EXCEPTION WHEN OTHERS THEN
RETURN '生日格式错误';
END;
RETURN 'OK';
END;
练习 2，编写一个函数，输入一个身份证号，返回该身份证号的合法性。
要求：长度必须是 15 位或 18 位，并且 15 位身份证的第 7 位开始的连续 6 位是 yymmdd 格式的出生日
期 ，18 位身份证的第 7 位开始的连续 8 位是 yyyymmdd 格式的出生日期，如果是 15 位身份证，则必须
全部由数字构成， 如果是 18 位身份证，则前 17 位必须是数字，最后一位必须是数字或者字符'X'，如果
合法，返回字符串 OK, 如果非法，返回原因。
CREATE OR REPLACE FUNCTION F_IDCHECK(PID VARCHAR2)
RETURN VARCHAR2
IS
V_LEN NUMBER:=LENGTH(PID);
BEGIN
相关推荐
2023年江西萍乡中考道德与法治真题及答案.doc
2012年重庆南川中考生物真题及答案.doc
2013年江西师范大学地理学综合及文艺理论基础考研真题.doc
2020年四川甘孜小升初语文真题及答案I卷.doc
2020年注册岩土工程师专业基础考试真题及答案.doc
2023-2024学年福建省厦门市九年级上学期数学月考试题及答案.doc
2021-2022学年辽宁省沈阳市大东区九年级上学期语文期末试题及答案.doc
2022-2023学年北京东城区初三第一学期物理期末试卷及答案.doc
2018上半年江西教师资格初中地理学科知识与教学能力真题及答案.doc
2012年河北国家公务员申论考试真题及答案-省级.doc
2020-2021学年江苏省扬州市江都区邵樊片九年级上学期数学第一次质量检测试题及答案.doc
2022下半年黑龙江教师资格证中学综合素质真题及答案.doc
