oracle实例练习.docx
- 文档编号:5884264
- 上传时间:2023-01-01
- 格式:DOCX
- 页数:16
- 大小:23.50KB
oracle实例练习.docx
《oracle实例练习.docx》由会员分享,可在线阅读,更多相关《oracle实例练习.docx(16页珍藏版)》请在冰豆网上搜索。
oracle实例练习
1.用sys账户登录,解锁scott账户
代码:
Connectsys/orcl@orcl_clientASSYSDBA
ALTERUSER"SCOTT"ACCOUNTUNLOCK
2.以scott身份登录数据库
connscott/tiger@orcl
3.创建学生表student(sno,sname,sgender,sbirthday,sadd)score(sno,math,english)
代码:
createtablestudent(snochar(3),snamevarchar2(10),sgenderchar2(20),sbirthdaydate,saddvarchar2(50))
createtablescore(snochar(3),mathnumber(4,1),englishnumber(4,1))
4.插入记录
student插入记录:
001,小张,女,1980-8-20,济南
002,小王,男,1983-4-1,莱芜
003,小李,女,1980-5-20,济南
004,小赵,女,1980-5-20,莱芜
005,小孔,女,1982-6-18威海
score插入记录:
(005没参加考试,800是个进修生,不是学校的正式生)
001,90,92
002,85,79
003,80,94
004,78,77
80079,88
代码:
altersessionsetnls_date_format='YYYY-MM-DDHH24:
MI:
SS';
insertintostudentvalues('001','小张','女',to_date('1980-08-20','yyyy-mm-dd'),'济南');
insertintostudentvalues('002','小王','男',to_date('1983-04-01','yyyy-mm-dd'),'莱芜');
insertintostudentvalues('003','小李','女',to_date('1980-05-20','yyyy-mm-dd'),'济南');
insertintostudentvalues('004','小赵','女',to_date('1980-05-20','yyyy-mm-dd'),'莱芜');
insertintostudentvalues('005','小孔','女',to_date('1982-06-18','yyyy-mm-dd'),'威海');
insertintoscorevalues('001','90','92');
insertintoscorevalues('002','85','79');
insertintoscorevalues('003','80','94');
insertintoscorevalues('004','78','77');
insertintoscorevalues('800','79','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','小林','男',to_date('1979-07-09','yyyy-mm-dd'),'泰安');
select*fromstudent;
savepointa;
deletefromstudentwheresno='003';
rollbacktosavepointa;
select*fromstudent;
commit;/rollback;
8.修改student_copy表名为student2
删除表student2的数据(注意delete/truncate的区别)
删除表student,score,student2
代码:
renamestudent_copytostudent2;
deletefromstudent2;
rollback;
select*fromstudent2;
truncatetablestudent2;
rollback;
select*fromstudent2;
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)';
executeimmediate'insertintotable_'||v_counter||'values('||v_counter||')';
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块,输出所有员工的员工名、员工号、工资和部门号。
代码:
declare
cursorc_empisselect*fromemp;
begin
forv_empinc_emploop
dbms_output.put_line(v_emp.ename||''||v_emp.empno||''||v_emp.deptno||''||v_emp.sal);
endloop;
end;
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,'smith',1500,10);
whentoo_many_rowsthen
forvin(select*fromempwhereename='smith')loop
dbms_output.put_line(v.empno||''||v.sal||''||v.deptno);
endloop;
end;
13.创建一个存储过程,以员工号为参数,输出该员工的工资。
createorreplaceprocedureshowsal(p_empnoemp.empno%type)
asv_salemp.sal%type;
begin
selectsalintov_salfromempwhereempno=p_empno;
dbms_output.put_line(v_sal);
end;
begin
showsal(7844);
end;
14.创建一个函数,以员工号为参数,返回该员工所在部门的平均工资。
createorreplacefunctionfun_sal(p_empnoemp.empno%type)
returnemp.sal%type
asv_salemp.sal%type;
begin
selectavg(sal)intov_salfromempwheredeptno=
(selectdeptnofromempwhereempno=p_empno);
returnv_sal;
end;
begin
dbms_output.put_line(fun_sal(7844));
end;
15.在emp表上创建一个触发器,当插入、删除或修改员工信息时,统计各个部门的人数及平均工资,并输出。
createorreplacetriggertrg_emp
afterinsertorupdateordelete
onemp
declare
v_salemp.sal%type;
v_countnumber;
begin
selectavg(sal),count(*)intov_sal,v_countfromemp;
dbms_output.put_line(v_sal||''||v_count);
end;
updateempsetsal=500whereempno=7844;
16.创建一个包,包含一个过程和一个游标。
游标返回所有员工的信息,过程实现每次输出游标中的5条记录。
createorreplacepackagepkg_persistcursor
as
cursorc_empisselect*fromemp;
proceduredisplayemp;
end;
createorreplacepackagebodypkg_persistcursor
Asproceduredisplayemp
Asv_empemp%rowtype;
begin
ifnotc_emp%isopenthen
openc_emp;
endif;
foriin1..5loop
fetchc_empintov_emp;
dbms_output.put_line(v_emp.empno||''||v_emp.ename);
endloop;
end;
end;//
begin
pkg_persistcursor.displayemp;
end;
17.创建一个存储过程,以2个整数为参数,输出工资在两者间的员工信息。
createorreplaceproceduresalbetween(minbinary_integer,maxbinary_integer)
as
begin
forv_empin(select*fromempwheresalbetweenminandmax)loop
dbms_output.put_line(v_emp.empno||''||v_emp.ename||''||v_emp.job||''||v_emp.mgr||''||v_emp.hiredate||''||v_emp.sal||''||v_m||''||v_emp.deptno);
endloop;
end;
begin
salbetween(1000,3000);
end;
18.在emp表上创建一个触发器,保证修改员工工资时,改后的工资低于同部门的最高工资,同时高于同部门的最低工资。
createorreplacepackagepkg_deptno
asv_deptnoemp.deptno%type;
v_salemp.sal%type;
end;//
createorreplacetriggertrg_updateemp
beforeupdateonemp
foreachrow
begin
pkg_deptno.v_sal:
=:
new.sal;
pkg_deptno.v_deptno:
=:
new.deptno;
end;//
createorreplacetriggertrg_statement
afterupdateonemp
declare
v_highsalemp.sal%type;
v_lowsalemp.sal%type;
begin
selectmax(sal),min(sal)intov_highsal,v_lowsal
fromempwheredeptno=pkg_deptno.v_deptno;
ifpkg_deptno.v_sal>v_highsalorpkg_deptno.v_sal raise_application_error(-20001,'thesalisbeyond! '); endif; end; updateempsetsal=500whereempno=7844; 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)修改数据库的归档日志模式: SQL>alterdatabasearchivelog; (3)Alterdatabaseopen;打开数据库,查看数据库中的表空间文件: selectnamefromv$datafile; (4)使数据库表空间处于热备份状态;SQL>altertablespaceusersbeginbackup; (5)此时可以直接将表空间数据文件复制到另一个目录中进行备份。 (6)使用如下命令结束热备份状态: SQL>altertablespaceusersendbackup; (7)对数据库进行一些数据修改操作。 故障模拟: 关闭数据库,删除users01.dbf 打开数据库 SQL>startup 数据库完全恢复: (1)将原先备份的表空间文件复制到其原来所在的目录,并覆盖原有文件。 (2)使用recover命令进行介质恢复: SQL>recoverdatafile'E: \ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF'; 将数据库打开 SQL>alterdatabaseopen;至此表空间数据恢复完成。 查看数据库已恢复到最新状态。 25.不完全恢复(恢复一个删除的表) 1)正常关闭数据库,备份所有的数据文件 SQL>shutdownimmediate数据库
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 实例 练习