Oracle PL SQL经典练习题1

end;

2. 使用For循环，接受一个部门号，从emp表中显示该部门的所有雇员的姓名，工作和

薪水。

declare

cursor v_cursor is select * from emp where deptno=&部门号; begin

for v_emp in v_cursor

loop

dbms_output.put_line(v_emp.ename||'--'||v_emp.job||'--' ||v_emp.sal);

end loop; end;

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”开始的所有雇员按他们基本薪水的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%给他们加薪，如果所增加后的薪水大于

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参数）

create or replace procedure

insert_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_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.编写一个程序块，接受一个雇员号与一个百分数，从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.创建一个函数，它以部门号作为参数传递并且使用函数显示那个部门名称与位置。

然后调用此函数。

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_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 varchar2; end pack_1;

create or replace package body pack_1 is

function find_dname(dept_no number) return varchar2 is

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表中的记录显示出来。只创建程序包，无需主体。

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,:old.comm,:old.deptno); end;

5.创建一个行级别触发器，停止用户删除雇员名为\的记录。 create or replace trigger delete_smith

before delete on emp for each row

when (old.ename='SMITH') begin

raise_application_error(-20001,'不能删除该条信息!'); end;

6. 创建一个语句级别触发器，不允许用户在\使用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;