803
oracle中函数和存储过程简单示例-动态sql
oracle中函数和存储过程
1. 函数
create or replace function getArtCount(keyword in varchar2) return number
as
Result number;
Sqlt varchar2(4000);
begin
-- select t.sys_documentid into Result from dom_2_doclib t where t.sys_topic like '%key%';
-- select count(t.sys_documentid) into Result from dom_2_doclib t where t.art_auctioncode like '%art%';
-- select count(t.sys_documentid) into Result from dom_2_doclib t where t.sys_topic like '%1%';
-- select count(t.sys_documentid) into Result from dom_2_doclib t where t.sys_topic like '%高%';
Sqlt:='select count(t.sys_documentid) from dom_2_doclib t where t.art_auctioncode='''||keyword||''' or t.sys_topic like ''%'||keyword||'%''';
Sqlt:=Sqlt||' or t.art_authors like ''%'||keyword||'%''';
dbms_output.put_line(Sqlt);
EXECUTE IMMEDIATE Sqlt INTO Result;
-- Execute IMMEDIATE Sqlt into Result;
dbms_output.put_line(Result);
return Result;
end getArtCount;
调用方法:
select getArtcount('齐白石') from dual;
2. 储存过程
create or replace procedure getArtsCount(artcount out number,keyword in varchar2) as
Sqlt varchar2(4000);
begin
-- select count(*) into artcount from dom_2_doclib t where t.sys_topic like '%key%';
Sqlt:='select count(t.sys_documentid) from dom_2_doclib t where t.art_auctioncode='''||keyword||''' or t.sys_topic like ''%'||keyword||'%''';
Sqlt:=Sqlt||' or t.art_authors like ''%'||keyword||'%''';
EXECUTE IMMEDIATE Sqlt INTO artcount;
dbms_output.put_line(Sqlt);
end;
调用方法 ：
declare
a number;
begin
getArtsCount(a,'a');
dbms_output.put_line('aaaa=='||a);
end;
最后更新：2017-04-02 06:51:19