PLSQL PLSQL n l BINARYINTEGER 2 147 483

PL/SQL

PL/SQL의 기초 n 데이타 타입 l 숫자타입 ü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의 값만 가짐 6

선언부 n 변수선언 l 한줄에 하나의 변수만 선언 가능 DECLARE v_job v_count v_total_sal v_hiredate v_valid BEGIN VARCHAR 2(9); BINARY_INTEGER : = 0; NUMBER(9, 2) : = 0; DATE; BOOLEAN : = FALSE; 12

l DEFAULT의 사용 ü : = 연산자 대신에 키워드 DEFAULT를 사용하여 변수에 초기 값을 할당 DECLARE blood_type CHAR : = ‘O’; -- blood_type CHAR DEFAULT ‘O’ BEGIN 13

선언부 n 변수선언 l NOT NULL의 사용 : 초기값을 할당하는 것 이외에, NOT NULL 제 약을 변수에 부여 DECLARE v_emp_id NUMBER(4) NOT NULL : = 9999; --적합 v_dept_no NUMBER(2) NOT NULL; -- 에러 BEGIN l %TYPE 속성의 사용 ü 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 14

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; 16

선언부 n 상수선언 l 키워드 CONSTANT를 명시하고 반드시 기본값을 지정 DECLARE c_num CONSTANT NUMBER : = 100; BEGIN c_num : = 200; --에러 END; 17

구분 묵시적 커서 명시적 커서 생성 및 관리 DBMS 사용자 선언 SELECT, DML문에 의해 DECLARE CURSOR C 1 IS SELECT 명령; BEGIN 속성 %Not. Found %ISOpen(항상 False) %Row. Count %Not. Found %ISOpen %Row. Count 19

DECLARE CURSOR c 1 IS SELECT empno, ename, job, sal FROM emp; WHERE sal > 2000; CURSOR c 2 RETURN dept%ROWTYPE IS SELECT * FROM dept WHERE deptno = 10; BEGIN 21

실행부 n SQL 명령 l SELECT 명령에서 INTO 절을 사용하여 SELECT 명령 에 의해 반환되는 결과를 PL/SQL의 변수에 저장가능 l 아래의 예제는 s_dept테이블에서 id가 10인 부서이름 name 을 검색하여 그 값을 v_name에 지정 DECLARE v_name s_dept. name%TYPE; BEGIN SELECT name INTO v_name FROM s_dept WHERE id = 10; ⋮ END; 22

실행부 n 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; 23

실행부 n IF문 l IF – THEN IF 조건 THEN 문; END IF; l IF – THEN – ELSE IF 조건 THEN 문 1; ELSE 문 2; END IF; 24

l IF – THEN – ELSIF IF 조건 1 THEN 문 1; ELSEIF 조건 2 THEN 문 2; [ELSEIF 조건 3 THEN 문 3; ] … [ELSE 문 4; ] END IF; 25

LOOP … EXIT WHEN 조건; END LOOP; 27

실행부 n NULL 문 l NULL 문은 아무 일도 하지 않는다는 사실을 명시적으 로 나타냄 IF rating > 90 THEN compute_bonus(emp_id); ELSE NULL; END IF; 31

실행부 n 커서의 조작 l 명시적 커서의 속성 ü %FOUND BEGIN – 커서를 오픈했을 때 첫번째 FETCH가 실행되기 전에 %FOUND는 NULL … 값 LOOP FETCH c 1 INTO my_ename, my_sal, my_hiredate; IF c 1%FOUND THEN -- FETCH의 성공 … -- 데이타의 처리 ELSE EXIT; END IF; END LOOP; … END; 36

실행부 n 커서의 조작 ü %NOTFOUND – %NOTFOUND는 %FOUND와 논리적으로 반대 의미 BEGIN– 가장 최근에 FETCH가 행을 반환하면 %NOTFOUND는 거짓 – 가장 최근의 FETCH가 행을 반환하는데 실패하면 참 LOOP FETCH c 1 INTO my_ename, my_sal, my_hiredate; EXIT WHEN c 1%NOTFOUND; … END LOOP; … END; 38

예외 상황 처리부 예외 상황 Oracl 상황 SQL n 미리 정의된 예외 e Error 내용 CODE값 No_Data_Found ORA 01403 +100 행이 한건도 반환되지 않음 Too_Many_Row ORAs 01422 -1422 행이 두개 이상 반환 Zero_Divide ORA 01476 -1476 제수가 0일 때 Invalid_Number ORA 01722 -1722 숫자 형태가 아닌 값을 숫자 변수에 대입할 때 Login_Denied ORA 01017 -1017 잘못된 userid/password로 접속을 시도 했을 때 Not_Logged_On ORA- -1012 오라클에 접속하지 않고 41

예외 상황 처리부 n 예외 상황 전파 DECLARE가장 가까운 예외 상황 처리부를 찾아 실 l 에러 발생시 temp NUMBER; 행 v_salary NUMBER; BEGIN SELECT salary INTO v_salary FROM s_emp WHERE id = 1; DECLARE v_start_date DATE; v_date DATE; BEGIN SELECT start_date INTO v_date FROM s_emp WHERE id = 99; v_date : = v_start_date + 15; EXCEPTION WHEN No_Data_Found THEN message('데이타 없음'); END; temp : = v_salary * 12; EXCEPTION WHEN Too_Many_Rows THEN message('중복 데이타'); END; 43

서브프로그램 n 함수 l 직업이 title이고 봉급이 salary일 때, sals 테이블에 기록된 최소 봉급과 최대 봉급 범위에 salary가 해당하는지 여부를 나타내는 예제 l 함수 이름 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; 53

서브프로그램 n 서브 프로그램의 인자 l OUT ü 값을 참조하지 않고, 서브 프로그램을 호출한 PL/SQL문에 값 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; 58

서브프로그램 n 서브 프로그램의 인자 PROCEDURE l IN OUTcalc_bonus (emp_id IN INTEGER, bonus IN OUT REAL) IS hire_date서브프로그램에 DATE; ü 인자값을 전달하거나 호출 PL/SQL문에 값을 반환시 사용 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; 59

DECLARE CURSOR c 1 IS SELECT empno, ename, job, sal FROM emp WHERE sal > 2000; . . . BEGIN OPEN c 1; . . . END; 61

FETCH c 1 INTO my_empno, my_ename, my_deptno; BEGIN LOOP FETCH c 1 INTO my_record; EXIT WHEN c 1%NOTFOUND; … -- 데이타 레코드의 처리 END LOOP … END; 63

65

DECLARE CURSOR c 1 IS -- 커서의 선언 SELECT … BEGIN OPEN c 1; -- 커서 오픈 … FETCH c 1 INTO …; -- 데이타의 FETCH … CLOSE c 1; -- 커서 종료 END; 67

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; 69

BEGIN … IF c 1%ISOPEN THEN -- 커서가 오픈된 상태 … ELSE OPEN c 1; END IF … END; 71

BEGIN LOOP FETCH c 1 INTO my_ename, my_sal, my_hiredate; EXIT WHEN c 1%NOTFOUND; … END LOOP; … END; 73

BEGIN … LOOP FETCH c 1 INTO my_ename, my_sal, my_hiredate; IF c 1%ROWCOUNT > 10 THEN … -- 데이타의 처리 END IF; … END LOOP; … END; 75

예외 상황 Oracle Error SQL CODE값 No_Data_Found ORA 01403 +100 행이 한건도 반환되지 않음 Too_Many_Row s 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 내용 위에서 명세되지 않은 예외 상 황 78

DECLARE temp NUMBER; v_salary NUMBER; BEGIN SELECT salary INTO v_salary FROM s_emp WHERE id = 1; DECLARE v_start_date DATE; v_date DATE; BEGIN SELECT start_date INTO v_date FROM s_emp WHERE id = 99; v_date : = v_start_date + 15; EXCEPTION WHEN No_Data_Found THEN message('데이타 없음'); END; temp : = v_salary * 12; EXCEPTION WHEN Too_Many_Rows THEN message('중복 데이타'); END; 81

83

서브프로그램 n 프로시저 raise_salary(7844, 1000); 1. emp_audit 테이블 생성 86

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; 99

PROCEDURE debit_account (acct_id IN INTEGER, amount IN REAL) IS minimum_purchase CONSTANT REAL : = 10. 0; service_charge CONSTANT REAL : = 0. 50; BEGIN … IF amount < minimum_purchase THEN amount : = amount + service_charge; -- 컴파일 에러 발생 END IF; END; 106

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; 108

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; 110