【无标题】

最新推荐文章于 2024-05-30 14:52:09 发布
最新推荐文章于 2024-05-30 14:52:09 发布
阅读量2.5k 收藏 4
点赞数
文章标签： dba sql oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_48253097/article/details/122309057
版权

–一：创建数据环境
–1.用户及权限
–1.1 创建用户utest,密码为utest1。
create user utest identified by utest1;
–1.2 为utest用户赋予connect权和dba权。
grant connect to utest;
grant dba to utest;
–1.3 收回utest用户dba权限。
revoke dba from utset;
–1.4 使用utest用户登录。（使用connect命令）。
connect utest/utest1

–2.创建数据环境
–2.1 使用SQL语句中的 create table 语句。
create table Student(sno char(10) primary key,
sname varchar(20),
ssex char(2),
sage smallint,
sdept varchar(20));
–2.2 SQL语句中关键词大小写不区分。
create table Course(cno char(10) primary key,
cname varchar(20),
cpno char(10),
ccredit smallint);
–2.3 在每一句SQL语句书写结束时，请务必要使用分号作为结束运行符，否则语句将不会运行。若书写多句话中间不用分号分隔，则会出现语法错误。
create table SC(sno char(10),
cno char(10),
grade smallint,
primary key(sno,cno));

–3.修改表结构
–3.1 为course表添加一列snumber(最大选课人数)，类型为char(10)。
alter table Course add snumber char(10);
–3.2修改course表中snumber列，将其类型改为int
alter table Course modify snumber int;
–3.3 删除course表中的snumber列。
alter table Course drop column snumber;

–4.删除表
–4.1 删除student，course和sc表，其中sc表建立了外码（请注意删除顺序）。
drop table SC;
drop table Student;
drop table Course;

– 1.1向Course表中插入一行，数据为：
cno cname cpno ccredit snumber
C50 Oracle 4 100

insert into Course(cno,cname,ccredit,snumber)
values(‘C50’,‘Oracle’,4,100);
commit;

–1.2.使用Insert语句为CS系学生学生选择必修课C50。
insert into SC(sno,cno)
select sno,‘C50’ from Student
where sdept=‘CS’ and not exists(select* from SC where sno=Student.sno and cno=‘C50’);
commit;
–1.3.将Oracle课程的先行课修改为“DB”课程的课程号（使用子查询完成）
update Course set cpno=(select cno from Course where cname=‘DB’)
where cname=‘Oracle’;
commit;
–1.4将CS系“DB”课程成绩为空的学生选课信息删除。
delete from SC where sno in(select sno from Student where sdept=‘CS’) and cno in
(select cno from Course where cname=‘DB’) and grade is null;
commit;

–2.1.查询CS系年龄大于20岁的学生，列出学号，姓名，性别。
select sno,sname,ssex from Student
where sdept=‘CS’ and sage>20;
–2.2.查询选了’DB’课程的学生的学号。
select sno from SC
where cno in(select cno from Course where cname=‘DB’);
–2.3.查询CS系没有选’DB’课程的学生的学号，姓名，系。
select sno,sname,sdept
from Student
where not exists(select* from SC where sno=Student.sno
and cno in(select cno from Course where cname=‘DB’))
and sdept=‘CS’;
–2.4.查询男（‘m’）同学选择了’DB’课程，但是没有选’Oracle’课程的学生，列出学号，姓名。(课程名要区分大小写)
select sno,sname from Student
where ssex=‘m’ and sno in(select sno from SC where cno in(select cno from Course where cname=‘DB’)) and sno not in(select sno from SC where cno in(select cno from Course where cname=‘Oracle’));

3.DML之查询统计
–3.1.查询每个学生的平均分，列出学生的学号，平均分（列名为savg）,并按平均分降序排列。
select sno,avg(grade) as savg
from SC
group by sno
order by savg desc;
–3.2.查询选课人数大于等于3人的课程，列出课程号，课程名，选课人数（列名为scnt）,并按课程号升序排列。
select SC.cno,cname,count(sno) as scnt
from Course,SC
where Course.cno=SC.cno
group by SC.cno,cname
having count(sno)>=3
order by SC.cno asc;
–3.3.查询选课人数最多的课程。列出课程号，课程名。
select Course.cno,cname
from Course,SC
where Course.cno=SC.cno
group by Course.cno,cname
having count(sno)>=all(select count(sno) from SC group by SC.cno);
–3.4.查询CS系选课人数最多的课程。列出课程号，课程名，CS系的选课人数（列名为CScnt）。
select SC.cno,cname,count(Student.sno)as CScnt
from Course,SC,Student
where SC.sno=Student.sno and SC.cno=Course.cno and sdept=‘CS’
group by SC.cno,cname
having count(Student.sno)>=all(select count(Student.sno)from SC,Course,Student where SC.sno=Student.sno and SC.cno=Course.cno and sdept=‘CS’ group by SC.cno,cname);

–1.PL/SQL匿名块1
–1.1打印001号学生的姓名和年龄。打印结果格式（忽略行标号，其中冒号为中文状态下的冒号）：
–001号同学的姓名：查询出的姓名
–001号同学的年龄：查询出的年龄
declare
vsname varchar(20);
vsage smallint;
begin
select sname,sage into vsname,vsage from Student
where sno=‘001’;
dbms_output.put_line(‘001号同学的姓名：’||vsname);
dbms_output.put_line(‘001号同学的年龄：’||vsage);
end;

–1.2.将年龄最大的同学的学号，姓名，年龄打印出来（注：年龄最大的同学只有一个）。其格式为（忽略行标号）：
–查询出来的学号，查询出来的姓名，查询出来的年龄，性别（查询出来的性别，若是’f’，显示：美女。若是’m’显示：帅哥。）
–例如：（其中逗号为英文状态下的逗号）
–001，张三，20，美女
declare
vsno char(10);
vsname varchar(20);
vsage smallint;
vssex char(2);
begin
select sno,sname,sage,sex into vsno,vsname,vsage,vssex from Student
where sage>=all(select sage from Student);
if vssex=‘m’ then
dbms_ouput.put_line(vsno||’，’||vsname||’，’||vsage||’，’||‘帅哥’);
else
dbms_ouput.put_line(vsno||’，’||vsname||’，’||vsage||’，’||‘美女’);
end if;
end;

–2.PLSQL匿名块2
–2.1.将‘004’号同学的年龄改为18岁，系别改为‘E’，为CS系年龄最小的同学选‘C01’号课程。
begin
update Student set sage=18,sdept=‘CS’ where sno=‘004’;
insert into SC(sno,cno) select sno,‘C01’ from Student where sdept=‘CS’ and sage<=all(select sage from Student where sdept=‘CS’) and not exists(select*from SC where sno=Student.sno and cno=‘C01’);
commit;
end;
–2.2.为‘002’号同学选‘C02’课程。将选课人数最多的课程的最大选课人数（snumber）改为105。
begin
insert into SC(sno,cno) values(‘002’,‘C02’);
update Course set snumber=105 where cno in(select cno from SC where Course.cno=SC.cno group by SC.cno having count(sno)>=all(select count(sno) from SC group by SC.cno));
commit;
end;

–查询‘111’号同学的姓名，使用异常判断，若没有这个同学，则添加一条学号为‘111’的学生信息，姓名为‘张三’，其他属性为空，并输出：‘111’号同学已被成功添加。
–若存在该同学，则输出（冒号为中文状态下的冒号）： 111号同学的姓名：查询出的姓名
–若不存在该同学，则输出： 111号同学已被成功添加。
declare
vsname varchar(20);–姓名
begin
select sname into vsname from student where sno =‘111’;
dbms_output.put_line(‘111号同学的姓名：’||vsname);
exception when no_data_found then
insert into student(sno,sname) values(‘111’,‘张三’);
commit;
dbms_output.put_line(‘111号同学已被成功添加’);
end;

–打印‘C50’号课程的当前选课人数和最大选课人数（Snumber）。
–若出现异常，则判断C50号课程是否存在，
–若不存在，则添加C50课程（‘C50’,‘NewCourse’,null,3,100），
–再让001号同学和003号同学选修C50号课程，成绩分别为80分和90分，
–最后打印C50号课程的当前选课人数和最大选课人数。打印格式如下：
–若C50存在，则打印格式如下（其中的逗号和冒号是中文状态下的）：
–（查询出的C50的课程名）课程当前选课人数：（查出来的人数），最大选课人数：（查出来的最大人数）
–如：DB课程当前选课人数：80，最大选课人数：100

–若C50不存在，则打印格式（其中的逗号和冒号是中文状态下的）：
–添加NewCourse课程成功，课程当前选课人数：（查出来的人数），最大选课人数：（查出来的最大人数）
–如：添加NewCourse课程成功，课程当前选课人数：10，最大选课人数：100

declare
cnt int;–当前选课人数
vsnumber int; --最大选课人数
vcname varchar(20); --课程名
begin
–C50号课程的当前选课人数,最大选课人数和课程名
select count(sno),snumber,cname into cnt,vsnumber,vcname
from sc, course
where sc.cno = ‘C50’ and course.cno = ‘C50’
group by sc.cno, snumber,cname;

dbms_output.put_line(vcname||‘课程当前选课人数：’||cnt||’，最大选课人数：’||vsnumber);
exception when no_data_found then
–添加C50课程（‘C50’,‘NewCourse’,null,3,100）
insert into course values(‘C50’,‘NewCourse’,null,3,100);
commit;
–再让001号同学和003号同学选修C50号课程，成绩分别为80分和90分，
insert into SC values(‘001’,‘C50’,80);
insert into SC values(‘003’,‘C50’,90);
commit;
–C50号课程的当前选课人数,最大选课人数和课程名
select count(sno),snumber,cname into cnt,vsnumber,vcname
from sc, course
where sc.cno = ‘C50’ and course.cno = ‘C50’
group by sc.cno, snumber,cname;
dbms_output.put_line(’ 添加NewCourse课程成功，课程当前选课人数：’||cnt||’，最大选课人数：’||vsnumber);
end;
–为English系年龄最大的同学选C52课程，并打印选课结果和该同学的选课数，
–若该生已经选择了C52课程，则打印该生的平均分。
–使用嵌套块中的异常判断，
–若没有English系，则将Student表中的E系修改为English系，
–并打印：已成功将E系修改为English系，然后退出程序。
–若没有C52课程，请添加C52课程（‘C52’,‘NC’,null,2,100）
–并打印：C52课程添加成功，然后退出程序。
–注意：该程序要成功执行的前提是English系的最大年龄对应的同学只能有一个。若在你们自己的系统下测试，请通过修改数据确保这个前提条件。
–（1）若学生和课程都存在，
–则为其选课，并输出（冒号为中文状态下的冒号）： 已为（查询出的姓名）同学选择了课程（查询出来的课程名），该同学的选课门数为：（查询出的选课门数）
–如：已为张三同学选择了课程DB，该同学的选课门数为：10
–（2）若该生已经选择了课程C52，则输出： （查询出的姓名）同学的平均成绩为：（查询出的平均分） 如：张三同学的平均成绩为：85
–（3）若系别不存在，则修改数据后，输出： 已成功将E系修改为English系
–（4）若课程不存在，则添加数据后，输出： C52课程添加成功

declare
vsname varchar(20);–English系年龄最大的姓名
vgrade SC.grade%type;
vsno char(10);–English系年龄最大的学号
scnt int;-- scnt>0 学生选了指定课程
vcnt int;–学生选课数
vcname varchar(20);
begin
–查找English系年龄最大的学生
select sno,sname into vsno,vsname from Student where sdept=‘English’ and sage>=all(select sage from Student where sdept=‘English’);
–dbms_output.put_line(vsno||vsname);
begin
–是否存在C52
select cname into vcname from Course where cno=‘C52’;

begin
  --English系年龄最大的学生是否选了C52，scnt=1 则选了
  select count(*) into scnt from SC where sno=vsno and cno='C52';
  if(scnt>0) then
    select avg(grade) into vgrade from SC where sno=vsno;
    dbms_output.put_line(vsname||'同学的平均成绩为：'||vgrade);
  else 
    insert into SC(sno,cno) values(vsno,'C52');
    select count(*) into vcnt from SC where sno=vsno;
    dbms_output.put_line('已为'||vsname||'同学选择了课程'||vcname||'，该同学的选课门数为：'||vcnt);
    commit;
  end if;
end;

exception when no_data_found then–不存在C52
insert into Course values(‘C52’,‘NC’,null,2,100);
commit;
dbms_output.put_line(’ C52课程添加成功’);
end;
exception when no_data_found then --系别不存在
update Student set sdept=‘English’ where sdept=‘E’;
commit;
dbms_output.put_line(’ 已成功将E系修改为English系’);
end;

–1、使用静态游标，遍历CS系选择了“数学”的学生，列出学生的学号，姓名，成绩（按学号升序排列）。
declare
cursor c1 is select student.sno,sname,grade from sc,student
where sc.sno=student.sno and sdept=‘CS’ and cno in
(select cno from course where cname =‘数学’) order by sno asc;
v_stu c1%rowtype;
begin
open c1;
dbms_output.put_line(‘学号 姓名 成绩’);
dbms_output.put_line(rpad(’-’,25,’-’));
loop
fetch c1 into v_stu;
exit when c1%notfound;
dbms_output.put_line(rpad(v_stu.sno,10,’ ‘)||rpad(v_stu.sname,10,’ ')||v_stu.grade);
end loop;
end;

–2、使用静态游标，遍历所有CS系学生的选课信息，打印学号，姓名，系别，课程名，成绩（按学号和课程名升序排列）。
declare
cursor c1 is select student.sno,sname,sdept,cname,grade from student,sc,course
where student.sno=sc.sno and course.cno=sc.cno
and sdept = ‘CS’ order by student.sno,cname asc;
v_stu c1%rowtype;
v_sno student.sno%type;
begin
open c1;
fetch c1 into v_stu;
v_sno:=v_stu.sno;
dbms_output.put_line(‘学号 姓名 系别 课程名 成绩’);
dbms_output.put_line(rpad(’=’,45,’=’));
loop
if v_stu.sno = v_sno then
dbms_output.put_line(rpad(v_stu.sno,10,’ ‘)||rpad(v_stu.sname,10,’ ‘)||rpad(v_stu.sdept,10,’ ‘)||rpad(v_stu.cname,10,’ ‘)||rpad(v_stu.grade,10,’ ‘));
else
dbms_output.put_line(rpad(’-’,45,’-’));
dbms_output.put_line(rpad(v_stu.sno,10,’ ‘)||rpad(v_stu.sname,10,’ ‘)||rpad(v_stu.sdept,10,’ ‘)||rpad(v_stu.cname,10,’ ‘)||rpad(v_stu.grade,10,’ '));
end if;
v_sno:=v_stu.sno;
fetch c1 into v_stu;
exit when c1%notfound;
end loop;
close c1;

end;

–3、使用系统动态弱游标(sys_refcursor，请使用小写以便于后台验证)，查询男同学(性别为’m’)的选课情况，将每个同学的选课信息打印出来（及格则得对应课程的学分），并汇总其选课课程数。打印格式如下（按学号和课程名升序排列，使用rpad对齐打印，其中学号占10个长度，课程名占10个长度，成绩占10个长度）。
–使用系统动态弱游标
–(sys_refcursor，请使用小写以便于后台验证)，
–查询男同学(性别为’m’)的选课情况，将每个同学的选课信息打印出来（及格则得对应课程的学分）
–，并汇总其选课课程数。打印格式如下（按学号和课程名升序排列，使用rpad对齐打印，
–其中学号占10个长度，课程名占10个长度，成绩占10个长度）：
declare
v_rc sys_refcursor;
cursor c1 is select student.sno from course,sc,student where student.sno=sc.sno and course.cno=sc.cno and ssex = ‘m’ order by sno asc;
v_sno student.sno%type;
v_cname course.cname%type;
v_grade sc.grade%type;
v_ccredit course.ccredit%type;
v_rowcount int:=0;
v_snotmp student.sno%type;
begin
open c1;
dbms_output.put_line(‘学号 课程名 成绩 所得学分’);
dbms_output.put_line(rpad(’=’,40,’=’));
open v_rc for select student.sno,cname,grade,ccredit from course,sc,student where student.sno=sc.sno and course.cno=sc.cno and ssex = ‘m’ order by sno asc;
fetch c1 into v_snotmp;
loop
fetch v_rc into v_sno,v_cname,v_grade,v_ccredit;
if v_rc%notfound then
dbms_output.put_line(rpad(’-’,35,’-’));
dbms_output.put_line(substr(v_snotmp,1,4)||‘同学的选课数：’||v_rowcount||‘门’);
else
if v_snotmp <> v_sno then
dbms_output.put_line(rpad(’-’,35,’-’));
dbms_output.put_line(substr(v_snotmp,1,4)||‘同学的选课数：’||v_rowcount||‘门’);
v_snotmp:=v_sno;
v_rowcount:=0;
if v_grade >= 60 then
if v_rowcount=0 then
dbms_output.put_line(rpad(’=’,40,’=’));
end if;
v_rowcount:=v_rowcount+1;
dbms_output.put_line(rpad(v_sno,10,’ ‘)||rpad(v_cname,10,’ ‘)||rpad(v_grade,10,’ ‘)||v_ccredit);
else
if v_rowcount=0 then
dbms_output.put_line(rpad(’=’,40,’=’));
end if;
v_rowcount:=v_rowcount+1;
dbms_output.put_line(rpad(v_sno,10,’ ‘)||rpad(v_cname,10,’ ‘)||rpad(v_grade,10,’ ‘)||rpad(‘0’,10,’ ‘)||’（说明：没有及格，不能得学分）’);
end if;
else
if v_grade >= 60 then
v_rowcount:=v_rowcount+1;
dbms_output.put_line(rpad(v_sno,10,’ ‘)||rpad(v_cname,10,’ ‘)||rpad(v_grade,10,’ ‘)||v_ccredit);
else
v_rowcount:=v_rowcount+1;
dbms_output.put_line(rpad(v_sno,10,’ ‘)||rpad(v_cname,10,’ ‘)||rpad(v_grade,10,’ ‘)||rpad(‘0’,10,’ ‘)||’（说明：没有及格，不能得学分）’);
end if;
end if;
end if;
exit when v_rc%notfound ;
end loop;

end;

–1、创建函数Fs1，传入课程号，返回字符型。返回该课程的先行课名。若该课程不存在，则返回格式如下(3)所示，若该课程没有先行课，则返回格式如下（2）所示。
–要求： 1、不能在函数中使用dbms_output.put_line打印数据，必须是通过Return将结果传出。 2、创建完函数后，请使用匿名块多次调用该函数，分别按顺序传入‘C06’，‘C01’，‘C001’。并将返回的结果分别打印出来。
–输出格式说明：
–(1) 若传入课程有先行课（冒号为中文状态下的冒号）： （通过传入的课程号查询出的课程名）课程的先行课为：（先行课名） 例如：Oracle课程的先行课为：数据库
declare
– Local variables here
cno1 course1.cno%type;
begin
– Test statements here
cno1:=‘C001’;
dbms_output.put_line(Fs1(cno1));
end;
–(2) 若传入的课程没有先行课（cpno为null）： （通过传入的课程号查询出的课程名）课程没有先行课。
declare
– Local variables here
cno1 course1.cno%type;
begin
– Test statements here
cno1:=‘C01’;
dbms_output.put_line(Fs1(cno1));
end;
–(3) 若传入的课程不存在： （传入的课程号）课程不存在。
declare
– Local variables here
cno1 course1.cno%type;
begin
– Test statements here
cno1:=‘C06’;
dbms_output.put_line(Fs1(trim(cno1)));
end;

create or replace function Fs1(cno1 in varchar2) return varchar2
is
v_cpno course1.cpno%type;
v_cpname course1.cname%type;
v_cname course1.cname%type;
begin
begin
select cname into v_cname from course1 where cno = cno1;
exception when no_data_found then
return(trim(cno1)||‘课程不存在’);
end;
begin
select cpno into v_cpno from course1 where cno = cno1;
exception when no_data_found then
return(trim(v_cname)||‘课程没有先行课’);
end;
if(v_cpno is null) then
return(trim(v_cname)||‘课程没有先行课’);
end if;
select cname into v_cpname from course1 where cno=v_cpno;
return(trim(v_cname)||‘课程的先行课为：’||v_cpname);

end;
–2、创建函数Fs2，传入学生学号，返回数值型。返回该学生的选课门数，若该学生不存在，则返回-1。
– 要求：
–1、不能在函数中使用dbms_output.put_line打印数据，必须是通过Return将结果传出。
–2、创建完函数后，请使用匿名块调用该函数，多次调用函数，分别按顺序传入‘001’，‘018’，‘123’。并将返回的结果分别按要求打印出来。 若返回-1，则打印： 001号同学不存在。 若返回选课数，则打印（冒号为中文状态下的冒号）： 001号同学的选课门数为：（返回的选课数）
declare
– Local variables here
sno1 student1.sno%type;
n2 int;
begin
– Test statements here
sno1:=‘001’;
n2 := Fs2(sno1);
if n2=-1 then
dbms_output.put_line(trim(sno1)||‘号同学不存在’);
else
dbms_output.put_line(trim(sno1)||‘同学的选课门数为：’||Fs2(sno1));
end if;
end;
– Created on 2021/12/13 by 86151
declare
– Local variables here
sno1 student1.sno%type;
n2 int;
begin
– Test statements here
sno1:=‘123’;
n2 := Fs2(sno1);
if n2=-1 then
dbms_output.put_line(trim(sno1)||‘号同学不存在’);
else
dbms_output.put_line(trim(sno1)||‘同学的选课门数为：’||Fs2(sno1));
end if;
end;
– Created on 2021/12/13 by 86151
declare
– Local variables here
sno1 student1.sno%type;
n2 int;
begin
– Test statements here
sno1:=‘123’;
n2 := Fs2(sno1);
if n2=-1 then
dbms_output.put_line(trim(sno1)||‘号同学不存在’);
else
dbms_output.put_line(trim(sno1)||‘同学的选课门数为：’||Fs2(sno1));
end if;
end;

create or replace function Fs2(sno1 in varchar2) return number
is
v_sno student1.sname%type;
n int;
n1 int:=-1;
begin
begin
select sname into v_sno from student1 where sno1 = sno;
exception when no_data_found then
return(n1);
end;

  select count(cno) scnt into n from sc where sno1=sno;
         return(n);

end;

–1、创建存储过程Ps1，传入课程名，查询该课程的选修情况（按学号升序排列），在存储过程中遍历游标，打印选修情况。使用异常捕获来处理课程不存在的情况，打印格式如下（学号打印长度10，课程号打印长度10）： 说明：原则上在存储过程中是不能通过打印返回结果的，但此题的核心要求是在存储过程中遍历游标，因此此题允许在存储过程中对游标遍历结果进行打印。 要求：创建完存储过程后，请使用匿名块多次调用该存储过程，分别传入“数据库”和“Oracle”。 输出格式说明：
（1）课程存在的情况： 学号 课程号 成绩 ========================（打印25个等号） 001 C01 78 002 C01 -1（成绩为空，则打印成绩为-1） 003 C01 56 ========================（打印25个等号）

declare
– Local variables here
cname1 course1.cname%type;
begin
– Test statements here
cname1:=‘oracle’;
Ps1(cname1);
end;

（2）课程不存在的情况： （输入的课程名）课程不存在。 例如：数据库课程不存在。 =============================（打印25个等号）

declare
– Local variables here
cname1 course1.cname%type;
begin
– Test statements here
cname1:=‘数据库’;
Ps1(cname1);
end;

create or replace noneditionable procedure Ps1(cname1 in varchar2)
is
Type sys_refcursor is ref cursor;
V1 sys_refcursor;
V2 sys_refcursor;
V3 sys_refcursor;
sno1 student1.sno%type;
cno1 course1.cno%type;
grade1 sc.grade%type;
begin
open V1 for select course1.cno from sc,student1,course1
where sc.sno = student1.sno and sc.cno = course1.cno
and cname = cname1;
begin
select cno into cno1 from course1 where cname1=cname;
exception when no_data_found then
dbms_output.put_line(cname1||‘课程不存在’);
dbms_output.put_line(rpad(’=’,25,’=’));
close V1;
return;
end;
begin
open V2 for select sc.sno,sc.cno,sc.grade from sc,student1,course1
where sc.sno = student1.sno and sc.cno = course1.cno
and cname = cname1;
dbms_output.put_line(rpad(‘学号’,10)||rpad(‘课程号’,10)||‘成绩’);
dbms_output.put_line(rpad(’=’,25,’=’));
loop
fetch V2 into sno1,cno1,grade1;
exit when V2%notfound;
if grade1 is null then
dbms_output.put_line(rpad(sno1,10,’ ‘)||rpad(cno1,10,’ ‘)||’-1’);
else
dbms_output.put_line(rpad(sno1,10,’ ‘)||rpad(cno1,10,’ ‘)||grade1);
end if;
end loop;
dbms_output.put_line(rpad(’=’,25,’=’));
close V2;
end;
end;

–1、创建存储过程Ps2，传入学生学号，通过字符型out参数输出操作结果。若该生选择了数据库课程，则返回数据库的成绩，返回格式如下(1)所示。若该生没有选择数据库，则判断是否为CS系，若是CS系，则为其选择“数据库”课程（成绩为空），并返回选课成功信息，返回格式如下（2）所示。否则返回该生未选择数据库课程，返回格式如下（3）所示。使用异常捕获来处理学生不存在的情况，若学生不存在，则返回格式如下（4）所示。
–要求： 1、不能在存储过程中使用dbms_output.put_line打印数据，必须是通过out参数将结果传出。
–2、创建完存储过程后，请使用匿名块多次调用该存储过程，分别按顺序传入学号：002，003，004，040。并分别将接收到的结果打印出来。 输出格式说明：
–（1）学生存在，并选择了数据库课程（冒号为中文状态下的冒号）： （查出来的姓名）同学的系别为：查出来的系别，数据库的成绩为：查出来的成绩（若成绩为空，则打印“未考试”） 例如：张三同学的系为：CS，数据库的成绩为：80
–(2) 学生存在，但没有选数据库课程，若为CS系的同学，则打印： 已成功为（查出来的姓名）同学选择了数据库课程。
– (3) 学生存在，但没有选数据库课程，若不是CS系的同学，则打印： （查出来的姓名）同学没有选择数据库课程。
–（4）学生不存在的情况： （传入的学号）号同学不存在。

– Created on 2021/12/15 by 86151
declare
– Local variables here
sno1 sc.sno%type:=‘02’;
grade1 sc.grade%type;
sname1 student1.sname%type;
sdept1 student1.sdept%type;
v_cno course1.cno%type;
begin
– Test statements here
Ps2(sno1,grade1);
if grade1=’-1’ then
dbms_output.put_line(trim(sno1)||‘号同学不存在’);
elsif grade1=’-2’ then
select sname into sname1 from student1 where sno=sno1;
dbms_output.put_line(trim(sname1)||‘同学没有选择数据库课程’);
elsif grade1=’-3’ then
select sname into sname1 from student1 where sno=sno1;
dbms_output.put_line(‘已成功为’||trim(sname1)||‘同学选择了数据库课程’);
elsif grade1 = null then
dbms_output.put_line(‘未考试’);
else
select sdept into sdept1 from student1 where sno = sno1;
select sname into sname1 from student1 where sno=sno1;

        dbms_output.put_line(trim(sname1)||'同学的系别为：'||trim(sdept1)||'，数据库的成绩为：'||trim(grade1));
      end if;

end;

create or replace procedure Ps2(sno1 in varchar2,grade1 out varchar2)
is
v_sno student1.sno%type;
v_cno course1.cno%type;
vs_cno sc.cno%type;
v_sdept student1.sdept%type;
V_sname student1.sname%type;

begin
begin
select sname into v_sname from student1 where sno = sno1;
select sno into v_sno from student1 where sno = sno1;
exception when no_data_found then
grade1:=’-1’;
return;
end;
select cno into v_cno from course1 where cname=‘数据库’;
begin
select cno into vs_cno from sc where sno=v_sno and cno=v_cno;
exception when no_data_found then
select sdept into v_sdept from student1 where sno=sno1;
if v_sdept=‘CS’ then
insert into sc values(sno1,v_cno,null,null);
commit;
grade1:=’-3’;
return;
else
grade1:=’-2’;
return;
end if;
end;
select grade into grade1 from sc where sno = sno1 and cno = v_cno;
end;

–1、创建存储过程Ps3，传入学生姓名，课程名和成绩，使用字符型out参数输出选课结果。假设学生姓名和课程名都不能重复。若该生选择了该课程，则修改成绩为传入的成绩，输出格式如下（5）所示。若该生没有选择该课程，则为该生选择该课程，成绩为传入的成绩，输出格式如下（4）所示。若学生或课程不存在，则输出格式如下（2）（3）所示。若成绩不在0到100分之间，则输出如下(1)所示。 要求： 1、不能在存储过程中使用dbms_output.put_line打印数据，必须是通过out参数将结果传出。 2、创建完存储过程后，请使用匿名块多次调用该存储过程，分别按顺序传入以下数据： ‘符志阶’,‘数据库’,86 ‘符志阶’,‘数据库’,null ‘符志阶s’,‘数据库’,86 ‘符志阶’,‘数据库s’,86 ‘符志阶’,‘数据库’,120 并分别将接收到的结果打印出来。 输出格式说明： （1）成绩不在0到100分之间： 成绩必须在0到100分之间。 (2) 学生不存在： （传入的学生姓名）同学不存在。 例如：张三同学不存在。 (3) 课程不存在： (传入的课程名)课程不存在。 例如：数据库课程不存在。 (4) 该生没有选择该课程： 已经成功为(查出来的学号)号同学选择了(传入的课程名)课程，成绩为(传入的成绩) 例如：已经成功为001号同学选择了数据库课程，成绩为80 (5) 该生选择了该课程： 已将（查出来的学号）号同学（传入的课程名）课程的成绩改为（传入的成绩，若为null，则显示为“未考试”） 例如：已将001同学数据库课程的成绩改为90 或者 已将001同学数据库课程的成绩改为未考试

declare
sname1 student1.sname%type:=‘dwx’;
cname1 course1.cname%type:=‘数据库’;
grade1 sc.grade%type:=99;
msg varchar2(200);
begin
Ps3(sname1,cname1,grade1,msg);
dbms_output.put_line(msg);
end;

create or replace procedure Ps3(sname1 in varchar2,cname1 in varchar2,grade1 in varchar2,msg out varchar2)

is
v_sno student1.sno%type;
v_cno course1.cno%type;
vs_cno sc.cno%type;
begin
begin
select sno into v_sno from student1 where sname=sname1;
exception when no_data_found then
msg:=trim(sname1)||‘同学不存在’;
return;
end;
begin
select cno into v_cno from course1 where cname=cname1;
exception when no_data_found then
msg:=trim(cname1)||‘课程不存在’;
return;
end;
begin
select cno into vs_cno from sc where sno = v_sno and cno = v_cno;
exception when no_data_found then
insert into sc values(v_sno,v_cno,grade1,null);
commit;
msg:=‘已成功为’||trim(v_cno)||‘号同学选择了’||trim(cname1)||‘课程，’||‘成绩为’||trim(grade1);
return;
end;
if grade1<0 or grade1>100 then
msg:=‘成绩必须在0到100分之间’;
return;
end if;
update sc set grade = grade1 where sno = v_sno and cno = v_cno;
commit;
if grade1 is null then
msg:=‘已将’||trim(v_sno)||‘号同学’||trim(cname1)||‘课程的成绩改为未考试’;
else
msg:=‘已将’||trim(v_sno)||‘号同学’||trim(cname1)||‘课程的成绩改为’||trim(grade1);
end if;
return;
end;

–一、创建包pks1，包头中定义一个存放学号的变量（如vsno）和一个静态游标，查询学号变量对应的学生的选课信息，列出学号，姓名，课程名，成绩，并按成绩降序排列。
–1、在包中创建过程ps1，传入系，将该系平均分最高的学生的学号赋予包头中的学号变量（vsno）（这里假设每个系平均分最高的学生只有一个），若找不到平均分最高的学生，则判断系是否存在，若系不存在，则给学号变量赋值‘-1’，若系存在（该系的学生没有选课），则给学号变量赋值‘-2’。
–2、在包中创建函数fs1，传入学号，返回该学生获得的总学分。（必须要成绩及格才能获得对应课程学分）

create or replace package pks1 is

vsno student.sno%type;
cursor c1 is select student.sno,sname,cname,grade from student,sc,course where student.sno = vsno and student.sno = sc.sno and sc.cno = course.cno order by grade desc;
procedure ps1 (vsdept in student.sdept%type);
function fs1 (vsno in student.sno%type)return int;

end;

create or replace package body pks1 is
procedure ps1 (vsdept in student.sdept%type) is
vsdeptCount int:=0;
begin
select sno into vsno from sc where sno in (select sno from student where sdept = vsdept) and grade >= all (select max(grade) from sc where sno in(select sno from student where sdept = vsdept));
exception when no_data_found then
select count() into vsdeptCount from student where sdept = vsdept;
if vsdeptCount = 0 then
vsno:=-1;
else
select count() into vsdeptCount from sc where sno in (select sno from student where sdept = vsdept);
if vsdeptCount = 0 then
vsno:=-2;
end if;
end if;
end ps1;

function fs1 (vsno in student.sno%type)return int is
mycredits int:=0;
begin
select sum(ccredit) into mycredits from course where cno in (select cno from sc where sno = vsno and grade >= 60);
return mycredits;
end fs1;

end pks1;
declare
vsdept student.sdept%type:= ‘未知’;
r1 pks1.c1%rowtype;
begin
pks1.ps1(vsdept);
if pks1.vsno <> -1 and pks1.vsno <> -2 then
dbms_output.put_line(‘学号 姓名 课程名 成绩’);
dbms_output.put_line(rpad(’=’,40,’=’));
– for i in pks1.c1 loop
– dbms_output.put_line(r1.sno||’ ‘||r1.sname||’ ‘||r1.cname||’ ‘||r1.grade);
– end loop;
open pks1.c1;
loop
exit when pks1.c1%notfound;
fetch pks1.c1 into r1;
dbms_output.put_line(r1.sno||’ ‘||rpad(r1.sname,8,’ ‘)||’ ‘||rpad(r1.cname,15,’ ‘)||’ ‘||r1.grade);
end loop;
close pks1.c1;
dbms_output.put_line(rpad(’=’,40,’=’));
dbms_output.put_line(pks1.vsno||‘号同学获得的总学分为：’||pks1.fs1(pks1.vsno));
end if;
if pks1.vsno = -1 then
dbms_output.put_line(vsdept||‘系不存在。’);
end if;
if pks1.vsno = -2 then
dbms_output.put_line(vsdept||‘系的同学没有选课记录。’);
end if;
end;

–一、创建包pks2，使其完成下列功能：
–1、在包中创建私有过程pSCnumber，传入学号，首先判断SC_Number表是否存在，若不存在则创建该表（包括学号和选修门数（列名为Scnt）两列），并初始化该表数据。若表存在，则将SC_Number表中传入的学号对应的选课数加1，若该生不存在（使用隐试游标判断），则插入一行，使其选课数为1。
–2、在包中创建过程ps1，传入学生姓名，课程名，成绩，若该生已经选择了该课程，则修改该课程的成绩为输入成绩，并返回格式如下（1）所示；否则为学生选课，若该课程已经满额（当前选课人数大于等于最大选课人数），则返回如下（3）所示；若选课成功则调用pSCnumber私有过程将学生选课情况修改，返回格式如下（2）所示，若该生或该课程不存在，则返回格式如下（4）（5）（6）所示。
–3、在包中创建函数fs1，传入学生学号，返回一个字符串，将该生对应的选课信息(SC)，学生基本信息(Student)，SC_Number中关于该生的信息全部删除，返回信息格式如下（7）所示，若该生不存在，则返回信息格式如下（8）所示。
–4、在包中创建过程ps2，删除sc_number表，返回格式如下（9）（10）所示。

create or replace package pks2 is

procedure pSCnumber (vsno in student.sno%type);

procedure ps1(vsname in student.sname%type,vcname in course.cname%type,vgrade in sc.grade%type,vmsg out varchar2);

procedure ps2(vmsg out varchar2);

function fs1(vsno in student.sno%type)return varchar2;

end pks2;

create or replace package body pks2 is

procedure pSCnumber (vsno in student.sno%type) is
flag int := 0;
begin
select count() into flag from user_tables where table_name = ‘SC_Number’;
if flag = 0 then
execute immediate ‘create table SC_Number(sno char(10),scnt int)’;
execute immediate 'insert into SC_Number(sno,scnt) select sno,count() from sc group by sno’;
commit;
else
execute immediate ‘update SC_Numberset scnt = scnt + 1 where sno = :1’ using vsno;
if sql%notfound then
execute immediate ‘insert into SC_Number(sno,scnt) values (:1,1)’ using vsno;
end if;
commit;
end if;
end pSCnumber;

procedure ps1 (vsname in student.sname%type,vcname in course.cname%type,vgrade in sc.grade%type,vmsg out varchar2) is
flag int:= 0 ;
vsno student.sno%type;
vcno course.cno%type;
currentGrade int:=0;
selected int:=0;
maxSelected int:=0;
snoFlag int:=0;
cnoFlag int:=0;
begin
select sno into vsno from student where sname = vsname;
select cno into vcno from course where cname = vcname;
select count() into flag from sc where sno=vsno and cno = vcno;
if flag = 0 then
select snumber into maxSelected from course where cname = vcname;
select count() into selected from sc where cno in (select cno from course where cname = vcname);
if maxSelected >selected then
insert into sc(sno,cno,grade) values (vsno,vcno,vgrade);
commit;
vmsg:=vsno||‘号同学，选修了’||vcno||‘号课程，成绩为’||vgrade;
pks2.pSCnumber(vsno);
else
vmsg:=vcno||‘号课程已经满额了，当前选课人数’||selected||’，最大选课人数’||maxSelected;
end if;
else
select grade into currentGrade from sc where sno = vsno and cno = vcno;
update sc
set grade = vgrade
where cno = vcno and sno in (select sno from student where sname = vsname);
commit;
if vgrade is null then
vmsg:=‘已将’||vsno||‘号同学的’||vcno||‘号课程成绩从’||currentGrade||‘修改为未考试’;
else
vmsg:=‘已将’||vsno||‘号同学的’||vcno||‘号课程成绩从’||currentGrade||‘修改为’||vgrade;
end if;
end if;
exception when no_data_found then
select count() into snoFlag from student where sname = vsname;
select count() into cnoFlag from course where cname = vcname;
if snoFlag = 0 and cnoFlag = 0 then
vmsg:=vsname||‘同学不存在。’||vcname||‘课程不存在。’;
end if;
if snoFlag = 0 then
vmsg:=vsname||‘同学不存在。’;
end if;
if cnoFlag = 0 then
vmsg:=vcname||‘课程不存在。’;
end if;
end ps1;

procedure ps2(vmsg out varchar2) is
flag int:=0;
begin
select count(*) into flag from user_tables where table_name = ‘SC_Number’ ;
if flag = 0 then
vmsg:=‘SC_Number表不存在。’;
else
execute immediate ‘drop table SC_Number’;
vmsg:=‘SC_Number表被成功删除。’;
end if;
end ps2;

function fs1 (vsno in student.sno%type) return varchar2 is
flag int:=0;
msg varchar2(99);
begin
select count(*) into flag from student where sno = vsno;
if flag = 0 then
msg:=vsno||‘同学不存在。’;
return msg;
else
delete from sc where sno =vsno;
delete from student where sno =vsno;
execute immediate’delete from SC_Number where sno = :1’ using vsno;
commit;
msg:=vsno||‘同学已被删除。’;
return msg;
end if;
end fs1;

end pks2;

declare
msg varchar2(99);
i integer;
begin
msg:=pks2.fs1(‘0001’);
dbms_output.put_line(msg);
pks2.ps1(‘0001’,‘0009’,53,msg);
dbms_output.put_line(msg);

end;

–八：触发器

–第一关：
–1、完成第一个触发器的创建******
create or replace trigger tr_Student_update
before update
on student
for each row when (new.sage >= 20)
begin

RAISE_APPLICATION_ERROR(-20000, '不能修改20岁以上的同学系别。');

end ;

–2、完成第二个触发器的创建******
create or replace trigger tr_Course_Insert
before insert
on course
for each row
when (substr(new.cno, 1, 1) = ‘X’ and new.cpno is null)
begin

RAISE_APPLICATION_ERROR(-20000, '课程号以X开头的课程，先行课不能为空。');

End;
–3、完成第三个触发器的创建******
create or replace trigger tr_SC_Delete
before delete
on sc
for each row
declare
v_cname varchar(20);
begin
select cname into v_cname from course where cno = :old.cno;
if (v_cname = ‘数据库’) then
raise_application_error(-20000, ‘不能删除数据库课程的选课信息。’);
end if;
end;
–4、完成第四个触发器的创建******
create or replace trigger tr_sc_update
before update
of grade
on sc
for each row
declare
v_sdept varchar(20);
begin
select sdept into v_sdept from student where sno = :old.sno;
if (v_sdept = ‘CS’) then
if (:new.grade < :old.grade) then
raise_application_error(-20000, ‘CS系学生的成绩只能增加不能减少。’);
end if;
if (:new.grade is null) then
raise_application_error(-20000, ‘CS系学生的成绩不能修改为空。’);
end if;
end if;
end;

–第二关：

–1、完成触发器的创建******
create or replace trigger tr_student_delete
before delete
on student
for each row
begin
delete from sc where sno = :old.sno;
end;
delete from student where sno = ‘001’;
commit;
–*****为Student表添加一列Scnt（选课门数），并初始化其数据
alter table student add scnt int;
update student set scnt = 0;
commit;
–1、完成触发器的创建
create or replace trigger tr_sc
after insert or update or delete
on sc
declare
begin
update student set scnt = (select count(*) from sc where sc.sno = student.sno);
end;
insert into sc values(‘019’, ‘C01’, null);
update sc set sno = ‘015’ where sno = ‘014’ and cno = ‘C01’;
delete from sc where sno = ‘013’;
commit;

–第三关：
–*****为Student表添加一列SumG（总成绩），并初始化其数据
alter table student add sumG int;
update student set sumG = (
select sum(grade) from sc where sc.sno = student.sno
); commit;
–1、完成触发器的创建
create or replace trigger tr
after insert or update or delete
on sc
begin
update student set sumG = (
select sum(grade) from sc where sc.sno = student.sno
);
end;
–***********为Student表添加一列SumG（总成绩），并初始化其数据
alter table student add sumG int;
update student set sumG = (
select sum(grade) from sc where sc.sno = student.sno
);
commit;
create or replace trigger tr
after insert or update or delete
on sc
begin
update student set sumG = (
select sum(grade) from sc where sc.sno = student.sno
);
end;
–过程.txt
– 1 带参数的过程
CREATE OR REPLACE PROCEDURE show_line
(ip_line_length IN NUMBER,
ip_separator IN VARCHAR2)
IS
actual_line VARCHAR2(150);
BEGIN
FOR idx in 1…ip_line_length LOOP
actual_line :=actual_line ||ip_separator;
END LOOP;
DBMS_OUTPUT.PUT_LINE(actual_line);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
/

– 2 带参数的函数（需要Return返回值）
CREATE OR REPLACE FUNCTION f_line
(ip_line_length IN NUMBER,
ip_separator IN VARCHAR2)
RETURN VARCHAR2
IS
actual_line VARCHAR2(150);
BEGIN
FOR idx in 1…ip_line_length LOOP
actual_line :=actual_line ||ip_separator;
END LOOP;
RETURN (actual_line);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
RETURN (null);
END;
/

– 3 调用过程
DECLARE
v_length NUMBER :=50;
v_separator VARCHAR2(1):=’=’;
BEGIN
show_line(v_length,v_separator);
END;
/

– 4 调用过程，指定参数
DECLARE
v_length NUMBER :=50;
v_separator VARCHAR2(1):=’=’;
BEGIN
show_line(ip_line_length=>v_length,ip_separator=>v_separator);
END;
/

– 5 调用过程，指定部分参数
DECLARE
v_length NUMBER :=50;
v_separator VARCHAR2(1):=’=’;
BEGIN
show_line(v_length,ip_separator=>v_separator);
END;
/

– 6 过程通过参数返回结果（Out参数）
CREATE OR REPLACE PROCEDURE show_line2
(ip_line_length IN NUMBER,
ip_separator IN VARCHAR2,
op_line OUT VARCHAR2)
IS
actual_line VARCHAR2(150);
BEGIN
FOR idx in 1…ip_line_length LOOP
actual_line :=actual_line ||ip_separator;
END LOOP;
op_line :=actual_line;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
op_line :=null;
END;
/

– 7 调用上面过程
DECLARE
v_length NUMBER :=50;
v_separator VARCHAR2(1):=’=’;
v_line VARCHAR2(150);
BEGIN
show_line2(v_length,v_separator,v_line);
dbms_output.put_line(v_line);
END;
/

– 8 带缺省值的参数
CREATE OR REPLACE PROCEDURE show_line
(ip_line_length IN NUMBER,
ip_separator IN VARCHAR2 DEFAULT ‘=’)
IS
actual_line VARCHAR2(150);
BEGIN
FOR idx in 1…ip_line_length LOOP
actual_line :=actual_line ||ip_separator;
END LOOP;
DBMS_OUTPUT.PUT_LINE(actual_line);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
/

– 9 将所有参数设置缺省值
CREATE OR REPLACE PROCEDURE show_line
(ip_line_length IN NUMBER DEFAULT 50,
ip_separator IN VARCHAR2 DEFAULT ‘=’)
IS
actual_line VARCHAR2(150);
BEGIN
FOR idx in 1…ip_line_length LOOP
actual_line :=actual_line ||ip_separator;
END LOOP;
DBMS_OUTPUT.PUT_LINE(actual_line);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
/

–10 传入学生学号，得出学生的相关信息
create or replace function f_get_strM(vsno varchar2) return varchar2 is
v_strM varchar2(300);
begin
select rpad(‘学号：’ || sno ,12, ’ ')||rpad(‘姓名：’ || sname ,15, ’ ‘) ||rpad( ‘系别：’ || sdept,20,’ ')
into v_strm
from student
where sno = vsno;
return(v_strm);
exception
when no_data_found then
v_strm := ‘没有对应的学生’;
return(v_strm);
when others then
v_strm := sqlerrm;
return(v_strm);
end;

–在查询中调用上面函数
select f_get_strm(sno) 学生基本信息 from student;

– 11 包的定义（包头和包体）
CREATE OR REPLACE PACKAGE orgMaster
IS
max_sites_for_an_org NUMBER;
TYPE rc IS REF CURSOR;

PROCEDURE createOrg (ip_hrc_code NUMBER,
ip_org_id NUMBER,
ip_org_short_name VARCHAR2,
ip_org_long_name VARCHAR2,
op_retcd OUT NUMBER,
op_err_msg OUT VARCHAR2);

PROCEDURE updateOrg (ip_org_id NUMBER,
ip_org_short_name VARCHAR2,
ip_org_long_name VARCHAR2,
op_retcd OUT NUMBER,
op_err_msg OUT VARCHAR2);

PROCEDURE removeOrg (ip_org_id NUMBER,
op_retcd OUT NUMBER,
op_err_msg OUT VARCHAR2);

FUNCTION getOrgInfo (ip_org_id NUMBER) RETURN rc;

FUNCTION getAllOrgs (ip_hrc_code NUMBER) RETURN rc;

PROCEDURE assignSiteToOrg (ip_org_id NUMBER,
ip_site_no NUMBER,
op_retcd OUT NUMBER,
op_err_msg OUT VARCHAR2);

END orgMaster;
/
CREATE OR REPLACE PACKAGE BODY orgMaster
IS
–Procedure to remove rows from org_site_tab table for a given org_id
–This is necessary before deleting rows from org_tab.
–This procedure is called from removeOrg procedure
PROCEDURE removeOrgSites(ip_org_id NUMBER,
op_retcd OUT NUMBER,
op_err_msg OUT VARCHAR2)
IS
BEGIN
DELETE org_site_tab WHERE org_id = ip_org_id;
op_retcd :=0;
EXCEPTION WHEN OTHERS THEN
op_retcd :=SQLCODE;
op_err_msg :=SQLERRM;
END removeOrgSites;

–Procedure to create a new Org record in org_tab
PROCEDURE createOrg (ip_hrc_code NUMBER,
ip_org_id NUMBER,
ip_org_short_name VARCHAR2,
ip_org_long_name VARCHAR2,
op_retcd OUT NUMBER,
op_err_msg OUT VARCHAR2)
IS
BEGIN
INSERT INTO org_tab VALUES
(ip_hrc_code,ip_org_id,ip_org_short_name,ip_org_long_name);
op_retcd :=0;
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
op_retcd :=-1;
op_err_msg :='Organization with Id '||TO_CHAR(ip_org_id)||
‘already exists.’;
WHEN OTHERS THEN
op_retcd :=SQLCODE;
op_err_msg :=SQLERRM;
END createOrg;

–Procedure to update the short and long names of an Org in org_tab
–based on input org_id
PROCEDURE updateOrg(ip_org_id NUMBER,
ip_org_short_name VARCHAR2,
ip_org_long_name VARCHAR2,
op_retcd OUT NUMBER,
op_err_msg OUT VARCHAR2)
IS
BEGIN
UPDATE org_tab
SET org_short_name =ip_org_short_name,
org_long_name =ip_org_long_name
WHERE org_id =ip_org_id;
IF (SQL%NOTFOUND) THEN
op_retcd :=-1;
op_err_msg :='Organization with Id '||TO_CHAR(ip_org_id)||
‘does not exist.’;
RETURN;
END IF;
op_retcd :=0;
EXCEPTION WHEN OTHERS THEN
op_retcd :=SQLCODE;
op_err_msg :=SQLERRM;
END updateOrg;

–Procedure to delete a record in org_tab
PROCEDURE removeOrg(ip_org_id NUMBER,
op_retcd OUT NUMBER,
op_err_msg OUT VARCHAR2)
IS
BEGIN
removeOrgSites(ip_org_id,op_retcd,op_err_msg);
IF (op_retcd <>0) then
RETURN;
END IF;
DELETE org_tab WHERE org_id = ip_org_id;
IF (SQL%NOTFOUND) THEN
op_retcd :=-1;
op_err_msg :='Organization with Id '||TO_CHAR(ip_org_id)||
‘does not exist.’;
RETURN;
END IF;
op_retcd :=0;
EXCEPTION WHEN OTHERS THEN
op_retcd :=SQLCODE;
op_err_msg :=SQLERRM;
END removeOrg;

–Function to return a row in org_tab for a given org_id.
–It returns a resultset of type REF CURSOR defined in the package specification
FUNCTION getOrgInfo(ip_org_id NUMBER) RETURN rc
IS
v_rc rc;
BEGIN
OPEN v_rc FOR SELECT * FROM org_tab WHERE org_id = ip_org_id;
RETURN (v_rc);
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,SQLERRM);
END getOrgInfo;

–Function to return all rows in org_tab.
–It returns a resultset of type REF CURSOR defined in the package specification
FUNCTION getAllOrgs(ip_hrc_code NUMBER) RETURN rc
IS
v_rc rc;
BEGIN
OPEN v_rc FOR SELECT * FROM org_tab WHERE hrc_code = ip_hrc_code;
RETURN (v_rc);
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002,SQLERRM);
END getAllOrgs;

–Procedure to insert a row into org_site_tab based on
–input org_id and site_no
PROCEDURE assignSiteToOrg(ip_org_id NUMBER,
ip_site_no NUMBER,
op_retcd OUT NUMBER,
op_err_msg OUT VARCHAR2)
IS
v_num NUMBER;
BEGIN
BEGIN
SELECT 1
INTO v_num
FROM org_site_tab
WHERE org_id = ip_org_id
AND site_no = ip_site_no;
IF (v_num =1) THEN
op_retcd :=0;
RETURN;
END IF;
EXCEPTION WHEN NO_DATA_FOUND THEN
INSERT INTO org_site_tab VALUES (ip_org_id,ip_site_no);
END;
op_retcd :=0;
EXCEPTION WHEN OTHERS THEN
op_retcd :=SQLCODE;
op_err_msg :=SQLERRM;
END assignSiteToOrg;

–This is the initialization section that is executed
–the first time a package sub-program is invoked
–or a packaged variable is referenced
BEGIN
max_sites_for_an_org :=4;
END orgMaster;
/

– Subprograms returning resultsets, function getAllHierarchies
CREATE OR REPLACE FUNCTION getAllHierarchies
RETURN SYS_REFCURSOR
IS
v_rc SYS_REFCURSOR;
BEGIN
OPEN v_rc FOR SELECT * FROM hrc_tab;
RETURN (v_rc);
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002,SQLERRM);
END;
/

– Calling the above function
DECLARE
v_rc SYS_REFCURSOR;
hrc_rec hrc_tab%ROWTYPE;
BEGIN
v_rc :=getAllHierarchies;
LOOP
FETCH v_rc INTO hrc_rec;
EXIT WHEN v_rc%NOTFOUND;
dbms_output.put_line(TO_CHAR(hrc_rec.hrc_code)||’ ‘||hrc_rec.hrc_descr);
END LOOP;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(TO_CHAR(SQLCODE)||’ '||SQLERRM);
END;
/

– Using stored functions in SQL statements, function f_get_formatted_org_name
CREATE OR REPLACE FUNCTION f_get_formatted_org_name
(ip_hrc_code NUMBER,
ip_org_id NUMBER)
RETURN VARCHAR2
IS
v_name VARCHAR2(120);
BEGIN
SELECT 'Org Name: (Short) ‘||org_short_name||’ (Long) '||org_long_name
INTO v_name
FROM org_tab
WHERE hrc_code = ip_hrc_code
AND org_id = ip_org_id;
RETURN (v_name);
END f_get_formatted_org_name;
/
– Calling the above function from SQL
SELECT f_get_formatted_org_name(hrc_code,org_id) “Formatted Org Name”
FROM org_tab
ORDER BY hrc_code,org_id;

– Package rfPkg
CREATE OR REPLACE PACKAGE rfPkg
IS
FUNCTION f_get_formatted_org_name
(ip_hrc_code NUMBER,
ip_org_id NUMBER)
RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(f_get_formatted_org_name,WNDS,WNPS);
END rfPkg;
/
CREATE OR REPLACE PACKAGE BODY rfPkg
IS
FUNCTION f_get_formatted_org_name
(ip_hrc_code NUMBER,
ip_org_id NUMBER)
RETURN VARCHAR2
IS
v_name VARCHAR2(120);
BEGIN
SELECT 'Org Name: (Short) ‘||org_short_name||’ (Long) '||org_long_name
INTO v_name
FROM org_tab
WHERE hrc_code = ip_hrc_code
AND org_id = ip_org_id;
RETURN (v_name);
END f_get_formatted_org_name;
END rfPkg;
/

– Calling the above packaged function from SQL
SELECT rfPkg.f_get_formatted_org_name(hrc_code,org_id) “Formatted Org Name”
FROM org_tab
ORDER BY hrc_code,org_id;

– Package rfPkg2
CREATE OR REPLACE PACKAGE rfPkg2
IS
FUNCTION f_get_formatted_org_name
(ip_hrc_code NUMBER,
ip_org_id NUMBER)
RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(f_get_formatted_org_name,WNDS,WNPS);
END rfPkg2;
/
CREATE OR REPLACE PACKAGE BODY rfPkg2
IS
FUNCTION f_get_formatted_org_name
(ip_hrc_code NUMBER,
ip_org_id NUMBER)
RETURN VARCHAR2
IS
v_name VARCHAR2(120);
v_hrc_descr VARCHAR2(20);
v_org_short_name VARCHAR2(30);
v_org_long_name VARCHAR2(60);
BEGIN
SELECT 'Org Name: (Short) ‘||org_short_name||’ (Long) '||org_long_name
INTO v_name
FROM org_tab
WHERE hrc_code = ip_hrc_code
AND org_id = ip_org_id;
SELECT hrc_descr
INTO v_hrc_descr
FROM hrc_tab
WHERE hrc_code = ip_hrc_code;
SELECT org_short_name,org_long_name
INTO v_org_short_name,v_org_long_name
FROM org_tab
WHERE hrc_code = ip_hrc_code
AND org_id = ip_org_id;
INSERT INTO sec_hrc_org_tab VALUES
(ip_hrc_code,v_hrc_descr,ip_org_id,
v_org_short_name,v_org_long_name);
RETURN (v_name);
END f_get_formatted_org_name;
END rfPkg2;
/

– Specifying the TRUST keyword
– Package dirPkg
CREATE OR REPLACE PACKAGE dirPkg IS
FUNCTION FileType(ip_file_name VARCHAR2)
RETURN VARCHAR2
IS LANGUAGE JAVA
NAME ‘dirClass.fileType(java.lang.String)return java.lang.String’;
PRAGMA RESTRICT_REFERENCES(FileType,WNDS,TRUST);

FUNCTION displayFileType(ip_file_name VARCHAR2)
RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(displayFileType,WNDS);
END dirPkg;
/
CREATE OR REPLACE PACKAGE BODY dirPkg IS
FUNCTION displayFileType(ip_file_name VARCHAR2)
RETURN VARCHAR2
IS
v_descr VARCHAR2(100);
BEGIN
v_descr :=ip_file_name||’ '||'is a '||FileType(ip_file_name);
RETURN (v_descr);
END displayFileType;
END dirPkg;
/

–Package dirPkg2
CREATE OR REPLACE PACKAGE dirPkg2 IS
FUNCTION FileType(ip_file_name VARCHAR2)
RETURN VARCHAR2
IS LANGUAGE JAVA
NAME ‘dirClass.fileType(java.lang.String)return java.lang.String’;

FUNCTION displayFileType(ip_file_name VARCHAR2)
RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(displayFileType,WNDS,TRUST);
END dirPkg2;
/
CREATE OR REPLACE PACKAGE BODY dirPkg2 IS
FUNCTION displayFileType(ip_file_name VARCHAR2)
RETURN VARCHAR2
IS
v_descr VARCHAR2(100);
BEGIN
v_descr :=ip_file_name||’ '||'is a '||FileType(ip_file_name);
RETURN (v_descr);
END displayFileType;
END dirPkg2;
/

– Parameter passing by reference
– Procedure p_nocopy
CREATE OR REPLACE PROCEDURE p_nocopy
(ip_1 IN NUMBER,
op_2 OUT NOCOPY VARCHAR2)
IS
BEGIN
NULL;
END;
/

– Performance improvement of NOCOPY
CREATE OR REPLACE PACKAGE NoCopyPkg
is
type arr is varray(100000)of hrc_tab%ROWTYPE;
procedure p1(ip1 IN OUT arr);
procedure p2(ip1 IN OUT NOCOPY arr);
FUNCTION get_time RETURN NUMBER;
END NoCopyPkg;
/
CREATE OR REPLACE PACKAGE BODY NoCopyPkg
is
PROCEDURE p1(ip1 IN OUT arr)
IS
BEGIN
NULL;
END;
PROCEDURE p2(ip1 IN OUT NOCOPY arr)
IS
BEGIN
NULL;
END;
FUNCTION get_time RETURN NUMBER
IS
BEGIN
RETURN (dbms_utility.get_time);
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20010,SQLERRM);
END get_time;
END NoCopyPkg;
/
declare
arr1 NoCopyPkg.arr :=NoCopyPkg.arr(null);
cur_t1 number;
cur_t2 number;
cur_t3 number;
begin
select * into arr1(1)from hrc_tab where hrc_code =1;
/*Create 99999 new elements in the variable array
and populate each with the value in the 1st element */
arr1.extend(99999,1);
cur_t1 :=NoCopyPkg.get_time;
NoCopyPkg.p1(arr1);
cur_t2 :=NoCopyPkg.get_time;
NoCopyPkg.p2(arr1);
cur_t3 :=NoCopyPkg.get_time;
dbms_output.put_line('Without NOCOPY '||to_char((cur_t2-cur_t1)/100));
dbms_output.put_line('With NOCOPY '||to_char((cur_t3-cur_t2)/100));
end;
/

– Invoker Rights Model
connect system/manager;
create user region1 identified by region1;
grant connect,resource to region1;
create user region2 identified by region2;
grant connect,resource to region2;

Create or replace Procedure create_dyn_table
(i_region_name VARCHAR2,
retcd OUT NUMBER,
errmsg OUT VARCHAR2)
authid current_user
is
cur_id INTEGER;
ret_code INTEGER;
dyn_string VARCHAR2(1000);
dyn_Table_name VARCHAR2(21);
Begin
dyn_table_name :=‘ORDERS_FOR_’||i_region_name;
dyn_string :='CREATE TABLE '||dyn_table_name||
‘(order_id NUMBER(10)PRIMARY KEY,
order_date DATE NOT NULL,
total_qty NUMBER,
total_price NUMBER(15,2))’;
cur_id :=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur_id,dyn_string,DBMS_SQL.V7);
ret_code :=DBMS_SQL.EXECUTE(cur_id);
DBMS_SQL.CLOSE_CURSOR(cur_id);
retcd :=0;
EXCEPTION WHEN OTHERS THEN
retcd :=SQLCODE;
errmsg :=‘ERR:Creating table ‘||dyn_table_name ||’-’||SQLERRM;
End;
/

– Overloading Packaged procedures and functions
– Package orgMaster2
CREATE OR REPLACE PACKAGE orgMaster2
IS
max_sites_for_an_org NUMBER;
TYPE rc IS REF CURSOR;
PROCEDURE createOrg (ip_hrc_code NUMBER,
ip_org_id NUMBER,
ip_org_short_name VARCHAR2,
ip_org_long_name VARCHAR2,
op_retcd OUT NUMBER,
op_err_msg OUT VARCHAR2);
PROCEDURE updateOrg(ip_org_id NUMBER,
ip_org_short_name VARCHAR2,
ip_org_long_name VARCHAR2,
op_retcd OUT NUMBER,
op_err_msg OUT VARCHAR2);
PROCEDURE removeOrg(ip_org_id NUMBER,
op_retcd OUT NUMBER,
op_err_msg OUT VARCHAR2);
FUNCTION getOrgDetails(ip_hrc_code NUMBER) RETURN rc;
FUNCTION getOrgDetails(ip_hrc_code NUMBER,ip_org_id NUMBER) RETURN rc;
PROCEDURE assignSiteToOrg(ip_org_id NUMBER,
ip_site_no NUMBER,
op_retcd OUT NUMBER,
op_err_msg OUT VARCHAR2);
END orgMaster2;
/
CREATE OR REPLACE PACKAGE BODY orgMaster2
IS
–Procedure to delete records from the org_site_tab table
–before deleting from org_table.This procedure is called
–from the removeOrg procedure
PROCEDURE removeOrgSites(ip_org_id NUMBER,
op_retcd OUT NUMBER,
op_err_msg OUT VARCHAR2)
IS
BEGIN
DELETE org_site_tab WHERE org_id =ip_org_id;
op_retcd :=0;
EXCEPTION WHEN OTHERS THEN
op_retcd :=SQLCODE;
op_err_msg :=SQLERRM;
END removeOrgSites;

–Procedure to create a record in the org_tab table
PROCEDURE createOrg (ip_hrc_code NUMBER,
ip_org_id NUMBER,
ip_org_short_name VARCHAR2,
ip_org_long_name VARCHAR2,
op_retcd OUT NUMBER,
op_err_msg OUT VARCHAR2)
IS
BEGIN
INSERT INTO org_tab VALUES
(ip_hrc_code,ip_org_id,ip_org_short_name,ip_org_long_name);
op_retcd :=0;
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
op_retcd :=-1;
op_err_msg :='Organization with Id '||TO_CHAR(ip_org_id)||
‘already exists.’;
WHEN OTHERS THEN
op_retcd :=SQLCODE;
op_err_msg :=SQLERRM;
END createOrg;

–Procedure to update the long and short names for a
–given org_id in the org_tab table
PROCEDURE updateOrg(ip_org_id NUMBER,
ip_org_short_name VARCHAR2,
ip_org_long_name VARCHAR2,
op_retcd OUT NUMBER,
op_err_msg OUT VARCHAR2)
IS
BEGIN
UPDATE org_tab
SET org_short_name =ip_org_short_name,
org_long_name =ip_org_long_name
WHERE org_id =ip_org_id;
IF (SQL%NOTFOUND) THEN
op_retcd :=-1;
op_err_msg :='Organization with Id '||TO_CHAR(ip_org_id)||
‘does not exist.’;
RETURN;
END IF;
op_retcd :=0;
EXCEPTION WHEN OTHERS THEN
op_retcd :=SQLCODE;
op_err_msg :=SQLERRM;
END updateOrg;

–Procedure to remove a record from the org_tab table
PROCEDURE removeOrg(ip_org_id NUMBER,
op_retcd OUT NUMBER,
op_err_msg OUT VARCHAR2)
IS
BEGIN
removeOrgSites(ip_org_id,op_retcd,op_err_msg);
IF (op_retcd <>0) then
RETURN;
END IF;
DELETE org_tab WHERE org_id =ip_org_id;
IF (SQL%NOTFOUND) THEN
op_retcd :=-1;
op_err_msg :='Organization with Id '||TO_CHAR(ip_org_id)||
‘does not exist.’;
RETURN;
END IF;
op_retcd :=0;
EXCEPTION WHEN OTHERS THEN
op_retcd :=SQLCODE;
op_err_msg :=SQLERRM;
END removeOrg;

–Over-loaded function that returns a row from the org_tab table
–based on input hrc_code and org_id
FUNCTION getOrgDetails(ip_hrc_code NUMBER,ip_org_id NUMBER)RETURN rc
IS
v_rc rc;
BEGIN
OPEN v_rc FOR SELECT * FROM org_tab
WHERE hrc_code =ip_hrc_code
AND org_id =ip_org_id;
RETURN (v_rc);
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,SQLERRM);
END getOrgDetails;

–Over-loaded function that returns all row from the org_tab table
–for an input hrc_code.This function has the same name as the function
–above,but the number of parameters is different in each case.
FUNCTION getOrgDetails(ip_hrc_code NUMBER)RETURN rc
IS
v_rc rc;
BEGIN
OPEN v_rc FOR SELECT * FROM org_tab WHERE hrc_code =ip_hrc_code;
RETURN (v_rc);
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002,SQLERRM);
END getOrgDetails;

–Procedure to create a row in the org_site_tab table
–based on input org_id and site_no
PROCEDURE assignSiteToOrg(ip_org_id NUMBER,
ip_site_no NUMBER,
op_retcd OUT NUMBER,
op_err_msg OUT VARCHAR2)
IS
v_num NUMBER;
BEGIN
BEGIN
SELECT 1
INTO v_num
FROM org_site_tab
WHERE org_id =ip_org_id
AND site_no =ip_site_no;
IF (v_num =1) THEN
op_retcd :=0;
RETURN;
END IF;
EXCEPTION WHEN NO_DATA_FOUND THEN
INSERT INTO org_site_tab VALUES (ip_org_id,ip_site_no);
END;
op_retcd :=0;
EXCEPTION WHEN OTHERS THEN
op_retcd :=SQLCODE;
op_err_msg :=SQLERRM;
END assignSiteToOrg;

–Initialization section for the package
BEGIN
max_sites_for_an_org :=4;
END orgMaster2;
/
DECLARE
v_rc orgMaster2.rc;
org_rec org_tab%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Org Details for Org 1001’);
DBMS_OUTPUT.PUT_LINE(’------------------’);
v_rc :=orgMaster2.getOrgDetails(1,1001);
FETCH v_rc INTO org_rec;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(org_rec.hrc_code)||’ ‘||
TO_CHAR(org_rec.org_id)||’’||
org_rec.org_short_name||’ ‘||
org_rec.org_long_name);
DBMS_OUTPUT.PUT_LINE(‘Org Details for Hierarchy 1’);
DBMS_OUTPUT.PUT_LINE(’------------------’);
v_rc :=orgMaster2.getOrgDetails(1);
LOOP
FETCH v_rc INTO org_rec;
EXIT WHEN v_rc%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(org_rec.hrc_code)||’ ‘||
TO_CHAR(org_rec.org_id)||’ ‘||
org_rec.org_short_name||’ '||
org_rec.org_long_name);
END LOOP;
CLOSE v_rc;
END;
/

– Serially Reusable Packages
– Package srPkg1
CREATE OR REPLACE PACKAGE srPkg1
IS
PRAGMA SERIALLY_REUSABLE;
num_var NUMBER;
char_var VARCHAR2(20);
PROCEDURE initialize;
FUNCTION display_num RETURN NUMBER;
FUNCTION display_char RETURN VARCHAR2;
END srPkg1;
/
CREATE OR REPLACE PACKAGE BODY srPkg1
IS
PRAGMA SERIALLY_REUSABLE;
PROCEDURE initialize
IS
BEGIN
num_var :=100;
char_var :=‘Test String1’;
END;
FUNCTION display_num RETURN NUMBER
IS
BEGIN
RETURN (num_var);
END;
FUNCTION display_char RETURN VARCHAR2
IS
BEGIN
RETURN (char_var);
END;
END srPkg1;
/
DECLARE
v_num NUMBER;
v_char VARCHAR2(20);
BEGIN
srPkg1.initialize;
v_num :=srPkg1.display_num;
v_char :=srPkg1.display_char;
dbms_output.put_line(TO_CHAR(v_num)||’ ‘||v_char);
END;
/
CREATE OR REPLACE PACKAGE srPkg2
IS
PRAGMA SERIALLY_REUSABLE;
CURSOR csr_sites IS
SELECT * from site_tab ORDER BY site_no;
PROCEDURE displaySites;
END srPkg2;
/
CREATE OR REPLACE PACKAGE BODY srPkg2
IS
PRAGMA SERIALLY_REUSABLE;
PROCEDURE displaySites
IS
site_rec site_tab%ROWTYPE;
BEGIN
OPEN csr_sites;
FETCH csr_sites INTO site_rec;
dbms_output.put_line(TO_CHAR(site_rec.site_no)||’ ‘||
site_rec.site_descr);
FETCH csr_sites INTO site_rec;
dbms_output.put_line(TO_CHAR(site_rec.site_no)||’ '||
site_rec.site_descr);
END displaySites;
END srPkg2;
/
BEGIN
srPkg2.displaySites;
END;
/

–游标.txt
– 1 定义游标
DECLARE
CURSOR c1 IS
SELECT sname,cno
FROM student,sc
WHERE student.sno=sc.sno
ORDER by 1;
v_sname VARCHAR2(20);
v_cno VARCHAR2(30);
BEGIN
/……/
null;
END;3
/

– 2 打开游标
DECLARE
CURSOR c1 IS
SELECT sname,cno
FROM student,sc
WHERE student.sno=sc.sno
ORDER by 1;
v_sname VARCHAR2(20);
v_cno VARCHAR2(30);
BEGIN
OPEN c1;
/……/
null;
END;
/

– 3 读取游标(Fetch)
DECLARE
CURSOR c1 IS
SELECT sname,cno
FROM student,sc
WHERE student.sno=sc.sno
ORDER by 1;
v_sname VARCHAR2(20);
v_cno VARCHAR2(30);
BEGIN
OPEN c1;
FETCH c1 INTO v_sname,v_cno;
–This fetch fetches the first row in the active set.
null;
END;
/

– 4 在游标中使用ROWTYPE型变量
DECLARE
CURSOR c1 IS
SELECT sname,cno
FROM student,sc
WHERE student.sno=sc.sno
ORDER by 1;
v_s c1%ROWTYPE;
BEGIN
OPEN c1;
FETCH c1 INTO v_s;
–This fetch fetches the first row in the active set.
null;
END;
/

– 5 遍历游标（Fetch）Loop…end loop
DECLARE
/*Declare a cursor explicitly */
CURSOR c1 IS
SELECT sname,cno
FROM student,sc
WHERE student.sno=sc.sno
ORDER by 1;
v_s c1%ROWTYPE;
BEGIN
/*Open the cursor */
OPEN c1;
/*Format headings */
dbms_output.put_line(‘学生选课信息’);
dbms_output.put_line(’------------------------’);
dbms_output.put_line(rpad(‘学生姓名’,20,’ ‘)||’ ‘||rpad(‘所选课程号’,30,’ ‘));
dbms_output.put_line(rpad(’-’,20,’-’)||’ ‘||rpad(’-’,30,’-’));
/Fetch from the cursor resultset in a loop and display the results/
LOOP
FETCH c1 INTO v_s;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line(rpad(v_s.sname,20,’ ‘)||’ ‘||
rpad(v_s.cno,30,’ '));
END LOOP;
/*CLose the cursor */
CLOSE c1;
END;
/

– 6 遍历游标（Fetch）While…End Loop
DECLARE
CURSOR c1 IS
SELECT sname,cno
FROM student,sc
WHERE student.sno=sc.sno
ORDER by 1;
v_s c1%ROWTYPE;
BEGIN
OPEN c1;
dbms_output.put_line(‘学生选课信息’);
dbms_output.put_line(’------------------------’);
dbms_output.put_line(rpad(‘学生姓名’,20,’ ‘)||’ ‘||rpad(‘所选课程号’,30,’ ‘));
dbms_output.put_line(rpad(’-’,20,’-’)||’ ‘||rpad(’-’,30,’-’));
FETCH c1 INTO v_s;
WHILE (c1%FOUND) LOOP
dbms_output.put_line(rpad(v_s.sname,20,’ ‘)||’ ‘||
rpad(v_s.cno,30,’ '));
FETCH c1 INTO v_s;
END LOOP;
CLOSE c1;
END;
/

– 7 遍历游标（Fetch）For…End Loop
DECLARE
CURSOR c1 IS
SELECT sname,cno
FROM student,sc
WHERE student.sno=sc.sno
ORDER by 1;
BEGIN
dbms_output.put_line(‘学生选课信息’);
dbms_output.put_line(’------------------------’);
dbms_output.put_line(rpad(‘学生姓名’,20,’ ‘)||’ ‘||rpad(‘所选课程号’,30,’ ‘));
dbms_output.put_line(rpad(’-’,20,’-’)||’ ‘||rpad(’-’,30,’-’));
FOR idx IN c1 LOOP
dbms_output.put_line(rpad(idx.sname,20,’ ‘)||’ ‘||
rpad(idx.cno,30,’ '));
END LOOP;
END;
/

– 8 在游标中使用%IsOpen属性
DECLARE
CURSOR c1 IS
SELECT sname,cno
FROM student,sc
WHERE student.sno=sc.sno
ORDER by 1;
v_s c1%ROWTYPE;
BEGIN
IF (NOT c1%ISOPEN) THEN
OPEN c1;
END IF;
dbms_output.put_line(‘学生选课信息’);
dbms_output.put_line(’------------------------’);
dbms_output.put_line(rpad(‘学生姓名’,20,’ ‘)||’ ‘||rpad(‘所选课程号’,30,’ ‘));
dbms_output.put_line(rpad(’-’,20,’-’)||’ ‘||rpad(’-’,30,’-’));
FETCH c1 INTO v_s;
WHILE (c1%FOUND)LOOP
dbms_output.put_line(rpad(v_s.sname,20,’ ‘)||’ ‘||
rpad(v_s.cno,30,’ '));
FETCH c1 INTO v_s;
END LOOP;
IF (c1%ISOPEN)THEN
CLOSE c1;
END IF;
END;
/

– 9 在游标中使用%ROWCOUNT属性
DECLARE
CURSOR c1 IS
SELECT sname,cno
FROM student,sc
WHERE student.sno=sc.sno
ORDER by 1;
num_total_rows NUMBER;
BEGIN
dbms_output.put_line(‘学生选课信息’);
dbms_output.put_line(’------------------------’);
dbms_output.put_line(rpad(‘学生姓名’,20,’ ‘)||’ ‘||rpad(‘所选课程号’,30,’ ‘));
dbms_output.put_line(rpad(’-’,20,’-’)||’ ‘||rpad(’-’,30,’-’));
FOR idx IN c1 LOOP
dbms_output.put_line(rpad(idx.sname,20,’ ‘)||’ ‘||rpad(idx.cno,30,’ '));
num_total_rows :=c1%ROWCOUNT;
END LOOP;
IF num_total_rows >0 THEN
dbms_output.new_line;
dbms_output.put_line('总选课数 = '||to_char(num_total_rows));
END IF;
END;
/

– 10 在游标中使用%ROWCOUNT属性（%ROWCOUNT属性是递增的）
DECLARE
CURSOR c1 IS
SELECT sname,cno
FROM student,sc
WHERE student.sno=sc.sno
ORDER by 1;
num_total_rows NUMBER;
BEGIN
FOR idx IN c1 LOOP
IF c1%ROWCOUNT =1 THEN
dbms_output.put_line(‘学生选课信息’);
dbms_output.put_line(’------------------------’);
dbms_output.put_line(rpad(‘学生姓名’,20,’ ‘)||’ ‘||rpad(‘所选课程号’,30,’ ‘));
dbms_output.put_line(rpad(’-’,20,’-’)||’ ‘||rpad(’-’,30,’-’));
END IF;
dbms_output.put_line(rpad(idx.sname,20,’ ‘)||’ ‘||rpad(idx.cno,30,’ '));
num_total_rows :=c1%ROWCOUNT;
END LOOP;
IF num_total_rows >0 THEN
dbms_output.new_line;
dbms_output.put_line('总选课数 = '||to_char(num_total_rows));
END IF;
END;
/

– 11 带参数的游标Loop
DECLARE
CURSOR c1(v_sno varchar2) IS
SELECT sname,cno
FROM student,sc
WHERE student.sno=sc.sno
AND trim(sc.sno)=v_sno
ORDER by 1;
v_s c1%ROWTYPE;
BEGIN
OPEN c1(‘001’);
dbms_output.put_line(‘学生选课信息 001’);
dbms_output.put_line(’------------------------’);
dbms_output.put_line(rpad(‘学生姓名’,20,’ ‘)||’ ‘||rpad(‘所选课程号’,30,’ ‘));
dbms_output.put_line(rpad(’-’,20,’-’)||’ ‘||rpad(’-’,30,’-’));
LOOP
FETCH c1 INTO v_s;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line(rpad(v_s.sname,20,’ ‘)||’ ‘||rpad(v_s.cno,30,’ ‘));
END LOOP;
CLOSE c1;
OPEN c1(‘002’);
dbms_output.put_line(‘学生选课信息 002’);
dbms_output.put_line(’------------------------’);
dbms_output.put_line(rpad(‘学生姓名’,20,’ ‘)||’ ‘||rpad(‘所选课程号’,30,’ ‘));
dbms_output.put_line(rpad(’-’,20,’-’)||’ ‘||rpad(’-’,30,’-’));
LOOP
FETCH c1 INTO v_s;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line(rpad(v_s.sname,20,’ ‘)||’ ‘||rpad(v_s.cno,30,’ '));
END LOOP;
CLOSE c1;
END;
/

– 12 带参数的游标 FOR LOOP
DECLARE
CURSOR c1(v_sno varchar2) IS
SELECT sname,cno
FROM student,sc
WHERE student.sno=sc.sno
AND trim(sc.sno)=v_sno
ORDER by 1;
v_s c1%ROWTYPE;
BEGIN
dbms_output.put_line(‘学生选课信息 001’);
dbms_output.put_line(’------------------------’);
dbms_output.put_line(rpad(‘学生姓名’,20,’ ‘)||
‘’||rpad(‘所选课程号’,30,’ ‘));
dbms_output.put_line(rpad(’-’,20,’-’)||’ ‘||rpad(’-’,30,’-’));
FOR idx in c1(‘001’) LOOP
dbms_output.put_line(rpad(idx.sname,20,’ ‘)||’ ‘||
rpad(idx.cno,30,’ ‘));
END LOOP;
dbms_output.put_line(‘学生选课信息 2’);
dbms_output.put_line(’------------------------’);
dbms_output.put_line(rpad(‘学生姓名’,20,’ ‘)||’ ‘||
rpad(‘所选课程号’,30,’ ‘));
dbms_output.put_line(rpad(’-’,20,’-’)||’ ‘||rpad(’-’,30,’-’));
FOR idx in c1(‘002’) LOOP
dbms_output.put_line(rpad(idx.sname,20,’ ‘)||’ ‘||
rpad(idx.cno,30,’ '));
END LOOP;
END;
/

– 13 使用了 SELECT FOR UPDATE 的游标
Create table s1 as select * from student where 1=2;

BEGIN
INSERT INTO s1
SELECT *FROM student;
COMMIT;
END;
/

DECLARE
CURSOR csr_1 IS
SELECT * FROM s1 FOR UPDATE OF sname;
v_sname VARCHAR2(20);
BEGIN
FOR idx IN csr_1 LOOP
v_sname :=UPPER(idx.sname);
UPDATE s1
SET sname =v_sname
WHERE CURRENT OF csr_1;
END LOOP;
COMMIT;
END;
/

– 14 隐式游标
create table sc1 as select * from sc;

BEGIN
DELETE from sc1 WHERE sno>=‘005’;
INSERT INTO sc1(sno,cno)
SELECT sno,‘c03’
FROM student
WHERE sdept=‘CS’;
IF (SQL%FOUND)THEN
dbms_output.put_line(TO_CHAR(SQL%ROWCOUNT)||’ rows inserted into sc1’);
END IF;
COMMIT;
exception when others then
dbms_output.put_line(sqlerrm);
END;
/

– 15 使用隐式游标的SQL%NOTFOUND属性
create table sc_number(sno char(10),selCnt number)
/

DECLARE
v_num_rows NUMBER;
BEGIN
DELETE From sc1 WHERE sno =‘001’;
INSERT INTO sc1(sno,cno)
SELECT ‘001’,cno
FROM course
WHERE credit>2;
v_num_rows :=SQL%ROWCOUNT;
IF (SQL%FOUND)THEN
UPDATE sc_number
SET selCnt =v_num_rows
WHERE sno=‘001’;
IF (SQL%NOTFOUND)THEN
INSERT INTO sc_number VALUES (‘001’,v_num_rows);
END IF;
END IF;
COMMIT;
END;
/

– 16 使用隐式游标的SQL%ROWCOUNT属性
DECLARE
v_num_rows NUMBER;
BEGIN
DELETE From sc1 WHERE sno=‘001’;
INSERT INTO sc1(sno,cno)
SELECT ‘001’,cno
FROM course
WHERE credit>2;
v_num_rows :=SQL%ROWCOUNT;
IF (SQL%FOUND)THEN
UPDATE sc_number
SET selCnt =v_num_rows
WHERE sno=‘001’;
IF (SQL%ROWCOUNT=0)THEN
INSERT INTO sc_number VALUES (‘001’,v_num_rows);
END IF;
END IF;
COMMIT;
END;
/

– 17 游标变量
DECLARE
TYPE rc is REF CURSOR;
v_rc rc;
BEGIN
OPEN v_rc FOR SELECT from sc;
/…FETCH the results and process the resultset */
null;
END;
/

– 18 打开并遍历游标变量Loop
DECLARE
TYPE rc is REF CURSOR;
v_rc rc;
v_sc sc%ROWTYPE;
BEGIN
OPEN v_rc FOR SELECT from sc;
LOOP
FETCH v_rc INTO v_sc;
EXIT WHEN v_rc%NOTFOUND;
/…Process the individual records */
null;
END LOOP;
END;
/

– 19 使用游标变量
DECLARE
TYPE rc is REF CURSOR;
v_rc rc;
v_sc sc%ROWTYPE;
BEGIN
OPEN v_rc FOR SELECT *from sc;
dbms_output.put_line(‘选课信息’);
dbms_output.put_line(’------------------------’);
dbms_output.put_line(‘学号’||’ ‘||rpad(‘课程号’,20,’ ‘));
dbms_output.put_line(rpad(’-’,4,’-’)||’ ‘||rpad(’-’,20,’-’));
LOOP
FETCH v_rc INTO v_sc;
EXIT WHEN v_rc%NOTFOUND;
dbms_output.put_line(to_char(v_sc.sno)||’ ‘||rpad(v_sc.cno,20,’ '));
END LOOP;
CLOSE v_rc;
END;
/

– 20 游标变量相互赋值
DECLARE
TYPE rc is REF CURSOR;
v_rc1 rc;
v_rc2 rc;
v_sc sc%ROWTYPE;
BEGIN
OPEN v_rc1 FOR SELECT *from sc;
dbms_output.put_line(‘选课信息’);
dbms_output.put_line(’------------------------’);
dbms_output.put_line(‘学号’||’ ‘||rpad(‘课程号’,20,’ ‘));
dbms_output.put_line(rpad(’-’,4,’-’)||’ ‘||rpad(’-’,20,’-’));
/*Assign v_rc1 to v_rc2 */
v_rc2 :=v_rc1;
LOOP
/*Fetch from the second cursor variable,i.e.,v_rc2 */
FETCH v_rc2 INTO v_sc;
EXIT WHEN v_rc2%NOTFOUND;
dbms_output.put_line(to_char(v_sc.sno)||’ ‘||rpad(v_sc.cno,20,’ '));
END LOOP;
CLOSE v_rc2;
END;
/

– 21 使用系统弱游标类型：SYS_REFCURSOR

DECLARE
v_rc SYS_REFCURSOR;
v_sc sc%ROWTYPE;
BEGIN
OPEN v_rc FOR SELECT *from sc;
dbms_output.put_line(‘选课信息’);
dbms_output.put_line(’------------------------’);
dbms_output.put_line(‘学号’||’ ‘||rpad(‘课程号’,20,’ ‘));
dbms_output.put_line(rpad(’-’,4,’-’)||’ ‘||rpad(’-’,20,’-’));
LOOP
FETCH v_rc INTO v_sc;
EXIT WHEN v_rc%NOTFOUND;
dbms_output.put_line(to_char(v_sc.sno)||’ ‘||rpad(v_sc.cno,20,’ '));
END LOOP;
CLOSE v_rc;
END;
/

– 22 使用同一个游标打开多次查询
DECLARE
TYPE rc is REF CURSOR;
v_rc rc;
v_sc sc%ROWTYPE;
v_sname VARCHAR2(20);
v_cno VARCHAR2(30);
BEGIN
OPEN v_rc FOR SELECT *from sc;
dbms_output.put_line(‘选课信息’);
dbms_output.put_line(’------------------------’);
dbms_output.put_line(‘学号’||’ ‘||rpad(‘课程号 ‘,20,’ ‘));
dbms_output.put_line(rpad(’-’,4,’-’)||’ ‘||rpad(’-’,20,’-’));
LOOP
FETCH v_rc INTO v_sc;
EXIT WHEN v_rc%NOTFOUND;
dbms_output.put_line(to_char(v_sc.sno)||’ ‘||rpad(v_sc.cno,20,’ ‘));
END LOOP;
OPEN v_rc FOR SELECT sname,cno
FROM student,sc
WHERE student.sno=sc.sno;
dbms_output.put_line(‘学生选课信息’);
dbms_output.put_line(’------------------------’);
dbms_output.put_line(rpad(‘学生姓名’,20,’ ‘)||’ ‘||rpad(‘课程号’,30,’ ‘));
dbms_output.put_line(rpad(’-’,20,’-’)||’ ‘||rpad(’-’,30,’-’));
LOOP
FETCH v_rc INTO v_sname,v_cno;
EXIT WHEN v_rc%NOTFOUND;
dbms_output.put_line(rpad(v_sname,20,’ ‘)||’ ‘||rpad(v_cno,30,’ '));
END LOOP;
CLOSE v_rc;
END;
/

–异常处理.txt
– 1 预定义异常处理
DECLARE
v_sname VARCHAR2(20);
BEGIN
SELECT sname INTO v_sname
FROM student
WHERE sno=‘001’;
dbms_output.put_line('学号为001的学生的姓名: '||v_sname);
EXCEPTION WHEN NO_DATA_FOUND THEN
dbms_output.put_line(‘ERR:Invalid Student NO 001’);
END;
/

– 2 Others异常处理
DECLARE
v_sname VARCHAR2(20);
BEGIN
SELECT sname
INTO v_sname
FROM student
WHERE sno = ‘001’;
dbms_output.put_line('学号为001的学生的姓名: '||v_sname);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(‘ERR:Invalid Student NO 001’);
END;
/

– 3 使用NO_DATA_FOUND 和 WHEN OTEHRS 异常处理
DECLARE
v_sname VARCHAR2(20);
BEGIN
SELECT sname
INTO v_sname
FROM student
WHERE sno = ‘001’;
dbms_output.put_line('学号为001的学生的姓名: '||v_sname);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line(‘ERR:Invalid Student NO 001’);
WHEN OTHERS THEN
dbms_output.put_line(‘ERR:An error occurred’);
END;
/

– 4 使用SQLERRM（系统错误提示）和SQLCODE（系统错误代码）
DECLARE
v_sname VARCHAR2(20);
BEGIN
SELECT sname
INTO v_sname
FROM student
WHERE sno = ‘001’;
dbms_output.put_line('学号为001的学生的姓名: ‘||v_sname);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line(‘ERR:Invalid Student NO 001’);
WHEN OTHERS THEN
dbms_output.put_line(‘ERR:An error occurred with info :’||TO_CHAR(SQLCODE)||’ '||SQLERRM);
END;
/

– 5 嵌套程序中捕获异常
DECLARE
v_sname VARCHAR2(20);
BEGIN
BEGIN
SELECT sname
INTO v_sname
FROM student
WHERE sno = ‘001’;
dbms_output.put_line('The lowest Student NO is: 001 '||v_sname);
EXCEPTION WHEN NO_DATA_FOUND THEN
INSERT INTO student(sno,sname) VALUES (‘001’,‘Smith’);
COMMIT;
END;
BEGIN
SELECT sname
INTO v_sname
FROM student
WHERE sno=‘010’;
dbms_output.put_line('The highest Student NO is: Code 010 ‘||v_sname);
EXCEPTION WHEN NO_DATA_FOUND THEN
dbms_output.put_line(‘ERR:Invalid Data for Student NO 010’);
END;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘ERR:An error occurred with info :’||TO_CHAR(SQLCODE)||’ '||SQLERRM);
END;
/

– 6 捕获用户自定义异常
DECLARE
exp_no001 EXCEPTION; – a user-defined exception
v_cnt NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_cnt
FROM sc
WHERE sno=‘001’;
IF (v_cnt=0)THEN
–explicitly raising the user-defined exception
RAISE exp_no001;
END IF;
EXCEPTION
–handling the raised user-defined exception
WHEN exp_no001 THEN
dbms_output.put_line(‘There are no Student NO 001’);
WHEN OTHERS THEN
dbms_output.pu t_line(‘ERR:An error occurred with info :’||TO_CHAR(SQLCODE)||’ '||SQLERRM);
END;
/

– 7 捕获Declare（声明变量）中的异常
BEGIN
DECLARE
v_num NUMBER(2):=100;
BEGIN
/*…Do some processing /
null;
EXCEPTION
WHEN VALUE_ERROR THEN
/…Handle the error /
NULL;
WHEN OTHERS THEN
dbms_output.put_line(‘ERR:An error occurred with info :’||TO_CHAR(SQLCODE)||’ '||SQLERRM);
END;
EXCEPTION
WHEN VALUE_ERROR THEN
/…Handle the error */
dbms_output.put_line(‘Value error occurred’);
WHEN OTHERS THEN
dbms_output.put_line(‘ERR:An error occurred with info :’||TO_CHAR(SQLCODE)||’ '||SQLERRM);
END;
/

–PLSQL.txt
–1 简单程序块
DECLARE
v_sno VARCHAR2(6);
v_cno CHAR(10);
v_grade int;
BEGIN
v_sno :=‘001’;
v_cno :=‘C01’;
select avg(grade) into v_grade from sc where cno=v_cno;
INSERT INTO sc(sno,cno,grade) VALUES (v_sno,v_cno,v_grade);
commit;
END;
/

– 2 An example of the IF statement
DECLARE
a number :=50;
b number :=-20;
BEGIN
IF (a>b)THEN
dbms_output.put_line(‘A is greater than B’);
ELSIF (a<b)THEN
dbms_output.put_line(‘A is < than B’);
ELSE
dbms_output.put_line(‘A is equal to B’);
END IF;
END;
/

– 3 An example of the simple LOOP
DECLARE
line_length NUMBER :=50;
seperator VARCHAR2(1):=’=’;
actual_line VARCHAR2(150);
i NUMBER :=1;
BEGIN
LOOP
actual_line :=actual_line ||seperator;
EXIT WHEN i =line_length;
i:=i +1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(actual_line);
END;
/

– 4 A numeric FOR LOOP
DECLARE
line_length NUMBER :=50;
seperator VARCHAR2(1):=’=’;
actual_line VARCHAR2(150);
BEGIN
FOR idx in 1…line_length LOOP
actual_line :=actual_line ||seperator;
END LOOP;
DBMS_OUTPUT.PUT_LINE(actual_line);
END;
/

– 5 An example using a WHILE LOOP
DECLARE
line_length NUMBER :=50;
seperator VARCHAR2(1):=’=’;
actual_line VARCHAR2(150);
idx NUMBER :=1;
BEGIN
WHILE (idx<=line_length)LOOP
actual_line :=actual_line ||seperator;
idx :=idx +1 ;
END LOOP;
DBMS_OUTPUT.PUT_LINE(actual_line);
END;
/

– 6 An example of comparison of two numbers
– using a searched CASE expression
declare
a number :=20;
b number :=-40;
string varchar2(50);
begin
string :=case
when (a>b)then ‘A is greater than B’
when (a<b)then ‘A is less than B’
else
‘A is equal to B’
end;
dbms_output.put_line(string);
end;
/

–7 块嵌套
DECLARE
v_sno VARCHAR2(6);
v_sname VARCHAR2(20);
v_num NUMBER(1);
BEGIN
v_sno :=‘001’;
v_sname :=‘张三’;
BEGIN
SELECT 1
INTO v_num
FROM student
WHERE trim(sno) =v_sno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_num :=0;
WHEN OTHERS THEN
dbms_output.put_line(‘Error in SELECT:’||SQLERRM);
RETURN;
END;
IF (v_num =0)THEN
INSERT INTO student(sno,sname) VALUES (v_sno,v_sname);
Commit;
END IF;
dbms_output.put_line(‘Successful Completion’);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
/

沉梦昂志丶丶
关注
  • 0
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
android设置无标题栏 、 状态栏
z1web的博客
11-22 6565
设置无标题栏，状态栏的方式有三种 第一种：在java文件中修改(注意：如果改成隐藏标题栏，那么MainActivity必须继承Activity) public class MainActivity extends Activity { @Override protected void onCreate(Bundle savedInstanceState) { ...
Qt-无标题窗口
学而思
05-21 7233
        今天学习了下无标题窗口，于是来记录下我的学习过程首先建立一个Qt Widgets Application继承QWidget类创建完后直接运行就得到了如下效果那么，既然是无标题窗口就要去掉窗口栏啊，于是加上setWindowFlags(Qt::FramelessWindowHint | Qt::WindowMinimizeButtonHint);去掉标题栏，但是这样整个窗口就不能移动...
C# 移动无标题栏无边框窗体的3种方法
SabreWulf
03-13 2223
目录 第一种：手工移动 第二种：调用系统API 1.引入下面代码 前提需要引入命名空间using System.Runtime.InteropServices 2.增加鼠标按下事件发送消息，让系统误以为按下是标题栏 第三种，重写 WndProc 第一种：手工移动 直接通过修改窗体位置从而达到移动窗体的效果 //定义一个位置信息Point用于存储鼠标位置 private Point mPoint; /// <summary>
Qt实现改变无标题栏窗体大小及移动窗体
danshiming的博客
11-10 2919
Qt实现鼠标左键拖动无标题栏窗体右下角改变窗体大小及移动窗体
Chrome 无标题 哭脸 已崩溃
wkj52719的博客
05-24 4162
谷歌浏览器chrome突然打开后任何页面均显示为无标题，哭脸。鼠标选中sheet页面时，提示已崩溃。 无法打开设置、开发工具箱 经过检查，是chrome的Userdata数据出错，导致页面加载无法正常展示页面。 修复办法： 打开文件夹C:\Users\{privateUserId}\AppData\Local\Google\Chrome，删除User data文件夹，如果需要备份UserData,请备份User Data到其他地方。 ...
qt无标题栏设置
xiatiankuaizou的专栏
01-25 3865
1.设置没有标题栏 this-&gt;setWindowFlags(Qt::FramelessWindowHint); 2.鼠标移动大小 void Dialog::resizeEvent(QResizeEvent *event) { ui-&gt;Button-&gt;setGeometry(0, 0, (82*this-&gt;width())/1366, (48*this-&...
如何用Qt实现一个无标题栏、半透明、置顶（悬浮）的窗口
xktesla的专栏
02-17 1807
在Qt框架中，要实现一个无标题栏、半透明、置顶（悬浮）的窗口，需要一些特定的设置和技巧。废话不多说，下面我将以DrawClient软件为例，介绍一下实现这种效果的四个要点。在窗口的构造函数中设置窗口的样式，在强调一下，一定要找构造函数中设置，否则有些平台可能无效。
【QT学习】如何移动无标题栏窗口?（图文详解）
孤生i的博客
10-05 1739
本篇，作者将分享如何移动无标题栏的窗口。
pb powerbuiler 无标题栏窗口的移动
xiaox的专栏
07-23 1303
在使用 response! 或 popup! 窗口 的时候，经常会希望关闭标题栏（例图1），但关闭了标题栏，这个窗口就无法移动了。  （图1） 让无标题栏窗口的移动的设置方法： 1、在窗口中新建一个自定义事件(例图2，事件名 ue_cnhit)，event id 选择pbm_cnhittest 2、添加脚本： return 2  3、鼠标点击窗口没有控件的位置，长按左键，就可以移动窗...
VC去掉'无标题'字样的方法.zip
11-21
Visual c++去掉'无标题'字样的方法.zip
Qt无标题窗口
05-21
一个比较简单的Qt无标题窗口，基本实现了现在默认窗口自带的功能，可以用于界面美化自绘标题栏。
无标题
02-12
无标题
visual c++取掉对话框窗口的 -无标题 字样的方法
08-04
vc++取掉窗口的 -无标题 字样
【RLHF个人笔记】RLHF:Reinforcement Learning from Human Feedback具体过程
几度热忱的博客
05-29 729
【RLHF个人笔记】RLHF:Reinforcement Learning from Human Feedback具体过程 RLHF训练的三个步骤 步骤1：收集数据与有监督训练策略 步骤2：收集数据训练奖励模型 步骤3：结合奖励模型利用强化学习算法如PPO算法来优化策略 参考内容
基础—SQL—DQL（数据查询语言）聚合函数
最新发布
m0_74363339的博客
05-30 479
这篇博客主要讲到了SQL分类中的DQL（数据查询语言）—聚合函数的讲解，聚合函数比较简单，但它主要配合着分组查询进行数据查询的操作，注意NULL的字段值不参与聚合函数的运算......
postgressql——Tuple学习（2）
Re_view的博客
05-27 351
postgressql——Tuple学习（2）
PostgreSQL发展史
PGCCC的博客
05-28 1025
PostgreSQL 的发展史反映了其从学术研究项目到全球领先的开源数据库系统的演变过程。通过不断的创新和社区支持，PostgreSQL 已经成为一个功能强大、性能优越的数据库管理系统，广泛应用于各个行业的关键业务场景中。#postgresql培训。
SQL开窗函数
zwldn的博客
05-21 1572
数据分析必备函数：开窗函数提供了在查询结果中进行排序、排名、聚合和分析的灵活性，在数据分析和报表生成中非常有用，可以实现更复杂的计算和分析需求。
设置无标题 android
05-02
在Android应用中，设置无标题是一种简洁和美观的设计方式，可以为用户提供更流畅的使用体验。以下是一些实现设置无标题的方法： 1. 在Android的Manifest文件中，找到对应Activity的标签，并添加”android:theme="@...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值