1、Oracle 作业题一.创建一个简单的 PL/SQL 程序块使用不同的程序块组件工作使用编程结构编写 PL/SQL 程序块处理 PL/SQL 程序块中的错误1.编写一个程序块,从 emp 表中显示名为“SMITH ”的雇员的薪水和职位。declarev_emp emp%rowtype;beginselect * into v_emp from emp where ename=SMITH;dbms_output.put_line(员工的工作是：|v_emp.job| ； 他的薪水是：|v_emp.sal);end;2.编写一个程序块，接受用户输入一个部门号，从 dept 表中显示该部门的名称与所
2、在位置。方法一：（传统方法）declarev_loc deptcp.dname%type;v_dname deptcp.dname%type;v_deptno deptcp.deptno%type;beginv_deptno :=select loc,dname into v_loc,v_dname from deptcp where deptno=v_deptno;dbms_output.put_line(员工所在地是：| v_loc|；部门名称是：| v_dname);exceptionwhen no_data_found then dbms_output.put_line(您输入的部门编
3、号不存在，请从新输入，谢谢 );end;方法二：（使用%rowtype ）declarev_dept dept%rowtype;beginselect * into v_dept from dept where deptno=dbms_output.put_line(v_dept.dname|-|v_dept.loc);end;3.编写一个程序块，利用%type 属性，接受一个雇员号，从 emp 表中显示该雇员的整体薪水(即，薪水加佣金)。 （*期末考试试题*）declarev_sal emp.sal%type;begin select sal+comm into v_sal from emp
4、 where empno=dbms_output.put_line(v_sal);end;4.编写一个程序块，利用%rowtype 属性，接受一个雇员号，从 emp 表中显示该雇员的整体薪水(即，薪水加佣金)。方式一：（错误程序） （让学生思考错在哪里？）declarev_emp empcp%rowtype;beginselect * into v_emp from empcp where empno = dbms_output.put_line(整体薪水是：| v_emp.sal+v_m);end;declarev_emp emp%rowtype;beginselect * into v_e
5、mp from emp where empno=dbms_output.put_line(v_emp.sal+v_m);end;5.某公司要根据雇员的职位来加薪，公司决定按下列加薪结构处理：Designation Raise-Clerk 500Salesman 1000Analyst 1500Otherwise 2000编写一个程序块,接受一个雇员名，从 emp 表中实现上述加薪处理。 （*期末考试试题*）declare v_emp emp%rowtype;beginselect * into v_emp from emp where ename=if v_emp.job=CLERK then
6、update emp set sal=sal+500 where empno=v_emp.empno;elsif v_emp.job=SALESMAN thenupdate emp set sal=sal+1000 where empno=v_emp.empno; elsif v_emp.job=ANALYST thenupdate emp set sal=sal+1500 where empno=v_emp.empno; else update emp set sal=sal+2000 where empno=v_emp.empno; end if;commit;end;6.编写一个程序块，
7、将 emp 表中雇员名全部显示出来。declarecursor v_cursor is select * from emp;beginfor v_emp in v_cursorloopdbms_output.put_line(v_emp.ename);end loop; end;7.编写一个程序块，将 emp 表中前 5 人的名字显示出来。declarecursor v_cursor is select * from emp;v_count number :=1;beginfor v_emp in v_cursorloopdbms_output.put_line(v_emp.ename);v_
8、count := v_count+1;exit when v_count5;end loop; end;8.编写一个程序块，接受一个雇员名，从 emp 表中显示该雇员的工作岗位与薪水，若输入的雇员名不存在，显示“该雇员不存在”信息。 （*期末考试试题*）declare v_emp emp%rowtype;my_exception Exception;beginselect * into v_emp from emp where ename=raise my_exception;exceptionwhen no_data_found thendbms_output.put_line(该雇员不存在
9、! );when others thendbms_output.put_line(v_emp.job|-|v_emp.sal);end; 9.接受两个数相除并且显示结果，如果第二个数为 0，则显示消息“除数不能为 0”(课堂未讲)。declarev_dividend float;v_divisor float;v_result float;my_exception Exception;beginv_dividend:=v_divisor:=v_result:=v_dividend/v_divisor;raise my_exception;exceptionwhen my_exception t
10、hendbms_output.put_line(v_result);when others thendbms_output.put_line(除数不能为0);end;二.声明和使用游标使用游标属性 使用游标 For 循环工作声明带参数的游标（使用 FOR UPDATE OF 和 CURRENT OF 子句工作）1. 通过使用游标来显示 dept 表中的部门名称。declarecursor v_cursor is select * from dept;beginfor v_dept in v_cursorloopdbms_output.put_line(v_dept.dname);end loo
11、p; end;2. 使用 For 循环，接受一个部门号，从 emp 表中显示该部门的所有雇员的姓名，工作和薪水。declarecursor v_cursor is select * from emp where deptno=beginfor v_emp in v_cursorloop dbms_output.put_line(v_emp.ename|-|v_emp.job|-|v_emp.sal);end loop;end;3. 使用带参数的游标，实现第 2 题。declarecursor v_cursor(p_deptno number) is select * from emp wher
12、e deptno=p_deptno;v_deptno number(2);beginv_deptno:=for v_emp in v_cursor(v_deptno)loopdbms_output.put_line(v_emp.ename|-|v_emp.job|-|v_emp.sal);end loop;end; 4.编写一个 PL/SQL 程序块，从 emp 表中对名字以“A”或“S”开始的所有雇员按他们基本薪水的 10%给他们加薪。declarecursor v_cursor is select * from emp;beginfor v_emp in v_cursorloopif v_
13、emp.ename like A% thenupdate emp set sal=sal+sal*0.1 where empno=v_emp.empno;elsif v_emp.ename like S% thenupdate emp set sal=sal+sal*0.1 where empno=v_emp.empno;end if;commit;end loop;end;5. emp 表中对所有雇员按他们基本薪水的 10%给他们加薪，如果所增加后的薪水大于5000 卢布，则取消加薪。declarecursor v_cursor isselect * from emp;beginfor v_
14、emp in v_cursor loopif v_emp.sal * 1.1 5000 thenupdate emp set sal = sal * 1.1 where empno = v_emp.empno;end if;commit;end loop;end;三，创建 PL/SQL 记录和 PL/SQL 表创建过程创建函数3.创建一个过程，能向 dept 表中添加一个新记录.（in 参数）create or replace procedureinsert_dept(dept_no in number,dept_name in varchar2,dept_loc in varchar2)is
15、begininsert into dept values(dept_no,dept_name,dept_loc);end;调用该存储过程：begininsert_dept(50,技术部 ,武汉);end;4.创建一个过程，从 emp 表中带入雇员的姓名，返回该雇员的薪水值。 （out 参数）然后调用过程。create or replace procedurefind_emp3(emp_name in varchar2,emp_sal out number)isv_sal number(5);beginselect sal into v_sal from emp where ename = em
16、p_name;emp_sal:=v_sal;exceptionwhen no_data_found thenemp_sal :=0;end;调用：declarev_sal number(5);beginfind_emp3(ALLEN,v_sal);dbms_output.put_line(v_sal);end;5.编写一个程序块，接受一个雇员号与一个百分数，从 emp 表中将该雇员的薪水增加输入的百分比（*课堂没讲） 。（利用过程，in out 参数）create or replace procedureupdate_sal(emp_no in number,parsent in float)
17、isbeginupdate emp set sal=sal+sal*parsent where empno=emp_no;end;调用：beginupdate_sal(7499,0.5);end;6.创建一个函数，它以部门号作为参数且返回那个部门的所有的所有雇员的整体薪水。然后调用此函数。7.创建一个函数，它以部门号作为参数传递并且使用函数显示那个部门名称与位置。然后调用此函数。create or replace functionfind_dept(dept_no number)return dept%rowtypeisv_dept dept%rowtype;beginselect * int
18、o v_dept from dept where deptno=dept_no;return v_dept;end;调用函数：declarev_dept dept%rowtype;beginv_dept:=find_dept(30);dbms_output.put_line(v_dept.dname|-|v_dept.loc);end;四，创建程序包创建程序件创建触发器1.创建在 dept 表中插入和删除一个记录的数据包，它且有一个函数(返回插入或删除的部门名称)和两个过程。然后调用包。create or replace package pack_1isprocedure find_emp(e
19、mp_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;end pack_1;create or replace package body pack_1isfunction find_dname(dept_no number)return varchar2is v_dname varchar2(20);begin select dname into v_
20、dname from dept where deptno=dept_no;retrun v_dname;end;end pack_1;调用包：declarev_dname varchar2(20);beginv_dname:=pack_1.find_dname(50);dbms_output.put_line(v_dname);end;3.使用单独过程打开游标变量，将 dept 表中的记录显示出来。只创建程序包，无需主体。4.创建一个行级别触发器，将从 emp 表中删除的记录输入到 ret_emp 表中。create or replace trigger delete_empafter del
21、ete on emp for each rowbegininsert into ret_emp values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:m,:old.deptno);end;5.创建一个行级别触发器，停止用户删除雇员名为“SMITH“的记录。create or replace trigger delete_smithbefore delete on emp for each rowwhen (old.ename=SMITH)beginraise_application_error(-20001,不能删除该条信息! );end;6. 创建一个语句级别触发器，不允许用户在“Sundays“使用 emp 表。create or replace trigger t_control_empbefore insert or update or delete on empbeginif to_char(sysdate,DY,nls_date_language=AMERICAN)in(SUN) thenraise_application_error(-20001,不允许在星期天操作emp表);end if;end;
