set serveroutput on;
**제어문(조건문)
DECLARE
v_no NUMBER := 7;
BEGIN
--단수 IF ~ END IF
/*IF v_no = 7 THEN
dbms_output.put_line('7입니다.');
END IF;
END;*/
--IF ~ ELSE ~END IF
/*IF v_no = 5 THEN
dbms_output.put_line('5입니다.');
ELSE
dbms_output.put_line('5가 아닙니다.');
END IF;
END; */
--다중 IF ~ ELSIF ~ END IF
IF v_score > 90 THEN
dbms_output.put_line('A학점');
ELSIF v_score > 80 THEN
dbms_output.put_line('B학점');
ELSIF v_score > 70 THEN
dbms_output.put_line('C학점');
ELSE
dbms_output.put_line('F학점');
END IF;
END;
퀴즈hr> 난수구하기 => ROUND(DBMS_RANDOM.VALUE(10, 120),-1)
10~120 중 임의의 부서번호를 받아서 해당 부서의 평균급여에 따라서 등급이 출력되도록 하자.
1~3000 낮음
3000~6000 보통
6000이상 높음
->DECLARE
v_avgSal employees.salary%TYPE;
BEGIN
SELECT avg(salary)
INTO v_avgSal
FROM employees
WHERE department_id = ROUND(DBMS_RANDOM.VALUE(10, 120),-1);
/* GROUP BY department_id
HAVING department_id = ROUND(DBMS_RANDOM.VALUE(10, 120),-1); */
IF v_avgSal >=6000 THEN
dbms_output.put_line('높음');
ELSIF v_avgSal >=3000 THEN
dbms_output.put_line('보통');
ELSE
dbms_output.put_line('낮음');
END IF;
END;
-CASE WHEN v_salary BETWEEN 1 AND 3000 THEN
dbms_output.put_line('낮음');
WHEN v_salary BETWEEN 3000 AND 6000 THEN
dbms_output.put_line('보통');
ELSE
dbms_output.put_line('높음');
END CASE;
END;
**LOOP문
DECLARE
i NUMBER :=0;
BEGIN
LOOP
i := i+1;
--조건
EXIT WHEN i>10;
dbms_output.put_line(i);
END LOOP;
END;
**WHILE문
DECLARE
i NUMBER :=0;
BEGIN
WHILE i<10 LOOP
i:= i+1;
dbms_output.put_line(i);
END LOOP;
END;
**FOR문
DECLARE
i NUMBER :=0;
BEGIN
FOR i IN 1..10 LOOP
dbms_output.put_line(i);
END LOOP;
END;
퀴즈> LOOP => 구구단 3단 출력
FOR => 구구단 전체 출력(중첩for문)
->DECLARE
i NUMBER :=0;
BEGIN
LOOP
i := i+1;
--조건
EXIT WHEN i>9;
dbms_output.put_line(3*'||i||'='||3*i);
END LOOP;
END;
->DECLARE
i NUMBER :=0;
j NUMBER :=0;
BEGIN
FOR i IN 1..9 LOOP
FOR j IN 1..9 LOOP
dbms_output.put_line(i||'*'||j||'='||i*j);
END LOOP;
dbms_output.put_line('');
END LOOP;
END;
**예외(EXCEPTION)
-PL/SQL의 오류를 예외라고 한다.
-컴파일 시 문법적 오류, 실행시 발생하는 오류
-미리 정의된 오라클 서버예외 : 선언할 필요 없고, 발생시 예외절로 자동 이동
-사용자 예외를 강제 발생 : 선언부에서 예외 정의, 실행부에서 RAISE문 사용
-미리 정의된 서버오류 처리
DECLARE
employee_record employees%ROWTYPE;
BEGIN
SELECT employee_id, last_name, department_id
INTO employee_record.employee_id,
employee_record.last_name,
employee_record.department_id
FROM employees
WHERE department_id = 50; //50번 부서에 사람이 너무 많아.
ROWTYPE은 한 줄만 입력할 수 있어서 오류 뜸.
dbms_output.put_line('사번' || employee_record.employee_id);
dbms_output.put_line('사원명' || employee_record.last_name);
dbms_output.put_line('부서번호' || employee_record.department_id);
EXCEPTION
--UNIQUE 제약을 갖는 컬럼에 중복된 데이터를 insert시
WHEN DUP_VAL_ON_INDEX THEN
dbms_output.put_line('이미 존재하는 사원 입니다.');
--SELECT문 결과가 2개 이상 로우를 반환
WHEN TOO_MAMY_ROWS THEN
dbms_output.put_line('검색된 로우가 너무 많습니다.');
--데이터 없을 때
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('검색된 사원이 없습니다.');
--그 밖에 예외 이유
WHEN OTHERS THEN
dbms_output.put_line('몰라용');
END;
예외 > "exact fetch returns more than requested number of rows"
--사용자가 강제로 예외 발생
DECLARE
e_user_exception EXCEPTION; --예외 정의
cnt NUMBER :=0;
BEGIN
SELECT COUNT(employee_id) INTO cnt
FROM employees
WHERE department_id = 40;
If cnt<5 THEN
RAISE e_user_exception;--인위적으로 예외 발생
END IF;
EXCEPTION
WHEN e_user_exception THEN
dbms_output.put_line('5명 이하 부서 금지');
END;
퀴즈 hr>신입사원을 입력시 잘못된 부서번호에 대해서 사용자 예외처리 하세요.(employees2)
INSERT INTO employees2(employee_id, first_name,last_name,email, hire_date, job_id,department_id) VALUES (v_max_id+1,'Gildong','Hong','aa@aa.com',sysdate,'AD_UP',100000);
->
DECLARE
p_department_id NUMBER := 1000;
v_cnt NUMBER :=0;
v_employee_id employees2.employee_id%TYPE;
ex_invalid_deptid EXCEPTION;
BEGIN
SELECT COUNT(*) INTO v_cnt
FROM employees2
WHERE department_id = p_department_id;
IF v_cnt = 0 THEN //사원수가 없으면
RAISE ex_invalid_deptid; //잘못된 사원번호
END IF;
SELECT MAX(employee_id) +1
INTO v_employee_id
FROM employees2;
INSERT INTO employees2(employee_id, first_name,last_name,email, hire_date, job_id,department_id)
VALUES (v_employee_id,'bb','a','aa@aa.com',sysdate,'AD_UP',100000);
COMMIT;
EXCEPTION
WHEN ex_invalid_deptid THEN
dbms_output.put_line('잘못된 부서번호입니다.');
WHEN OTHERS THEN
dbms_output.put_line('그밖의 오류');
END;
**커서(CURSOR) with ROWTYPE
-질의결과가 다중로우일 경우 반드시 커서를 사용한다.
DECLARE
--커서 선언
CURSOR department_cursors IS
SELECT department_id, department_name, location_id
FROM departments;
department_record department_cursors%ROWTYPE; --커서에 있는 내용 rowtype으로 하나씩 가져옴
BEGIN
--커서 열기
OPEN department_cursors;
LOOP
FETCH department_cursors
INTO department_record.department_id,
department_record.department_name,
department_record.location_id;
EXIT WHEN department_cursors%NOTFOUND;
dbms_output.put_line(department_record.department_id
||' '|| department_record.department_name
||' '||department_record.location_id );
END LOOP;
CLOSE department_cursors;
END;
퀴즈hr >커서를 이용하여 사원의 정보를 출력하라.
(사원번호, 사원이름, 급여, 급여 누계)
->LOOP
DECLARE
CURSOR employees2_cursors IS
SELECT employee_id, last_name, salary
FROM employees2;
employees2_record employees2_cursors%ROWTYPE;
v_sum NUMBER := 0;
BEGIN
SELECT sum(salary)
INTO v_sum
FROM employees2;
OPEN employees2_cursors;
LOOP
FETCH employees2_cursors
INTO employees2_record.employee_id,
employees2_record.last_name,
employees2_record.salary;
EXIT WHEN employees2_cursors%NOTFOUND;
v_sum := v_sum + employees2_record.salary;
dbms_output.put_line(employees2_record.employee_id
||' '|| employees2_record.last_name
||' '||employees2_record.salary
||' '|| v_sum);
END LOOP;
CLOSE employees2_cursors;
END;
(강사님코드)DECLARE
CURSOR employee_cursors IS
SELECT employee_id, first_name, salary
FROM employees;
employee_record employee_cursors%ROWTYPE;
v_totalsalary NUMBER := 0;
BEGIN
OPEN employee_cursors;
dbms_output.put_line('==========================');
dbms_output.put_line('사원번호, 사원이름, 급여, 급여누계');
dbms_output.put_line('==========================');
LOOP
FETCH employee_cursors INTO employee_record;
EXIT WHEN employee_cursors %NOTFOUND;
v_totalsalary := v_totalsalary + employee_record.salary;
dbms_output.put_line(employee_record.employee_id
||', '|| employee_record.first_name||', '||
employee_record.salary||', '|| v_totalsalary);
END LOOP;
dbms_output.put_line(employee_cursors%rowcount||'행이 검색');
CLOSE employee_cursors;
-FOR.. IN
DECLARE
CURSOR employee_cursors IS
SELECT employee_id, first_name, salary
FROM employees;
employee_record employee_cursors%ROWTYPE;
v_totalsalary NUMBER := 0;
BEGIN
dbms_output.put_line('==========================');
dbms_output.put_line('사원번호, 사원이름, 급여, 급여누계');
dbms_output.put_line('==========================');
FOR employee_record IN employee_cursors LOOP
v_totalsalary := v_totalsalary + employee_record.salary;
dbms_output.put_line(employee_record.employee_id
||', '|| employee_record.first_name||', '||
employee_record.salary||', '|| v_totalsalary);
END LOOP;
END;
**프로시저(Procedure)
-자주 사용하는 PL/SQL 블록을 재사용하기 위해 모듈화한 것
문제> 부서번호를 받아 사원리스트 처리
--프로시저 생성(메소드의 시그니처정도)
CREATE OR REPLACE PROCEDURE listByDeptno(p_deptno
IN employees.department_id%TYPE)
IS
CURSOR employee_cursors IS
SELECT * FROM employees
WHERE department_id = p_deptno;
employee_record employee_cursors%ROWTYPE;
BEGIN
dbms_output.put_line('============사원리스트===========');
FOR employee_record IN employee_cursors LOOP
dbms_output.put_line(p_deptno ||' '||
employee_record.employee_id ||' '||
employee_record.last_name);
END LOOP;
END;
--프로시저 실행
EXECUTE listByDeptno(100);
퀴즈 hr> 기존 jobs => jobs2 복사
프로시저를 이용하여 job_id, job_title, min_salary, max_salary
입력받아 테이블에 새로운 row추가해보자.
->CREATE TABLE jobs2 AS SELECT * FROM jobs;
CREATE OR REPLACE PROCEDURE addList(
p_job_id IN jobs2.job_id%TYPE,
p_job_title IN jobs2.job_title%TYPE,
p_min_salary IN jobs2.min_salary%TYPE,
p_max_salary IN jobs2.max_salary%TYPE)
IS
BEGIN
INSERT INTO jobs2 VALUES(p_job_id, p_job_title,p_min_salary, p_max_salary);
END;
EXECUTE addList('a','b',100,1000);
<mission hr> job2 => job_id 제약조건(pk) 추가
동일한 job_id를 체크
No => INSERT 실행
Yes => UPDATE 실행
ALTER TABLE job2
ADD CONSTRAINT job2_job_id_pk PRIMARY KEY;
CREATE OR REPLACE PROCEDURE job_proc2(
p_job_id IN jobs2.job_id%TYPE,
p_job_title IN jobs2.job_title%TYPE,
p_min_salary IN jobs2.min_salary%TYPE,
p_max_salary IN jobs2.max_salary%TYPE)
IS
BEGIN
IF p_job_id %NOTFOUND THEN
INSERT INTO jobs2 VALUES(p_job_id, p_job_title,p_min_salary, p_max_salary);
ELSE
UPDATE jobs2 SET VALUES(p_job_id, p_job_title,p_min_salary, p_max_salary)
WHERE p_job_id = job_id ;
END IF;
END;
EXECUTE addList('a','b',100,1000);
(강사님 코드)
ALTER TABLE jobs2
ADD CONSTRAINT job2_job_id_pk PRIMARY KEY(job_id);
CREATE OR REPLACE PROCEDURE my_new_job_proc2(
p_job_id IN jobs2.job_id%TYPE,
p_job_title IN jobs2.job_title%TYPE,
p_min_salary IN jobs2.min_salary%TYPE,
p_max_salary IN jobs2.max_salary%TYPE)
IS
v_cnt NUMBEr := 0;
BEGIN
--동일한 job_id 체크
SELECT COUNT(*) INTO v_cnt
FROM jobs2
WHERE job_id = p_job_id;
IF v_cnt = 0 THEN
INSERT INTO jobs2(job_id, job_title, min_salary, max_salary)
VALUES(p_job_id, p_job_title, p_min_salary, p_max_salary);
ELSE
UPDATE jobs2
SET job_title = p_job_title,
min_salary = p_min_salary,
max_salary = p_max_salary
WHERE job_id = p_job_id;
END IF;
COMMIT;
END;
EXECUTE my_new_job_proc2('a', 'a111', 111000, 555000);
v(변수)VS p(받아서 쓰는 매개변수)
--매개변수 디폴트 값 설정
CREATE OR REPLACE PROCEDURE my_new_job_proc3(
p_job_id IN jobs2.job_id%TYPE,
p_job_title IN jobs2.job_title%TYPE,
p_min_salary IN jobs2.min_salary%TYPE := 100,
p_max_salary IN jobs2.max_salary%TYPE := 1000)
IS
v_cnt NUMBEr := 0;
BEGIN
--동일한 job_id 체크
SELECT COUNT(*) INTO v_cnt
FROM jobs2
WHERE job_id = p_job_id;
IF v_cnt = 0 THEN
INSERT INTO jobs2(job_id, job_title, min_salary, max_salary)
VALUES(p_job_id, p_job_title, p_min_salary, p_max_salary);
ELSE
UPDATE jobs2
SET job_title = p_job_title,
min_salary = p_min_salary,
max_salary = p_max_salary
WHERE job_id = p_job_id;
END IF;
COMMIT;
END;
EXECUTE my_new_job_proc3('a2', 'a222');
--OUT, IN 매개변수 사용
CREATE OR REPLACE PROCEDURE my_new_job_proc4(
p_job_id IN jobs2.job_id%TYPE, (IN : 파라미터값을 던지기 위해서)
p_job_title IN jobs2.job_title%TYPE,
p_min_salary IN jobs2.min_salary%TYPE := 100,
p_max_salary IN jobs2.max_salary%TYPE := 1000,
p_result OUT NUMBER) (out이 있으면 파라미터값 다 넣어줘야 함.)
IS
v_cnt NUMBER := 0;
BEGIN
--동일한 job_id 체크
SELECT COUNT(*) INTO v_cnt
FROM jobs2
WHERE job_id = p_job_id;
IF v_cnt = 0 THEN
p_result :=1;
INSERT INTO jobs2(job_id, job_title, min_salary, max_salary)
VALUES(p_job_id, p_job_title, p_min_salary, p_max_salary);
ELSE
p_result :=2;
UPDATE jobs2
SET job_title = p_job_title,
min_salary = p_min_salary,
max_salary = p_max_salary
WHERE job_id = p_job_id;
END IF;
COMMIT;
END;
DECLARE
p_result NUMBER;
BEGIN
my_new_job_proc4('c', 'c1111',111000,555000,p_result); --out(값을 return해줌)
dbms_output.put_line(p_result);
END;
1 (out 값 출력됨.)
--함수(Function)
~프로시저에 비해 실행 결과를 더 유연하게 사용 가능 -> 기존 sql문에도 사용 가능.
CREATE OR REPLACE FUNCTION getSalary(
p_no employees.employee_id%TYPE)
RETURN NUMBER IS
v_salary NUMBER;
BEGIN
SELECT salary INTO v_salary
FROM employees
WHERE employee_id = p_no;
RETURN v_salary;
END;
SELECT getSalary(100) FROM dual;
퀴즈> 사번을 입력 받아 이름을 반환하는 함수를 구현하자.
없으면 => 해당사원 없음
CREATE OR REPLACE FUNCTION getEmpID(
p_no employees.employee_id%TYPE)
RETURN VARCHAR2 IS
v_name VARCHAR2(20);
BEGIN
SELECT last_name INTO v_name
FROM employees
WHERE employee_id = p_no;
RETURN v_name;
END;
SELECT getEmpID(100) FROM dual;
(승히언니 코드)
CREATE OR REPLACE FUNCTION getName(
p_employee_id employees.employee_id%TYPE)
RETURN employees.last_name%TYPE IS
v_name employees.last_name%TYPE;
v_cnt NUMBER:= 0;
BEGIN
SELECT COUNT(*) INTO v_cnt
FROM employees
WHERE employee_id = p_employee_id;
IF v_cnt!=0 THEN
SELECT last_name INTO v_name
FROM employees
WHERE EMPLOYEE_ID=p_employee_id;
RETURN v_name;
ELSE
RETURN ('해당 사원 없음');
END IF;
END;
(강사님코드)
CREATE OR REPLACE FUNCTION get_emp_name (
p_employee_id employees.employee_id%TYPE)
RETURN VARCHAR2
IS
vs_emp_name employees.last_name%TYPE;
result VARCHAR2(50):=null;
BEGIN
-- 사원명을 가져온다.
SELECT last_name
INTO vs_emp_name
FROM employees
WHERE employee_id = p_employee_id;
-- 사원명 반환
RETURN result;
EXCEPTION
WHEN NO_DATA_FOUND THEN
return '해당사원없음';
END;
SELECT get_emp_name(1000) FROM dual;
'FULLSTACK > DB' 카테고리의 다른 글
| DB 8차시 - ER다이어그램, 정규화, DB프로젝트 (0) | 2020.11.13 |
|---|---|
| DB 7차시 - 패키지, 트리거, 데이터모델링, ER다이어그램 (0) | 2020.11.13 |
| DB 5차시 - 인덱스, 뷰, 시퀀스, PL/SQL (0) | 2020.11.13 |
| DB 4차시 - 서브쿼리문 (0) | 2020.11.13 |
| DB 3차시 - 조인(SELF, OUTER) (0) | 2020.11.13 |