用分析函数实现自动生成sqlloader定长数据装载控制文件的一个语句

来源：互联网发布：phantomjs python 编辑：程序博客网时间：2024/05/21 16:57

本文中的内容在《2010-05-07 22:08 Automatic SQL*LOADER(以定长方式为例) ，原文地址：http://hi.baidu.com/nsj820/item/b346e517416d43011994ec5c》也能实现，但这里主要想体现的是oracle分析函数的强大。

WITH FIX_LENGTH AS (SELECT A.TABLE_NAME,         A.COLUMN_ID,         A.COLUMN_NAME,         A.DATA_LENGTH,         SUM(P_OFFSET) OVER(PARTITION BY A.TABLE_NAME ORDER BY A.COLUMN_ID) P_START,         SUM(P_OFFSET) OVER(PARTITION BY A.TABLE_NAME ORDER BY A.COLUMN_ID) + DATA_LENGTH - 1 P_END    FROM (SELECT A.TABLE_NAME,                 A.COLUMN_ID,                 A.COLUMN_NAME,                 A.DATA_LENGTH,                 LAG(A.DATA_LENGTH, 1, 1) OVER(PARTITION BY A.TABLE_NAME ORDER BY A.COLUMN_ID) P_OFFSET,                 SUM(A.DATA_LENGTH) OVER(PARTITION BY A.TABLE_NAME ORDER BY A.COLUMN_ID) P_CONTINUOUS_SUMMATION            FROM USER_TAB_COLUMNS A) A)SELECT DISTINCT 'LOAD DATAINFILE *INTO TABLE ' || TABLE_NAME || 'REPLACE'  FROM USER_TAB_COLUMNS WHERE TABLE_NAME = &TAB_NAMEUNION ALLSELECT *  FROM (SELECT DECODE(COLUMN_ID, 1, '(', '') || COLUMN_NAME || '  POSITION(' ||               P_START || ':' || P_END || ')' ||               DECODE(COLUMN_ID,                      MAX(COLUMN_ID) OVER(PARTITION BY TABLE_NAME),                      ')',                      ',')          FROM FIX_LENGTH         WHERE TABLE_NAME = &TAB_NAME         ORDER BY TABLE_NAME, COLUMN_ID);

用到的知识点
1、oracle分析函数
1.1、偏移函数lead
1.2、sum()over(PARTITION by order by)连续求和的使用
2、union all的排序
使用union all不能任意排序，只能按两个union all的内容都有的字段进行排序；排序是对union all后的整体结果进行排序，而非其某一部分。
3、此代码可以实现sqlloader定长数据装载控制文件的编写
当然，用oracle游标也能实现；但本文中的方法相对简单一些。