create or replace function mail(sysname in varchar2,emailAddress in varchar2,emailaddresstype in number)
return varchar2 is
PRAGMA AUTONOMOU S_TRANSATION;
csid number(19) default 7;
isvalid char(1) default '2';
flag boolean default false;
verifyInfo varchar2(100) default'';
cursor c_emailaddress is
select ea.emailaddress from t_emailaddress ea;
begin
if emailaddresstype = null then
csid :=7;
else
csid :=MOD(emailaddresstype,100);
end if;
for var_emailaddressvar in c_emailaddress loop
if var_emailaddressvar emailaddress = emailAddress then
flag :=true;
verifyInfo := 'zhongfu ';
end if;
end loop;
if flag = false then
insert into t_emailaddress(creatoe,emailaddress,csid,createtime,isvalid,id)
values(sysname,emailAddress,csid,sysdate,isvalid,t_emailaddress_seq.nextval);
commit;
end if;
return verifyInfo;
end;
这段代码我手打的,我想知道PRAGMA AUTONOMOU S_TRANSATION;和commit;能不能去掉,还有就是我去掉返回值后出错,什么原因?
return varchar2 is
PRAGMA AUTONOMOU S_TRANSATION;
csid number(19) default 7;
isvalid char(1) default '2';
flag boolean default false;
verifyInfo varchar2(100) default'';
cursor c_emailaddress is
select ea.emailaddress from t_emailaddress ea;
begin
if emailaddresstype = null then
csid :=7;
else
csid :=MOD(emailaddresstype,100);
end if;
for var_emailaddressvar in c_emailaddress loop
if var_emailaddressvar emailaddress = emailAddress then
flag :=true;
verifyInfo := 'zhongfu ';
end if;
end loop;
if flag = false then
insert into t_emailaddress(creatoe,emailaddress,csid,createtime,isvalid,id)
values(sysname,emailAddress,csid,sysdate,isvalid,t_emailaddress_seq.nextval);
commit;
end if;
return verifyInfo;
end;
这段代码我手打的,我想知道PRAGMA AUTONOMOU S_TRANSATION;和commit;能不能去掉,还有就是我去掉返回值后出错,什么原因?
PRAGMA AUTONOMOU S_TRANSATION这个不知道是什么,貌似也没有用到;
如果把commit去掉那么你insert的不知道会不会提交,应该需要,我每次都写了
由于你的function里有dml语句,需要事务的支持,所以需要PRAGMA AUTONOMOU S_TRANSATION,自治事务。commit是提交语句。2.还有就是我去掉返回值后出错,什么原因?
function是必须要有返回值的,和procedure不一样,procedure是没有返回值的,function里面一般是用来计算的,不应该出现dml语句,如果出现需要自己控制事务。dml的语句一般多出现在procedure里,procedure一般是用来组合一系列的数据库操作的。你这里如果function没有返回值,而且含有dml语句,用procedure比较好,而且出现dml的function不能用select的方式来执行,只能用赋值语句来执行,限制多多。
v_tmp varchar2(1000);
begin
v_tmp = mail(....);
.....
end;如果是sqlplus里
SQL>variable v_tmp varchar2(1000);
SQL>exec :v_tmp := main(....);
SQL>print :v_tmp;
有可能没有执行到insert into
function顾名思义是函数,类似数学里的function,执行一系列运算,返回一个结果