CREATE OR REPLACE
PROCEDURE dynamicSelect(a VARCHAR2 ,s out VARCHAR2 ) IS
v_sql VARCHAR(2000);
v_sql_a VARCHAR(2000);
V_SQL_B VARCHAR(2000);
--V_out out VARCHAR(2000);
cursor cursor_2 is select distinct code,text from APDS_RESULT_CODE;
cursor cursor_1 is SELECT distinct code,text from APDS_PROPERTY_CODE;
BEGIN
-- routine body goes here, e.g.
-- DBMS_OUTPUT.PUT_LINE('Navicat for Oracle');
v_sql := 'select sorties';
v_sql_a := ',';
v_sql_b := ',';
for itemCursor_2 in cursor_2
loop
v_sql_a := v_sql_a||''''||itemCursor_2.code ||''''||','||''''||itemCursor_2.text||''''||',';
end loop;
v_sql_a := SUBSTR(v_sql_a,1,length(v_sql_a)-1);
for itemCursor_1 in cursor_1
loop
v_sql := v_sql||',(decode('||itemCursor_1.code||v_sql_a||')) as '||itemCursor_1.code;
V_SQL_B := V_SQL_B||'wmsys.wm_concat (DECODE (property_code,'||''''||itemCursor_1.code||''''||',result_code)) as '||itemCursor_1.code||',';
end loop;
V_SQL_B := substr(V_SQL_B,1,length(V_SQL_B)-1);
v_sql := v_sql||' from (select distinct sorties,aircraft_type_code'||V_SQL_B||' from apds_report_sublist group by sorties,aircraft_type_code) where aircraft_type_code = '||''''||a||'''';
s := v_sql;
dbms_output.put_line(v_sql);
--execute immediate v_sql;
-- SELECT * FROM (v_sql);
--return 'aaa';
END;
PROCEDURE dynamicSelect(a VARCHAR2 ,s out VARCHAR2 ) IS
v_sql VARCHAR(2000);
v_sql_a VARCHAR(2000);
V_SQL_B VARCHAR(2000);
--V_out out VARCHAR(2000);
cursor cursor_2 is select distinct code,text from APDS_RESULT_CODE;
cursor cursor_1 is SELECT distinct code,text from APDS_PROPERTY_CODE;
BEGIN
-- routine body goes here, e.g.
-- DBMS_OUTPUT.PUT_LINE('Navicat for Oracle');
v_sql := 'select sorties';
v_sql_a := ',';
v_sql_b := ',';
for itemCursor_2 in cursor_2
loop
v_sql_a := v_sql_a||''''||itemCursor_2.code ||''''||','||''''||itemCursor_2.text||''''||',';
end loop;
v_sql_a := SUBSTR(v_sql_a,1,length(v_sql_a)-1);
for itemCursor_1 in cursor_1
loop
v_sql := v_sql||',(decode('||itemCursor_1.code||v_sql_a||')) as '||itemCursor_1.code;
V_SQL_B := V_SQL_B||'wmsys.wm_concat (DECODE (property_code,'||''''||itemCursor_1.code||''''||',result_code)) as '||itemCursor_1.code||',';
end loop;
V_SQL_B := substr(V_SQL_B,1,length(V_SQL_B)-1);
v_sql := v_sql||' from (select distinct sorties,aircraft_type_code'||V_SQL_B||' from apds_report_sublist group by sorties,aircraft_type_code) where aircraft_type_code = '||''''||a||'''';
s := v_sql;
dbms_output.put_line(v_sql);
--execute immediate v_sql;
-- SELECT * FROM (v_sql);
--return 'aaa';
END;