开博了。其实之前有的,只是不在pub上,最近实在忍受不了msn共享空间了。
取某个表所有的字段
上一篇 /
下一篇 2007-12-12 11:59:46
/ 个人分类:读书笔记
用户生产环境有些表字段很多,为了方便查询的时候取字段,自己写了一个函数,取某个用户某个表中所有的字段,用逗号隔开。函数如下:
CREATE OR REPLACE Function Create_Cols(Ow In String, Tab_Name In String)
Return Varchar2 Is
Cols Varchar2(4000);
Num Number(5);
Cursor All_Cols Is
Select Column_Name || ','
From All_Tab_Cols
Where wner = Ow
And Table_Name = Tab_Name
Order By Internal_Column_Id;
Type Studydate Is Table Of Varchar2(20) Index By Binary_Integer;
v_Studydate Studydate;
Begin
Select Count(Column_Name)
Into Num
From All_Tab_Cols
Where wner = Ow
And Table_Name = Tab_Name;
Open All_Cols;
For i In 1 .. Num Loop
Fetch All_Cols
Into v_Studydate(i);
End Loop;
For i In 1 .. Num Loop
Select Cols || v_Studydate(i) Into Cols From Dual;
End Loop;
Select Substr(Cols, 1, Length(Cols) - 1) Into Cols From Dual;
Return Cols;
End Create_Cols;
/
导入论坛
引用链接
收藏
分享给好友
推荐到圈子
管理
举报
TAG: