oracle金牌认证都没解决的问题哦~!!!!!
首先人工手动添加三条数据,然后再执行查询操作,导致查询结果报错,而且每次都是添加了三条或者三条以上时报错,两条或者一条的情况则正常,
具体代码如下:
CREATE OR REPLACE PACKAGE aaa
AS
TYPE typUserId IS TABLE OF m_user.user_id%TYPE
INDEX BY BINARY_INTEGER;
TYPE typUserNm IS TABLE OF m_user.user_nm%TYPE
INDEX BY BINARY_INTEGER;
TYPE typPostId IS TABLE OF m_post.post_id%TYPE
INDEX BY BINARY_INTEGER;
TYPE typPostNm IS TABLE OF m_post.post_nm%TYPE
INDEX BY BINARY_INTEGER;
·············
END;
CREATE OR REPLACE PACKAGE BODY aaa
AS
·············
FUNCTION FN_SEL_DATA(
iNumCount IN NUMBER,
iTypPostId IN typPostId,
iTypUnGetUserId IN typUserId,
iNumRecCnt OUT NUMBER,
iTypUserId OUT typUserId,
iTypUserNm OUT typUserNm,
iTypPostNm OUT typPostNm,
oVchrErrMsg OUT VARCHAR2 エラーメッセージ
) RETURN NUMBER
IS
カーソル
CURSOR CurUser
IS
SELECT
m_user.user_id,
m_user.user_nm,
m_user.post_id,
m_post.post_nm
FROM
m_user, m_post;
wkVchrUserId m_user.user_id%TYPE;
wkVchrUserNm m_user.user_nm%TYPE;
wkChrPostId m_user.post_id%TYPE;
wkVchrPostNm m_post.post_nm%TYPE;
wkNumIndex NUMBER := 0;
BEGIN
-- エラーメッセージ
oVchrErrMsg := 'FN_SEL_DATA';
--
iNumRecCnt := 0;
--
wkNumCount := iNumCount;
wkTypPostId := iTypPostId;
-- カーソルオープン
OPEN CurUser;
-- カーソルフェッチ
FETCH CurUser INTO wkVchrUserId,
wkVchrUserNm,
wkChrPostId,
wkVchrPostNm;
WHILE CurUser%NOTFOUND = FALSE LOOP
-- チェック
IF FN_CHECK_POST_ID (wkChrPostId) = TRUE THEN
--
wkNumIndex := wkNumIndex + 1 ;
iTypUserId(wkNumIndex) := wkVchrUserId;
iTypUserNm(wkNumIndex) := wkVchrUserNm;
iTypPostNm(wkNumIndex) := wkVchrPostNm;
END IF;
-- カーソルフェッチ
FETCH CurUser INTO wkVchrUserId,
wkVchrUserNm,
wkChrPostId,
wkVchrPostNm;
END LOOP;
·············
/