oracle学习笔记_创建存储过程

**

–1.用存储过程实现decode函数的功能 --输出参数是修改的记录数

**

create or replace procedure proc_demo2(up_count out number) is
begin
up_count := 0;
declare
cursor e_job is
select empno, job from emp;
begin
    for v_job in e_job loop
      if v_job.job = 'CLERK' then
        update emp set job = '办事员' where empno = v_job.empno;
      elsif v_job.job = 'SALESMAN' then
        update emp set job = '销售人员' where empno = v_job.empno;
      elsif v_job.job = 'MANAGER' then
        update emp set job = '经理' where empno = v_job.empno;
      else
        update emp set job = '其他' where empno = v_job.empno;
      end if;
      up_count := up_count + 1;
    end loop;
    commit;
  end;
end proc_demo2;

**

**–2.向manager表中插入职位为经理年薪大于7W的数据 ## --如果MANAGER表里已经存在此员工，则不插入 ** --如果不存在