CODEWITHSUNDEEP
mysqlsqlplsql
Gowtham Ramamoorthy
Gowtham Ramamoorthy

Reputation: 111

IS it possible to declare a array variable inside a stored procedure of SQL?

I tried with the below code but its throwing an error.

DECLARE TYPE INPUTVALUES IS VARRAY(5) OF VARCHAR(10);

Is ther a way to declare array variables inside the stored procedure.

Upvotes: 3

Views: 8118

Answers (2)

brenners1302
brenners1302

Reputation: 1478

Its possible. Your code should be written like this

TYPE input_values IS TABLE OF VARCHAR2  -- Associative array type
INDEX BY PLS_INTEGER;                  --  indexed by string

v_array  input_values;              -- Associative array variable
i  VARCHAR2(64);                    -- Scalar variable

Take Note:

  • If you want to declare an array variable inside a stored procedure , you dont need to write DECLARE anymore inside your block.

  • If you are using ORACLE RDBMS, its commonly called ASSOCIATIVE ARRAYS. You can read more here

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172438

As far as I know relational databases don't have arrays. They usually work on scalar values.

Is there a way to declare array variables inside the stored procedure.

This is more or less broad as to what you are trying to achieve. An example as to how you can achieve an array sort of thing can be like:

SET @myArray = 'test1,test2,test3'; -- Treat it like an array

SELECT * FROM myTable
WHERE FIND_IN_SET (columnName, @arr);

(This is for MySQL)

Upvotes: 5

Related Questions