• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to secondary sidebar

Coder Tutorial

  • Home
  • HTML
  • CSS
  • PHP
  • SQL
  • MySQL
  • JS
  • PL/SQL
  • Python
  • Java
  • Oracle

PL/SQL Indexed Arrays

PL/SQL Indexed Arrays – How to write syntax, example

Indexed arrays have elements with two components: a primary key of type BINARY_INTEGER and a column of type scalar or record.

The Indexed Arrays syntax is:

TYPE type_name IS TABLE OF {
{ element_type | variable%TYPE |
table.column%TYPE }[NOT NULL]}
| table%ROWTYPE }
INDEX BY BINARY_INTEGER;
var_array type_name;

First example:

Example below shows an array indexed by numbers, for which the first 5 natural numbers.
It will display the number of rows contained in the array and its elements.

SET SERVEROUTPUT ON
DECLARE
	TYPE type_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
	ta type_array;
BEGIN
	FOR i IN 1..5 LOOP
		ta (i):=i;
	END LOOP;
	DBMS_OUTPUT.PUT_LINE('The rows of array: ' ||ta.COUNT||' rows');
	FOR i IN ta.FIRST..ta.LAST LOOP
		DBMS_OUTPUT.PUT_LINE('The element ' || i||' is '|| ta(i));
	END LOOP;
END;
SET SERVEROUTPUT OFF

Second example:

In the following example we define an array of records with the type of the EMPLOYEE table.
It will insert a new record in the EMPLOYEE table, and the same recording will be saved in the table EMPLOYEE_HISTORY.

SET SERVEROUTPUT ON
DECLARE
	TYPE type_array IS TABLE OF employee%ROWTYPE INDEX BY BINARY_INTEGER;
	table_1 type_array;
	table_2 type_array;
	i BINARY_INTEGER;
BEGIN
	IF table_1.COUNT<>0 THEN 
		i:= tablou1.LAST+1;
	ELSE 
		i:=1;
	END IF;
	table_1(i).EMPLOYEE_ID:=231;
	table_1(i).FIRST_NAME:='LARRY';
	table_1(i).LAST_NAME:='ROMPUY';
	table_1(i).HIRE_DATE:=SYSDATE;
	table_1(i).SALARY:=5000;
	table_1(i).DEPARTMENT_ID:=123;
	table_2:=table_1;
	INSERT INTO employee VALUES(
	table_1(i).EMPLOYEE_ID,
	table_1(i).FIRST_NAME
	table_1(i).LAST_NAME,
	table_1(i).HIRE_DATE, 
	table_1(i).SALARY, 
	table_1(i).DEPARTMENT_ID);
	INSERT INTO employee_history VALUES(
	table_2(i).EMPLOYEE_ID,
	table_2(i).FIRST_NAME
	table_2(i).LAST_NAME,
	table_2(i).HIRE_DATE, 
	table_2(i).SALARY, 
	table_2(i).DEPARTMENT_ID);
END;
SET SERVEROUTPUT OFF

Filed Under: PL/SQL

Primary Sidebar

Tutorials

  • HTML Tutorial
  • CSS Tutorial
  • PHP Tutorial
  • SQL Tutorial
  • MySQL Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • PL/SQL Tutorial
  • Java Tutorial
  • SQLPlus Tutorial
  • Oracle Tutorial
  • PostgreSQL Tutorial
  • Ruby Tutorial

Secondary Sidebar

    • PL/SQL Tutorial
    • PL/SQL Blocks
    • PL/SQL Variables
    • PL/SQL Control structures
    • PL/SQL Collections and Records
    • PL/SQL Cursors
    • PL/SQL Stored Procedures
    • PL/SQL Functions
    • PL/SQL Packages
    • PL/SQL Exception Handling
    • PL/SQL Triggers
    • PL/SQL If-Then-Else Statement
    • PL/SQL For Loop
    • PL/SQL While Loop
    • PL/SQL Case Statement
    • PL/SQL Varray
    • PL/SQL Nested Table
    • PL/SQL Indexed Arrays
    • PL/SQL Record
    • PL/SQL Examples
    • Oracle PL/SQL Functions
    • PL/SQL Interview Questions

Copyright  2018 - 2021 Coder Tutorial

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Cookie settingsACCEPT
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled

Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.

Non-necessary

Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.

SAVE & ACCEPT