<강의>
※스칼라 타입(SCALAR data type): 한가지 타입의 값을 갖는 타입(SELECT문, PL/SQL 변수의 자료형)
※PL/SQL 테이블은 오라클 SQL 테이블과 달리 일차원 배열 속성이다.
2. 자료형이 같은 데이터들을 저장하는 COLLECTION: 연관배열(IndexBy테이블), 중첩테이블, VARRAY
연계된,의미있는ㄱ
1) 연관배열(Associative array, IndexBy테이블) : 인덱스((유일)키)+값으로 구성. ＝HASH TABLE
TYPE 이름 IS TABLE OF 자료형 [NOT NULL] INDEX BY 이름;
DECLARE
TYPE ITAB_EX IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
-- : 문자가 여러개. PLS_INTEGER 숫자값으로 순서(INDEX) 지정하겠다.
--PLS_INTEGER: PLSQL 자료형. BINARY_INTEGER or NUMBER보다 빠르다.
text_arr ITAB_EX;
ㅊ
text_arr(1) := '1 data'; --인덱스
text_arr(2) := '2 data';
text_arr(3) := '3 data';
text_arr(4) := '4 data';
DBMS_OUTPUT.PUT_LINE('1번 값:'|| text_arr(1));
DBMS_OUTPUT.PUT_LINE('2번 값:'|| text_arr(2));
DBMS_OUTPUT.PUT_LINE('3번 값:'|| text_arr(3));
DBMS_OUTPUT.PUT_LINE('4번 값:'|| text_arr(4));
END;
/
가. RECORD로 입력하기
①DECLARE
TYPE REC_DEPT IS RECORD(
deptno DEPT.DEPTNO%TYPE,
dname DEPT.DNAME%TYPE,
loc DEPT.LOC%TYPE);
TYPE ITAB_EX1 IS TABLE REC_DEPT INDEX BY PLS_INTEGER;
dept_arr ITAB_EX1;
idx PLS_INTEGER := 0;
BEGIN
FOR I IN (SELECT *FROM DEPT) LOOP
idx := idx+1;
dept_arr(idx).DEPTNO := I.DEPTNO;
dept_arr(idx).DNAME := I.DNAME;
dept_arr(idx).LOC := I.LOC;
DBMS_OUTPUT.PUT_LINE(
dept_arr(idx).DEPTNO||':'||dept_arr(idx).DNAME||':'||dept_arr(idx).LOC);
END LOOP;
END;
/
②DECLARE
TYPE ITAB_EX2 IS TABLE OF DEPT%ROWTYPE INDEX BY PLS_INTEGER;
dept_arr ITAB_EX2;
idx PLS_INTEGER :=0;
BEGIN
FOR I IN (SELECT *FROM DEPT) LOOP
idx := idx+1;
dept_arr(idx).DEPTNO := I.DEPTNO;
dept_arr(idx).DNAME := I.DNAME;
dept_arr(idx).LOC := I.LOC;
DBMS_OUTPUT.PUT_LINE(
dept_arr(idx).DEPTNO||':'||dept_arr(idx).DNAME||':'||dept_arr(idx).LOC);
END LOOP;
END;
/
나. 연관배열관련 함수: COUNT / FIRST / LAST / PRIOR() / NEXT()
DECLARE
TYPE ITAB_EX IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
text_arr ITAB_EX;
BEGIN
TEXT_ARR(1) :='1 DATA';
TEXT_ARR(2) :='2 DATA';
TEXT_ARR(3) :='3 DATA';
TEXT_ARR(50) :='50 DATA';
DBMS_OUTPUT.PUT_LINE('1번 값:'|| text_arr.COUNT); --4
DBMS_OUTPUT.PUT_LINE('2번 값:'|| text_arr.FIRST); --1
DBMS_OUTPUT.PUT_LINE('3번 값:'|| text_arr.LAST); --50
DBMS_OUTPUT.PUT_LINE('50번 값:'|| text_arr.PRIOR(50)); --3
DBMS_OUTPUT.PUT_LINE('50번 값:'|| text_arr.NEXT(50)); --NULL
END;
/
RECORD COLLECTION 실습
DECLARE
TYPE ITAB_EMP IS TABLE OF EMP%ROWTYPE INDEX BY PLS_INTEGER;
emp_arr ITAB_EMP;
int PLS_INTEGER :=0;
BEGIN
FOR I IN (SELECT *FROM EMP) LOOP
int := int +1;
emp_arr(INT).empno := I.EMPNO;
emp_arr(INT).ename := I.ENAME;
emp_arr(INT).job := I.JOB;
emp_arr(INT).mgr := I.MGR;
emp_arr(INT).hiredate := I.HIREDATE;
emp_arr(INT).sal := I.SAL;
emp_arr(INT).comm := I.COMM;
emp_arr(INT).deptno := I.DEPTNO;
DBMS_OUTPUT.PUT_LINE(
emp_arr(INT).EMPNO||':'||emp_arr(INT).ENAME
||':'||emp_arr(INT).JOB||':'||emp_arr(INT).MGR
||':'||emp_arr(INT).HIREDATE||':'||emp_arr(INT).SAL
||':'||emp_arr(INT).COMM||':'||emp_arr(INT).DEPTNO);
END LOOP;
END;
/
2) 중첩테이블(≒중첩레코드)
3) VARRAY
커서는 주머니!
-SQL문 실행 결과 집합(Private SQL area_memorye≒JAVA의 STRINGPOOL)을 가리키는, 주소값을 저장한 변수(≒C언어의 포인터)
ㄴ특정 QUERY결과를 저장하며 CACHING.
같은 SESSION에서 같은 QUERY를 반복 호출할 때 Private SQL area를 반환하며 자원낭비를 최소화.
프로시저(PROCEDURE)과 함께 사용하는 것이 효과적.
-명시적 커서, 묵시적 커서
명시적 커서(Declaration)(≒JAVA의 메소드)
1. 순서 및 구조: 가지고오다ㄱ
커서 선언 > 커서 열기 > 커서에서 읽어온 데이터 사용(Fetch) > 커서 닫기
DECLARE
CURSOR 커서이름(대소문자 구분) IS SQL문; --커서 선언★★ 실무사용多
BEGIN
OPEN 커서이름; --커서 열기
FETCH 커서이름 INTO 변수; --한 행씩 가져오다.
CLOSE 커서이름; --커서 닫기
END;
/
2. 단행출력
DECLARE
dept_row DEPT%ROWTYPE; --커서데이터를 입력할 변수 선언
CURSOR C1 IS SELECT DEPTNO, DNAME, LOC
FROM DEPT
WHERE DEPTNO=40; --커서 선언
BEGIN
OPEN C1;
FETCH C1 INTO dept_row;
DBMS_OUTPUT.PUT_LINE(dept_row.deptno);
DBMS_OUTPUT.PUT_LINE(dept_row.dname);
DBMS_OUTPUT.PUT_LINE(dept_row.loc);
CLOSE C1;
END;
/
3. 전체행 출력
DECLARE
dept_row DEPT%ROWTYPE;
CURSOR C1 IS SELECT * FROM DEPT;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO dept_row;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(dept_row.deptno || ':' ||dept_row.dname|| ':' ||dept_row.loc);
END LOOP;
CLOSE C1;
END;
/
4. FOR문 사용 시에는 커서 선언만 하면 된다.
DECLARE
CURSOR C1 IS SELECT *FROM DEPT;
BEGIN
FOR I IN C1 LOOP --★★★자동 OPEN/FETCH/CLOSE
DBMS_OUTPUT.PUT_LINE(I.deptno || ':' ||I.dname|| ':' ||I.loc);
END LOOP;
END;
/
5.심화 출력
1)커서로 10번, 20번 부서 출력
DECLARE
dept_row DEPT%ROWTYPE; --커서데이터를 입력할 변수 선언
CURSOR C2(p_deptno DEPT.DEPTNO%TYPE) IS --C2(PARAMETER)
SELECT*FROM DEPT WHERE DEPTNO=p_deptno;
BEGIN
OPEN C2(10);
LOOP
FETCH C2 INTO dept_row;
EXIT WHEN C2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('10번 부서 - DEPTNO : ' || dept_row.DEPTNO
|| ', DNAME : ' || dept_row.DNAME
|| ', LOC : ' || dept_row.LOC);
END LOOP;
CLOSE C2;
OPEN C2(20);
LOOP
FETCH C2 INTO dept_row;
EXIT WHEN C2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('20번 부서 - DEPTNO : ' || dept_row.DEPTNO
|| ', DNAME : ' || dept_row.DNAME
|| ', LOC : ' || dept_row.LOC);
END LOOP;
CLOSE C2;
END;
/
※ PARAMETER(매개변수)와 ARGUMENT(인수) 차이점
PARAMETER: 함수 선언 시 사용되는 변수
ARGUMENT: 함수 호출 시 전달값
(참고 https://blog.naver.com/youndok/222078242772)
2)사용자가 입력한 부서 출력
DECLARE
v_deptno DEPT.DEPTNO%TYPE; --사용자가 입력할 부서번호를 저장할 변수 선언
CURSOR C1 (p_deptno DEPT.DEPTNO%TYPE) IS
SELECT *FROM DEPT WHERE DEPTNO=p_deptno;
BEGIN
v_deptno := &INPUT;
FOR I IN C1(v_deptno) LOOP
DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || I.DEPTNO
|| ', DNAME : ' || I.DNAME
|| ', LOC : ' || I.LOC);
END LOOP;
END;
/
묵시적 커서
-선언 없이 SQL문 사용 시 자동 선언되는 커서.
내부에서 상태를 체크하는 속성 제공.
1. SQP or 커서이름%NOTFOUND: FETCH문 추출행이 있으면 FLASE, 없으면 TRUE 반환.
2. SQP or 커서이름%FOUND: FETCH문 추출행이 있으면 TRUE, 없으면 FLASE 반환.
3. SQL or 커서이름%ROWCOUNT: 현재까지 추출된 행 수를 반환.
4. SQP or 커서이름%ISOPEN: 커서가 열렸다면 TRUE, 닫혔으면 FLASE 반환.
BEGIN
UPDATE DEPT_TEST SET DNAME='DATABASE' WHERE DEPTNO = 40;
DBMS_OUTPUT.PUT_LINE('갱신된 행의 수 : ' || SQL%ROWCOUNT);
IF (SQL%FOUND) THEN
DBMS_OUTPUT.PUT_LINE('갱신 대상 행 존재 여부 : true');
ELSE
DBMS_OUTPUT.PUT_LINE('갱신 대상 행 존재 여부 : false');
END IF;
IF (SQL%ISOPEN) THEN
DBMS_OUTPUT.PUT_LINE('커서의 OPEN 여부 : true');
ELSE
DBMS_OUTPUT.PUT_LINE('커서의 OPEN 여부 : false');
END IF;
END;
/
SUBPROGRAM
(Stored Procedure/Fuction/Package, Trigger)
-지금까지는 한번 저장하여 바로 실행하는, 저장되지 않는 익명블럭(Anonymous block)방식이었으나,
-SUBPROGRAM은 이름있는 블럭으로 DB에 객체로 저장.
최초 실행 시 한번만 컴파일.
단독 실행 및 다른 응용프로그램에서 사용 가능.
함수일 경우 값을 반환.
매개변수 사용 가능.
프로시저(Stored PROCEDURE)
-특징: SQL sever에서 제공되는 프로그래밍 기능으로, 특정 동작 일괄 처리를 위해 작성.
일반 쿼리의 반복 실행보다 SQL sever 성능 향상에 도움.
보안 강화(사용자별 테이블 접근 권한이 아닌 프로시저 접근 권한을 주면 된다)
Parameter 선택적 사용.
사용자정의프로시저, 시스템저장프로시저(T-SQL 프로시저, 'sp-' 접두어로 시작)
CREATE PROCEDURE 프로시저명 -- 수정 불가
CREATE OR REPLACE PROCEDURE 프로시저명 ( --수정 가능
parameter1 IN 데이터타입
parameter2 IN 데이터타입
) IS || AS
변수 or 상수 선언;
BEGIN
실행문장(INSERT, UPDATE, DELET 가능);
END;
/
1. Parameter 없는 프로시저 생성
CREATE OR REPLACE PROCEDURE pro_noparam
IS
V_EMPNO NUMBER(4) := 7788;
V_ENAME VARCHAR2(10);
BEGIN
V_ENAME := 'SCOTT';
DBMS_OUTPUT.PUT_LINE('V_EMPNO : ' || V_EMPNO);
DBMS_OUTPUT.PUT_LINE('V_ENAME : ' || V_ENAME);
END;
/ -- Procedure PRO_NOPARAM이(가) 컴파일되었습니다.
EXECUTE pro_noparam; --PL/SQL 프로시저가 성공적으로 완료되었습니다.
BEGIN
pro_noparam;
END;
/ --PL/SQL 프로시저가 성공적으로 완료되었습니다.
-프로시저 확인: 코드 라인별 확인 가능
SELECT *FROM USER_SOURCE WHERE NAME='PRO_NOPARAM';
SELECT*FROM USER_OBJECTS; --대문자로 검색
-프로시저 수정
ALTER PROCEDURE~
-프로시저 삭제
DROP PROCEDURE PRO_NOPARAM;
2. Parameter 있는 프로시저 생성 : IN모드(입력모드) Parameter
CREATE OR REPLACE PROCEDURE pro_param_in
(param1 IN NUMBER, --IN : 값 지정을 하지 않으면 프로시저 호출 시 값을 입력받겠다.
param2 NUMBER,
param3 NUMBER :=3,
param4 NUMBER DEFAULT 4
)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(param1);
DBMS_OUTPUT.PUT_LINE(param2);
DBMS_OUTPUT.PUT_LINE(param3);
DBMS_OUTPUT.PUT_LINE(param4);
END;
/
EXECUTE pro_param_in(10,9,8,7); --★위치지정방식: 반드시 parameter 순서 및 타입을 맞춰 인수 전달
EXECUTE pro_param_in(10); --'PRO_PARM_IN' 호출 시 인수의 갯수나 유형이 잘못되었습니다
EXECUTE pro_param_in(10,9);
EXECUTE pro_param_in(param1=>10,param2=>20); -- '=>': 배정연산자. 이름지정방식. 파라미터값 지정.
CREATE OR REPLACE PROCEDURE pro_parm_in
(param1 IN NUMBER,
param2 NUMBER :=3,
param3 NUMBER,
param4 NUMBER DEFAULT 4
)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(param1);
DBMS_OUTPUT.PUT_LINE(param2);
DBMS_OUTPUT.PUT_LINE(param3);
DBMS_OUTPUT.PUT_LINE(param4);
END;
/
EXECUTE pro_parm_in(10,9); --'PRO_PARM_IN' 호출 시 인수의 갯수나 유형이 잘못되었습니다
EXECUTE pro_parm_in(10,9,8);
3. Parameter 있는 프로시저 생성 : OUT모드(반환모드) Parameter
CREATE OR REPLACE PROCEDURE pro_param_out(
in_empno IN EMP.EMPNO%TYPE,
out_ename OUT EMP.ENAME%TYPE,
out_sal OUT EMP.SAL%TYPE
)IS
BEGIN
SELECT ENAME, SAL INTO out_ename, out_sal
FROM EMP
WHERE EMPNO=in_empno;
END pro_param_out;
/
DECLARE
v_ename EMP.ENAME%TYPE;
v_sal EMP.SAL%TYPE;
BEGIN
pro_param_out(7788,v_ename,v_sal);
DBMS_OUTPUT.PUT_LINE('ENAME:' || v_ename);
DBMS_OUTPUT.PUT_LINE('ENAME:' || v_sal);
END;
/
4. Parameter 있는 프로시저 생성 : IN OUT모드(입력및반환모드) Parameter
CREATE OR REPLACE PROCEDURE pro_param_inout(
inout_no IN OUT NUMBER
)IS
BEGIN
inout_no := inout_no*2;
END pro_param_inout;
/
DECLARE
no NUMBER;
BEGIN
no := 5;
pro_param_inout(no);
DBMS_OUTPUT.PUT_LINE(no);
END;
/
'DataBase' 카테고리의 다른 글
| 8019 [Oracle] SubProgram(TRIGGER) (0) | 2022.08.21 |
|---|---|
| 0818 [Oracle] EXCEPTION, 서브프로그램(FUNCTION) (0) | 2022.08.19 |
| 220816~220817 데이터 입출력 구현 인강 수강요약 (0) | 2022.08.17 |
| 0811 [Oracle] ROLL, PL/SQL 구성, RECORD TYPE (0) | 2022.08.16 |
| 0810 [Oracle] SQL CONSTRAINT(NN,U,PK, FK, C) (0) | 2022.08.11 |
