PL/SQL
: Procedural Language/SQL
- 오직 오라클에서만 사용가능
(my sql, ms sql 연동 불가) - SQL에서 프로그래밍 언어의 역할을 한다
(변수, 반복문 등 수행 가능)
PL/SQL 문법
DECLARE
(선언)
BEGIN
(실행)
EXCEPTION
(예외처리)
END;- DECLARE / 선언 : 변수들을 정의
- BEGIN / 실행 : 실제 실행될 PL/SQL이 들어감 (필수)
- EXCEPTION / 예외처리 : 예외 발생 구문을 적는 부분
변수의 종류
변수의 데이터 타입
%TYPE
테이블명.컬럼명%TYPE
: 변수 또는 커서가 참조하는 열의 데이터타입을 자동으로 가져오는데 사용
(단일 열에 대한 데이터 타입을 가져오는데 사용)
%ROWTYPE
테이블명%ROWTYPE
: 레코드 타입 변수를 선언할 때 사용
(전체 테이블 또는 뷰의 열에 대한 데이터 타입을 가져오는데 사용)
%TYPE과 %ROWTYPE을 쓰는 이유?
-
변수의 데이터 타입을 테이블의 열의 데이터 타입과 일치시킬 수 있음
(변수와 테이블의 열의 데이터 타입이 일치하지 않으면 오류 발생할 수 있음) -
변수를 선언할 때 테이블의 열의 정보를 일일이 작성하지 않아도 된다.
-
또, %ROWTYPE을 사용하면 한 번에 테이블의 모든 열에 대한 정보를 가져올 수 있기 때문에 코드가 더욱 간결해지고 유지보수가 용이해짐.
변수 선언
식별자 [CONSTANT] 데이터타입 [NOT NULL] [:=초기값];- 초기값을 지정하고자 할 때는 할당연산자 := 를 사용
- 식별자를 상수로 지정하고 싶은 경우 'CONSTANT'라는 키워드를 명시하고 반드시 초기값 지정
- NOT NULL이 정의되어 있으면 초기값 반드시 지정
- 초기값 정의하지 않으면 변수는 NULL값을 가짐
SET SERVEROUTPUT ON
: 출력옵션으로 ON
PL/SQL은 기본적으로 처리된 PL/SQL 문장 결과를 화면에 출력해 주지 않음.
프로그램 실행시 해당 문장을 입력해서 출력 기능을 활성화 시켜줘야 한다.
DBMS_OUTPUT
: 결과값을 화면에 출력하기 위한 패키지
<< 활성화 하기 위해서는 DBMS_OUTPUT.ENABLE; 을 사용해야 함>>
- PUT_LINE
- DBMS_OUTPUT.PUT_LINE()
- 새 줄에 출력 작성 (자동 줄바꿈 실행)
- PUT
- DBMS_OUTPUT.PUT()
- 줄바꿈 수동으로 처리해야 함
- NEW_LINE
- DBMS_OUTPUT.NEW_LINE()
- 새 줄 생성(인수 없이 호출할 수 있기 때문에 출력할 문장이 없을 때 사용할 수 있음)
- ENABLE
- DBMS_OUTPUT.ENABLE()
- DBMS_OUTPUT 활성화
(함수 호출하면 위에 나온 1,2,3 함수 사용 가능)
- DISABLE
- DBMS_OUTPUT.DISABLE()
- DBMS_OUTPUT 비활성화
(주로 성능 개선을 위해 사용)
ex)
SET SERVEROUTPUT ON
DECLARE
V_I NUMBER(9,2):=0;
V_NAME VARCHAR2(20);
C_PI CONSTANT NUMBER(8,6):=3.141592;
V_FLAG BOOLEAN NOT NULL := TRUE;
V_DATE VARCHAR2(10):= TO_CHAR(SYSDATE,'YYYY-MM-DD');
BEGIN
V_NAME:= '홍길동';
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE('V_I:'||V_I);
DBMS_OUTPUT.PUT_LINE('V_NAME:'||V_NAME);
DBMS_OUTPUT.PUT_LINE('C_PI:'||C_PI);
DBMS_OUTPUT.PUT_LINE('V.DATE:'||V_DATE);
END;IF문
- 조건 다음에는 THEN , 문장 제일 마지막에는 END IF를 작성
- ELSE IF가 아닌 ELSIF
- ELSIF는 여러개 가능하나, ELSE는 하나만 가능
/* 조건이 1개일 경우*/
IF 조건식 THEN
조건 처리;
END IF;
/* 조건이 2개일 경우*/
IF 조건식 THEN
조건처리 1;
ELSE
조건처리 2;
END IF;
/* 조건이 N개일 경우*/
IF 조건식 THEN
조건처리 1;
ELSIF 조건식 THEN
조건처리 2;
...
ELSE
조건처리 N;
END IF;ex1) 조건이 2개인 경우
DECLARE
V_AVG_SALE PROD.PROD_SALE%TYPE;
V_SALE NUMBER := 500000;
BEGIN
DBMS_OUTPUT.ENABLE;
SELECT AVG(PROD_SALE) INTO V_AVG_SALE FROM PROD;
IF V_SALE < V_AVG_SALE THEN
DBMS_OUTPUT.PUT_LINE('평균단가가 500000초과입니다.');
ELSE
DBMS_OUTPUT.PUT_LINE('평균단가가 500000이하입니다.');
END IF;
END;ex) 조건이 N개인 경우
DECLARE
v_num NUMBER := 67;
BEGIN
DBMS_OUTPUT.ENABLE;
IF v_num > 90 THEN
DBMS_OUTPUT.PUT_LINE('수');
ELSIF v_num > 80 THEN
DBMS_OUTPUT.PUT_LINE('우');
ELSIF v_num > 70 THEN
DBMS_OUTPUT.PUT_LINE('미');
ELSE
DBMS_OUTPUT.PUT_LINE('분발합시다.');
END IF;
END;
- 조건 평가
- 조건이 참일 경우 IF 블록 실행, FALSE인 경우 ELSIF 절 검사.
- 모든 ELSIF절이 거짓이면 ELSE절 싱행
- IF문 종료
SELECT INTO
: 테이블로부터 데이터를 조회하여, 해당 데이터를 변수에 저장하는 구문
ex) TABLE A에서 모든 데이터를 가져와 A_COPY라는 테이블을 생성하여 데이터들을 INSERT하고자 함.
(A_COPY라는 테이블은 만들어지지 않음)
SELECT * INTO A_COPY FROM A: A테이블과 같은 칼럼과 같은 데이터를 가지는 A_COPY라는 테이블이 생성됨.
SELECT AVG(PROD_SALE) INTO V_AVG_SALE FROM PROD;:AVG(PROD_SALE) 데이터를 V_AVG_SALE에 집어넣음
CASE문
- SQL에서 사용하는 CASE문과 동일
- 단, END CASE를 마지막에 적어줘야 함
CASE 표현식
WHEN 조건식 1 THEN
실행할 코드 1;
WHEN 조건식 2 THEN
실행할 코드 2;
...
ELSE
실행할 코드 N;
END CASE;- 표현식 : 평가할 값이나 식을 의미
ex)
DECLARE
v_dept_no NUMBER := 10;
v_dept_name VARCHAR2(20);
BEGIN
CASE v_dept_no
WHEN 10 THEN
v_dept_name := 'ACCOUNTING';
WHEN 20 THEN
v_dept_name := 'RESEARCH';
WHEN 30 THEN
v_dept_name := 'SALES';
ELSE
v_dept_name := 'OTHER';
END CASE;
DBMS_OUTPUT.PUT_LINE('Department name: ' || v_dept_name);
END;WHILE문
: 반복될 때마다 조건을 확인하고 조건이 TRUE가 되어야 LOOP를 실행
- 조건이 만족할 때까지 반복 처리
- EXIT 문은 WHILE LOOP를 벗어나게 함
ex) 1부터 10까지 더하기
DECLARE
V_SUM NUMBER := 0;
V_VAR NUMBER := 1;
BEGIN
WHILE V_VAR <= 10 LOOP
V_SUM := V_SUM + V_VAR;
V_VAR := V_VAR + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('1부터 10까지의 합=' || V_SUM);
END;LPAD: 왼쪽에 블랭크를 부가하는 함수
SELECT LPAD('A',2) FROM DUAL;
SELECT LPAD('A',3,'B') FROM DUAL;RPAD: 오른쪽에 블랭크를 부가하는 함수
SELECT RPAD('A',2) FROM DUAL;
SELECT RPAD('A',3,'B') FROM DUAL;WHILE문을 사용하여 피라미드 만들기
