2023.03.23
sql 에는 pl/sql 이란것이 있다
procedural language 란 것인데
이전 교육에는 배우지 못한 것이 나오니
당황스럽기도 하고 새롭기도 했다
1. PROCEDURE
구문만 보자면 아래와 같다
CREATE or REPLACE PROCEDURE INSERTBOOK (
MYBOOKID in number,
MYBOOKNAME in varchar2,
MYPUBLISHER in varchar2,
MYPRICE in number)
as
begin
insert into book(bookid, bookname, publisher, price)
values(mybookid, mybookname, mypublisher, myprice);
end;
/
create or replace 다음 procedure 를 붙이고 조건에 만족하는 구문을 작성해주면 되는데 근 몇개월동안 select * from ~ 만 쓰다가 이런 구문을 맞닥뜨리니 충격으로 다가왔다
내가 알고 있던 sql 이 다가 아니구나 😂
위 내용을 차례로 분석해보자
create or replace procedure INSERTBOOK ( // procedure 구문 생성
MYBOOKID in number, // number 로 된 MYBOOKID
MYBOOKNAME in varchar2, // varchar2 로 된 MYBOOKNAME
MYPUBLISHER in varchar2, // varchar2 로 된 MYPUBLISHER
MYPRICE in number) // number 로 된 MYPRICE
as
begin
insert into book(bookid, bookname, publisher, price) // procedure 가 실행되면 해당 열 insert
values(mybookid, mybookname, mypublisher, myprice); // 해당 열들의 value
end;
/
즉 procedure 가 실행되면 임의로 만들어 둔 procedure 내의 열들에 insert 구문을 begin 하라는 것이다
영어를 해석만 하면 얼추 내용이 이해가 간다
procedure 의 실행은 아래와 같다
exec insertbook(1, '스포츠과학', '과학서적', 25000);
select * from book;
exec 을 통해 procedure 의 이름과 해당 procedure 에 필요한 value 를 넣어주면 끝!
select * from ~ 으로 테이블에 내가 원하는 값들이 들어갔는지 확인해보자
이런 procedure 는 select ~ 구문을 쓰지 않아도 바로 값을 출력해주는 방식으로도 쓸 수 있다
create or replace procedure AVERAGEPRICE(
AVERAGEVAL out number)
as
begin
select avg(price) into AVERAGEVAL from book
where price is not null;
end;
set serveroutput on;
declare
AVERAGEVAL NUMBER;
begin
AVERAGEPRICE(AVERAGEVAL);
DBMS_OUTPUT.PUT_LINE('책값 평균 : '||AVERAGEVAL);
end;
/
procedure 도 처음보는데 AVERAGEVAL 옆의 out 도 처음본다
이 out 은 보통 우리가 테이블을 만들 때 CREATE TABLE IN system 처럼 in 을 쓰지만 생략하고 쓰기도 하는데 여기서의 in 은 자료의 ‘안’에 넣는것이다
이해가 가지 않을 것이기 때문에 DBMS_OUTPUT.PUT_LINE 구문과 함께 살펴보자
위 procedure 를 실행해보고 set serveroutput on; 을 꼭 실행해주어야 한다 그리고 DBMS_OUTPUT.PUT_LINE 을 실행해보면
이런식으로 우리가 직접 select ~ 어쩌구 해서 평균값을 구해주지 않아도 바로 평균을 구해주는 구문처럼 쓸 수 있다
그런데 만약
create or replace procedure AVERAGEPRICE(
AVERAGEVAL out number)
여기서 out 이 아닌 in 을 쓴다면 오류가 생기기도 하지만 오류 없이 넘어간다 한들
요 에서 원하는 책값 평균에는 값이 들어오지 않는다
즉, out 은 이처럼 ‘밖’으로 출력을 원할 때, in 은 ‘안’에서 자료 정리를 원할 때 쓴다
* in out 을 같이 써주면 모두 가능
2. CURSOR
파이썬에서 DB에 접속하고 sql 을 통해 자료를 불러올 때 커서를 많이 접해봤다
conn.cursor cursor.fetchall()
등으로 많이 활용했다
커서는 우리가 평소에 쓰는 말처럼 깜박이는 커서를 연상하면 이해가 쉽다
현재 어떤 튜플에 커서가 위치해있고 해당 커서에 어떤 명령을 입력하느냐 인것같다
create or replace procedure INTEREST
as
MYINTEREST NUMERIC;
PRICE NUMERIC;
cursor INTERESTCURSOR is select saleprice from orders; // 커서에 어떤 sql 을 불러줄지 설정
begin
myinterest := 0.0;
open interestcursor; // open cursor
loop
fetch interestcursor into price;
exit when interestcursor%notfound;
if price >= 30000 then
myinterest := myinterest + price * 0.1;
else
myinterest := myinterest + price * 0.05;
end if;
end loop;
close interestcursor; // close cursor
DBMS_OUTPUT.PUT_LINE('전체 이익 금액 = '||myinterest);
end;
/
set serveroutput on;
exec interest;
3. TRIGGER
트리거는 어떤 구문이 수행 되었을 때 따라 가게되는 수행구문이다
무언가 건드렸을 때 발동되는 덫을 연상하면 이해가 쉬울것 같다
트리거에는 before 와 after 가 있는데 after 를 통한 로그 자료 기입 구문을 살펴보자
-- trigger 실습 위한 테이블 생성
create table book_log (
BOOKID_1 NUMBER,
BOOKNAME_1 VARCHAR2(40),
PUBLISHER_1 VARCHAR2(40),
PRICE_1 NUMBER);
create or replace trigger AFTERINSERTBOOK // 트리거 설정
after insert on book for each row // 트리거 발동 조건
declare
average number;
begin
insert into BOOK_LOG
values(:new.bookid, :new.bookname, :new.publisher, :new.price);
DBMS_OUTPUT.PUT_LINE('삽입 튜플을 BOOK_LOG 테이블에 백업');
end;
/
insert into book values(16, '스포츠 과학 1', '미디어', 25000); // 트리거 발동 조건 수행
select * from book where bookid = '16'; // 트리거 수행 여부 확인
select * from book_log where bookid_1='16';
4. 사용자 정의 함수
사용자 정의 함수는 여느 언어들과 거의 비슷한다고 느꼈다
그리 어렵진 않으니 아래 구문을 살펴보면서 이해해보자
create or replace function fnc_interest( // 사용자 정의 함수 설정
price number) return int
is
myinterest number;
begin
if price >= 30000 then
myinterest := price * 0.1;
else
myinterest := price * 0.05;
end if;
return myinterest;
end;
/
select custid, orderid, saleprice, fnc_interest(saleprice) interest from orders;
pl/sql 을 통해 내가 모르던 sql의 세계로 발을 담근거 같아 신기하기도 하지만 직접 필요한식으로 짜보려면 조금 익숙해질 필요가 있을것 같다 😂