Varray type parameter : Function Parameters « Stored Procedure Function « Oracle PL / SQL






Varray type parameter

   
SQL>
SQL> create table emp
  2  ( empno      NUMBER(4)    constraint E_PK primary key
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , sal        NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2)    default 10
 11  ) ;

Table created.

SQL> insert into emp values(1,'Tom','N',   'TRAINER', 13,date '1965-12-17',  800 , NULL,  20);

1 row created.

SQL> insert into emp values(2,'Jack','JAM', 'Tester',6,date '1961-02-20',  1600, 300,   30);

1 row created.

SQL> insert into emp values(3,'Wil','TF' ,  'Tester',6,date '1962-02-22',  1250, 500,   30);

1 row created.

SQL> insert into emp values(4,'Jane','JM',  'Designer', 9,date '1967-04-02',  2975, NULL,  20);

1 row created.

SQL> insert into emp values(5,'Mary','P',  'Tester',6,date '1956-09-28',  1250, 1400,  30);

1 row created.

SQL> insert into emp values(6,'Black','R',   'Designer', 9,date '1963-11-01',  2850, NULL,  30);

1 row created.

SQL> insert into emp values(7,'Chris','AB',  'Designer', 9,date '1965-06-09',  2450, NULL,  10);

1 row created.

SQL> insert into emp values(8,'Smart','SCJ', 'TRAINER', 4,date '1959-11-26',  3000, NULL,  20);

1 row created.

SQL> insert into emp values(9,'Peter','CC',   'Designer',NULL,date '1952-11-17',  5000, NULL,  10);

1 row created.

SQL> insert into emp values(10,'Take','JJ', 'Tester',6,date '1968-09-28',  1500, 0,     30);

1 row created.

SQL> insert into emp values(11,'Ana','AA',  'TRAINER', 8,date '1966-12-30',  1100, NULL,  20);

1 row created.

SQL> insert into emp values(12,'Jane','R',   'Manager',   6,date '1969-12-03',  800 , NULL,  30);

1 row created.

SQL> insert into emp values(13,'Fake','MG',   'TRAINER', 4,date '1959-02-13',  3000, NULL,  20);

1 row created.

SQL> insert into emp values(14,'Mike','TJA','Manager',   7,date '1962-01-23',  1300, NULL,  10);

1 row created.

SQL>
SQL>
SQL>
SQL> create table e
  2  as
  3  select empno, ename, init, mgr, deptno
  4  from   emp;

Table created.

SQL>
SQL>
SQL> create or replace type numberlist_t
  2  as varray(4) of varchar2(20);
  3  /

Type created.

SQL>
SQL> column numlist format a60
SQL>
SQL> alter table e add (numlist numberlist_t);

Table altered.

SQL>
SQL> describe e
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(8)
 INIT                                               VARCHAR2(5)
 MGR                                                NUMBER(4)
 DEPTNO                                             NUMBER(2)
 NUMLIST                                            NUMBERLIST_T

SQL>
SQL> create or replace function ext
  2       (p_varray_in numberlist_t)
  3  return varchar2
  4  is
  5    v_ext varchar2(20);
  6  begin
  7    v_ext := p_varray_in(1);
  8    return v_ext;
  9  end;
 10  /

Function created.

SQL>
SQL>
SQL> drop table e;

Table dropped.

SQL> drop table emp;

Table dropped.

   
    
    
  








Related examples in the same category

1.Pass value to function parameter
2.Boolean value function parameter
3.The FullName Function
4.A stored function with no parameters.
5.Use Column type as the function parameter type
6.Clob type parameter
7.Positional Notation
8.Get circle area
9.Named Notation
10.Pass number value to function
11.Mixed Name and Position Notation Calls