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;
---------*********