学习历程->Oracle流程控制及存储过程

流程控制

1. 条件语句
   
   • if else

-- 声明一个变量i
declare i number;

-- begin 开始存储过程
begin
select count(*) into i from DVD_USER;
if i>0 and i<5 then
dbms_output.put_line('0<>5');
ELSIF i>=5 THEN
    dbms_output.put_line('>=5');
else
dbms_output.put_line('<=0');
end if;
end;

• case

declare i number;
BEGIN
  select count(*) into i from DVD_DVD;
  case i
  when 0 then dbms_output.put_line(0);
  when 1 then dbms_output.put_line(1);
  when 2 then dbms_output.put_line(2);
  when 3 then dbms_output.put_line(3);
    when 4 then dbms_output.put_line(4);
    when 5 then dbms_output.put_line(5);
    else dbms_output.put_line('else');
    end case;
end;
/

declare i number;
begin
  select count(*) into i from dvd_lr;
  case
  when 0<i and i<5 then dbms_output.put_line('0<>5');
  when 5<=i then dbms_output.put_line('>=5');
  else dbms_output.put_line('<=0');
  end case;
end;
/

1. 循环语句
   
   • 无条件循环 LOOP

declare
  i number := 1;
  j number := 0;
begin
  loop
    /* 另一种写法
    if i >= 11 then
      exit;
    end if;
    */
    exit when i>=5;
    j := j + i;
    i := i + 1;
  end loop;
  dbms_output.put_line(j);
end;
/

• while循环

declare
  i number := 1;
  j number := 1;
begin
  while i <= 100 loop
    if mod(i, 2) = 0 then
      j := j * i;
    end if;
    i := i + 1;
  end loop;
  dbms_output.put_line(j);
  dbms_output.put_line(length(to_char(j)));
end;
/

• for循环

declare
  i number := 1;
  j number := 1;
begin
  for i in 1..100 loop
        if mod(i, 2) = 0 then
      j := j * i;
    end if;
  end loop;

  dbms_output.put_line(j);
  dbms_output.put_line(length(to_char(j)));
end;
/

存储过程

1. 存储过程,倾向于数据库操作,没有返回值,可以写事务
   
   • 关键字:Procedure
   • 语法格式:

-- 参数有三种写法
-- 入参,出参
-- 参数格式 参数名称 in(可以省略)/out/in out 数据类型
create or replace procedure addDVDP(dvdName in varchar2, mResult out number) as
begin
  insert into dvd_dvd values(default,dvdName,default,default,default);
  mResult:=1;
  commit;
  exception
    when others then
      mResult := 0;
      rollback;
end addDVDP;

1. 存储函数,倾向于数据操作,有返回值,不可以写事务
   
   • 语法结构(无返回值):

create or replace function getUserCount return number as
begin
  declare
    user_count number;
  begin
    select count(*) into user_count from dvd_user;
    return user_count;
  end;
end getUserCount;
--调用函数
select getUserCount from dual;
--查看用户对象
select object_name, object_type, status from user_objects order by object_type;
--查看用户资源
select name,type,line,text from user_source;

• 语法结构(含参,注意参数不能添加长度):

create or replace function testArgument(i number) return varchar2 as
begin
  declare n varchar2(50);
begin
  select user_name into n from dvd_user where user_id=i;
  return n;
end;
end testArgument;
/
--测试函数
begin
 dbms_output.put_line (testArgument(3)); 
end;
/

其他

1. 游标(行转列) cursor
   
   • 在关系型数据库中,数据都是以行/记录形式存在的,因此,Oracle提供游标来循环访问每行数据;
   • 游标分为:
   • • 静态游标(常用):分为显式游标(常用)和隐式游标
   • • 动态游标
   • 声明游标

declare cursor mCursor is select dvd_id,dvd_name,dvd_date,dvd_lendcount,dvd_status from dvd_dvd order by dvd_id;
  c mCursor%rowtype;
begin
  open mCursor;
  fetch mCursor into c;
  while mCursor%found loop
    --dbms_output.put_line(c.dvd_id || '-----' || c.dvd_name|| '-----' || c.dvd_date|| '-----' || c.dvd_lendcount|| '-----' || c.dvd_status);
    dbms_output.put(c.dvd_id || '-----' || c.dvd_name|| '-----' || c.dvd_date|| '-----' || c.dvd_lendcount|| '-----' || c.dvd_status);
    dbms_output.put_line('');
    fetch mCursor into c;
  end loop;
  close mCursor;
end;