How to do simple array in PLSQL procedure

Question

how to do something like simple array in plsql? Better to explain on example.

Let's see what I have

PROCEDURE MyProcedure(name varchar2 := '', father varchar2 := '', description varchar2 := '') IS 
        BEGIN
            UPDATE BC_ACTIONTYPEGROUP SET
                ColumnName = name,
                ColumnFather = father,
                ColumnDecription = description
            WHERE
                ColumnName = name;
            IF SQL%ROWCOUNT = 0 THEN
                INSERT INTO TableNames  (ColumnName, ColumnFather, ColumnDescription)
                VALUES (name, description, father);
            END IF;
        END;
/
MyProcedure('John',     '', 'Little John has three brothers');
MyProcedure('George',   'YES',  'George is father of John');

and I need something like this

MyProcedure(name=>'John', description=>'Little John has three brothers');
MyProcedure(name=>'George', father=>'YES',  description=>'George is father of John');

Is it possible? Or what is the simplest way how to use something like this in procedure.

I'm fresh student of IT so thanks for any advice.

The code you posted would work (although a MERGE statement would be a better implementation). So please make clear what you want to do differently. What do you want to do which you think requires an array? — APC
– APC, Commented Aug 8, 2012 at 12:58

Community · Accepted Answer · 2017-05-23 10:24:38Z

1

I am not sure if you are looking for arrays or hash tables (associative arrays).

Arrays are easy:

declare TYPE names IS VARRAY(6) OF VARCHAR2(20) ; name names; name names.extend(1); begin name(1) := 'John Doe'; end;

Reference:
http://docs.oracle.com/cd/B10500_01/appdev.920/a96624/05_colls.htm

In the case of associative arrays you would have to define a type such as:

TYPE my_type IS TABLE OF VARCHAR2(256) INDEX BY VARCHAR2(5); -- Declare actual associative array using new data type. my_array my_type; my_key VARCHAR2(5);

Take a look at this:

http://javaconfessions.com/2008/08/associative-arrays-hashtables-for-plsql.html

http://tylermuth.wordpress.com/2008/02/21/plsql-associative-arrays/

Then follow these steps to pass array to a function or procedure:

How to use pass an array in PL/SQL function

edited May 23, 2017 at 10:24

CommunityBot

11 silver badge

answered Aug 8, 2012 at 12:41

Edmon

4,8924 gold badges34 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to do simple array in PLSQL procedure

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related