存储过程问题2则

ORA-20000：ORU-10027：buffer overflow,limit of 2000 bytes.

 

https://www.cnblogs.com/aipan/p/5306613.html

oracle存储过程中ORA-01403: 未找到数据 问题解决方法

https://blog.csdn.net/u010999809/article/details/80663895

 

##sample 0 oracle  存储过程的调试过程， 包括source 0 和 source 1, (参考案例)。从0 到test 1 再到test 2. 再到test3 ,每一步步的调试阶段的存储过程，

调试完一个存储过程再进行第二个存储过程调试

 

##Oracle 存储过程使用参考 ， 这个案例有cursor 游标，和fecth into 使用和 execute immediate语法使用 ，可以参考一下。

############source 0
Testcase:
=======

create table test (id integer,name varchar2(32));

create unique index idx_u_test on test(id);

create table test1 (id integer,name varchar2(32));

create table t_error (id integer,name varchar2(32));

insert into test values (2,'2');
insert into test values(4,'4');

commit;

insert into test1 values (1,'2');
insert into test1 values (2,'2');
insert into test1 values (3,'2');
insert into test1 values (4,'2');
insert into test1 values (5,'2');
insert into test1 values (6,'2');
insert into test1 values (7,'2');
insert into test1 values (8,'2');
commit;

CREATE OR REPLACE PROCEDURE p_insert
as
i_id integer;
s_name varchar2(32);
exeSql varchar2(200);
exeSql2 varchar2(200);
cursor cur_cdr is SELECT id,name FROM test1;
BEGIN
OPEN cur_cdr ;
LOOP
FETCH cur_cdr INTO i_id,s_name;
EXIT WHEN cur_cdr%NOTFOUND;
exeSql := 'insert into test values('||i_id||','||''''||s_name||''''||')';
DBMS_OUTPUT.put_line(exeSql);
execute immediate exeSql;
DBMS_OUTPUT.put_line('Completed: '||exeSql);
commit;

EXCEPTION
WHEN OTHERS THEN
begin
exeSql2 := 'insert into t_error values(' ||i_id||','||''''||s_name||''''||')';
execute immediate exeSql2;
commit;
end;

END LOOP;
close cur_cdr;
END p_insert;
/

 

###############souce 1 file .,这里使用FOR irec IN loop ,end loop 循环，我们可以借鉴下。
ormally the statement CONTINUE immediately completes the current iteration of a loop and passes control to the next iteration of the loop. But when it is executed in a CURSOR FOR LOOP and the initialization parameter PLSQL_OPTIMIZE_LEVEL is set to 2 (which is the default setting on 11G), it exits the loop.

The following sample code demonstrates the behavior:

set serverout on
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 2;

DECLARE
x NUMBER := 0;
CURSOR get_6rows IS SELECT * FROM EMP WHERE ROWNUM < 7;
BEGIN
DBMS_OUTPUT.PUT_LINE ('First a basic loop without CURSOR FOR LOOP running fine');
LOOP -- After CONTINUE statement, control resumes here
DBMS_OUTPUT.PUT_LINE ('Inside loop: x = ' || TO_CHAR(x));
x := x + 1;
IF x < 3 THEN
CONTINUE;
END IF;
DBMS_OUTPUT.PUT_LINE ('Inside loop, after CONTINUE: x = '||TO_CHAR(x));
EXIT WHEN x = 5;
END LOOP;
DBMS_OUTPUT.PUT_LINE (' After loop: x = ' || TO_CHAR(x));
DBMS_OUTPUT.PUT_LINE (' Basic loop was working correctly');
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE ('Should see the same results with CURSOR FOR LOOP');
x := 0;
FOR irec IN get_6rows LOOP -- After CONTINUE statement, control should resume here
DBMS_OUTPUT.PUT_LINE ('Inside cursor loop, before CONTINUE: x = ' || TO_CHAR(x));
x := x + 1;
IF x < 3 THEN
CONTINUE;
END IF;
DBMS_OUTPUT.PUT_LINE ('Inside cursor loop, after CONTINUE: x = ' || TO_CHAR(x));
END LOOP;
DBMS_OUTPUT.PUT_LINE (' After cursor loop: x = ' || TO_CHAR(x));

IF X < 6 THEN
DBMS_OUTPUT.PUT_LINE (' So the CURSOR FOR LOOP is exited on call to CONTINUE' );
END IF;
end;
/

OUTPUT :-

############test 1

set serverout on
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 2;

DECLARE
x NUMBER := 0;
CURSOR get_6rows IS SELECT *
FROM (SELECT owner,SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB
FROM DBA_SEGMENTS
GROUP BY owner,SEGMENT_NAME, segment_type
having segment_type like '%TABLE%'
ORDER BY 3 DESC)
WHERE ROWNUM < 50;
BEGIN
DBMS_OUTPUT.PUT_LINE ('First a basic loop without CURSOR FOR LOOP running fine');

FOR irec IN get_6rows LOOP -- After CONTINUE statement, control should resume here

DBMS_OUTPUT.PUT_LINE ('Inside loop: x = ' || TO_CHAR(x));
x := x + 1;
IF x < 3 THEN
CONTINUE;
END IF;
--DBMS_OUTPUT.PUT_LINE ('Inside loop, after CONTINUE: x = '||TO_CHAR(x));
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('owner_name: '||irec.owner);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('sgment_name: '||irec.SEGMENT_NAME);
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('sgment_size: '||irec.MB);
EXIT WHEN x = 5;
END LOOP;
end;
/

 

########## test 2

DECLARE
x NUMBER := 0;
CURSOR get_50rows IS SELECT *
FROM (SELECT owner,SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB
FROM DBA_SEGMENTS
GROUP BY owner,SEGMENT_NAME, segment_type
having segment_type like '%TABLE%'
ORDER BY 3 DESC)
WHERE ROWNUM < 50;
BEGIN
--DBMS_OUTPUT.PUT_LINE ('First a basic loop without CURSOR FOR LOOP running fine');

FOR irec IN get_50rows LOOP -- After CONTINUE statement, control should resume here

--DBMS_OUTPUT.PUT_LINE ('Inside loop: x = ' || TO_CHAR(x));
x := x + 1;
--IF x < 3 THEN
--CONTINUE;
---END IF;
--DBMS_OUTPUT.PUT_LINE ('Inside loop, after CONTINUE: x = '||TO_CHAR(x));
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('owner_name: '||irec.owner);
---DBMS_OUTPUT.PUT_LINE('---------------------------');
---DBMS_OUTPUT.PUT_LINE(CHR(10));
---DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('sgment_name: '||irec.SEGMENT_NAME);
DBMS_OUTPUT.PUT_LINE('sgment_size(MB): '||irec.MB);
DBMS_OUTPUT.PUT_LINE('========');
EXIT WHEN x = 51;
END LOOP;
end;
/

 

##test 3

DECLARE
x NUMBER := 0;
s_owner varchar2(32);
s_sgement_name varchar2(200);
s_sgement_size varchar2(200);
s_total_size varchar2(200);

CURSOR get_50rows IS SELECT *
FROM (SELECT owner,SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB
FROM DBA_SEGMENTS
GROUP BY owner,SEGMENT_NAME, segment_type
having segment_type like '%TABLE%'
ORDER BY 3 DESC)
WHERE ROWNUM < 50;
BEGIN
--DBMS_OUTPUT.PUT_LINE ('First a basic loop without CURSOR FOR LOOP running fine');

---FOR irec IN get_50rows LOOP -- After CONTINUE statement, control should resume here

OPEN get_50rows;
LOOP
FETCH get_50rows INTO s_owner,s_sgement_name,s_sgement_size;

 

SELECT
(SELECT SUM(S.BYTES/1024/1024) -- The table segment size
FROM DBA_SEGMENTS S
WHERE S.OWNER = s_owner AND
(S.SEGMENT_NAME = s_sgement_name)) +
(SELECT NVL(SUM(S.BYTES/1024/1024), 0) -- The Lob Segment Size
FROM DBA_SEGMENTS S, DBA_LOBS L
WHERE S.OWNER = s_owner AND
(L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = s_sgement_name AND L.OWNER = s_owner)) +
(SELECT SUM(S.BYTES/1024/1024) -- The Lob Index size
FROM DBA_SEGMENTS S, DBA_INDEXES I
WHERE S.OWNER = s_owner AND
(I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = s_sgement_name AND I.OWNER = s_owner))
"TOTAL TABLE SIZE" into s_total_size
FROM DUAL;

--DBMS_OUTPUT.PUT_LINE ('Inside loop: x = ' || TO_CHAR(x));
x := x + 1;
--IF x < 3 THEN
--CONTINUE;
---END IF;
--DBMS_OUTPUT.PUT_LINE ('Inside loop, after CONTINUE: x = '||TO_CHAR(x));
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('owner_name: '||s_owner);
---DBMS_OUTPUT.PUT_LINE('---------------------------');
---DBMS_OUTPUT.PUT_LINE(CHR(10));
---DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('sgment_name: '||s_sgement_name);
DBMS_OUTPUT.PUT_LINE('sgment_size(MB): '||s_sgement_size);
DBMS_OUTPUT.PUT_LINE('sgment_total_size(MB): '||s_total_size);
DBMS_OUTPUT.PUT_LINE('========');
EXIT WHEN x = 51;

END LOOP;
end;
/