学步园

create or replace function getEmpCount return number as
       begin
          declare mycount number;
          begin
            select count(*) into mycount from emp;
            return mycount;
            end;
       end;
    end getEmpCount;
    
    --测试
    begin
    dbms_output.put_line('Count:'||getEmpCount());
    end;
    
    
    
    ---带参数的函数
    
   create or replace function getTableCount(tablename varchar2) return number as
     begin
              declare recored_count number;
                      query_sql varchar(200);
              begin
                      query_sql:='select count(*) from '||tablename;
                      execute immediate query_sql into recored_count;
                      return record_count;

              end;

    end getTableCount;
    --测试
    declare tablename varchar2(50);
    begin
        tablename:='emp';
        dbms_output.put_line('Count:'||getTableCount(tablename));
    end;
    
    --取某一列的数据并用逗号连接起来
    
  create or replace function cols2rows(sqlString varchar2) return varchar2 as
    begin
      declare type cu_type is ref cursor;
      tmp_cur cu_type;
      tmp_row varchar(30);
      v_result varchar(500);
      begin
              open tmp_cur for sqlString;
              fetch tmp_cur into tmp_row;
              while tmp_cur%found loop
                   v_result:=v_result||tmp_row||',';
                   fetch tmp_cur into tmp_row;
               end loop;
               close tmp_cur;
               return rtrim(v_result,',');
      end;
    end cols2rows;
    
    
    select cols2rows('select ename from emp where deptno=30') from dual
    

附上一点网上找到的

execute immediate 的用法

declare
   l_depnam varchar2(20) := 'testing';
   l_loc     varchar2(10) := 'Dubai';
   begin
   execute immediate 'insert into dept values   (:1, :2, :3)'
     using 50, l_depnam, l_loc;
   commit;
end;

declare
   type empdtlrec is record (empno   number(4),
                            ename   varchar2(20),
                            deptno   number(2));
   empdtl empdtlrec;
begin
   execute immediate 'select empno, ename, deptno ' ||
                    'from emp where empno = 7934'
     into empdtl;
end;

declare
   l_cnt     varchar2(20);
begin
   execute immediate 'select count(1) from emp'
     into l_cnt;
   dbms_output.put_line(l_cnt);
end;