Oracle(procedure)

--存储过程
-- 列1： 如果员工工资小于2000的涨百分之十
create or replace procedure test1(Name varchar2) is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where ename=Name;
if v_sal<2000 then
update emp set sal=sal*1.1 where ename = Name;
end if;
end;
--调用
exec test1('SCOTT');
-- 列2： 如果员工奖金不等于0的加一百，等于0的加两百
create or replace procedure test2(Name varchar2)is
v_comm emp.comm%type;
begin
select comm into v_comm from emp where ename=Name;
if v_comm<>0 then
update emp set comm=comm+100 where ename=Name;
else
update emp set comm=comm+200 where ename=Name;
end if;
end;
--调用
exec test2('SCOTT');

--列3：职位为PRESIDENT 的加1000 MANAGER 加500 其他加200
create or replace procedure test3(phNo number)is
v_job emp.job%type;
begin
select job into v_job from emp where empno=phNo;
if v_job='PRESIDENT' then
update emp set sal=sal+1000 where empno=phNo;
elsif v_job='MANAGER' then
update emp set sal=sal+500 where empno=phNo;
else
update emp set sal=sal+200 where empno=phNo;
end if;
end;
--调用
exec test3(7369);

-- 创建一个user表
create table user(userId number, username varchar2(40));
-- 循环向表里面 插入十条数据
create or replace procedure test4(phName varchar2) is
v_num number:=1;
begin
loop
insert into user1 values(v_num,phName);
exit when v_num=10;
v_num:=v_num+1;
end loop;
end;

exec test4('xiaona');


-- 插入十条数据 从11开始
create or replace procedure test5(phName varchar2) is
v_num number:=11;
begin
while v_num<=20 loop
insert into user1 values(v_num,phName);
v_num:=v_num+1;
end loop;
end;

exec test5('xiaona');

-- 创建book表
create table book(bookId number,bookName varchar2(20),publicName varchar2(30));
-- in 输入 默认
-- out 输出
create or replace procedure test6(BookId in number,BookName in varchar2
, pubName in varchar2)is
begin
insert into book values(BookId,BookName,pubName);
end;