Oracle 11g Release 1 (11.1) PL/SQL Collection method

Source: Internet
Author: User

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.

  • 1
  • 2
  • 3
  • 4
  • 5
  • Next Page

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.