oracle 存储过程 求助

Question

create or replace procedure test_cjsql_z( /*in_sqlnr in clob,*/out_code in out varchar2,
out_mess in out varchar2) Authid Current_User IS
PRAGMA AUTONOMOUS_TRANSACTION;
in_sqlnr clob;
V_LENGTH NUMBER;
V_NUMBER1 NUMBER;
V_NUMBER2 NUMBER;
V_BSF NUMBER; --标识附，标识SQL语句中最后CHR(10)的位置。
V_STEP NUMBER;
buffer VARCHAR2(4000);
V_TEMPSQL VARCHAR2(4000);
V_LINESQL VARCHAR2(4000);
V_SQLERR VARCHAR2(255);
jb_err exception;
amount INT;
--v_sql VARCHAR2(4000);
BEGIN
select sqlnr into in_sqlnr from t_sql where sql_id = '100475'; --取clob 字段
V_LENGTH := to_number(ceil(dbms_lob.getlength(in_sqlnr) / 2000)); --取lob长度
for i in 1 .. V_LENGTH loop
--循环次数
amount := 2000;
dbms_lob.read(in_sqlnr, amount, (i - 1) * 2000 + 1, buffer); --提取lob
buffer := replace(buffer, chr(13), ''); --替换 回车
buffer := V_TEMPSQL || buffer;
V_BSF := INSTR(buffer, CHR(10), -1, 1); --最后 换行 的位置
V_TEMPSQL := SUBSTR(buffer, V_BSF, length(buffer) - V_BSF + 1);
buffer := SUBSTR(buffer, 1, V_BSF);
V_STEP := 1;
loop
V_NUMBER1 := INSTR(buffer, CHR(10), 1, V_STEP);
V_NUMBER2 := INSTR(buffer, CHR(10), 1, V_STEP + 1);
V_LINESQL := RTRIM(REPLACE(SUBSTR(buffer,
V_NUMBER1,
V_NUMBER2 - V_NUMBER1 + 1),
CHR(10),
''),
';');
EXIT WHEN V_LINESQL IS NULL; --V_LINESQL 为 null 跳出循环
BEGIN

-- v_sql := 'insert into hx_sjmx.test_zh values (V_LINESQL)';
-- EXECUTE IMMEDIATE v_sql;
EXECUTE IMMEDIATE V_LINESQL; --执行 V_LINESQL
EXCEPTION
WHEN OTHERS THEN
V_SQLERR := SUBSTR(SQLERRM, 1, 255); --例外
out_code := '2';
out_mess := substrb(V_SQLERR || '-' || V_LINESQL, 1, 2000);
raise jb_err;
END;
V_STEP := V_STEP + 1;
end loop;
end loop;
out_code := '1';
out_mess := '执行成功!';
commit;
exception
when jb_err then
rollback;
when others then
V_SQLERR := SUBSTR(SQLERRM, 1, 255);
out_code := '3';
out_mess := V_SQLERR;
rollback;
END test_cjsql_z;

麻烦给个朋友 给解释一下 各个步骤的作用功能 谢谢了

Answer 1

详见文本中“--注：”后的注释
create or replace procedure test_cjsql_z( /*in_sqlnr in clob,*/out_code in out varchar2,
out_mess in out varchar2) Authid Current_User IS
--注：创建存储过程test_cjsql_z，输入输出型参数out_code、out_mess
PRAGMA AUTONOMOUS_TRANSACTION; --注：自助事务处理
in_sqlnr clob;--注：声明clob类型变量
V_LENGTH NUMBER;--注：number是数值类型变量
V_NUMBER1 NUMBER;
V_NUMBER2 NUMBER;
V_BSF NUMBER; --标识附，标识SQL语句中最后CHR(10)的位置。
V_STEP NUMBER;
buffer VARCHAR2(4000);--注：varchar2(xx)是字符串类型变量，xx是长度
V_TEMPSQL VARCHAR2(4000);
V_LINESQL VARCHAR2(4000);
V_SQLERR VARCHAR2(255);
jb_err exception;--注：声明例外对象
amount INT;
--v_sql VARCHAR2(4000);
BEGIN
select sqlnr into in_sqlnr from t_sql where sql_id = '100475'; --取clob 字段
V_LENGTH := to_number(ceil(dbms_lob.getlength(in_sqlnr) / 2000)); --取lob长度
for i in 1 .. V_LENGTH loop
--循环次数
amount := 2000;--注：以2000为单位
dbms_lob.read(in_sqlnr, amount, (i - 1) * 2000 + 1, buffer); --提取lob
buffer := replace(buffer, chr(13), ''); --替换 回车
buffer := V_TEMPSQL || buffer;--注：拼接字符串
V_BSF := INSTR(buffer, CHR(10), -1, 1); --最后 换行 的位置
V_TEMPSQL := SUBSTR(buffer, V_BSF, length(buffer) - V_BSF + 1);--注：从buffer中截取字符串
buffer := SUBSTR(buffer, 1, V_BSF);
V_STEP := 1;
loop
V_NUMBER1 := INSTR(buffer, CHR(10), 1, V_STEP);
V_NUMBER2 := INSTR(buffer, CHR(10), 1, V_STEP + 1);
V_LINESQL := RTRIM(REPLACE(SUBSTR(buffer,
V_NUMBER1,
V_NUMBER2 - V_NUMBER1 + 1),
CHR(10),
''),
';');
EXIT WHEN V_LINESQL IS NULL; --V_LINESQL 为 null 跳出循环
BEGIN
-- v_sql := 'insert into hx_sjmx.test_zh values (V_LINESQL)';
-- EXECUTE IMMEDIATE v_sql;
EXECUTE IMMEDIATE V_LINESQL; --执行 V_LINESQL
EXCEPTION --注：FOR循环中的例外处理部分
WHEN OTHERS THEN
V_SQLERR := SUBSTR(SQLERRM, 1, 255); --例外
out_code := '2';--注：输出返回代码
out_mess := substrb(V_SQLERR || '-' || V_LINESQL, 1, 2000);--注：输出返回文本
raise jb_err;--注：抛出例外
END;
V_STEP := V_STEP + 1;
end loop;
end loop;
out_code := '1';--注：输出返回代码
out_mess := '执行成功!';--注：输出返回文本
commit;
exception--注：例外处理
when jb_err then--注：当出现自定义例外时
rollback;--注：回滚
when others then--注：其它情况
V_SQLERR := SUBSTR(SQLERRM, 1, 255);
out_code := '3';--注：输出返回代码
out_mess := V_SQLERR;--注：输出返回文本
rollback;--注：回滚
END test_cjsql_z;追问

请问这个过程 最最终 通过 能得到什么东西 没看明白

EXECUTE IMMEDIATE V_LINESQL; --执行 V_LINESQL

这里的V_LINESQL 得到的 是个字符串， 不是执行脚本 总是这报错

追答

目测是从t_sql的sqlnr列读取文本（应该存的是SQL语句）到in_sqlnr
通过EXECUTE IMMEDIATE V_LINESQL; --执行 V_LINESQL
由于sqlnr是clob类型，所以有用字符串缓冲读取的过程
还有有些状态判断和例外处理的逻辑