实验1-8 参考答案.pdf

实验 1 参考答案 1 ORACLE 表的 DDL 实践 设有如下关系表 S S SNO SNAME SSEX SAGE SDEPT 主关键字是 SNO 其中 SNO 为学号 NUMBER 5 学号为主码 SNAME 为姓名 姓名不能 为空 VARCHAR2 20 SSEX 为性别 CHAR 2 SAGE 为年龄 NUMBER 2 SDEPT 为所在系名 VARCHAR2 10 写出实现下列功能的 SQL 语句 CREATE TABLE S SNO NUMBER 5 PRIMARY KEY SNAME VARCHAR2 20 NOT NULL SSEX CHAR 2 SAGE NUMBER 2 SDEPT VARCHAR2 10 1 创建此表 插入以下数据并提交数据 INSERT INTO S VALUES 95001 李明勇 男 20 CS INSERT INTO S VALUES 95002 刘晨 女 19 IS INSERT INTO S VALUES 95003 王名 女 18 MA INSERT INTO S VALUES 95004 张立 男 19 CS INSERT INTO S VALUES 95005 张军 男 21 MA INSERT INTO S VALUES 95006 王张凤 女 19 FL INSERT INTO S VALUES 95011 王敬 女 18 IS INSERT INTO S VALUES 95021 张名惠 男 19 FL 2 查看此表的数据结构和数据 DESC S SELECT FROM S 3 根据此表再复制创建一个只有 IS 系学生组成的新表 S IS CREATE TABLE S IS AS SELECT FROM S WHERE SDEPT IS 4 向 S 表添加 入学时间 COMEDATE 列 其数据类型为日期型 DATE ALTER TABLE S ADD COMEDATE DATE 5 将年龄的数据类型改为 NUMBER 3 且默认值为 19 并查看这些修改后的 表结构 ALTER TABLE S MODIFY SAGE NUMBER 3 DEFAULT 19 6 删除新添加的列 ALTER TABLE S DROP COLUMN COMEDATE 查看更改情况 DESC S 7 将表 S 改名为 STUDENT RENAME S TO STUDENT 8 为表 STUDENT 添加注释 学生信息表 COMMENT ON TABLE STUDENT IS 学生信息表 9 截断 STUDENT 表 TRUNCATE TABLE STUDENT 10 删除生成的新表 S IS DROP TABLE S IS 2 创建约束 要求按普遍采用的约定为这些约束命名 约束创建后要进行约束 测试 参见所附的表结构和数据 1 为 COURSE 表添加一个主键约束 1 建表时创建约束 CREATE TABLE COURSE CNO NUMBER 4 CONSTRAINT PK COURSE CNO PRIMARY KEY CNAME VARCHAR2 30 CPNO NUMBER 4 CCREDIT NUMBER 2 CLIMIT NUMBER 4 2 或建表后添加约束 ALTER TABLE COURSE ADD CONSTRAINT PK COURSE CNO PRIMARY KEY CNO 2 创建 SC 表的主键约束和外键约束 一个列级和一个表级 表级的外键约束 要求允许使用级联删除选项 1 建表同时创建约束 CREATE TABLE SC SNO NUMBER 5 CONSTRAINT FK SC SNO FOREIGN KEY REFERENCES STUDENT SNO CNO NUMBER 4 GRADE NUMBER 3 1 CONSTRAINT PK SC SNOCNO PRIMARY KEY SNO CNO CONSTRAINT FK SC CNO FOREIGN KEY CNO REFERENCES COURSE CNO ON DELETE CASCADE 2 或建表后添加约束 创建 SC 表的主键约束 ALTER TABLE SC ADD CONSTRAINT PK SC SNOCNO PRIMARY KEY SNO CNO 用修改列定义的方式建立 SC 表的外键约束 ALTER TABLE SC MODIFY SNO NUMBER 5 CONSTRAINT FK SC SNO REFERENCES STUDENT SNO CNO NUMBER 4 CONSTRAINT FK SC CNO REFERENCES COURSE CNO ON DELETE CASCADE 用增加约束的方式创建 SC 表的外键约束 ALTER TABLE SC ADD CONSTRAINT FK SC SNO FOREIGN KEY SNO REFERENCES STUDENT SNO ALTER TABLE SC ADD CONSTRAINT FK SC CNO FOREIGN KEY CNO REFERENCES COURSE CNO ON DELETE CASCADE 3 为 STUDENT 表的 SSEX 创建 CHECK 约束 ALTER TABLE STUDENT ADD CONSTRAINT CK S SSEX CHECK SSEX IN 男 女 4 为 STUDENT 表创建一个合适的 NOT NULL 和 UNIQUE 惟一性约束 ALTER TABLE STUDENT MODIFY SNAME VARCHAR2 20 CONSTRAINT UK S SNAME UNIQUE SSEX CHAR 2 CONSTRAINT NN S SSEX NOT NULL 或 NOT NULL 约束 ALTER TABLE S MODIFY SSEX CHAR 2 NOT NULL UNIQUE 约束 ALTER TABLE S ADD CONSTRAINT S SNAME UK UNIQUE SNAME 5 在上题基础上 删除其中一个约束 启用 禁用其中一个约束 并测试之 删除约束 ALTER TABLE STUDENT DROP CONSTRAINT NN S SSEX 禁用约束 ALTER TABLE STUDENT DISABLE CONSTRAINT UK S SNAME 启用约束 ALTER TABLE STUDENT ENABLE CONSTRAINT UK S SNAME 3 使用数据字典视图 利用任意 3 种数据字典视图查看数据库对象的信息 如查看表 表空间信息和约 束信息等 查看当前用户拥有的表信息 SELECT FROM USER TABLES SELECT FROM TAB 查看 SC 表的约束 SELECT CONSTRAINT NAME CONSTRAINT TYPE SEARCH CONDITION FROM USER CONSTRAINTS WHERE TABLE NAME SC 查看 SC 表与约束与相关的列 SELECT CONSTRAINT NAME COLUMN NAME FROM USER CONS COLUMNS WHERETABLE NAME SC 实验 2 参考答案 1 略 2 略 3 显示雇员名称中包含 A 的雇员号 雇员名称及名称长度 SELECT EMPNO ENAME LENGTH EMPNAME FROM EMP WHERE ENAME LIKE A 4 将系统日期显示转换成中文的年月日 SELECT TO CHAR SYSDATE YYYY 年 MM 月 DD 日 系统日期 FROM DUAL 5 显示各雇员的姓名 部门号 工资及其预增工资 其中 10 号 20 号 30 号部门分别增 10 12 和 15 其余不变 要求使用 decode 函数 SELECT ENAME DEPTNO SAL DECODE DEPTNO 10 SAL 1 1 20 SAL 1 12 30 SAL 1 15 SAL PRE RAISE SAL FROM EMP 6 为了计算所有雇员一年的报酬 您需要 12 乘以月薪再加上之外的奖金 要求分别使用 NVL 函数 NVL2 函数和 COALESCE 函数实现 SELECT ENAME SAL COMM SAL 12 NVL COMM 0 年薪 FROM EMP SELECT ENAME SAL COMM NVL2 SAL TO CHAR SAL 12 NVL COMM 0 0 年薪 FROM EMP SELECT ENAME SAL COMM COALESCE SAL 12 NVL COMM 0 0 年薪 FROM EMP 7 查找出每个部门内的最低薪水 并且只显示最低薪水小于 1000 的部门 输出结果按最 低薪水升序排列 SELECT DEPTNO MIN SAL MINSAL FROM EMP GROUP BY DEPTNO HAVING MIN SAL 65000 ORDER BY SUMSAL 实验 3 参考答案 第一部分 1 查询各部门的人数 要求 1 DEPT 表中的所有部门都要进行统计 2 查询结果显示部 门名称和部门人数 SELECT DNAME COUNT EMPNO FROM DEPT LEFT OUTER JOIN EMP ON DEPT DEPTNO EMP DEPTNO GROUP BY DEPT DNAME 说明 要统计所有部门 所以要使用外连接 要显示部门名称 所以部门名要作为分组 依据 不能用 COUNT 这样外连接中不匹配的行也会计为一行 2 假设DEPT表中的DNAME字段没有唯一和非空限制的话 上题的SQL语句中group by 子句该怎么写才正确 为什么 DANME 没有唯一和非空限制 上面的语句会将所有同名部门作为一组统计 部门名为 空的也会作为一组统计 要区分名字相同或都为空的部门 GROUP BY 子名要加上部门 编号 SELECT DNAME COUNT EMPNO FROM DEPT LEFT OUTER JOIN EMP ON DEPT DEPTNO EMP DEPTNO GROUP BY DEPT DEPTNO DNAME 或 SELECT DNAME COUNT EMPNO FROM DEPT LEFT OUTER JOIN EMP ON DEPT DEPTNO EMP DEPTNO GROUP BY EMP DEPTNO DNAME 注 如果要显示部门编号 此方法不行 3 查询每一个雇员的薪水信息以及根据此薪水确定的薪水级别 薪水级别在 SALGRADE 表中 SELECT EMPNO ENAME SAL GRADE FROM EMP SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL 4 查询课程与其间接先行课的名称 间接先行课指的是先修课的先修课 SELECT FIRST CNAME 课程名 SECOND CNAME 直接先行课名 题目不要求显示直接先行课名 这里标出让大家注意区别 THIRD CNAME 间接先行课名 FROM COURSE FIRST COURSE SECOND COURSE THIRD WHERE FIRST CPNO SECOND CNO AND SECOND CPNO THIRD CNO 此方法只显示有间接先行课的课程 或 SELECT FIRST CNAME SECOND CNAME THIRD CNAME FROM COURSE FIRST LEFT OUTER JOIN COURSE SECOND ON FIRST CPNO SECOND CNO LEFT JOIN COURSE THIRD ON SECOND CPNO THIRD CNO 此方法可以把所有课程都列出 5 查询有选课的学生人数 SELECT COUNT DISTINCT SNO FROM SC 6 查询选修了两门课及两门以上课程的学生的学号 SELECT SNO FROM SC GROUP BY SNO HAVING COUNT CNO 2 7 查询每个学生选课的总学分 SELECT SNO SUM CCREDIT FROM SC COURSE WHERE SC CNO COURSE CNO GROUP BY SNO 8 查询每个学生的选课情况 学号 姓名 课程号 成绩 未选课的学生也要包含在结 果集中 SELECT STUDENT SNO SNAME SC CNO GRADE FROM STUDENT LEFT JOIN SC ON STUDENT SNO SC SNO 9 查询选修 3 号课程且成绩在 85 分以上的所有学生 SELECT STUDENT FROM STUDENT SC WHERE STUDENT SNO SC SNO AND SC CNO 3 AND SC GRADE 85 10 查询先行课的学分为 4 的课程信息 SELECT FIRST FROM COURSE FIRST COURSE SECOND WHERE FIRST CPNO SECOND CNO AND SECOND CCREDIT 4 第二部分 使用第二部分 使用 SQL 语句完成以下查询要求 语句完成以下查询要求 1 查询每一种零件被供应的次数 要求 1 结果显示零件号 零件名称和被供应的次数 2 零件表中的所有零件都要统计 SELECT P PNO PNAME COUNT SPJ PNO FROM P LEFT OUTER JOIN SPJ 必须使用外连接必须使用外连接 ON P PNO SPJ PNO GROUP BY P PNO PNAME 或或 SELECT P PNO PNAME COUNT SPJ PNO FROM P SPJ WHERE P PNO SPJ PNO GROUP BY P PNO PNAME 问题 1 GROUP BY P PNO 还是 SPJ PNO 答 答 P PNO 问题 2 GROUP BY P PNO 和 GROUP BY P PNO PNAME 结果是否一样 答 如果不显示零件名 结果一样 如果要显示零件名 则要答 如果不显示零件名 结果一样 如果要显示零件名 则要 GROUP BY P PNO PNAME 问题 3 COUNT SPJ PNO 还是 COUNT P PNO 答 答 COUNT SPJ PNO 问题 4 如果查询结果不要求显示零件号 只要求显示零件名称和数量 以下这个 SQL 语 句对吗 SELECT PNAME COUNT SPJ PNO FROM P SPJ WHERE P PNO SPJ PNO GROUP BY PNAME 答 如果零件名唯一且非空 则正确答 如果零件名唯一且非空 则正确 如果不唯一 有可能为空 则还是要用如果不唯一 有可能为空 则还是要用 GROUP BY P PNO PNAME 2 查询其他系中比计算机科学系所有学生年龄都小的学生完整信息 至少用两种方法实现 SELECT FROM STUDENT WHERE SAGE ALL SELECT SAGE FROM STUDENT WHERE SDEPT CS AND SDEPT CS SELECT FROM STUDENT WHERE SAGE SELECT MIN SAGE FROM STUDENT WHERE SDEPT CS AND SDEPT S2 SAGE AND S2 SDEPT CS AND SDEPT CS 3 从emp表中查找出和10部门中任意一个雇员的薪水和岗位完全相同的雇员 并显示其姓名 部门编号 薪水和佣金 但是该雇员不能来自10部门 SELECT ENAME DEPTNO SAL COMM FROM EMP WHERE SAL JOB IN SELECT SAL JOB FROM EMP WHERE DEPTNO 10 AND DEPTNO10 4 查询哪些项目使用哪些供应商供应的零件的总数量大于该供应商所供应零件的平均数量 查询结果要求显示项目号 供应商号和零件总数量 两种方法 from子句中的子查询和相 关的子查询 SELECT B JNO B SNO SUMQTY FROM SELECT SNO AVG QTY AVGQTY FROM SPJ GROUP BY SNO A 供应商供应零件的平均数供应商供应零件的平均数 SELECT SNO JNO SUM QTY SUMQTY FROM SPJ GROUP BY SNO JNO B 供应商供应给某项目的零件总数供应商供应给某项目的零件总数 WHERE A SNO B SNO AND SUMQTY AVGQTY SELECT A SNO A JNO A SUMQTY FROM SELECT SNO JNO SUM QTY SUMQTY FROM SPJ GROUP BY SNO JNO A WHERE A SUMQTY SELECT AVG QTY FROM SPJ WHERE A SNO SPJ SNO SELECT SNO JNO SUM QTY SUMQTY FROM SPJ A GROUP BY SNO JNO HAVING SUM QTY SELECT AVG QTY FROM SPJ B WHERE A SNO B SNO 5 查询工资第4高的员工的工号 姓名和薪水 假设SAL子段作了惟一限制 提示 使用from 子查询语句 内嵌视图和ROWNUM伪列 SELECT EMPNO ENAME SAL FROM SELECT EMPNO ENAME SAL FROM SELECT EMPNO ENAME SAL FROM EMP ORDER BY SAL DESC WHERE ROWNUM 4 ORDER BY SAL WHERE ROWNUM 1 SELECT EMPNO ENAME SAL FROM SELECT EMPNO ENAME SAL FROM EMP ORDER BY SAL DESC WHERE ROWNUM 4 MINUS SELECT EMPNO ENAME SAL FROM SELECT EMPNO ENAME SAL FROM EMP ORDER BY SAL DESC WHERE ROWNUM 3 第二部分的附加题部分 第二部分的附加题部分 1 查询 student 表中各系学生数占全校学生人数的百分比 2 查询工资低于本部门平均工资的员工信息 3 显示与 30 部门任何雇员薪水及岗位相匹配的雇员的姓名 部门编号 薪水及佣金 1 SELECT SDEPT AS 系系 S SUM S TOTAL 100 AS 百分比百分比 FROM SELECT SDEPT COUNT S SUM FROM STUDENT GROUP BY SDEPT A SELECT COUNT S TOTAL FROM STUDENT B 2 SELECT FROM EMP E1 WHERE SAL select avg sal from emp where deptno emp deptno 实验 5 参考答案 1 创建一个程序脚本 使用匿名块完成以下任务 用主机变量 AREA 存储结果 声明局部变量 RADIUS 为数字型 声明常量 PI 值为 3 14 使用替换变量 将一个值赋给变量 RADIUS 用下面公式计算圆面积 AREA PI RADIUS RADIUS 在 SQL PLUS 中显示 这一结果 SET SERVEROUTPUT ON VARIABLE AREA NUMBER DECLARE RADIUS NUMBER PI CONSTANT NUMBER 3 14 BEGIN RADIUS AREA PI RADIUS RADIUS DBMS OUTPUT PUT LINE 面积是 TO CHAR AREA END PRINT 面积是 TO CHAR AREA 2 编写一个匿名块 实现 通过替换变量 dno 输入一个部门编号 把该部门的平均工资赋给局部变量 v sal 用 DBMS OUTPUT 包输出该平均工资 SET SERVEROUTPUT ON DECLARE BH EMP DEPTNO TYPE V SAL NUMBER BEGIN BH SELECT AVG SAL INTO V SAL FROM EMP WHERE DEPTNO BH GROUP BY DEPTNO DBMS OUTPUT PUT LINE BH 号部门平均工资为 TO CHAR V SAL L9999 99 END 附加 1 通过 CASE 语句判断 7788 雇员的工资等级后输出 工资等级划分条件是 3000 含 以上为高 1500 含 至 3000 为中 低于 1500 为低 DECLARE V SAL EMP SAL TYPE 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 2 用 PL SQL 的 FOR 循环输出以下实心三角形 1 2 3 4 5 BEGIN FOR I IN 1 5 LOOP DBMS OUTPUT PUT RPAD TO CHAR I 10 I DBMS OUTPUT PUT LINE LPAD 2 I 1 END LOOP END BEGIN FOR I IN 1 5 LOOP DBMS OUTPUT PUT I FOR J IN 1 10 I LOOP DBMS OUTPUT PUT END LOOP FOR J IN 1 2 I 1 LOOP DBMS OUTPUT PUT END LOOP DBMS OUTPUT NEW LINE END LOOP END 3 先创建 d asc 表 再编写一个匿名 PL SQL 块 实现往此表插入 ASCII 码值从 32 至 126 的码号和相应码 值的字符 提示 用 CHR 函数 要求分别用两种循环方法实现 create table d asc n number c char BEGIN FOR I IN 32 126 LOOP INSERT INTO D ASC VALUES I CHR I END LOOP COMMIT END SELECT FROM D ASC DELETE D ASC COMMIT DECLARE I NUMBER 32 BEGIN WHILE I126 END LOOP COMMIT END 4 用 PL SQL 的循环语句编程 求 1 3 5 13 15 的值 DECLARE S NUMBER 0 I NUMBER 1 BEGIN WHILE I 15 LOOP S S I I I I 2 END LOOP DBMS OUTPUT PUT LINE S END 实验 6 参考答案 1 利用游标技术将 95001 学生选修的所有课程名称放进一个嵌套表变量中 然后遍历该嵌套表并 输出该学员选修的课程名称 执行结果形式如下 95001 选修的课程如下 数据库 数学 信息系统 数据结构 PL SQL 过程已成功完成 DECLARE TYPE STU TYPE IS TABLE OF COURSE CNAME TYPE STU STU TYPE CURSOR STUCUR IS SELECT COURSE CNAME FROM SC COURSE WHERE SNO 95001 AND SC CNO COURSE CNO I NUMBER 1 BEGIN STU STU TYPE FOR S IN STUCUR LOOP STU EXTEND STU I S CNAME I I 1 END LOOP DBMS OUTPUT PUT LINE 95001 选修的课程如下 FOR I IN STU FIRST STU LAST LOOP DBMS OUTPUT PUT LINE STU I END LOOP END 2 用带参数的显式游标技术将用户输入学号的学生选修的所有课程成绩放进一个 VARRAY 变量中 然 后遍历该 VARRAY 并输出该学员选修的课程成绩 执行结果形式如下 输入 sno 的值 95001 原值 10 v sno 新值 10 v sno 95001 95001 选修的课程成绩如下 58 85 70 92 0 PL SQL 过程已成功完成 DECLARE TYPE STU VAR TYPE IS VARRAY 10 OF SC GRADE TYPE STU VAR STU VAR TYPE I NUMBER 1 XH STUDENT SNO TYPE CURSOR STU CUR XH SC SNO TYPE IS SELECT GRADE FROM SC WHERE SNO XH BEGIN XH STU VAR STU VAR TYPE FOR S IN STU CUR XH LOOP STU VAR EXTEND STU VAR I S GRADE I I 1 END LOOP DBMS OUTPUT PUT LINE XH 选修的课程成绩如下 FOR I IN STU VAR FIRST STU VAR LAST LOOP DBMS OUTPUT PUT LINE STU VAR I END LOOP END 3 有这么一张表 t t 他只有一个 number 8 的字段 a 由于在创建表时忘记设置主键约束 导致表中 有很多重复的记录 请你编写一个程序 利用游标技术将表中重复的记录保留一个 删除其余的 create table t t a number 8 insert into t t values 1 insert into t t values 3 insert into t t values 6 insert into t t values 1 insert into t t values 6 insert into t t values 5 insert into t t values 3 insert into t t values 1 insert into t t values 1 DECLARE CURSOR TCUR IS SELECT DISTINCT A FROM T T V T TCUR ROWTYPE BEGIN OPEN TCUR DELETE FROM T T LOOP FETCH TCUR INTO V T EXIT WHEN TCUR NOTFOUND DBMS OUTPUT PUT LINE V T A INSERT INTO T T VALUES V T A END LOOP COMMIT CLOSE TCUR END 实验七实验七 参考答案参考答案 1 根据表 emp 的全部字段定义记录变量 emp record 用 SELECT 语句将编号为 7788 的雇员 的全部字段对应地存入该记录变量 最后输出记录变量的雇员名称字段 emp record ename 和雇 员工资字段的内容 执行结果如下 SCOTT 的工资为 3000 PL SQL 过程已成功完成 DECLARE TYPE EMP RECORD TYPE IS RECORD EMPNO EMP EMPNO TYPE ENAME EMP ENAME TYPE JOB EMP JOB TYPE MGR EMP MGR TYPE HIREDATE EMP HIREDATE TYPE SAL EMP SAL TYPE COMM EMP COMM TYPE DEPTNO EMP DEPTNO TYPE EMP RECORD EMP RECORD TYPE BEGIN SELECT INTO EMP RECORD FROM EMP WHERE EMPNO 7788 DBMS OUTPUT PUT LINE EMP RECORD ENAME 的工资为 EMP RECORD SAL END 2 声明一张 Index By 表 用来接收并存储 DEPT 表的信息 把部门号作为键 不要忘记部门 号是 10 的倍数 使用循环从 DEPT 表中将所有部门信息检索到 PL SQL 记录表中 然后用另一 个循环来显示表中的这些信息 执行结果为 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON PL SQL 过程已成功完成 DECLARE TYPE DEPT INDEXBYTABLE TYPE IS TABLE OF DEPT ROWTYPE INDEX BY BINARY INTEGER DEPT LIST DEPT INDEXBYTABLE TYPE V COUNT NUMBER BEGIN SELECT COUNT INTO V COUNT FROM DEPT FOR I IN 1 V COUNT LOOP SELECT INTO DEPT LIST I 10 FROM DEPT WHERE DEPTNO I 10 END LOOP FOR I IN 1 V COUNT LOOP DBMS OUTPUT PUT LINE DEPT LIST I 10 DEPTNO DEPT LIST i 10 DNAME DEPT LIST I 10 LOC END LOOP FOR I IN DEPT LIST FIRST DEPT LIST LAST LOOP IF DEPT LIST EXISTS I THEN DBMS OUTPUT PUT LINE DEPT LIST I DEPTNO DEPT LIST i DNAME DEPT LIST I LOC END IF END LOOP END 3 阅读以下程序 找出出错之处 说明出错原因 预测运行输出结果是什么 请删改错误 加上适当注释后 运行该程序 验证自己的预测是否正确 SET SERVEROUTPUT ON DECLARE TYPE dept list IS TABLE OF dept dname TYPE TYPE top5 list IS VARRAY 5 OF dept loc TYPE dis dept dept list num 5 top5 list BEGIN dis dept 1 AMGN 此句出错 dis dept 是嵌套表 必须先初始化 集合变量初始化的方法是通过调用集合的构造函数来实现 其构造函数的名称与集合对象名 非变量名 相同 初始化的方法有两种 一是在集合变量声明时初始化 dis dept dept list dept list AMGN BGEN 二是在声明后 在使用前进行初始化 dis dept dept list AMGN BGEN 本题的改正方法 把此句删除即可 IF dis dept IS NULL THEN dis dept dept list AMGN BGEN DBMS OUTPUT PUT LINE dis dept 表当前元素个数为 to char dis dept count END IF num 5 top5 list ORCL CSCO NULL NULL IF num 5 3 IS NULL THEN num 5 3 CPQ END IF num 5 4 DELL FOR COUNTER IN 1 4 LOOP DBMS OUTPUT PUT LINE NUM 5 COUNTER END LOOP END 运行结果为 dis dept表当前元素个数为 2 ORCL CSCO CPQ DELL 4 将上题改为使用 INDEX BY BINARY INTEGER 的索引表 则实现同一执行结果 程序要 做哪些相应的改变 蓝色字体为修改的内容蓝色字体为修改的内容 SET SERVEROUTPUT ON DECLARE TYPE dept list IS TABLE OF dept dname TYPE INDEX BY BINARY INTEGER TYPE top5 list IS VARRAY 5 OF dept loc TYPE dis dept dept list num 5 top5 list BEGIN dis dept 1 AMGN IF NOT dis dept EXISTS 2 THEN dis dept 2 BGEN DBMS OUTPUT PUT LINE dis dept 表当前元素个数为 to char dis dept count END IF num 5 top5 list ORCL CSCO NULL NULL IF num 5 3 IS NULL THEN num 5 3 CPQ END IF num 5 4 DELL FOR COUNTER IN 1 4 LOOP DBMS OUTPUT PUT LINE NUM 5 COUNTER END LOOP END 5 参照教材和课件中的相关例子参照教材和课件中的相关例子 自选实例使用复合数据的各种内置方法自选实例使用复合数据的各种内置方法 略 实验实验 8 参考答案参考答案 1 创建一个显示 EMP 表中雇员总人数的存储过程 然后在程序块中和 SQL Plus 环境中调用该存储过 程 执行结果形式如下 雇员总人数为 17 CREATE OR REPLACE PROCEDURE EMP COUNT IS V SUM NUMBER BEGIN SELECT COUNT INTO V SUM FROM EMP DBMS OUTPUT PUT LINE 雇员总人数为 V SUM END EMP COUNT 在匿名块中调用 BEGIN EMP COUNT END 在 SQL PLUS 环境中调用 SET SERVEROUTPUT ON EXEC EMP COUNT 2 编写一个匿名 PL SQL 程序块 调用局部 本地 函数 输入参数是部门号 查询并显示 10 号部门 的人数 要求函数体内有对不存在部门号的异常处理 给用户的错误指示信息是 不存在部门号为 的部门 执行结果形式如下 10 号部门的人数为 5 DECLARE FUNCTION COUNT DEPT PDEPTNO DEPT DEPTNO TYPE RETURN NUMBER IS E NULL EXCEPTION V SUM NUMBER BEGIN SELECT COUNT INTO V SUM FROM DEPT WHERE DEPTNO PDEPTNO IF V SUM 0 THEN RAISE E NULL END IF SELECT COUNT INTO V SUM FROM EMP WHERE DEPTNO PDEPTNO RETURN V SUM EXCEPTION WHEN E NULL THEN RAISE APPLICATION ERROR 20001 不存在部门号为 PDEPTNO 的部门 WHEN OTHERS THEN RAISE APPLICATION ERROR 200002 其他类型错误 END BEGIN DBMS OUTPUT PUT LINE 10号部门的人数是 count dept 10 人 END 3 将上题的局部 本地 函数改写为存储函数 调用存储函数 查询并显示 30 号部门的人数 要求 函数体内有对不存在部门的异常处理 给用户的错误指示信息是 该部门不存在 CREATE OR REPLACE FUNCTION COUNT DEPT PDEPTNO DEPT DEPTNO TYPE RETURN NUMBER IS E NULL EXCEPTION V SUM NUMBER BEGIN SELECT COUNT INTO V SUM FROM DEPT WHERE DEPTNO PDEPTNO IF V SUM 0 THEN RAISE E NULL END IF SELECT COUNT INTO V SUM FROM EMP WHERE DEPTNO PDEPTNO RETURN V SUM EXCEPTION WHEN E NULL THEN RAISE APPLICATION ERROR 20001 不存在部门号为 PDEPTNO 的部门 WHEN OTHERS THEN RAISE APPLICATION ERROR 200002 其他类型错误 END 函数已创建 调用函数 BEGIN DBMS OUTPUT PUT LINE 10号部门的人数是 count dept 30 人 END 4 有一个权限控制的表中有三个字段 分别存放用户姓名 密码和权限级别 创建一个用户登录存储 函数 用户登录时核对用户名和密码 正确则函数返回真 否则返回假 要求有适当的异常处理 还要求进行适当