[오라클] 5월1일 stored procedure, function , procedure with parameter

카테고리 없음

by esoesmio 2023. 5. 1. 17:27

---------------------------------------------------------------------
--emp의 eno, ename에 eno 999 ename 장길산을 저장하는 프로시저

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

--일반화학의 학생별 기말고사 성적을 저장하는 테이블 생성

-- grade까지 가지는 일반화학 학생별 기말고사 성적 테이블

--이 조건으로

--이 점수를 가지고 90 이상이면 a 80 이상이면 b 70 이상이면 c 로 만드는 프로시저 만들겠다.

--1-2. 파라미터가 있는 프로시저

--프로시저 p_new_dept를 만드는데 dept의 dno, dname, loc, director 에 1,5,4,0을 넣는 프로시저를 만들어라.

--stored function

--급여별로 세금 조회하는 함수선언 sal 7000 이상이면 0.1 6000 0.07 5000 0.05 0.03 그리고 emp의 정보와 tax를 보여준다.

--3-1 trigger
-- before trigger
-- 급여가 3000 미만으로 입력됐을 때 에러메세지 출력하는 트리거
--emp에서 급여를 넣기 전에 3000보다 낮으면 저렇게 문구가 뜨는 트리거가 만듥게 되는거

--t_nche_sc 테이블에 데이터가 입력이나 점수가 수정되면 t_nche_Scgr테이블에 데이터가 자동으로 입력되거나
-- 점수가 수정된 거는 grade가 자동 업데이트 되는 크리거

--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

es mio

고정 헤더 영역

메뉴 레이어

메뉴 리스트

검색 레이어

검색 영역

상세 컨텐츠

본문 제목

본문

---------------------------------------------------------------------
--emp의 eno, ename에 eno 999 ename 장길산을 저장하는 프로시저

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

--일반화학의 학생별 기말고사 성적을 저장하는 테이블 생성

-- grade까지 가지는 일반화학 학생별 기말고사 성적 테이블

--이 조건으로

--이 점수를 가지고 90 이상이면 a 80 이상이면 b 70 이상이면 c 로 만드는 프로시저 만들겠다.

--1-2. 파라미터가 있는 프로시저

--프로시저 p_new_dept를 만드는데 dept의 dno, dname, loc, director 에 1,5,4,0을 넣는 프로시저를 만들어라.

--stored function

--급여별로 세금 조회하는 함수선언 sal 7000 이상이면 0.1 6000 0.07 5000 0.05 0.03 그리고 emp의 정보와 tax를 보여준다.

--3-1 trigger
-- before trigger
-- 급여가 3000 미만으로 입력됐을 때 에러메세지 출력하는 트리거
--emp에서 급여를 넣기 전에 3000보다 낮으면 저렇게 문구가 뜨는 트리거가 만듥게 되는거

--t_nche_sc 테이블에 데이터가 입력이나 점수가 수정되면 t_nche_Scgr테이블에 데이터가 자동으로 입력되거나
-- 점수가 수정된 거는 grade가 자동 업데이트 되는 크리거

댓글 영역

추가 정보

인기글

최신글

티스토리툴바

es mio

고정 헤더 영역

메뉴 레이어

메뉴 리스트

검색 레이어

검색 영역

상세 컨텐츠

본문 제목

본문

-----------------------------------------------------------------------emp의 eno, ename에 eno 999 ename 장길산을 저장하는 프로시저

-------------------------------------------------------------일반화학의 학생별 기말고사 성적을 저장하는 테이블 생성

-- grade까지 가지는 일반화학 학생별 기말고사 성적 테이블

--이 조건으로

--이 점수를 가지고 90 이상이면 a 80 이상이면 b 70 이상이면 c 로 만드는 프로시저 만들겠다.

--1-2. 파라미터가 있는 프로시저--프로시저 p_new_dept를 만드는데 dept의 dno, dname, loc, director 에 1,5,4,0을 넣는 프로시저를 만들어라.

--stored function--급여별로 세금 조회하는 함수선언 sal 7000 이상이면 0.1 6000 0.07 5000 0.05 0.03 그리고 emp의 정보와 tax를 보여준다.

--3-1 trigger-- before trigger-- 급여가 3000 미만으로 입력됐을 때 에러메세지 출력하는 트리거--emp에서 급여를 넣기 전에 3000보다 낮으면 저렇게 문구가 뜨는 트리거가 만듥게 되는거

--t_nche_sc 테이블에 데이터가 입력이나 점수가 수정되면 t_nche_Scgr테이블에 데이터가 자동으로 입력되거나-- 점수가 수정된 거는 grade가 자동 업데이트 되는 크리거

댓글 영역

추가 정보

인기글

최신글

티스토리툴바

---------------------------------------------------------------------
--emp의 eno, ename에 eno 999 ename 장길산을 저장하는 프로시저

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

--일반화학의 학생별 기말고사 성적을 저장하는 테이블 생성

--1-2. 파라미터가 있는 프로시저

--프로시저 p_new_dept를 만드는데 dept의 dno, dname, loc, director 에 1,5,4,0을 넣는 프로시저를 만들어라.

--stored function

--급여별로 세금 조회하는 함수선언 sal 7000 이상이면 0.1 6000 0.07 5000 0.05 0.03 그리고 emp의 정보와 tax를 보여준다.

--3-1 trigger
-- before trigger
-- 급여가 3000 미만으로 입력됐을 때 에러메세지 출력하는 트리거
--emp에서 급여를 넣기 전에 3000보다 낮으면 저렇게 문구가 뜨는 트리거가 만듥게 되는거

--t_nche_sc 테이블에 데이터가 입력이나 점수가 수정되면 t_nche_Scgr테이블에 데이터가 자동으로 입력되거나
-- 점수가 수정된 거는 grade가 자동 업데이트 되는 크리거