[Oracle] PLSQL Array

 

---

Oracle Array 샘플 #1

DECLARE
    type employeeNames IS VARRAY(6) OF VARCHAR2(10);
    type salary IS VARRAY(6) OF INTEGER;

    names employeeNames;
    salaries salary;
    total integer;

    BEGIN
        names := employeeNames('Mayur', 'Ekta', 'Pari', 'Yashika', 'Payal');
        salaries:= salary(9845, 9007, 7813, 8754, 9022);
        total := names.count;
    
        dbms_output.put_line('Total '|| total || ' Employees');
    
        FOR i in 1 .. total 
        LOOP
            dbms_output.put_line('Employee: ' || names(i) || ' Salaries: ' || salaries(i));
        END LOOP;
END;

실행결과 #1

Total 5 Employees
Employee: Mayur
Salaries: 9845
Employee: Ekta
Salaries: 9007
Employee: Pari
Salaries: 7813
Employee: Yashika
Salaries: 8754
Employee: Payal
Salaries: 9022

Oracle Array 샘플 #2

declare
   type array_t is varray(3) of varchar2(10);
   array array_t := array_t('Matt', 'Joanne', 'Robert');
begin
   for i in 1..array.count loop
       dbms_output.put_line(array(i));
   end loop;
end;

실행결과 #2

Matt
Joanne
Robert

Oracle Array 샘플 #3

create or replace TYPE "STRING_ARRAY" AS TABLE OF VARCHAR2(4000);

CREATE OR REPLACE PROCEDURE PRC_ARRAY_TEST (
      P_ID         IN  VARCHAR2(500)
    , P_ARR_STRING IN  STRING_ARRAY
    , O_SUCC       OUT VARCHAR2(4000)
)
AS
/*****************************************************************
 Program Name   : 프로시져명
 Description    : 설명
 Author         : 성명
 History        : 날짜
*****************************************************************/
BEGIN 
    DBMS_OUTPUT.PUT_LINE('P_ID :: ' || P_ID);

    FOR i IN 1 .. P_ARR_STRING.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE('P_ARR_STRING :: ' || P_ARR_STRING(i));
    END LOOP;
    
    O_SUCC := '성공';
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('오류가 발생했습니다');
        O_SUCC := '실패';
END;

실행 #3

DECLARE
    O_PARAM VARCHAR2(4000);
BEGIN

    PRC_ARRAY_TEST('USER_ID', STRING_ARRAY('첫번째', '두번째', '세번째', '네번째'), O_PARAM);
    DBMS_OUTPUT.PUT_LINE('O_PARAM :: ' || O_PARAM);

EXCEPTION
  WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE('기타오류') ;
END;

실행결과 #3

P_ID :: USER_ID
P_ARR_STRING :: 첫번째
P_ARR_STRING :: 두번째
P_ARR_STRING :: 세번째
P_ARR_STRING :: 네번째
O_PARAM :: 성공

---

https://www.educba.com/plsql-array/

PLSQL Array | Syntax and Working | Example of PLSQL Array

https://stackoverflow.com/questions/7012625/oracle-pl-sql-how-to-create-a-simple-array-variable

Oracle PL/SQL - How to create a simple array variable?

https://hermeslog.tistory.com/289

[Oracle] Procedure 예제

https://hermeslog.tistory.com/291

[Oracle] PL/SQL PREDEFINED EXCEPTION - 미리정의된 예약

https://hermeslog.tistory.com/293

[Oracle] PL/SQL 예제