Oracle 11g Release 1 (11.1) PL/SQL Collection method
Content
- EXISTS Method
- COUNT Method
- LIMIT Method
- FIRST and LAST methods
- PRIOR and NEXT Methods
- EXTEND Method
- TRIM Method
- DELETE Method
CollectionThe method is a built-in PL/SQL subroutine that can returnCollectionInformation, orCollection.
You can callCollectionMethod. The syntax is shown in:
Figure 1 Collection Method call
Cannot be called in SQL statementsCollectionMethod.
WhenCollectionIf it is null, you can only useEXISTSMethod.COLLECTION_IS_NULLException.
EXISTS Method
IfCollectionCenterNElements existEXISTS (n)ReturnTRUEOtherwise, returnFALSE.EXISTSMethod combinationDELETEMethodCollectionSparse nested tables (sparse nested tables ). PassEXISTSTo avoid referencing an element that does not exist. When a tag value out of the range is passed,EXISTSMethod returnFALSEInstead of generatingSUBSCRIPT_OUTSIDE_LIMITException.
Example 1: Check whether the element exists
DECLARE
TYPE NumList IS TABLE OF INTEGER;
n NumList := NumList(1,3,5,7);
BEGIN
n.DELETE(2); -- Delete the second element
IF n.EXISTS(1) THEN
DBMS_OUTPUT.PUT_LINE('OK, element #1 exists.');
END IF;
IF n.EXISTS(2) = FALSE THEN
DBMS_OUTPUT.PUT_LINE('OK, element #2 was deleted.');
END IF;
IF n.EXISTS(99) = FALSE THEN
DBMS_OUTPUT.PUT_LINE('OK, element #99 does not exist at all.');
END IF;
END;
/
COUNT Method
COUNTReturnCollectionThe current number of elements in. When you do not knowCollectionIt is useful when there are many elements. For example, when you put a column in the obtained table intoNested tableThe number of elements depends on the size of the result set.
ForVarray,COUNTAlways equalLAST. PassEXTENDAndTRIMMethod, you can increase or decreaseVarrayTherefore,COUNTThe value varies depending onLIMITThe value of the method.
ForNested tables,COUNTThe method is usually equalLASTMethod. However, if youNested tableTo delete an elementCOUNTLessLAST. When you organize elements,COUNTThe deleted elements are ignored. UseDELETEMethod will be setCOUNTIs0.
Note:FIRSTMethod andLASTReturns the largest and smallest number of indexes. It is described later.