willcool4ever • Oracle笔记(三)

分支语句
if 条件 then 语句
end if;

if 条件  then 语句1   else 语句2
end if;

if 条件1  then 语句1 elsif 条件2 then ......
--创建简单过程
create or replace procedure zxx_pro1 as
--声明部分
v_sal emp.sal%type;
begin
select sal into v_sal from emp where ename='SCOTT';
dbms_output.put_line('SCOTT的工资是'||v_sal);
end;
--调用过程
exec 过程名；
--创建过程，对工资低于2000的雇员工资提高10%
create or replace procedure sal_pro1(sname varchar2) as
v_sal emp.sal%type;
begin
select sal into v_sal from emp where ename=sname;
if v_sal<2000
   then  update emp set sal=sal+sal*0.1  where ename=sname;
end if;
end;
--创建过程
create or replace procedure sal_pro1(sname varchar2)
as
v_sal emp.sal%type;
begin
select sal into v_sal from emp where ename=sname;
if v_sal<2000 then
   begin
   update emp set sal=sal*1.1 where ename=sname;
   select sal into v_sal from emp where ename=sname;
   dbms_output.put_line(sname||'已调整工资为'||v_sal);
   end;
end if;
end;
--过程中例外的应用,绑定变量的应用
create or replace procedure emp_pro1(enum number)
as
v_enum  emp.empno%type;
v_name  emp.ename%type;
v_sal   emp.sal%type;
begin
select ename,sal,empno into v_name,v_sal,v_enum from emp where empno=enum;
if v_sal<2000 then
              begin
              update emp set sal=sal*1.1 where empno=v_enum;
              dbms_output.put_line('name is '||v_name||' salary is '||v_sal);
              end;
end if;
exception
when no_data_found then
     dbms_output.put_line('this number is not exsist!');
end;
--循环结构
--loop...end loop;
--建个简单的users表，循环添加记录
--userid number(4),username varchar2(16)
create table users(
userid  number(4),
username varchar2(16)
);
--创建过程循环添加10条记录到users表
create or replace procedure users_pro1
is
v_num users.userid%type:=1;
begin
    loop
    insert into users values(v_num,'洪蕾');
    exit when v_num=10;
    v_num:=v_num+1;
    end loop;
end;
-- for循环
--添加记录到users表
create or replace procedure users_pro2
is
i number(4);
begin
for i in reverse 11..20 loop
    insert into users values(i,'玉龙'）;
    end loop;
end;

create or replace procedure emp_pro1
is
v_name  emp.ename%type;
v_sal  emp.sal%type;
v_enum emp.empno%type;
begin
select ename,sal,empno into v_name,v_sal,v_enum from emp where empno=&no;
if v_sal<2000 then
              begin
              update emp set sal=sal*1.1 where empno=v_enum;
              dbms_output.put_line('name is '||v_name||'   salary is '||v_sal);
              end;
end if;
exception
  when  no_data_found then
  dbms_output.put_line('not exsist');
end;