PL/SQL - 강원대학교 컴퓨터과학전공
Download
Report
Transcript PL/SQL - 강원대학교 컴퓨터과학전공
PL/SQL
KANG JOHYEON
[email protected]
PL/SQL
PL/SQL
Procedural language extension to Structured Query Language(SQL)
SQL을 확장한 순차적 처리 언어
SQL과 일반 프로그래밍 언어의 특성을 결합
조건문, 반복문 등을 선언해서 사용할 수 있음
DATABASE LABORATORY
PL/SQL 기본 구조
기본 단위는 블록(block)
선언부
실행부
예외처리부
선언부
사용할 변수나 상수를 선언하는 부분
DECLARE를 사용해서 나타냄
실행부
실제 처리 로직을 담당하는 부분
선언된 변수에 값을 할당하거나 SQL문장을 사용하는 등 실제 로직을 처리
BEGIN으로 시작되어 END로 끝남
예외처리부
로직을 처리하던 중 발생할 수 있는 각종 오류들에 대해 처리하는 부분
EXCEPTION 키워드 사용
DATABASE LABORATORY
PL/SQL 기본 구조
DECLARE
counter INTEGER;
BEGIN
counter := counter +1;
IF counter IS NULL THEN
dbms_output.put_line(‘Result : COUNTER IS Null’);
END IF;
END;
DATABASE LABORATORY
PL/SQL 기본 구조
변수 선언은 ‘변수명 데이터타입‘ 형태로 선언
문장의 종료 시점에 세미콜론(;) 사용
값 할당에 ‘=‘ 가 아닌 ‘:=‘를 사용
IF … END IF
EXCEPTION은 생략 가능
결과 메시지가 나오지 않을 경우, 첫번째 줄에 다음 명령어 입력
SET SERVEROUTPUT ON;
DATABASE LABORATORY
PL/SQL 예제
DECLARE
counter INTEGER;
i INTEGER;
BEGIN
FOR i IN 1..10 LOOP
counter := (2*i);
dbms_output.put_line(‘2*’ || i || ‘ = ‘ || counter);
END LOOP;
END;
실습 : 구구단 프로그래밍하기
DATABASE LABORATORY
예외처리부
DATABASE LABORATORY
예외처리부
EXCEPTION WHEN 예외1 THEN 예외처리1
WHEN 예외2 THEN 예외처리2
…
WHEN OTEHRS THEN 예외처리
EXCEPTION
오라클에서 미리 정의된 내용
사용자가 직접 정의해서 사용할 수 있는 EXCEPTION
DATABASE LABORATORY
예외처리부
예외 내용
예외 번호
SQLCOD
E
발생시점
ACCESS_INTO_NULL
ORA-06530
-6530
초기화 되지 않은 오브젝트에 값을 할당
COLLECTION_IS_NULL
ORA-06531
-6531
초기화 되지 않은 중첩 테이블이나 VARRAY
같은 콜렉션을 EXISTS 외의 다른 메소드로
접근을 시도
DUP_VAL_ON_INDEX
ORA-00001
-1
유일 인덱스가 걸린 컬럼에 중복 데이터를 입력
LOGIN_DENIED
ORA-01017
-1017
잘못된 사용자나 비밀번호로 로그인 시도
NOT_LOGGED_ON
ORA-01012
-1012
오라클에 연결되지 않은 경우
ZERO_DIVIDE
ORA-01476
-1476
제수가 0일 때 발생
DATABASE LABORATORY
예외처리부
DATABASE LABORATORY
변수와 상수
변수 선언
emp_num1 NUMBER(9);
grade CHAR(2);
emp_num2 INTEGER := 1;
상수 선언
nYear CONSTANT INTEGER := 30; (o)
nYear CONSTANT INTEGER;
(x)
DATABASE LABORATORY
변수와 상수
%TYPE
참조할 테이블에 있는 컬럼의 데이터 타입을 가져옴
변수명 테이블명.컬럼명%TYPE
%ROWTYPE
하나 이상의 값에 적용
일반 변수에는 사용할 수 없다.
컬렉션이나 OBJECT 타입 변수에서만 사용 가능
DATABASE LABORATORY
레코드
배열 형태와 다르게 여러 개의 데이터 타입으로 구성될 수 있음
PL/SQL에서 사용하는 테이블 형태의 데이터 타입
구문형식
TYPE 레코드이름 IS RECORD (필드1 데이터타입1, 필드 2 데이터타입2,…)
레코드이름 테이블명%ROWTYPE;
레코드이름 커서명%ROWTYPE;
DATABASE LABORATORY
레코드
member 테이블의 스키마
DATABASE LABORATORY
레코드
DATABASE LABORATORY
IF 문
IF 조건 THEN
처리문;
END IF;
IF 조건 THEN
처리문 1;
ELSE
처리문 2;
END IF;
IF 조건1 THEN
처리문 1;
ELSIF 조건2 THEN
처리문 2;
…
ELSE
처리문 n;
END IF;
DATABASE LABORATORY
IF 문
DATABASE LABORATORY
CASE 문
DATABASE LABORATORY
NULL 문
아무것도 처리하지 않겠다는 것을 나타낸다.
DATABASE LABORATORY
LOOP 문
LOOP
처리문장들…
END LOOP;
간단하지만 내부에 처리문장만 나열한다면 무한루프에 빠진다.
‘EXIT;’ 을 사용해서 반복문을 종료하는 구문을 사용해야 한다.
DATABASE LABORATORY
LOOP 문
DATABASE LABORATORY
WHILE-LOOP 문
WHILE 조건 LOOP
처리문장들;
END LOOP;
DATABASE LABORATORY
FOR-LOOP
FOR 카운터 IN [REVERSE] 최소값..최대값 LOOP
처리문장들;
END LOOP;
카운터 값은 최솟값부터 시작해서 최대값까지 반복
루프를 1씩 증가하면서 실행
DATABASE LABORATORY
FOR-LOOP
DATABASE LABORATORY
GOTO 문
해당 라벨로 바로 이동한다.
DATABASE LABORATORY
커서(CURSOR)
쿼리에 의해 반환되는 결과는 메모리 상에 위치하게 되는데 커서(cursor)를 이용
하여 결과집합에 접근할 수 있음.
커서 선언
CURSUR 커서명 IS SELECT 문장;
커서 열기(open)
OPEN 커서명;
패치(fetch)
FETCH 커서명 INTO 변수…;
커서 닫기(close)
CLOSE 커서명;
DATABASE LABORATORY
커서(CURSOR)
DATABASE LABORATORY
커서(CURSOR)
%FOUND
커서가 막 오픈된 상태에서는 NULL 값을 반환한다. 오픈되고 첫 번째 패치가 발생한 이후부터는 TRUE 값을 반환하
며 더 이상 패치할 로우가 없을 경우 FALSE를 반환한다.
%NOTFOUND
더 이상 패치(할당)할 로우가 없음을 의미한다. %FOUND와 반대
%ISOPEN
커서가 오픈된 상태일 경우 TRUE 값을 반환한다.
%ROWCOUNT
카운터(counter) 역할을 한다. 커서가 막 오픈되었을 때에는 0, 패치될 때마다 1씩 증가한다.
DATABASE LABORATORY
함수
오라클에서 제공하는 SQL 함수가 아닌 사용자 정의 함수를 말한다.
CREATE OR REPLACE FUNCTION 함수명(파라미터1 데이터타입1,…)
RETURN INTEGER IS
변수선언…;
BEGIN
처리내용…;
RETURN 리턴값;
END;
DATABASE LABORATORY
함수
DATABASE LABORATORY
함수
DATABASE LABORATORY
프로시저
특정한 처리를 수행하는 서브 프로그램
함수와 달리 값을 반환하지 않음
CREATE OR REPLACE PROCEDURE 프로시저명 (파라미터1 데이터타입, …)
IS
변수선언부…;
BEGIN
프로시저 본문처리…;
EXCEPTION
예외처리…;
END;
DATABASE LABORATORY
프로시저
프로시저의 실행은 EXEC 또는 EXECUTE로 실행시킨다
DATABASE LABORATORY
과제
Member 테이블에 데이터를 수정, 삭제 할 수 있는 프로시저를 만드시오.
Member 테이블에서 Email을 얻어오는 함수를 작성하시오
DATABASE LABORATORY