10.5: COLLECTION METHODS
G) PRIOR:- This method returns the next or succeeding index number of n th element
Example:
a := subs.NEXT(a);
H) EXTEND:- This method is used to increase the size of nested table or varray. It either appends one null element to a collection, n null elements or n copies of ith element of a collection.
Example :
subs.EXTEND(5,1); // It appends 5 copies of element 1.
I) TRIM:- this method is used to decrease the size of collection by removing one element from end of collection or n
elements.
Example:
subs.TRIM(3);
J) DELETE:- This method deletes the collection elements. It either deletes all elements, nthelement from an array, all elements in the range m, n.
Example:
subs.DELETE(2);
Example on collection methods:
1 DECLARE
2 TYPE my_office IS TABLE OF VARCHAR2 (100);
3
4 office_staff names_t := names_t ( );
5 clerk names_t := names_t ( );
6 manager names_t := names_t ( );
7 BEGIN
8 office_staff.EXTEND (4);
9 office_staff (1) := ‘Takshak’;
10 office_staff (2) := ‘Viraj’;
11 office_staff (3) := ‘Sonal’;
12 office_staff (4) := ‘pooja’;
13
14 clerk.EXTEND;
15 clerk(clerk.LAST) := ‘Sonal’;
16 clerk.EXTEND;
17 clerk(clerk.LAST) := ‘Viraj’;
18
19 manager: = office_staff MULTISET EXCEPT clerk;
20
21 FOR l_row IN 1 .. manager.COUNT 22 LOOP
23 DBMS_OUTPUT.put_line (manager (l_row));
24 END LOOP;
25 END;
10.6 HOW TO USE INDEX BY TABLE OF RECORDS?
Example :Declare an index-by table variable to hold the employee records in cursor
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL> INSERT INTO EMP VALUES (736, 'Sonali', 'CLERK', 790, TO_DATE('17-DEC-2000', 'DD-MON-YYYY'), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (749, 'Tushar', 'SALESMAN', 769, TO_DATE('20-FEB-2001', 'DD-MON-YYYY'), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (752, 'Priya', 'SALESMAN', 769, TO_DATE('22-FEB-2000', 'DD-MON-YYYY'), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (756, 'Tanu', 'MANAGER', 783, TO_DATE('2-APR-2001', 'DD-MON-YYYY'), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (765, 'Sara', 'SALESMAN', 769,TO_DATE('28-SEP-2001', 'DD-MON-YYYY'), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (769, 'Sana', 'MANAGER', 783,TO_DATE('1-MAY-2001', 'DD-MON-YYYY'), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (778, 'Tejas', 'MANAGER', 783,TO_DATE('9-JUN-2001','DD-MON-YYYY'), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (778, 'Akhi', 'ANALYST', 756,TO_DATE('09-DEC-2002','DD-MON-YYYY'), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (783, 'Amol', 'PRESIDENT', NULL,TO_DATE('17-NOV-2001', 'DD-MON-YYYY'), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (784, 'Sandip', 'SALESMAN', 769,TO_DATE('8-SEP-2001', 'DD-MON-YYYY'), 1500, 0, 30);
1 row created.
SQL> DECLARE
2 CURSOR all_emps IS
3 SELECT *
4 FROM emp
5 ORDER BY ename;
6
7 TYPE emp_table IS TABLE OF emp%ROWTYPE
8 INDEX BY BINARY_INTEGER;
9 emps emp_table;
10 emps_max BINARY_INTEGER;
11 BEGIN
12 emps_max := 0;
13 FOR emp IN all_emps LOOP 14 emps_max := emps_max + 1;
15 emps(emps_max).empno := emp.empno;
16 emps(emps_max).ename := emp.ename;
17 emps(emps_max).JOB := emp.JOB;
18 emps(emps_max).HIREDATE := emp.HIREDATE;
19 emps(emps_max).DEPTNO := emp.DEPTNO;
20 END LOOP;
21 END;
22 /
PL/SQL procedure successfully completed.
10.7 INTRODUCTION TO PL/SQL RECORD:
The PLSQL record is a group of related data items stored in fields, each with its own name and datatype. The record is special type of PLSQL variable that can hold a table row, or some columns from a table row. The fields of record correspond to table columns.
The %ROWTYPE attribute lets us declare a record that represents a row in a database table, without listing all the columns.
Our code keeps working even after columns are added to the table.
If we want to represent a subset of columns in a table, or columns from different tables, we can define a view or declare a cursor to select the right columns and do any necessary joins, and then apply%ROWTYPEto the view or cursor.
The important thing while using %TYPE or %ROWTYPE is the table name and the column name must already exist in database.
A) %TYPE and %ROWTYPE:
The %TYPE and %ROWTYPE attributes are used to define variables in PL/SQL , as it is defined within the database. If the datatype or precision of a column changes, the program automatically picks up the new definition from the database without having to make any code changes.
The %TYPE and %ROWTYPE constructs the variable, provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.
%TYPE
The %TYPE attribute is used to declare a field with the same type as that of a specified table's column. % TYPE provides the data type of a variable or a database column to that variable. This is very useful because we do not have to declare the parameter data
type and also, when the column data type changes in the table, we do not come to procedure and change the data type.
Syntax :
4 SELECT name INTO v_name FROM student WHERE ROWNUM = 1;
5 DBMS_OUTPUT.PUT_LINE('Name = ' || v_name);
6 END;
7 /
Name = abc
PL/SQL procedure successfully completed.
%ROWTYPE
The %ROWTYPE attribute is used to declare a record with the same types as found in the specified database table, view or cursor. % ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor. We normally use %ROWTYPE to retrieve the record which contains all of the columns from a specified database table.
Syntax :
4 v_stud.rollno := 6;
5 v_stud.name := 'xyz';
6 v_stud.mark1 := 35;
7 v_stud.mark2 := 56;
8 v_stud.mark3 := 67;
9 DBMS_OUTPUT.PUT_LINE('rollno : '||v_stud.rollno);
10 DBMS_OUTPUT.PUT_LINE('name : '||v_stud.name);
11 DBMS_OUTPUT.PUT_LINE('mark1 : '||v_stud.mark1);
12 DBMS_OUTPUT.PUT_LINE( 'mark2 : '||v_stud.mark2);
13 DBMS_OUTPUT.PUT_LINE( 'mark3 : '||v_stud.mark3);
14 END;
15 / rollno : 6 name : xyz mark1 : 35
mark2 : 56 mark3 : 67
PL/SQL procedure successfully completed.
C) Using of %type :