PL/SQL
8511 ワード
1.PL/SQL作成方法
複雑な構造をクエリーするには、関連するアクションを連続的に実行します.
結果を変数に書き込むか出力できます
-3つの構文
DECLARE/宣言変数
BEGIN/運転部
EXCEPTION/異常発生時に実行する部分
example)
1)if(条件)then~endif
example)
1)loop
example)
example)
example)
1)タイプ定義の
typeタイプ名はテーブル名です.列名%type
index by binary_integer;
2)定義されたタイプで変数を宣言する
3)ループを巻いて値をとる
example)
%rowtypeには、指定したテーブルのすべての列が含まれています.
recordタイプには、指定したテーブル内の任意の計算のみが含まれます.
タイプ定義:タイプタイプタイプ名is record(列リスト);
==>列リストを列名タイプとして定義
example)
(注意:
set serveroutput on;
上記の文を実行してから、Processer、関数などで実装されている印刷文を表示できます.)
使用プロセスの目的
また、sqlfileに記述および格納された複数行のSQL文をブロックに設定して実行することもできます.
必要に応じてorプログラム内のコード間で呼び出すのは難しい.
プロシージャとして保存します.
(javaがmethodを呼び出すように)必要に応じてプロシージャを呼び出すことができます.
実行方法
execを呼び出します.
in、outパラメータは、実行環境で宣言および与えられる必要があります.
example)
目的は、返される値が外部でも使用可能であることを指定することです.
プロセスによって得られるOUTパラメータは、複数行を含む形式であり、<-カーソルを使用することができる.
Cursorの役割はJDBCのResultSetと同じである.複数行の検索結果はcursorオブジェクトに入り、cursurで結果を取得できます.
example)
関数の使用目的
SQLの内蔵関数(MIN、AVG、MAXなど)の代わりに
私自身が関数で使うものを実現します.
実行方法
execで呼び出すこともできます.
また、SQL query文では、関数自体(返される値)を使用することもできます.
executionexample-query文で呼び出す)
TRIGGGER使用の目的
イベント前後の状態をテーブルに保存/管理する
実行方法
イベントの履歴を記録するテーブルAを作成します.
別のテーブルBでイベントが発生した場合(insert、update、delete)
triggerの実装スキームで定義されたように、テーブルAにはイベント履歴が含まれています.
トリガには2つのタイプがあり、イベントbefore/after
example)
複雑な構造をクエリーするには、関連するアクションを連続的に実行します.
結果を変数に書き込むか出力できます
-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~endifdeclare
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~endifdeclare
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)forloopexample)
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 loopexample)
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. TriggerTRIGGGER使用の目的
イベント前後の状態をテーブルに保存/管理する
実行方法
イベントの履歴を記録するテーブル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;
Reference
この問題について(PL/SQL), 我々は、より多くの情報をここで見つけました https://velog.io/@miscaminos/PLSQLテキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol