動態sql 報表

PROCEDURE run_report(errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_report_id NUMBER,
p_para_1 VARCHAR2,
p_para_2 VARCHAR2,
p_para_3 VARCHAR2,
p_para_4 VARCHAR2,
p_para_5 VARCHAR2,
p_para_6 VARCHAR2,
p_para_7 VARCHAR2,
p_para_8 VARCHAR2,
p_para_9 VARCHAR2,
p_para_10 VARCHAR2) IS
v_date DATE;
v_number NUMBER;
v_string VARCHAR2(2000);
---
v_cursor NUMBER;
v_state NUMBER;
v_col_cnt NUMBER;
v_desc_tab dbms_sql.desc_tab;
---
v_col_id NUMBER;
v_label_tab label_tab;
v_label VARCHAR2(240);
CURSOR cur_cols IS
SELECT t.column_name,
t.column_num
FROM cux_report_columns_tbl t
WHERE SYSDATE BETWEEN nvl(start_date,
SYSDATE) AND
nvl(end_date,
SYSDATE)
AND report_id = p_report_id
ORDER BY t.column_num;
---
v_cstring VARCHAR2(240);
v_para_id NUMBER := 0;
CURSOR cur_paras IS
SELECT t.parameter_name,
t.data_type,
t.parameter_num
FROM cux_report_parameters_tbl t
WHERE SYSDATE BETWEEN nvl(start_date,
SYSDATE) AND
nvl(end_date,
SYSDATE)
AND report_id = p_report_id
ORDER BY t.parameter_num;
---
e_end EXCEPTION;
BEGIN
BEGIN
SELECT report_name,
report_sql
INTO g_report_name,
g_report_sql
FROM cux_report_reports_tbl
WHERE report_id = p_report_id;
EXCEPTION
WHEN OTHERS THEN
errbuf := 'The program can''t get the report information correctly; SQLERRM: ' ||
SQLERRM;
print_log(errbuf);
retcode := 2;
RAISE e_end;
END;
IF authority(g_user_id,
p_report_id) = 'N' THEN
errbuf := 'You do not have the authorization to run report "' ||
g_report_name || '".';
print_log(errbuf);
retcode := 2;
RAISE e_end;
END IF;
insert_record;
---
v_cursor := dbms_sql.open_cursor;
dbms_sql.parse(v_cursor,
g_report_sql,
dbms_sql.native);
dbms_sql.describe_columns(v_cursor,
v_col_cnt,
v_desc_tab);
print_log('=======================================================');
FOR i IN 1 .. v_col_cnt LOOP
print_log('col_type:' || v_desc_tab(i).col_type);
print_log('col_max_len:' || v_desc_tab(i).col_max_len);
print_log('col_name:' || v_desc_tab(i).col_name);
print_log('col_name_len:' || v_desc_tab(i).col_name_len);
print_log('col_schema_name:' || v_desc_tab(i).col_schema_name);
print_log('col_schema_name_len:' || v_desc_tab(i).col_schema_name_len);
print_log('col_precision:' || v_desc_tab(i).col_precision);
print_log('col_scale:' || v_desc_tab(i).col_scale);
print_log('col_charsetid:' || v_desc_tab(i).col_charsetid);
print_log('col_charsetform:' || v_desc_tab(i).col_charsetform);
print_log('=======================================================');
IF v_desc_tab(i).col_type = 2 THEN
dbms_sql.define_column(v_cursor,
i,
v_number);
ELSIF v_desc_tab(i).col_type = 12 THEN
dbms_sql.define_column(v_cursor,
i,
v_date);
ELSE
dbms_sql.define_column(v_cursor,
i,
v_string,
v_desc_tab(i).col_max_len);
END IF;
END LOOP;
FOR fet_para IN cur_paras LOOP
v_para_id := v_para_id + 1;
v_cstring := NULL;
IF v_para_id = 1 THEN
v_cstring := p_para_1;
ELSIF v_para_id = 2 THEN
v_cstring := p_para_2;
ELSIF v_para_id = 3 THEN
v_cstring := p_para_3;
ELSIF v_para_id = 4 THEN
v_cstring := p_para_4;
ELSIF v_para_id = 5 THEN
v_cstring := p_para_5;
ELSIF v_para_id = 6 THEN
v_cstring := p_para_6;
ELSIF v_para_id = 7 THEN
v_cstring := p_para_7;
ELSIF v_para_id = 8 THEN
v_cstring := p_para_8;
ELSIF v_para_id = 9 THEN
v_cstring := p_para_9;
ELSIF v_para_id = 10 THEN
v_cstring := p_para_10;
END IF;
IF upper(fet_para.data_type) = 'DATE' THEN
dbms_sql.bind_variable(v_cursor,
fet_para.parameter_name,
to_date(v_cstring,
'yyyy/mm/dd hh24:mi:ss'));
ELSIF upper(fet_para.data_type) = 'NUMBER' THEN
dbms_sql.bind_variable(v_cursor,
fet_para.parameter_name,
to_number(v_cstring));
ELSE
dbms_sql.bind_variable(v_cursor,
fet_para.parameter_name,
v_cstring);
END IF;
END LOOP;
v_state := dbms_sql.execute(v_cursor);
---
print_output('');
print_output('');
print_output(' print_output(' xmlns:o="urn:schemas-microsoft-com:office:office"');
print_output(' xmlns:x="urn:schemas-microsoft-com:office:excel"');
print_output(' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"');
print_output(' xmlns:html=");
print_output(' ');
print_output(' ');
print_output(' ');
print_output(' ');
print_output(' ');
print_output(' ');
print_output(' ');
print_output('

');
FOR i IN 1 .. v_col_cnt LOOP
IF v_desc_tab(i).col_type = 2 THEN
print_output('');
ELSIF v_desc_tab(i).col_type = 12 THEN
print_output('');
ELSE
IF v_desc_tab(i).col_max_len < 60 THEN
print_output('');
ELSIF v_desc_tab(i).col_max_len > 300 THEN
print_output('');
ELSE
print_output('');
END IF;
END IF;
END LOOP;
print_output(' ');
print_output(' ' ||
conv_string(g_report_name) || '');
print_output(' ');
---
v_col_id := 0;
FOR fet_cols IN cur_cols LOOP
v_col_id := v_col_id + 1;
v_label_tab(v_col_id).label_name := fet_cols.column_name;
END LOOP;
print_output(' ');
FOR i IN 1 .. v_col_cnt LOOP
BEGIN
v_label := v_label_tab(i).label_name;
EXCEPTION
WHEN no_data_found THEN
v_label := v_desc_tab(i).col_name;
END;
print_output(' ' ||
conv_string(v_label) || '');
END LOOP;
print_output(' ');
---
LOOP
EXIT WHEN dbms_sql.fetch_rows(v_cursor) <= 0;
print_output(' ');
FOR i IN 1 .. v_col_cnt LOOP
IF v_desc_tab(i).col_type = 2 THEN
dbms_sql.column_value(v_cursor,
i,
v_number);
print_output(' ' || v_number ||
'');
ELSIF v_desc_tab(i).col_type = 12 THEN
dbms_sql.column_value(v_cursor,
i,
v_date);
print_output(' ' ||
REPLACE(to_char(v_date,
'yyyy-mm-dd hh24:mi:ss'),
' ',
'T') || '');
ELSE
dbms_sql.column_value(v_cursor,
i,
v_string);
print_output(' ' ||
conv_string(v_string) || '');
END IF;
END LOOP;
print_output(' ');
END LOOP;
---
print_output(' ');
print_output(' ');
print_output(' ');
print_output(' ');
print_output(' 9');
print_output(' 600');
print_output(' 600');
print_output(' ');
print_output(' ');
print_output(' ');
print_output(' ');
print_output(' 3');
print_output(' 3');
print_output(' 2');
print_output(' False');
print_output(' False');
print_output(' ');
print_output(' ');
print_output('');
EXCEPTION
WHEN e_end THEN
NULL;
WHEN OTHERS THEN
errbuf := 'The report "' || g_report_name ||
'" faces an error; SQLERRM: ' || SQLERRM;
print_log(errbuf);
retcode := 2;
END;

FUNCTION conv_string(p_string VARCHAR2) RETURN VARCHAR2 IS
v_string VARCHAR2(4000);
BEGIN
print_log(p_string);
v_string := REPLACE(p_string,
'&',
'&' || 'amp;');
v_string := REPLACE(v_string,
' '&' || 'lt;');
v_string := REPLACE(v_string,
'>',
'&' || 'gt;');
v_string := REPLACE(v_string,
'''',
'&' || 'apos;');
v_string := REPLACE(v_string,
'"',
'&' || 'quot;');
print_log(v_string);
RETURN v_string;
END;

[@more@]