需要写函数么with tmp_test as
(select 1 as a,'xxx' as b,'ss' as c from dual)select t2.a,t1.b,t1.c from tmp_test t1,(select &id as a from dual) t2
where t2.a = t1.a(+)
(select 1 as a,'xxx' as b,'ss' as c from dual)select t2.a,t1.b,t1.c from tmp_test t1,(select &id as a from dual) t2
where t2.a = t1.a(+)
需要写函数,我这个函数有其它用处
你希望的返回值是什么?一个cursor,还是字段的拼接组合,还是统计值?
CREATE OR REPLACE FUNCTION TEST1(V_ID VARCHAR2) RETURN SYS_REFCURSOR IS
RESULT SYS_REFCURSOR;
BEGIN
OPEN RESULT FOR
SELECT T1.ID, T2.NAME, T2.VALUE
FROM (SELECT V_ID ID FROM DUAL) T1,
(SELECT ID, NAME, VALUE FROM TEST) T2
WHERE T1.ID = T2.ID(+);
RETURN(RESULT);
END TEST1;
额,可能我没有表达清楚,我最后要的是一条表集合记录。
当传入V_ID进来的时候,我要去判断这个V_ID是否在A表中,如果在就返回A表中的记录,如果不在就要返回一个只有id有值,其它字段的值为空的表值记录
select count(*) into v_count from A t where t.ID=V_ID;
if (v_count = 0) then
SELECT id,'' as name, '' as value FROM dual;
else
SELECT t.ID,t.Name,t.Value FROM A t WHERE t.ID=V_ID;
额,可能我没有表达清楚,我最后要的是一条表集合记录。
当传入V_ID进来的时候,我要去判断这个V_ID是否在A表中,如果在就返回A表中的记录,如果不在就要返回一个只有id有值,其它字段的值为空的表值记录
select count(*) into v_count from A t where t.ID=V_ID;
if (v_count = 0) then
SELECT id,'' as name, '' as value FROM dual;
else
SELECT t.ID,t.Name,t.Value FROM A t WHERE t.ID=V_ID;
没错啊,就是这样的啊你的意思是你接收不了cursor?需要rowtype?
额,可能我没有表达清楚,我最后要的是一条表集合记录。
当传入V_ID进来的时候,我要去判断这个V_ID是否在A表中,如果在就返回A表中的记录,如果不在就要返回一个只有id有值,其它字段的值为空的表值记录
select count(*) into v_count from A t where t.ID=V_ID;
if (v_count = 0) then
SELECT id,'' as name, '' as value FROM dual;
else
SELECT t.ID,t.Name,t.Value FROM A t WHERE t.ID=V_ID;
没错啊,就是这样的啊你的意思是你接收不了cursor?需要rowtype?
如果是rowtype要怎么写?