--1. 스토더으 프로시져
--1-1 파라미터가 없는 프로시져
---------------------------------------------------------------------
--emp의 eno, ename에 eno 999 ename 장길산을 저장하는 프로시저
create or replace procedure pro_noparam
is
eno varchar(8);
ename varchar(20);
begin
eno := '999';
ename := '장길산';
insert into EMP(eno, ename)
values (eno, ename);
end;
/
call pro_noparam();
select *
from emp
where eno = '999';
-----------------------------------------------------------
--일반화학의 학생별 기말고사 성적을 저장하는 테이블 생성
create table t_nche_sc1 as
select sc.*, st.sname
from score sc
join course c on sc.cno = c.cno
right join student st on sc.sno = st.SNO
where c.cname = '일반화학';
select *
from t_nche_sc1;
drop table t_nche_sc1;
-- grade까지 가지는 일반화학 학생별 기말고사 성적 테이블
create table t_nche_scgr
(
sno number primary key,
sname varchar2(20),
result number(5, 2),
grade char(1)
);
select *
from t_nche_scgr;
--이 점수를 가지고 90 이상이면 a 80 이상이면 b 70 이상이면 c 로 만드는 프로시저 만들겠다.
--프로시저 선언
--내거는 왜안될까?!?!!--내거는 왜안될까?!?!!--내거는 왜안될까?!?!!--내거는 왜안될까?!?!!--내거는 왜안될까?!?!!--내거는 왜안될까?!?!!--내거는 왜안될까?!?!!--내거는 왜안될까?!?!!--내거는 왜안될까?!?!!--내거는 왜안될까?!?!!--내거는 왜안될까?!?!!--내거는 왜안될까?!?!!--내거는 왜안될까?!?!!--내거는 왜안될까?!?!!ㅍ
create or replace procedure p_nche_scgr is
type nche_scgr_rec is record
(
sno t_nche_scgr.sno%type,
sname t_nche_scgr.sname%type,
result t_nche_scgr.result%type,
grade t_nche_scgr.grade%type
);
schescgrrec nche_scgr_rec;
cursor cur_nche_scgr is
select ncs.sno, ncs.sname, ncs.result, 'A'
from t_nche_sc1 ncs;
begin
open cur_nche_scgr;
loop
fetch cur_nche_scgr into schescgrrec;
exit when cur_nche_scgr%notfound;
if schescgrrec.result >= 90 then
schescgrrec.grade := 'A';
elsif schescgrrec.result >= 80 then
schescgrrec.grade := 'B';
elsif schescgrrec.result >= 60 then
schescgrrec.grade := 'C';
elsif schescgrrec.result >= 50 then
schescgrrec.grade := 'D';
else
schescgrrec.grade := 'F';
end if;
insert into t_nche_scgr
values schescgrrec;
end loop;
close cur_nche_scgr;
end;
call p_nche_scgr();
select *
from t_nche_scgr;
drop table t_nche_scgr;
--1-2. 파라미터가 있는 프로시저
--프로시저 p_new_dept를 만드는데 dept의 dno, dname, loc, director 에 1,5,4,0을 넣는 프로시저를 만들어라.
create or replace procedure p_new_dept(
dno in varchar2 := '1',
dname in varchar2 := '5', loc in varchar2 := '4', director in varchar2 := '0'
)
is
begin
insert into DEPT values (dno, dname, loc, DIRECTOR);
end;
call P_NEW_DEPT('1', '1', '1', '1');
select *
from DEPT;
delete
from DEPT
where loc = '1';
--stored function
--급여별로 세금 조회하는 함수선언 sal 7000 이상이면 0.1 6000 0.07 5000 0.05 0.03 그리고 emp의 정보와 tax를 보여준다.
create or replace function f_gettax1(
sal number
)
return number
is
tax number;
begin
if sal >= 7000 then
tax := 0.1;
elsif sal >= 6000 then
tax := 0.07;
elsif sal >= 5000 then
tax := 0.05;
else
tax := 0.03;
end if;
return round(sal * tax);
end;
/
--f_gettax 함수 쿼리문에서 호출
select e.*, f_gettax1(e.sal) as tax
from emp e;
--3-1 trigger
-- before trigger
-- 급여가 3000 미만으로 입력됐을 때 에러메세지 출력하는 트리거
--emp에서 급여를 넣기 전에 3000보다 낮으면 저렇게 문구가 뜨는 트리거가 만듥게 되는거
create or replace trigger tr_emp_sal1
--of sal 넣어스 특정 칼럼 감지할수도 있다.
before insert or update of sal
on EMP
referencing new as new
for each row
begin
if :new.sal < 3000 then
if inserting then
raise_application_error(-20000, '최저급여보다 낮음');
elsif updating then
raise_application_error(-20001, '최저급여보다 낮음');
else
raise_application_error(-20002, '최저급여보다 낮음');
end if;
end if;
end;
select *
from emp;
insert into emp
values ('8001', '홍길동', '취합', '2001', sysdate, 2000, 0, '01');
insert into emp(eno, ename)
values ('9999', '장길산');
select *
from emp;
delete
from emp
where ENAME = '홍길동';
update emp
set sal = 2000
where eno = '9999';
--이것도 최저급여가 낮아서 업데이트가 안됨
--t_nche_sc 테이블에 데이터가 입력이나 점수가 수정되면 t_nche_Scgr테이블에 데이터가 자동으로 입력되거나
-- 점수가 수정된 거는 grade가 자동 업데이트 되는 크리거
create or replace trigger tr_nche_scgr1
after
insert or update
on t_nche_sc1
referencing new as new
for each row----------------------------------------------------모든 행에 있어서 다 바꿔야해서 포이치로우
declare
grd char(1);
begin
if :new.result >= 90 then
grd := 'A';
elsif :new.result >= 80 then
grd := 'B';
elsif :new.result >= 70 then
grd := 'C';
elsif :new.result >= 60 then
grd:= 'D';
else
grd:= 'F';
end if;
merge into t_nche_scgr A
using DUAL-------------------------------------------- 따로 테이블 넣을 필요가 없어서 듀얼로
on (A.sno = :new.sno)--------------------------------------------
when matched then
update
set a.result =:new.RESULT,-------------------------------------------- :new는 그냥 문법이다.
a.grade = grd--------------------------------------------
when not matched then
insert (a.sno, a.sname, a.result, a.grade)
values (:new.sno, :new.sname, :new.result, grd);--------------------------------------------
end;
drop trigger tr_nche_scgr1;
select * from t_nche_scgr;
insert into t_nche_sc1
values (85001, 1211, 78, '조병5조');
select *
from t_nche_sc1;
select * from t_nche_scgr;
select *
from t_nche_scgr
where sno = 8001;
update t_nche_sc1
set result = 85--------------------------------------------
where sno = 915305;
create or replace procedure p
is
eno varchar2(20);
ename varchar2(20);
begin
eno := 14;
ename := 'boot';
insert into emp(eno, ename) values (eno, ename);
end;
/
call p();
select *
from emp;
select *
from score;
select *
from tsc;
drop table tsc;
create table tsc as
select sc.*, st.sname
from score sc,
student st,
course c
where st.SNO = Sc.SNO
and c.CNO = sc.CNO
and cname = '일반화학';
create table tsca
(
sno number,
sname varchar2(20),
result number,
grade char(1)
);
select *
from tsca;
-- tsca - sno, sname, result, grade;
select *
from tsc;
-- tsc -sno, cno, result, sname 꽉차있음;
DROP PROCEDURE PO;
create or replace procedure po is
type rcd is record
(
sno tsca.sno%type,
sname tsca.sname%type,
result tsca.result%type,
grade tsca.grade%type
);
r rcd;
cursor curst is select sno, sname, result, 'A'
from tsc;
begin
open curst;
loop
fetch curst into r;
EXIT WHEN CURST%NOTFOUND;
if r.result >= 90 then
r.grade := 'A';
ELSIF R.result >= 80 THEN
R.grade := 'B';
ELSE
R.grade := 'Z';
end if;
INSERT INTO TSCA VALUES R;
end loop;
CLOSE CURST;
end;
call po();
select *
from tsca;
delete
from tsca;
SELECT *
FROM TSCA;
SELECT *
FROM TSC;
DELETE
FROM TSCA;
create or replace procedure pop is
type rcd is record
(
sno tsca.sno%type,
sname tsca.sname%type,
result tsca.result%type,
grade tsca.grade%type
);
r rcd;
cursor curst is select sno, sname, result, 'A'
from tsc;
begin
open curst;
loop
fetch curst into r;
exit when curst%notfound;
if r.result >= 90
then
r.grade := 'A';
elsif r.result >= 80
then
r.grade := 'B';
else
r.grade := 'Z';
end if;
insert into tsca values r;
end loop;
close curst;
end;
call pop();
select *
from tsca;
delete
from tsca;
drop procedure pop;
drop procedure po;
select *
from tsca;
select *
from tsc;
create or replace procedure pow
is
type rcd is record
(
sno tsca.sno%type,
sname tsca.sname%type,
result tsca.result%type,
grade tsca.grade%type
);
r rcd;
cursor curst is select sno, sname, result, 'A'
from tsc;
begin
open curst;
loop
fetch curst into r;
exit when curst%notfound;
if r.result >= 90 then
r.grade := 'A';
elsif r.result >= 80 then
r.grade := 'B';
else
r.grade := 'Z';
end if;
insert into tsca values r;
end loop;
close curst;
end;
call pow();
create or replace procedure pnew(dno in varchar2 := 1, dname in varchar2 := 5, loc in varchar2 := 4,
director in varchar2 := '0')
is
begin
insert into dept values (dno, dname, loc, director);
end;
call pnew(1, 1, 1, 1);
select *
from dept;
delete
from dept
where dname = '1';
create or replace procedure pneww(dno varchar2, dname varchar2, loc varchar2, director varchar2) is
begin
insert into dept values (dno, dname, loc, DIRECTOR);
end ;
call pneww(1, 1, 1, 1);
select *
from DEPT;
create or replace procedure paew(dno varchar2, dname varchar2, loc varchar2, director varchar2) is
begin
insert into dept(dno, dname, loc, DIRECTOR);
end;
create or replace procedure poww(dno varchar2, dname varchar2) is
begin
insert into dept(dno, dname);
end;
create or replace procedure powi(dno varchar2, dname varchar2) is
begin
insert into dept values (dno, dname);
end;
create or replace function aa(sal number)
return number is
tax number;
begin
if sal >= 7000 then
tax := 0.1;
elsif sal >= 6000 then
tax := 0.2;
else
tax := 0;
end if;
return round(sal * tax);
end;
select e.*, aa(e.sal)
from EMP e;
create or replace function ee(sal number)
return number
is
tax number;
begin
if sal >= 7000 then
tax := 0.3;
elsif sal >= 6000 then
tax := 0.2;
else
tax := 0.1;
end if;
return round(sal * tax);
end;/
select e.*, ee(e.sal)
from EMP e;
drop function ee;
drop function aa;
create or replace function zz(sal number)
return number is
tax number;
begin
if sal >= 7000 then
tax := 1.2;
elsif sal >= 6000 then
tax := 0.8;
else
tax := 0;
end if;
return round(sal * tax);
end;
select e.*, zz(e.sal)
from emp e;
create or replace function ass(sal number) return number is
tax number;
begin
if sal >= 7000 then
tax := 0.9;
elsif sal >= 6000 then
tax := 0.5;
else
tax := 0;
end if;
return round(sal * tax);
end;
select e.*, ass(e.sal)
from emp e;
drop trigger ppp;
drop trigger pppp;
drop trigger tr_emp_sal1;
drop trigger ahaa;
create or replace trigger popa
before insert or update of sal
on emp
referencing new as new
for each row
begin
if :new.sal < 3000 then
if inserting then
raise_application_error(-20000, '최저임금보다 낮음');
if updating then
raise_application_error(-20001, '최저보다 낮음');
else
raise_application_error(-20002, '최저');
end if;
end if;
end if;
end;
drop trigger popa;
insert into emp(eno, sal)
values (1, 1);
insert into emp(eno, sal)
values (1, 2000);
select *
from tsc;
select *
from tsca;
delete
from tsca;
create or replace trigger kikha
after insert or update
on tsc
referencing new as new
for each row
declare
grd char(1);
begin
if new.result >= 90
then
grd := 'A';
else
grd := 'Z';
end if;
merge into tsca A
using DUAL
on (a.sno = :new.sno)
when matched then
update
set a.result =:new.result,
a.grade = grd
when not matched then
insert (sno, sname, result, grade) values (:new.sno, :new.sname, :new.result, grd);
end;
select *
from tsca;
select *
from tsc;
insert into tsc
values (1, 1, 1, 'e');
delete
from tsca
where sno = 1;
select *
from tsca;
select *
from tsc;
create or replace procedure aaaa is
type rcd is record
(
sno tsca.sno%type,
sname tsca.sname%type,
result tsca.result%type,
grade tsca.grade%type
);
r rcd;
cursor curst is select sno, sname, result, 'A'
from tsc;
begin
open curst;
loop
fetch curst into r;
exit when curst%notfound;
if r.result >= 90 then
r.grade := 'A';
elsif r.result >= 80 then
r.grade := 'B';
else
r.grade := 'Z';
end if;
insert into tsca values r;
end loop;
end;
call aaaa();
select *
from tsca;
drop function hak;
create or replace function hak(sal number) return number is
tax number;
begin
if sal >= 7000 then
tax := 0.1;
elsif sal >= 6000 then
tax := 0.2;
else
tax := 0;
end if;
return round(sal * tax);
end;
select e.*, hak(e.sal)
from emp e;
create or replace trigger haki
before insert or update of sal
on emp
referencing new as new
댓글 영역