Oracle经典练习题很全面.docx

1、Oracle经典练习题很全面Oracle 经典练习题一.创建一个简单的PL/SQL程序块 1.编写一个程序块,从emp表中显示名为“SMITH”的雇员的薪水和职位。declare v_emp emp%rowtype; begin select * into v_emp from emp where ename=SMITH;） (员工的工作是：| ； 他的薪水是：|; end; 2.编写一个程序块，接受用户输入一个部门号，从dept表中显示该部门的名称与所在位置。方法一：（传统方法） declare pname %type; ploc %type; pdeptno %type;|begin pd

2、eptno:=&请输入部门编号; select dname,loc into pname,ploc from dept where deptno=pdeptno; (部门名称: |pname|所在位置:|ploc); exception 异常处理 when no_data_found then (你输入的部门编号有误!); when others/ then (其他异常);end;方法二：（使用%rowtype） declare erow dept%rowtype; begin select * into erow from dept where deptno=&请输入部门编号; |-|; e

3、xception when no_data_found then (你输入的部门号有误); when others then (其他异常); end; 3.编写一个程序块，利用%type属性，接受一个雇员号，从emp表中显示该雇员的整体薪水(即，薪水加佣金)。declare pempno %type; totalSal %type;begin pempno:=&请输入员工编号; select sal+nvl(comm,0) into totalSal from emp where empno=pempno; (该员工总共薪水|totalSal); exception when no_data_

4、found, then (你输入的员工编号有误!); when others then (其他异常);end; 4.编写一个程序块，利用%rowtype属性，接受一个雇员号，从emp表中显示该雇员的整体薪水(即，薪水加佣金)。declare erow emp%rowtype;begin； select * into erow from emp where empno=&请输入员工编号; +nvl,0); exception when no_data_found then (你输入的员工编号有误!); when others then (其他异常);end;： 5.某公司要根据雇员的职位来加薪，

5、公司决定按下列加薪结构处理： Designation Raise - Clerk 500 Salesman 1000 Analyst 1500 Otherwise 2000编写一个程序块,接受一个雇员名，从emp表中实现上述加薪处理。declareerow emp%rowtype;begin select * into erow from emp where ename=&name; if =Clerk then update emp set sal=sal+500 where empno=; elsif =Salesman then（ update emp set sal=sal+1000

6、where empno=; elsif =Analyst then update emp set sal=sal-1500 where empno=; else update emp set sal=sal+2000 where empno=; end if; commit;exception% when no_data_found then (你输入的员工编号有误!); when others then (其他异常);end;6.编写一个程序块，将emp表中雇员名全部显示出来。declare cursor cs is select ename from emp;begin for erow

7、in cs loop ; end loop;end; 7.编写一个程序块，将emp表中前5人的名字显示出来。 方式一:/declare cursor cs is select t.* from (select ,rownum rm from emp e)t where between 1 and 6;begin for erow in cs loop ; end loop;end;方式二:,-方式二declare cursor cs is select ename from emp; i number :=1;begin for erow in cs loop ; i:=i+1; -迭代 ex

8、it when i5; -退出条件 end loop;end; 8.编写一个程序块，接受一个雇员名，从emp表中显示该雇员的工作岗位与薪水，若输入的雇员名不存在，显示“该雇员不存在”信息。declare pjob %type; totalsal %type;begin select job,sal into pjob,totalsal from emp where ename=&请输入员工姓名; (pjob |- |totalsal); exception when no_data_found then (你输入的员工姓名有误!);end;- 9.接受两个数相除并且显示结果，如果第二个数为0，

9、则显示消息“除数不能为0”。declare num1 float; num2 float; res float; my_exception Exception;| begin num1:=&被除数; num2:=&除数; res:=num1/num2; raise my_exception; exception when my_exception then (res); when others then (除数不能为0); end;二.声明和使用游标- 游标:(集合) ，处理返回多行记录的问题 - 声明游标 -语法： cursor 游标名 is DQL;- 遍历游标 /* 1.打开游标, op

10、en 游标名; 2.从游标中提取一行的记录：fetch 游标名 into 变量名,.; 3.使用循环， exit when 游标名%notfound; 4.关闭游标， close 游标名;1.通过使用游标来显示dept表中的部门名称。 declare cursor co is select dname from dept; begin for vname in co loop ; end loop; end;-2.使用For循环，接受一个部门号，从emp表中显示该部门的所有雇员的姓名，工作和薪水。declare cursor c_emp is select * from emp where d

11、eptno=&请输入部门号;begin for erow in c_emp loop | | | |; end loop;exception when no_data_found then (输入的部门编号有误);end;3.使用带参数的游标，实现第2题。declare cursor c_cs(c_deptno number) is select * from emp where deptno=c_deptno; v_deptno number;(begin v_deptno:=&请输入部门编号; for erow in c_cs(v_deptno) loop | | | |; end loo

12、p;exception when no_data_found then (输入的部门编号有误);end; 4.编写一个PL/SQL程序块，从emp表中对名字以“A”或“S”开始的所有雇员按他们基本薪水的10%给他们加薪。declare cursor c_emp is select ename from emp;begin for erow in c_emp loop if like A% then update emp set sal=sal+sal* where ename=; elsif like S% then update emp set sal=sal+sal* where enam

13、e=; end if; commit; end loop;end;5.emp表中对所有雇员按他们基本薪水的10%给他们加薪，如果所增加后的薪水大于5000卢布，则取消加薪。 declare cursor c_emp is select * from emp;begin for erow in c_emp loop if *5000 then update emp set sal=sal+sal* where ename=; end if; commit; end loop;end;三 存储过程- 存储过程(dba声明,得授予dba权限): 封装了一组sql语句，提前编译好，效率较高 ,存储在服

14、务端 - 场景：网购：数据库发生什么改变 - 库存量-1(update) ,订单增加(insert)，钱（update）,物流(insert) , 日志(insert)- 语法 /* create or replace procedure 存储过程名称(参数名 in|out 类型,.) as | is - 声明变量 begin - 过程化语言 end; */】1.创建一个过程，能向dept表中添加一个新记录.（in参数）create or replace procedure insert_dept(p_deptno in number,p_dname in varchar2,p_loc in

15、varchar2)isbegin insert into dept values(p_deptno,p_dname,p_loc);end;-调用该存储过程-declarebegin insert_dept(50,DEVELOP,SHENGZ);end; 2.创建一个过程，从emp表中输入雇员的姓名，返回该雇员的薪水值。（out参数）%然后调用过程。create or replace procedure find_emp(p_name in varchar2,p_sal out number)ise_sal %type;begin select sal into e_sal from emp w

16、here ename=p_name; p_sal:=e_sal;.exception when no_data_found then p_sal:=0;end;-调用存储过程declare msal number(5);begin、 find_emp(SCOTT,msal); (msal);end; 3.编写一个程序块，接受一个雇员号与一个百分数，从emp表中将该雇员的薪水增加输入的百分比。 （利用过程，in out 参数） create or replace procedure addSal(p_empno in number,p_num in float)isbegin update em

17、p set sal=sal+sal*p_num where empno=p_empno;exception when no_data_found then (输入的员工编号有误);；end;-访问存储过程declarebegin addSal(7788,; commit;end;&存储函数 - 存储函数:封装了一组sql语句，提前编译好，效率较高 ,存储在服务端 - 存储函数必须有一个返回值，存储函数可以用select语句中 /* create or replace function 函数名(参数名 in|out 类型,.) return type as | is begin return 值

18、; end; */ 4.创建一个函数，它以部门号作为参数且返回那个部门的所有的雇员的整体薪水(其实就是该部门的平均工资)。 然后调用此函数。/create or replace function getAllSal(f_deptno in number)return numberise_sal %type;begin select avg(sal) into e_sal from emp where deptno=f_deptno; return e_sal;】exception when no_data_found then return 0;end;-调用存储函数select getAll

19、Sal(20) from dual;/ 5.创建一个函数，它以部门号作为参数传递并且使用函数显示那个部门名称与位置。然后调用此函数。 create or replace function showDnameAndLoc(f_deptno in number)return dept%rowtypease_row dept%rowtype;begin* select * into e_row from dept where deptno=f_deptno; return e_row;end;-访问存储函数declare erow dept%rowtype;begin erow:=showDname

20、AndLoc(20); | | ;end;四 触发器练习 - 触发器(监听器):监听表中的数据是否发生了改变 - 增删改 操作/*create or replace trigger 触发器名after | before 在改变之前还是之后执行触发器insert | delete | update 监听表的哪个操作on 表 对哪张表的监听触发器的级别:表级触发器， 行级触发器 表级触发器不能使用old，new 行级触发器：可以使用old，newdeclarebegin end;*/ 1.创建一个行级别触发器，将从emp表中删除的记录输入到ret_emp表中。create or replace t

21、rigger del_empafterdeleteon empfor each rowbegin insert into ret_emp values(:,:,:, :,:,:,:,:;end;2.创建一个行级别触发器，停止用户删除雇员名为SMITH的记录。create or replace trigger nodel_SMITHbeforedeleteon empfor each rowwhen =SMITH)begin raise_application_error(20001,不能删除该用户信息);end;3.创建一个语句级别触发器，不允许用户在Sundays使用emp表。 create or replace trigger control_emp before insert or update or delete on emp begin if to_char(sysdate,DY,nls_date_language=AMERICAN)-显示系统当前日期以美国格式表示的名字缩写的一天 in(SUN) then raise_application_error(-20001,不允许在星期天操作emp表); end if; end;