oracle的一些习题.doc

求雇员的平均薪水，当薪水小于1000时，提示“薪水太低”，当薪水大于等于1000小于2000时，提示起步的薪水，当薪水大于等于2000时，提示已经步入软件行业中，使用CASE表达式来实现。set serveroutput ondeclare v_sal emp.sal%type; v_var varchar2(20);begin select avg(sal) into v_sal from emp; v_var := case when v_sal=1000 and v_sal3; end loop;end;for v_count in 1.3 loop insert into test(empno,ename,job) values(v_empno+v_count),v_ename,v_job)创建表message，结构如下： results varchar2（10）使用for循环插入1到10（除了6和8）的数据到表中，在结束之前提交数据。create table message(results varchar2(10); begin for i in 1.10 loop if i=6 or i=8 then null; else insert into message values(i); end if; end loop; commit; end; select * from message; begin for i in 1.10 loop; if i6 and i8 then insert into message values(i); end if; end loop; commit; end; 尽量不用goto语句；begin dbms_output.put_line(1); goto l_last_line; dbms_output.put_line(2); return; dbms_output.put_line(3);end; 第三章创建一个记录类型emp_record_type，它包含雇员的名字、薪水、工作岗位，声明一个该类型的变量emp_record，查询雇员编号是7788雇员的上述信息，并显示。declare type emp_record_type is recode (ename emp.ename%type, sal emp.sal%type, job varchar2(20); emp_record emp_record_type;begin select ename,job,sal into emp_record from emp where empno=7788; dbms_output.put_line(雇员名：=emp_record.ename;薪水：=sal);end; 第四章创建一个游标，查询雇员的名字和薪水，当查询到某雇员的薪水大于3000元时，退出循环，并显示其对应的名字和薪水。set serveroutput ondeclare ename emp.ename%type; sal emp.sal%type; cursor emp_cursor is select ename,sal from emp;begin open emp_cursor; loop fetch emp_cursor into ename,sal; if(sal3000)then dbms_output.put_line(ename|sal); exit; end if; end loop; close emp_cursor;end; 创建一个游标，查询薪水大于3000元的雇员名字和对应薪水，并显示其对应的 名字和薪水。set serveroutput ondeclare ename emp.ename%type; sal emp.sal%type; cursor emp_cursor is select ename,sal from emp where sal2000;begin if not emp_cursor%isopen then open emp_cursor; end if; loop fetch emp_cursor into ename,sal; exit when emp_cursor%notfound; dbms_output.put_line(ename|的薪水为|sal);end loop; close emp_cursor;end; 创建一个游标，查询雇员名字和对应薪水，当遇到雇员名是SCOTT的雇员时，退出游标的FOR循环，显示其对应的薪水，并显示其在游标中的位置。 declare cursor emp_cursor is select ename,sal from emp;begin for emp_record in emp_cursor loop if emp_record.ename=SCOTT then dbms_output.put_line(emp_record.ename|的薪水为|emp_record.sal); dbms_output.put_line(emp_cursor%rowcount); exit; end if; end loop;end; 不需声明的游标begin for emp_record in (select empno,ename from emp) loop if emp_record.ename=SCOTT then dbms_output.put_line(emp_record.empno); end if; end loop; end;带参数的游标declare cursor emp_cursor (p_deptno number,p_sal number) is select ename from emp where deptno=p_deptno and salp_sal;begin open emp_cursor(10,2000); close emp_cursor; open emp_cursor(20,4000);end; 在一个快中分别 显示薪水低于2000，在2000与4000之间，以及大于4000的雇员名。declare cursor emp_cursor (p_sal1 number,p_sal2 number) is select ename from emp where salp_sal1 and sal=5; -第八章-1、创建表test，表定义如下：empno是主键1、create table test(empnonumber(2) constraint emp_pk primary key,enamevarchar2(10),salnumber(7,2);2、创建包的声明test_pak，内容包含add_user的过程、del_user过程（根据empno值删除用户）、add_sal函数（根据empno值确定用户，增加员工工资，并返回该用户的工资）create or replace package test_pakisPROCEDURE add_user(p_empno in number,p_ename in varchar2,p_sal innumber);PROCEDURE del_user(p_empno in number);function add_sal(p_empno in number,p_sal innumber)returnnumber;end test_pak;3、创建包体，实现上述定义create or replace package body test_pakisPROCEDURE add_user(p_empno in number,p_ename in varchar2,p_sal innumber)isbegininsert into testvalues(p_empno,p_ename,p_sal);commit;end add_user;PROCEDURE del_user(p_empno in number)isbegindelete from test where empno=p_empno;commit;end del_user;function add_sal(p_empno in number,p_sal innumber)return numberisv_salnumber(10);beginupdate testset sal=sal+p_salwhere empno=p_empno;commit;select sal into v_sal from testwhere empno=p_empno;return v_sal;end add_sal;end test_pak;4、调用上述的每部分，验证正确性exec test_pak.add_user(1,ljs,1000);declarev_salnumber(7,2);beginv_sal:=test_pak.add_sal(1,500);dbms_output.put_line(v_sal);end;exec test_pak.del_user(1);声明无体包（续）create or replace package meter_to_yard (p_meter in number,p_yard out number) isbegin p_yard :=p_meter*global_consts.meter_2_yard; end meter_to_yard; / variable yard number execute meter_to_yard(1,:yard) print yard drop package package_name;drop package body package_name; 重载（续）create or replace package comm_packis procedure reset_comm(p_comm in number); procedure reset_comm(p_comm in varchar2); end comm_pack; 重载（续）create or replace package body comm_packis procedure reset_comm(p_comm in number) is begin dbms_output.put_line(参数是数字); end reset_comm; procedure reset_comm(p_comm in varchar2) is begin dbms_output.put_line(参数是字符); end reset_comm;end comm_pack; exec comm_pack.reset_comm(1);set serveroutput on 重载练习创建test_pak，实现reset_sal的重载： reset_sal(p_sal in number,empno in number) reset_sal(ename in varchar2,p_sal in number) 当执行test_pak.reset_sal(1,1)时，显示call 1.当执行test_pak.reset_sal(1,1)时，显示call 2.create or replace package test_pakis procedure reset_sal(p_sal in number,empno in number); procedure reset_sal(ename in varchar2,p_sal in number);end test_pak;create or replace package body test_pakis procedure reset_sal(p_sal in number,empno in number) is begin dbms_output.put_line(call 1); end; procedure reset_sal(ename in varchar2,p_sal in number) is begin dbms_output.put_line(call 2); end;end test_pak; exec test_pak.reset_sal(1);set serveroutput on create or replace procedure del_rows( p_table_name in varchar2,p_rows_deld out number)isvariable deleted numberexecute del_rows(emp,:deleted);rollback;1、创建存储过程proc_test,根据用户输入的表明、字段名、字段类型参数动态创建包含两个字段的表2、执行该存储过程，验证正确性 create or replace procedure proc_test( table_name in varchar2,-表名 field1 in varchar2,-字段名 datatype1 in varchar2,-字段类型 field2 in varchar2,-字段名 datatype2 in varchar2-字段类型) Authid Current_User-可以使用角色中的权限 as str_sql varchar2(500);begin str_sql:=create table|table_name|(|field1| |datatype1|, |field2| |datatype2|); dbms_output.put_line(str_sql); execute immediate str_sql;-动态执行DDL语句exception when others then null;end;execute proc_test(dinya_test,id,number(8) not null,name,varchar2(100); 第五章declare v_ename emp.ename%type;begin select ename into v_ename from emp where empno=30;exception when no_data_found then dbms_output.put_line(没有找到其他数据); when others then dbms_output.put_line(其他异常);end; -预定异常查询部门编号是30的所有雇员的名字，注意运用SELECT语句返回多行数据的异常处理，在异常处理部分显示返回的记录数太多 declare v_ename emp.ename%type;begin select ename into v_ename from emp where deptno=30;exception when TOO_MANY_ROWS then dbms_output.put_line(返回的记录数太多); when others then dbms_output.put_line(其他异常);end; -非预定异常如果部门中有员工，当删除部门数据时，提示用户该部门不能被 删除declare e_emps_remaining exception; pragma exception_init(e_emps_remaining,-02292); v_deptno dept.deptno%type:=&p_deptno;begin delete from dept where deptno=v_deptno;exception when e_emps_remaining then dbms_output.put_line(该部门不能被删除); when others then dbms_output.put_line(其他异常); end; -自定义异常统计大于平均薪水的员工数量，如果数量大于5，触发e_too_manyy异常，显示大于平均工资的人数不少；如果数量小于等于5，触发e_too_low异常显示大于平均工资的人 数太少declare e_too_many exception; e_too_low exception; v_count number(10);begin select count(*)into v_count from emp where sal (select avg(sal)from emp); if v_COUNT5 THEN raise e_too_many; else raise e_too_low; end if ;exception when e_too_many then dbms_output.put_line(大于平均工资的人数不少); when others then dbms_output.put_line(大于平均工资的人数太少); end; -异常传递1.创建表message(result varchar2(100),存放状态信息2.写PL/SQL块，传递不同的sal值到块中，根据传递的sal值进行查 询当emp表中没有该sal值时，引发异常，在异常部分将没有雇员挣 sal的薪水信息插入到message表中，并显示该信息当emp表中有多于一个sal值时，引发异常，在异常部分将太多的 雇员挣sal的薪水信息插入到message表中，并显示该信息当只有一个雇员具有该工资时，则输出雇员名和工资create table message(result varchar2(100);declare v_sal emp.sal%type; v_ename emp.ename%type; v_var varchar2(100); begin select ename into v_ename from emp where sal=v_sal; dbms_output.put_line(v_ename|v_sal);exception when no_data_found then v_var:=没有雇员挣|v_sal|的薪水; dbms_output.put_line(v_var); insert into message values(v_var); when too_many_rows then v_var:=太多雇员挣sal的薪水; dbms_output.put_line(v_var); insert into message values(v_var);end;第六章写一个存储过程，两个参数，薪水参数信息参数，当传入的薪水大 于所有薪水平均值时，薪水太高了 信息传给信息参数2、写一个存储过程，调用上面过程，并显示create or replace procedure p_pro(p_sal in emp.sal%type, p_sat out varchar2)is v_sal emp.sal%type;begin select avg(sal) into v_sal from emp; if p_salv_sal then p_sat:=薪水太高; else p_sat:=薪水正常; end if;end p_pro;create or replace procedure p_show(p_sal in emp.sal%type, p_sat out varchar2)isbegin p_pro(p_sal,p_sat); dbms_output.put_line(薪水等级是：|p_sat);end;declare v_sals emp.sal%type:=&v_sals; p_sats varchar2(100);begin p_show(v_sals, p_sats);end p_show;create or replace procedure p_sal( p_sals emp.sal%type, p_empno emp.empno%type, p_ename emp.ename%type)is cursor p_cursor (v_sal emp.sal%type) is select empno,ename from emp where salv_sal order by empno;begin for p_record in p_cursor(p_sals) loop dbms_output.put_line(员工的编号是:|p_record.empno| 员工的姓名是:|p_record.ename); end loop;end p_sal;declare v_sal emp.sal%type:=&v_sal; p_empno emp.empno%type; p_ename emp.ename%type;begin p_sal(v_sal,p_empno,p_ename);end;-create or replace procedure p_yearsal( p_empno in emp.empno%type, p_yearsal out emp.sal%type)is v_sal emp.sal%type; begin select sal*12 into p_yearsal from emp where empno=p_empno; dbms_output.put_line(p_empno|的员工的年薪 是:|p_yearsal);end p_yearsal;declare p_empno emp.empno%type:=&p_empno; p_yearsals emp.sal%type;begin p_yearsal(p_empno,p_yearsals);end; 第七章-2.创建一个函数，根据输入的参数（部门代码）值，返回对应的 部门员工的最高薪水；- 创建一个存储过程，查询dept表的所有的部门信息，调用该函 数，当函数返回的薪水大于4000时，产生异常，在异常部门显示- 某某部门的薪水太高了。-函数的创建create or replace function func(v_deptno in emp.deptno%type)returnnumberisv_salemp.sal%type;beginselect max(sal) into v_sal from emp where deptno = v_deptno;return v_sal;end;create or replace procedure sal_procisv_salemp.sal%type;e_many_salEXCEPTION;v_messagevarchar2(20);cursoremp_cursor isselect deptno,dname from dept;beginfor emp_record in emp_cursor loopv_sal := func(emp_record.deptno);if v_sal 4000 thenv_message := emp_record.dname; raise e_many_sal;end if;end loop;exceptionwhen e_many_sal thendbms_output.put_line(v_message|部门的薪 水太高了);end;-函数的使用实例-1、创建函数，根据输入的参数员工代码返回对应的员工姓名create or replace function p_query( p_empno in emp.empno%type) return varchar2is p_ename emp.ename%type;begin select ename into p_ename from emp where empno=p_empno; return p_ename;end p_query;declare v_empno emp.empno%type:=&v_empno; v_ename emp.ename%type;begin v_ename:=p_query(v_empno); dbms_output.put_line(v_empno|的员工的姓名 是:|v_ename);end; variable g_ename varchar2exec:g_ename:=func(7369)Print g_ename-2.创建一个函数，根据输入的参数（部门代码）值，返回对应的 部门员工的最高薪水；- 创建一个存储过程，查询dept表的所有的部门信息，调用该函 数，当函数返回的薪水大于4000时，产生异常，在异常部门显示- 某某部门的薪水太高了。-函数的创建create or replace function func_maxsal( func_empno in emp.empno%type ) return numberis func_v_sal emp.sal%type;begin select max(sal) into func_v_sal from emp where deptno=func_empno group by deptno; return func_v_sal;end func_maxsal;-第九章- 1、在emp表上创建语句级别的触发器，当用户在8:00点至17:00点以外插入、修改、删除数据之后，系统提示只是在工作期间可以录入数据.create or replace trigger sal_empafter insert or update or delete on empbegin if(to_char(sysdate,HH24:MI) not between 08:00 and 17:00 ) then raise_application_error(-20001,只是在工作期间可以录入数据); end if;end;insert into emp(empno,ename,job,mgr,hiredate,sal)values(1002,ljs,clerk,7902,sysdate,2000);