CREATE OR REPLACE PROCEDURE get_value(field_name IN VARCHAR2,data_value IN VARCHAR2)
AS
table_query VARCHAR2(1000);
BEGIN
table_query :='SELECT s.CNNAME FROM S_DIC s WHERE s.TYPE1 = (SELECT t.REFERENCE FROM S_ELEMENT t WHERE t.ENNAME = '\\field_name\\' AND t.dictionary IS NOT NULL)';
EXECUTE IMMEDIATE table_query RETURNING INTO table_data;
DBMS_OUTPUT.put_line(table_data.CNNAME);
END;
select get_value('SFCBG','02') from dual;
AS
table_query VARCHAR2(1000);
BEGIN
table_query :='SELECT s.CNNAME FROM S_DIC s WHERE s.TYPE1 = (SELECT t.REFERENCE FROM S_ELEMENT t WHERE t.ENNAME = '\\field_name\\' AND t.dictionary IS NOT NULL)';
EXECUTE IMMEDIATE table_query RETURNING INTO table_data;
DBMS_OUTPUT.put_line(table_data.CNNAME);
END;
select get_value('SFCBG','02') from dual;
--应当这么调用。
begin
get_value('SFCBG','02');
end;
EXECUTE IMMEDIATE table_query RETURNING INTO table_data;
这里的table_data没有定义。如果只有一个返回值,定义成varchar2即可,
DBMS_OUTPUT.put_line(table_data.CNNAME); 这里改成DBMS_OUTPUT.put_line(table_data)如果有多个,要定义成
type stringtable is table of varchar2(4000);
table_date stringtable;
然后
EXECUTE IMMEDIATE table_query RETURNING INTO table_data;
那里改成EXECUTE IMMEDIATE table_query RETURNING bulk collect INTO table_data;
输出那里也要改成循环。