Create a PLSQL record type

The following example will create a PLSQL variable for a record type that consists of two attributes – id and description. The attributes aren’t restricted to standard Oracle types; you could also include user created types like the record_type in the example.

type record_type is record(id number, description varchar2(1000));
record_var record_type;

Creating a variable of this type is as simple as referring a variable as something like a varchar2

variable1 record_type;

The following example assigns values to the record.

variable1.id := 1;
variable1.description := 'this is a test description;

Create an Oracle PL/SQL package

How to create a package, with the name PACK1, with one function called F1

CREATE OR REPLACE PACKAGE PACK1 IS
 
	FUNCTION F1 RETURN BOOLEAN;
 
END PACK1;
 
CREATE OR REPLACE PACKAGE BODY PACK1 IS
 
	FUNCTION F1 RETURN BOOLEAN IS
 
	BEGIN
		RETURN TRUE;
	END F1;
 
END PACK1;

PL/SQL array

How to use a PL/SQL array

DECLARE
  TYPE year_type IS TABLE OF NUMBER;
  year_sales year_type := year_type();
  tot_sales NUMBER;
 
  CURSOR c IS
  SELECT 1 a
  FROM dual
  UNION ALL
  SELECT 81 a
  FROM dual;
 
BEGIN
  FOR r IN c LOOP
    year_sales.extend();
    year_sales(year_sales.LAST) := r.a;
  END LOOP;
 
  FOR i IN year_sales.FIRST .. year_sales.LAST LOOP
    DBMS_OUTPUT.put_line('Total sales: ' || year_sales(i));
  END LOOP;
 
END;