How to determine an empty cursor in Oracle

1. Declare the package www.2cto.com 01 createorreplacepackage TAL_TESTis02 03 -- Author: ADMINISTRATOR04 -- Created: 15: 15: 3805 -- Purpose: XXXX test 06 07 TYPE myCursorTypeISREFCURSOR; 08 09 -- Test empty cursor; 11 12endTAL_TEST; 2. Package 01 createorreplacepackage body TAL_TESTis02 03 -- Test empty cursor listen SJ_CD_PERIODS % ROWTYPE; 07 myCursorFirst myCursorType; 08 myCursorSecond m YCursorType; 09 myCursorThird myCursorType; 10 myCursorForth myCursorType; 11BEGIN12 -- use the dataset www.2cto.com 13OPENmyCursorFirstFOR14SELECTA without restrictions in the notfound (failed) database. * FROMSJ_CD_PERIODS AWHEREA. FPERIODID = 1001; 15IF myCursorFirst % databases (the database does not contain a dataset with the FPERIODID of 1001); 17ELSE18dbms_output.put_line (the database contains a database with the FPERIODID of 1001); 19 ENDIF; 20 21 -- use ROWCOUNT (failed) to query the dataset 22 OPENmyCursorSecon with restrictions in the database DFOR23SELECTA. * FROMSJ_CD_PERIODS AWHEREA. FPERIODID = 20112012141202260210001003001002002; 24IF myCursorSecond % ROWCOUNT = Hangzhou (the database does not contain a dataset with the FPERIODID of 20112012141202260210001003001002002); 26ELSE27dbms_output.put_line (the database contains a dataset with the FPERIODID of 20112012141202260210001003001002002; 29 30 -- use the 31OPENmyCursorThirdFOR32SELECTA dataset with restrictions in the FETCH (successful) database. * FROMSJ_CD_PERIODS AWHEREA. FPERIODID = 20112012141202260210001003001002002; 33 FETCHmyCursorThirdINTOrowRecord; 34IF myCursorThird % exist (the database does not contain a dataset with the FPERIODID of 20112012141202260210001003001002002); exist (the database contains a dataset with the FPERIODID of 20112012141202260210001003001002002); 38 ENDIF; 39 40 -- use the 41OPENmyCursorForthFOR42SELECTA dataset without restrictions in the FETCH (successful) database. * FROMSJ_CD_PERIODS AWHEREA. FPERIODID = 1001; 43 FETCHmyCurs OrForthINTOrowRecord; 44IF myCursorForth % records (the database does not contain a dataset with the FPERIODID of 1001); exist (the database contains a dataset with the FPERIODID of 1001); 48 ENDIF; 49END; 50 51endTAL_TEST; 3. The following is a description of the above tests: myCursorFirst: 1 -- use the 2OPENmyCursorFirstFOR3SELECTA dataset in the notfound (failed) database without restrictions. * FROMSJ_CD_PERIODS AWHEREA. FPERIODID = 1001; 4IF myCursorFirst % notfoundTHEN5dbms_output.put_line (the database does not contain a dataset whose FPERIODID is 1001. ); 6ELSE7dbms_output.put_line (a database with the FPERIODID of 1001 exists in the Database); 8 ENDIF; in my database, a dataset with the ID 1001 does not exist. But when debugging the Code: The program runs incorrectly. Note: in fact, the above IF statement has a problem. myCursorFirst % notFound must be FlasemyCursorSecond: 1 -- use the dataset 2OPENmyCursorSecondFOR3SELECTA with restrictions in the ROWCOUNT (failed) database. * FROMSJ_CD_PERIODS AWHEREA. FPERIODID = 20112012141202260210001003001002002; 4IF myCursorSecond % ROWCOUNT = Hangzhou (the database does not contain a dataset with the FPERIODID of 20112012141202260210001003001002002); 6ELSE7dbms_output.put_line (the database contains a dataset with the FPERIODID of 20112012141202260210001003001002002 ); IF; in my database, the preceding labeled dataset exists. But when debugging the Code: The program runs incorrectly. Note: When the cursor does not FETCH. % ROWCOUNT, the attribute is always 0. Therefore, this judgment is useless. Generally, Fetch is performed first. After the Fetch operation is completed, use % NOTFOUND to check whether there is any data in the Cursor. So we have the following two tests. MyCursorThird1 -- use the 2OPENmyCursorThirdFOR3SELECTA dataset with restrictions in the FETCH (successful) database. * FROMSJ_CD_PERIODS AWHEREA. FPERIODID = 20112012141202260210001003001002002; 4 FETCHmyCursorThirdINTOrowRecord; 5IF myCursorThird % exist (the database does not contain a dataset with the FPERIODID of 20112012141202260210001003001002002); exist (the database contains a dataset with the FPERIODID of 20112012141202260210001003001002002; my database contains the preceding dataset. Result: The program runs successfully. MyCursorForth: 1 -- use the 2OPENmyCursorForthFOR3SELECTA dataset in the FETCH (successful) database without restrictions. * FROMSJ_CD_PERIODS AWHEREA. FPERIODID = 1001; 4 fetchmycursorforth1_rowrecord; 5IF myCursorForth % exist (the database does not contain a dataset with the FPERIODID of 1001); exist (the database contains a dataset with the FPERIODID of 1001); 9 ENDIF; no 1001 dataset exists in my database. The program runs correctly. Summary: during my discussion with Xi Jinping last night, I thought Oracle provided a function to directly judge whether the cursor is null. I searched the internet today and it seems that I didn't. For cursor, when it is only Open and does not Fetch, Cursor will not know how much data it actually has. Of course, you can also select count (*)... you are welcome to point out the error. Reference post: http://bbs.csdn.net/topics/370107459