[PL/SQL] PROCEDURE

1. PROCEDURE

- 여러개 값 반환 가능

- 반환값이 없어도 됨

- 반환할때 return이 아닌 out 사용, return은 강제 종료를 의미

- procedure 에서 return 할려면 out 사용후 정의문, 호출문 따로 만들어서 사용(begin end 두개 사용)

 

Q) 지구 몸무게를 입력하면 달 몸무게를 출력하는 프로시져 만들기

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE moon_pro(v_earth IN NUMBER)
IS
v_moon NUMBER;
BEGIN
v_moon := v_earth*(1/6);
dbms_output.put_line('달몸무게='||v_moon);
END;
/
EXEC moon_pro(70); 

결과: 달몸무게=11.66666666666666666666666666666666666667

 

Q) 사원아이디와 인상율을 입력받아 해당 사원의 급여를 인상율 만큼 인상하는 update 문장이 들어간 프로시져를 작성하시오 (입력으로 110과 10 이 들어온 경우라면 110번 사원의 급여를 10% 인상하는 경우이다)

 

CREATE OR REPLACE PROCEDURE upgrade_salary(v_emp_id employees.employee_id%type, v_up_salary NUMBER)
IS
BEGIN
UPDATE employees
SET salary = salary + salary*(v_up_salary/100)
WHERE employee_id=v_emp_id;
END;
/
SELECT * FROM employees where employee_id=150;
EXEC upgrade_salary(150,10);
SELECT * FROM employees where employee_id=150;

 

 

2. 프로시저로 값 반환하기

 

Q)사원수와 부서테이블의 행수를 조회 출력하시오, 두개의 프로시저를 사용, 하나는 정의, 하나는 호출, out 사용

--메소드 정의문
CREATE OR REPLACE PROCEDURE soo_count(v_emp_cnt OUT NUMBER, v_dept_cnt OUT NUMBER)
IS
BEGIN
SELECT count(*) INTO v_emp_cnt
FROM employees;
SELECT count(*) INTO v_dept_cnt
FROM departments;
END;
/
--메소드 호출문
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE soo_count_result
IS
v_emp_cnt NUMBER;
v_dept_cnt NUMBER;
BEGIN
soo_count(v_emp_cnt,v_dept_cnt);
--함수 안에서 함수 사용할때 exec 사용안함
dbms_output.put_line('v_emp_cnt='||v_emp_cnt||' v_dept_cnt='||v_dept_cnt);
END;
/
EXEC soo_count_result;

결과: v_emp_cnt=107 v_dept_cnt=27

 

 

3. %TYPE 사용하기(필드단위)

- 일일이 컬럼의 type을 지정하지 않고 테이블에 원래 있는 type을 적용시켜 사용
- (형식) 테이블명.컬럼명%TYPE
- %type attribute는 변수의 데이터 타입을 컬럼이나 어떤 변수의 데이터 타입과 같은 형태로 생성시 사용
- 장점으로는 유지보수가 쉽다 즉, 중간에 테이블의 컬럼 형식이 변경되어도 프로그램은 변경 없이 사용 (c언어에서 define max 500과 유사), 또는 데이터타입이 뭔지 정확히 몰라도 만들수가 있다
- 데이터 타입이 varchr2(20) 이었다가 나중에 varchr2(50) 으로 변경되면 테이블은 바꿔야 하지만 프로그램은 안바꿔도됨

 

Q) 사원을 입력하는 프로시저를 작성하시오

CREATE OR REPLACE PROCEDURE insert_teltable(v_id IN teltable.id%TYPE,v_name IN teltable.name%TYPE,v_tel IN teltable.tel%TYPE,v_d IN teltable.d%TYPE)
IS
BEGIN
INSERT INTO teltable
VALUES(v_id,v_name,v_tel,v_d);
COMMIT;
END;
/
EXECUTE insert_teltable(201,'ondal2','010-1234-1234','15/03/01');
SELECT * FROM teltable;

 

Q) id를 입력하여 해당 사원을 지우는 프로시저를 작성하시오

CREATE OR REPLACE PROCEDURE delete_teltable(v_id IN teltable.id%TYPE)
IS
BEGIN
DELETE FROM teltable
WHERE id=v_id;
COMMIT;
END;
/
EXECUTE delete_teltable(200);
SELECT * FROM teltable;

 

4. %ROWTYPE 사용하기(행단위)

- 원래 있는 table 및 view 컬럼명과 타입을 사용하게 된다
- (형식) 테이블명%rowtype

- %rowtype attribute의 장접은 %type과 유사하며 컬럼의 수가 많은 경우는 %type을 사용해도 선언이 길어지므로 %rowtype을 사용하는 편이 좋다
- 사용시 aa employees%rowtype 으로 선언하고 aa.first_name, aa.salary 등으로 사용한다

 

Q) 지역 아이디를 입력받아 해당 지역아이디의 모든 컬럼내용을 조회 출력하시오(%rowtype 사용)

CREATE OR REPLACE PROCEDURE f_regions(v_r_id IN regions.region_id%TYPE)
IS
v_regions regions%ROWTYPE;
BEGIN
SELECT region_id,region_name INTO v_regions
FROM regions
WHERE region_id=v_r_id;
dbms_output.put_line('v_regions.region_id='||v_regions.region_id||' v_regions.region_name='||v_regions.region_name);
END;
/
EXECUTE f_regions(1);

결과: v_regions.region_id=1 v_regions.region_name=Europe

 

5. 한 프로시저안에 여러개의 sql문 삽입 가능

 

Q) 현재 사원중 적정 인원을 다른 프로젝트에 재배치하고자 한다, 원하는 사원 중 급여 조건은 자기 부서의 최고금액(부서장급)을 주는 조건이다.  사원명과 부서아이디를 입력받아 출력결과로 사원명, 부서아이디, 급여를 새로운 테이블 special_table 에 입력하는 프로시저(special_project)를 작성

CREATE TABLE special_table (
    name VARCHAR2(20),
    department_id NUMBER(5),
    salary NUMBER(10)
);

CREATE OR REPLACE PROCEDURE special_project(v_name IN employees.first_name%TYPE,v_dept_id IN employees.department_id%TYPE)
IS
v_employees special_table%ROWTYPE;
BEGIN
SELECT max(salary) INTO v_employees.salary
FROM employees
WHERE first_name=v_name AND department_id=v_dept_id
GROUP BY department_id;

INSERT INTO special_table
VALUES(v_name,v_dept_id,v_employees.salary);
COMMIT;
END;
/
EXEC special_project('Nancy',100);
select * from special_table;

 

6. 조건문

- 조건문 사용 방법

IF 조건1 THEN

ELSIF 조건2 THEN

....

ELSE

END IF;

 

Q) 지구 몸무게를 입력하면 달 몸무게를 출력하는 프로시져 만들기(양수만 입력 가능하게 하기)

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE moon_pro(v_earth IN NUMBER)
IS
v_moon NUMBER;
BEGIN
IF v_earth<0 THEN
dbms_output.put_line('마이너스 집어넘');
RETURN;--강제 종료
ELSIF v_earth=0 THEN
dbms_output.put_line('0 집어넘');
END IF;
RETURN;
v_moon := v_earth*(1/6);
dbms_output.put_line('달몸무게='||v_moon);
END;
/
EXEC moon_pro(-200);

결과: 마이너스 집어넘

 

Q) 사원 아이디를 입력받아 급여가 5000이상이면 A, 그렇지 않으면 B를 출력하는 프로시저를 작성하시오 (출력은 사원아이디, 급여, 등급)

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE salary_5000(v_emp_id IN employees.employee_id%TYPE)
IS
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary
FROM employees
WHERE employee_id=v_emp_id;
IF v_salary >= 5000 THEN
dbms_output.put_line('사원아이디:'||v_emp_id||' 급여:'||v_salary||' 등급:'||'AAA');
ELSE 
dbms_output.put_line('사원아이디:'||v_emp_id||' 급여:'||v_salary||' 등급:'||'BBB');
END IF;
END;
/
EXEC salary_5000(101);

 

결과: 사원아이디:101 급여:17000 등급:AAA

 

Q) 사원 아이디를 입력받아 급여가 15000이상이면 A,10000~15000 이면 B, 5000~10000 이면 C그렇지 않으면 D를 출력하는 프로시저를 작성하시오 (출력은 사원아이디, 급여, 등급)

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE salary_grade(v_emp_id IN employees.employee_id%TYPE)
IS
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary
FROM employees
WHERE employee_id=v_emp_id;
IF v_salary >= 15000 THEN
dbms_output.put_line('사원아이디:'||v_emp_id||' 급여:'||v_salary||' 등급:'||'AAA');
ELSIF v_salary >= 10000 THEN
dbms_output.put_line('사원아이디:'||v_emp_id||' 급여:'||v_salary||' 등급:'||'BBB');
ELSIF v_salary >= 5000 THEN
dbms_output.put_line('사원아이디:'||v_emp_id||' 급여:'||v_salary||' 등급:'||'CCC');
ELSE
dbms_output.put_line('사원아이디:'||v_emp_id||' 급여:'||v_salary||' 등급:'||'DDD');
END IF;
END;
/
EXEC salary_grade(126);

결과: 사원아이디:126 급여:2700 등급:DDD

 

 

7. 반복문

- 사용법(LOOP)

LOOP

반복할 로직

EXIT WHEN 조건
END LOOP

 

Q) 1~100 까지 합 구하기

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE sum_100
IS
v_sum NUMBER(5) := 0;
v_count NUMBER(5) := 0;
BEGIN
LOOP
v_count := v_count + 1;
v_sum := v_sum + v_count;
EXIT WHEN v_count>=100;
END LOOP;
dbms_output.put_line('v_sum:'||v_sum);
END;
/
EXEC pro_sum;

결과: 5050

 

- for loop 반복문

Q) 1~100 까지 합 구하기

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE sum_100_forloop
IS
v_sum NUMBER(5):= 0;
BEGIN 
FOR i IN 1..100 LOOP 
v_sum := v_sum+ i;
END LOOP;
dbms_output.put_line('v_sum:'||v_sum);         
END;
/
EXEC sum_100_forloop;

결과: 5050

 

- while loop 반복문

Q) 1~100 까지 합 구하기

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE sum_100_whileloop
IS
v_cnt NUMBER(5):=0;
v_sum NUMBER(5):=0;
BEGIN 
WHILE v_cnt < 100 LOOP 
v_cnt := v_cnt+1;
v_sum := v_sum + v_cnt;
END LOOP;
dbms_output.put_line('v_sum:'||v_sum);         
END;
/
EXEC sum_100_whileloop;

결과: 5050

 

8. cursor

- cursor는 sql 커서를 말하며 multi row 저장관련 메모리 포인터(select 결과로 나오는 여러개의 행을 저장하는 종이박스의 개념)
- 기존 프로시저, 함수는 SELECT 사용시 한 row만 출력 및 사용이 가능하나 cursor를 사용하면 다중 row 출력 및 사용이 가능

- 커서의 종류: implicit cursor , explicit cursor
-- implicit cursor
-- 오라클(pl/sql)이 자동으로 생성 및 관리
-- sql%found: sql문장 수행 결과로 반환된 행수가 1개 이상이면 true
-- sql%notfound: sql문장 수행 결과로 반환된 행수가 없으면 true
-- sql%rowcount: sql문장 수행 결과로 반환된 행수를 알려줌
-- sql%isopen: 디폴트값이 false

Q) teltable 테이블에서 이름을 입력받아 해당 이름 데이터를 삭제하는 프로시저 작성, 삭제 후 comment도 출력하시오

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE delete_teltable_2(v_name teltable.NAME%TYPE)
IS
v_result VARCHAR2(30);
BEGIN 
DELETE FROM teltable
WHERE NAME=v_name;
IF SQL%notfound THEN
v_result := '없어';
ELSE  
v_result := to_char(SQL%rowcount) || '명';
END  IF; 
COMMIT;
--commit도 sql문장이라 SQL%notfound할때 개수 세짐, 그래서 이 위치에 둬야됨
dbms_output.put_line('삭제 인원은 ' || v_result || '  입니다');
END;
/
EXECUTE delete_teltable_2('박찬호');
SELECT * FROM teltable;

 

-- explicit cursor
-- 명시적으로 이름을 명명하는 커서(SELECT절로부터 가져온 데이터를 담는 곳)
-- cursor, open, fetch, close의 4단계로 구성된다
-- %found: cursor부터 data를 가져오는 fetch시 반환 행이 있으면 true
-- %notfound: cursor부터 data를 가져오는 fetch시 반환 행이 없으면 true
-- %rowcount: 반환된 행수
-- %isopen: cursor가 열려있으면 true (cursor 사용시 cursor open 후 사용 가능)
-- 자바의 ResultSet 같은 느낌

 

Q) 급여를 입력하여 해당 급여보다 많은 급여를 받는 사원의 이름, 급여, 입사일을
출력하고 몇명인지 인원수도 출력하는 프로시져를 작성하시오(cursor사용)

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE sawon_sal_gt(v_in_salary employees.salary%TYPE)
IS
v_first_name   employees.first_name%TYPE;
v_salary       employees.salary%TYPE;
v_hiredate     employees.hire_date%TYPE;
v_cnt          NUMBER(5) :=0;
CURSOR salary_gt IS --커서 정의
SELECT first_name, salary, hire_date  
FROM employees
WHERE salary > v_in_salary;
BEGIN 
OPEN salary_gt; --커서 오픈
LOOP
FETCH salary_gt INTO v_first_name, v_salary, v_hiredate;
EXIT WHEN salary_gt%notfound;
dbms_output.put_line('사원이름='||v_first_name||CHR(10)||'급여= '||v_salary||CHR(10)||'입사일='||v_hiredate);
v_cnt :=v_cnt+1; 
--몇명인지 구하기위해 
END LOOP;
dbms_output.put_line('사원인원수='||to_char(v_cnt));
CLOSE salary_gt; 
END;
/
EXEC sawon_sal_gt(10000);