728x90
반응형
SMALL
■ 조합데이터 유형
- 다중값을 보유할 수 있다.
- 레코드 변수 : 서로 다른 유형의 값을 저장한다. ex) SELECT * 문
- 배열 변수 : 동일한 데이터 유형(1차원 배열)의 값을 저장한다.
- index by table (연관 배열)
- nested table (중첩 테이블)
- varray
1. 레코드 변수
TYPE 레코드명 IS RECORD(
필드명1 필드1 타입 [[NOT NULL] := 디폴트값],
필드명2 필드2 타입 [[NOT NULL] := 디폴트값],
...
필드명N 필드N 타입 [[NOT NULL] := 디폴트값],
);
레코드변수명 레코드명;
DECLARE
TYPE dept_record_type IS RECORD (
dept_id NUMBER, -- TABLE의 타입과 사이즈, 순서
dept_name VARCHAR(30),
dept_mgr NUMBER,
dept_loc NUMBER );
v_rec dept_record_type;
BEGIN
SELECT *
INTO v_rec
FROM hr.departments
WHERE department_id = 10;
DBMS_OUTPUT.PUT_LINE(v_rec.dept_id);
DBMS_OUTPUT.PUT_LINE(v_rec.dept_name);
DBMS_OUTPUT.PUT_LINE(v_rec.dept_mgr);
DBMS_OUTPUT.PUT_LINE(v_rec.dept_loc);
END;
/
# 레코드 변수 %TYPE 으로도 가능
DECLARE
TYPE dept_record_type IS RECORD (
dept_id hr.departments.department_id%TYPE,
dept_name hr.departments.department_name%TYPE,
dept_mgr hr.departments.manager_id%TYPE,
dept_loc hr.departments.location_id%TYPE );
v_rec dept_record_type;
BEGIN
SELECT *
INTO v_rec
FROM hr.departments
WHERE department_id = 10;
DBMS_OUTPUT.PUT_LINE(v_rec.dept_id);
DBMS_OUTPUT.PUT_LINE(v_rec.dept_name);
DBMS_OUTPUT.PUT_LINE(v_rec.dept_mgr);
DBMS_OUTPUT.PUT_LINE(v_rec.dept_loc);
END;
/
# 오류발생
이유) v_rec:컬럼명을 테이블의 컬럼명으로 그대로 작성해야 한다.
DECLARE
v_rec hr.departments%rowtype;
BEGIN
SELECT *
INTO v_rec
FROM hr.departments
WHERE department_id = 10;
DBMS_OUTPUT.PUT_LINE(v_rec.dept_id); --레코드명.필드명
DBMS_OUTPUT.PUT_LINE(v_rec.dept_name);
DBMS_OUTPUT.PUT_LINE(v_rec.dept_mgr);
DBMS_OUTPUT.PUT_LINE(v_rec.dept_loc);
END;
/
# %rowtype : 테이블 또는 뷰의 열이름, 데이터 유형을 가져다 사용하는 속성이다.
- 이점)
1. 테이블의 컬럼수, 데이터 유형을 알 필요가 없으며, 실제 런타임(프로그램)에 변경된 속성을 그대로 사용할 수 있다.
2. 'SELECT * INTO 레코드변수.. FROM' 일때 사용한다.
3. 관리의 편리성
4. 코드의 간결화
5. 행레벨의 INSERT 및 UPDATE
DECLARE
v_rec hr.departments%rowtype;
BEGIN
SELECT *
INTO v_rec
FROM hr.departments
WHERE department_id = 10;
DBMS_OUTPUT.PUT_LINE(v_rec.department_id);
DBMS_OUTPUT.PUT_LINE(v_rec.department_name);
DBMS_OUTPUT.PUT_LINE(v_rec.manager_id);
DBMS_OUTPUT.PUT_LINE(v_rec.location_id);
END;
/
# 중첩레코드 변수
DECLARE
TYPE rec_type IS RECORD (
v_sal number(8) ,
v_minsal number(8) not null default 1000 , --초기값 지정
v_hire_date hr.employees.hire_date%TYPE ,
v_rec hr.employees%rowtype --레코드변수(중첩)
);
v_myrec rec_type;
BEGIN
v_myrec.v_sal := v_myrec.v_minsal + 500;
v_myrec.v_hire_date := sysdate;
SELECT *
INTO v_myrec.v_rec
FROM hr.employees
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE('이름 : '||v_myrec.v_rec.first_name);
DBMS_OUTPUT.PUT_LINE('근무일수 : '||trunc(months_between(v_myrec.v_hire_date, v_myrec.v_rec.hire_date)/12));
END;
/
# test table 생성
CREATE TABLE hr.retired_emp (
empno NUMBER(4),
ename VARCHAR2(20),
job VARCHAR2(10),
mgr NUMBER(4),
hiredate DATE,
leavedate DATE,
sal NUMBER(10),
comm NUMBER(7, 2),
deptno NUMBER(2)
);
desc hr.retired_emp# PL/SQL 코드화
DECLARE
v_id number := 115;
v_emp_rec hr.employees%rowtype;
BEGIN
SELECT *
INTO v_emp_rec
FROM hr.employees
WHERE employee_id = v_id;
INSERT INTO hr.retired_emp( empno, ename, job, mgr, hiredate, leavedate, sal, comm, deptno )
VALUES ( v_emp_rec.employee_id, v_emp_rec.first_name, v_emp_rec.job_id, v_emp_rec.manager_id, v_emp_rec.hire_date, sysdate, v_emp_rec.salary, v_emp_rec.commission_pct, v_emp_rec.department_id );
END;
/SELECT * FROM hr.retired_emp;
# 레코드타입의 이점: 코드의 간결화
SELECT employee_id, first_name, job_id, manager_id, hire_date, sysdate, salary, commission_pct, department_id
FROM hr.employees
WHERE employee_id = 115;
DECLARE
v_id number := 115;
v_rec hr.retired_emp%rowtype;
BEGIN
SELECT employee_id, first_name, job_id, manager_id, hire_date, sysdate, salary, commission_pct, department_id
INTO v_rec -- 열과 구조 일치하면 SELECT 가능
FROM hr.employees
WHERE employee_id = v_id;
-- 행레벨의 INSERT
INSERT INTO hr.retired_emp -- 레코드모양과 컬럼모양이 일치해야 한다.
VALUES v_rec; -- type, size가 동일해서 insert 가능
END;
/
SELECT * FROM hr.retired_emp;
COMMIT;
# 행레벨의 UPDATE
- row : 키워드
DECLARE
v_id number := 115;
v_rec hr.retired_emp%rowtype;
BEGIN
SELECT employee_id, first_name, job_id, manager_id, hire_date, sysdate, salary, commission_pct, department_id
INTO v_rec
FROM hr.employees
WHERE employee_id = v_id;
-- 레코드 변수 안에 있는 필드 값 수정
v_rec.ename := upper(v_rec.ename);
v_rec.leavedate := to_date('2023-10-01','yyyy-mm-dd');
v_rec.comm := 0;
-- 행레벨의 UPDATE
UPDATE hr.retired_emp
SET row = v_rec
WHERE empno = v_id ;
END;
/
SELECT * FROM hr.retired_emp;
ROLLBACK;
2. 배열 타입(+EXISTS 구문 활용)
# PLS_INTEGER(계산작업) : 정수타입, -2의 31승 ~ 2의 31승 -1(-2G ~ 2G-1), INDEX 생성, NUMBER 보다 계산 실행속도가 빠름.
TYPE tab_char_type IS TABLE OF varchar2(10) INDEX BY PLS_INTEGER;
TYPE 데이터타입명 IS TABLE OF 값 INDEX BY 타입(방번호) ;
DECLARE
TYPE tab_char_type IS TABLE OF varchar2(10) INDEX BY PLS_INTEGER;
v_city tab_char_type;
BEGIN
v_city(1) := '서울';
v_city(2) := '경기';
v_city(3) := '부산';
v_city(4) := '광주';
DBMS_OUTPUT.PUT_LINE('배열의 저장된 값의 수 : '||v_city.count);
DBMS_OUTPUT.PUT_LINE('배열의 첫 번째 인덱스 번호 : '||v_city.first);
DBMS_OUTPUT.PUT_LINE('배열의 마지막 인덱스 번호 : '||v_city.last);
DBMS_OUTPUT.PUT_LINE('배열의 인덱스 next(n) 뒤에 오는 인덱스 번호 : '||v_city.next(1)); --
DBMS_OUTPUT.PUT_LINE('배열의 인덱스 prior(n) 앞에 오는 인덱스 번호 : '||v_city.prior(3));
END;
/
# FOR문 활용
DECLARE
TYPE tab_char_type IS TABLE OF varchar2(10) INDEX BY PLS_INTEGER;
v_city tab_char_type;
BEGIN
v_city(1) := '서울';
v_city(2) := '경기';
v_city(3) := '부산';
v_city(4) := '광주';
-- DBMS_OUTPUT.PUT_LINE('v_city(1) : '||v_city(1));
-- DBMS_OUTPUT.PUT_LINE('v_city(2) : '||v_city(2));
-- DBMS_OUTPUT.PUT_LINE('v_city(3) : '||v_city(3));
-- DBMS_OUTPUT.PUT_LINE('v_city(4) : '||v_city(4));
FOR i IN v_city.first .. v_city.last LOOP
DBMS_OUTPUT.PUT_LINE('v_city'||'('||i||')'||' : '||v_city(i));
END LOOP;
v_city(4) := '전주';
v_city(5) := '인천';
FOR i IN 1 .. v_city.last LOOP
DBMS_OUTPUT.PUT_LINE('v_city'||'('||i||')'||' : '||v_city(i));
END LOOP;
END;
/
# 오류발생
이유) 중간 데이터를 삭제되어 FOR문에서 오류발생
DECLARE
TYPE tab_char_type IS TABLE OF varchar2(10) INDEX BY PLS_INTEGER;
v_city tab_char_type;
BEGIN
v_city(1) := '서울';
v_city(2) := '경기';
v_city(3) := '부산';
v_city(4) := '광주';
v_city.delete(3); --배열의 n번 요소를 삭제
FOR i IN v_city.first .. v_city.last LOOP
DBMS_OUTPUT.PUT_LINE('v_city'||'('||i||')'||' : '||v_city(i));
END LOOP;
END;
/
# 없는 요소를 참조할 때, NO_DATA_FOUND 오류발생한다. IF문 EXISTS 구문 활용!
- 배열변수.EXISTS(N) : N번 요소가 존재하면 TRUE, 존재하지 않으면 FALSE 리턴한다.
DECLARE
TYPE tab_char_type IS TABLE OF varchar2(10) INDEX BY PLS_INTEGER;
v_city tab_char_type;
BEGIN
v_city(1) := '서울';
v_city(2) := '경기';
v_city(3) := '부산';
v_city(4) := '광주';
v_city.delete(3); --배열의 n번 요소를 삭제
--v.city.delete; 배열에 모든 요소 삭제
--v.city.delete(n); 배열의 n번 요소를 삭제
--v.city.delete(m,n); 배열에 m번부터 n번까지의 요소 삭제
FOR i IN v_city.first .. v_city.last LOOP
IF v_city.EXISTS(i) THEN
DBMS_OUTPUT.PUT_LINE('v_city'||'('||i||')'||' : '||v_city(i));
ELSE
DBMS_OUTPUT.PUT_LINE(i||' 요소는 존재하지 않습니다.');
END IF;
END LOOP;
END;
/
# UPDATE 변수를 배열변수로 활용
UPDATE hr.employees
SET salary = salary*1.1
WHERE employee_id = 100;
...
UPDATE hr.employees
SET salary = salary*1.1
WHERE employee_id = 200;
DECLARE
TYPE table_id_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
v_tag table_id_type;
BEGIN
v_tag(1) := 100;
v_tag(2) := 200;
UPDATE hr.employees
SET salary = salary*1.1
WHERE employee_id = v_tag(1);
UPDATE hr.employees
SET salary = salary*1.1
WHERE employee_id = v_tag(2);
ROLLBACk;
END;
/
# FOR문 으로 UPDATE문을 간결화 + EXISTS 활용
DECLARE
TYPE table_id_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
v_tag table_id_type;
BEGIN
v_tag(1) := 100;
v_tag(3) := 200;
FOR i IN v_tag.first ..v_tag.last LOOP
IF v_tag.EXISTS(i) THEN
UPDATE hr.employees
SET salary = salary*1.1
WHERE employee_id = v_tag(i);
dbms_output.put_line(v_tag(i));
ELSE
dbms_output.put_line(i||' 요소는 존재하지 않습니다.');
END IF;
END LOOP;
ROLLBACk;
END;
/728x90
반응형
LIST
'Data Base > PL SQL' 카테고리의 다른 글
| 231030 PL/SQL CURSOR, 명시적 CURSOR (0) | 2023.10.30 |
|---|---|
| 231030 PL/SQL 중첩테이블(NESTED TABLE) , VARRAY(VARIABL-SIZE ARRAY) 가변길이의 배열방식, 2차원 배열 (1) | 2023.10.30 |
| 231026 PL/SQL 반복문(CONTINUE문, WHILE LOOP문, FOR) (0) | 2023.10.26 |
| 231025 PL/SQL 조건제어문 (1) | 2023.10.25 |
| 231025 PL/SQL( Procedure Language Structured Query Language ) (0) | 2023.10.25 |