首先人工手动添加三条数据,然后再执行查询操作,导致查询结果报错,而且每次都是添加了三条或者三条以上时报错,两条或者一条的情况则正常,
具体代码如下: 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; ·············/
具体代码如下: 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; ·············/
解决方案 »
- 怎样去oracle表里字段的下划线 请比我高的高手指教
- [求救]where instr(:column,:content)>0 给定不同列查询相应记录
- 难度较高的树型结构的陈列方式
- ORACLE 多表查询
- oracle 9i (9.0.1.1.0)通用问题
- 在PLSQL中执行Oracle存储过程执行完毕后,等近10分钟才能看到更新数据,什么情况?
- 雪地里跪求关于oracle方面的好书
- sql语言中where子句查询怎么样利用日期(oracle9i)相比较?
- oracle在查询时没有结合索引查询,查出数据居然是乱的,请给个结合索引查询的语句
- 请各位好手帮忙!是oracle的bug,还是我的存储过程用法错误??!!
- 高手请指教,游标的问题
- 求助!
客户端连接到数据库后,用Object Browser手动往表中添加3条以上数据。然后执行查询操作,把查询结果放到游标中,再把游标中的数据一条一条地赋到一维自定义表中(定义:TYPE typUserId IS TABLE OF m_user.user_id%TYPE
INDEX BY BINARY_INTEGER;
)
假设原表中有100条数据,当赋值到第103条时,数据库报数组溢出错误。但是INDEX BY BINARY_INTEGER定义的变量的下限应该是动态的,怎么会溢出呢,问题出在哪里呢?请各位指教。