PACKAGE BODY PKG1
Function list:
- FCN_SEQ1_GET_NEXT
Procedure list:
- PRC_2
- PRC_REF_CUR_TAB2
Cursors list:
- CUR_GET_VIEW1
- CUR_SEQ1_NEXT
CREATE OR REPLACE PACKAGE BODY pkg1 IS
user_defined_exc EXCEPTION;
CURSOR cur_get_view1 IS /*=>*/
SELECT * FROM view1;
--- Returns the next value of SEQ1
/* FCN_SEQ1_GET_NEXT: ref_list, ref_tree, fcall_tree */
FUNCTION fcn_seq1_get_next RETURN NUMBER
IS
CURSOR cur_seq1_next IS /*=>*/
SELECT seq1.NEXTVAL
FROM dual;
lv_next_val NUMBER;
BEGIN
OPEN cur_seq1_next;
FETCH cur_seq1_next INTO lv_next_val;
CLOSE cur_seq1_next;
RETURN lv_next_val;
END;
/* ***************************************************
This procedure returns a reference cursor pointing
to a SELECT statement.
*************************************************** */
/* PRC_REF_CUR_TAB2: ref_list, ref_tree, fcall_tree */
PROCEDURE prc_ref_cur_tab2(iov_ref IN OUT type1)
IS
lv_max_val NUMBER;
BEGIN
lv_max_val := pkg2.fcn_big_max_val;
OPEN iov_ref FOR
SELECT *
FROM tab2
WHERE f1<=lv_max_val;
END;
/* ***************************************************
This procedure is a sample of how reference cursors
can be used.
*************************************************** */
/* PRC_2: ref_list, ref_tree, fcall_tree */
PROCEDURE prc_2
IS
lv_cur typ1;
lv_rec tab2%ROWTYPE;
lv_next_val NUMBER;
BEGIN
prc_ref_cur_tab2(lv_cur);
LOOP
FETCH lv_cur INTO lv_rec;
EXIT WHEN lv_cur%NOTFOUND;
IF lv_rec.f1=pkg2.fcn_max_val THEN
BEGIN
UPDATE tab2
SET f4='This is maximum value'
WHERE f1=lv_rec.f1;
EXCEPTION
WHEN OTHERS THEN
/* ********************************************
this is an artificial example to demonstrate
as many features of the PLSQL2HTML converter
as possible
********************************************* */
RAISE user_defined_exc;
END;
END IF;
END LOOP;
CLOSE lv_cur;
FOR lv_rec1 IN cur_get_view1 LOOP
--- do some processing of the rows to be fetched
END LOOP;
END;
END;
/