Go Back

PLSQL 10

PL/SQL -> PLSQLBasics

INTRODUCTION TO PL/SQL 9:

PLSQL Using VARRAYS:

Varrays are similar to c or java arrays. ARRAY is collection of similar datatypes.

 
CREATE TABLE collegues
  (
              name VARCHAR2(10),
     collegue_name VARCHAR2(10)
  ); 

Table created.

ALTER TABLE collegues
  ADD CONSTRAINT collegues_pk PRIMARY KEY(name); 

Table altered.

INSERT INTO collegues
     VALUES ('AYAN',
             'RAGHU'); 

1 row created.


INSERT INTO collegues
     VALUES ('AYAN',
             'SHRITHAN'); 
             
INSERT INTO COLLEGUES
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.COLLEGUES_PK) violated 


ALTER TABLE collegues
  DROP CONSTRAINT collegues_pk; 

Table altered.



INSERT INTO collegues
     VALUES ('AYAN',
             'SHRITHAN'); 

1 row created.


INSERT INTO collegues
     VALUES ('AYAN',
             'LAKSHMI') 
SQL> /

1 row created.


INSERT INTO collegues
     VALUES ('AYAN',
             'NANCY') 
SQL> /

1 row created.


INSERT INTO collegues
     VALUES ('BHARATH',
             'NANCY') 
SQL> /

1 row created.


INSERT INTO collegues
     VALUES ('BHARATH',
             'LAKSHMI') 
SQL> /

1 row created.


INSERT INTO collegues
     VALUES ('BHARATH',
             'PREKSHA') 
SQL> /

1 row created.


INSERT INTO collegues
     VALUES ('RAJESH',
             'PREKSHA') 
SQL> /

1 row created.


INSERT INTO collegues
     VALUES ('RAJESH',
             'NANCY') 
SQL> /

1 row created.


INSERT INTO collegues
     VALUES ('RAJESH',
             'TANUJA')
SQL> /

1 row created.

SELECT *
  FROM collegues; 

NAME       COLLEGUE_NAM                                                                                                             
---------- ----------                                                                                                             
AYAN      	RAGHU                                                                                                                    
AYAN      	SHRITHAN                                                                                                                 
AYAN      	LAKSHMI                                                                                                                
AYAN      	NANCY                                                                                                               
BHARATH     NANCY                                                                                                               
BHARATH    	LAKSHMI                                                                                                                
BHARATH     PREKSHA                                                                                                               
RAJESH    	PREKSHA                                                                                                               
RAJESH    	NANCY                                                                                                               
RAJESH    	TANUJA                                                                                                                

10 rows selected.


CREATION OF VARRAY:

 
SQL> CREATE OR replace TYPE collegues_name AS varray(5) OF VARCHAR2(10); 

Type created.
CREATING TABLE USING VARRAY:

CREATION OF VARRAY:

 
CREATE TABLE collegues1
  (
              name VARCHAR2(10),
     collegue_name COLLEGUES_NAME
  ); 

Table created.

ALTER TABLE collegues1
  ADD CONSTRAINT collegues1_pk PRIMARY KEY(name); 

Table altered.

DESCRIBING THE TABLE:

 

SQL> DESC COLLEGUES1
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 NAME                                                                    NOT NULL VARCHAR2(10)
 COLLEGUE_NAME                                                                      COLLEGUES_NAME


SELECT column_name,
       data_type
  FROM user_tab_columns
 WHERE table_name = 'COLLEGUES1' 
SQL> /

COLUMN_NAME                                                                                                                       
------------------------------                                                                                                    
DATA_TYPE                                                                                                                         
----------------------------------------------------------------------------------------------------------                        
NAME                                                                                                                              
VARCHAR2                                                                                                                          
                                                                                                                                  
COLLEGUE_NAME                                                                                                                       
COLLEGUES_NAME                                                                                                                      
                                                                                                                                  

SELECT typecode,
       attributes
  FROM user_types
 WHERE type_name = 'COLLEGUES_NAME' 
SQL> /

TYPECODE                       ATTRIBUTES                                                                                         
------------------------------ ----------                                                                                         
COLLECTION                              0                                                                                         

SELECT coll_type,
       elem_type_owner,
       elem_type_name,
       upper_bound,
       length
  FROM user_coll_types
 WHERE type_name = 'COLLEGUES_NAME'; 

COLL_TYPE                      ELEM_TYPE_OWNER                ELEM_TYPE_NAME                 UPPER_BOUND     LENGTH               
------------------------------ ------------------------------ ------------------------------ ----------- ----------               
VARYING ARRAY                                                 VARCHAR2                                 5         10               

Inserting values in to the table:

 

INSERT INTO collegues1
     VALUES ('AYAN',
             Collegues_name('RAGHU', 'SHRITHAN', 'LAKSHMI', 'NANCY')) 
 /

1 row created.


INSERT INTO collegues1
     VALUES ('BHARATH',
             Collegues_name('PREKSHA', 'LAKSHMI', 'NANCY')) 
 /

1 row created.


INSERT INTO collegues1
     VALUES ('RAJESH',
             Collegues_name('PREKSHA', 'LAKSHMI', 'TANUJA')) 
SQL> /

1 row created.

Selecting values from the table:

 
SELECT *
  FROM collegues1; 

NAME                                                                                                                              
----------                                                                                                                        
COLLEGUE_NAME                                                                                                                       
----------------------------------------------------------------------------------------------------------------------------------
AYAN                                                                                                                             
COLLEGUES_NAME('RAGHU', 'SHRITHAN', 'LAKSHMI', 'NANCY')                                                                              
                                                                                                                                  
BHARATH                                                                                                                            
COLLEGUES_NAME('PREKSHA', 'LAKSHMI', 'NANCY')                                                                                   
                                                                                                                                  
RAJESH                                                                                                                           
COLLEGUES_NAME('PREKSHA', 'LAKSHMI', 'TANUJA')                                                                                    

Selecting values from the table using cursor:

 
                               
DECLARE
    CURSOR c IS
      SELECT *
        FROM collegues1;
BEGIN
    FOR c_rec IN c LOOP
        dbms_output.Put_line('NAME: '
                             ||c_rec.name);

        FOR i IN 1.. c_rec.collegue_name.count LOOP
            dbms_output.Put_line(c_rec.Collegue_name(i));
        END LOOP;
    END LOOP;
END; 
SQL> /

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
SQL> /
NAME: AYAN                                                                                                                       
RAGHU                                                                                                                               
SHRITHAN                                                                                                                            
LAKSHMI                                                                                                                           
NANCY                                                                                                                          
NAME: BHARATH                                                                                                                      
PREKSHA                                                                                                                          
LAKSHMI                                                                                                                           
NANCY                                                                                                                          
NAME: RAJESH                                                                                                                     
PREKSHA                                                                                                                          
LAKSHMI                                                                                                                           
TANUJA                                                                                                                           

PL/SQL procedure successfully completed.


SELECT F.name,
       N.*
  FROM collegues1 F,
       TABLE(f.collegue_name) N 
/

NAME       COLUMN_VAL                                                                                                             
---------- ----------                                                                                                             
AYAN      	RAGHU                                                                                                                    
AYAN      	SHRITHAN                                                                                                                 
AYAN      	LAKSHMI                                                                                                                
AYAN      	NANCY                                                                                                               
BHARATH     PREKSHA                                                                                                               
BHARATH     LAKSHMI                                                                                                                
BHARATH     NANCY                                                                                                               
RAJESH    	PREKSHA                                                                                                               
RAJESH    	LAKSHMI                                                                                                                
RAJESH    	TANUJA                                                                                                                

10 rows selected.

Creating number varray:

 
DECLARE
    TYPE numbers IS varray(20) OF NUMBER(3);
    v_list1 NUMBERS := Numbers(1, 2, 3, 4, 5);
    v_list2 NUMBERS;
BEGIN
    FOR i IN 1..v_list1.count LOOP
        dbms_output.Put_line(V_list1(i));
    END LOOP;

    dbms_output.Put_line('COUNT:'
                         ||v_list1.count);
END; 
SQL> /
1                                                                                                                                 
2                                                                                                                                 
3                                                                                                                                 
4                                                                                                                                 
5                                                                                                                                 
COUNT:5                                                                                                                           

PL/SQL procedure successfully completed.


DECLARE
    TYPE numbers IS varray(5) OF NUMBER(3);
    v_list1 NUMBERS := Numbers(1, 2, 3, 4, 5);
    v_list2 NUMBERS;
BEGIN
    FOR i IN 1..v_list1.count LOOP
        dbms_output.Put_line(V_list1(i));
    END LOOP;

    dbms_output.Put_line('COUNT:'
                         ||v_list1.count);
END;
SQL> /
1                                                                                                                                 
2                                                                                                                                 
3                                                                                                                                 
4                                                                                                                                 
5                                                                                                                                 
COUNT:5                                                                                                                           

PL/SQL procedure successfully completed.


DECLARE
    TYPE numbers IS varray(5) OF NUMBER(3);
    v_list1 NUMBERS := Numbers(1, 2, 3, 4, 5);
BEGIN
    FOR i IN 1..v_list1.count LOOP
        dbms_output.Put_line(V_list1(i));
    END LOOP;

    dbms_output.Put_line('COUNT:'
                         ||v_list1.count);
END; 
/
1                                                                                                                                 
2                                                                                                                                 
3                                                                                                                                 
4                                                                                                                                 
5                                                                                                                                 
COUNT:5                                                                                                                           

PL/SQL procedure successfully completed.


DECLARE
    TYPE numbers IS varray(6) OF NUMBER(3);
    v_list1 NUMBERS := Numbers(1, 2, 3, 4, 5);
BEGIN
    FOR i IN 1..v_list1.count LOOP
        dbms_output.Put_line(V_list1(i));
    END LOOP;

    dbms_output.Put_line('COUNT:'
                         ||v_list1.count);

    v_list1.extend;

    V_list1(6) := 6;

    dbms_output.Put_line(V_list1(6));
END; 
SQL> /
1                                                                                                                                 
2                                                                                                                                 
3                                                                                                                                 
4                                                                                                                                 
5                                                                                                                                 
COUNT:5                                                                                                                           
6                                                                                                                                 

PL/SQL procedure successfully completed.


DECLARE
    TYPE numbers IS varray(6) OF NUMBER(3);
    v_list1 NUMBERS := Numbers(1, 2, 3, 4, 5);
BEGIN
    FOR i IN 1..v_list1.count LOOP
        dbms_output.Put_line(V_list1(i));
    END LOOP;

    dbms_output.Put_line('COUNT:'
                         ||v_list1.count);

    v_list1.extend;

    V_list1(6) := 6;

    v_list1.extend;

    dbms_output.Put_line(V_list1(6));
END; 
SQL> /
1                                                                                                                                 
2                                                                                                                                 
3                                                                                                                                 
4                                                                                                                                 
5                                                                                                                                 
COUNT:5                                                                                                                           
DECLARE
*
ERROR at line 1:
ORA-06532: Subscript outside of limit 
ORA-06512: at line 11 



DECLARE
    TYPE numbers IS varray(6) OF NUMBER(3);
    v_list1 NUMBERS := Numbers(1, 2, 3, 4, 5);
BEGIN
    FOR i IN 1..v_list1.count LOOP
        dbms_output.Put_line(V_list1(i));
    END LOOP;

    dbms_output.Put_line('COUNT:'
                         ||v_list1.count);

    v_list1.extend;

    V_list1(6) := 6;

    dbms_output.Put_line(V_list1(6));

    dbms_output.Put_line('LIMIT:'
                         ||v_list1.count);
END; 
 /
1                                                                                                                                 
2                                                                                                                                 
3                                                                                                                                 
4                                                                                                                                 
5                                                                                                                                 
COUNT:5                                                                                                                           
6                                                                                                                                 
LIMIT:6                                                                                                                           

PL/SQL procedure successfully completed.


DECLARE
    TYPE numbers IS varray(6) OF NUMBER(3);
    v_list1 NUMBERS := Numbers(1, 2, 3, 4, 5);
BEGIN
    FOR i IN 1..v_list1.count LOOP
        dbms_output.Put_line(V_list1(i));
    END LOOP;

    dbms_output.Put_line('COUNT:'
                         ||v_list1.count);

    v_list1.extend;

    V_list1(6) := 6;

    dbms_output.Put_line(V_list1(6));

    dbms_output.Put_line('LIMIT:'
                         ||v_list1.count);

    dbms_output.Put_line('COUNT:'
                         ||v_list1.count);
END; 
SQL> /
1                                                                                                                                 
2                                                                                                                                 
3                                                                                                                                 
4                                                                                                                                 
5                                                                                                                                 
COUNT:5                                                                                                                           
6                                                                                                                                 
LIMIT:6                                                                                                                           
COUNT:6                                                                                                                           

PL/SQL procedure successfully completed.


DECLARE
    TYPE numbers IS varray(6) OF NUMBER(3);
    v_list1 NUMBERS := Numbers(1, 2, 3, 4, 5);
BEGIN
    FOR i IN 1..v_list1.count LOOP
        dbms_output.Put_line(V_list1(i));
    END LOOP;

    dbms_output.Put_line('COUNT:'
                         ||v_list1.count);

    v_list1.extend;

    V_list1(6) := 6;

    dbms_output.Put_line(V_list1(6));

    dbms_output.Put_line('LIMIT:'
                         ||v_list1.count);

    dbms_output.Put_line('COUNT:'
                         ||v_list1.count);

    dbms_output.Put_line('FIRST:'
                         ||v_list1.first);

    dbms_output.Put_line('LAST:'
                         ||v_list1.last);

    dbms_output.Put_line('PRIOR:'
                         ||v_list1.PRIOR(2));

    dbms_output.Put_line('NEXT:'
                         ||v_list1.NEXT(2));
END; 
 19  /
1                                                                                                                                 
2                                                                                                                                 
3                                                                                                                                 
4                                                                                                                                 
5                                                                                                                                 
COUNT:5                                                                                                                           
6                                                                                                                                 
LIMIT:6                                                                                                                           
COUNT:6                                                                                                                           
FIRST:1                                                                                                                           
LAST:6                                                                                                                            
PRIOR:1                                                                                                                           
NEXT:3                                                                                                                            

PL/SQL procedure successfully completed.


DECLARE
    TYPE numbers IS varray(6) OF NUMBER(3);
    v_list1 NUMBERS := Numbers(1, 2, 3, 4, 5);
BEGIN
    FOR i IN 1..v_list1.count LOOP
        dbms_output.Put_line(V_list1(i));
    END LOOP;

    dbms_output.Put_line('COUNT:'
                         ||v_list1.count);

    v_list1.extend;

    V_list1(6) := 6;

    dbms_output.Put_line(V_list1(6));

    dbms_output.Put_line('LIMIT:'
                         ||v_list1.count);

    dbms_output.Put_line('COUNT:'
                         ||v_list1.count);

    dbms_output.Put_line('FIRST:'
                         ||v_list1.first);

    dbms_output.Put_line('LAST:'
                         ||v_list1.last);

    dbms_output.Put_line('PRIOR:'
                         ||v_list1.PRIOR(2));

    dbms_output.Put_line('NEXT:'
                         ||v_list1.NEXT(2));
END; 
SQL> /
1                                                                                                                                 
2                                                                                                                                 
3                                                                                                                                 
4                                                                                                                                 
5                                                                                                                                 
COUNT:5                                                                                                                           
6                                                                                                                                 
LIMIT:6                                                                                                                           
COUNT:6                                                                                                                           
FIRST:1                                                                                                                           
LAST:6                                                                                                                            
PRIOR:1                                                                                                                           
NEXT:3                                                                                                                            

PL/SQL procedure successfully completed.

Go Back

Go Back