mysql更改表数据的格式化_mysql – 将SQL表数据格式化为文本表

如果您正在使用SQLPlus或TOAD或SQLNavigator或SQL * Developer等,请使用您正在使用的功能来很好地格式化输出.

如果您正在寻找纯数据库解决方案(没有客户端),我会使用DBMS_SQL.这是基本代码(如下).此版本只是将结果集写入DBMS_OUTPUT.您可以将其更改为您想要的任何内容.此外,您需要扩展它以处理NUMBER,DATE和VARCHAR2之外的数据类型.

DECLARE

l_rc SYS_REFCURSOR;

-- This is the procedure to format cursor output nicely

PROCEDURE dump_cursor( p_rc IN OUT SYS_REFCURSOR) IS

-- Dump the results of p_rc to log

l_cursor INTEGER;

l_column_count INTEGER;

l_column_descriptions sys.DBMS_SQL.desc_tab;

l_status INTEGER;

l_column_value VARCHAR2 (4000);

l_column_width NUMBER;

l_rec_count NUMBER := 0;

l_line VARCHAR2(4000);

FUNCTION get_length (l_column_def IN sys.DBMS_SQL.desc_rec)

RETURN NUMBER IS

l_width NUMBER;

BEGIN

l_width := l_column_def.col_max_len;

l_width :=

CASE l_column_def.col_type

WHEN 12 THEN /* DATE */

20

WHEN 2 THEN /* NUMBER */

10

ELSE l_width

END;

-- Don't display more than 256 characters of any one column

l_width := LEAST(256,GREATEST (l_width, l_column_def.col_name_len));

RETURN l_width;

END get_length;

BEGIN

EXECUTE IMMEDIATE 'alter session set nls_date_format=''DD-MON-YYYY HH24:MI:SS''';

l_cursor := sys.DBMS_SQL.TO_CURSOR_NUMBER(p_rc);

-- Describe columns

sys.DBMS_SQL.describe_columns (c => l_cursor, col_cnt => l_column_count, desc_t => l_column_descriptions);

l_line := '';

FOR i IN 1 .. l_column_count LOOP

l_column_width := get_length (l_column_descriptions (i));

l_line := l_line || RPAD (l_column_descriptions (i).col_name, l_column_width);

l_line := l_line || ' ';

DBMS_SQL.define_column (l_cursor, i, l_column_value, 4000);

END LOOP;

DBMS_OUTPUT.PUT_LINE(l_line);

l_line := '';

FOR i IN 1 .. l_column_count LOOP

l_column_width := get_length (l_column_descriptions (i));

l_line := l_line || RPAD ('-', l_column_width, '-');

l_line := l_line || ' ';

DBMS_SQL.define_column (l_cursor, i, l_column_value, 4000);

END LOOP;

DBMS_OUTPUT.PUT_LINE(l_line);

-- l_status := sys.DBMS_SQL.execute (l_cursor);

WHILE (sys.DBMS_SQL.fetch_rows (l_cursor) > 0) LOOP

l_rec_count := l_rec_count + 1;

l_line := '';

FOR i IN 1 .. l_column_count LOOP

DBMS_SQL.COLUMN_VALUE (l_cursor, i, l_column_value);

l_column_value := translate(l_column_value,chr(10), chr(200));

l_column_width := get_length (l_column_descriptions (i));

IF l_column_value IS NULL THEN

l_line := l_line || RPAD (' ', l_column_width);

ELSE

l_line := l_line || RPAD (l_column_value, l_column_width);

END IF;

l_line := l_line || ' ';

END LOOP;

DBMS_OUTPUT.PUT_LINE(l_line);

END LOOP;

IF l_rec_count = 0 THEN

DBMS_OUTPUT.PUT_LINE ('No data found.');

ELSE

DBMS_OUTPUT.PUT_LINE (l_rec_count || ' rows returned.');

END IF;

sys.DBMS_SQL.close_cursor (l_cursor);

EXECUTE IMMEDIATE 'alter session set nls_date_format=''DD-MON-YYYY''';

EXCEPTION

WHEN OTHERS THEN

EXECUTE IMMEDIATE 'alter session set nls_date_format=''DD-MON-YYYY''';

RAISE;

END dump_cursor;

-- This shows how to use it...

BEGIN

OPEN l_rc FOR SELECT object_id, object_name, object_type, last_ddl_time FROM dba_objects WHERE rownum <= 100;

dump_cursor(l_rc);

END;