160623: 70회차

Programming 2016. 6. 23. 16:30

종료하기 전 티스토리 네이버 로그아웃 할 것

1. 툴

동일

추가시:

2. 폴더

동일

추가시:

3. 사용할 사이트

동일

추가시:

4. 공부하는 것

70일 기념 럭키

역시 나의 행복

[04] Stored Procedure - IN 매개변수

[01] Stored Procedure Create & Execution

1. sungjuk 테이블에서 총점과 평균을 계산하면서 레코드를 1000개 추가하는

프로시저를 작성하세요.

DROP TABLE sungjuk PURGE;

CREATE TABLE sungjuk(

no number(5) not null,

kuk number(3) not null,

eng number(3) not null,

tot number(3) not null,

avg number(5,2) not null

);

CREATE OR REPLACE PROCEDURE sungjuk_ins1000

v_i number(5) DEFAULT 0;

v_no sungjuk.no%TYPE; --sungjuk테이블의 no컬럼의 타입을 지정

v_kuk sungjuk.kuk%TYPE; --프로시저에서 사용하는 내부 지역변수

v_eng sungjuk.eng%TYPE;

v_tot sungjuk.tot%TYPE;

v_avg sungjuk.avg%TYPE;

BEGIN

FOR v_i IN 1..1000 LOOP

v_no := v_i; -- v_i변수의 값을 v_no에 할당합니다.

v_kuk := 100;

v_eng := 93;

v_tot := v_kuk + v_eng;

v_avg := v_tot / 2.0;

INSERT INTO sungjuk(no, kuk, eng, tot, avg)

VALUES(v_no, v_kuk, v_eng, v_tot, v_avg);

END LOOP;

COMMIT WORK; -- transaction commit

END sungjuk_ins1000;

-- 실행

begin

sungjuk_ins1000;

end;

SELECT count(*) FROM sungjuk;

SELECT * FROM sungjuk WHERE no <= 10;

exec sungjuk_ins1000;

[02] IN 매개변수

- 프로시져로 파라미터를 전달 할 수 있습니다.

1. INSERT의 구현

DROP TABLE emp CASCADE CONSTRAINTS PURGE;

CREATE TABLE emp(

empno number(5) not null,

ename varchar(10) null,

job varchar(10) null,

mgr number(5) null,

hiredate date null,

sal number(10) null,

comm number(3,2) null,

deptno number(5) null,

CONSTRAINT emp_empno_pk PRIMARY KEY(empno) --중복된 값이 올수 없음, 반드시 값을 입력

);

CREATE SEQUENCE emp_seq

start with 1 -- 일련 번호 시작 값

increment by 1 -- 증가 값

cache 2;

COMMIT;

SELECT * FROM emp;

CREATE OR REPLACE PROCEDURE ins_emp(

i_emp_name IN emp.ename%TYPE, -- 프로시저 호출시 이용할 가인수

i_emp_job IN emp.job%TYPE, -- 테이블명.컬럼명%TYPE

i_emp_mgr IN emp.mgr%TYPE,

i_emp_sal IN emp.sal%TYPE

)

v_emp_comm emp.comm%TYPE; --프로시저에서 사용하는 지역변수

BEGIN

IF i_emp_job ='SALESMAN' THEN -- 영업부 직원이면

v_emp_comm :=0.03; -- 3 % 수당 지급

ELSE

v_emp_comm :=0.01; -- 1 % 수당 지급

END IF;

INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm)

VALUES(emp_seq.NextVal, i_emp_name, i_emp_job, i_emp_mgr, sysdate, i_emp_sal, v_emp_comm);

COMMIT WORK;

END ins_emp;

-- 실행

begin

ins_emp('가길동', 'SALESMAN',1,1500000);

ins_emp('나길동', 'developer',1,2000000);

ins_emp('다길동', 'salesman',1,1800000); -- 대소문자 구분합니다.

end;

SELECT * FROM emp;

[05] Stored Procedure - OUT 매개변수, IN/OUT 매개변수

[01] OUT 매개변수

- 프로시져로부터 값을 가져올 수 있습니다.

1. 컬럼값을 출력하는 프로시져

CREATE OR REPLACE PROCEDURE query_emp(

i_emp_no IN emp.empno%TYPE, -- IN 값 입력

o_emp_name OUT emp.ename%TYPE, -- OUT 값 출력

o_emp_sal OUT emp.sal%TYPE, -- OUT 값 출력

o_emp_comm OUT emp.comm%TYPE -- OUT 값 출력

)

BEGIN

SELECT ename, sal, comm

INTO o_emp_name, o_emp_sal, o_emp_comm -- output매개변수 목록 ★

FROM emp

WHERE empno = i_emp_no; -- IN 매개 변수의 값과 비교

END query_emp;

-- 실행

DECLARE

emp_name varchar2(10) := null;

emp_sal number := 0;

emp_comm number := 0;

BEGIN

query_emp(6, emp_name, emp_sal, emp_comm);

DBMS_OUTPUT.PUT_LINE(emp_name);

DBMS_OUTPUT.PUT_LINE(emp_sal);

DBMS_OUTPUT.PUT_LINE(emp_comm);

END;

3. IN/OUT 매개변수

--Procedure Editor에서 작업합니다.

CREATE OR REPLACE PROCEDURE add_one(

io_phone_no IN OUT VARCHAR2

)

BEGIN

io_phone_no := SUBSTR(io_phone_no, 1, 3) || '-' || SUBSTR(io_phone_no, 4, length(io_phone_no));

END add_one;

-- 실행

DECLARE

phone_num VARCHAR2(15) := '01030211495';

BEGIN

add_one(phone_num);

DBMS_OUTPUT.PUT_LINE(phone_num);

END;

[06] Stored Procedure INSERT, DELETE, UPDATE의 이용

[01] INSERT, DELETE, UPDATE의 이용

1. 기본 테이블 구조 만들기

drop table sungjuk PURGE;

create table sungjuk

(

num number(3) not null,

name varchar2(10) not null,

kuk number(3) not null,

eng number(3) not null,

tot number(3) null,

pye number(3) null,

rpt varchar(10) null,

opt number(3) null

);

1. 성적을 입력하는 프로시저

CREATE OR REPLACE PROCEDURE insert_sungjuk

(

i_num IN sungjuk.num%TYPE,

i_name IN sungjuk.name%TYPE,

i_kuk IN sungjuk.kuk%TYPE DEFAULT 0,

i_eng IN sungjuk.eng%TYPE DEFAULT 0,

i_rpt IN sungjuk.rpt%TYPE DEFAULT '미제출'

)

v_tot sungjuk.tot%TYPE;

v_pye sungjuk.pye%TYPE;

v_opt sungjuk.opt%TYPE;

BEGIN

v_tot := i_kuk + i_eng;

v_pye := v_tot / 2;

if i_rpt='제출' then

v_opt := 10;

else

v_opt := 0;

end if;

insert into sungjuk(num, name, kuk, eng, tot, pye, rpt, opt) values(i_num, i_name, i_kuk, i_eng, v_tot, v_pye, i_rpt, v_opt);

commit;

end insert_sungjuk;

-- 실행

begin

insert_sungjuk(1, '개발자4', 90, 80, '제출');

insert_sungjuk(2, '개발자5', 90, 80);

end;

select * from sungjuk;

2. 성적을 update하는 프로시저

- 특정 학생의 번호와 점수를 입력받아 Update를 합니다.

CREATE OR REPLACE PROCEDURE update_sungjuk

(

i_num IN sungjuk.num%TYPE,

i_name IN sungjuk.name%TYPE,

i_kuk IN sungjuk.kuk%TYPE DEFAULT 0,

i_eng IN sungjuk.eng%TYPE DEFAULT 0,

i_rpt IN sungjuk.rpt%TYPE DEFAULT '미제출'

)

v_tot sungjuk.tot%TYPE;

v_pye sungjuk.pye%TYPE;

v_opt sungjuk.opt%TYPE;

BEGIN

v_tot := i_kuk + i_eng;

v_pye := v_tot / 2;

if i_rpt='제출' then

v_opt := 10;

else

v_opt := 0;

end if;

update sungjuk set name = i_name, kuk = i_kuk, eng = i_eng, tot = v_tot, pye = v_pye, rpt = i_rpt, opt = v_opt

where num = i_num;

commit;

end update_sungjuk;

-- 실행

begin

update_sungjuk(1, '디자이너', 100, 100, '미제출');

end;

select * from sungjuk;

3. 성적을 삭제하는 프로시저

create or replace procedure del_num

(

i_num IN sungjuk.num%TYPE

)

begin

delete from sungjuk where num = i_num;

commit;

end del_num;

-- 실행

begin

del_num(1);

end;

select * from sungjuk;

4. 성적을 출력하는 프로시저

CREATE OR REPLACE PROCEDURE p_select_sungjuk

(

i_num IN sungjuk.num%TYPE

)

v_name sungjuk.name%TYPE;

v_kuk sungjuk.kuk%TYPE;

v_eng sungjuk.eng%TYPE;

v_tot sungjuk.tot%TYPE;

v_pye sungjuk.pye%TYPE;

v_rpt sungjuk.rpt%TYPE;

v_opt sungjuk.opt%TYPE;

BEGIN

log_write('SELECT name, kuk, eng, tot, pye, rpt, opt FROM sungjuk WHERE num = ' || i_num);

DBMS_OUTPUT.ENABLE;

SELECT name, kuk, eng, tot, pye, rpt, opt

INTO v_name, v_kuk, v_eng, v_tot, v_pye, v_rpt, v_opt

FROM sungjuk

WHERE num = i_num;

DBMS_OUTPUT.PUT_LINE('조회된 데이터');

DBMS_OUTPUT.PUT_LINE('번호:' || i_num);

DBMS_OUTPUT.PUT_LINE('국어:' || v_kuk);

DBMS_OUTPUT.PUT_LINE('영어:' || v_eng);

DBMS_OUTPUT.PUT_LINE('총점:' || v_tot);

DBMS_OUTPUT.PUT_LINE('평균:' || v_pye);

DBMS_OUTPUT.PUT_LINE('레포트제출:' || v_rpt);

DBMS_OUTPUT.PUT_LINE('추가점수:' || v_opt);

END p_select_sungjuk;

-- 실행

being

p_select_sungjuk(1);

end;

[07] Cursor 사용, Stored Function

[01]Cursor // result set 비슷한 것

- 질의 수행결과가 여러행일때 처리하도록 지원합니다.

- 어떤 위치를 가리키는 속성을 가집니다.

- 커서는 질의의 결과가 여러행일때 여러행이 저장된 메모리 상의 위치를

나타냅니다.

- 결과 SET의 행들을 하나씩 가져오는 역할을 합니다.

DECLARE

커서의 선언

CURSOR 커서이름 IS SELECT 구문;

BEGIN

커서 열기

OPEN 커서이름;

커서로 부터 데이터 읽기

FETCH 커서이름 INTO 로컬 변수;

커서 닫기

CLOSE 커서이름;

END;

- 사용예문

LOOP

FETCH emp_cur INTO id, name,salary; --지역변수

EXIT WHEN emp_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(id ||' '||name||' '||salary);

END LOOP;

※ 커서의 속성

%FOUND - PL/SQL 코드가 마지막으로 얻은 커서의 결과 SET에 레코드가 있다면 참

%NOTFOUND - %FOUND의 반대

%ROWCOUNT - 커서가 얻은 레코드의 수를 반환

%ISOPEN - 커서가 열려있다면 참 반환

1. 예제 실습

- 여러 개의 행을 읽기 위해서 FOR-LOOP-END LOOP문을 사용한 예제 입니다.

DECLARE

vemp emp%ROWTYPE;

CURSOR C1

SELECT empno, ename, sal

FROM emp

WHERE deptno=20;

BEGIN

dbms_output.put_line('번호 이름 급여');

FOR vemp IN C1 LOOP

EXIT WHEN C1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(TO_CHAR(vemp.empno) ||

' '||vemp.ename||' '||TO_CHAR(vemp.sal));

END LOOP;

END;

2. 급여 총합을 구하는 예제를 만들어보세요

이름 급여

-----------------------------------------

홍길동 1500000

김길동 2500000

: :

-----------------------------------------

총액 350000000

3. 사원별 급여 현황을 그래프로 표현해 보세요

사원별 급여 현황

-------------------------------------------------------------

홍길동 *********************(1500000)

김길동 **********************************(2500000)

: :

[02] Stored Function

- 처리결과를 리턴합니다.

- 반복적으로 발생하는 컬럼 데이터 조작이 있는 경우 함수의 대상이 됩니다.

- 다양한 쿼리(INSERT, DELETE, UPDATE, SELECT, GROUP BY...) 속에서 사용될 수 있습니다.

- 프로시저는 여러값을 리턴하나 함수는 하나의 값만 리턴합니다.

1. 함수의 실습

CREATE OR REPLACE FUNCTION tax(i_value IN NUMBER)

RETURN NUMBER -- 리턴 타입

BEGIN

return (i_value * 0.07); -- 세금 7 % 계산

END tax;

-- 실행

SELECT ename as "성명", deptno as "번호", sal as "급여", tax(sal) as "세금 7% 산출 금액"

FROM emp;

2. emp.deptno 컬럼의 값이 10-전산부, 20-경리부, 30-영업부, 40-자재부

- c_deptno()

CREATE OR REPLACE FUNCTION c_deptno(i_deptno IN NUMBER)

RETURN VARCHAR

v_deptno varchar(20) := null;

BEGIN

if i_deptno = 10 then

v_deptno := '전산부';

end if;

if i_deptno = 20 then

v_deptno := '경리부';

end if;

if i_deptno = 30 then

v_deptno := '영업부';

end if;

if i_deptno = 40 then

v_deptno :='자재부';

end if;

return v_deptno;

END c_deptno;

CREATE OR REPLACE FUNCTION c_deptno(i_deptno IN NUMBER)

RETURN VARCHAR

v_deptno varchar(20) := null;

BEGIN

if i_deptno = 10 then

v_deptno := '전산부';

elsif i_deptno = 20 then

v_deptno := '경리부';

elsif i_deptno = 30 then

v_deptno := '영업부';

else

v_deptno :='자재부';

end if;

return v_deptno;

END c_deptno;

-- 실행

SELECT * FROM emp;

SELECT ename as "성명", deptno as "번호", c_deptno(deptno) as "부서명" FROM emp;

5. 수업

진도:

hw:

6. 할것

'Programming' 카테고리의 다른 글

160627: 72회차 (0)	2016.06.27
160624: 71회차 (0)	2016.06.24
160622: 69회차 (0)	2016.06.22
160621: 68회차 (0)	2016.06.21
160620: 67회차 (0)	2016.06.20

Posted by ＤＡＶＩＤ

PC

160623: 70회차

'Programming' 카테고리의 다른 글

티스토리툴바