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;