3 PLSQL PLSQL u l w BINARYINTEGER 2
3. PL/SQL
PL/SQL의 기초 u 데이타 타입 l 숫자타입 w BINARY_INTEGER : -2, 147, 483, 647 ~ 2, 147, 483, 647사이의 정수 SUBTYPE NATURAL : 음수를 제외한 정수(0 ~ 2, 147, 483, 647) POSITIVE : 양의 정수(1 ~ 2, 147, 483, 647) SIGNTYPE : -1, 0 , 1의 값만 가짐 w NUMBER : 10 -130 ~ 10125사이의 고정소수점이나 부동소수점 수 SUBTYPE DEC, DECIMAL, NUMERIC : 최대 38자리의 고정 소수점 수 DOUBLE PRECISION, FLOAT: 최대 126자리 이진부동소수점 수 REAL : 최대 63자리 이진 부동 소수점 수 INTEGER, INT, SMALLINT : 최대 38자리의 정수 w PLS_INTEGER : -231 ~ 231 사이의 정수
선언부 u 변수선언 l 한줄에 하나의 변수만 선언 가능 DECLARE v_job v_count v_total_sal v_hiredate v_valid BEGIN l VARCHAR 2(9); BINARY_INTEGER : = 0; NUMBER(9, 2) : = 0; DATE; BOOLEAN : = FALSE; DEFAULT의 사용 w : = 연산자 대신에 키워드 DEFAULT를 사용하여 변수에 초기값을 할당 DECLARE blood_type CHAR : = ‘O’; -- blood_type CHAR DEFAULT ‘O’ BEGIN
선언부 u 변수선언 l NOT NULL의 사용 : 초기값을 할당하는 것 이외에, NOT NULL 제약 을 변수에 부여(완전한 구문이아님에 주의) DECLARE v_emp_id NUMBER(4) NOT NULL : = 9999; --적합 v_dept_no NUMBER(2) NOT NULL; -- 에러 BEGIN l %TYPE 속성의 사용 w v_student_name은 이미 선언된 변수 v_name과 동일한 VARCHAR 2(10) 으 로 선언 DECLARE v_name VARCHAR 2(10); v_student_name v_name%TYPE; v_st_name v_name%TYPE : = ‘scott’ BEGIN
선언부 u 변수선언 l %ROWTYPE 속성의 사용 w Student 테이블이 두 애트리뷰트 name, grade를 포함하고 있을 때, 아래 선언의 결과로 v_student_rec와 v_stu_rec변수는 student 테이 블의 레코드와 동일한 복합 변수로 선언 w 아래와 같은 SELECT 명령에서의 v_stu_rec와 같이 %ROWTYPE 속성은 SELECT 명령에서 행을 가져 올 때 매우 유용 DECLARE v_student_rec student%ROWTYPE; v_stu_rec student%ROWTYPE; BEGIN v_student_rec. name : = ‘JOHNSON’; v_student_rec. grade : = ‘A’; ⋮ SELECT * INTO v_stu_rec FROM student WHERE. . . ⋮ END;
선언부 u 상수선언 l 키워드 CONSTANT를 명시하고 반드시 기본값을 지정 DECLARE c_num CONSTANT NUMBER : = 100; BEGIN c_num : = 200; --에러 END;
실행부 u SQL 명령 l INSERT, UPDATE, DELETE SQL 명령으로 데이타를 삽입 , 갱신, 삭제 가능 BEGIN INSERT INTO emp(empno, ename) VAULES(8288, ‘TEST’); ⋮ UPDATE dept SET dname = ‘EDUCATION’ WHERE deptno = 10; ⋮ DELETE FROM emp WHERE deptno = 20; ⋮ END;
실행부 u IF문 l IF – THEN IF 조건 THEN 문; END IF; l IF – THEN – ELSE IF 조건 THEN 문 1; ELSE 문 2; END IF; l IF 조건 1 THEN 문 1; ELSEIF 조건 2 THEN 문 2; [ELSEIF 조건 3 THEN IF – THEN – ELSIF 문 3; ] … [ELSE 문 4; ] END IF;
실행부 u NULL 문 l NULL 문은 아무 일도 하지 않는다는 사실을 명시적으로 나타냄 IF rating > 90 THEN compute_bonus(emp_id); ELSE NULL; END IF;
실행부 u 커서의 조작 l 명시적 커서의 속성 w %FOUND 커서를 오픈했을 때 첫번째 FETCH가 실행되기 전에 %FOUND는 NULL값 BEGIN … LOOP FETCH c 1 INTO my_ename, my_sal, my_hiredate; IF c 1%FOUND THEN -- FETCH의 성공 … -- 데이타의 처리 ELSE EXIT; END IF; END LOOP; … END;
실행부 u 커서의 조작 w %NOTFOUND는 %FOUND와 논리적으로 반대 의미 가장 최근에 FETCH가 행을 반환하면 %NOTFOUND는 거짓 가장 최근의 FETCH가 행을 반환하는데 실패하면 참 BEGIN LOOP FETCH c 1 INTO my_ename, my_sal, my_hiredate; EXIT WHEN c 1%NOTFOUND; … END LOOP; … END;
예외 상황 처리부 u 미리 정의된 예외 상황 Oracle Error SQL CODE 값 No_Data_Found ORA 01403 +100 행이 한건도 반환되지 않음 Too_Many_Rows ORA 01422 -1422 행이 두개 이상 반환 Zero_Divide ORA 01476 -1476 제수가 0일 때 Invalid_Number ORA 01722 -1722 숫자 형태가 아닌 값을 숫자변수에 대입할 때 Login_Denied ORA 01017 -1017 잘못된 userid/password로 접속을 시 도 했을 때 Not_Logged_On ORA 01012 -1012 오라클에 접속하지 않고 PL/SQL을 수행했을 때 Others 내용 위에서 명세되지 않은 예외 상황
예외 상황 처리부 u 예외 상황 전파 l 에러 발생시 가장 가까운 예외 상황 처리부를 찾아 실행 DECLARE temp NUMBER; v_sal NUMBER; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = 7900; -- 무명 블록의 시작 DECLARE v_date DATE; BEGIN SELECT hiredate INTO v_date FROM emp WHERE deptno = 20; DBMS_OUTPUT. PUT_LINE(‘v_date : ’ || v_date); EXCEPTION WHEN No_Data_Found THEN DBMS_OUTPUT. PUT_LINE(‘데이타 없음’); END; -- 무명 블록의 끝 temp : = v_sal * 12; DBMS_OUTPUT. PUT_LINE(‘temp : ’ || temp); EXCEPTION WHEN Too_Many_Rows THEN DBMS_OUTPUT. PUT_LINE(‘중복 데이타’); END;
서브프로그램 u 함수 l l 직업이 title이고 봉급이 salary일 때, sals 테이블에 기록된 최소 봉급과 최대 봉 급 범위에 salary가 해당하는지 여부를 나타내는 예제 함수 이름 sal_ok는 RETURN 명령에 의해 Boolean 값을 가짐 FUNCTION sal_ok(salary REAL, title VARCHAR 2) RETURN BOOLEAN IS min_sal REAL; max_sal REAL; BEGIN SELECT low_sal, high_sal INTO min_sal, max_sal FROM sals WHERE job = title; RETURN (salary >= min_sal) AND (salary <= max_sal); END sal_ok;
서브프로그램 u 서브 프로그램의 인자 l OUT w 값을 참조하지 않고, 서브 프로그램을 호출한 PL/SQL문에 값을 반 환하는데 사용 w 확인예제 PROCEDURE calc_bonus (emp_id IN INTEGER, bonus OUT REAL) IS hire_date DATE; BEGIN SELECT sal * 0. 10, hiredate INTO bonus, hire_date FROM emp WHERE empno = emp_id; IF MONTHS_BETWEEN(SYSDATE, hire_date) > 60 THEN bonus : = bonus + 500; -- 에러발생 END IF; END;
서브프로그램 u 서브 프로그램의 인자 l IN OUT w 인자값을 서브프로그램에 전달하거나 호출 PL/SQL문에 값을 반환시 사용 PROCEDURE calc_bonus (emp_id IN INTEGER, bonus IN OUT REAL) IS hire_date DATE; bonus_missing EXCEPTION; BEGIN SELECT sal * 0. 10, hiredate INTO bonus, hire_date FROM emp WHERE empno = emp_id; IF bonus IS NULL THEN RAISE bonus_missing; END IF; IF MONTHS_BETWEEN(sysdate, hire_date) > 60 THEN bonus : = bonus + 500; END IF; EXCEPTION WHEN bonus_missing THEN END calc_bonus;
- Slides: 50
