在SQL中如何继续循环异常引发 [英] How to continue while loop after exception raised in SQL
问题描述
如果在下面的代码中引发异常，我将如何继续执行while循环？
How would I continue the while loop after an exception has been raised in the code below?
DECLARE
v_blob_data BLOB;
v_blob_len NUMBER;
v_position NUMBER;
v_raw_chunk RAW(10000);
v_char CHAR(1);
c_chunk_len number := 1;
v_line VARCHAR2 (32767) := NULL;
v_data_array wwv_flow_global.vc_arr2;
v_rows number;
v_sr_no number := 1;
v_first_line_done boolean := false;
v_error_cd number :=0;
v_quote_pos1 NUMBER;
v_quote_pos2 NUMBER;
v_enclosed_str VARCHAR(200);
v_errmsg VARCHAR2(4000);
BEGIN
delete from TEMP_MM_UPDATE where username = :P1_USER_ID;
-- Read data from wwv_flow_files</span>
select
blob_content
into v_blob_data
from wwv_flow_files
where name = :P2_FILE_UPLOAD;
v_blob_len := dbms_lob.getlength(v_blob_data);
v_position := 1;
-- Read and convert binary to char</span>
WHILE ( v_position <= v_blob_len )
LOOP
begin
v_raw_chunk := dbms_lob.substr(v_blob_data,c_chunk_len,v_position);
v_char := chr(hex_to_decimal(rawtohex(v_raw_chunk)));
v_line := v_line || v_char;
v_position := v_position + c_chunk_len;
-- When a whole line is retrieved </span>
IF v_char = CHR(10) THEN
LOOP
--Make sure there's something to replace
IF INSTR(v_line, '"', 1, 1) = 0 THEN
EXIT; -- If nothing to replace, exit loop and don't try
END IF;
--Find the position of the first and second quotes in the line of text
v_quote_pos1 := INSTR(v_line, '"', 1, 1);
v_quote_pos2 := INSTR(v_line, '"', 1, 2);
--Extract the inner string
v_enclosed_str := SUBSTR(v_line, v_quote_pos1 + 1, v_quote_pos2 - v_quote_pos1 - 1);
--perform the replacement
v_line := SUBSTR(v_line, 0, v_quote_pos1 - 1) || REPLACE(v_enclosed_str, ',', '<') || SUBSTR(v_line, v_quote_pos2 + 1);
END LOOP;
-- Convert comma to : to use wwv_flow_utilities </span>
v_line := REPLACE (v_line, ',', ':');
v_line := REPLACE (v_line, '<', ',');
v_line := REPLACE (trim(v_line), '-', NULL);
--v_line := REPLACE (trim(v_line), '"', NULL);
-- Convert each column separated by : into array of data </span>
v_data_array := wwv_flow_utilities.string_to_table (v_line);
--Check to see if the row of column headers has already been parsed through
IF(v_first_line_done != true)THEN
v_first_line_done := true;
--Check column order in spreadsheet
IF(v_data_array(1) LIKE '%Username%' AND
v_data_array(2) LIKE '%NDN%' AND
v_data_array(3) LIKE '%PCFN%') THEN
v_error_cd := 0;
v_line := NULL;
ELSE
v_error_cd := 1;
END IF;
--If first line is done and the column order is correct then
ELSIF(v_first_line_done = true AND v_error_cd = 0) THEN
-- Insert data into target table </span>
EXECUTE IMMEDIATE 'insert into TEMP_MM_UPDATE
(USERNAME,
RPT_FLAG,
PCFN)
values (:1,:2,:3)'
USING
v_data_array(1),
v_data_array(2),
v_data_array(3);
-- Clear out
v_line := NULL; v_sr_no := v_sr_no + 1;
END IF;
END IF;
exception
WHEN OTHERS then
v_errmsg := SQLERRM;
insert into temp_mm_update (username,error_desc)
values (:P1_USER_ID, v_errmsg);
END;
END LOOP;
DELETE FROM WWV_FLOW_FILES where name = :P2_FILE_UPLOAD;
DELETE FROM TEMP_MM_UPDATE WHERE USERNAME IS NULL AND PCFN IS NULL;
IF(v_error_cd = 1) THEN
INSERT INTO temp_mm_update (USERNAME, ERROR_DESC)
VALUES (:P1_USER_ID, 'Error. Please check column order in spreadsheet.');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
insert into temp_mm_update (username,error_desc)
values (:P1_USER_ID, 'No Data Found.');
WHEN OTHERS then
v_errmsg := SQLERRM;
insert into temp_mm_update (username,error_desc)
values (:P1_USER_ID, v_errmsg);
END;
当我将异常放在循环中，如上所述，该过程似乎没有完成，我最终当我尝试删除表格或类似的东西时，会出现NOWAIT错误。
When I put the exception inside the loop like above, the procedure never seems to finish and I end up getting a "NOWAIT" error when I try to delete the table or anything like that.
如果在循环后立即取出BEGIN也可以在循环中取出异常，但是我希望能够指定每个记录的错误，而不是处理正确的记录，然后在一个记录之后停止，例如只有一个列中的9个值接受6。
The code works fine if I take out the "BEGIN" right after the loop and also take out the exception inside the loop, but I want to be able to specify what is wrong with every record instead of processing correct records and then stopping after it comes to a record that has, for example, 9 values in a column that only accepts 6.
有人可以帮助吗？
谢谢，
Steven
编辑：
我更改了行：
ELSIF（v_first_line_done = true AND v_error_cd = 0）
to
ELSIF（v_first_line_done = true AND v_error_cd = 0 AND（v_data_array（1）not null OR v_data_array（1）不像'％ - ％'））
并且程序完成，但是当涉及到ecord这引发和例外，所有以下记录都有相同的例外。
EDITS:
I changed the line:
ELSIF(v_first_line_done = true AND v_error_cd = 0)
to
ELSIF(v_first_line_done = true AND v_error_cd = 0 AND (v_data_array(1) is not null OR v_data_array(1) not like '%-%'))
and the procedure finishes, but when it comes to a record that raises and exception, all of the following records have the same exception.
我的CSV文件示例：
用户名NDN PCFN
DOOL0008 X 808461
DOOL0008 X 886332
DOOL0008 X 887223
DOOL0008 X 887992
DOOL0008 X 904959
DOOL0008 X 904959
DOOL0008 X 904968
DOOL0008 X 904968
Sample of my CSV File:
Username NDN PCFN
DOOL0008 X 808461
DOOL0008 X 886332
DOOL0008 X 887223
DOOL0008 X 887992
DOOL0008 X 904959
DOOL0008 X 904959
DOOL0008 X 904968
DOOL0008 X 904968
所以例如，如果列MM（其数据类型为VARCHAR2（1））在第三个记录上具有值XX，并且异常将被引起该值太大，然后对于记录4-8将会提出相同的例外。
So for example if the column MM (which had a datatype of VARCHAR2(1)) has a value of "XX" on the third record, and exception will be raised that the value is too large, and then for records 4-8 the same exception will be raised.
推荐答案
通过玩我的代码，我弄清楚出了什么问题。
我需要将以下行添加到我的异常代码块中：
By playing around with my code I figured out what was wrong.
I needed to add in the following line into my exception block of code:
v_line := NULL; v_sr_no := v_sr_no + 1;
最终代码：
Final code:
DECLARE
v_blob_data BLOB;
v_blob_len NUMBER;
v_position NUMBER;
v_raw_chunk RAW(10000);
v_char CHAR(1);
c_chunk_len number := 1;
v_line VARCHAR2 (32767) := NULL;
v_data_array wwv_flow_global.vc_arr2;
v_rows number;
v_sr_no number := 1;
v_first_line_done boolean := false;
v_error_cd number :=0;
v_quote_pos1 NUMBER;
v_quote_pos2 NUMBER;
v_enclosed_str VARCHAR(200);
v_errmsg VARCHAR2(4000);
BEGIN
delete from TEMP_MM_UPDATE where username = :P1_USER_ID;
-- Read data from wwv_flow_files</span>
select
blob_content
into v_blob_data
from wwv_flow_files
where name = :P2_FILE_UPLOAD;
v_blob_len := dbms_lob.getlength(v_blob_data);
v_position := 1;
-- Read and convert binary to char</span>
WHILE ( v_position <= v_blob_len )
LOOP
begin
v_raw_chunk := dbms_lob.substr(v_blob_data,c_chunk_len,v_position);
v_char := chr(hex_to_decimal(rawtohex(v_raw_chunk)));
v_line := v_line || v_char;
v_position := v_position + c_chunk_len;
-- When a whole line is retrieved </span>
IF v_char = CHR(10) THEN
LOOP
--Make sure there's something to replace
IF INSTR(v_line, '"', 1, 1) = 0 THEN
EXIT; -- If nothing to replace, exit loop and don't try
END IF;
--Find the position of the first and second quotes in the line of text
v_quote_pos1 := INSTR(v_line, '"', 1, 1);
v_quote_pos2 := INSTR(v_line, '"', 1, 2);
--Extract the inner string
v_enclosed_str := SUBSTR(v_line, v_quote_pos1 + 1, v_quote_pos2 - v_quote_pos1 - 1);
--perform the replacement
v_line := SUBSTR(v_line, 0, v_quote_pos1 - 1) || REPLACE(v_enclosed_str, ',', '<') || SUBSTR(v_line, v_quote_pos2 + 1);
END LOOP;
-- Convert comma to : to use wwv_flow_utilities </span>
v_line := REPLACE (v_line, ',', ':');
v_line := REPLACE (v_line, '<', ',');
v_line := REPLACE (trim(v_line), '-', NULL);
--v_line := REPLACE (trim(v_line), '"', NULL);
-- Convert each column separated by : into array of data </span>
v_data_array := wwv_flow_utilities.string_to_table (v_line);
--Check to see if the row of column headers has already been parsed through
IF(v_first_line_done != true)THEN
v_first_line_done := true;
--Check column order in spreadsheet
IF(v_data_array(1) LIKE '%Username%' AND
v_data_array(2) LIKE '%NDN%' AND
v_data_array(3) LIKE '%PCFN%') THEN
v_error_cd := 0;
v_line := NULL;
ELSE
v_error_cd := 1;
END IF;
--If first line is done and the column order is correct then
ELSIF(v_first_line_done = true AND v_error_cd = 0) THEN
-- Insert data into target table </span>
EXECUTE IMMEDIATE 'insert into TEMP_MM_UPDATE
(USERNAME,
RPT_FLAG,
PCFN)
values (:1,:2,:3)'
USING
v_data_array(1),
v_data_array(2),
v_data_array(3);
-- Clear out
v_line := NULL; v_sr_no := v_sr_no + 1;
END IF;
END IF;
exception
WHEN OTHERS then
v_errmsg := SQLERRM;
insert into temp_mm_update (username,error_desc)
values (:P1_USER_ID, v_errmsg);
v_line := NULL; v_sr_no := v_sr_no + 1;
END;
END LOOP;
DELETE FROM WWV_FLOW_FILES where name = :P2_FILE_UPLOAD;
DELETE FROM TEMP_MM_UPDATE WHERE USERNAME IS NULL AND PCFN IS NULL;
IF(v_error_cd = 1) THEN
INSERT INTO temp_mm_update (USERNAME, ERROR_DESC)
VALUES (:P1_USER_ID, 'Error. Please check column order in spreadsheet.');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
insert into temp_mm_update (username,error_desc)
values (:P1_USER_ID, 'No Data Found.');
WHEN OTHERS then
v_errmsg := SQLERRM;
insert into temp_mm_update (username,error_desc)
values (:P1_USER_ID, v_errmsg);
END;
这篇关于在SQL中如何继续循环异常引发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
