oracle 存储过程简单示例

一、建表

declare
exitTab number;
begin
select count(1) into exitTab from user_tables t where upper(t.table_name) = 'EMP';
if exitTab = 0 then
execute immediate '
create table emp (guid varchar(32),name varchar2(10),sal number(6,2),deno varchar(32))';
end if;
end;

二、插入测试数据

declare
  maxnumber int := 10;
  i         int := 1;
begin
  for i in 1 .. maxnumber loop
    insert into emp (guid, name, sal) values (sys_guid(), '张' || i, i);
  end loop;
  commit;

end;

三、为某员工提薪多少倍

1、新建存储过程

create or replace procedure emp_pro(v_name varchar2,v_salrare number) is
  v_sal emp.sal%type;
begin
  select sal into v_sal from emp where name = v_name;
  if v_sal < 100 then
    update emp set sal = sal * v_salrare where name = v_name;
  end if;

end;

--此处传递了两个变量 员工名称(v_name ),涨薪几倍(v_salrare )

2、调用存储过程

begin
  emp_pro('张1',1.1);

end;

--为张1涨薪10%