PL/SQL 사용법 및 튜닝
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 PL/SQL[편집]
android 데이터 트랜잭션 처리 능력이나 정보 보호, 데이터에 대한 보안, 예외처리 기능, 객체지향 등 데이터베이스와 관련된 중요한 모든 기능을 지원
- 데이터베이스 업무를 처리하기에 최적화된, 오라클에서 제공하는 프로그래밍 언어
- PL/SQL엔진은 메모리에서 구동됨
1.1 실행 원리[편집]
- PL/SQL 블록에서의 SQL문장이 수행되어 결과가 돌아옴
- 그 결과를 받고 난 후 선언된 변수와 함께 나머지 PL/SQL 문장을 실행됨
1.2 기본 구조[편집]
- DECLARE (선언부)
- - 모든 변수나 상수를 선언
- EXECUTABLE (실행부)
- - BEGIN,제어문 반복문, 함수정의 등의 로직을 기술
- EXCEPTION (예외처리부)
- - 실행 도중 에러 발생시 해결하는 문장 기술
1.3 유형[편집]
- Anonymous PL/SQL Block
- - 익명블록, 일회용, 한번만 쓰고 끝
- Stored PL/SQL Block
- - 저장된블록, 주기적으로 반복해서 사용할 경우
- PL/SQL은 기본적으로 처리된 결과값을 화면에 출력하지 않기 때문에 화명 출력 기능을 활성화 시켜야 함
SCOTT>set serveroutput on ;1.4 기본적인 문법[편집]
DECLARE
변수선언 ;
BEGIN
SQL 문장 ;
DBMS_OUTPUT.PUT_LINE(출력내용) ; --> 화면출력을 어떻게 할 것인가, END 전에 설정, 출력내용에 써주는 칼럼명은 변수이름을 써줘야한다!)
END ;
/1.5 주의사항[편집]
- 들여쓰기 권장 - 보기에도 쉬움. 습관을 들이자
- 그룹함수와 DECODE 함수는 SQL 문장에 포함되어야만 사용할 수 없음
- 문자, 날짜는 ' '로 묶어주기
- PL/SQL에서는 DDL, DCL을 지원하지 않음
- DDL - CREATE / ALTER / TRUNCATE / DROP
- DCL - GRAN / REVOKE
1.5.1 SELECT 문장 사용하기[편집]
- 문법: (실행부에서의 문법)
BEGIN
SELECT 칼럼1,칼럼2 INTO 변수1, 변수2
FROM 테이블명
WHERE 조건 ;
END ;
1.5.2 DML 문장 사용하기 (INSERT / UPDATE / DELETE / MERGE)[편집]
1.5.2.1 INSERT[편집]
- 서버에 데이터를 입력해주는 것이니까 변수선언은 없음 즉, DECLARE (선언부)는 없음
- 단, 사용자로부터 입력받은 값으로 테이블에 데이터 입력하는 것은 입력받는 값에 대한 변수는 선언해줘야함
- 문법:
BEGIN
INSERT INTO 테이블명
VALUES (값/변수) ;
END ;1.5.2.2 UPDATE[편집]
BEGIN
UPDATE 테이블명
SET 칼럼=값
WHERE 조건 ;
END ;1.5.2.4 MERGE[편집]
- 문법:
BEGIN
MERGE INTO 테이블1
USING 테이블2
ON (병합조건절)
WHEN MATCHED THEN
UPDATE SET 업데이트내용
WHEN NOT MATCHED THEN
INSERT VALUES (칼럼명) ;
END ;
- 알아두기!
- a = 20
- --> 비교를 하는 비교연산자, a가 20인지 비교
- a := 20
- --> 할당 연산자, 20의 값을 a에 할당
- 사용자에게 값을 입력 받아서 변수에 할당 할 때는 &(앰퍼센트) 기호를 사용
- 그래서 사용자한테 입력을 받은 값을 a에 넣고 싶으면
- a := '&abc'
1.5.2.5 중첩된 PL/SQL 블록 작성 방법[편집]
1.5.3 PL/SQL 샘플[편집]
DECLARE
-- Global variables
num1 number := 95;
num2 number := 85;
BEGIN
dbms_output.put_line('Outer Variable num1: ' || num1);
dbms_output.put_line('Outer Variable num2: ' || num2);
DECLARE
-- Local variables
num1 number := 195;
num2 number := 185;
BEGIN
dbms_output.put_line('Inner Variable num1: ' || num1);
dbms_output.put_line('Inner Variable num2: ' || num2);
END;
END;
/2 PL/SQL 변수[편집]
- 변수는 반드시 문자로 시작해야함!
2.1 단순 변수[편집]
- SCALAR 변수
- - 데이터 타입을 직접 지정해 주는 변수
- - number, varchar2, date 등등 으로 직접 데이터타입을 정해주는 것
- Reference변수 = 참조변수
- - 해당 데이터가 들어있는 칼럼 정보를 참조
- v_ename emp.ename%type
- --> 변수 이름을 v_ename 으로 하되 데이터타입은 emp테이블의 ename 칼럼의 데이터 타입과 동일하게
- 칼럼이 많지 않을 경우는 위의 방법을 쓰면 되지만 칼럼이 많을 경우는 rowtype 변수를 사용,
- 하나의 테이블에 여러 칼럼의 값을 한꺼번에 저장할 수 있는 변수
vrow emp%ROWTYPE2.2 복합 변수[편집]
- 변수 하나안에 여러가지 다른 유형의 데이터를 포함
2.2.1 Record Type[편집]
- - 레코드 타입 변수
- 여러개의 컬럼에 여러개의 값을 넣음
- 사용방법:
- DECLARE 에서
TYPE 타입이름(보통 테이블명_record_type) IS RECORD
(타입이름에 들어갈 여러 변수들, 일반 변수 선언과 동일한 문법으로 쓰면 됨) ;
변수이름 타입이름 ;
BEGIN에서
SELECT 출력할 칼럼들 INTO 변수이름 FROM 테이블명 WHERE 조건 ;2.2.2 Table Type[편집]
- - 테이블/ 컬렉션 타입 변수
- 하나의 칼럼에 데이터가 여러개
- 하지만 interger라는 칼럼을 만들 수 있는데 관리를 위한 데이터 구분의 역할을 하는 칼럼
- 사용방법:
DECLARE 에서
TYPE 타입이름(보통 tbl_테이블명) IS TABLE OF
(사용할 변수 설정) ; --> 테이블 타입은 칼럼이 하나기 때문에 변수도 하나
INDEX BY BINARY_INTEGER --> interger 칼럼 만드는 옵션
변수이름 타입이름 ;
BEGIN에서
SELECT 출력할 칼럼들 INTO 다른변수이름 FROM 테이블명 WHERE 조건 ;
변수이름(0) := 다른변수;
--> table type의 변수 1번째 칸에 다른변수 즉, 출력해야 할 칼럼에 대한 내용을 입력하라는 뜻.문자열 처리방법
https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#sthref344
q'구분자.........구분자'
p_cols=> q''{'||V_INDEX_COLUMNS||'}''