스칼라 데이터 타입
스칼라 데이터 타입에는 일반단일 데이터타입의 변수와 %TYPE 데이터형 변수가 있다.
일반 변수 선언 문법
identifier [CONSTANT] 데이터타입 [NOT NULL] [:= 상수값이나 표현식]- identifier(변수)의 이름은 sql의 obejct 명과 동일한 규칙을 따름
- identifier를 상수로 지정하고 싶은 경우에는 CONSTANT라는 KEYWORD를 명시하고 반드시 초기값을 할당
- NOT NULL이 정의되어 있으면 초기값을 반드시 지정, 정의되어 있지 않을때는 생략 가능
- 초기값은 할당 연산자(:=)를 사용하여 정의
- 초기값을 정의하지 않으면 변수(identifier)는 NULL을 가짐
- 일반적으로 한줄에 한개의 변수를 정의
일반 변수 선언 예제
---숫자형 상수 선언(변할수 없는 변수)
v_price CONSTANT NUMBER(4,2) := 12.34
v_name VARCHAR2 ( 20);
v_Bir TYPE CHAR(1);
---NOT NULL의 TRUE로 초기화
v_flag BOOLEAN NOTNULL := TRUE;
v_birthday DATE%TYPE 데이터형
%TYPE 데이터형은 기술한 데이터베이스 테이블의 컬럼 데이터 타입을 모를 경우 사용할 수 있다.
코딩 이후 데이터베이스 컬럼의 데이터타입이 변경될 경우 다시 수정할 필요가 X
이미 선언된 다른 변수나 데이터베이스 컬럼의 데이터 타입을 이용하여 선언
데이터베이스 테이블과 컬럼 그리고 이미 선언한 변수명이 %TYPE앞에 올수 있다.
%TYPE속성을 이용하여 얻을 수 있는 장점
- DB Column definition을 정확히 알지 못하는 경우에 사용할 수 있다.
- DB Column definition이 변경되어도 다시 PL/SQL을 고칠 필요가 없다.
복합 데이터 타입
하나 이상의 데이터 값을 갖는 데이터타입으로 배열과 비슷한 역할을 하고 재사용이 가능
%ROWTYPE데이터형, PL/SQL 테이블, 레코드 등이 있다.
%ROWTYPE
- 테이블이나 뷰 내부의 컬럼 데이터형, 크기, 속성등을 그대로 사용할 수 있다.
- %ROWTYPE앞에 오는 것은 데이터베이스 테이블이름이다.
- 지정된 테이블의 구조와 동일한 구조를 갖는 변수를 선언할 수 있다.
- 데이터베이스 컬럼들의 수나 DATATYPE을 알지 못할때 편리함
- 테이블의 데이터 컬럼의 DATATYPE이 변경될 경우 프로그램을 재수정할 필요가 없다.
%ROWTYPE 예제
SQL> CREATE OR REPLACE PROCEDURE RowType_Test
(p_empno IN emp.empno%TYPE)
IS
--- %ROWTYPE 변수 선언,
--- emp테이블의 속성을 그대로 사용할 수 있다.
v_emp emp%ROWTYPE;
BEGIN
DBMS_OUTPUT.ENABLE;
--%ROWTYPE 변수 사용
SELECT empno,ename,hiredate
INTO v_emp.empno, v_emp.ename, v_emp.hiredate
FROM emp
WHERE empno = p_empno;
DBMS_OUTPUT.PUT_LINE('사원번호:' || v_emp.empno);
DBMS_OUTPUT.PUT_LINE('사원이름:' || v_emp.ename);
DBMS_OUTPUT.PUT_LINE('입 사 일:' || v_emp.hiredate);
END;
/
---- DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용
SQL > SET SERVEROUTPUT ON;
--- 실행결과
SQL> EXECUTE RowType_Test(7900);
사원번호: 7900
사원이름: JAMES
입사일: 81/12/03
PL/SQL 테이블
PL/SQL에서의 테이블은 오라클 SQL에서의 테이블과는 다르다. PL/SQL에서의 테이블은 일종의 일차원배열이라고 생각하면 이해하기 쉽다.
- 테이블은 크기에 제한X
- ROW의 수는 데이터가 들어옴에 따라 자동증가
- BINARY_INTEGER 타입의 인덱스 번호로 순서가 정해짐
- 하나의 테이블에 한 개의 컬럼 데이터를 저장
PL/SQL 테이블 문법 및 선언 예제
테이블 문법
TYPE table_name IS TABLE OF datatype
INDEX BY BINARY_INTEGER
identifier type_name;예시
--- 선언 예제
TYPE prdname_table IS TABLE OF VARCHAR2(30)
INDEX BY BINARY_INTEGER;
---prdname_table 테이블 타입으로 prdname_tab변수를 선언해서 사용
prdname_tab prdname_tablePL/SQL 테이블 예제
SQL> CREATE OR REPLACE PROCEDURE Table_Test
(v_deptno IN emp.deptno%TYPE)
IS
--각 컬럼에서 사용할 테이블의 선언
TYPE empno_table IS TABLE OF emp.empno%TYPE
INDEX BY BAINARY_INTEGER;
TYPE ename_table IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
TYPE sal_table IS TABLE OF emp.sal%TYPE
INDEX BY BINARY_INTEGER;
--테이블 타입으로 변수를 선언해서 사용
empno_tab empno_table;
ename_tab ename_table;
sal_tab sal_table;
i BINARY_INTEGER :=0;
BEGIN
DBMS_OUTPUT.ENABLE;
FOR emp_list IN (SELECT empno,ename,sal
FROM emp WHERE deptno =v_deptno) LOOP
i := i+1;
---테이블 변수에 검색된 결과를 넣음
empno_tab(i) := emp_list.empno;
ename_tab(i) := emp_list.ename;
sal_tab(i) := emp_list.sal;
END LOOP;
END;
/
---(DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)
SQL> SET SERVEROUTPUT ON;
실행결과
SQL> EXECUTE Table_Test(10);
사원번호: 7782
사원이름: CLARK
사원급여: 2450
사원번호: 7839
사원이름: KING
사원급여: 5000
사원번호: 7934
사원이름: MILLER
사원급여: 1300
-- emp 테이블에 있는 데이터의 입력한 부서에 해당하는 사원번호,
-- 사원이름, 사원급여를 뿌려주는 프로시저 이다PL/SQL 레코드
PL/SQL 레코드는 여러개의 데이터 타입을 갖는 변수들의 집합
스칼라, RECORD,또는 PL/SQL TABLE datatype 중 하나 이상의 요소로 구성된다.
논리적 단위로서 필드 집합을 처리할 수 있도록 해준다.
PL/SQL 테이블과 다르게 개별 필드의 이름을 부여할 수 있고, 선언시 초기화 가능하다.
PL/SQL 레코드 문법 및 선언 예제
TYPE record_name IS RECORD
(필드이름1 필드유형1 [NOT NULL {:= | DEFAULT } 식],
필드이름2 필드유형2 [NOT NULL {:= | DEFAULT } 식],
필드이름3 필드유형3 [NOT NULL {:= | DEFAULT } 식]);
Identifier record_name;---선언예제
TYPE record_test IS RECORD
( record_empno NUMBER,
record_ename VARCHAR2(30),
record_sal NUMBER);
--record _test 레코드 타입으로 prd_record변수를 선언해 사용
prd_record record_test;PL/SQL 레코드 예제
SQL> CREATE OR REPLACE PROCEDURE Record_Test
(p_empno IN emp.empno%TYPE)
IS
--하나의 레코드의 세가지의 변수타입 선언
TYPE emp_record IS RECORD
(v_empno NUMBER,
v_ename VARCHAR2(30),
v_hiredate DATE);
emp_rec emp_record;
BEGIN
DBMS_OUTPUT.ENABLE;
-- 레코드의 사용
SELECT empno, ename, hiredate
INTO emp_rec.v_empno, emp_rec.v_ename, emp_rec.v_hiredate
FROM emp
WHERE empno=p_empno;
DBMS_OUTPUT.PUT_LINE('사원번호 :' || emp_rec.v_empno);
DBMS_OUTPUT.PUT_LINE('사원이름 :' || emp_rec.v_ename);
DBMS_OUTPUT.PUT_LINE('입 사 일 :' || emp_rec.v_hiredate);
END;
/
-- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)
SQL> SET SERVEROUTPUT ON;
-- 실행 결과
SQL> EXECUTE Record_Test(7369);
사원번호 : 7368
사원이름 : SMITH
입사일 : 80/12/17
Table Of Record
PL/SQL TABLE 변수 선언과 비슷하며 데이터타입을 %ROWTYPE으로 선언하면 됨
PL/SQL TABLE과 RECORD의 복합 기능을 함
PL/SQL Table Of Record문법
--선언예제
TYPE dept_table_type IS TABLE OF dept%ROWTYPE
INDEX BY BINARY_INTEGER;
--Each element of dept_table is a record
dept_table dept_table_type
--아래 프로시저에서 사용된 예제PL/SQL Table Of Record 예제
CREATE OR REPLACE PROCEDURE Table_Test
IS
i BINARY_INTEGER := 0;
---PL/SQL Table of Record의 선언
TYPE dept_table_type IS TABLE OF dept%ROWTYPE
INDEX BY BINARY_INTEGER;
dept_table dept_table_type;
BEGIN
FOR dept_list IN (SELECT * FROM dept) LOOP
i:i+1;
--TABLE OF RECORD에 데이터 보관
dept_table(i).deptno := dept_list.deptno;
dept_table(i).dname := dept_list.dname;
dept_table(i).loc := dept_list.loc;
END LOOP;
FOR cnt IN 1..i LOOP
--데이터 출력
DBMS_OUTPUT.PUT_LINE('부서번호 : ' || dept_table(cnt).deptno || '부서명 : ' || dept_table(cnt).dname || '위치 : ' || dept_table(cnt).loc );
END LOOP;
END;
SQL> SET SERVEROUTPUT ON ;
SQL> EXECUTE Table_test;
부서번호 : 10부서명 : ACCOUNTING위치 : NEW_YORK
부서번호 : 20부서명 : RESEARCH위치 : DALLAS
부서번호 : 30부서명 : 인사과위치 : CHICAGO
부서번호 : 40부서명 : OPERATIONS위치 : BOS%TON
PL/SQL 처리가 정상적으로 완료되었습니다. PL/SQL내의 SQL문
INSERT 예제
--- 사원 등록 프로시저 예제
SQL > CREATE OR REPLACE PROCEDURE Insert_Test
(v_empno IN emp.empno%TYPE,
v_ename IN emp.ename%TYPE,
v_deptno IN emp.deptno%TYPE)
IS
BEGIN
DBMS_OUTPUT.ENABLE;
INSERT INTO emp(empno,ename, hiredate, deptno)
VALUES(v_empno, v_ename, sysdate,v_deptno);
COMMIT;
DBMS_OUTPUT.PUT_LINE(' 사원번호 : ' ||v_empno);
DBMS_OUTPUT.PUT_LINE('사원이름 : ' ||v_ename);
DBMS_OUTPUT.PUT_LINE('사원부서 : ' || v_deptno);
DBMS_OUTPUT.PUT_LINE('데이터 입력 성공');
END;
/
---DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용
SQL> SET SERVEROUTPUT ON;
---실행 결과
SQL> EXECUTE INSERT_TEST(1000, 'brave', 20);
사원번호: 1000
사원이름: brave
사원부서 : 20
데이터 입력성공
UPDATE 예제
SQL> CREATE OR REPLACE PROCEDURE Update_test
(v_empno IN emp.empno%TYPE, -- 급여를 수정한 사원의 사번
IS
--수정 데이터를 확인하기 위한 변수 선언
v_emp emp%ROWTYPE;
BEGIN
DBMS_OUTPUT.ENABLE;
UPDATE emp
SET sal = sal+(sal*(v_rate/100)) ---급여를 계산
WHERE empno = v_empno;
COMMIT;
DBMS_OUTPUT.PUT_LINE('데이터 수정 성공');
--수정된 데이터 확인하기 위해 검색
SELECT empno,ename,sal
INTO v_emp.empno, v_emp.ename,v_emp.sal
FROM emp
WHERE empno =v_empno;
DBMS_OUTPUT.PUT_LINE(' ****수정확인****');
DBMS_OUTPUT.PUT_LINE('사원번호 :' ||v_emp.empno);
DBMS_OUTPUT.PUT_LINE('사원이름 :' ||v_emp.ename);
DBMS_OUTPUT.PUT_LINE('사원급여 :' ||v_emp.sal);
END;
/
--- DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용
SQL> SET SERVEROUTPUT ON;
--- 7900번 사원의 급여 10%인하함
SQL> EXCUTE Update_Test(7900,-10);
데이터 수정 성공
데이터 수정 성공
****수정확인****
사원번호 : 7900
사원이름 : JAMES
사원급여 : 855DELETE 예제
SQL> CREATE OR REPLACE PROCEDURE Delete_Test
(p_empno IN emp.empno%TYPE)
IS
--삭제 데이터를 확인하기 레코드 선언
TYPE del_record IS RECORD
(v_empno emp.empno%TYPE,
v_ename emp.ename%TYPE,
v_hiredate emp.hiredate%TYPE);
v_emp del_recode;
BEGIN
DBMS_OUTPUT.ENABLE;
--삭제된 데이터 확인용 쿼리
SELECT empno,ename,hiredate
INTO v_emp.v_empno, v_emp.v_ename,v_emp.v_hiredate
FROM emp
WHERE empno = p_empno;
DBMS_OUTPUT.PUT_LINE('사원번호: ' ||v_emp.v_empno);
DBMS_OUTPUT.PUT_LINE('사원이름: ' ||v_emp.v_ename);
DBMS_OUTPUT.PUT_LINE('입사일 : ; ||v_emp.v_hiredate);
--삭제 쿼리
DELETE
FROM emp
WHERE empno = p_empno;
COMMIT;
DBMS_OTUPTU.PUT_LINE('데이터 삭제 성공 ');
END;
/
--DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용
SQL> SET SERVEROUTPUT ON;
-- 7900사원을 삭제
SQL> EXECUTE Delete_Test(7900);
사원번호 : 7900
사원이름 : JAMES
입 사 일 : 81/12/03
데이터 삭제 성공
FOR LOOP 문법
FOR index IN [REVERSE] 시작값..END값 LOOP
statement 1
statement 2
...
END LOOP;- index는 자동 선언되는 binary_integer형 변수이고, 1씩 증가함
- reverse 옵션이 사용될 경우 index는 upper_bound에서 lower_bound로 1씩 감소함
- IN다음에는 cursor나 select 문이 올수 있다.
FOR LOOP 예제
DECLARE
-- 사원 이름을 출력하기 위한 PL/SQL 테이블 선언
TYPE ename_table IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGE;
---사원 급여를 출력하기 위한 PL/SQL 테이블 선언
TYPE sal_table IS TABLE OF emp.sal%TYPE
INDEX BY BINARY_INTEGER;
ename_tab ename_table;
sal_tab sal_table;
i BINARY_INTEGER :=0;
BEGIN
DBMS_OUTPUT.ENABLE;
FOR emp_list IN (SELECT ename,sal
FROM emp
WHERE deptno =10) LOOP
i := i+1;
--테이블에 상품 이름을 저장
ename_tab(i) := emp_list.ename;
--테이블에 상품 가격을 저장
sal_tab(i) := emp_list.sal;
END LOOP;
-- 화면에 출력
FOR cnt IN 1..i LOOP
DBMS_OUTPUT.PUT_LINE('사원이름 :' || ename_tab(cnt));
DBMS_OUTPUT.PUT_LINE('사원급여 :' || sal_tab(cnt));
END LOOP;
END;
/
LOOP 문법
LOOP
PL/SQL satatement ...
다른 LOOP를 포함하여 중첩으로 사용 가능
EXIT [WHEN condition]
END LOOP;- EXIT 문이 사용되었을 경우 무조건 LOOP문을 빠져나감
- EXIT WHEN이 사용될 경우 WHEN절에 LOOP를 빠져나가는 조건을 제어할 수 있따.
LOOP 문 예제
--- DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
v_cnt NUMBER := 10;
BEGIN
DBMS_OUTPUT.ENABLE;
LOOP
INSERT INTO emp(empno,ename,hiredate)
VALUES(v_cnt, 'test'||to_char(v_cnt),sysdate);
v_cnt := v_cnt+1;
END WHEN v_cnt >110;
END LOOP;
DBMS_OUTPUT.PUT_LINE('데이터 입력 완료');
DBMS_OUTPUT.PUT_LINE(v_cnt-100|| '개의 데이터가 입력되었습니다')'
END;
/
WHILELOOP문 예제
WHILE LOOP문은 FOR문과 비슷하며, 조건이 TRUE일 경우 만 반복되는 LOOP문 이다.
SQL> DECLARE
v_cnt number := 100;
BEGIN
DBMS_OUTPUT.ENABLE;
WHILE v_cnt <110 LOOP
INSERT INTO emp(empno,ename,hiredate)
VALUES(emp_seq.nextval, 'test',sysdate);
v_cnt := v_cnt+1;
EXIT WHEN v_cnt > 110;
END LOOP;
DBMS_OUTPUT.PUT_LINE('데이터 입력 완료');
DBMS_OUTPUT.PUT_LINE(v_cnt -100 ||'개의 데이터가 입력되었습니다.');
END;
/
IF문 문법
IF 조건 THEN
statements
ELSEIF 조건 THEN
statements
ELSE
statements
END;IF문 예제
SQL> CREATE OR REPLACE PROCEDURE Dept_Search
(p_empno IN emp.empno%TYPE)
IS
v_deptno emp.deptno%type;
BEGIN
DBMS_OUTPUT.ENABLE;
SELECT deptno
INTO v_deptno //-SELECT INTO 문은 Select된 내용을복사할때 사용
FROM emp
WHERE empno = p_empno
IF v_deptno = 10 THEN
DBMS_OUTPUT_PUT_LINE('ACCOUNTING 부서 사원 입니다');
ELSEIF v_deptno =20 THEN
DBMS_OUTPUT_PUT_LINE('RESEARCH 부서 사원입니다');
ELSEIF v_deptno =40 THEN
DBMS_OUTPUT_PUT_LINE('OPERATIONS 부서 사원입니다.');
ELSE
DBMS_OUTPUT_PUT_LINE('부서가 이상해요...);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('부서가 없네요...');
END;
/
--- DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용
SQL> SET SERVEROUTPUT ON;
--- 프로시저 실행
SQL> EXECUTE Dept_Search(7900);
부서가 없네요...
SQL> EXECUTE Dept_Search(7369);
RESEARCH 부서 사원입니다. 
주니어 개발자 되고싶어요