DB練習13

PL/SQL

PL/SQL Oracleのプロセス言語をSQLの略語

に拡張

oracle自体に組み込まれたプログラム言語(変数定義、条件処理、繰返し処理など)をサポートするため、SQL文の使用を拡張し、欠点を保護できます.

変数の宣言と割り当て

宣言は、実行機関が使用できる変数を宣言する.

変数を宣言する場合は、変数名の後の資料型を説明する.

identifier [constant] datatype [not null] [:= } default expression];= 변수 명 [변수의 값 변경할 수 없도록 제약] 자료형 [값을 반드시 포함하도록 제약] default 다른 변수나 연산자, 함수를 포함하는 표현식

PL/SQL文には4種類あります

ストレージプロセス

記憶関数

カーソル

フリップフロップ

きおくてつづき

->SQL文を順番にリストし、oracleサーバに配置

-- 프로시저를 드는데 (프로시저명)
-- is begin
-- 실행할 내용
-- commit으로 밀어넣기
-- end;
-- /

練習1

-- emp 복사해서 emp01 생성
create table emp01 as
    select * from emp;
-- 확인
select * from emp01;

-- 1. 프로시저 정의 ( 만들어서 저장만 해놓음 ) -> 오라클 서버에 저장
-- create or replace procedure 프로시저명 is begin [SQL문 작성] end
create or replace procedure del_all
    is begin
        delete from emp01;
        commit;
    end;
/
-- 2. 만들어 준거 실행하기 -> 오라클 사용하는 클라이언트 쪽에서 호출해 사용
execute del_all;

-- 확인 ( 틀은 살아 있는데 내용이 모두 사라짐 )
select * from emp01;
-- 저장된 프로시저 보기
--( 오라클 서버 안에 저장된 소스 (프로시저, 함수, 커서, 트리거 ) 내용을 본다
select * from user_source;

練習2

-- 김사랑의 급여가 현재+100 되도록 만드는 프로시저 작성
drop table emp01;
create table emp01 as
    select * from emp;

-- 프로시저 정의
create or replace procedure sel_dept10
    is begin
        update emp01 set sal=sal+100 where ename='김사랑';
        commit;
    end;
/
-- 실행
execute sel_dept10;
-- 확인
select ename, sal from emp01 where ename='김사랑';

練習3

-- insert (삽입) 쿼리를 만들어보아요
create or replace procedure insert_data
    is begin
        insert into emp01 (empno, ename, sal, deptno)
                    values (1030, '홍홍홍', 300, 10);
        commit;
    end;
/
-- 적용해보기
execute insert_data;
-- 확인
select * from emp01;

事前保存中のパラメータの使用

プロシージャを定義するときに値を事前に決定せずに、あるタイプだけを書き、使用時に値を入れることもできます.

-- 프로시저를 드는데 (프로시저명)(새변수명 원래테이블.원래변수%type)
-- is begin
-- 실행할 내용
-- commit으로 밀어넣기
-- end;
-- /


-- vename 이라는 변수는 emp01의 ename과 같은 타입이다
create or replace procedure del_ename(vename emp01.ename%type)
    is begin
        delete from emp01 where ename like vename;
        commit;
    end;
/

-- vname 변수에 들어갈 값 쓰기
-- 이 이름이 emp01의 ename으로 가서 지워지게 된다.
execute del_ename('홍홍홍');
-- 확인
select * from emp01;




-- 이름을 넣어주면 그 사람의 월급이 +100 되도록 만들어주시오
create or replace procedure sal_plus (vname emp01.ename%type)
    is begin
        update emp01 set sal=sal+100 where ename=vname;
        commit;
    end;
/
execute sal_plus('이문세');
select * from emp01;

In、Out、Inoutパラメータ

-- in out inout 매개변수
-- in : 입력 받아올 변수 / out : 출력해줄 변수

-- 1. 프로시저 만들기
create or replace procedure sal_empno (vempno in emp01.empno%type, vename out emp01.ename%type,
                                       vsal out emp01.sal%type, vjob out emp01.job%type)
    is begin
        select ename, sal, job into vename, vsal, vjob from emp01 where empno = vempno;
        commit;
    end;
/
-- 2. 실행 전 out 할 변수 선언하기
variable var_ename varchar2(10);
variable var_sal number;
variable var_job varchar2(9);
-- 3. 실행하기
execute sal_empno(1001, :var_ename, :var_sal, :var_job);
-- 4. print로 변수값 출력해보기
print var_ename;
print var_sal;
print var_job;





select * from dept;
-- deptno 10을 입력해서 deptno=10 도 출력하고 dname인 경리부, loc인 서울도 출력되도록 하고 싶다오
-- 1단계 프로시저 만들기 (한번에 inout은 안되고, in 한번, out 한번 따로 정의해줘야함)
create or replace procedure dname_loc (vdeptno in dept.deptno%type, vdeptno1 out dept.deptno%type,
                                        vdname out dept.dname%type, vloc out dept.loc%type)
    is begin
        select deptno, dname, loc into vdeptno1, vdname, vloc from dept where deptno=vdeptno;
        commit;
    end;
/
-- 2. 실행 전 out할 변수 선언하기 
desc dept;
variable var_deptno number;
variable var_dname varchar2(10);
variable var_loc varchar2(10);
-- 3. 실행하기
execute dname_loc (10, :var_deptno, :var_dname, :var_loc);
-- 4. print 해보기
print var_deptno;
print var_dname;
print var_loc;

きおくかんすう

->ストレージ・プロシージャとほぼ同じ用途で使用
->区別:関数は実行結果を返します

-- empno를 받아서 sal을 변경하는 저장함수 만들기
create or replace function cal_bonus (vempno in emp.empno%type)
    return number
    is vsal number;
    begin select sal into vsal from emp where empno = vempno;
        return (vsal * 200);
    end;
/
-- 바이드 변수 선언 (클라이언트 쪽으로 넘길)
variable var_res number;
-- 실행하기
-- execute 함수호출 리턴자료(값)
execute :var_res :=cal_bonus(1001);
-- print해보기(학인)
print var_res;
-- execute 하지 않고 바로 select 문에서도 사용 가능하다
select ename, sal, cal_bonus(1001) from emp where empno=1001;

カーソル

->複数行を含むselect文の処理に使用します.

-- 부서 테이블의 모든 내용을 조회하기
-- 1. 커서를 이용한 프로시저 만들기
select * from dept;
create or replace procedure cursor_sample
    is vdept dept%rowtype;
        cursor c1 is select * from dept;
    begin dbms_output.put_line('부서번호  부서번호  지역명');
          dbms_output.put_line('--------------------------');
    open c1;
    loop fetch c1 into vdept.deptno, vdept.dname, vdept.loc;
    exit when c1%notfound;
    dbms_output.put_line(vdept.deptno||'     ' || vdept.dname || '    ' || vdept.loc );
    end loop;
    close c1;
end;
/
-- 프로시저 호출하기
set serveroutput on
execute cursor_sample;

トリガ

特定のテーブルが変更されたときに自動的にイベントに変更されます.
データがDML文で入力、変更または削除されると、テーブルまたはビューが自動的に実行されます.

-- 직원이 새로 입력되면 메시지 출력하는 트리거 작성
drop table emp01;
create table emp01 (
    empno number(4) primary key,
    ename varchar2(20),
    job varchar2(20) );
    
-- 1단계. 서버에 트리거 정의하기
create or replace trigger trg_sample
    after insert on emp01
    begin dbms_output.put_line('입사를 환영합니다.');
    end;
/
-- insert 해보기
insert into emp01 values(1001, '김사랑', '사원'); -- 입사를 환영합니다. 가 삽입 안내보다 먼저 뜬다




-- 급여 정보를 자동으로 추가하는 트리거 작성
create table sal (
    salno number(4) primary key,
    sal number(7,3),
    empno number(4) references emp01(empno) );
-- 자동으로 넘버링 해주는 시퀀스 생성
create sequence sal_seq;

-- 트리거 정의하기
create or replace trigger sal_trg
    after insert on emp01
    for each row
    begin insert into sal values(sal_seq.nextval, 100, :new.empno);
    end;
/
insert into emp01 values(3, '정', '과장');
insert into emp01 values(4, '박', '과장');
select * from emp01;
select * from sal;

-- 트리거 삭제하기
drop trigger sal_trg;