公司PL/SQL考核及小结
一.数据库初始化脚本
1 -- Create table 学生信息 2 drop table HAND_STUDENT; 3 create table HAND_STUDENT 4 ( 5 STUDENT_NO VARCHAR2(10) not null, 6 STUDENT_NAME VARCHAR2(20), 7 STUDENT_AGE NUMBER(2), 8 STUDENT_GENDER VARCHAR2(5) 9 ); 10 -- Add comments to the table 11 comment on table HAND_STUDENT 12 is '学生信息表'; 13 -- Add comments to the columns 14 comment on column HAND_STUDENT.STUDENT_NO 15 is '学号'; 16 comment on column HAND_STUDENT.STUDENT_NAME 17 is '姓名'; 18 comment on column HAND_STUDENT.STUDENT_AGE 19 is '年龄'; 20 comment on column HAND_STUDENT.STUDENT_GENDER 21 is '性别'; 22 -- Create/Recreate primary, unique and foreign key constraints 23 alter table HAND_STUDENT add primary key (STUDENT_NO); 24 25 -- Create table 教师信息表 26 drop table HAND_TEACHER; 27 create table HAND_TEACHER 28 ( 29 TEACHER_NO VARCHAR2(10) not null, 30 TEACHER_NAME VARCHAR2(20), 31 MANAGER_NO VARCHAR2(10) 32 ); 33 -- Add comments to the table 34 comment on table HAND_TEACHER 35 is '教师信息表'; 36 -- Add comments to the columns 37 comment on column HAND_TEACHER.TEACHER_NO 38 is '教师编号'; 39 comment on column HAND_TEACHER.TEACHER_NAME 40 is '教师名称'; 41 comment on column HAND_TEACHER.MANAGER_NO 42 is '上级编号'; 43 -- Create/Recreate primary, unique and foreign key constraints 44 alter table HAND_TEACHER add primary key (TEACHER_NO); 45 46 -- Create table 课程信息表 47 drop table HAND_COURSE; 48 create table HAND_COURSE 49 ( 50 COURSE_NO VARCHAR2(10) not null, 51 COURSE_NAME VARCHAR2(20), 52 TEACHER_NO VARCHAR2(20) not null 53 ); 54 -- Add comments to the table 55 comment on table HAND_COURSE 56 is '课程信息表'; 57 -- Add comments to the columns 58 comment on column HAND_COURSE.COURSE_NO 59 is '课程号'; 60 comment on column HAND_COURSE.COURSE_NAME 61 is '课程名称'; 62 comment on column HAND_COURSE.TEACHER_NO 63 is '教师编号'; 64 -- Create/Recreate primary, unique and foreign key constraints 65 alter table HAND_COURSE add constraint PK_COURSE primary key (COURSE_NO, TEACHER_NO); 66 67 -- Create table 成绩信息表 68 drop table HAND_STUDENT_CORE; 69 create table HAND_STUDENT_CORE 70 ( 71 STUDENT_NO VARCHAR2(10) not null, 72 COURSE_NO VARCHAR2(10) not null, 73 CORE NUMBER(4,2) 74 ); 75 -- Add comments to the table 76 comment on table HAND_STUDENT_CORE 77 is '学生成绩表'; 78 -- Add comments to the columns 79 comment on column HAND_STUDENT_CORE.STUDENT_NO 80 is '学号'; 81 comment on column HAND_STUDENT_CORE.COURSE_NO 82 is '课程号'; 83 comment on column HAND_STUDENT_CORE.CORE 84 is '分数'; 85 -- Create/Recreate primary, unique and foreign key constraints 86 alter table HAND_STUDENT_CORE add constraint PK_SC primary key (STUDENT_NO, COURSE_NO); 87 88 -- Create table 学生历史信息表 89 drop table HAND_STUDENT_HIS; 90 create table HAND_STUDENT_HIS 91 ( 92 STUDENT_NO VARCHAR2(10) not null, 93 STUDENT_NAME VARCHAR2(20), 94 STUDENT_AGE NUMBER(2), 95 STUDENT_GENDER VARCHAR2(5), 96 LAST_UPDATE_DATE DATE, 97 STATUS VARCHAR2(5) 98 ); 99 -- Add comments to the table 100 comment on table HAND_STUDENT_HIS 101 is '学生信息历史表'; 102 -- Add comments to the columns 103 comment on column HAND_STUDENT_HIS.STUDENT_NO 104 is '学号'; 105 comment on column HAND_STUDENT_HIS.STUDENT_NAME 106 is '姓名'; 107 comment on column HAND_STUDENT_HIS.STUDENT_AGE 108 is '年龄'; 109 comment on column HAND_STUDENT_HIS.STUDENT_GENDER 110 is '性别'; 111 comment on column HAND_STUDENT_HIS.LAST_UPDATE_DATE 112 is '最后更新时间'; 113 comment on column HAND_STUDENT_HIS.STATUS 114 is '状态'; 115 116 -- Create table 日志信息表 117 drop table HAND_LOG; 118 create table HAND_LOG 119 ( 120 CODE VARCHAR2(240) not null, 121 MSG VARCHAR2(240) not null, 122 KEY1 VARCHAR2(240), 123 KEY2 VARCHAR2(240), 124 KEY3 VARCHAR2(240), 125 KEY4 VARCHAR2(240), 126 KEY5 VARCHAR2(240) 127 ); 128 -- Add comments to the table 129 comment on table HAND_LOG 130 is '日志信息表'; 131 -- Add comments to the columns 132 comment on column HAND_LOG.CODE 133 is '编号'; 134 comment on column HAND_LOG.MSG 135 is '消息'; 136 comment on column HAND_LOG.KEY1 137 is '关键字1'; 138 comment on column HAND_LOG.KEY2 139 is '关键字2'; 140 comment on column HAND_LOG.KEY3 141 is '关键字3'; 142 comment on column HAND_LOG.KEY4 143 is '关键字4'; 144 comment on column HAND_LOG.KEY5 145 is '关键字5'; 146 147 /*******初始化学生表的数据******/ 148 insert into HAND_STUDENT values ('s001','张三',23,'男'); 149 insert into HAND_STUDENT values ('s002','李四',23,'男'); 150 insert into HAND_STUDENT values ('s003','吴鹏',25,'男'); 151 insert into HAND_STUDENT values ('s004','琴沁',20,'女'); 152 insert into HAND_STUDENT values ('s005','王丽',20,'女'); 153 insert into HAND_STUDENT values ('s006','李波',21,'男'); 154 insert into HAND_STUDENT values ('s007','刘玉',21,'男'); 155 insert into HAND_STUDENT values ('s008','萧蓉',21,'女'); 156 insert into HAND_STUDENT values ('s009','陈萧晓',23,'女'); 157 insert into HAND_STUDENT values ('s010','陈美',22,'女'); 158 commit; 159 /******************初始化教师表***********************/ 160 insert into HAND_TEACHER values ('t001', '刘阳',''); 161 insert into HAND_TEACHER values ('t002', '谌燕','t001'); 162 insert into HAND_TEACHER values ('t003', '胡明星','t002'); 163 commit; 164 /***************初始化课程表****************************/ 165 insert into HAND_COURSE values ('c001','J2SE','t002'); 166 insert into HAND_COURSE values ('c002','Java Web','t002'); 167 insert into HAND_COURSE values ('c003','SSH','t001'); 168 insert into HAND_COURSE values ('c004','Oracle','t001'); 169 insert into HAND_COURSE values ('c005','SQL SERVER 2005','t003'); 170 insert into HAND_COURSE values ('c006','C#','t003'); 171 insert into HAND_COURSE values ('c007','JavaScript','t002'); 172 insert into HAND_COURSE values ('c008','DIV+CSS','t001'); 173 insert into HAND_COURSE values ('c009','PHP','t003'); 174 insert into HAND_COURSE values ('c010','EJB3.0','t002'); 175 insert into HAND_COURSE values ('c011','PHP','t004'); 176 commit; 177 /***************初始化成绩表***********************/ 178 insert into HAND_STUDENT_CORE values ('s001','c001',58.9); 179 insert into HAND_STUDENT_CORE values ('s002','c001',80.9); 180 insert into HAND_STUDENT_CORE values ('s003','c001',81.9); 181 insert into HAND_STUDENT_CORE values ('s004','c001',60.9); 182 insert into HAND_STUDENT_CORE values ('s001','c002',82.9); 183 insert into HAND_STUDENT_CORE values ('s002','c002',72.9); 184 insert into HAND_STUDENT_CORE values ('s003','c002',81.9); 185 insert into HAND_STUDENT_CORE values ('s001','c003','59'); 186 insert into HAND_STUDENT_CORE values ('s003','c011',81.9); 187 insert into HAND_STUDENT_CORE values ('s001','c011','59'); 188 commit;
二.PL/SQL练习题
1.创建一个 package，命名为 cux_exam_工号_pkg，包含以下内容：
1.1声明一个 recoder 数据类型命名为 type_studens_rec，包含以下字段：
1.2 基于上面的记录声明一个内存表类型，命名为 type_studens_tbl。
1.3创建一个方法，返回类型为 type_studens_tbl，取出分数低于课程平均分 的学生信息：学号、姓名、课程编号、课程名、教师、分数、课程平均分、 课程最高分、最低分。
1.4创建一个过程，调用上面的方法，输出这些学生中，给所有成绩低于平均分 的学生加 1 分（更新成绩表），如果加分后的学生成绩低于 60 分，则输出这 个学生的信息。处理成功则返回 S 状态并提交事物；失败则返回 E 状态及 回错误消息，同时回滚事物。
1.5创建一个方法，根据参数的教师编号，返回这个教师的直接领导的教师编 号。如果不存在，返回-1， 如果存在多条记录，返回-2，如果存在其他异 常，返回-3。
1.6创建一个公共方法，根据参数 课程编号，返回该课程学生的及格率，返回 类型为数字，保留两位小数（及格率=课程成绩在 60 分以上的同学人数/课 程总人数）。
1.7写一个匿名块，创建一张临时表，hand_teacher_temp（动态 SQL）, 结构 与 hand_teacher 相同。取所有课程及格率高于 50%的教师信息，插入到hand_teacher_temp 表中。（使用前面程序中求及格率的函数）。
1.8 写一个匿名块，更新学生所选课程的成绩。如果存在课程成绩在 60 分以 上，则对原成绩降分 2%，输出学生编号，课程编号以及更新后的成绩（用 returning into ）。
题解：
包头(1.1~1.6)：
create or replace package cux_exam_xxxx_pkg is -- 1. 定义数据类型 type score_data_type is record( student_no hand_student.student_no%type, student_name hand_student.student_name%type, student_age hand_student.student_age%type, student_gender hand_student.student_gender%type, course_no hand_course.course_no%type, course_name hand_course.course_name%type, teacher_name hand_teacher.teacher_name%type, core hand_student_core.core%type, avg_core hand_student_core.core%type, max_core hand_student_core.core%type, min_core hand_student_core.core%type); -- 2. 定义内存表类型 type_studens_tbl TYPE type_studens_tbl IS TABLE OF studens_rec%ROWTYPE INDEX BY BINARY_INTEGER; -- 3. 获得低于平均分数的学⽣生信息 function get_grounded_info return type_studens_tbl; -- 4. 分数调整 procedure score_adjustment(p_std_score in number, x_return_status out nocopy varchar2, x_msg_data out nocopy varchar2); -- 5. 查询教师的直接领导 function get_manager(p_teacher_no in varchar2) return varchar2 ; -- 6. 查询课程的及格率 function get_course_pass_rate(p_course_no in varchar2) return number ; end cux_exam_xxxx_pkg;
包体(1.1~1.6)：
1 create or replace package body cux_exam_xxxx_pkg is 2 -- 3. 获得低于平均分数的学⽣生信息 3 function get_grounded_info return type_studens_tbl is 4 l_studens_tbl type_studens_tbl; 5 begin 6 -- 计算每⻔门课程的平均分，并取出所有成绩低于平均分的学⽣生 7 select * 8 from (select hs.student_no, 9 hs.student_name, 10 hs.student_age, 11 hs.student_gender, 12 hc.course_no, 13 hc.course_name, 14 ht.teacher_name, 15 hsc.core, 16 round(avg(hsc.core) over(partition by 17 hsc.course_no), 2) avg_core, 18 max(hsc.core) over(partition by hsc.course_no) 19 max_core, 20 min(hsc.core) over(partition by hsc.course_no) 21 min_core 22 from hand_course hc, 23 hand_student hs, 24 hand_student_core hsc, 25 hand_teacher ht 26 where hc.course_no = hsc.course_no 27 and hs.student_no = hsc.student_no 28 and hc.teacher_no = ht.teacher_no) 29 BULK COLLECT into l_studens_tbl 30 where core < avg_core; 31 return l_studens_tbl; 32 end get_grounded_info; 33 -- 4. 分数调整 34 procedure score_adjustment(p_std_score in number, 35 x_return_status out nocopy varchar2, 36 x_msg_data out nocopy varchar2) is 37 l_data_cur ref_cursor; 38 l_data_rec score_data_type; 39 l_new_score number; 40 l_savepoint_name varchar2(30) := 'sp_score_adjustment'; 41 begin 42 x_return_status := 'S'; 43 x_msg_data := null; 44 savepoint l_savepoint_name; 45 l_data_cur := get_grounded_info; 46 loop 47 fetch l_data_cur 48 into l_data_rec; 49 exit when l_data_cur%notfound; 50 update hand_student_core hsc 51 set hsc.core = nvl(hsc.core, 0) + 1 52 where hsc.student_no = l_data_rec.student_no 53 and hsc.course_no = l_data_rec.course_no 54 returning hsc.core into l_new_score; 55 if l_new_score < nvl(p_std_score, 60) then 56 dbms_output.put_line('Student No.: ' || 57 l_data_rec.student_no || 58 ', Student Name: ' || 59 l_data_rec.student_name || 60 ', Course Name: ' || 61 l_data_rec.course_name || 62 ', Teacher Name: ' || 63 l_data_rec.teacher_name || 64 ', Score: ' || l_data_rec.core || 65 ', Avg score: ' || l_data_rec.avg_core 66 || 67 ', Max score: ' || l_data_rec.max_core 68 || 69 ', Min score: ' || 70 l_data_rec.min_core); 71 end if; 72 end loop; 73 commit; 74 exception 75 when others then 76 rollback to l_savepoint_name; 77 x_return_status := 'E'; 78 x_msg_data := substr(sqlerrm, 1, 2000); 79 end score_adjustment; 80 -- 5. 查询教师的直接领导 81 function get_manager(p_teacher_no in varchar2) return varchar2 is 82 l_teacher_no hand_teacher.teacher_no%type; 83 begin 84 select ht.teacher_no 85 into l_teacher_no 86 from hand_teacher ht 87 where level = 2 88 connect by ht.teacher_no = prior ht.manager_no 89 start with ht.teacher_no = p_teacher_no; 90 return l_teacher_no; 91 exception 92 when no_data_found then 93 return '-1'; 94 when too_many_rows then 95 return '-2'; 96 when others then 97 return '-3'; 98 end get_manager; 99 -- 6. 查询课程及格率 100 function get_course_pass_rate(p_course_no in varchar2) return 101 number is 102 l_passed_count number; 103 l_total_count number; 104 l_course_pass_rate number; 105 begin 106 select sum(case 107 when hsc.core > 60 108 then 1 109 else 110 0 111 end) passed_count, count(1) total_count 112 Into l_passed_count, l_total_count 113 from hand_student_core hsc 114 where hsc.course_no = p_course_no; 115 l_course_pass_rate := round(l_passed_count/l_total_count,2); 116 return l_course_pass_rate; 117 exception 118 when others then 119 return null; 120 end get_course_pass_rate; 121 end cux_exam_XXXX_pkg;
1.7:
-- 7.取所有课程及格率⾼高于50%的教师信息，插⼊入到 hand_teacher_temp 表中。 declare l_count number; begin select count(1) into l_count from all_tables t where t.TABLE_NAME = 'HAND_TEACHER_TEMP'; if l_count > 0 then execute immediate 'DROP TABLE HAND_TEACHER_TEMP'; end if; execute immediate 'CREATE TABLE HAND_TEACHER_TEMP AS ' || insert hand_teacher_temp select * from hand_teacher ht where not exists( select 1 from hand_course hc where cux_exam_xxxx_pkg.get_grounded_info(hc.course_no) <0.5 and hc.teacher_no = ht.teacher_no)'; exception when others then dbms_output.put_line(sqlcode || '-' || sqlerrm); end;
1.8:
-- 8. 写⼀一个匿匿名块，更更新学⽣生所选课程的成绩。如果存在课程成绩在 60 分以 上，则对 原成绩降分 2%，输出学⽣生编号，课程编号以及更更新后的成绩(⽤用 returning into )(10 分) Declare TYPE type_student_core_tb IS TABLE OF hand_student_core%ROWTYPE; l_student_core_tb type_student_core_tb; begin Update hand_student_core t set t.core = t.core * 0.98 Where t.core >60 RETURNING student_no ,course_no, core BULK COLLECT INTO l_student_core_tb; FOR i IN l_student_core_tb.FIRST .. l_student_core_tb.LAST LOOP DBMS_OUTPUT.PUT_LINE( l_student_core_tb(i). student_no || ' '|| l_student_core_tb(i). course_no || ' '|| l_student_core_tb(i). core ); END LOOP; commit; exception when others then rollback; dbms_output.put_line(sqlcode || '-' || sqlerrm); end;
小结：
注函数和存储过程的区别：函数有返回值，存储过程没有返回值，函数参数只能是INT类型（传入参数），存储过程可以是INT，OUT，IN OUT；
大多数SQL函数能在PL/SQL中过程语句中使用，如单行的数值和字符串函数、数据类型转换函数、日期函数、时间函数、求最大、最小值的 GREATEST, LEAST 函数等，但是有些函数不能再PL/SQL中使用，如Decode函数、分组函数（AVG, MIN, MAX, COUNT, SUM, STDDEV, and VARIANCE）等；
PLSQL的块是可以嵌套的，变量的作用范围与其他语言类似 ，如果同名，想要访问外层的需要使用outer关键字；
PLSQL中常用的自定义类型就两种： 记录类型、PLSQL内存表类型（根据表中的数据字段的简单和复杂 程度又可分别实现类似于简单数组和记录数组的功能）；
意外处理等，内置异常，以及raise_application_error()函数等注意。。。
2.(1) 创建一个历史表 hand_teacher_his, 其中包括 hand_teacher 表的所 有字段, 新增 last_update_date（处理日期）以及 operation（操作类 型）两列 。
(2) 在 hand_teacher 表上面创建一个触发器，记录表的新增,更新(记录更 新前的数据)和删除（记录删除前的数据）操作，并记录操作类型和处理日期。
题解：
（1）：
-- 2-1. 创建⼀一个 hand_teacher_his 历史表 create table hand_teacher_his as select ht.*, sysdate last_update_date, 'U' operation from hand_teacher ht where 1=-1;
（2）：
-- 2-2. 在 hand_teacher 表上新建⼀一个触发器器 create or replace trigger hand_teacher_tgr1 after insert or update or delete on hand_teacher for each row declare begin if inserting then insert into hand_teacher_his values(:new.teacher_no, :new.teacher_name, :new.manager_no, sysdate, 'I'); elsif updating then insert into hand_teacher_his values(:old.teacher_no, :old.teacher_name, :old.manager_no, sysdate, 'U'); elsif deleting then insert into hand_teacher_his values(:old.teacher_no, :old.teacher_name, :old.manager_no, sysdate, 'D'); end if; exception when others then raise_application_error(-20005, sqlcode ||'-' ||sqlerrm ); end;
小结：
触发器注意：时机：Before 或者 After 或 Instead of 事件：Insert 或 Update 或 Delete 对象：表名（或视图名） 类型：Row 或者 Statement级； 条件：满足特定Where条件才执行； 内容：通常是一段PLSQL块代码；
重点注意： Instead of : 用Trigger的内容替换 事件本身的动作 Row级：SQL语句影响到的每一行都会引发Trigger Statement级：一句SQL语句引发一次，不管它影响多少行（甚至0行）。
3. 写一个匿名块，在 system(system/manager)用户下执行。 新建一个 directory : HAND_DIR_TEMP_XXXX , 物理地址为虚拟机的 c:\temp 目录, 并授权 读写 权限给 student1 用户，然后将 hand_exam_data.txt 这个 文件放到 c:\temp 目录下。(xxxx 为自己的工号)：
-- 3. 使⽤用system ⽤用户新建⼀一个 directory begin execute immediate 'create or replace directory HAND_DIR_TEMP as ''c:\temp'''; execute immediate 'grant read ,write on directory HAND_DIR_TEMP to student1'; end;
小结：
Oracle Directory是为了便于控制Bfile存储的安全性；
在Oracle 内部创建的 Directory 默认的所有者是sys, 并有DBA( 或者是另一个拥有 CREATE ANY DIRECTORY 权限的用户)创建；Directory 对象可以像表那样给其他用户赋权。
4.从 hand_exam_data.txt 文件读取内容并导入到 hand_teacher 表。要求： (1) 从数据文件中读取数据，并按照逗号分隔符进行拆分，提取字段的值。 (2) 去掉数据中的双引号 (3) 将处理后的数据，插入到 hand_teacher 表 ：
1 -- 3. 从hand_exam_data.txt⽂文件读取内容并导⼊入到 hand_teacher 表 2 declare 3 l_line VARCHAR2(1023); 4 fid UTL_FILE.FILE_TYPE; 5 l_data_rec hand_teacher%rowtype; 6 procedure split_columns(p_data_str in varchar2, 7 x_data_rec out hand_teacher%rowtype) is 8 l_substr varchar2(2000); 9 l_instr number; 10 begin 11 if p_data_str is null then 12 return; 13 end if; 14 l_substr := p_data_str; 15 l_instr := instr(l_substr, ','); 16 x_data_rec.teacher_no := trim(replace(substr(l_substr, 1, 17 l_instr - 1),'"','')); 18 -- 提取teacher_name 19 l_substr := substr(l_substr, l_instr + 1); 20 l_instr := instr(l_substr, ','); 21 x_data_rec.teacher_name := trim(replace(substr(l_substr, 1, 22 l_instr - 1),'"','')); 23 -- 提取manager_no 24 l_substr := substr(l_substr, l_instr + 1); 25 l_instr := instr(l_substr, ','); 26 x_data_rec.manager_no := trim(replace(l_substr,'"','')); 27 end; 28 BEGIN 29 BEGIN 30 fid := UTL_FILE.FOPEN('HAND_DIR_TEMP', 'hand_exam_data.txt', 31 'R'); 32 loop 33 UTL_FILE.GET_LINE(fid, l_line); 34 dbms_output.put_line(l_line); 35 split_columns(l_line, l_data_rec); 36 if l_data_rec.teacher_no is not null then 37 insert into hand_teacher values l_data_rec; 38 end if; 39 end loop; 40 utl_file.fclose(fid); 41 EXCEPTION 42 WHEN NO_DATA_FOUND THEN 43 if utl_file.is_open(fid) then 44 utl_file.fclose(fid); 45 end if; 46 END; 47 commit; 48 EXCEPTION 49 WHEN others THEN 50 rollback; 51 dbms_output.put_line(sqlcode || '-' || sqlerrm); 52 END;
小结：
这里注意打开文件函数 UTL_FILE.FOPEN，读取内容函数 UTL_FILE.GET_LINE，以及何时不在读取（为null），以及异常处理，内容格式处理。
