oracle procedure 存储过程

oracle procedure 存储过程入门 小例子 ， 高手路过

 

 

-- procedure instruction

-----------**simplest procedure-------
create or replace procedure out_time is
begin
  dbms_output.put_line(systimestamp);
end;

begin
out_time;
end;
--------*********



---******example for procedure in------
create or replace procedure add_project(proid      varchar2,
                                        proversion number,
                                        proname    varchar2,
                                        customID varchar2 default null) is
  --e_intergrity exception;
  --pragma exception_init(e_intergrity, -2291);
begin
  insert into T_LPROMIS_XMGL_GBBXMXX pro
    (xmid, xmbbh, xmmc,pro.khid)
  values
    (proid, proversion, proname,customID);
--exception
--  when dup_val_on_index then
--    raise_application_error(-2000, '项目号不可重复');
--  when e_intergrity then
--    raise_application_error(-20001, '编号有误');
end;
-- execute
begin
  add_project(proid=>'testproject',proname=> '测试项目0号', proversion=>0);
end;
--test
select * from T_LPROMIS_XMGL_GBBXMXX pro
 where pro.xmid = 'testproject' 
---************





-- *******example of query data using out 

create or replace procedure query_project
(proname in varchar2, proid out varchar2)
is 
begin
  select pro.xmid into proid from T_LPROMIS_XMGL_GBBXMXX pro
where pro.xmmc = proname;
end;

--execute
declare
  v1 varchar2(80) := '全网项目';
  v2 varchar2(80) := 'qq';
begin
  query_project('全网项目', v2);
  dbms_output.put_line(v2);
end;

-- *****************


-- *****advanced application todo for test in out
create or replace procedure computenum(num1 in out number,
                                       num2 in out number) is
  v1 number;
  v2 number;
begin
  v1   := num1 / num2;
  v2   := mod(num1, num2);
  num1 := v1;
  num2 :v2;
end;

declare num1 float; num2 float;
begin
num1 := -11; num2 := 4; computenum(num1, num2); dbms_output.put_line(num1 || num2);
end;
---------*********