OraclePLSQL经典练习题1.docx

1、OraclePLSQL经典练习题1Oracle-PL-SQL经典练习题1D dbms_output.put_line(v_emp.sal+v_m); end; 5.某公司要根据雇员的职位来加薪，公司决定按下列加薪结构处理： Designation Raise - Clerk 500 Salesman 1000 Analyst 1500 Otherwise 2000编写一个程序块,接受一个雇员名，从emp表中实现上述加薪处理。（*期末考试试题*）declare v_emp emp%rowtype; begin select * into v_emp from emp where ename=&n

2、ame; if v_emp.job=CLERK then update emp set sal=sal+500 where empno=v_emp.empno; elsif v_emp.job=SALESMAN then update emp set sal=sal+1000 where empno=v_emp.empno; elsif v_emp.job=ANALYST then update emp set sal=sal+1500 where empno=v_emp.empno; else update emp set sal=sal+2000 where empno=v_emp.emp

3、no; end if; commit;end; 6.编写一个程序块，将emp表中雇员名全部显示出来。 declare cursor v_cursor is select * from emp; begin for v_emp in v_cursor loop dbms_output.put_line(v_emp.ename); end loop; end; 7.编写一个程序块，将emp表中前5人的名字显示出来。declare cursor v_cursor is select * from emp; v_count number :=1; begin for v_emp in v_cursor

4、 loop dbms_output.put_line(v_emp.ename); v_count := v_count+1; exit when v_count5; end loop; end; 8.编写一个程序块，接受一个雇员名，从emp表中显示该雇员的工作岗位与薪水，若输入的雇员名不存在，显示“该雇员不存在”信息。（*期末考试试题*）declare v_emp emp%rowtype; my_exception Exception; begin select * into v_emp from emp where ename=&name; raise my_exception; excep

5、tion when no_data_found then dbms_output.put_line(该雇员不存在!); when others then dbms_output.put_line(v_emp.job|-|v_emp.sal); end; 9.接受两个数相除并且显示结果，如果第二个数为0，则显示消息“除数不能为0”(课堂未讲)。declare v_dividend float; v_divisor float; v_result float; my_exception Exception; begin v_dividend:=&被除数; v_divisor:=&除数; v_res

6、ult:=v_dividend/v_divisor; raise my_exception; exception when my_exception then dbms_output.put_line(v_result); when others then dbms_output.put_line(除数不能为0); end;二.声明和使用游标 使用游标属性 使用游标For循环工作 声明带参数的游标 （使用FOR UPDATE OF和CURRENT OF子句工作）1.通过使用游标来显示dept表中的部门名称。declare cursor v_cursor is select * from dep

7、t; begin for v_dept in v_cursor loop dbms_output.put_line(v_dept.dname); end loop; end;2.使用For循环，接受一个部门号，从emp表中显示该部门的所有雇员的姓名，工作和薪水。declare cursor v_cursor is select * from emp where deptno=&部门号; begin for v_emp in v_cursorloop dbms_output.put_line(v_emp.ename|-|v_emp.job|-|v_emp.sal); end loop; end;

8、3.使用带参数的游标，实现第2题。declare cursor v_cursor(p_deptno number) is select * from emp where deptno=p_deptno; v_deptno number(2); begin v_deptno:=&部门号; for v_emp in v_cursor(v_deptno) loop dbms_output.put_line(v_emp.ename|-|v_emp.job|-|v_emp.sal); end loop; end; 4.编写一个PL/SQL程序块，从emp表中对名字以“A”或“S”开始的所有雇员按他们基本

9、薪水的10%给他们加薪。declare cursor v_cursor is select * from emp; begin for v_emp in v_cursor loop if v_emp.ename like A% then update emp set sal=sal+sal*0.1 where empno=v_emp.empno; elsif v_emp.ename like S% then update emp set sal=sal+sal*0.1 where empno=v_emp.empno; end if; commit; end loop; end;5.emp表中对

10、所有雇员按他们基本薪水的10%给他们加薪，如果所增加后的薪水大于5000卢布，则取消加薪。declare cursor v_cursor is select * from emp;begin for v_emp in v_cursor loop if v_emp.sal * 1.1 5000 then update emp set sal = sal * 1.1 where empno = v_emp.empno; end if; commit; end loop;end;三，创建PL/SQL记录和PL/SQL表 创建过程 创建函数 3.创建一个过程，能向dept表中添加一个新记录.（in参数

11、）create or replace procedureinsert_dept(dept_no in number,dept_name in varchar2,dept_loc in varchar2) is begin insert into dept values(dept_no,dept_name,dept_loc); end;调用该存储过程： begin insert_dept(50,技术部,武汉); end; 4.创建一个过程，从emp表中带入雇员的姓名，返回该雇员的薪水值。（out参数）然后调用过程。create or replace procedure find_emp3(emp

12、_name in varchar2,emp_sal out number) is v_sal number(5); begin select sal into v_sal from emp where ename = emp_name; emp_sal:=v_sal; exception when no_data_found then emp_sal :=0; end;调用：declare v_sal number(5); begin find_emp3(ALLEN,v_sal); dbms_output.put_line(v_sal); end; 5.编写一个程序块，接受一个雇员号与一个百分

13、数，从emp表中将该雇员的薪水增加输入的百分比（*课堂没讲）。 （利用过程，in out 参数）create or replace procedure update_sal(emp_no in number,parsent in float) is begin update emp set sal=sal+sal*parsent where empno=emp_no; end;调用： begin update_sal(7499,0.5); end; 6.创建一个函数，它以部门号作为参数且返回那个部门的所有的所有雇员的整体薪水。 然后调用此函数。 7.创建一个函数，它以部门号作为参数传递并且使用

14、函数显示那个部门名称与位置。然后调用此函数。create or replace function find_dept(dept_no number) return dept%rowtype is v_dept dept%rowtype; begin select * into v_dept from dept where deptno=dept_no; return v_dept; end;调用函数：declare v_dept dept%rowtype; begin v_dept:=find_dept(30); dbms_output.put_line(v_dept.dname|-|v_de

15、pt.loc); end;四，创建程序包 创建程序件 创建触发器 1.创建在dept表中插入和删除一个记录的数据包，它且有一个函数(返回插入或删除的部门名称)和两个过程。然后调用包。create or replace package pack_1 is procedure find_emp(emp_no in number,emp_name out varchar2); procedure find_emp1(emp_name in varchar2,emp_no out number); function find_dname(dept_no number) return varchar2;

16、 end pack_1; create or replace package body pack_1is function find_dname(dept_no number) return varchar2is v_dname varchar2(20); begin select dname into v_dname from dept where deptno=dept_no; retrun v_dname; end;end pack_1;调用包：declare v_dname varchar2(20); begin v_dname:=pack_1.find_dname(50); dbms

17、_output.put_line(v_dname); end; 3.使用单独过程打开游标变量，将dept表中的记录显示出来。只创建程序包，无需主体。 4.创建一个行级别触发器，将从emp表中删除的记录输入到ret_emp表中。create or replace trigger delete_emp after delete on emp for each row begin insert into ret_emp values(:old.empno,:old.ename,:old.job, :old.mgr,:old.hiredate,:old.sal,:m,:old.deptno); end

18、;5.创建一个行级别触发器，停止用户删除雇员名为SMITH的记录。 create or replace trigger delete_smith before delete on emp for each row when (old.ename=SMITH) begin raise_application_error(-20001,不能删除该条信息!); end;6.创建一个语句级别触发器，不允许用户在Sundays使用emp表。 create or replace trigger t_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;