09月22日, 2014 140次
这篇作品重要解说了 在SQL PLUS中方法化输入查问截止的本领是什么 ,文中的解说实质大略明显，容易进修与领会，底下请大师随着小编的思绪渐渐深刻，一道来接洽和进修 在SQL PLUS中方法化输入查问截止的本领是什么 吧！
一：进程代码
creatoreplayprocedurat _ SqL(I _ SqLinvarchar 2)为
i _ CursorIntegerDefaultDbms _ Sql .open _ Cursor()；
Desc .desc _ Tab；
i _ StatusInteger-执奇迹态
n _ ColcntNumber-列数
I _ Col _ width平头：=3；-列间距，即使感触太小不妨调大少许
n _ row cntnumber :=0；-记载截止行数
VC _ columnnamevarchar 2(4000)；-列头名字
VC _ Col _ splitvarchar 2(4000):= - ；-记载和列头之间的分隔线
VC _ column valuevarchar 2(4000)；-列值
typ _ collenglistablefnumberindexbybinary _ Integer；
tab _ Typ _ Collength Typ _ Collength；-寄存每一列值的最大长度
l _ ResultsLong-输入截止
-第一次轮回获得每列值的最大长度
i_Cursor:=Dbms_Sql .翻开光标-翻开游标；
Dbms_Sql .领会(i_Cursor，i_Sql，Dbms_Sql .原生);
Dbms_Sql .刻画列（光标，列，Desc列);
ForiIn1.n_ColcntLoop
Dbms_Sql .Define_Column(i_Cursor，I，Vc_Columnvalue，4000)；
tab _ Typ _ Collength(I):=长度(desc)科尔斯.col _ Name)；
EndLoop
i_Status:=Dbms_Sql .实行(I _ Cursor)；
ExitWhenDbms _ Sql .fetch _ Rows(I _ Cursor)1；
ForiIn1.n_ColcntLoop
Dbms_Sql .列值(i_Cursor，I，Vc _列值)；
即使长度(Vc_Columnvalue)选项卡_典型_搜集长度则
Tab_Typ_Collength(i) := Length(Vc_Columnvalue);
End If;
End Loop;
End Loop;
Dbms_Sql.Close_Cursor(i_Cursor);
第二次轮回拼接输入截止
i_Cursor := Dbms_Sql.Open_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 999999
TS@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 emp
a2dk8bdn0ujx7 0 1745700775
select * from emp
a2dk8bdn0ujx7 1 1745700775
select * from emp e,dept d where e.deptno = d.deptno
4cs33ya9vumkh 0 2478657104
TS@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: 3
NO 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.
感动诸位的观赏，之上即是“在SQL PLUS中方法化输入查问截止的本领是什么”的实质了，过程正文的进修后，断定大师对在SQL PLUS中方法化输入查问截止的本领是什么这一题目有了更深沉的领会，简直运用情景还须要大师试验考证。这边是，小编将为大师推送更多关系常识点的作品，欢送关心！