Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the method of formatting query results in SQL PLUS". The content of the article is simple and clear, and it is easy to learn and understand. Please follow Xiaobian's train of thought to study and learn "what is the method of formatting query results in SQL PLUS".

One: process code

Create Or Replace Procedure Format_Sql (i_Sql In Varchar2) Is i_Cursor Integer Default Dbms_Sql.Open_Cursor (); Desc_Cols Dbms_Sql.Desc_Tab; i_Status Integer;-- execution status n_Colcnt Number;-- number of columns i_Col_Width Integer: = 3;-- column spacing. If you think it is too small, you can increase n_Rowcnt Number: = 0. -- number of rows of record results Vc_Columnname Varchar2 (4000);-- column header name Vc_Col_Split Varchar2 (4000): ='-';-- split line between record and column head Vc_Columnvalue Varchar2 (4000);-- column value Type Typ_Collength Is Table Of Number Index By Binary_Integer; Tab_Typ_Collength Typ_Collength;-- maximum length l_Results Long of each column value -- output result Begin-- get the maximum length of each column value in the first loop i_Cursor: = Dbms_Sql.Open_Cursor;-- Open the cursor Dbms_Sql.Parse (i_Cursor, i_Sql, Dbms_Sql.Native); Dbms_Sql.Describe_Columns (i_Cursor, n_Colcnt, Desc_Cols); For i In 1. N_Colcnt Loop Dbms_Sql.Define_Column (i_Cursor, I, Vc_Columnvalue, 4000); Tab_Typ_Collength (I): = Length (Desc_Cols (I) .Col_Name); End Loop; i_Status: = Dbms_Sql.Execute (i_Cursor); Loop Exit When Dbms_Sql.Fetch_Rows (i_Cursor)

< 1; For i In 1 .. n_Colcnt Loop Dbms_Sql.Column_Value(i_Cursor, i, Vc_Columnvalue); If Length(Vc_Columnvalue) >

Tab_Typ_Collength (I) Then Tab_Typ_Collength (I): = Length (Vc_Columnvalue); End If; End Loop; End Loop; Dbms_Sql.Close_Cursor (i_Cursor);-- output of the second loop i_Cursor: = Dbms_Sql.Open_Cursor;-- Open the cursor Dbms_Sql.Parse (i_Cursor, i_Sql, Dbms_Sql.Native); Dbms_Sql.Describe_Columns (i_Cursor, n_Colcnt, Desc_Cols); For i In 1. N_Colcnt Loop Dbms_Sql.Define_Column (i_Cursor, I, Vc_Columnvalue, 4000); End Loop; i_Status: = Dbms_Sql.Execute (i_Cursor); Loop Exit When Dbms_Sql.Fetch_Rows (i_Cursor)

< 1; n_Rowcnt := n_Rowcnt + 1; l_Results := l_Results || Rpad(n_Rowcnt, i_Col_Width + 3); For i In 1 .. n_Colcnt Loop Dbms_Sql.Column_Value(i_Cursor, i, Vc_Columnvalue); l_Results := l_Results || Rpad(Vc_Columnvalue || ' ', Tab_Typ_Collength(i) + i_Col_Width); End Loop; l_Results := l_Results || Chr(10); End Loop; --定义列头 Vc_Columnname := Rpad('NO', i_Col_Width + 3, ' '); For i In 1 .. n_Colcnt Loop Vc_Columnname := Vc_Columnname || Rpad(Desc_Cols(i).Col_Name, Tab_Typ_Collength(i) + i_Col_Width, ' '); Vc_Col_Split := Rpad(Vc_Col_Split, Length(Vc_Col_Split) + i_Col_Width, ' '); Vc_Col_Split := Rpad(Vc_Col_Split, Length(Vc_Col_Split) + Tab_Typ_Collength(i), '-'); End Loop; Dbms_Sql.Close_Cursor(i_Cursor); -- Vc_Columnname := Vc_Columnname || Chr(10); --输出结果 Dbms_Output.Put_Line('Total Row: ' || n_Rowcnt); Dbms_Output.Put_Line(Vc_Columnname); Dbms_Output.Put_Line(Vc_Col_Split); Dbms_Output.Put_Line(l_Results);Exception When Others Then Dbms_Sql.Close_Cursor(i_Cursor); Raise;End; 二：测试结果 TS@PROD>

Set serveroutput on size 999999TS@PROD > select sql_text,sql_id,child_number,hash_value from v$sql Where sql_text Like'select * from emp%' SQL_TEXT- - - - - - - - -- SQL_ID CHILD_NUMBER HASH_VALUE- select * from empa2dk8bdn0ujx7 0 1745700775select * from empa2dk8bdn0ujx7 1 1745700775select * from emp e Dept d where e.deptno = d.deptno4cs33ya9vumkh 0 2478657104TS@PROD > exec Format_Sql ('select sql_text,sql_id,child_number,hash_value from v$sql Where sql_text Like''select * from emp%''') Total Row: 3NO SQL_TEXT SQL_ID CHILD_NUMBER HASH_VALUE 1 select * from emp a2dk8bdn0ujx7 0 1745700775 2 select * from emp a2dk8bdn0ujx7 1 1745700775 3 select * from emp e Dept d where e.deptno = d.deptno 4cs33ya9vumkh 0 2478657104 PL/SQL procedure successfully completed. Thank you for your reading, the above is the content of "what is the method of formatting query results in SQL PLUS". After the study of this article, I believe you have a deeper understanding of what the method of formatting query results in SQL PLUS is, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.