oracle实例练习Word下载.docx

oracle实例练习Word下载.docx

《oracle实例练习Word下载.docx》由会员分享，可在线阅读，更多相关《oracle实例练习Word下载.docx（16页珍藏版）》请在冰豆网上搜索。

,to_date（'

1980-08-20'

yyyy-mm-dd'

）,'

济南'

）;

002'

小王'

男'

1983-04-01'

莱芜'

003'

小李'

1980-05-20'

004'

小赵'

005'

小孔'

1982-06-18'

威海'

insertintoscorevalues（'

90'

92'

85'

79'

80'

94'

78'

77'

800'

88'

5.a统计各个地区的学生数

b计算各个学生的总成绩（数学+英语），并且按照成绩由高到低做出学生的成绩单报告（没考试的学生名字不要出现在报告单上,进修生的成绩也不在报告单上）

报告单标题显示：

学号姓名数学英语总成绩

c计算各个学生的总成绩（数学+英语），并且按照成绩由高到低做出学生的成绩单报告（没考试的学生名字也要出现在报告单上,进修生的成绩不在报告单上）

selectsadd地区,count（*）as人数fromstudentgroupbysadd

selectstudent.sno学号,sname姓名,math数学,english英语,（math+english）总成绩fromstudentinnerjoinscoreonscorewherestudent.sno=score.snoorderby总成绩desc

selectstudent.sno学号,sname姓名,math数学,english英语,math+english总成绩fromstudentleftouterjoinscoreonstudent.sno=score.snoorderby总成绩desc

或selectstudent.sno学号,sname姓名,math数学,english英语,（math+english）总成绩fromstudent,scorewherestudent.sno=score.sno（+）orderby总成绩desc;

6.根据student表，创建一个新表student_copy（结构相同，数据只有济南的两个学生）

从student表中查出莱芜得同学信息，插入到student_copy表中

commit//提交刚才的插入

createtablestudent_copyasselect*fromstudentWheresadd='

insertintostudent_copy（select*fromstudentwheresadd='

commit;

7.插入一条新的学生纪录：

006小林男1979-7-9泰安

savepointa//设置保存点a

删除掉学号为003的学生纪录（误删）

rollbacktosavepointa

察看结果

commit（提交插入纪录的操作）/rollback（回滚到插入006记录前的数据状态）

insertintostudentvalues（'

006'

小林'

1979-07-09'

泰安'

select*fromstudent;

savepointa;

deletefromstudentwheresno='

rollbacktosavepointa;

/rollback;

8.修改student_copy表名为student2

删除表student2的数据（注意delete/truncate的区别）

删除表student,score,student2

renamestudent_copytostudent2;

deletefromstudent2;

rollback;

select*fromstudent2;

truncatetablestudent2;

droptablestudent;

droptablescore;

droptablestudent2;

9.创建100个表，table_0到table_99，分别插入数据，第1条数据插入到第1个表。

。

第99条数据插入到第99个表

createorreplaceprocedurecreate_table（startnumberinnumber,endnumberinnumber）as

begin

forv_counterinstartnumber..endnumberloop

executeimmediate'

createtabletable_'

||v_counter||'

（col1number）'

insertintotable_'

||v_counter||'

values（'

）'

Endloop;

end;

execcreate_table（'

0'

99'

10.创建一个emp1表,其结构和数据与emp表完全一致.用游标完成操作:

再sql*plus中显示工资低于1500的职员信息,并显示如果给他们涨30%工资后的工资.

createtableemp1asselect*fromemp;

setserveroutputon

declare

cursorcurisselect*fromempwheresal<

1500;

begin

forv_counterincurloop

dbms_output.put_line（v_counter.empno||'

'

||v_counter.ename||'

||v_counter.mgr||'

'

||v_counter.hiredate||'

||v_m||'

||v_counter.sal||'

||v_counter.sal*1.3）;

endloop;

end;

11.编写一个pl/sql块，输出所有员工的员工名、员工号、工资和部门号。

cursorc_empisselect*fromemp;

forv_empinc_emploop

dbms_output.put_line（v_emp.ename||'

||v_emp.empno||'

||v_emp.deptno||'

'

||v_emp.sal）;

12.查询名为“smith”的员工信息，并输出其员工号、工资、部门号。

如果该员工不存在，则插入一条新记录，员工号为2007，员工名为“smith”，工资为1500，部门号为10。

如果存在多个名为“smith”的员工，则输出所有名为“smith”的员工号、工资和部门号。

Declarev_empemp%rowtype;

begin

select*intov_empfromempwhereename='

smith'

dbms_output.put_line（v_emp.empno||'

||v_emp.sal||'

||v_emp.deptno）;

exception

whenno_data_foundthen

insertintoemp（empno,ename,sal,deptno）values（2007,'

1500,10）;

whentoo_many_rowsthen

forvin（select*fromempwhereename='

）loop

dbms_output.put_line（v.empno||'

||v.sal||'

||v.deptno）;

13.创建一个存储过程，以员工号为参数，输出该员工的工资。

createorreplaceprocedureshowsal（p_empnoemp.empno%type）

asv_salemp.sal%type;

selectsalintov_salfromempwhereempno=p_empno;

dbms_output.put_line（v_sal）;

showsal（7844）;

14.创建一个函数，以员工号为参数，返回该员工所在部门的平均工资。

createorreplacefunctionfun_sal（p_empnoemp.empno%type）

returnemp.sal%type

selectavg（sal）intov_salfromempwheredeptno=

（selectdeptnofromempwhereempno=p_empno）;

returnv_sal;

dbms_output.put_line（fun_sal（7844））;

15.在emp表上创建一个触发器，当插入、删除或修改员工信息时，统计各个部门的人数及平均工资，并输出。

createorreplacetriggertrg_emp

afterinsertorupdateordelete

onemp

v_salemp.sal%type;

v_countnumber;

selectavg（sal）,count（*）intov_sal,v_countfromemp;

dbms_output.put_line（v_sal||'

||v_count）;

updateempsetsal=500whereempno=7844;

16.创建一个包，包含一个过程和一个游标。

游标返回所有员工的信息，过程实现每次输出游标中的5条记录。

createorreplacepackagepkg_persistcursor

as

proceduredisplayemp;

createorreplacepackagebodypkg_persistcursor

Asproceduredisplayemp

Asv_empemp%rowtype;

ifnotc_emp%isopenthen

openc_emp;

endif;

foriin1..5loop

fetchc_empintov_emp;

dbms_output.put_line（v_emp.empno||'

||v_emp.ename）;

//

pkg_persistcursor.displayemp;

17.创建一个存储过程，以2个整数为参数，输出工资在两者间的员工信息。

createorreplaceproceduresalbetween（minbinary_integer,maxbinary_integer）

forv_empin（select*fromempwheresalbetweenminandmax）loop

||v_emp.ename||'

||v_emp.job||'

||v_emp.mgr||'

||v_emp.hiredate||'

||v_emp.sal||'

||v_m||'

||v_emp.deptno）;

endloop;

salbetween（1000,3000）;

18.在emp表上创建一个触发器，保证修改员工工资时，改后的工资低于同部门的最高工资，同时高于同部门的最低工资。

createorreplacepackagepkg_deptno

asv_deptnoemp.deptno%type;

createorreplacetriggertrg_updateemp

beforeupdateonemp

foreachrow

pkg_deptno.v_sal:

=:

new.sal;

pkg_deptno.v_deptno:

new.deptno;

createorreplacetriggertrg_statement

afterupdateonemp

v_highsalemp.sal%type;

v_lowsalemp.sal%type;

selectmax（sal）,min（sal）intov_highsal,v_lowsal

fromempwheredeptno=pkg_deptno.v_deptno;

ifpkg_deptno.v_sal>

v_highsalorpkg_deptno.v_sal<

v_lowsalthen

raise_application_error（-20001,'

thesalisbeyond!

endif;

19.一个用户user1具有dba权限他拥有scott方案

他要给人力资源经理授予管理scott的所有对象的权限

他要给人力资源职员授予查询所有对象的权限

connuser1

createrolerolemagr;

createroleroleclerk;

grantcreatesessiontorolemagr;

grantallonscott.emptorolemagr;

grantcreatesessiontoroleclerk;

grantselectonscott.emptoroleclerk;

createusermagr1identifiedbymagr1;

createuserclerk1identifiedbyclerk1;

--指定用户名及密码

grantrolemagrtomagr1withadminoption;

grantroleclerktoclerk1;

20.truncate,delete,drop的比较:

注意:

这里说的delete是指不带where子句的delete语句

相同点:

truncate和不带where子句的delete,以及drop都会删除表内的数据

不同点:

1）truncate和delete只删除数据不删除表的结构（定义）

drop语句将删除表的结构被依赖的约束（constrain）,触发器（trigger）,索引（index）;

依赖于该表的存储过程/函数将保留,但是变为invalid状态.

2）delete语句是DML,这个操作会放到rollbacksegement中,事务提交之后才生效;

如果有相应的trigger,执行的时候将被触发.

truncate,drop是DDL,操作立即生效,原数据不放到rollbacksegment中,不能回滚.操作不触发trigger.

3）delete语句不影响表所占用的extent,高水线（highwatermark）保持原位置不动

显然drop语句将表所占用的空间全部释放

truncate语句缺省情况下见空间释放到minextents个extent,除非使用reusestorage;

truncate会将高水线复位（回到最开始）.

4）速度,一般来说:

drop>

truncate>

delete

5）安全性:

小心使用drop和truncate,尤其没有备份的时候.否则哭都来不及

使用上,想删除部分数据行用delete,注意带上where子句.回滚段要足够大.

想删除表,当然用drop

想保留表而将所有数据删除.如果和事务无关,用truncate即可.如果和事务有关,或者想触发trigger,还是用delete.

如果是整理表内部的碎片,可以用truncate跟上reusestroage,再重新导入/插入数据。

21.BindVariables（绑定变量：

）和SubstitutionVariables（替代变量&

）的区别

答：

&

变量只能使用在sql*plus中，其它地方无法使用。

仅仅是作为变量替换用的，目的估计是为了代码与人交互用的。

而绑定变量在程序里面用:

i来表示环境里面的一个值，放在sql里面就是一个占位符，以便于下次执行该sql如果这个数变了，oracle还是认为是一个sql提高效率

22.索引分类

逻辑上：

Singlecolumn单行索引Concatenated多行索引Unique唯一索引

NonUnique非唯一索引Function-based函数索引Domain域索引

物理上：

Partitioned分区索引NonPartitioned非分区索引

B-tree：

Normal正常型B树ReverKey反转型B树

Bitmap位图索引

23.索引结构：

1）B-tree：

适合与大量的增、删、改（OLTP）；

不能用包含OR操作符的查询；

适合高基数的列（唯一值多）；

典型的树状结构；

每个结点都是数据块；

大多都是物理上一层、两层或三层不定，逻辑上三层；

叶子块数据是排序的，从左向右递增；

在分支块和根块中放的是索引的范围；

2）Bitmap:

适合与决策支持系统；

做UPDATE代价非常高；

非常适合OR操作符的查询；

基数比较少的时候才能建位图索引；

3）树型结构：

索引头开始ROWID，结束ROWID（先列出索引的最大范围）

4）BITMAP

每一个BIT对应着一个ROWID，它的值是1还是0，如果是1，表示着BIT对应的ROWID有值；

24.数据库联机备份完全恢复

进行热备份

（1）查看数据库是否己经启动归档日志：

SQL>

archiveloglist;

SQL>

startupmount

（2）修改数据库的归档日志模式：

alterdatabasearchivelog;

（3）Alterdatabaseopen;

打开数据库，查看数据库中的表空间文件：

selectnamefromv$datafile;

（4）使数据库表空间处于热备份状态；

altertablespaceusersbeginbackup;

（5）此时可以直接将表空间数据文件复制到另一个目录中进行备份。

（6）使用如下命令结束热备份状态：

altertablespaceusersendbackup;

（７）对数据库进行一些数据修改操作。

故障模拟：

关闭数据库，删除users01.dbf

打开数据库

startup

数据库完全恢复：

（1）将原先备份的表空间文件复制到其原来所在的目录，并覆盖原有文件。

（2）使用recover命令进行介质恢复：

recoverdatafile'

E:

\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF'

将数据库打开

alterdatabaseopen;

至此表空间数据恢复完成。

查看数据库已恢复到最新状态。

25.不完全恢复（恢复一个删除的表）

1）正常关闭数据库，备份所有的数据文件

shutdownimmediate数据库