[03] Stored Procedure응용, 스크립트 문법
▩ INSERT, DELETE, UPDATE의 이용
1. 기본 테이블 구조 만들기
drop table sungjuk;
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
);
insert into sungjuk(num, name, kuk, eng) values(1, '왕눈이', 90, 80);
commit;
drop table logTable;
create table logTable(
user_id varchar2(10),
log_date date);
2. 로그인 정보를 저장하는 프로시저
create or replace procedure log_write --@D:\dev\oracle\plSql\log_write.sql
is
begin
insert into logTable(user_id, log_date) VALUES(user, sysdate);
commit;
end log_write;
/
생성: @D:\dev\oracle\plSql\log_write.sql
execute log_write;
select * from logTable;
3. 성적을 삭제하는 프로시저
create or replace procedure del_num --@D:\dev\oracle\plSql\del_num
(v_num IN sungjuk.num%TYPE)
is
begin
log_write;
delete from sungjuk where num = v_num;
commit;
end del_num;
/
생성: @D:\dev\oracle\plSql\del_num
execute del_num(1);
select * from sungjuk;
4. 성적을 입력하는 프로시저
CREATE OR REPLACE PROCEDURE insert_sungjuk --@D:\dev\oracle\plSql\insert_sungjuk
( v_num IN sungjuk.num%TYPE,
v_name IN sungjuk.name%TYPE,
v_kuk IN sungjuk.kuk%TYPE DEFAULT 0,
v_eng IN sungjuk.eng%TYPE DEFAULT 0,
v_rpt IN sungjuk.rpt%TYPE DEFAULT '미제출')
IS
v_tot sungjuk.tot%TYPE;
v_pye sungjuk.pye%TYPE;
v_opt sungjuk.opt%TYPE;
BEGIN
log_write;
v_tot := v_kuk + v_eng;
v_pye := v_tot / 2;
if v_rpt='제출' then
v_opt := 10;
else
v_opt := 0;
end if;
insert into sungjuk(num, name, kuk, eng, tot, pye, rpt, opt) values(v_num, v_name, v_kuk, v_tot, v_eng, v_pye, v_rpt, v_opt);
commit;
end insert_sungjuk;
/
생성: @D:\dev\oracle\plSql\insert_sungjuk
execute insert_sungjuk(1, '왕눈이', 90, 80, '제출');
select * from logTable;
select * from sungjuk;
5. 성적을 update하는 프로시저
CREATE OR REPLACE PROCEDURE update_sungjuk --@D:\dev\oracle\plSql\update_sungjuk
( v_num IN sungjuk.num%TYPE,
v_kuk IN sungjuk.kuk%TYPE DEFAULT 0,
v_eng IN sungjuk.eng%TYPE DEFAULT 0,
v_rpt IN sungjuk.rpt%TYPE DEFAULT '미제출')
IS
v_tot sungjuk.tot%TYPE;
v_pye sungjuk.pye%TYPE;
v_opt sungjuk.opt%TYPE;
BEGIN
log_write;
v_tot := v_kuk + v_eng;
v_pye := v_tot / 2;
if v_rpt='제출' then
v_opt := 10;
else
v_opt := 0;
end if;
update sungjuk set kuk = v_kuk, eng = v_eng, tot = v_tot, pye = v_pye, rpt = v_rpt, opt = v_opt
where num = v_num;
commit;
end update_sungjuk;
/
생성: @D:\dev\oracle\plSql\update_sungjuk
execute update_sungjuk(1, 90, 100, '미제출');
select * from logTable;
select * from sungjuk;
6. 성적을 출력하는 프로시저
CREATE OR REPLACE PROCEDURE select_sungjuk --@D:\dev\oracle\plSql\select_sungjuk
( v_num IN sungjuk.num%TYPE)
IS
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;
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 = v_num;
DBMS_OUTPUT.PUT_LINE('조회된 데이터');
DBMS_OUTPUT.PUT_LINE('번호:' || v_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 select_sungjuk;
/
7. 실행한 쿼리의 종류를 저장하는 스토어드 프로시져를 작성할것.
ID, 날짜, 시간(to_char(sysdate, 'HH24:MI:SS')), 실행쿼리 종류(INSERT, DELETE, UPDATE, SELECT)
▩ 스크립트 문법
- 식별자는 30문자를 초과할 수 없습니다.
- 식별자는 테이블, 컬럼명과 동일 할 수 없습니다.
- 식별자는 반드시 문자값으로 시작해야 합니다.
- 문자와 날짜는 " ' " 인용부호를 이용합니다.
- 주석은 "--", 여러라인 주석은 "/* */을 이용합니다.
1. 비교문
SET SERVEROUTPUT ON;
DECLARE
v_condition number :=1;
BEGIN
IF v_condition=1 THEN
DBMS_OUTPUT.PUT_LINE('데이터의 값은 1입니다.');
END IF;
END;
/
DECLARE
v_condition number :=2;
BEGIN
IF v_condition > 1 THEN
DBMS_OUTPUT.PUT_LINE('데이터의 값은 1보다 큽니다.');
ELSE
DBMS_OUTPUT.PUT_LINE('데이터의 값은 1보다 작습니다.');
END IF;
END;
/
DECLARE
v_condition number :=2;
BEGIN
IF v_condition > 1 THEN
DBMS_OUTPUT.PUT_LINE('데이터의 값은 1보다 큽니다.');
elsif v_condition = 1 THEN
DBMS_OUTPUT.PUT_LINE('데이터의 값은 1입니다.');
ELSE
DBMS_OUTPUT.PUT_LINE('데이터의 값은 1보다 작습니다.');
END IF;
END;
/
2. 반복문
DECLARE
cnt number :=0;
BEGIN
LOOP
CNT := CNT + 1;
DBMS_OUTPUT.PUT_LINE(CNT);
EXIT WHEN CNT = 10;
END LOOP;
END;
/
BEGIN
FOR I IN 1..10 LOOP
IF (MOD(I, 2) = 1) THEN
DBMS_OUTPUT.PUT_LINE(I);
END IF;
END LOOP;
END;
/
DECLARE
v_cnt number := 1;
v_str varchar2(10) := null;
BEGIN
WHILE v_cnt <= 10 LOOP
v_str := v_str || '#';
DBMS_OUTPUT.PUT_LINE(v_str);
v_cnt := v_cnt+1;
END LOOP;
END;
/
>>>>> sjk 테이블에서 총점과 평균을 계산하면서 레코드를 1만개 추가하는 프로시저를 작성하세요.
CREATE TABLE sung(
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 sung_ins10000
IS
i number(5) DEFAULT 0;
v_no sung.no%TYPE;
v_kuk sung.kuk%TYPE;
v_eng sung.eng%TYPE;
v_tot sung.tot%TYPE; --프로시저에서 사용하는 내부 지역변수
v_avg sung.avg%TYPE;
BEGIN
FOR i IN 1..10000 LOOP
v_no := i;
v_kuk := 100;
v_eng := 93;
v_tot := v_kuk + v_eng;
v_avg := v_tot / 2.0;
INSERT INTO sung(no, kuk, eng, tot, avg)
VALUES(v_no, v_kuk, v_eng, v_tot, v_avg);
COMMIT WORK;
END LOOP;
END sung_ins10000;
--SQL Editor에서 작업합니다.
begin
sung_ins10000;
end;
/
SELECT count(*) FROM sung;
SELECT * FROM sung WHERE no <= 10;
