存储过程基础提高

                                                            整理自网络

1.使用OUT类型的参数返回存储过程的结果
创建带输出参数的存储过程
create or replace procedure emp_count_out(ptotal out number) is
begin
  select count(*) into ptotal from my_emp;
end emp_count_out;
备注：输出参数为ptotal
 
创建另一个存储过程，并调用存储过程emp_count_out
create or replace procedure emp_list is
v_empcount number;        --定义变量
BEGIN
emp_count_out(v_empcount); --调用带有输出参数的存储过程,并将输出参数的值传递给变量v_empcount
dbms_output.put_line('人数:'||v_empcount);  
end emp_list;
 
执行存储过程
set serveroutput on
call emp_list();
执行结果
人数:14
Call completed.
 
2.带输入参数的存储过程 
编写给雇员增加工资的存储过程CHANGE_SALARY，通过IN类型的参数传递要增加工资的雇员编号和增加的工资额。
创建带输入参数的存储过程
create or replace procedure change_salary(p_empno number default 7788, p_paise number default 10) is
v_ename dongxiaobing.my_emp.ename%type;
v_sal   dongxiaobing.my_emp.sal%type;
begin
     select ename,sal into v_ename,v_sal from dongxiaobing.my_emp where empno=p_empno;
     update dongxiaobing.my_emp set sal=v_sal+p_paise where empno=p_empno;
     dbms_output.put_line('员工:'||v_ename||'工资改为'||to_char(v_sal+p_paise));
     commit;
     exception
       when others then
         dbms_output.put_line('发生错误,修改失败!');
         rollback;
end change_salary;
 
执行存储过程
call change_salary();
员工:SCOTT工资改为3030
Call completed.
执行结果
call change_salary();
员工:SCOTT工资改为3030   --参数默认值
Call completed.
或者
 call change_salary(7369,100);
员工:SMITH工资改为1100
Call completed.
 
练习：创建插入雇员信息的存储过程，将雇员编号等作为输入参数
create or replace procedure insert_emp(
v_empno my_emp.empno%type, --用my_emp表字段类型类定义变量
v_ename my_emp.ename%type,
v_job my_emp.job%type,    
v_mgr my_emp.mgr%type,
v_hiredate my_emp.hiredate%type,
v_sal my_emp.sal%type,
v_comm my_emp.comm%type,
v_deptno my_emp.deptno%type
) is
begin
  insert into my_emp values(v_empno,v_ename,v_job,v_mgr,v_hiredate,v_sal,v_comm,v_deptno);
  commit;
  end insert_emp;
 
执行存储过程
 call insert_emp(8001,'yang','SALESMAN',7698,to_date('1982-7-8','yyyy-mm-dd'),2000,300,30);
Call completed.
 
3.带输入输出参数的存储过程
使用IN OUT类型的参数，给电话号码增加区码。
   create or replace procedure add_region(p_phone_number in out varchar2) is  --定义输入输出参数
begin
    p_phone_number:='0334-'||p_phone_number;   --给参数赋值
end add_region;
 
 
调用存储过程add_region
create or replace procedure add_region_call is
v_phone_number varchar2(15);
begin
   v_phone_number:='23233981';  
   add_region(v_phone_number);
   dbms_output.put_line('电话号码为:'||v_phone_number);
end add_region_call;
 
 
执行存储过程，返回结果
SQL> call add_region_call();
电话号码为:0334-23233981
Call completed.
 
 
4.
如果该员工职位是MANAGER，并且在DALLAS工作那么就给他薪金加15％；
如果该员工职位是CLERK，并且在NEW YORK工作那么就给他薪金扣除5％;其他情况不作处理
 
使用sql实现
update my_emp set sal=sal*1.15 where empno in
(select empno from my_emp a,scott.dept b where a.deptno=b. deptno and a.job='MANAGER' and b.loc='DALLAS');
update my_emp set sal=sal*0.95 where empno in
(select empno from my_emp a,scott.dept b where a.deptno=b. deptno and a.job='CLERK' and b.loc='NEW YORK');
 
 
使用存储过程实现
create or replace procedure update_sarlary  is
  cursor a_cursor is select * from my_emp;
   b_cursor a_cursor%rowtype;
   v_loc scott.dept.loc%type;        
begin
     for b_cursor in a_cursor loop
     select loc into v_loc from scott.dept where deptno=b_cursor.deptno;
     if b_cursor.job='MANAGER' and v_loc='DALLAS' then
       update my_emp set sal=sal*1.15 where empno=b_cursor.empno;
     elsif b_cursor.job='CLERK' and v_loc='NEW YORK' then
       update my_emp set sal=sal*0.95 where empno=b_cursor.empno;
       else
         null;
         commit;
         end if;
         end loop;
end update_sarlary;
 
 
5.根据员工在各自部门中的工资高低排出在部门中的名次(允许并列).
使用sql实现
select deptno,empno,ename,sal,rank()over(partition by deptno order by sal) rn from scott.emp;
备注：rank()为跳跃排序，如果有两个第二名，接下来就是第四名
 
使用存储过程实现
create or replace procedure paixu is
cursor a_cursor is select deptno,empno,ename,sal,rank()over(partition by deptno order by sal) rn from scott.emp;
b_cursor a_cursor%rowtype;
begin
  for b_cursor in a_cursor loop
   dbms_output.put_line(b_cursor.deptno||','||b_cursor.empno||','||b_cursor.ename||','||b_cursor.sal||','||b_cursor.rn);
   end loop;
end paixu;
 
6.
实现批量修改
 
名字以"A"或"S"开始的所有雇员按他们的基本薪水的10%加薪
使用sql实现
update my_emp set sal=sal*1.1 where empno in (select empno from my_emp where ename like 'A%' or ename like 'S%');
使用存储过程实现
以下是两种存储过程方法
create or replace procedure update_salary_3 is
cursor test1_cursor is select * from my_emp where ename like 'A%' or ename like 'S%';
test2_cursor test1_cursor%rowtype;
begin
   for test2_cursor in test1_cursor loop
       update my_emp set sal=sal*1.1 where empno=test2_cursor.empno;
       commit;
       end loop;
end update_salary_3;
##################################
create or replace procedure update_salary_3 is
cursor test1_cursor is select * from my_emp;
test2_cursor test1_cursor%rowtype;
begin
   for test2_cursor in test1_cursor loop
       if test2_cursor.ename like 'A%' or  test2_cursor.ename like 'S%' then
       update my_emp set sal=sal*1.1 where empno=test2_cursor.empno;
       end if;
       commit;
       end loop;
end update_salary_3; 
 
7.对直接上级是'BLAKE'的所有员工，按照参加工作的时间加薪：
81年6月以前的加薪10％
81年6月以后的加薪5％
 
使用sql实现
update my_emp set sal=sal*1.1 where hiredate<to_date('1981/06/01','yyyy-mm-dd')  and 
empno in (select empno from my_emp where mgr=(select empno from my_emp where ename='BLAKE'));


update my_emp set sal=sal*1.05 where hiredate>to_date('1981/06/01','yyyy-mm-dd') and
empno in (select empno from my_emp where mgr=(select empno from my_emp where ename='BLAKE'));
commit;
 
使用存储过程实现
create or replace procedure update_salary_2 is
cursor a_cursor is select * from my_emp where mgr=(select empno from my_emp where ename='BLAKE');
b_cursor a_cursor%rowtype;
begin
    for b_cursor in a_cursor loop
        if b_cursor.hiredate
        update my_emp set sal=sal*1.1 where empno=b_cursor.empno;
        elsif
           b_cursor.hiredate>to_date('1981/06/01','yyyy-mm-dd') then
        update my_emp set sal=sal*1.05 where empno=b_cursor.empno;
      end if;
      commit;
      end loop;
end update_salary_2;
 
 8.对所有职位为'saleman'增加佣金500
 sql实现
update my_emp set comm=comm+500 where empno in (select empno from my_emp where job='SALESMAN');
 
存储过程实现
create or replace procedure update_salary_4 is
cursor a_cursor(pjob my_emp.job%type) is                  --定义带参数的游标
select * from my_emp where job=pjob for update of comm;   --锁定符合条件的行
b_cursor a_cursor%rowtype;
v_comm my_emp.comm%type;        --定义变量
begin
  for b_cursor in a_cursor('SALESMAN') loop   --传递值给游标
    v_comm:=b_cursor.comm+500;  --变量赋值
    update my_emp set comm=v_comm WHERE CURRENT OF a_cursor; ---从当前游标修改
    end loop;
end update_salary_4;
 
9.提升两个资格最老的"职员"为"高级职员"（工作时间越长，优先级越高）
sql实现
 
update my_emp set job='HIGHCLERK' where empno in (select empno from
(select * from my_emp where job='CLERK' order by hiredate)
where rownum<3);
 
存储过程实现
create or replace procedure update_job_1 is
cursor a_cursor is
select * from
(select * from my_emp where job='CLERK' order by hiredate)
where rownum<3;
b_cursor a_cursor%rowtype;
begin
   for b_cursor in a_cursor loop
     update my_emp set job='HIGHCLERK' where empno in b_cursor.empno;
     commit;
     end loop; 
end update_job_1;
 
10.对所有雇员按他们基本薪水的10%加薪，如果所增加的薪水大于5000，则取消加薪（并输出工资情况）
create or replace procedure update_salary_5 is
cursor a_cursor is select * from my_emp for update of sal;
b_cursor a_cursor%rowtype;
v_newsal my_emp.sal%type;
begin
    for b_cursor in a_cursor loop
      if b_cursor.sal>=5000 then
        v_newsal:=b_cursor.sal;
        dbms_output.put_line(b_cursor.ename||':'||'保持原来的工资:'||v_newsal);
        else
          v_newsal:=b_cursor.sal*1.1;
          dbms_output.put_line(b_cursor.ename||':'||'工资更新为:'||v_newsal);
          end if;
          update my_emp set sal=v_newsal where current of a_cursor;
          end loop;
          commit;
end update_salary_5;
 
11.显示emp的第四条记录
使用sql实现
select * from
(select rownum rn,e.* from my_emp e where rownum<3)
where rn>=2;
 
使用存储过程实现
create or replace procedure select_record is
cursor a_cursor is select * from my_emp;
b_cursor a_cursor%rowtype;
begin
     for b_cursor in a_cursor loop
      if a_cursor%rowcount=4 then
     Dbms_output.put_line(b_cursor.empno||','||b_cursor.ename
     ||','||b_cursor.job||','||b_cursor.mgr||','||b_cursor.hiredate
     ||','||b_cursor.sal||','||b_cursor.comm||','||b_cursor.deptno);
     end if;
     end loop;
 end select_record;
 
12.求1-100之间的素数


create or replace procedure test is
pag boolean:=true;
begin
for i in 1..100 loop
  for j in 2..i-1 loop
    if mod(i,j)=0 then
      pag:=false;
      end if;
  end loop;
    if pag then
      dbms_output.put_line(i);
      end if;
    pag:=true;
   end loop;
end test;
 
13.给雇员加薪10%的过程，这之后，检查如果已经雇佣该雇员超过60个月，则给他额外加薪3000.
create or replace procedure update_salary_6 is
cursor a_cursor is select * from my_emp for update of sal;
b_cursor a_cursor%rowtype;
v_newsal my_emp.sal%type;
begin
  for b_cursor in a_cursor loop
    if months_between(sysdate,b_cursor.hiredate)>60 then
       v_newsal:=b_cursor.sal*1.1+3000;
       else
       v_newsal:=b_cursor.sal*1.1;
      end if;
      update my_emp set sal=v_newsal where current of a_cursor; 
    end loop; 
    commit;
end update_salary_6;
 
14.一个函数以检查所指定雇员的薪水是否有效范围内。
不同职位的薪水范围为：
Designation Raise 
Clerk 1500-2500 
Salesman 2501-3500 
Analyst 3501-4500
Others 4501 and above
如果薪水在此范围内，则显示消息"Salary is OK"，否则，更新薪水为该范围内的最低值
 
使用函数实现
create or replace procedure update_salary_7(p_empno in my_emp.empno%type, ss out varchar2) is
 v_name my_emp.ename%type;
 v_job  my_emp.job%type;
 v_sal  my_emp.sal%type;
begin
   select ename,job,sal into v_name,v_job,v_sal from my_emp where empno=p_empno;
      if v_job='CLERK' then
      if v_sal>=1500 and v_sal<=2500 then
         ss:='Salary is OK';
         else
            v_sal:=1500;
         ss:='sal is:'||to_char(v_sal);  
         end if;
     elsif v_job='SALESMAN' then
        if v_sal>=2501 and v_sal<=3000 then
         ss:='Salary is OK';
         else
            v_sal:=2501;
         ss:='sal is:'||to_char(v_sal);  
         end if;
      elsif v_job='ANALYST' then
         if v_sal>=3500 and v_sal<=4500 then
         ss:='Salary is OK';
         else
           v_sal:=3500;
         ss:='sal is:'||to_char(v_sal);
         end if;
      elsif v_sal>=4501 then
         ss:='Salary is OK';
      else
         v_sal:=4501;
         ss:='sal is:'||to_char(v_sal);
         end if;
     update my_emp set sal=v_sal where empno=p_empno;       
end update_salary_7;
 
执行函数
BEGIN 
DBMS_OUTPUT.PUT_LINE(salary_test(7499));  
END;
 
返回结果
Salary is OK
 
15.输入员工编号，返回员工姓名
使用函数实现
create or replace function get_emp_ename(f_empno in my_emp.empno%type) return varchar2 is
v_ename varchar2(20);
begin
    select ename into v_ename from my_emp where empno=f_empno;
     return(v_ename);  
    exception
        when no_data_found then
        dbms_output.put_line('雇员编号不存在!');
      when too_many_rows then
        dbms_output.put_line('有重复的雇员编号!');
      when others then
        dbms_output.put_line('编号输入错误!');
end get_emp_ename;
 
 执行
begin
  dbms_output.put_line('员工姓名为:'||get_emp_ename(7369));
 end;
 
结果
员工姓名为:SMITH
#####################################################
使用带输出参数的存储过程实现
create or replace procedure update_salary_7(p_empno in my_emp.empno%type,name out my_emp.ename%type) is
begin
select ename into name from my_emp where empno=p_empno;
dbms_output.put_line('雇员姓名:'||name);
exception
        when no_data_found then
        dbms_output.put_line('雇员编号不存在!');
      when too_many_rows then
        dbms_output.put_line('有重复的雇员编号!');
      when others then
        dbms_output.put_line('编号输入错误!');
end update_salary_7;
 
执行
declare
 name my_emp.ename%type;
begin
  empinfo(7369,name);
  end;
 
结果
雇员姓名:SMITH
 
16.(华为笔试题) 
用户资料表：serv( serv_id number(10), pro_id number(10), user_type varchar2(30), terminal_name varchar2(30) )
其中，serv_id 为用户标识，是serv表的主键，prod_id为产品标识；user_type为用户类型；terminal_name为终端类型
终端类型临时表：terminal(serv_id number(10),terminal_name varchar2(30))
初始化的情况下，serv表的serv_id,prod_id,user_type字段是已知的，terminal_name字段是空的，现在主要根据prod_id,user_type字段的值来更新terminal_name字段
更新条件为：
当条件满足“prod_id = 1 and user_type='A'“时，terminal_name更新为‘固话’
当条件满足”prod_id = 1 and user_type='B'“时，terminal_name更新为‘小灵通’
当条件满足”prod_id = 2“时，terminal_name更新为‘宽带’
当条件满足”user_type=‘C’“时，terminal_name更新为‘CDMA’
以上条件均不满足时，terminal_name更新为  -1；
create table serv(
serv_id number(10),
pro_id number(10) not null,
user_type varchar2(30)  not null,
terminal_name varchar2(30),
constraints serv_id_pk primary key(serv_id));
insert into serv values(0001,1,'A',null);
insert into serv values(0002,1,'B',null);
insert into serv values(0003,1,'C',null);
insert into serv values(0004,2,'A',null);
insert into serv values(0005,2,'B',null);
insert into serv values(0006,2,'C',null);
insert into serv values(0007,3,'A',null);
insert into serv values(0008,3,'B',null);
insert into serv values(0009,3,'C',null);
create table terminal(
serv_id number(10) not null,
terminal_name varchar2(30)
);
insert into terminal values(0001,null);
insert into terminal values(0002,null);
insert into terminal values(0003,null);
insert into terminal values(0004,null);
insert into terminal values(0005,null);
insert into terminal values(0006,null);
insert into terminal values(0007,null);
insert into terminal values(0008,null);
insert into terminal values(0009,null);
 
 create or replace procedure update_terminal is
cursor a_cursor is select * from serv_test for update of terminal_name;
b_cursor a_cursor%rowtype;
v_terminal_name serv_test.terminal_name%type;
begin
      open a_cursor;
      loop
      fetch a_cursor into b_cursor;
      exit when a_cursor%notfound;
      case
         when b_cursor.pro_id=1 and b_cursor.user_type='A' then
         v_terminal_name:='固话';
         when b_cursor.pro_id=1 and b_cursor.user_type='B' then
         v_terminal_name:='小灵通';
         when b_cursor.pro_id=2 then
         v_terminal_name:='宽带';
         when b_cursor.user_type='C' then
         v_terminal_name:='CDMA';  
         else
          v_terminal_name:='-1'; 
         end case;
          update serv_test set terminal_name=v_terminal_name where current of a_cursor;
       end loop;
      close a_cursor;
        
 update terminal_test tt set terminal_name=
 (select terminal_name from serv_test st where tt.serv_id=st.serv_id)
 where exists
(select 1 from serv_test st where st.serv_id=tt.serv_id);   
end update_terminal;