복습
✏️ PL/SQL 변수의 종류 3가지
1. 스칼라 변수
2. 조합 변수 ( 레코드 , 컬렉션 )
- 레코드
- 사용자 정의 레코드
- %ROWTYPE
- 컬렉션
- 연관배열
- 중첩 테이블
- VARRAY
-
바인드 변수
오늘의 TIL
- %ROWTYPE의 이점
- 레코드를 사용해서 테이블의 행을 갱신하기
- 컬렉션 (연관배열, 중첩테이블, Varray)
- 명시적 커서
- 레코드를 사용한 커서문 작성하기
- 레코드를 사용한 커서문으로 insert, update
%ROWTYPE의 이점
- %ROWTYPE 속성은 다음을 사용하여 행을 검색하려는
경우에 유용합니다.
- SELECT * 문
- 행 레벨 INSERT 및 UPDATE 문
SQL update문 PL/SQL update문
update emp update emp
set sal = 9000, set row = 레코드변수
job = 'SALESMAN', where ename ='SCOTT';
hiredate = sysdate
where ename ='SCOTT'; 레코드를 사용해서 테이블의 행을 갱신하기
실습! EMP_TEST2에 update 수행하기
1. emp 테이블과 똑같은 emp_test2 테이블을 생성하는데, 월급을 모두 0으로 변경하시오
create table emp_test2 -- 테이블 생성
as
select * from emp;
update emp_test2
set sal = 0; -- 월급 0으로 수정
update emp_test2
set job = null; -- 직업 null로 수정
update emp_test2
set deptno = null; -- deptno null로 수정
commit; ✅ emp_test2 테이블은 emp테이블과 데이터는 같지만 개인정보가 대부분 null이다.
- 다음과같이 PL/SQL의 레코드변수를 이용해서 EMP 테이블의 데이터로 emp_test2 테이블의 데이터를 업데이트 하시오
accept p_empno prompt '사원번호를 입력하세요!'
declare
v_empno emp.empno%type := &p_empno;
v_emp emp%rowtype; -- 레코드 생성(레코드 변수 선언)
begin
select * into v_emp
from emp
where empno = v_empno;
v_emp.hiredate := SYSDATE;
update emp_test2 -- update!!
set row = v_emp -- 레코드변수
where empno = v_empno;
end;
/7788 입력하면 해당 사원의 모든 row를 넣는데, hiredate만 sysdate로. v_emp에 들어갈때마다 emp_test2에 업데이트가 된다.
✅ 원래 emp_test2 는 개인정보가 null인데 사원번호 7788인 사람의 정보는 v_emp에서 업데이트가 되서 들어왔다.
문제 89. salgrade테이블과 똑같은 salgrade_test 라는 테이블을 생성하고, losal, hisal을 전부 널값으로 갱신.
create table salgrade_test as select * from salgrade; update salgrade_test set losal = null; update salgrade_test set hisal = null; commit;
문제 90. grade를 물어보게하고 grade를 입력하면 해당 등급의 데이터를 salgrade테이블에서 읽어서 salgrade_test테이블에 갱신되게 하는 PL/SQL작성
accept p_grade prompt 'Grade를 입력하세요!' declare v_grade salgrade.grade%type := &p_grade; v_salgrade salgrade%rowtype; begin select * into v_salgrade from salgrade where grade = v_grade; update salgrade_test set row = v_salgrade where grade = v_grade; end; /
✅ 1 입력했더니 1에대한 모든 정보가 업데이트되었다.
조합 변수의 종류 2가지!
- 레코드
- 사용자 정의 레코드
- %ROWTYPE
- 컬렉션
- 연관배열
- 중첩 테이블
- VARRAY
연관배열
✅ 왜하냐면, loop, if문으로 어떠한 결과를 뽑아서 다른 것 하려고! 아니면 select 막 어렵게 해서 데이터 뽑아야하는데 너무 힘들다.
연관배열의 구조
구성
✔️ 숫자 1번부터 시작하는 unique한 열이 하나가 존재
✔️ 1번부터 시작하는 유니크한 열 + 스칼라 변수 하나
✔️ 1번부터 시작하는 유니크한 열 + 레코드 변수
연관배열 만드는 문법
✅ type + 타입명 + is table of
✅ INDEX BY PLS_INTEGER; 는 유니크한 숫자를 생성하는 코드
✅ 연관배열 변수명 + 타입명; -> 연관배열 변수 선언
위 예제에 대한 full code ↓
SET SERVEROUTPUT ON
CREATE OR REPLACE PACKAGE pkg_test_type1 -- PACKAGE 만들기
IS
TYPE emp_arr_typ IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
END pkg_test_type1;
/
DECLARE
emp_arr pkg_test_type1.emp_arr_typ; -- emp_arr연관배열 변수 생성
CURSOR emp_cur IS -- emp_cur라는 커서이다.
SELECT ename
FROM emp
WHERE ROWNUM <= 10; -- 이 데이터를 우리가 만든 연관배열(emp_arr)에 넣어줄거다!
i INTEGER := 0;
BEGIN
-- 연관 배열에 데이터 입력해서 구성하는 코드
FOR r_emp IN emp_cur LOOP
i := i + 1;
emp_arr(i) := r_emp.ename;
END LOOP;
-- 연관 배열에 구성된 데이터를 출력하는 코드
FOR j IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(emp_arr(j));
END LOOP;
END;
/✔️ PACKAGE의 장점은 코드 암호화!
✔️ TYPE emp_arr_typ IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;은 연관배열 만드는 코드. 이거로 패키지 만들었다.
연관배열 변수에 저장된 data에 쉽게 엑세스 하기 위한 옵션들
✔️ EXISTS
✔️ COUNT
✔️ FIRST
✔️ LAST
✔️ PRIOR
✔️ NEXT
✔️ DELETE
연관배열을 구성하는 예제
DECLARE
TYPE emp_table_type IS TABLE OF
employees%ROWTYPE INDEX BY PLS_INTEGER;
my_emp_table emp_table_type;
max_count NUMBER(3):= 104;
BEGIN
FOR i IN 100..max_count
LOOP
SELECT * INTO my_emp_table(i) FROM employees
WHERE employee_id = i;
END LOOP;
FOR i IN my_emp_table.FIRST..my_emp_table.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(my_emp_table(i).last_name);
END LOOP;
END;
/
emp 스타일로 !
1. emp2테이블을 다음과 같이 구성합니다
create table emp2
as
select rownum as imp_id, e.*
from emp e;
select * from emp2;2. 연관배열 실습을 합니다.
declare
type emp2_table_type is table of
emp2%rowtype index by pls_integer;
my_emp2_table emp2_table_type;
max_count number(3) := 14;
begin
-- emp2 테이블의 모든 데이터를 my_emp2_table연관배열에 입력하는 코드
for i in 1 ..max_count loop -- 1 ~ 14까지
select * into my_emp2_table(i)
from emp2
where emp_id = i;
end loop; -- 연관배열 레코드 구성하는
-- 연관배열 레코드 변수의 데이터를 처음부터 끝까지 가져와서 출력하는 코드
for i in my_emp2_table.first .. my_emp2_table.last loop
dbms_output.put_line(my_emp2_table(i).ename);
end loop;
end;
/문제 91. 위 코드를 수정해서, 이름옆에 월급도 함께 출력
declare type emp2_table_type is table of emp2%rowtype index by pls_integer; my_emp2_table emp2_table_type; max_count number(3) := 14; begin for i in 1 ..max_count loop select * into my_emp2_table(i) from emp2 where emp_id = i; end loop; for i in my_emp2_table.first .. my_emp2_table.last loop dbms_output.put_line(my_emp2_table(i).ename || ' ' || my_emp2_table(i).sal ); end loop; end; /
문제 92. 위 결과가 모든 사원이 다 출력되는것이 아니라, 월급이 2000 이상인 사원들만 출력되도록 하기
declare type emp2_table_type is table of emp2%rowtype index by pls_integer; my_emp2_table emp2_table_type; max_count number(3) := 14; begin for i in 1 ..max_count loop select * into my_emp2_table(i) from emp2 where emp_id = i; end loop; for i in my_emp2_table.first .. my_emp2_table.last loop if my_emp2_table(i).sal >= 2000 then -- if문!! dbms_output.put_line(my_emp2_table(i).ename || ' ' || my_emp2_table(i).sal ); end if; -- if문!! end loop; end; /
✅dbms_output.put_line위쪽에 if문 사용!
[점심시간 문제] 월급을 물어보게 하고, 월급을 입력하면 해당 월급 이상인 사원들의 이름, 월급이 출력되게 하는 pl/sql 작성
set serveroutput on accept p_sal prompt '월급을 입력하세요!' -- p_sal에 숫자를 담아서 declare type emp2_table_type is table of emp2%rowtype index by pls_integer; my_emp2_table emp2_table_type; max_count number(3) := 14; begin for i in 1 ..max_count loop select * into my_emp2_table(i) from emp2 where emp_id = i; end loop; for i in my_emp2_table.first .. my_emp2_table.last loop if &p_sal <= my_emp2_table(i).sal then -- 입력받은 숫자보다(월급) 월급이 큰 사원들만 출력 dbms_output.put_line(my_emp2_table(i).ename || ' ' || my_emp2_table(i).sal ); end if; end loop; end; /
중첩 테이블
✅ 연관배열과는 다르게 데이터베이스에 변수를 저장할 수 있다. 최대 2GB까지 저장할 수 있다. 마치 임시테이블처럼 쓰여진다.
Varray
✅ 중첩 테이블과 같이 최대 2GB까지 저장할 수 있고, 기본은 메모리에 데이터를 저장하는데 SQL유형으로 사용하면 데이터베이스의 테이블로 저장할 수 있다.
📖 7장 명시적 커서
배우게 될 것들!
✅ 암시적 커서 및 명시적 커서 구분
✅ 명시적 커서를 사용하는 이유 설명
✅ 명시적 커서 선언 및 제어
✅ 간단한 루프 및 커서 FOR 루프를 사용하여 데이터 패치(fetch)
✅ 파라미터가 포함된 커서 선언 및 사용
✅ FOR UPDATE 절을 사용하여 행 잠금
✅ WHERE CURRENT OF 절을 사용하여 현재 행 참조 ⭐
커서란? (메모리공간이다)
Oracle 서버에서 실행되는 모든 SQL 문에는 연관된 개별 커서가 있습니다.
암시적 커서: 모든 DML 및 PL/SQL SELECT 문에 대해
PL/SQL에서 선언하고 관리합니다. 오라클이 알아서 할당한 메모리. 이 메모리는 PL/SQL에서 DML문을 처리했을 때 몇건의 행을 처리했는지 그 데이터를 저장할 메모리!!
ex) 1. SQL%rowcount 2. SQL%found 3. SQL%notfound명시적 커서: 프로그래머가 선언하고 관리합니다. PL/SQL 프로그래머가 프로그램 내에서 사용할 데이터를 미리 SQL로 SELECT해서 올려놓은 메모리 영역
암시적 커서 참고
명시적 커서작업
PL/SQL 프로그래머가 프로그램 내에서 사용할 데이터를 미리 SQL로 SELECT해서 올려놓은 메모리 영역
명시적 커서 제어
- 커서를 먼저 선언한다.
- 커서를 연다.
- 커서 내의 데이터를 첫행부터 하나씩 FETCH 한다.
- 더이상 FETCH할게 없을 때 까지 FETCH 한다.
- 커서를 닫는다. (꼭닫다. 계속열려있으면) <--- 만약 커서를 닫는 코드를 잊어버리고 코딩하게 된다면 나중에 메모리 부족으로 커서를 열지 못하게 된다.
✏️ DBA가 꼭 알아야하는 중요 파라미터 !(중요설정)
DB에서 열 수 있는 최대 커수의 갯수
open_cursors;
show parameter cursors;
select name ,value
from v$parameter
where name like '%cursor%;반드시 커서를 확인하고 열어서 썼으면 커서를 닫는 코드를 써줘야 한다.
커서를 사용하는 전체 예제
accept p_deptno prompt '부서번호를 입력하시오'
declare
cursor emp_cursor is
select ename, sal, job, deptno
from emp
where deptno = &p_deptno ; -- 1. 커서 선언
v_ename emp.ename%type;
v_sal emp.sal%type;
v_job emp.job%type;
v_deptno emp.deptno%type;
begin
open emp_cursor; -- 2. 커서 오픈
loop
fetch emp_cursor into v_ename, v_sal, v_job, v_deptno; -- 3. 커서 fetch
exit when emp_cursor%notfound; -- 커서에 더 이상 fetch 할게 없으면 loop문 종료해라!
dbms_output.put_line( v_ename || ' ' || v_sal || ' ' || v_job
|| ' ' || v_deptno);
end loop;
close emp_cursor; -- 4. 커서 닫기
end;
/명령 프롬프트창에서 PL/SQL 코드 수행하는 방법
- 접속하기
sqlplus 사용자명/비밀번호
- 아래 명령어 해서 메모장에 실행할 코드 넣어서 저장한다. 한글 안깨지게 하려면 다른이름으로 저장해서 인코딩을 ANSI로!
ed emp_cursor.sql
- 실행! 혹시 수정해야할 사항이 생기면 노트패드에서 코드 불러와서 수정후 저장한다. 그럼 프롬프트에서 그냥 바로 저장된 코드로 실행이 된다.
@emp_cursor.sql
문제 94. 우리반 테이블에서 통신사를 물어보게하고, 통신사를 입력하면 해당 통신사의 학생들의 이름과 나이와 주소가 출력되는 PL/SQL문을 작성하시오! (커서문장, 스크립트명 : emp17_cursor.sql)
set serveroutput on set verify off accept p_telecom prompt '통신사를 입력하시오' declare cursor emp17_cursor is -- 커서 선언 select ename, age, address from emp17 where telecom = '&p_telecom' ; v_ename emp17.ename%type; v_age emp17.age%type; v_address emp17.address%type; begin open emp17_cursor; -- 커서 열기 loop fetch emp17_cursor into v_ename, v_age, v_address; exit when emp17_cursor%notfound; -- 커서가 데이터 발견하지 않을때 까지 dbms_output.put_line( v_ename || chr(9) || v_age || chr(9) || v_address ); -- 하나 fetch 하고 출력하고 아직 데이터 남아있으니까 -- 다시 fetch 하고 출력하고 ... -- 반복하다가 데이터가 없으면 end loop 만나서 반복문 종료한다. end loop; close emp17_cursor; -- 커서 닫기 end; /✅chr(9) 는 탭이다! 그래서 간격이 일정하게 띄어진다.
레코드를 사용한 커서문 작성하기
set serveroutput on set verify off accept p_telecom prompt '통신사를 입력하시오' declare cursor emp17_cursor is select ename, age, address from emp17 where telecom = '&p_telecom' ; emp17_recode emp17_cursor%rowtype; -- 방 3개짜리 레코드 변수(emp17_recode)가 만들어졌다. (선언) -- 방 이름은 ename, age, address이렇게 3개다. begin open emp17_cursor; loop fetch emp17_cursor into emp17_recode; -- 레코드이름으로 변경되었다. exit when emp17_cursor%notfound; dbms_output.put_line( emp17_recode.ename || chr(9) || -- 레코드의 ename에 있는거 출력 emp17_recode.age || chr(9) || -- 레코드의 age에 있는거 출력 emp17_recode.address ); -- 레코드의 address에 있는거 출력 end loop; close emp17_cursor; end; /✅
emp17_recode emp17_cursor%rowtype;로 레코드를 선언해준다. 원래 레코드 생성할 때는 뒤에 테이블명 썼는데 여기서는 위에 만든 커서를 가져왔다. 여기서, cursor는 ename, age, address 이렇게 3개의 방이 생기니까 이형태 그대로 emp17_recode가 생성이 된다.
✅fetch emp17_cursor into emp17_recode;하면 커서 그대로 recode에 들어간다. 어차피 같은 형태니까 그대로 들어간다.
✅ 출력하는 과정에서 이름을 레코드의 ename, 레코드의 age 니까emp17_recode.ename로 써준다.
문제 95. 우리반 테이블을 가지고 3개의 테이블 생성
create table kt_emp17
as
create table lg_emp17
as
select * from emp17 where 1=2;
create table sk_emp17
as
select * from emp17 where 1=2;커서문으로 insert하기
문제 96. 우리반 테이블의 데이터를 읽어서 각각의 통신사에 해당하는 학생들의 데이터를 각 통신사 테이블에 입력하시오. 입력할 때 개인정보는 입력되지 않도록 null처리 하시오!
나이, 주소, 이메일 null처리 하기
declare cursor emp17_cursor is select * from emp17; emp17_recode emp17_cursor%rowtype; -- emp17 모든 컬럼이 -- emp17_recode에 들어감 begin open emp17_cursor; loop fetch emp17_cursor into emp17_recode; exit when emp17_cursor%notfound; --개인정보 null처리 emp17_recode.age := null; emp17_recode.address := null; emp17_recode.email := null; if emp17_recode.telecom = 'kt' then insert into kt_emp17 values emp17_recode; elsif emp17_recode.telecom = 'lg' then insert into lg_emp17 values emp17_recode; else insert into sk_emp17 values emp17_recode; end if; -- if문 종료 end loop; -- loop 종료 close emp17_cursor; -- cursor 종료 end; /
문제 97. emp 테이블에 grade라는 컬럼을 추가하세요!
alter table emp
add grade varchar2(5);커서문으로 update하기
문제 98. emp 테이블에 grade라는 컬럼에 다음의 조건에 해당되는 데이터로 값을 갱신하세요 (update)
A 등급 : 월급이 3000 이상일 때
B 등급 : 월급이 2000 이상일 때
C 등급 : 월급이 1000 이상일 때
D 등급 : 나머지
declare cursor emp_cursor is select empno, ename, sal from emp; emp_record emp_cursor%rowtype; v_grade varchar2(5); begin open emp_cursor ; loop fetch emp_cursor into emp_record; exit when emp_cursor%notfound; if emp_record.sal >= 3000 then v_grade := 'A'; elsif emp_record.sal >= 2000 then v_grade := 'B'; elsif emp_record.sal >=1000 then v_grade := 'C' ; else v_grade := 'D'; end if; update emp set grade = v_grade where empno = emp_record.empno; end loop; close emp_cursor; end; /✅ 지금 emp테이블에 이미 grade라는 컬럼이 있기때문에 v_grade를 따로 만들지 않아도 되고, 커서만들때 만들어도 된다. 수정코드 ↓
declare cursor emp_cursor is select empno, ename, sal, grade from emp; emp_record emp_cursor%rowtype; begin open emp_cursor ; loop fetch emp_cursor into emp_record; exit when emp_cursor%notfound; if emp_record.sal >= 3000 then emp_record.grade := 'A'; elsif emp_record.sal >= 2000 then emp_record.grade := 'B'; elsif emp_record.sal >= 1000 then emp_record.grade := 'C' ; else emp_record.grade := 'D'; end if; update emp set grade = emp_record.grade where empno = emp_record.empno; end loop; close emp_cursor; end; /
문제 96. 우리반 테이블에 domain 이라는 컬럼을 추가하고, 해당 학생의 이메일의 도메인으로 값이 갱신되게 하시오. 그 학생의 이메일이 gmail이면 gamil로 갱신, naver면 naver로 갱신되게 하시오!
alter table emp17
add domain varchar2(10);declare cursor emp17_cursor is -- 메모리 이름(커서)이 emp17_cursor! select empno, ename, rtrim(substr(email, instr(email,'@')+1),'.com') as email, domain from emp17; emp17_record emp17_cursor%rowtype; begin open emp17_cursor ; loop fetch emp17_cursor into emp17_record; exit when emp17_cursor%notfound; if emp17_record.email = 'naver' then emp17_record.domain := 'naver'; else emp17_record.domain := 'gmail'; end if; update emp17 set domain = emp17_record.domain where empno = emp17_record.empno; end loop; close emp17_cursor; end; /
