我有一个存储过程，它带来了特定用户创建的所有数据。因此，如果一个名为A的用户创建了100条记录，并且当我运行此过程时，它应该会带来100条记录。
目前，它并没有带来所有这100张唱片，其中一些已经不见了，我不知道为什么会这样。
下面是我的存储过程。
create or replace PROCEDURE CHANGEREQUESTS_CREATED_GETLIST
(
P_USERNAME in CHANGEREQUESTS.CREATEDBY%type
, P_JOBID IN CLOB--VARCHAR2
, P_PAGENO IN NUMBER
, P_PAGESIZE IN NUMBER
, TOTALCOUNT OUT NUMBER
, OUTPUTTABLE OUT SYS_REFCURSOR
) AS
START_RECORD_NO NUMBER := 1;
END_RECORD_NO NUMBER := 10;
BEGIN
IF P_PAGENO IS NOT NULL AND P_PAGESIZE IS NOT NULL THEN
START_RECORD_NO := ((P_PAGENO -1) * P_PAGESIZE) +1;
END_RECORD_NO := P_PAGESIZE * P_PAGENO;
END IF;
--open TOTALCOUNT for
SELECT COUNT(*) INTO TOTALCOUNT
FROM
CHANGEREQUESTS CR
where
EXISTS ( SELECT * FROM THE ( SELECT CAST( STR2TBL( P_JOBID ) AS MYTABLETYPE ) FROM DUAL ) TT WHERE TT.COLUMN_VALUE = CR.JOBID )
--CR.JOBID in (select REGEXP_SUBSTR(P_JOBID,'[^,]+', 1, level) from DUAL connect by REGEXP_SUBSTR(P_JOBID, '[^,]+', 1, level) is not null)
and CREATEDBY = P_USERNAME
and JOBID > 0;
OPEN OUTPUTTABLE FOR
select
CHANGEREQUESTID
,DESCRIPTION
,CHANGEREQUESTNUMBER
,STATENAME
,CITYNAME
,CATEGORY
,CHANGETYPE
,STATUSID
,CREATEDBY
,OPENCLOSED
,JOBID
,CREATEDON
,LASTMODIFIEDON
from
( select
ROW_NUMBER() over ( order by CR.CHANGEREQUESTID DESC) ROWNO
,CR.CHANGEREQUESTID
,CR.DESCRIPTION
,CR.CHANGEREQUESTNUMBER
,CR.STATENAME
,CR.CITYNAME
,CC.CATEGORY
,CT.CHANGETYPE
,CR.STATUSID
,CR.CREATEDBY
,'' as OPENCLOSED
,CR.JOBID
,CR.CREATEDON
,CR.LASTMODIFIEDON
FROM
CHANGEREQUESTS CR
inner join
CHANGETYPES CT
on CR.CHANGETYPEID = CT.CHANGETYPEID
inner join
CHANGECATEGORIES CC
ON CC.CHANGECATEGORYID = CT.CHANGECATEGORYID
where
EXISTS ( SELECT * FROM THE ( SELECT CAST( STR2TBL( P_JOBID ) AS MYTABLETYPE ) FROM DUAL ) TT WHERE TT.COLUMN_VALUE = CR.JOBID )
--CR.JOBID in (select REGEXP_SUBSTR(P_JOBID,'[^,]+', 1, level) from DUAL connect by REGEXP_SUBSTR(P_JOBID, '[^,]+', 1, level) is not null)
and CREATEDBY = P_USERNAME
and JOBID > 0
--ORDER BY CR.CHANGEREQUESTID DESC
)
WHERE ROWNO >= START_RECORD_NO AND ROWNO <= END_RECORD_NO;
END CHANGEREQUESTS_CREATED_GETLIST;
此外，表格定义和要求的详细信息在下方
`CHANGEREQUESTS`
Name Null Type
--------------------------- -------- --------------
CHANGEREQUESTID NOT NULL NUMBER(10)
CHANGEREQUESTNUMBER NOT NULL VARCHAR2(50)
CHANGETYPEID NOT NULL NUMBER(10)
NETWORKTYPE VARCHAR2(50)
STATENAME VARCHAR2(50)
CITYNAME VARCHAR2(50)
DESCRIPTION VARCHAR2(100)
REASON VARCHAR2(250)
REMARK VARCHAR2(250)
CREATEDBY NVARCHAR2(100)
CREATEDON DATE
LASTMODIFIEDBY NVARCHAR2(100)
LASTMODIFIEDON DATE
JOBID NUMBER(10)
GROUPID NUMBER(10)
STATUSID NUMBER
ISCANCELLED NOT NULL NUMBER(1)
APPLICATIONID NUMBER(10)
APPCHANGETYPE VARCHAR2(50)
CHANGEDETAILID NUMBER(10)
REQSUBMITTEDON DATE
PRIORITY VARCHAR2(50)
MODELING_CHANGE_TYPE_IDS VARCHAR2(200)
MODELING_CHANGE_DETAILS_IDS VARCHAR2(200)
DB_CHANGE_DETAIL_ID NUMBER(10)
BAND VARCHAR2(2000)
`CHANGETYPES`
Name Null Type
---------------- -------- -------------
CHANGETYPEID NOT NULL NUMBER(10)
CHANGETYPE NOT NULL VARCHAR2(250)
CHANGECATEGORYID NUMBER(10)
ISACTIVE NOT NULL NUMBER(1)
`CHANGECATEGORIES`
Name Null Type
---------------- -------- -------------
CHANGECATEGORYID NOT NULL NUMBER(10)
CATEGORY NOT NULL VARCHAR2(50)
ISACTIVE NOT NULL NUMBER(1)
更新
所以基本上我的Oracle版本是：-18.3
此外，
上述过程由3个表组成，其中主列为CHANGETYPEID、CHANGECATEGORYID。它们已连接以带来数据。
所以我的主表是CHANGEREQUESTS，其中的记录是从连接其他两个表的ID中获取的。
例如：-
CREATE TABLE CHANGEREQUESTS ( CHANGEREQUESTID, CHANGETYPEID, JOBID ) AS
SELECT 7835, 4, 42234 FROM DUAL UNION ALL
SELECT 7834, 22, 42233 FROM DUAL UNION ALL
SELECT 7833, 8, 42242 FROM DUAL;
CREATE TABLE CHANGETYPES (CHANGETYPEID, CHANGETYPE, CHANGECATEGORYID, ISACTIVE) AS
SELECT 8, 'Change in media type (OFC/MW)', 1, 1 FROM DUAL UNION ALL
SELECT 22, 'RF site deletion', 1, 1 FROM DUAL;
CREATE TABLE CHANGECATEGORIES ( CHANGECATEGORYID, CATEGORY, ISACTIVE ) AS
SELECT 3, 'OSP Engineering', 1 FROM DUAL UNION ALL
SELECT 2, 'Wireline', 1 FROM DUAL UNION ALL
SELECT 1, 'Wireless', 1 FROM DUAL;
您可以通过以下方式大大简化您的过程：
- 测试
jobid是否是p_jobid参数的子串；以及 - 使用CCD_ 7语法
create or replace PROCEDURE CHANGEREQUESTS_CREATED_GETLIST
(
P_USERNAME IN CHANGEREQUESTS.CREATEDBY%type
, P_JOBID IN CLOB--VARCHAR2
, P_PAGENO IN NUMBER
, P_PAGESIZE IN NUMBER
, TOTALCOUNT OUT NUMBER
, OUTPUTTABLE OUT SYS_REFCURSOR
)
AS
v_size NUMBER := COALESCE( p_pagesize, 10 );
v_start NUMBER := COALESCE( p_pageno - 1, 0 ) * v_size;
BEGIN
SELECT COUNT(*) INTO TOTALCOUNT
FROM CHANGEREQUESTS CR
WHERE ',' || p_jobid || ',' LIKE '%,' || jobid || ',%'
AND CREATEDBY = P_USERNAME
AND JOBID > 0;
OPEN OUTPUTTABLE FOR
SELECT CR.CHANGEREQUESTID
,CT.CHANGETYPE
,CR.JOBID
FROM CHANGEREQUESTS CR
INNER JOIN CHANGETYPES CT
ON ( CR.CHANGETYPEID = CT.CHANGETYPEID )
INNER JOIN CHANGECATEGORIES CC
ON ( CC.CHANGECATEGORYID = CT.CHANGECATEGORYID )
WHERE ',' || p_jobid || ',' LIKE '%,' || jobid || ',%'
AND CREATEDBY = P_USERNAME
AND JOBID > 0
ORDER BY CR.CHANGEREQUESTID DESC
OFFSET v_start ROWS
FETCH NEXT v_size ROWS ONLY;
END CHANGEREQUESTS_CREATED_GETLIST;
/
对于样本数据：
CREATE TABLE CHANGEREQUESTS ( CHANGEREQUESTID, CHANGETYPEID, JOBID, CREATEDBY ) AS
SELECT 7835, 4, 42234, 'user1' FROM DUAL UNION ALL
SELECT 7834, 22, 42233, 'user1' FROM DUAL UNION ALL
SELECT 7833, 8, 42242, 'user1' FROM DUAL UNION ALL
SELECT LEVEL,
DECODE( MOD( LEVEL, 3), 0, 4, 1, 22, 8 ),
99999,
'user1'
FROM DUAL
CONNECT BY LEVEL <= 40;
CREATE TABLE CHANGETYPES (CHANGETYPEID, CHANGETYPE, CHANGECATEGORYID, ISACTIVE) AS
SELECT 4, 'Random Type', 1, 1 FROM DUAL UNION ALL
SELECT 8, 'Change in media type (OFC/MW)', 1, 1 FROM DUAL UNION ALL
SELECT 22, 'RF site deletion', 1, 1 FROM DUAL;
CREATE TABLE CHANGECATEGORIES ( CHANGECATEGORYID, CATEGORY, ISACTIVE ) AS
SELECT 3, 'OSP Engineering', 1 FROM DUAL UNION ALL
SELECT 2, 'Wireline', 1 FROM DUAL UNION ALL
SELECT 1, 'Wireless', 1 FROM DUAL;
然后：
DECLARE
v_pgsz NUMBER := 20;
BEGIN
FOR v_pgno IN 1 .. 3 LOOP
DECLARE
v_cur SYS_REFCURSOR;
v_size NUMBER;
v_crid CHANGEREQUESTS.CHANGEREQUESTID%TYPE;
v_ctyp CHANGETYPES.CHANGETYPE%TYPE;
v_jid CHANGEREQUESTS.JOBID%TYPE;
BEGIN
CHANGEREQUESTS_CREATED_GETLIST( 'user1', '42234,99999', v_pgno, v_pgsz, v_size, v_cur );
DBMS_OUTPUT.PUT_LINE( 'Page:' || v_pgno || ' (Total: ' || v_size || ' rows)' );
LOOP
FETCH v_cur INTO v_crid, v_ctyp, v_jid;
EXIT WHEN v_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(
LPAD( v_crid, 4, ' ' ) || ' | ' ||
RPAD( v_ctyp, 30, ' ' ) || ' | ' ||
LPAD( v_jid, 4, ' ' )
);
END LOOP;
CLOSE v_cur;
END;
END LOOP;
END;
/
输出：
Page:1 (Total: 41 rows)
7835 | Random Type | 4223
40 | RF site deletion | 9999
39 | Random Type | 9999
38 | Change in media type (OFC/MW) | 9999
37 | RF site deletion | 9999
36 | Random Type | 9999
35 | Change in media type (OFC/MW) | 9999
34 | RF site deletion | 9999
33 | Random Type | 9999
32 | Change in media type (OFC/MW) | 9999
31 | RF site deletion | 9999
30 | Random Type | 9999
29 | Change in media type (OFC/MW) | 9999
28 | RF site deletion | 9999
27 | Random Type | 9999
26 | Change in media type (OFC/MW) | 9999
25 | RF site deletion | 9999
24 | Random Type | 9999
23 | Change in media type (OFC/MW) | 9999
22 | RF site deletion | 9999
Page:2 (Total: 41 rows)
21 | Random Type | 9999
20 | Change in media type (OFC/MW) | 9999
19 | RF site deletion | 9999
18 | Random Type | 9999
17 | Change in media type (OFC/MW) | 9999
16 | RF site deletion | 9999
15 | Random Type | 9999
14 | Change in media type (OFC/MW) | 9999
13 | RF site deletion | 9999
12 | Random Type | 9999
11 | Change in media type (OFC/MW) | 9999
10 | RF site deletion | 9999
9 | Random Type | 9999
8 | Change in media type (OFC/MW) | 9999
7 | RF site deletion | 9999
6 | Random Type | 9999
5 | Change in media type (OFC/MW) | 9999
4 | RF site deletion | 9999
3 | Random Type | 9999
2 | Change in media type (OFC/MW) | 9999
Page:3 (Total: 41 rows)
1 | RF site deletion | 9999
db<gt；小提琴这里