Oracle---PL-SQL经典练习题

1、Oracle 作业题一.创建一个简单的PL/SQL程序块 使用不同的程序块组件工作 使用编程结构编写PL/SQL程序块 处理PL/SQL程序块中的错误 1.编写一个程序块,从emp表中显示名为“SMITH”的雇员的薪水和职位。declare v_emp emp%rowtype; begin select * 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） declare v_dept dept%rowtype; begin select * into v_dept from dept where deptno=&部门号; dbms_output.put_line(v_dept.dname|-|v_dept.loc); end; 3.编写一个程序块，利用%type属性，接受一个雇员号，从emp表中显示该雇员的整体薪水(即，薪水加佣金)。（*期末考试试题*）declare v_sal emp.sal%type; begin select sal+comm into v_s

4、al from emp where empno=&雇员号; dbms_output.put_line(v_sal); end; 4.编写一个程序块，利用%rowtype属性，接受一个雇员号，从emp表中显示该雇员的整体薪水(即，薪水加佣金)。方式一：（错误程序）（让学生思考错在哪里？） declare v_emp empcp%rowtype; begin select * into v_emp from empcp where empno = &雇员编号; dbms_output.put_line(整体薪水是：|v_emp.sal+v_m); end; declare v_emp

5、 emp%rowtype; begin select * into v_emp from emp where empno=&雇员号; 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

6、* into v_emp from emp where ename=&name; 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 se

7、t sal=sal+2000 where empno=v_emp.empno; 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 num

8、ber :=1; begin for v_emp in v_cursor 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 en

9、ame=&name; raise my_exception; exception 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_1 number :=&被除数;V_2 number :=&除数;V_result number;BeginV_result :=v_1/v_2;Dbms_outp

10、ut.put_line(v_result);ExceptionWhen Zero-divide thenDbms_output.put_line(除数不能为0！)；When others thenDbms_output.put_line(未知错误);End;二.声明和使用游标 使用游标属性 使用游标For循环工作 声明带参数的游标 （使用FOR UPDATE OF和CURRENT OF子句工作）1. 通过使用游标来显示dept表中的部门名称。declare cursor v_cursor is select * from dept; begin for v_dept in v_cursor l

11、oop 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;3. 使用带参数的游标，实现第2题。declare cursor

12、 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”开始的所有雇员按他们基本薪水的10%给他们加薪。declare cursor v_cu

13、rsor is select * from emp where ename like A% or ename like S%; 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.

14、 emp表中对所有雇员按他们基本薪水的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表中添加一个新记

15、录.（in参数）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_e

16、mp3(emp_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.编写一个程序块，接受一个雇

17、员号与一个百分数，从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.创建一个函数，它以部门号作为参

18、数传递并且使用函数显示那个部门名称与位置。然后调用此函数。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

19、|-|v_dept.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 va

20、rchar2; 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_output.put_line(v_dname); end; 3.使用单独过程打开游标变量，将dept表中的记录显示出来