oracle存储过程简单例子

一.从emp表找出所有部门最低工资的员工.如果这个部门的最低员工工资大于1000,直接打印输出,否则插入全局临时表emp_tmp.最后还要更新一下临时表emp_tmp的描述字段des为'最低工资'.这样操作的例子意义不大,只是作为学习存储过程的一个例子.

1.源码

create or replace procedure myprocedure as

  tabExt    varchar2(10); --用于判断临时表是否存在的中间变量
  createStr varchar2(500); --用于创建临时表(如果临时表不存在)
  deleteStr varchar2(50); --用于删除临时表记录(如果临时表存在)
  insertStr varchar2(500); --用于向临时表插入数据(如果临时表存在)
  
  --查询所有部门号
  cursor cur_deptno is select deptno from emp group by deptno order by deptno asc;
  vdeptno emp.deptno%type;
  --查询此部门最低工资员工的信息
  cursor cur_emp(vdeptno number) is select * from (select * from emp where deptno=vdeptno order by sal asc) where rownum<2;
  vemp_rd emp%rowtype;


begin
  select count(1) into tabExt from all_tables where table_name = 'EMP_TMP';
  createStr := 'create global temporary table emp_tmp(
  empno number(4),
  ename varchar2(10),
  sal number(7,2),
  deptno number(2),
  des varchar2(32)
  )on commit preserve rows';
  deleteStr := 'delete from emp_tmp';

  if tabExt = 0 then
    execute immediate createStr;
    dbms_output.put_line('临时表创建成功!');
  else
    execute immediate deleteStr;
    dbms_output.put_line('删除记录完成!');
  end if;

  open cur_deptno;
  loop
    fetch cur_deptno into vdeptno;
    exit when cur_deptno%notfound;
    
    open cur_emp(vdeptno);
         fetch cur_emp into vemp_rd;
         if cur_emp%found then
           if vemp_rd.sal>1000 then --部门最低工资高于1000的,直接输出信息
             dbms_output.put_line(vdeptno||'部门的最低工资是'||vemp_rd.ename||'的工资:'||vemp_rd.sal);
           else --部门最低工资小于1000的要插入临时表
             
             insertStr:='insert into emp_tmp(empno,ename,sal,deptno) values('||vemp_rd.empno||','''||vemp_rd.ename||''','||vemp_rd.sal||','||vemp_rd.deptno||')';
             execute immediate insertStr;
             
           end if;
         end if;
    close cur_emp;
  end loop;
  commit;
  close cur_deptno;
  execute immediate 'update emp_tmp set des=''最低工资''';
end;

2.测试

begin
  myprocedure;
end;

执行这个存储过程,查看输出.,最后查看执行完之后的临时表数据.

select * from emp_tmp;

二.找出与入参员工同一个部门的员工信息(带有入参的存储过程).
1.源码

create or replace procedure myprocedure(pEname in varchar2) as
vdeptno emp.deptno%type;
vemp_rd emp%rowtype;
begin
  --找出入参员工的部门号
  select deptno into vdeptno from emp where emp.ename=pEname;
  if SQL%found then
    dbms_output.put_line('和'||pEname||'同部门的员工信息:');
  end if;
  --轮询所有与入参员工同一个部门的员工
  for vemp_rd in (select * from emp where emp.deptno=vdeptno)
  loop
    if vemp_rd.ename!=pEname then
       dbms_output.put_line('员工号:'||vemp_rd.empno||',员工名:'||vemp_rd.ename||',员工工资'||vemp_rd.sal);
    end if;
  end loop;
--处理异常
exception
  when no_data_found then
    dbms_output.put_line(pEname||'员工不存在!');
  when too_many_rows then
    dbms_output.put_line('不止一个员工叫'||pEname||'!');
end;

2.测试,查看输出

begin
  -- Call the procedure
  myprocedure('SMITH');
end;