公司PL/SQL考核及小结
时间：2021-07-01 10:21:17
帮助过：5人阅读
-- 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;
View Code
二.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: ‘
