PROCEDURE FamilyDiscusslist(
t_Filter IN varchar)
IS
Begin
select count(*) into out_RecordSum from blog_familydiscuss where t_Filter;
End FamilyDiscusslist;
在如上所示的存储过程中有个字符型的输入参数,该变量的值为查询语句的过滤条件比如
title like 'a%' and createdate>sysdate 之类的
如果是使用sqlserver 应该是这样实现exec('select count(*) into out_RecordSum from blog_familydiscuss'+t_Filter);
请问如果在oracle中实现类似的功能应该怎么办!!
t_Filter IN varchar)
IS
Begin
select count(*) into out_RecordSum from blog_familydiscuss where t_Filter;
End FamilyDiscusslist;
在如上所示的存储过程中有个字符型的输入参数,该变量的值为查询语句的过滤条件比如
title like 'a%' and createdate>sysdate 之类的
如果是使用sqlserver 应该是这样实现exec('select count(*) into out_RecordSum from blog_familydiscuss'+t_Filter);
请问如果在oracle中实现类似的功能应该怎么办!!
t_Filter IN varchar
out_RecordSum out number)
IS
strsql varchar2(400);
Begin
strsql = 'select count(*) from blog_familydiscuss where ' || t_Filter; execute immediate strsql into out_RecordSum;
End FamilyDiscusslist;
strsql = 'select count(*) from blog_familydiscuss where ' || t_Filter;
open cur for strsql;
如果返回记录集的话用BULK COLLECT试试
CREATE TYPE COUNT_TAB AS TABLE OF NUMBER(4);CREATE OR REPLACE FUNCTION FamilyDiscusslist(
t_Filter IN varchar
) RETURN COUNT_TAB
IS
out_RecordSum COUNT_TAB;
strsql varchar2(400);
BEGIN
strsql = 'select count(*) from blog_familydiscuss where ' || t_Filter; execute immediate strsql BULK COLLECT into COUNTS ;
RETURN out_RecordSum ;END;
l_VAR := 1;
OPEN REC FOR l_SQL USING l_VAR;先定义l_SQL VARCHAR2(2000), l_VAR INT, REC SYS_REFCURSOR
其中REC应该是一个输出参数