Shulou(Shulou.com)06/01 Report--

Define

A stored procedure (Stored Procedure) is a set of SQL statements used to perform specific database functions. The set of SQL statements passes through the

After compilation, it is stored in the database system. When in use, the user specifies the name of the defined stored procedure and gives the corresponding stored procedure parameters

To invoke and execute it to complete one or a series of database operations.

It has always been very useful to use the\ G parameter in MySQL to change the way the output result set is displayed, especially in the command line interface. However, the ORACLE database does not have this feature. Today, when I searched a blog post by Master Tom, I found that the Master implemented a similar function with a stored procedure print_table. It's just that we ordinary people don't know it, and we specially sort it out here to facilitate ourselves or those in need to check it later.

CREATE OR REPLACE PROCEDURE print_table (p_query IN VARCHAR2) AUTHID CURRENT_USER IS l_thecursor INTEGER DEFAULT dbms_sql.open_cursor; l_columnvalue VARCHAR2 (4000); l_status INTEGER; l_desctbl dbms_sql.desc_tab; l_colcnt NUMBER; BEGIN EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-mon-yyyy hh34:mi:ss'''; dbms_sql.parse (l_thecursor, p_query, dbms_sql.native) Dbms_sql.describe_columns (l_thecursor, l_colcnt, l_desctbl); FOR i IN 1. L_colcnt LOOP dbms_sql.define_column (l_thecursor, I, l_columnvalue, 4000); END LOOP; l_status: = dbms_sql.EXECUTE (l_thecursor); WHILE (dbms_sql.Fetch_rows (l_thecursor) > 0) LOOP FOR i IN 1. L_colcnt LOOP dbms_sql.column_value (l_thecursor, I, l_columnvalue); dbms_output.Put_line (RPAD (L_desctbl (I). Col_name, 30) | |':'| l_columnvalue); END LOOP; dbms_output.put_line ('-'); END LOOP EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-rr'''; EXCEPTION WHEN OTHERS THEN EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-rr'''; RAISE; END; /

As shown in the following test:

SQL > set serveroutput on size 99999and execute print_table ('select * from v$session where sid=997') SADDR: 000000085FA35CA0SID: 997SERIAL#: 1AUDSID: 0PADDR: 000000085F6B7E70USER#: 0USERNAME: COMMAND: 2147483644TADDR: LOCKWAIT: STATUS: ACTIVESERVER: DEDICATEDSCHEMA#: 0SCHEMANAME: SYSOSUSER: oraclePROCESS: 5036MACHINE: xxxxPORT: 0TERMINAL: UNKNOWNPROGRAM: oracle@xxxxx (DBW0) TYPE: BACKGROUNDSQL _ ADDRESS: 00SQL_HASH_VALUE: 0SQL_ID: SQL_CHILD_NUMBER: 0PREV_SQL_ADDR: 00PREV_HASH_VALUE: 0PREV_SQL_ID: PREV_CHILD_NUMBER: 0PLSQL_ENTRY_OBJECT_ID: PLSQL_ENTRY_SUBPROGRAM_ID: PLSQL_OBJECT_ID: PLSQL_SUBPROGRAM_ID: MODULE: MODULE_HASH: 0ACTION: ACTION_HASH: 0CLIENT_INFO : FIXED_TABLE_SEQUENCE: 0ROW_WAIT_OBJ#:-1ROW_WAIT_FILE#: 0ROW_WAIT_BLOCK#: 0ROW_WAIT_ROW#: 0LOGON_TIME: 04-jul-2018 21:15:52LAST_CALL_ET: 5272838PDML_ENABLED: NOFAILOVER_TYPE: NONEFAILOVER_METHOD: NONEFAILED_OVER: NORESOURCE_CONSUMER_GROUP: PDML_STATUS: DISABLEDPDDL_STATUS: DISABLEDPQ_STATUS : DISABLEDCURRENT_QUEUE_DURATION: 0CLIENT_IDENTIFIER: BLOCKING_SESSION_STATUS: NO HOLDERBLOCKING_INSTANCE: BLOCKING_SESSION: SEQ#: 34697EVENT#: 3EVENT: rdbms ipc messageP1TEXT: timeoutP1: 300P1RAW: 000000000000012CP2TEXT: P2: 0P2RAW: 00P3TEXT: P3: 0P3RAW: 00WAIT_CLASS_ID: 2723168908WAIT_CLASS#: 6WAIT_CLASS : IdleWAIT_TIME: 0SECONDS_IN_WAIT: 107STATE: WAITINGSERVICE_NAME: SYS$BACKGROUNDSQL_TRACE: DISABLEDSQL_TRACE_WAITS: FALSESQL_TRACE_BINDS: FALSEECID:-PL/SQL procedure successfully completed.SQL >

Reference materials:

Https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1035431863958,%7Bprint_table%7D

Summary

The above is the introduction of print_table stored procedures in oracle introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave a message for me, and the editor will reply you in time. Thank you very much for your support to the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.