每次写存储过程或包的时候，都要定义参数或内部变量，写法一般都是：

p_字段名  表名.字段名%type,

参数少还可以，多了的话，手写起来有点麻烦，我想能否写一个sql或者函数，把某个表的所有字段的参数定义都写出来呢？到时候写过程的时候，在这里面直接拷贝部分字段的定义，岂不是又方便，又不容易错；

我尝试了一把，比如：


  SELECT REPLACE(STR,'|',CHR(13)||CHR(10)) FROM
  (
  (SELECT substr(MAX(SYS_CONNECT_BY_PATH(column_name,
                                                                  '|' )),
                                           2)  AS STR
                               FROM (SELECT ROWNUM AS ROW_NUM, column_name
                                       FROM (SELECT *
                                               FROM (SELECT
                                                            'P_'||COLUMN_NAME||' '||TABLE_NAME||'.'||COLUMN_NAME||'%TYPE, --'||REPLACE(COMMENTS,chr(13),' ') AS COLUMN_NAME
                                                      FROM USER_COL_COMMENTS
                                                            WHERE TABLE_NAME ='OKFORM'

                                                         )
                                          ))
                              START WITH ROW_NUM = 1
                             CONNECT BY ROW_NUM = ROWNUM) )


这个就把OKFORM这个表的所有字段的定义都列出来了，如下：

P_FORMCLASSNAME OKFORM.FORMCLASSNAME%TYPE, --窗体类名
P_CAPTION OKFORM.CAPTION%TYPE, --标题
P_PARENT OKFORM.PARENT%TYPE, --父窗体类
P_FORM_TYPE OKFORM.FORM_TYPE%TYPE, --类型(0-不显示 1-显示)
P_FORM_ORDER OKFORM.FORM_ORDER%TYPE, --次序
P_SHORTCUT OKFORM.SHORTCUT%TYPE, --快捷键



为了便于使用，我把上面的sql封装成了一个函数:

-- 功能点:得到表变量参数
  FUNCTION get_tab_cols_param
  (
    p_table_name VARCHAR2, --表名
    p_prefix     CHAR:= 'P', --变量或参数前缀
    p_separator  char:=','   --各个变量间的分隔符
   
   
  ) RETURN VARCHAR2 IS
    v_value VARCHAR2(4000);
  BEGIN
    SELECT REPLACE(STR, '|', CHR(13) || CHR(10))
      INTO v_value
      
      FROM ((SELECT substr(MAX(SYS_CONNECT_BY_PATH(column_name, '|')), 2) AS STR
               FROM (SELECT ROWNUM AS ROW_NUM, column_name
                       FROM (SELECT *
                               FROM (SELECT p_prefix|| '_' || COLUMN_NAME || ' ' ||
                                            TABLE_NAME || '.' || COLUMN_NAME ||
                                            '%TYPE'||p_separator||' --' ||
                                            REPLACE(COMMENTS, chr(13), ' ') AS COLUMN_NAME
                                       FROM USER_COL_COMMENTS
                                      WHERE TABLE_NAME = p_table_name
                                    
                                     )))
              START WITH ROW_NUM = 1
             CONNECT BY ROW_NUM = ROWNUM));
   
    RETURN v_value;      
  
  END;


以上是我实现的方法，大家看看以上的sql是否还有优化的余地，或者有其他更好的方法

其实，函数用一个循环遍历所有字段  写起来更容易，而且效率也未必比上面的用SYS_CONNECT_BY_PATH 方法慢； 不过用SYS_CONNECT_BY_PATH实现起来感觉“酷”一点，而且可以不用函数，直接sql 就能搞定；

SELECT  'P_' || COLUMN_NAME || ' ' || TABLE_NAME || '.' || COLUMN_NAME ||'%TYPE'|| ',' ||' --' ||COMMENTS
FROM USER_COL_COMMENTS
WHERE TABLE_NAME ='&1'

足够了吧，何必那么麻烦呢

讲的有点道理，我有建了一个视图：
CREATE OR REPLACE VIEW VW_TAB_COLS_PARAM
AS
SELECT TABLE_NAME,   COLUMN_NAME || ' ' || TABLE_NAME || '.' || COLUMN_NAME ||'%TYPE'|| ',' ||' --' ||COMMENTS
AS PARAM
FROM USER_COL_COMMENTS

比如：
SELECT 'p_'||PARAM FROM VW_TAB_COLS_PARAM
WHERE TABLE_NAME='OKFORM'

好

视图？目的何在？没那个必要吧

QUOTE:

---

原帖由 zhangfengh 于 2008-6-30 13:17 发表
视图？目的何在？没那个必要吧

---

只是做了个封装而已，便于批量自动实现

p_字段名  表名.字段名%type,

这样的定义；

否则也记不住，就算记住，也要敲入那么一大段代码；
就算事先放在一个地方，每次用的时候，在找出来拷贝过来用也麻烦；
再说，你也要考虑也开发团队的其他人用；

主要是因为，有些过程参数较多，动辄十几个，手工输入有些麻烦；

不错！
另：
呵呵，自己打起广告来了。