Oracle之PL/SQL存储过程与函数练习题（七）

1.创建一个存储过程，以员工号为参数，输出该员工的工资

法一：

create or replace procedure fa(v_empno number,v_sal out number)
is
beginselect sal into v_sal from emp where empno=v_empno;
end;
----------------------------------------------------------------
declarev_empno number(5):=7369;v_sal number(5);
beginfa(v_empno,v_sal);dbms_output.put_line(v_empno||','||v_sal);
end;

法二：

create or replace procedure fa(eno emp.empno%type)
isv_sal emp.sal%type;--声明变量，存储员工编号对应的工资
beginselect sal into v_sal from emp where empno=eno;dbms_output.put_line('该员工的工资为：'||v_sal);
end;
----------------------------------------------------------------
call fa(7369);

2.创建一个存储过程，以员工号为参数，修改该员工的工资。若该员工属于10号部门，则工资增加150；若属于20号部门，则工资增加200；若属于30号部门，则工资增加250；若属于其他部门，则增加300。

法一：

create or replace procedure fb(v_empno number)
isv_deptno number;v_sal number;
beginbeginselect deptno into v_deptno from emp where empno=v_empno;exceptionwhen no_data_found thendbms_output.put_line('该员工不存在');return;end;case v_deptnowhen 10 then v_sal:=150;when 20 then v_sal:=200;when 30 then v_sal:=250;else v_sal:=300;end case;update empset sal=sal+v_salwhere deptno=v_deptno;
end;
----------------------------------------------------------------
call fb(7369);

法二：

create or replace procedure fb(eno emp.empno%type)
isv_dno emp.deptno%type;--声明变量存储该员工对应的部门编号
beginbeginselect deptno into v_dno from emp where empno=eno;exceptionwhen NO_DATA_FOUND thendbms_output.put_line('该员工不存在');return;--退出end;case v_dnowhen 10 thenupdate emp set sal=sal+150 where empno=eno;when 20 thenupdate emp set sal=sal+200 where empno=eno;when 30 thenupdate emp set sal=sal+250 where empno=eno;elseupdate emp set sal=sal+300 where empno=eno;end case;
end;
----------------------------------------------------------------
call fb(7369);

3.创建一个存储过程，以员工号为参数，返回该员工的工作年限（以参数形式返回）。

法一：

create or replace procedure f3(v_empno number,v_hd out date)
is
beginselect hiredate into v_hd from emp where empno=v_empno;
end;
----------------------------------------------------------------
declarev_hd date;
beginf3(7369,v_hd);dbms_output.put_line(floor(months_between(sysdate,v_hd)/12));
end;

法二：

create or replace procedure f3(eno emp.empno%type,v_year out number)
is
beginselect (sysdate-hiredate)/365 into v_year from emp where empno=eno;
end;
----------------------------------------------------------------
declarev_y number;--声明变量，用来接收存储过程返回输出的工作年限
beginf3(7369,v_y);--调用存储过程，v_y存储的是工作年限dbms_output.put_line('工作年限：'||floor(v_y));
end;

4.创建一个存储过程，以部门号为参数，输出入职日期最早的10个员工信息。

法一：

create or replace procedure f4(v_deptno number)
is
beginfor v in (select e.*,rownum from(select * from emp where deptno=v_deptno order by hiredate)e where rownum<=2) loopdbms_output.put_line(v.ename||','||v.job||','||v.sal||','||v.hiredate);end loop;
end;
----------------------------------------------------------------
call f4(20);

法二：

create or replace procedure f4(dno emp.deptno%type)
iscursor cur_e is select e.*,rownum from (select * from emp where deptno=dno order by hiredate) e where rownum<=10;
begin for e in cur_e loopdbms_output.put_line(e.ename||','||e.sal||','||e.hiredate);end loop;
end;
----------------------------------------------------------------
call f4(30);

5.创建一个函数，以员工号为参数，返回该员工的工资。

create or replace function f1(v_empno number)
return number
isv_sal number(10);
beginselect sal into v_sal from emp where empno=v_empno;return v_sal;
end;
----------------------------------------------------------------
begindbms_output.put_line(f1(7369));
end;
或：
select f1(7369) from dual;

6.创建一个函数，以部门号为参数，返回该部门的平均工资。

create or replace function f6(v_deptno number)
return number
isv_avgsal number(10);
beginselect avg(sal) into v_avgsal from emp where deptno=v_deptno;return v_avgsal;
end;
----------------------------------------------------------------
begindbms_output.put_line(f6(30));
end;

7.创建一个函数，以员工号为参数，返回该员工所在的部门的平均工资。

create or replace function f7(v_empno number)
return number
isv_deptno emp.deptno%type;v_avgsal number(10);
beginselect deptno into v_deptno from emp where empno=v_empno;select avg(sal) into v_avgsal from emp where deptno=v_deptno;return v_avgsal;
end;
----------------------------------------------------------------
begindbms_output.put_line(f7(7369));
end;

8.创建一个存储过程，以员工号和部门号作为参数，修改员工所在的部门为所输入的部门号。如果修改成功，则显示“员工由……号部门调入调入……号部门”；如果不存在该员工，则显示 “员工号不存在，请输入正确的员工号。”；如果不存在该部门，则显示 “该部门不存在，请输入正确的部门号。”。

法一：

create or replace procedure fc(v_empno number,v_deptno number)
isv_esum number(10);v_d varchar2(10);v_dsum number(5);
beginselect count(*) into v_esum from emp where empno=v_empno;select count(*) into v_dsum from emp where deptno=v_deptno;if v_esum=0 thendbms_output.put_line('员工号不存在，请输入正确的员工号。');end if;if v_dsum=0 thendbms_output.put_line('该部门不存在，请输入正确的部门号。');end if;if v_esum=1 and v_dsum>=1 thendbms_output.put_line('员工由'||v_d||'号部门调入'||v_deptno||'号部门. ');select deptno into v_d from emp where empno=v_empno;update emp set deptno=v_deptno where empno=v_empno;end if;
end;
----------------------------------------------------------------
call fc(1254534,30);

法二：

create or replace procedure fc(eno emp.empno%type,dno emp.deptno%type)
isdno_1 emp.deptno%type;--修改前dno_2 emp.deptno%type;--修改后
beginbegin--员工号是不是存在--根据员工编号查询出该员工对应的部门select deptno into dno_1 from emp where empno=eno;exceptionwhen no_data_found thendbms_output.put_line('该员工不存在');return;--退出end;begin--部门号是不是存在select deptno into  dno_2  from dept where deptno=dno;exceptionwhen no_data_found thendbms_output.put_line('该部门不存在');return;--退出end;    update emp set deptno=dno where empno=eno;dbms_output.put_line('员工由'||dno_1||'号部门调入调入'||dno||'号部门');
end;
----------------------------------------------------------------
call fc(7369,12);

9.创建一个存储过程，以一个整数为参数，输入工资最高的前几个（参数值）员工的信息。

法一：

create or replace procedure fd(n number)
iscursor v_emp is select * from emp order by sal desc;v_n number:=0;
beginfor i in v_emp loopdbms_output.put_line(i.ename||','||i.sal);v_n:=v_n+1;exit when v_n=n;end loop;
end;
----------------------------------------------------------------
call fd(5);

法二：

create or replace procedure fd(i number)
iscursor c_e is select e.*,rownum from (select * from emp order by sal desc) e where rownum<=i;
beginfor e in c_e loopdbms_output.put_line(e.ename||','||e.sal);end loop;
end;
----------------------------------------------------------------
call fd(3);

10.创建一个存储过程，以两个整数为参数，输出工资排序在两个参数之间的员工信息。

法一：

create or replace procedure fe(m number,n number)
iscursor v_emp is select * from emp where sal between m and n;
beginfor i in v_emp loopdbms_output.put_line(i.ename||','||i.sal);end loop;
end;
----------------------------------------------------------------
call fe(2000,4000);

法二：

create or replace procedure fe(i1 number,i2 number)
isbeginfor e in (select * from emp where sal between i1 and i2) loopdbms_output.put_line(e.ename||','||e.sal);end loop;
end;
----------------------------------------------------------------
call p_t10(3000,5000);

11.创建一个过程，能向dept表中添加一个新记录。（in参数）

create or replace procedure ff(v_id number,v_name varchar2,v_loc varchar2)
is
begininsert into deptvalues(v_id,v_name,v_loc);
end;
----------------------------------------------------------------
call ff(50,'rongyao','huawei');

12.从emp表中查询给定职工（提示：使用&来输入员工编号）的职工姓名和工资。（要求：利用out模式的参数将值传给调用者。）

create or replace procedure fg(v_e in out emp%rowtype)
is
beginselect * into v_e from emp where empno=v_e.empno;
end;
----------------------------------------------------------------
declarev_e emp%rowtype;
beginv_e.empno:=&员工编号;fg(v_e);dbms_output.put_line(v_e.ename||', '||v_e.sal);
end;

13.创建一个过程，在执行调用过程时，可随机输入emp表中某个雇员的姓名，根据雇员的姓名，返回该雇员的薪水值，并输出。（out参数）。

create or replace procedure fh(v_name varchar2,v_sal out number)
is
beginselect sal into v_sal from emp where ename=v_name;
end;
----------------------------------------------------------------
declarev_ename emp.ename%type:='&雇员名';v_sal emp.sal%type;
beginfh(v_ename,v_sal);dbms_output.put_line(v_ename||','||v_sal);
end;

14.编写过程，实现交换两个变量的值的功能。并输出交换前和交换后的两个值。（in out参数）

create or replace procedure fi(m in out number,n in out number)
isv number(5);
beginv:=m;m:=n;n:=v;
end;
----------------------------------------------------------------
declarem number:=4;n number:=5;
begindbms_output.put_line(m||', '||n);fi(m,n);dbms_output.put_line(m||', '||n);
end;

15.创建存储过程，根据员工编号删除emp表中的相关记录。（提示：由调用语句提供的员工编号来删除记录，要求员工编号可输入。）

create or replace procedure fj(v_id number)
is
begindelete from emp where empno=v_id;
end;
----------------------------------------------------------------
call fj(7369);

16. 创建存储过程：输入部门编号，输出emp表中该部门所有职工的员工编号、姓名、工作岗位。

create or replace procedure fk(v_id number)
iscursor v_emp is select empno,ename,job from emp where deptno=v_id;
beginfor i in v_emp loopdbms_output.put_line(i.empno||','||i.ename||','||i.job);end loop;
end;
----------------------------------------------------------------
call fk(30);

17.编写一个过程，指定一个员工编号与一个工资增加的百分比，使emp表中将该员工的工资（sal）增加输入的百分比。

create or replace procedure fl(v_id number,v_salp number)
is
beginupdate empset sal=sal*(1+v_salp/100)where empno=v_id;
end;
----------------------------------------------------------------
call fl(7369,30);

18.创建函数,从emp表中查询指定员工编号的职工的工资

create or replace function fm(v_id number)
return number
isv_sal emp.sal%type;
beginselect sal into v_sal from emp where empno=v_id;return v_sal;
end;
---------------------------------------------------------------- 
begindbms_output.put_line(fm(7369));
end;

19.创建函数，返回emp表中指定职工的工资和姓名。

法一：

create or replace function fm(v_id number)
return number
isv_sal emp.sal%type;v_ename emp.ename%type;
beginselect sal,ename into v_sal,v_ename from emp where empno=v_id;dbms_output.put_line(v_ename);return v_sal;
end;
----------------------------------------------------------------
begindbms_output.put_line(fm(7369));
end;

法二：

create or replace function fn(v_id number,v_name out varchar2)
return number
isv_sal emp.sal%type;
beginselect sal,ename into v_sal,v_name from emp where empno=v_id;return v_sal;
end;
----------------------------------------------------------------
declarev_sal number(5);v_name varchar2(5);
beginv_sal:=fn(7369,v_name);dbms_output.put_line(v_name||','||v_sal);
end;

20. 创建函数，根据给定的部门编号(提示: 利用&)，计算该部门所有职工的平均工资。

create or replace function fo(v_id number)
return number
isavg_sal number;
beginselect avg(nvl(sal,0)) into avg_sal from emp where deptno=v_id;return avg_sal;
end;
----------------------------------------------------------------
declarev_id emp.deptno%type:=&部门编号;
begindbms_output.put_line(fo(v_id));
end;

21.创建函数，将emp表中工资低于平均工资的职工工资加上200,并返回修改了工资的总人数.

法一：

create or replace function fp
return number
iscursor v_emp is select ename from emp where sal<(select avg(nvl(sal,0)) from emp);v_sum number:=0;
beginfor i in v_emp loopupdate empset sal=sal+200where ename=i.ename;v_sum:=v_sum+1;end loop;return v_sum;
end;
----------------------------------------------------------------
begindbms_output.put_line(fp);
end;

法二：

create or replace function fp
return number
is
beginupdate emp set sal=sal+200 where sal<(select avg(sal) from emp);return sql%rowcount;
end;
----------------------------------------------------------------
begindbms_output.put_line(fp());
end;

22.创建一个函数，仅有一个形参，它接收调用函数中传递过来的实参部门号，函数的返回值为该部门的一整条记录信息

create or replace function fq(v_id number)
return dept%rowtype
is v_dept dept%rowtype;
beginselect * into v_dept from dept where deptno=v_id;return v_dept;
end;
----------------------------------------------------------------
declarei dept%rowtype;
begini:=fq(30);dbms_output.put_line(i.deptno||', '||i.dname||', '||i.loc);
end;

23.

c1 c2

1 西
1 安
1 的
2 天
2 气
3 好
……
转换为
1 西安的
2 天气
3 好
要求：不能改变表结构及数据内容，仅在最后通过SELECT显示出这个查询结果

create table cc(c1 number(5),c2 varchar2(5)
);
insert into cc values(1,'西');
insert into cc values(1,'安');
insert into cc values(1,'的');
insert into cc values(2,'天');
insert into cc values(2,'气');
insert into cc values(3,'好');
create or replace function fcc(n number)
return varchar2
iss varchar2(50);
beginfor i in (select * from cc where c1=n) loops:=s||i.c2;end loop;return s;
end;
----------------------------------------------------------------
select c1,fcc(c1) from cc group by c1;

24.查找出当前用户模式下，每张表的记录数，以scott用户为例，结果应如下：

DEPT…4
EMP…14
BONUS…0
SALGRADE…5
提示：查找用户下所有表名的sql为select table_name from user_tables;
法一：

create or replace procedure fs
isv_tablename varchar2(100);v_sql varchar2(100);v_sum number(10);cursor v_name is select table_name from user_tables;
beginopen v_name;fetch v_name into v_tablename;while v_name%found loopexecute immediate 'select count(*) from '||v_tablename into v_sum;dbms_output.put_line(v_tablename||'-----------'||v_sum);fetch v_name into v_tablename;end loop;close v_name;
end;
----------------------------------------------------------------
call fs();

法二：

create or replace procedure fs
isc number; v_sql varchar2(100);
beginfor tn in (select table_name from user_tables) loopv_sql:='select count(*) from '||tn.table_name;dbms_output.put_line(v_sql);execute immediate v_sql into c;dbms_output.put_line(tn.table_name||'----------'||c);end loop;
end;
----------------------------------------------------------------
call fs();

25.存储过程和函数，完成下面的功能：输入姓名，课程名，成绩，该过程完成对SC表的插入或修改操作，若插入成功，返回成功信息，若该选课信息已经存在，则修改其成绩为输入的成绩，若遇系统错误，返回错误信息。

create or replace procedure f_25(v_name varchar2,c_name varchar2,v_score number)
isv_sno varchar2(10);v_cno varchar2(10);n number(5);
beginselect sno into v_sno from student where sname=v_name;select cno into v_cno from course where cname=c_name;select count(*) into n from sc where sno=v_sno and cno=v_cno;if n=1 thenupdate sc set score=v_score where sno=v_sno and cno=v_cno;dbms_output.put_line('修改成功');else insert into sc values(v_sno,v_cno,v_score);dbms_output.put_line('插入成功');end if;exceptionwhen others thendbms_output.put_line(sqlerrm);
end;
----------------------------------------------------------------
call f_25('李四','SSH',60);

26.建立过程，当传入姓名和年龄，保存在stu表中.首先判断stu表表是否存在，若不存在则创建该表格（包括姓名和年龄两列）

create or replace procedure f_26(v_name varchar2,v_age number)
isn number(5);
beginselect count(*) into n from user_tables where table_name='STU';if n=0 thenexecute immediate 'create table stu(sname varchar2(5),age number)';end if;execute immediate 'insert into stu values('''||v_name||''','||v_age||')';
end;
----------------------------------------------------------------
declarev_name varchar2(5):='&姓名';v_age number(5):=&年龄;
beginf_26(v_name,v_age);
end;