728x90
# PROCEDURE
l PLSQL의 종류
1. anonymous PL/SQL- 기본(fund)
2. procedure
3. function
4. trigger
5. package
n anonymous PL/SQL은 DB에 코드를 저장하지 않고 나머지는 코드를 저장한다
l 프로시져를 생성했을 때 장점
1. 반복된 코드를 단순화 할 수 있다.
똑같은 코드를 여러 번 사용해야할 때 코딩 양을 줄일 수 있다
2. 프로그램 코드를 다른 프로그램에 이식하기가 편하다
l 매개변수
- 입력용 : in
- 출력용 : out
- 입출력용 : inout
l exec pro2 : 프로시져 수행
l show err : 에러 출력
l 프로시져를 만들기 위해서는 set 구문삭제, declare 문장 삭제해야함
l 화살표를 써주면 서로 위치가 바뀌어도 수행된다
문제45. 위의 소스를 프로시져(pro2)로 만들어서 디버깅을 하시오
CREATE OR REPLACE PROCEDURE pro2
is
v_count NUMBER(10):=1;
v_count2 NUMBER(10):=0;
BEGIN
WHILE v_count<4 loop="" o:p="">
v_count := v_count + 1;
v_count2 := 0;
WHILE v_count2<9 loop="" o:p="">
v_count2 := v_count2 + 1;
dbms_output.put_line(v_count || '*' || v_count2 || '=' || v_count*v_count2);
END LOOP;
END LOOP;
END;
/
문제107. 아래의 소스를 프로시져로 생성하시오
ALTER TABLE EMP
ADD rnk NUMBER(10);
declare
cursor emp_cursor is
select ename, rank() over(order by sal desc) as "순위"
from emp;
begin
for emp_record in emp_cursor loop
update emp
emp2345.txt set rnk=emp_record.순위
where ename=emp_record.ename;
end loop;
end;
/
ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
CREATE OR REPLACE PROCEDURE PRO1
is
cursor emp_cursor is
select ename, rank() over(order by sal desc) as "순위"
from emp;
begin
for emp_record in emp_cursor loop
update emp
set rnk=emp_record.순위
where ename=emp_record.ename;
end loop;
end;
/
l 프로시져를 만드는 것과 실행하는 것은 틀리며 exec을 수행해야 한다. 프로시져를 만드는 것은 소스를 DB에 저장하는 것이고 실행하는 것은 소스를 실행하는 것이다.
문제108. DB에 저장된 pro1 코드를 확인하시오
select text
from user_source
where name='PRO1';
문제109. 아래의 소스를 프로시져로 생성하시오
accept p_job prompt '직업을 입력하세요'
declare
v_job emp.job%type := upper('&p_job');
v_sumsal number(10);
v_exception exception;
begin
select sum(sal) into v_sumsal
from emp
where job = v_job;
IF v_sumsal < 5000 then
raise v_exception;
else
insert into emp348
values ( v_job, v_sumsal);
end if;
EXCEPTION when v_exception then
raise_application_error (-20002, '월급이 너무 작아 출력불가');
end;
/
ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
CREATE OR REPLACE PROCEDURE PRO2
(p_job IN varchar2)
IS
v_job emp.job%TYPE;
v_sumsal number(10);
v_exception exception;
begin
select job, sum(sal) into v_job, v_sumsal
from emp
where job = p_job
GROUP BY job;
IF v_sumsal < 5000 then
raise v_exception;
else
insert into emp348
values ( v_job, v_sumsal);
end if;
EXCEPTION when v_exception then
raise_application_error (-20002, '월급이 너무 작아 출력불가');
end;
/
exec pro2(‘SALESMAN’);
문제113. 아래의 프로시져를 생성하시오(사원번호를 입력하면 해당 사원의 월급이 출력되는 프로시져)
CREATE OR REPLACE PROCEDURE query_emp
(v_empno IN emp.empno%type,
v_sal OUT emp.sal%type)
IS
BEGIN
SELECT sal INTO v_sal
FROM EMP
WHERE empno=v_empno;
END query_emp;
/
l OUT : 다른 프로시져나 anontymous PL/SQL에서 사용하기 위해서 이용
문제115. 사원번호를 물어보게 하고 사원번호를 입력하면 해당 사원의 월급이 2배로 갱신되는 프로시져를 생성하시오
CREATE OR REPLACE PROCEDURE query_emp_sal
(p_empno IN number)
IS
BEGIN
UPDATE EMP
SET sal=sal*2
WHERE empno=p_empno;
END;
/
문제116. 위에서 생성한 query_emp, query_emp_sal 를 이용해서 pro4 프로시져를 완성하고 exec pro4(7788); 를 수행하면 “수정되기전 월급 : 3000”, “수정된 후 월급 : 6000” 이렇게 출력되게 하시오
CREATE OR REPLACE PROCEDURE pro6
(v_empno emp.empno%type)
is
v_sal emp.sal%TYPE;
BEGIN
query_emp(v_empno, v_sal);
dbms_output.put_line('수정되기전 월급' || v_sal);
query_emp_sal(v_empno, v_sal);
query_emp(v_empno, v_sal);
dbms_output.put_line('수정된 후 월급' || v_sal);
END;
/
문제117. P1-24를 보고 답을 작성하는데 사원번호와 인상될 월급의 %를 입력해서 실행하면 해당 사원의 월급이 인상되는 프로시져를 생성하시오
CREATE OR REPLACE PROCEDURE raise_sal
(p_empno number,
p_pct number)
IS
BEGIN
UPDATE EMP
SET sal = sal+sal*p_pct/100
WHERE empno = p_empno;
END raise_sal;
/
문제118. 위의 프로시져를 다시 수정하는데 프로시져를 수행했을 때 아래와 같이 출력되게 하시오. “수정되기전 월급 : 3000”, “수정된 후의 월급: 6000”
CREATE OR REPLACE PROCEDURE raise_sal
(p_empno number,
p_pct number)
IS
v_sal emp.sal%TYPE;
BEGIN
query_emp(p_empno, v_sal);
dbms_output.put_line('수정되기전 월급 : ' || v_sal);
UPDATE EMP
SET sal = sal+sal*p_pct/100
WHERE empno = p_empno;
query_emp(p_empno, v_sal);
dbms_output.put_line('수정된후 월급 : ' || v_sal);
END raise_sal;
/
문제120. p1-32 를 보고 위의 코드를 수정하는데 문제119번 프로시져를 수행할 때 퍼센트를 입력안하면 무조건 default 값인 20%로 인상될 수 있도록 하시오. 그리고 " 1 명의 월급이 수정되었습니다" 라는 메세지도 출력되게 하시오
CREATE OR REPLACE PROCEDURE raise_sal
(p_empno IN emp.empno%TYPE,
p_pct IN NUMBER DEFAULT 20)
IS
v_sal emp.sal%TYPE;
BEGIN
query_emp(p_empno, v_sal);
dbms_output.put_line('수정되기전 월급 : ' || v_sal);
UPDATE EMP
SET sal = sal+sal*p_pct/100
WHERE empno = p_empno;
query_emp(p_empno, v_sal);
dbms_output.put_line('수정된후 월급 : ' || v_sal);
dbms_output.put_line(SQL%ROWCOUNT || ' 몇의 월급이 수정되었습니다');
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('해당 사원은 없습니다.');
END raise_sal;
/
문제123. raise_salary 프로시져에서 방금 생성한 pro122 를 호출될 수 있도록 하고 아래와 같이 출력되게 하시오 “해당 사원의 직업은 ANALYST”
CREATE OR REPLACE PROCEDURE raise_sal
(p_empno IN emp.empno%TYPE,
p_pct IN NUMBER DEFAULT 20)
IS
v_sal emp.sal%TYPE;
BEGIN
query_emp(p_empno, v_sal);
dbms_output.put_line('수정되기전 월급 : ' || v_sal);
UPDATE EMP
SET sal = sal+sal*p_pct/100
WHERE empno = p_empno;
query_emp(p_empno, v_sal);
dbms_output.put_line('수정된후 월급 : ' || v_sal);
dbms_output.put_line(SQL%ROWCOUNT || ' 몇의 월급이 수정되었습니다');
pro122(p_empno);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('해당 사원은 없습니다.');
END raise_sal;
/
l raise_sal 을 실행하면 에러가 안나온다. 그 이유는 pro122나 raise_sal 프로시져 둘 중에 하나가 예외 처리가 되어있기 때문이다.
문제159. 신입사원 연봉 테이블을 가지고 점심시간 했던 프로시져를 수정해서 3위를 입력해도 에러 안나고 겨로가가 출력될 수 있도록 하시오
CREATE OR REPLACE PROCEDURE sal_rank
(p_rank IN number)
IS
CURSOR emp_sal_cursor IS
SELECT t_name, t_sal
FROM (SELECT t_name, t_sal, RANK() OVER(ORDER BY t_sal desc) 순위 FROM emp_sal)
WHERE 순위 = p_rank;
BEGIN
FOR emp_record IN emp_sal_cursor LOOP
dbms_output.put_line(emp_record.t_name);
dbms_output.put_line(emp_record.t_sal);
END LOOP;
END;
/문제182. 프로시져를 생성하는ㄴ데 아래와 같이 수행했을 떄 결과가 출력되게 하시오
exec sal_rank(1,5) - 1위부터 5위까지 출력
CREATE OR REPLACE PROCEDURE sal_rank
(p_rank IN NUMBER,
p_rank2 IN NUMBER)
IS
CURSOR emp_sal_cursor IS
SELECT t_name, t_sal, 순위
FROM (SELECT t_name, t_sal, RANK() OVER(ORDER BY t_sal desc) 순위 FROM emp_sal)
WHERE 순위 BETWEEN p_rank AND p_rank2;
BEGIN
FOR emp_record IN emp_sal_cursor LOOP
dbms_output.put_line('순위 ' || emp_record.순위 ||' '||emp_record.t_name);
dbms_output.put_line('순위 ' || emp_record.순위 ||' '||emp_record.t_sal);
END LOOP;
END;
/
문제194. 위의 SQL을 이용해서 프로시져를 생성하는데 년도를 입력하고 프로시져를 실행하면 아래의 결과가 출력되게 하시오
exec pro194(2000);
2000 T_SK 65 1
2000 T_NAVER 63 2
2000 T_DAUM 0 3
2000 T_GOOGLE 0 3
CREATE OR REPLACE PROCEDURE pro194
(p_year cus_index.t_year%type)
IS
CURSOR cus_cursor IS
SELECT *
FROM (SELECT t_year, compp, cnt, RANK() over(PARTITION BY t_year ORDER BY cnt desc)순위
FROM cus_index
unpivot(cnt FOR compp IN (T_NAVER,T_SK,T_DAUM,T_GOOGLE))
WHERE t_year=2000);
BEGIN
FOR cus_record IN cus_cursor LOOP
dbms_output.put_line(cus_record.t_year ||' '||cus_record.compp||' '||cus_record.cnt
||' '||cus_record.순위);
END LOOP;
END;
/
문제197. 아래의 결과가 출력되겠금 하는 SQL 을 만들어서 검색포탈 사이트에서 가장 좋은 점수를 받고 있는 기업이 어디인지 기업명이 출력되는 프로시져를 생성하시오
exec pro197(1);
NAVER
CREATE OR REPLACE PROCEDURE pro196
(p_rank number)
IS
v_name VARCHAR2(10);
begin
SELECT compp INTO v_name
FROM (SELECT compp, SUM(cnt), RANK() over(ORDER BY SUM(cnt) desc) 순위
FROM cus_index
unpivot(cnt FOR compp IN (T_NAVER,T_SK,T_DAUM,T_GOOGLE))
GROUP BY compp)
WHERE 순위=p_rank;
dbms_output.put_line(v_name);
END;
/
문제214. emp2345의 테이블에 각 해당년도 1위업체 컬럼을 추가하고 그 해의 1위업체를 추가하시오
HIREDATE NS SY OT PD 1위업체
1999 73 64 65 66 NS
2007 74 72 71 71 NS
2008 72 69 69 69 NS
2009 70 69 68 68 NS
2010 73 72 70 70 NS
ALTER TABLE emp2345
ADD ndl VARCHAR(20);
CREATE OR REPLACE PROCEDURE noodle_rnk
IS
CURSOR emp_cursor IS
SELECT noodle ,hiredate
FROM (SELECT hiredate , noodle, cnt, RANK() OVER(PARTITION BY hiredate ORDER BY cnt desc) 순위
FROM emp2345 unpivot(cnt FOR noodle IN (NS,SY,OT,PD)))
WHERE 순위=1;
BEGIN
FOR emp_record IN emp_cursor LOOP
UPDATE EMP2345
SET ndl=emp_record.noodle
WHERE hiredate=emp_record.hiredate;
END LOOP;
END;
/
'빅데이터과정 > PL/SQL ' 카테고리의 다른 글
| #10_140626_PL_SQL_RECORD (0) | 2014.07.14 |
|---|---|
| #10_140626_PL_SQL_LABEL (0) | 2014.07.14 |
| #10_140626_PL_SQL_FOR 문 (0) | 2014.07.14 |
| #9_140625_PL_SQL_CASE 문 (0) | 2014.07.14 |
| #9_140625_PL_SQL_LOOP문 (0) | 2014.07.14 |