Oracle_PL_SQL经典练习题

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表中显示该部门的名称与所在位置。

方法一：（传统方法）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_foundthen dbms_output.put_line('您输入的部门编号不存在，请从新输入，谢谢');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;beginselect sal+comm into v_sal from emp 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_emp from emp where empno=&雇员号;dbms_output.put_line(v_emp.sal+v_m);end;5.某公司要根据雇员的职位来加薪，公司决定按下列加薪结构处理：Designation Raise-----------------------Clerk 500Salesman 1000Analyst 1500Otherwise 2000编写一个程序块,接受一个雇员名，从emp表中实现上述加薪处理。

（*期末考试试题*）declarev_emp emp%rowtype;beginselect * into v_emp from emp where ename='&name';if v_emp.job='CLERK'thenupdate 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;elseupdate emp set sal=sal+2000 where empno=v_emp.empno;end if;commit;end;6.编写一个程序块，将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_count := v_count+1;exit when v_count>5;end loop;end;8.编写一个程序块，接受一个雇员名，从emp表中显示该雇员的工作岗位与薪水，若输入的雇员名不存在，显示“该雇员不存在”信息。

（*期末考试试题*）declarev_emp emp%rowtype;my_exception Exception;beginselect * into v_emp from emp where ename='&name';raise my_exception;exceptionwhen no_data_found thendbms_output.put_line('该雇员不存在!');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 thendbms_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 loop;2.使用For循环，接受一个部门号，从emp表中显示该部门的所有雇员的姓名，工作和薪水。

declarecursor v_cursor is select * from emp where deptno=&部门号;beginfor v_emp in v_cursorloopdbms_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 wheredeptno=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_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;5.emp表中对所有雇员按他们基本薪水的10%给他们加薪，如果所增加后的薪水大于5000卢布，则取消加薪。

declarecursor v_cursor isselect * from emp;beginfor v_emp in v_cursor loopif v_emp.sal * 1.1 < 5000thenupdate emp set sal = sal * 1.1where 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)isbegininsert into dept values(dept_no,dept_name,dept_loc);end;调用该存储过程：begininsert_dept(50,'技术部','武汉');end;4.创建一个过程，从emp表中带入雇员的姓名，返回该雇员的薪水值。