Oracle生成驼峰命名sql函数

一、执行结果

select
NAME_FIRST	AS	nameFirst,
NAME_TWO	AS	nameTwo,
NAME_TREE	AS	nameTree,
NAME_FOUR	AS	nameFour,
NAME_FIVE	AS	nameFive,
NAME_SIX	AS	nameSix
from TEST_TABLE

二、创建函数

-- 创建函数to_hump代码如下：

create or replace function to_hump(column_name IN VARCHAR2) RETURN VARCHAR2 IS
  str1      VARCHAR2(1024);
  RE_COLUMN VARCHAR2(1024);
BEGIN
  str1 := column_name;                                            /**/
  select decode(INSTR(str1, '_', 1, 1),           /*INSTR(str1,'_',1,1) 从第一个字符开始查找第一个‘_’的位置，返回'_'的位置*/
         0,
         str1,                                    /* INST没有’_’ 0，则函数返回小写的原值 */
         replace(str1, '_' || substr(str1, INSTR(str1, '_', 1, 1) + 1, 1),/* 把‘_’和后一个字符 替换成成这个大写字符 _a -> A */
                 upper(substr(str1, INSTR(str1, '_', 1, 1) + 1, 1))))
    into RE_COLUMN from dual;
  return RE_COLUMN;
END;

-- 创建函数create_sql代码如下：
create or replace function create_sql(table_name1 IN VARCHAR2) return clob is
  reval_1 varchar2(20000) ;/*chr(9) 空格     20000缓存sql长度*/
  re_clob clob;
  nu integer :=0;
begin
  for x in(select t.*,
                   (select comments comments1
                      from user_col_comments c
                     where c.TABLE_NAME = upper(table_name1)
                       and c.column_name = t.COLUMN_NAME) comments1
              from user_tab_columns t
             where Table_Name = upper(table_name1)) loop /*查询表的所有column 开启循环*/
    nu := nu+1;
    /*拼接 column as 别名*/
    reval_1:= reval_1 || x.COLUMN_NAME || chr(9) || 'AS'|| chr(9) || to_hump(to_hump(to_hump(to_hump(to_hump(lower(x.COLUMN_NAME)))))) || ',' || chr(10) ;/*chr(10) 换行*/
    end loop;
  if reval_1 is not null then
      reval_1 := substr(reval_1,0,length(reval_1)-2);/*去除最后一个，*/
      reval_1 := 'select' || chr(10)||reval_1||chr(10)|| 'from '|| table_name1;/*加头 select ，加尾 from*/
  end if;
    re_clob := reval_1;
  return(re_clob);
end create_sql;

三、调用函数：

-- 调用函数生成sql
select create_sql('TEST_TABLE') from dual；

四、转载于

链接: 点击查看原文.