Heesang kim dexaboud3@naver.com PL/SQL Heesang kim dexaboud3@naver.com

PL/SQL Procedural Language extension to SQL SQL문장에서 변수정의, 조건처리(IF), 반복처리(LOOP, WHILE, FOR) 등을 지원하는 Procedure Language 장점 BLOCK구조로 다수의 SQL문을 한번에 처리 (속도 향상) 모든 요소는 하나 또는 두 개 이상의 블록으로 구성하여 모듈화가 가능 VARIABLE, CONSTANT, CURSOR, EXCEPTION을 정의하고, SQL문장과 Procedural 문장에서 사용 한다. 테이블의 데이터 구조와 컬럼명에 준하여 동적으로 변수를 선언 가능

PL/SQL 기본 구조 논리적인 블록으로 나누는 구조화된 블록 언어 블록 구성 선언부 (선택) 실행부 (필수) 예외 처리부 (선택) DECLARE를 사용해서 나타냄 변수, 상수, 커서(CURSOR), 사용자 정의 Exception을 선언 실제 처리 로직을 담당(처리)하는 부분 BEGIN으로 시작되어 END로 끝남 일반적 오류를 정의하고 처리하는 부분 EXCEPTION 키워드 사용

PL/SQL 기본 구조 문장의 종료 시점에 세미콜론(;) 사용 값 할당에 ‘:=‘를 사용 단일행 주석: -- 여러행 주석: /* */ PL/SQL블록은 행에 ‘/’ 가 있으면 종결 결과 메시지를 확인하기 위해 명령어 입력 SET SERVEROUTPUT ON;

PL/SQL 블록 유형 Anonymous Block Procedure Function 프로그램 안에서 선언/실행 매개변수를 받고 반복적 사용 가능 보통 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL 블록을 데이터베이스에 저장하기 위해 생성 Function 보통 값을 계산하고 결과를 받기 위한 함수로 사용 RETURN문에 데이터 타입을 반드시 선언해야 함 RETURN 문을 통해서 값을 반환

Procedure과 function Procedure 서버에서 실행이 된다. 리턴값이 있어도 되고 없어도 된다.(out parameter로 처리) Select문에서는 호출이 불가능하다. 프로시저는 실행, 처리를 할때 주로 사용된다. Function 리턴값이 필수이고 하나만 된다. 피라미터는 IN값만 사용할 수 있다(default parameter’s option is IN) Select에서 호출이 가능하다. 클라이언트에서 처리한다. 주로 간단한 계산, 수치 이런 결과를 나타낼때 사용한다.

PL/SQL 블록 유형 Anonymous Block DECLARE counter INTEGER; -- counter INTEGER := 0; -- 초기화를 하지 않아 counter 는 NULL이다. BEGIN IF counter IS NULL THEN DBMS_OUTPUT.PUT_LINE('Result : COUNTER IS NULL'); END IF; END;

PL/SQL 블록 유형 Procedure CREATE OR REPLACE PROCEDURE testProcedure IS hello varchar(20) := 'Hello'; world varchar(20) := 'World'; BEGIN DBMS_OUTPUT.PUT_LINE(hello || ' ' || world); END testProcedure; / EXECUTE testProcedure; Begin문 안에서는 함수나 프로시저를 호출 할때 execute를 사용x

PL/SQL 블록 유형 Function CREATE OR REPLACE FUNCTION calc(n1 NUMBER, n2 NUMBER) RETURN NUMBER IS n NUMBER; BEGIN n := n1 + n2; RETURN n; END calc; / SELECT calc(1,3) FROM dual;

PL/SQL 블록 유형 Function CREATE OR REPLACE FUNCTION out_fn (outparm OUT VARCHAR2) RETURN VARCHAR2 IS BEGIN outparm := 'out param'; RETURN 'return param'; END out_fn; / -- 마지막에 꼭 넣어줘야 함 DECLARE retval VARCHAR2(20); outval VARCHAR2(20); BEGIN retval := out_fn(outval); DBMS_OUTPUT.put_line(outval); DBMS_OUTPUT.put_line(retval); END;

예외처리부 EXCEPTION WHEN 예외1 THEN 예외처리1 WHEN 예외2 THEN 예외처리2 … WHEN OTEHRS THEN 예외처리 EXCEPTION 오라클에서 미리 정의된 내용 사용자가 직접 정의해서 사용할 수 있는 EXCEPTION

예외처리부

예외처리부 예외 내용 예외 번호 SQLCODE 발생시점 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일 때 발생

예외처리부

IF 문 IF 조건 THEN 처리문; END IF; IF 조건 THEN 처리문 1; ELSE 처리문 2; END IF; IF 조건1 THEN 처리문 1; ELSIF 조건2 THEN 처리문 2; … ELSE 처리문 n; END IF;

IF 문

CASE 문

NULL 문 아무것도 처리하지 않겠다는 것을 나타냄

반복문 LOOP … END LOOP; 간단하지만 내부에 처리문장만 나열한다면 무한루프에 빠진다. ‘EXIT;’ 을 사용해서 반복문을 종료하는 구문을 사용해야 한다. WHILE 조건 LOOP … END LOOP; FOR 카운터 IN [REVERSE] 최소값..최대값 LOOP … END LOOP;

LOOP

WHILE-LOOP

FOR-LOOP

GOTO 해당 라벨로 바로 이동한다.

CURSOR 쿼리에 의해 반환되는 결과는 메모리 상에 위치하게 되는데 커서(cursor)를 이용 하여 결과집합에 접근할 수 있음.(간단하게 포인터라고 생각하면된다.) 커서 선언 CURSUR 커서명 IS SELECT 문장; 커서 열기(open) OPEN 커서명; 패치(fetch) FETCH 커서명 INTO 변수…; 커서 닫기(close) CLOSE 커서명;

CURSOR

CURSOR %FOUND %NOTFOUND %ISOPEN %ROWCOUNT 커서가 막 오픈된 상태에서는 NULL 값을 반환한다. 오픈되고 첫 번째 패치가 발생한 이후부터는 TRUE 값을 반환하 며 더 이상 패치할 로우가 없을 경우 FALSE를 반환한다. %NOTFOUND 더 이상 패치(할당)할 로우가 없음을 의미한다. %FOUND와 반대 %ISOPEN 커서가 오픈된 상태일 경우 TRUE 값을 반환한다. %ROWCOUNT 카운터(counter) 역할을 한다. 커서가 막 오픈되었을 때에는 0, 패치될 때마다 1씩 증가한다.

변수와 상수 변수 선언 상수 선언 emp_num1 NUMBER(9); grade CHAR(2); emp_num2 INTEGER := 1; 상수 선언 nYear CONSTANT INTEGER := 30; (o) nYear CONSTANT INTEGER; (x)

TYPE %TYPE %ROWTYPE 참조할 테이블에 있는 컬럼의 데이터 타입을 가져옴 변수명 테이블명.컬럼명%TYPE 하나 이상의 값에 적용 일반 변수에는 사용할 수 없다. 컬렉션이나 OBJECT 타입 변수에서만 사용 가능

RECORD 배열 형태와 다르게 여러 개의 데이터 타입으로 구성될 수 있음 (구조체와 비슷) PL/SQL에서 사용하는 테이블 형태의 데이터 타입 구문형식 TYPE 레코드이름 IS RECORD (필드1 데이터타입1, 필드 2 데이터타입2,…) 레코드이름 테이블명%ROWTYPE; 레코드이름 커서명%ROWTYPE;

RECORD DB 스키마 Id(PK) Password Name Email varchar(25) varchar(100) TABLE NAME : MEMBER (04.JDBC_Programming2.pptx) Id(PK) Password Name Email varchar(25) varchar(100)

RECORD

과제 구구단 전체를 출력하는 익명함수를 작성하세요. DECLARE counter INTEGER; BEGIN FOR i IN 1 .. 9 LOOP counter := 2 * i; DBMS_OUTPUT.PUT_LINE('2 * ' || i || ' = ' || counter); END LOOP; END; 입력 값을 받아, 2 ~ 9라면 구구단을 출력하고 그 외의 값일 경우 “2 ~ 9 사이의 정수 값이 와야 합니다.”를 출력하는 프로시저 “the_rules_of_multiplication”를 만드세요. EXECUTE the_rules_of_multiplication(5); EXECUTE the_rules_of_multiplication(1); EXECUTE the_rules_of_multiplication(10);

과제 Member 테이블에서 ID(PK)를 이용하여 name, password, email을 수정 할 수 있는 프로시저를 만드시오. Member 테이블에서 ID(PK)를 이용하여 Email을 얻어오는 함수를 작성하시오.