declare
v_count number;
v_table_name varchar2(100) := 'PROD'; --表名
v_sql clob;
v_i number := 0;
begin
select count(*) into v_count from user_tab_cols where table_name=v_table_name;
v_sql := 'select {';
for col_cur in (select COLUMN_NAME,DATA_TYPE,DATA_LENGTH from user_tab_cols where table_name=v_table_name)
loop
if(col_cur.DATA_TYPE = 'DATE') then
v_sql := v_sql || '"' ||col_cur.COLUMN_NAME || ' ":"'' || TO_CHAR('
|| col_cur.COLUMN_NAME || ',''yyyy-mm-dd HH24:mi:ss'') || ''"''';
elsif(col_cur.DATA_TYPE = 'VARCHAR2') then
v_sql := v_sql || '"' || col_cur.COLUMN_NAME || '":"''||' || col_cur.COLUMN_NAME || '|| ''"''';
elsif(col_cur.DATA_TYPE = 'NUMBER') then
--格式化数字
v_sql := v_sql || '"' || col_cur.COLUMN_NAME || '":''|| nvl(RTRIM(to_char(' || col_cur.COLUMN_NAME || ',''fm9999999990.99999''),''.''),''""'')';
end if;
v_i := v_i + 1;
if(v_i < v_count) then
v_sql := v_sql || '|| '',';
end if;
--如果拼接的sql字符串太长,50个字段拼接为1列
if(v_i mod 50 = 0) then
dbms_output.put_line(v_i);
v_sql := v_sql || ''',''';
end if;
if(v_i = v_count) then
v_sql := v_sql || ' || '' } ''';
end if;
--VARCHAR2
--NUMBER
end loop;
v_sql := v_sql || ' from ' || v_table_name;
dbms_output.put_line(v_sql);
end;
把打印出来的SQL再执行, 查询的结果就是json格式