65.9K
CodeProject 正在变化。 阅读更多。
Home

为 Spooling 准备 (Oracle) SQL

Marijan Nikic
starIconstarIconstarIconstarIconstarIcon

5.00/5 (1投票)

2019 年 8 月 16 日

CPOL

8分钟阅读

viewsIcon

10626

downloadIcon

91

一个用 JavaScript 编写的 HTML 工具，可将常规 Oracle SQL 查询转换为 spooling 脚本

引言

很多时候，需要从数据库中提取大量数据。在这些情况下，大多数数据库工具通常会失败，要么出现内存不足异常、缓冲区问题、内存访问冲突，要么它们根本无响应。

因此，出现了以最干净的方式提取数据的需求——直接将其 spool 到文本文件，通常是逗号分隔值。

不幸的是，不能简单地 spool 一个普通的查询——查询本身需要进行一些输出操作，否则结果将无法使用。

本文介绍的工具是对常规 SQL 查询进行准备的过程的自动化，使其能够输出分隔符分隔的值，这些值可以被读取并传输到例如 Excel 格式。

背景

如前所述，为了从 Oracle 数据库 spool 查询结果，需要以特殊方式准备查询，以便输出数据可用。

有几点需要考虑：首先，我们需要设置正确的会话参数；然后，我们需要标记 SQL 处理引擎 spool 的开始和结束位置；最后，为了实现分隔符分隔的输出格式，我们需要以某种方式操作查询列的输出——所有这些都将在后续文本中进行解释。

附言我并不是说这是从 Oracle 数据库 spool 的唯一方法，但在阅读了互联网上的许多文章和评论后，结论是 Oracle spool 引擎确实存在一些需要手动覆盖的限制。

必要的会话参数

SET PAGESIZE 0 LINESIZE 5000 TRIMSPOOL ON FEEDBACK OFF ECHO OFF TERMOUT OFF VERIFY OFF;
  • PAGESIZE 0 用于将页面大小设置为无限——否则，输出中的行将每 20 行（默认）分割一次，中间会有空行，并且页眉会在每页重复。
  • LINESIZE 5000 表示将一行的大小设置为 5000 个字符——否则，行将在 1024 个字符（默认）后断开。您可以将此值设置为任何大于等于 1 且小于等于 32767 的数字。
  • TRIMSPOOL ON 表示任何比 linesize 短的内容都将被修剪——否则，您会得到直到最大 linesize 的每行的空格。
  • FEEDBACK OFF 表示 SQL 引擎的反馈被关闭——否则，您会在输出中看到类似“xxx 行已选择”的消息。
  • ECHO OFF 表示 echo 命令将不起作用——否则，它的输出也会进入分隔文件。
  • TERMOUT OFF 用于关闭 SQL 命令本身的输出——否则，命令也会与结果一起写入输出文件。
  • VERIFY OFF 用于关闭查询中替代变量的替换信息——否则，输出的顶部会包含类似以下内容：
    旧：... &as_of_date ...<br /> 新：... 31.08.2019 ...

Oracle Spool 引擎的限制和解决方法

可以通过 Oracle 引擎自动创建查询结果的定界文件，无需修改原始查询。

这将通过一个会话参数列表来完成，如下所示：

set heading on
set colsep ;
set headsep on
set pagesize 0
set trimspool on
set linesize 5000

但是，这会产生大量的空格，因为默认行为是每个列都输出其定义的最大字符数，因此如果一个列定义为 VARCHAR(100)，则每行都会是 100 个字符长——例如，如果值为 'Hello world!'，它有 12 个字符长，后面将跟着 88 个空格。

因此，为了避免这种情况，我们不使用 colsep 参数来实现定界，而是将原始查询包装在一个只有一个列的查询中，该查询将原始查询（现在是子查询）的所有列连接在一起，并用选定的定界符分隔，如下所示：

SELECT
col1 || ';' ||
col2 || ';' ||
col3  || ';' ||
...
FROM
(
SELECT
col1,
col2,
col3,
...
FROM
...
)

另外，标题功能通常不如预期工作，因此标题不会显示，因此我们也对标题应用相同的逻辑：

SELECT
'col1;' ||
'col2;' ||
'col3;' ||
...
FROM DUAL
UNION ALL
...

原始查询有一个特殊要求才能使其正常工作：原始查询中的所有列都需要是单个单词或具有别名！！！

这些是可以的

column_1

t.column_1

nvl(t.column_1, 0) col1

sum(t.column_1 + t.column_2) col12

这些是不可以的

nvl(t.column_1, 0)

t.column_1 + t.column_2

case when column_1 is null then 0 else column_1 end

结果查询

SET PAGESIZE 0 LINESIZE 5000 TRIMSPOOL ON FEEDBACK OFF ECHO OFF  TERMOUT OFF VERIFY OFF;

SPOOL "&spool_location";

SELECT
'col1;' ||
'col2;' ||
'col3;' ||
...
FROM DUAL
UNION ALL
SELECT
col1 || ';' ||
col2 || ';' ||
col3  || ';' ||
...
FROM
(
-- original query
SELECT
col1,
col2,
col3,
...
FROM
...
-- end of original query
)

Using the Code

HTML 布局

该工具包含 2 个按钮，一个输入和一个输出 textarea，以及 2 个用于行号显示的 textarea。

原始查询被写入或粘贴到上面的 textarea 中，通过单击 Convert 按钮生成结果，并在下面的 textarea（它是 readonly）中显示。

textarea 下方的标签显示当前选中的内容。

Clear 按钮会清除输入和输出的 textarea。

我将不详细介绍 HTML 代码，只提一下每个元素定义的事件处理程序以及元素的 ID。

Convert 按钮

onclick="convert()"

Clear 按钮

onclick="clear_text()"

输入行号 textarea

id="sql_in_rownr"

输入 textarea

id="sql_in" onclick="selectionchanged(this)" onkeyup="keyup(this,event)" 
onfocus="inputfocus()" onfocusout="resetinput()" oninput="input_changed()" 
onscroll="scroll_changed(this)"

输出行号 textarea

id="sql_out_rownr"

输出 textarea

id="sql_out" onclick="selectionchanged(this)" 
    onkeyup="keyup(this,event)" onscroll="scroll_changed(this)"

JavaScript

事件处理程序

以下函数主要负责 HTML 显示，与 SQL 代码的解析或转换无关。

  • keyup(obj, e) - 此函数在 onkeyup 事件上调用，并启用键盘滚动。
  • selectionchanged(obj) - 刷新当前选区显示。由 keyup 函数和 onclick 事件调用。
  • initialize() - 在 onload 事件上调用。初始化行号。
  • populate_rownr(obj, cntline) - 将行号填充到 rownr 文本区域的函数。
  • input_changed() - 由输入 textarea 的 oninput 事件调用。它计算更改输入中的行数，与更改前的行数进行比较，并相应地更新 rownr 和滚动。
  • scroll_changed(obj) - 在输入和输出 textarea 的 onscroll 事件上调用。确保行号和主 textarea 之间的滚动同步。
  • scrollsync(obj1, obj2) - 由 scroll_changed 事件处理程序调用。
  • count_lines(txt) - 计算字符串 txt 中的行数。
  • inputfocus() - 当输入 textarea 获得焦点时，此函数会删除默认文本。
  • resetinput() - 当输入 textarea 失去焦点时，此函数会将默认文本（如果 textarea 为空）重新显示。
  • clear_text() - 清除输入和输出的 textarea，并重置选区和行号。

行号显示

此页面的行号在其另一篇文章中有更详细的解释：

SQL 解析和转换

convert() 函数是转换输入 SQL 并创建输出 SQL 代码的主要函数。它调用所有子函数，并处理错误消息的显示。

首先，它检查输入 SQL 查询是否有效——这意味着只能有一个命令；如果有一个以上的命令，它将报告错误。

insql = trimFunc(sql_in.value);
res = checksql(insql);

请注意，checksql 函数进行非常健壮的检查；它将逐个字符地遍历整个查询，并计算字符 ';' 的出现次数。

    function checksql(sql)
    {
        // check if the sql is a single query
        
        var inquote = false;
        var comm_inline = false, comm_mult = false;
        var cnt = 0;
        for(var i=0; i<sql.length; i++)
        {
            c1 = sql.substring(i, i+1);
            c2 = sql.substring(i, i+2);
            
            if(c1=='\'' || c1=='\"') inquote=!inquote;
            if(c2=='--') comm_inline=!comm_inline;
            if(c1=='\n' && comm_inline) comm_inline = false;
            if(c2=='/*' || c2=='*/') comm_mult = !comm_mult;
            
            if(!inquote && !comm_inline && !comm_mult && c1==';' && 
                            trimFunc(sql.substring(i,sql.length-1))!='')
            {
                error_msg = 'INVALID SQL - Multiple commands detected!';
                return -1;
            }
        }
        return 0;
    }

在此之后，调用 gather_cols 函数。

res = gather_cols();

此函数首先通过调用 searchsql 函数搜索并隔离 SELECT 子句，然后逐个字符地解析 SELECT 子句，提取每个列表达式，最后对每个表达式调用 get_alias 函数来提取有效的列名或别名。

基于 searchsql 和 get_alias 函数的返回值，它设置错误消息并返回到主函数 convert()。

    function gather_cols()
    {
        // runs through input sql char by char
        // identifies SELECT clause by calling searchsql
        // isolates expressions from the SELECT clause, 
        // and from each expression extracts alias by calling get_alias
        
        var sql = insql;
        
        // eliminate ; in the end of SQL
        if(sql.substring(sql.length - 1,sql.length) == ';')
               sql = sql.substring(0, sql.length - 1);
        
        // search for SELECT clause
        sql = searchsql(sql, 'SELECT', 'FROM');
        if(sql == '' || sql == 'SELECT')
        {
            cols = '';
            sql == '' ? error_msg = 'INVALID SQL - SELECT not found!' : 
                        error_msg = 'INVALID SQL - FROM not found!';
            return -1;
        }
        sql = trimFunc(sql.substring('SELECT'.length+1,sql.length));
        
        cols = '';
        var cnt_par = 0;
        var inquote = false;
        var expr = '';
        var alias = '';
        var c;
        // run through the query char by char
        // identify each column expression - search for ,
        // ignore if you are inside a comment or parentheses or quotes
        // for each expr call get_alias
        for(var i = 0; i < sql.length; i++)
        {
            if(i <= sql.length - 1) c = sql.substring(i, i + 1);
            else c = ',';
            // check for parentheses
            if(c == '(') cnt_par++;
            else if(c == ')') cnt_par--;
            // check for quotes
            if(c == '\"') inquote = !inquote;
            if(c == ',' && !inquote && cnt_par == 0) // found end of expression
            {
                expr = trimFunc(expr);
                // get alias from expr
                alias = get_alias(expr);
                if(alias == '')
                {
                    error_msg = 'INVALID SQL - Invalid or no alias found in expression' + expr;
                    return -1;
                }
                cols = cols + alias + ';' + '\n';
                expr = '';
            }
            else
            {
                expr = expr + c;
            }
        }
        if(cols != '')
        {
            cols = cols.substring(0, cols.length - (';' + '\n').length);
        }
        return 0;
    }

searchsql 函数的设计目的是返回 SQL 查询中两个关键字之间的所有内容。

它逐个字符地解析 SQL 表达式，首先查找 keyword1，然后找到它时，将所有字符记录在一个 string 变量中，直到遇到 keyword2。

（该函数需要跟踪它是否处于单引号/双引号、括号或注释中，因为这些不应被考虑在内。）

如果找不到第一个关键字，它将返回一个空的 string；如果找不到第二个关键字，它将只返回第一个关键字。

    function searchsql(sql,keyw_from,keyw_to)
    {
        /*
        this function will return the substring of sql string 
        from the first occurrence of keyw_from (not in parentheses or comment or quote)
        until the first following occurrence of keyw_to 
        (also not in parentheses or comment or quote)
        + it will erase all the comments from sql
        */
        
        var found1 = false, found2 = false;
        var tmpsql = sql.toUpperCase();
        var tmpkeyw1 = keyw_from.toUpperCase();
        var tmpkeyw2 = keyw_to.toUpperCase();
        var retval = '';
        // search for first keyword keyw outside of parentheses, 
        // comments or quotes (eliminate WITH clause)
        var inquote = false;
        var cnt_par = 0, comm_inline = false, comm_mult = false;
        var lensql = tmpsql.length;
        var skip = true;
        var c1, c2, cw1, cw2, lookbehind, lookahead1, lookahead2;
        // read sql char by char
        // skip everything until first occurrence of keyw_from found 
        // (ignore comments, parentheses and quotes)
        // after keyw_from found, stop skipping until keyw_to reached, then return
        for(var i=0; i<lensql; i++)
        {
            c1 = tmpsql.substring(i, i+1); // current char
            c2 = tmpsql.substring(i, i+2); // current and next char
            
            cw1 = tmpsql.substring(i, i+tmpkeyw1.length); // chars in length of keyw_from
            cw2 = tmpsql.substring(i, i+tmpkeyw2.length); // chars in length of keyw_to
            
            lookbehind = tmpsql.substring(i-1, i); // prev character
            lookahead1 = tmpsql.substring(i+tmpkeyw1.length, 
                                          i+tmpkeyw1.length+1); // char following cw1
            lookahead2 = tmpsql.substring(i+tmpkeyw2.length, 
                                          i+tmpkeyw2.length+1); // char following cw2
            
            if(c1=='\'' || c1=='\"') inquote=!inquote; // check quotes
            if(c1=='(' || c1==')') c1=='(' ? cnt_par++ : cnt_par--; // check parentheses
            if(c2=='--' && !comm_inline || c1=='\n' && comm_inline) 
                            comm_inline = !comm_inline;          // check inline comment
            if(c2=='/*' && !comm_mult || c2=='*/' && comm_mult)  // check multiline comment
            {
                comm_mult = !comm_mult;
                i++;
                continue;
            }
            
            if(!comm_inline && !comm_mult) // ignore comments
            {
                // keyw1 found - stop skipping chars
                if(cw1==tmpkeyw1 && trimFunc(lookbehind)=='' 
                     && trimFunc(lookahead1)=='' && cnt_par == 0 && 
                     !inquote) // 1st keyw found - it has to be surrounded by blank chars, 
                               // and not in parentheses or quotes
                {
                    skip = false;
                    found1 = true;
                }
                if(!skip)
                {
                    if(cw2==tmpkeyw2 && trimFunc(lookbehind)=='' 
                           && trimFunc(lookahead2)=='' && cnt_par == 0 
                           && !inquote) // 2nd keyw found - it has to be surrounded 
                                        // by blank chars, and not in parentheses or quotes
                    {
                        found2 = true;
                        break;
                    }
                    retval += sql.substring(i, i+1);
                }
            }
        }
        if(!found1) return ''; // first keyw not found
        if(!found2) return keyw_from; // found only the first keyw -- second keyw not found
        return retval;
    }

get_alias 函数有些复杂，因为需要考虑各种挑战，例如：

  • 列名或别名可能包含空格，并用双引号括起来。
  • 表达式中可能包含包含空格的 string，这使得将表达式中的单独单词检测为一个整体更加困难。
  • 表达式中也可能存在非单词分隔符的空格，例如算术运算符前后。
  • 函数和逗号内部也可能存在空格，这会使检测实际可以代表别名的单词变得更加困难。

为了克服这些挑战，我使用了替换和映射字典，在实际将表达式拆分成单词并检查可能的别名之前，使事情更容易、更易读。

        //map-substitute single and double quoted words
        for(var i = 0; i < expr.length; i++)
        {
            c = expr.substring(i,i+1);
            if(c=='\'')    inquote1=!inquote1;
            if(c=='\"')    inquote2=!inquote2;
            if(inquote1 || c=='\'') word1 += c;
            if(inquote2 || c=='\"') word2 += c;
            if(!inquote1 && word1.length > 0)
            {
                dict_quotes1['#' + i + '#'] = word1;
                tmpexpr = tmpexpr.replace(word1, '#' + i + '#');
                word1='';
            }
            if(!inquote2 && word2.length > 0)
            {
                dict_quotes2['#' + i + '#'] = word2;
                tmpexpr = tmpexpr.replace(word2, '#' + i + '#');
                word2='';
            }
        }
        // map-substitute expressions in parentheses
        word1='';
        for(i = 0; i < tmpexpr.length; i++)
        {
            c = tmpexpr.substring(i,i+1);
            if(c=='(') inpar++;
            if(c==')') inpar--;
            if(inpar>0 || c=='(' || c==')') word1 += c;
            if(inpar==0 && word1.length > 0)
            {
                dict_par['#' & i & '#'] = word1;
                tmpexpr = tmpexpr.replace(word1, '#' + i + '#');
                word1='';
            }
        }

另外，我需要擦除算术运算符周围的所有剩余空格；这些多个使用正则表达式的替换命令完成了任务。

tmpexpr = tmpexpr.replace(/ *\+ */g,'+').replace(/ *\- */g,'-').replace
          (/ *\* */g,'*').replace(/ *\/ */g,'/');

最后，当表达式可读时，函数会将表达式拆分成“单词”，我们假设最后一个单词应该是别名。

为了拥有一个有效的别名，需要满足以下规则：

  • 如果最后一个单词是单引号单词，则它不是有效的别名。
  • 如果单词包含点，但不是表达式中唯一的单词，则它不是有效的别名。
  • 如果单词包含算术运算符，则它不是别名。

最后，消除了点符号，如果别名被双引号括起来，则从映射字典中取出原始单词。

get_alias 函数的全部代码

    function get_alias(expr)
    {
        // function to extract alias from a column expression from SELECT clause
        // first it searches for all single and double-quoted expressions 
        // and replaces them with some mapping
        // then it does the same thing with expressions in parentheses
        // --> this is done to clean out the expression, to be more readable
        // next it deletes all the white spaces around arithmetic operators - 
        // this will be needed since the next step will be dividing the 
        // expression into words --> so we want to see an arithmetic expression as one word
        // next - take the last word and presume it to be alias - check if this word 
        // qualifies as alias, if OK, return it, else return blank (error)
        
        var alias;
        
        var dict_quotes1 = {};
        var dict_quotes2 = {};
        var dict_par = {};
        
        var tmpexpr = expr;
        
        var inquote1 = false, inquote2 = false;
        var c, word1 = '', word2 = '';
        
        var inpar = 0;
        
        //map-substitute single and double quoted words
        for(var i = 0; i < expr.length; i++)
        {
            c = expr.substring(i,i+1);
            if(c=='\'')    inquote1=!inquote1;
            if(c=='\"')    inquote2=!inquote2;
            if(inquote1 || c=='\'') word1 += c;
            if(inquote2 || c=='\"') word2 += c;
            if(!inquote1 && word1.length > 0)
            {
                dict_quotes1['#' + i + '#'] = word1;
                tmpexpr = tmpexpr.replace(word1, '#' + i + '#');
                word1='';
            }
            if(!inquote2 && word2.length > 0)
            {
                dict_quotes2['#' + i + '#'] = word2;
                tmpexpr = tmpexpr.replace(word2, '#' + i + '#');
                word2='';
            }
        }
        // map-substitute expressions in parentheses
        word1='';
        for(i = 0; i < tmpexpr.length; i++)
        {
            c = tmpexpr.substring(i,i+1);
            if(c=='(') inpar++;
            if(c==')') inpar--;
            if(inpar>0 || c=='(' || c==')') word1 += c;
            if(inpar==0 && word1.length > 0)
            {
                dict_par['#' & i & '#'] = word1;
                tmpexpr = tmpexpr.replace(word1, '#' + i + '#');
                word1='';
            }
        }
        // delete all the spaces around arithmetic operators
        tmpexpr = tmpexpr.replace(/ *\+ */g,'+').replace(/ *\- */g,'-').replace
                  (/ *\* */g,'*').replace(/ *\/ */g,'/');
        // presume last word is alias
        alias = tmpexpr.split(' ')[tmpexpr.split(' ').length-1];
        // if the last word is in parentheses or in single quotes, this is not a valid alias
        for(var key in dict_par)
            if(alias==key) return '';
        for(var key in dict_quotes1)
            if(alias==key) return '';
        // if the alias contains table alias but is not the only word in the expression, 
        // or it contains arithmetic operator, this is not a valid alias
        if(alias.replace('.','').length != alias.length && tmpexpr.split(' ').length > 1 
        || alias.replace('+','').replace('-','').replace('*','').replace('/','').length 
        != alias.length) return '';
        // remove any table alias
        alias = alias.split('.')[alias.split('.').length-1];
        // if it is double-qouted alias, get the original expression from the mapping dictionary
        for(var key in dict_quotes2)
            if(alias==key) alias = dict_quotes2[key];
        return trimFunc(alias);
    }

Spool 查询输出

提取所有列后，唯一剩下的工作就是安排输出 SQL，这由 generate_spool_sql 函数完成。

    function generate_spool_sql()
    {
        // generate output (spool) sql using the previously populated column list 
        // saved in variable cols
        var colsarr = cols.split('\n');
        cols = '';
        var colshead = '';
        var col = '';
        for(i = 0; i < colsarr.length; i++)
        {
            col = colsarr[i];
            if(i < colsarr.length - 1) col = col.substring(0, col.length - 1);
            colshead += '\'' + col.replace(/\"/g, '') + '\'||\';\'';
            cols += col + '||\';\'';
            if(i < colsarr.length - 1)
            {
                colshead += '||' + '\n';
                cols += '||' + '\n';
            }
        }
        // get rid of ; from the end of the insql
        if(insql.substring(insql.length-1,insql.length)==';')
            insql = insql.substring(0,insql.length-1);
        // output of spool script
        outsql = sql_set + '\n' + '\n' +
            'SPOOL \"&spool_location\";'  + '\n' + '\n' +
            'SELECT' + '\n' +
            colshead + '\nFROM DUAL\nUNION ALL\n' +
            'SELECT\n' + cols + '\nFROM\n(\n' +
            insql + '\n);\n\n' +
            'SPOOL OFF;\n' +
            'CLEAR BUFFER;';
    }

历史

  • 2019 年 8 月 17 日：初始版本
© . All rights reserved.