Oracle 数据库学习-存储过程

Oracle 数据库学习-存储过程

 

  SQL中循环和分支的写法

create or replace procedure basePro is
  num number default 0;
begin
 
 --loop循环
  loop
    Dbms_Output.put_line('loop...end loop' || num);
    exit when(num > 2);
    num := num + 1;
  end loop;

  --while loop循环
  while (num < 6) loop
    Dbms_Output.put_line('while...end loop' || num);
    num := num + 1;
  end loop;

  --for loop循环
  for i in 1 .. 4 loop
    Dbms_Output.put_line('for...end loop' || i);
  end loop;

  num := 5;
  if num < 10 then
    Dbms_Output.put_line('if num < 10');
  elsif num < 20 then
    Dbms_Output.put_line('if num < 20');
  else
    Dbms_Output.put_line('if num < 30');
  end if;

  case
    when num < 10 then
      Dbms_Output.put_line('case num < 10');
    when num < 20 then
      Dbms_Output.put_line('case num < 10');
    else
      Dbms_Output.put_line('case num < 30');
  end case;
  
  
  
--自己抛出异常  
raise_application_error(-20000,'bug bug');

  insert into test values (1, '33');
  commit;
  
exception
 
     
  when others then
    Dbms_Output.put_line('insert erroer'||sqlcode||sqlerrm);
  
end basePro;
/

 

 

 

create or replace procedure bigNumInsert(arg1 in out default 0  number  , arg2 varchar2) is
  newid number default 0;
begin
  dbms_output.put_line(arg1);
  dbms_output.put_line(arg2);
  dbms_output.put_line(newid);

  while (arg1 < 1000) loop
    select nvl(max(id), 0) into newid from test;
    newid := newid + 1;
    insert into test values (newid, arg2 || arg1);
 
    arg1 := arg1 + 1;
  end loop;
  

update test t set t.name='4';
   dbms_output.put_line(SQL%rowcount);
  
  commit;

end;
/

 

 

 

prompt
prompt Creating procedure CURSOR1
prompt ==========================
prompt
create or replace procedure cursor1 is
  num number default 0;
  cursor c1 is
    select * from emp;

  emprow emp%rowtype;
begin

  --简单的游标

  for i in (select * from emp) loop
    dbms_output.put_line(i.EMPNO);
  end loop;
  --简单的游标2
  for i in c1 loop
    dbms_output.put_line(i.ENAME);
  end loop;

--通常的用法
  open c1;
  loop
    fetch c1
      into emprow;
    exit when c1%notfound;
    dbms_output.put_line(emprow.empno || ' ' || emprow.ENAME);
  end loop;
  close c1;
  
  
  --隐式游标
  
end cursor1;
/

 

 

 

 

create or replace procedure cursor2 is
  --num number default 0;
  --type ref_cursor is ref cursor;
  c1     p1.ref_cursor;
  emprow emp%rowtype;
  sqlString varchar2(2000);
begin

--动态sql

  sqlString := 'select * from emp t where t.empno=  7369';
  open c1 for sqlString;
  loop
    fetch c1
      into emprow;
    exit when c1%notfound;
    dbms_output.put_line(emprow.ename);
  end loop;
  close c1;

  sqlString := 'select * from emp t where t.empno=  :empno';
  open c1 for sqlString
    using 7369;
  loop
    fetch c1
      into emprow;
    exit when c1%notfound;
    dbms_output.put_line(emprow.ename);
  end loop;
  close c1;
end cursor2;
/