如何优化子字符串&指示或替换调试函数

Question

我编写了下面的存储过程，目的是读取移动到服务器("DIR“文件夹)上的逗号分隔文件，在执行脚本时，它实质上解析文件(.csv)，并将数据分配给相应的变量(xJOB_ID、xCTRL_ID、xACCT_SEC、xCREATEDON_DATE)，以便将数据插入到表中。

我在Windows 7环境中使用Oracle SQL Developer版本4.0.0.13。幸运的是，在我把头撞到桌子上几次之后，代码就能工作了，而且我在运行脚本时没有遇到任何问题。

文件的示例格式：

1111，2，T，2000年10月10日

2222,12345，U，10/10/2001年10月

5555,123，S，10/10/1999

我的问题:我发现使用SUBSTRING & INSTRING函数解析数据有点困难，并想知道如何改进脚本，以便在需要调试的情况下，对于没有编写存储过程的人来说，可以很容易地解决这个问题。

如果有道理请告诉我。我给出了整个脚本，这样您就可以了解我想要完成的任务，并且可以为调试目的改进代码。

create or replace PROCEDURE SP_INSERT_INTO_TABLE(xFILE_NAME IN VARCHAR2)

IS 


--UTL_FILE is an oracle package that allows you to read and write operating system files. 

TEXT_DATA UTL_FILE.FILE_TYPE;

v_ROW_LENGTH    NUMBER := 1024;
v_TEXTSTRING    VARCHAR2(4000);
cLINE           VARCHAR2(100);
xJOB_ID         NUMBER;
xCTRL_ID        NUMBER;
xACCT_SEC       VARCHAR2(1);
xCREATEDON_DATE DATE;
xCOUNT          NUMBER := 0;

BEGIN
    BEGIN
    --Streams in the file data and assigns it to TEXT_DATA variable. 
    TEXT_DATA := UTL_FILE.FOPEN('DIR', xFILE_NAME, 'R', v_ROW_LENGTH);      
END;

--Begin LOOP to get each line and assign to cLINE to extract, assign to each variable, and insert into the table
LOOP    
    BEGIN
        --Gets each string/line up to the line terminator
        UTL_FILE.GET_LINE(TEXT_DATA, v_TEXTSTRING);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            EXIT;
    END;

    --Each line is assigned to the variable cLINE.
    cLINE := v_TEXTSTRING;

    --Begin to parse data using SUBSTRING and INSTRING functions
    BEGIN        
        --Extracts string from cLINE position 1 up to the first occurrence, converts it to a number, and assigns it to the variable. 
        xJOB_ID         := TO_NUMBER(SUBSTR(cLINE, 1,INSTR(cLINE, ',', 1, 1)-1));

        --Extracts string from cLINE between the 1st and 2nd occurrence, converts it to a number, and assigns it to the variable.
        xCTRL_ID        := TO_NUMBER(SUBSTR(cLINE, INSTR(cLINE, ',', 1, 1)+1, INSTR(cLINE, ',', 1,2)-INSTR(cLINE, ',', 1,1)-1));

        --Extracts string from cLINE between the 2nd and 3rd occurrence and assigns it to the variable.
        xACCT_SEC       := SUBSTR(cLINE, INSTR(cLINE, ',', 1, 2) +1, INSTR(cLINE, ',', 1,3)-INSTR(cLINE, ',', 1,2) -1);  

        --Extracts string from cLINE after the last occurrence, converts it to a date, and assigns it the variable.
        xCREATEDON_DATE := TO_DATE(SUBSTR(cLINE, INSTR(cLINE, ',', 1, 3)+1), 'MM/DD/YYYY');

        INSERT INTO TABLE(JOB_ID, CTRL_ID, ACCT_SEC, CREATEDON_DATE)
        VALUES(xJOB_ID, xCTRL_ID, xACCT_SEC, xCREATEDON_DATE);
        COMMIT;

        --Counter to count the amount of inserts
        xCOUNT := xCOUNT + 1;

    EXCEPTION
        --Exception to handle the conversion of a string to a NUMBER or value is longer than the declared length of the variable.
        WHEN VALUE_ERROR THEN 
        NULL;

    END;        
END LOOP;

DBMS_OUTPUT.PUT_LINE('RECORDS INSERTED: ' || xCOUNT);    
UTL_FILE.FCLOSE(TEXT_DATA);

END;

Answer 1

您可以使用REGEXP_SUBSTR，因为这只需要一个函数调用，而不是一系列SUBSTR和INSTR调用，例如：

(由Gary_W提供)：

declare
  v_str varchar2(20) := 'a,,bcd';
  v_substr1 varchar2(10);
  v_substr2 varchar2(10);
  v_substr3 varchar2(10);
begin
  v_substr1 := regexp_substr(v_str, '([^,]*)(,|$)', 1, 1, NULL, 1);
  v_substr2 := regexp_substr(v_str, '([^,]*)(,|$)', 1, 2, NULL, 1);
  v_substr3 := regexp_substr(v_str, '([^,]*)(,|$)', 1, 3, NULL, 1);
  dbms_output.put_line(v_substr1||':'||v_substr2||':'||v_substr3);
end;
/

a::bcd

如演示所示，上面将处理具有空部分的字符串。搜索模式被分成两组(也称为子表达式)：[^,]*和,|$。

第一组表示:任何不是逗号([^,])而出现0次或多次(*)的字符。

第二组说:逗号或行尾。

因此，整个模式正在寻找一组字符，不包括可能存在或不存在的逗号，后面跟着逗号或行尾。

regexp_substr中的最后一个参数表示我们希望从搜索模式中选择第一个子表达式来显示--如果我们没有包括这个子表达式，那么您将以逗号作为返回字符串的一部分而结束。

如果您绝对确信字符串的任何元素都不会为null，那么下面的操作就可以了：

declare
  v_str varchar2(20) := 'a,123,bcd';
  v_substr1 varchar2(10);
  v_substr2 varchar2(10);
  v_substr3 varchar2(10);
begin
  v_substr1 := regexp_substr(v_str, '[^,]+', 1, 1);
  v_substr2 := regexp_substr(v_str, '[^,]+', 1, 2);
  v_substr3 := regexp_substr(v_str, '[^,]+', 1, 3);
  dbms_output.put_line(v_substr1||':'||v_substr2||':'||v_substr3);
end;
/

a:123:bcd

这只是寻找一个指定的字符字符串的出现，这些字符不是逗号，比较容易摸索(imho!)与前面示例中使用的搜索模式相比，它的鲁棒性要差得多。