oracle实例练习.docx

1、oracle实例练习1. 用sys账户登录，解锁scott账户代码：Connect sys/orclorcl_client AS SYSDBA ALTER USER SCOTT ACCOUNT UNLOCK2. 以scott身份登录数据库conn scott/tigerorcl3. 创建学生表student(sno,sname,sgender,sbirthday,sadd) score(sno,math,english)代码：create table student(sno char(3),sname varchar2(10),sgender char2(20),sbirthday date,

2、sadd varchar2(50) create table score(sno char(3),math number(4,1),english number(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 800

3、 79, 88代码：alter session set nls_date_format =YYYY-MM-DD HH24:MI:SS; insert into student values(001,小张,女,to_date(1980-08-20,yyyy-mm-dd),济南); insert into student values(002,小王,男,to_date(1983-04-01,yyyy-mm-dd),莱芜); insert into student values(003,小李,女,to_date(1980-05-20,yyyy-mm-dd),济南); insert into stud

4、ent values(004,小赵,女,to_date(1980-05-20,yyyy-mm-dd),莱芜); insert into student values(005,小孔,女,to_date(1982-06-18,yyyy-mm-dd),威海); insert into score values(001,90,92); insert into score values(002,85,79); insert into score values(003,80,94); insert into score values(004,78,77); insert into score values

5、(800,79,88);5. a统计各个地区的学生数 b计算各个学生的总成绩（数学+英语），并且按照成绩由高到低做出学生的成绩单报告(没考试的学生名字不要出现在报告单上,进修生的成绩也不在报告单上) 报告单标题显示：学号 姓名 数学 英语 总成绩 c计算各个学生的总成绩（数学+英语），并且按照成绩由高到低做出学生的成绩单报告(没考试的学生名字也要出现在报告单上,进修生的成绩不在报告单上) 报告单标题显示：学号 姓名 数学 英语 总成绩代码：select sadd 地区,count(*) as 人数 from student group by sadd select student.sno 学号

6、, sname 姓名, math 数学, english 英语, (math+english) 总成绩 from student inner join score on score where student.sno=score.sno order by 总成绩 desc select student.sno 学号, sname 姓名, math 数学, english 英语, math+english 总成绩 from student left outer join score on student.sno=score.sno order by 总成绩 desc或select student

7、.sno 学号, sname 姓名, math 数学,english 英语, (math+english) 总成绩 from student,score where student.sno=score.sno(+) order by 总成绩 desc;6. 根据student表，创建一个新表student_copy（结构相同，数据只有济南的两个学生） 从student表中查出莱芜得同学信息，插入到student_copy表中 commit/提交刚才的插入 代码：create table student_copy as select * from student Where sadd=济南; i

8、nsert into student_copy(select * from student where sadd=莱芜); commit;7. 插入一条新的学生纪录： 006 小林 男 1979-7-9 泰安 savepoint a /设置保存点a 删除掉学号为003的学生纪录（误删） rollback to savepoint a 察看结果 commit(提交插入纪录的操作）/rollback（回滚到插入006记录前的数据状态）代码：insert into student values(006,小林,男,to_date(1979-07-09,yyyy-mm-dd),泰安);select *

9、from student; savepoint a; delete from student where sno=003; rollback to savepoint a;select * from student; commit;/rollback;8. 修改student_copy表名为student2 删除表student2的数据 (注意delete/truncate的区别) 删除表student,score,student2代码：rename student_copy to student2; delete from student2;rollback;select * from st

10、udent2;truncate table student2;rollback;select * from student2;drop table student;drop table score;drop table student2;9. 创建100个表，table_0到table_99，分别插入数据，第1条数据插入到第1个表。第99条数据插入到第99个表 代码：create or replace procedure create_table(startnumber in number,endnumber in number) as begin for v_counter in start

11、number.endnumber loop execute immediate create table table_|v_counter| (col1 number); execute immediate insert into table_|v_counter| values(|v_counter|); End loop; end; exec create_table(0,99);10. 创建一个emp1表,其结构和数据与emp表完全一致.用游标完成操作:再sql*plus中显示工资低于1500的职员信息,并显示如果给他们涨30%工资后的工资.代码：create table emp1 as

12、 select * from emp;set serveroutput ondeclarecursor cur is select * from emp where sal1500;beginfor v_counter in cur loopdbms_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);end loop;end;11. 编写一个pl/sql块，输出所有员工的员工名、员工号、

13、工资和部门号。代码：declarecursor c_emp is select * from emp;beginfor v_emp in c_emp loopdbms_output.put_line(v_emp.ename| |v_emp.empno| |v_emp.deptno| |v_emp.sal);end loop; end;12. 查询名为“smith”的员工信息，并输出其员工号、工资、部门号。如果该员工不存 在，则插入一条新记录，员工号为2007，员工名为“smith”，工资为1500，部门号为10。如果存在多个名为“smith”的员工，则输出所有名为“smith”的员工号、工资和

14、部门号。代码：Declare v_emp emp%rowtype;begin select * into v_emp from emp where ename=smith;dbms_output.put_line(v_emp.empno| |v_emp.sal| |v_emp.deptno);exceptionwhen no_data_found then insert into emp(empno,ename,sal,deptno) values(2007,smith,1500,10);when too_many_rows thenfor v in (select * from emp wh

15、ere ename=smith) loopdbms_output.put_line(v.empno| |v.sal| |v.deptno);end loop;end;13. 创建一个存储过程，以员工号为参数，输出该员工的工资。create or replace procedure showsal(p_empno emp.empno%type)as v_sal emp.sal%type;beginselect sal into v_sal from emp where empno=p_empno;dbms_output.put_line(v_sal);end;beginshowsal(7844);end;14. 创建一个函数，以