PL/SQL

1.PL/SQL作成方法
複雑な構造をクエリーするには、関連するアクションを連続的に実行します.
結果を変数に書き込むか出力できます
-3つの構文
DECLARE/宣言変数
BEGIN/運転部
EXCEPTION/異常発生時に実行する部分
example)

declare //변수선언
   vename employees.last_name%type;
   vempno employees.employee_id%type;
   vjob_id employees.job_id%type;
   vdeptname departments.department_name%type;
begin //현재 실행하고싶은 부분
   select employee_id, last_name, job_id, department_name
   into vempno, vename, vjob_id, vdeptname
   from employees e, departments d
   where e.department_id=d.department_id
   and employee_id=145;
   DBMS_OUTPUT.PUT_LINE('emp no:' ||  vempno);
   DBMS_OUTPUT.PUT_LINE('name:' ||  vename);
   DBMS_OUTPUT.PUT_LINE('job:' ||  vjob_id);
   DBMS_OUTPUT.PUT_LINE('department:' ||  vdeptname);
end;

-制御文(制御プログラムフロー)&選択文(条件に応じて実行内容を選択)
1)if(条件)then~endif
example)

declare
   vscore number(3);
begin
   vscore := 50;
   if(vscore>=60)
   then DBMS_OUTPUT.PUT_LINE('합격');
   end if;
   end;

2)if(条件)then~else~endif

declare
   vscore number(3);
begin
   vscore := 50;
   if(vscore>=60)
   then DBMS_OUTPUT.PUT_LINE('합격');
   else DBMS_OUTPUT.PUT_LINE('불합격');
   end if;
   end;
   /

3)if(条件1)then~elsif(条件2)~else~endif

declare
   x number(1);
begin
   x := 3;
   if(x=1)
   then DBMS_OUTPUT.PUT_LINE('하나');
   elsif(x=2) 
   then DBMS_OUTPUT.PUT_LINE('둘');
   elsif(x=3) 
   then DBMS_OUTPUT.PUT_LINE('셋');
   elsif(x=4) 
   then DBMS_OUTPUT.PUT_LINE('넷');
   else
   DBMS_OUTPUT.PUT_LINE('범위 밖의 값');
   end if;
   DBMS_OUTPUT.PUT_LINE('if밖');
   end;
   /

-繰り返し文
1)loop
example)

declare
        x number :=1;
        y number :=0;
    begin
        loop
            y:=y+x;
            x:=x+1;
            if(x>100)
                then exit;
            end if;
        end loop;
        DBMS_OUTPUT.PUT_LINE(y);
    end;
    /

2)forloop
example)

declare
        dan number(1);
        x number:=1;
    begin
        dan:=&dan;        
        for x in 1..9 loop
            DBMS_OUTPUT.PUT_LINE(dan || ' * ' || x || ' = ' || dan*x);
        end loop; 
    end;
    / 

3) while loop
example)

declare
    x number:=1;
begin
    while x<10 loop
        DBMS_OUTPUT.PUT_LINE(x);
        x:=x+1;
    end loop;
end;

-表タイプ(複数行のタイプを含むことができます)
1)タイプ定義の
typeタイプ名はテーブル名です.列名%type
index by binary_integer;
2)定義されたタイプで変数を宣言する
3)ループを巻いて値をとる
example)

declare
    type ename_arr is table of employees.last_name%type
    index by binary_integer;
    type job_arr is table of employees.job_id%type
    index by binary_integer;
    
    names ename_arr;
    jobs job_arr;
    
    i binary_integer:=0; 
begin
    for k in(select last_name, job_id from employees) loop
        i:=i+1;
        names(i):=k.last_name;
        jobs(i):=k.job_id;
    end loop;
    
    for j in 1..i loop
        DBMS_OUTPUT.PUT_LINE(names(j) || ' / ' || jobs(j));
    end loop;
end;

-レコードタイプ
%rowtypeには、指定したテーブルのすべての列が含まれています.
recordタイプには、指定したテーブル内の任意の計算のみが含まれます.
タイプ定義:タイプタイプタイプ名is record(列リスト);
==>列リストを列名タイプとして定義
example)

declare
    type emp_record is record(
    vnum employees.employee_id%type,
    vname employees.last_name%type,
    vsalary employees.salary%type,
    vjob employees.job_id%type);
    
    vemp_rec emp_record;
begin
    select employee_id, last_name, salary, job_id
    into vemp_rec 
    from employees
    where employee_id=100;
    DBMS_OUTPUT.PUT_LINE(vemp_rec.vnum || ' / ' || 
    vemp_rec.vsalary || ' / ' || vemp_rec.vjob || ' / ' || 
    vemp_rec.vname);
end;
/

2. Procedure
(注意:
set serveroutput on;
上記の文を実行してから、Processer、関数などで実装されている印刷文を表示できます.)
使用プロセスの目的
また、sqlfileに記述および格納された複数行のSQL文をブロックに設定して実行することもできます.
必要に応じてorプログラム内のコード間で呼び出すのは難しい.
プロシージャとして保存します.
(javaがmethodを呼び出すように)必要に応じてプロシージャを呼び出すことができます.
実行方法
execを呼び出します.
in、outパラメータは、実行環境で宣言および与えられる必要があります.
example)

create or replace PROCEDURE PROC1 
(
  EMP_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE,
  emp_name out employees.last_name%type,
  emp_salary out employees.salary%type
) AS 
BEGIN
  select last_name, salary into emp_name, emp_salary 
  from employees 
  where employee_id=emp_id;
END PROC1;

実行例)

var emp_name varchar2(25);
var emp_salary number;
exec proc1(206, :emp_name, :emp_salary); 
/*procedure_name(input 변수; :반환받을 변수1,:반환받을 변수2))*/
print emp_name;
print emp_salary;

ここで、変数の前:bindingを表します.
目的は、返される値が外部でも使用可能であることを指定することです.
プロセスによって得られるOUTパラメータは、複数行を含む形式であり、<-カーソルを使用することができる.
Cursorの役割はJDBCのResultSetと同じである.複数行の検索結果はcursorオブジェクトに入り、cursurで結果を取得できます.
example)

create or replace PROCEDURE PRODUCT_LIST AS 
    cursor c is select * from product order by sno; 
    r product%rowtype; 
    /*product테이블의 한줄을 담을 수 있는 type */
BEGIN
  open c; 
  /*cursor open한 후 사용*/
    loop
        fetch c into r; 
        /*c 한개씩 fetch해서 r에 담아라*/
        exit when c%NOTFOUND; 
        /*더이상 c에서 읽을것이 없을때 exit*/
        DBMS_OUTPUT.PUT_LINE('sno:'||r.sno||'/name:'||
                r.name||'/company:'||r.company||'/p_date:'||r.p_date||
                '/price:'||r.price||'/quantity:'||r.quantity);
    end loop;
    close c; 
    /*cursor 사용완료 후 close*/
END PRODUCT_LIST;

実行例)

exec product_list(); 

3.Function(関数)
関数の使用目的
SQLの内蔵関数(MIN、AVG、MAXなど)の代わりに
私自身が関数で使うものを実現します.
実行方法
execで呼び出すこともできます.
また、SQL query文では、関数自体(返される値)を使用することもできます.
executionexample-query文で呼び出す)

select make_name(first_name, last_name) name
from employees;

exec呼び出しexec)

exec get_product_by_name('신라면');

exception文を作成します.例外が発生しても、関数は常に値を返します.次の例に示します.

create or replace FUNCTION MAKE_GRADE 
(
  SCORE IN NUMBER 
) RETURN VARCHAR2 AS 
    grade varchar2(20):='잘못된 점수';
    score_range_exception exception;
BEGIN
    if(score>100 or score < 0)
        then raise score_range_exception;--예외를 강제로 발생
        else 
            if(score>=90) then grade:='A';
            elsif(score>=80) then grade:='B';
            elsif(score>=70) then grade:='C';
            elsif(score>=60) then grade:='D';
            else grade:='F';
            end if;
    end if;
    RETURN grade;
    exception 
        when score_range_exception 
            then DBMS_OUTPUT.PUT_LINE('score_range_exception 발생'); 
                    RETURN grade;
END MAKE_GRADE;

4. Trigger
TRIGGGER使用の目的
イベント前後の状態をテーブルに保存/管理する
実行方法
イベントの履歴を記録するテーブルAを作成します.
別のテーブルBでイベントが発生した場合(insert、update、delete)
triggerの実装スキームで定義されたように、テーブルAにはイベント履歴が含まれています.
トリガには2つのタイプがあり、イベントbefore/after
example)

CREATE OR REPLACE TRIGGER TRIGGER1 
after DELETE OR INSERT OR UPDATE /*after:사건(delete,insert,update) 발생 후 */
ON emp
for each row
BEGIN /*사건의 종류별 실행 사항 나열*/
/*여기 쓰이는 old(사건 전), new(사건 후)는 정해진 binding keyword*/
  if inserting then insert into emp_tmp 
    values(:new.employee_id, :new.last_name, :new.job_id,
    :new.salary, 'i'); /*마지막은 동작 종류*/
  elsif updating then insert into emp_tmp 
    values(:old.employee_id, :old.last_name, :old.job_id,
    :old.salary, 'u');
  elsif deleting then insert into emp_tmp 
    values(:old.employee_id, :old.last_name, :old.job_id,
    :old.salary, 'd');
  end if;
END;

実行例)

create table emp_temp 
as select employee_id, last_name, job_id, salary
from employees where 1=0;

alter table emp_temp
add (action varchar2(1));

insert into emp values(206, 'aaa', 'bbb', 'emaila', '111.222.3333', 
sysdate, 'MK_MAN', 13000, null, 100, 20);

update emp set salary=15000 where employee_id=206;
delete emp where employee_id=206;

select * from emp_temp;