PL/SQL

Cursor Attributes

There are five cursor attributes:
%isopen

%found

%notfound

%rowcount

%bulk_rowcount

------------------------------------------------------------------------------
%isopen
With %isopen it is possible to test whether a cursor was opened:

declare 
      cursor cur_emp is
        SELECT NAME
        FROM   EMP;       
      l_name
begin
      open cur_emp;
      fetch cur_emp  into  l_name;
      if cur_emp%isopen
      then
            log("Cursor is Open")
      end if;
      close cur;
end;

If there are records in EMP table then the message "Cursor is Open" will be logged at the terminal.

------------------------------------------------------------------------------
%found returns true when the last fetch operation on the cursor fetched a row.
%notfound returns true when the last fetch operation on the cursor did not fetch a row.

declare 
        cursor cur_emp is
        SELECT NAME
        FROM   EMP;
begin
       open cur_emp;
       Loop
       if cur_emp%found
       then
              log("Record returned");        
       elsif cur_emp%notfound
              log("No records");
       exit;          
       end if;
       close cur;
end;

If there are 5 records in EMP TABLE then you will get "Record returned" message five times and then
loop will exit logging the message "No records".
%NOTFOUND is mostly used to exit the LOOP for cursor after the last record has been fetched/processed.

---------------------------------------------------
%rowcount returns the number of rows that have been fetched so far

example;
declare 
        cursor cur_emp is
        SELECT NAME
        FROM   EMP;       
begin
       open cur_emp;
       loop
       if cur%found then     
                    dbms_output.put_line(cur%rowcount);
      else
                    exit;
      end if;
      end loop;
end;

%bulk_rowcount is similar to %rowcount, but is used in bulk collects.

Count a specific character occurence in a String

In PLSQL there are functions like INSTR, SUBSTR, REPLACE, but
to Count number of occurrences of a character in the string there is no standard function.

Here is a simple way, In the example below I am trying to find out the number of
occurrence of '|' in the string

select length('san|tom|elp|ert|') -
length(replace('san|tom|elp|ert|','|')) count from dual;

PLSQL Collections

A collection in PLSQL helps to achieve the Array kind of Programming. Its an ordered group of elements, all of the same type. In a collection an element has a unique subscript that determines its position in the collection.

PLSQL has 3 types of collections
    Index-by tables
    Varrays
    Nested Tables

Declaration: 

Nested tables
TYPE type_name IS TABLE OF element_type ;
e.g.
TYPE Books IS TABLE OF VARCHAR2(60);

Varrays
TYPE type_name IS VARRAY(size_limit) OF element_type;
e.g.
TYPE Books IS VARRAY(100) OF VARCHAR2(60);

Index-by tables
TYPE type_name IS TABLE OF element_type
   INDEX BY BINARY_INTEGER ;
  
TYPE Books IS TABLE OF VARCHAR2(60)
   INDEX BY VARCHAR2(60) ;
  
Varrays size is fixed at the time of declaration, however the size of Nested tables and Index-by tables is dynamic.


Initialization:
Nested Tables
Declare
    TYPE Books IS TABLE OF VARCHAR2(60);   
    c_book  BOOKS;

begin
     c_book := Books('Book1', 'Book2');
     
end;

Varrays
Declare
    TYPE Books IS varray(3) OF VARCHAR2(60);   
    c_book  BOOKS;

begin
     c_book := Books('Book1', 'Book2');
     
end;

Index by Table
Declare
    TYPE Books IS table OF VARCHAR2(60)
    inde by VARCHAR2(60);   
    c_book  BOOKS;

begin
     c_book := Books('Book1', 'Book2');
     
end;

Using collections in PLSQL.
The best way to implement collections is to use them in For Loops or Bulk collects.
FORALL and BULK COLLECTS can be used for implementing collections in better way

BULK COLLECT tell the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these keywords in the SELECT INTO, FETCH INTO, and RETURNING INTO clauses. Here is the syntax:

... BULK COLLECT INTO collection_name[, collection_name] ...

e.g.
DECLARE
   TYPE empntab IS TABLE OF emp.empno%TYPE;
   TYPE enameTab IS TABLE OF emp.ename%TYPE;
   empnum empntab;  -- no need to initialize
   enames enameTab;
BEGIN
   SELECT empno, ename BULK COLLECT INTO empnum, enames FROM emp;
   ...
END;

FORALL
Its used along with collections to perform SQL operations such as Insert, Updates and Deletes.
We can say its spec ail loop to perform Mass operations, a shortcut to implement FOR Loops

e.g.
DECLARE
   TYPE BOOKS IS VARRAY(10) OF VARCHAR2(60);
   var_book BOOKS := BOOKS('Book1','Book2','Book3','Book4','Book5');
BEGIN
   FORALL i IN 1..7  -- bulk-bind only part of varray
      UPDATE BOOK_TAB SET CODE = 1111+1 WHERE NAME = var_book(i);
END;

Other types
RECORD:
TYPE type_name IS RECORD (colname type1, colname2 type2);

e.g.
DECLARE
   TYPE EMPREC IS RECORD (
      EMPNUM    VARCHAR2(60),
      NAME      VARCHAR2(120));
     
      EMP1 EMPREC;
BEGIN
   ...
END;

Collection Methods:

A variety of methods/Functions exist for collections, these can be used to make the implementation
of collection more effective

    EXISTS(n) - Returns TRUE if the specified element exists.

    COUNT - Returns the number of elements in the collection.

    LIMIT - Returns the maximum number of elements for a VARRAY, or NULL for nested tables.

    FIRST - Returns the index of the first element in the collection.

    LAST - Returns the index of the last element in the collection.

    PRIOR(n) - Returns the index of the element prior to the specified element.

    NEXT(n) - Returns the index of the next element after the specified element.

    EXTEND - Appends a single NULL element to the collection.

    EXTEND(n) - Appends n NULL elements to the collection.

    EXTEND(n1,n2) - Appends n1 copies of the n2th element to the collection.

    TRIM - Removes a single element from the end of the collection.

    TRIM(n) - Removes n elements from the end of the collection.

    DELETE - Removes all elements from the collection.

    DELETE(n) - Removes element n from the collection.

    DELETE(n1,n2) - Removes all elements from n1 to n2 from the collection.