ITPub博客

取某个表所有的字段

原创 Linux操作系统 作者:foxmile 时间:2007-12-12 11:59:46 0 删除 编辑
用户生产环境有些表字段很多,为了方便查询的时候取字段,自己写了一个函数,取某个用户某个表中所有的字段,用逗号隔开。函数如下:
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;
/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/79499/viewspace-436/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-10

  • 博文量
    107
  • 访问量
    196440