oracle 存储过程学习

第一个存储过程:

create or replace procedure p_Test is

begin

  dbms_output.put_line('第一个procedure');

end p_Test;

 

2/ 输入值,输出值,number类型可以给varchar类型赋值;

create or replace procedure p_Test(a in varchar2,b out varchar2) is

  num number := 99;

begin

  b := num;

  dbms_output.put_line(b);

end p_Test;

 

3/ if else 判断

create or replace procedure p_Test(a in varchar2,b out varchar2) is

  num number := 70;

begin

  if num > 99 then

    b := 'A';

  end if;

  if num <60 then

    b := 'C';

  else

    b := 'B';

  end if;

  dbms_output.put_line(b);

end p_Test; 

 

4/ for 循环

create or replace procedure p_Test(a in varchar2,b out varchar2) is

  num number := 0;

begin

  for countNum in 0..10 loop

    num := num+countNum;

  end loop;

  dbms_output.put_line(num);

end p_Test;

 

5/ 查询数据库表 返回值:

create or replace procedure p_Test(a in varchar2 ,b out varchar2) is

  code varchar(100);

  num number;

begin

  select count(1) into num from sys_res_tree t where t.res_tree_id=a;

  select t.tree_name into code from sys_res_tree t where t.res_tree_id=a;

  dbms_output.put_line(code);

  exception 

   WHEN NO_DATA_FOUND THEN 

   dbms_output.put_line('error');

end p_Test;

6/游标 遍历游标 

create or replace procedure p_Test(a in varchar2) is

  code varchar2(200);

  cursor cursor1 is

  select t.* from sys_res_tree t where t.p_res_tree_id=a;

begin

  for cursorresult in cursor1 loop

      code := code||cursorresult.tree_name;--点游标里的对象

  end loop;

  dbms_output.put_line(code);

   RETURN;--退出存储过程

end p_Test;

 

7/sys_refcursor游标使用方法

create or replace procedure p_Test is

  

 cu sys_refcursor;

 res_tree_id varchar2(30);

 tree_name varchar2(30);

 p_res_tree_id varchar2(30);

begin

    open cu for select t.res_tree_id,t.tree_name,t.p_res_tree_id from sys_res_tree t;

    loop

    fetch cu into res_tree_id,tree_name,p_res_tree_id;

    dbms_output.put_line(res_tree_id);

    dbms_output.put_line(tree_name);

    exit when cu%Notfound;

    end loop;

 

end p_Test;