存储过程
create or replace procedure YHS is
begin
dbms_output.put_line('YHS');
end YHS;
使用or replace,假如已经有一个叫YHS的procedure，则会修改它
用一个匿名块来调用YHS
begin
YHS;
end;
使用参数
IN参数，传入用，被procedure读取，没大小与精度信息
create or replace procedure YHS(
YHS_ACTION_ID in varchar2,
YHS_ACTION_NAME in varchar2,
YHS_ACTION_DESC in varchar2) is
begin
insert into td_b_action(action_id,action_name,action_desc)
values (YHS_ACTION_ID,YHS_ACTION_NAME,YHS_ACTION_DESC);
end YHS;
可以在存储过程中上加事务处理
create or replace procedure YHS(
YHS_ACTION_ID in varchar2,
YHS_ACTION_NAME in varchar2,
YHS_ACTION_DESC in varchar2) is
begin
insert into td_b_action(action_id,action_name,action_desc)
values (YHS_ACTION_ID,YHS_ACTION_NAME,YHS_ACTION_DESC);
commit;
end YHS;
传递参数
1 名称法
begin
YHS(YHS_ACTION_ID=>'110',YHS_ACTION_NAME=>'YHS test',YHS_ACTION_DESC=>'YHS 0910');
end;
前面定义时对应关系已经确定，这里的顺序可以打乱，如：
begin
YHS(YHS_ACTION_DESC=>'YHS 09101',YHS_ACTION_ID=>'111',YHS_ACTION_NAME=>'YHS test1');
end;
select * from td_b_action a;
2 位置表示法，当参数过多，写参数麻烦时用，但是必须顺序相同
如果在调用时没传入参数，可以使用默认值
create or replace procedure YHS(
YHS_ACTION_ID in varchar2,
YHS_ACTION_NAME in varchar2 default '默认值',
YHS_ACTION_DESC in varchar2 default '默认描述') is
begin
insert into td_b_action(action_id,action_name,action_desc)
values (YHS_ACTION_ID,YHS_ACTION_NAME,YHS_ACTION_DESC);
end YHS;
begin
YHS('110110');
end;
OUT参数，由procedure传入值，用户接收参数
create or replace procedure YHS0912P1(
Y_ACTION_ID in varchar2,
Y_ACTION_NAME out td_b_action.action_name%type,
Y_ACTION_DESC out td_b_action.action_desc%type) is
begin
select action_name,action_desc
into Y_ACTION_NAME,Y_ACTION_DESC
from td_b_action
where action_id=Y_ACTION_ID;
exception
when no_data_found then
dbms_output.put_line('NULL');
end YHS0912P1;
declare
Yname td_b_action.action_name%type;
Ydesc td_b_action.action_desc%type;
begin
YHS0912P1(100000022,Yname,Ydesc);
dbms_output.put_line(Yname);
dbms_output.put_line(Ydesc);
end;
也可以在“命令窗口”
variable name varchar2(10);
variable desc varchar2(50);
exec YHS0912P1(100000022,:name,:desc);
/
评论