是这样的,我有很多类似的表需要合并成一个记录集显示出来,我用union all 把这些表联合起来,如下
v_sql:=select * from
(select id,'''' as title,dept,username,curtime,flagsave,''请假表'' as RepType from rep_holiday
a where exists(select father from rep_holiday_item b where username='''||UserName||''' and father=a.id and content is null) )
union all
(select id,'''' as title,dept,username,curtime,flagsave,''工作日报'' as RepType from Rep_dayly
a where exists(select father from Rep_dayly_item b where username='''||UserName||''' and father=a.id and content is null) )
....后面还有结果造成v_sql超过了4000字符,大家有什么好的办法么?
v_sql:=select * from
(select id,'''' as title,dept,username,curtime,flagsave,''请假表'' as RepType from rep_holiday
a where exists(select father from rep_holiday_item b where username='''||UserName||''' and father=a.id and content is null) )
union all
(select id,'''' as title,dept,username,curtime,flagsave,''工作日报'' as RepType from Rep_dayly
a where exists(select father from Rep_dayly_item b where username='''||UserName||''' and father=a.id and content is null) )
....后面还有结果造成v_sql超过了4000字符,大家有什么好的办法么?
比如:sql1存储4000个字符,sql2存储4000个字符
然后
execute immediate sql1||sql2
查查这个包,8i里面就有了
DBMS_SQL.EXECUTE(cursor)发一段示例:
CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS
cursor_name INTEGER;
rows_processed INTEGER;
BEGIN
cursor_name := dbms_sql.open_cursor;
DBMS_SQL.PARSE(cursor_name, 'DELETE FROM emp WHERE sal > :x',
DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(cursor_name, ':x', salary);
rows_processed := DBMS_SQL.EXECUTE(cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
我认为这种办法方便可行,容易接受。
不错