[DataBase] DB Programming(PL/SQL)

데이터베이스 프로그래밍

• DBMS에 데이터를 정의하고 저장된 데이터를 읽어와 데이터를 변경하는 프로그램을 작성하는 과정
• 일반 프로그래밍과는 데이터베이스 언어인 SQL을 포함한다는 점이 다르다.

데이터 베이스

• mysql : 소용량 데이터 베이스를 위한 것
• oraclr : 대용량 데이터 베이스

PL/SQL

• sql 언어 중 하나
• pl/sql(Procedual Language/Structured : 오라클 전용 DB 프로그래밍 언어

Procedure 프로시저

• 자바의 메소드처럼 어떤 동작을 하는 sql 명령어를 미리 만들어 놓는다.

• 자바의 메소드처럼 빈번하게 사용하는 sql 명령어를 프로시저를 만들어 두면 쉽게 사용할 수 있다.

프로시저 생성

• create or replace procedure 프로시점이름(변수이름 모드 자료형, ...) as [지역변수선언] begin 프로시저가 해야 할 명령어; end;

• create or replace procedure 프로시점이름(변수이름 모드 자료형,...) as [지역변수선언] cusor 별칭 is 프로시저가 해야 할 명령어 begin ; open cursor별칭 loop end loop;

  • 프로시저 생성하고 싶으면 / 과 함께 enter 누르기

  • or replace : 동일 이름의 프로시저 있으면 이름을 바꿔줌

  • 모드

    • in(입력용) : default로 생략가능, 프로시저에서 필요한 값을 읽기(전달) 위한 용도로(레코드 추가하기 위해서) 전달 받기
    • out(출력용) : select 한 결과를 프로시저가 담아서(프로시저에 구해진 값) 돌려줘야 하는 경우 출력하기 위한 용도로 전달 받음

  • localVariable 지역변수 : as와 begin 사이에 프로시저가 실행하기에 필요한 변수 작성

  • 프로시저 명령: 프로시저 명령에 오류 있을 경우 컴파일 오류와 함께 프로시저 생성

    • ex) select sum(saleprice) into total from orders where custid = p_custid; --> 실행한 결과를 total에 저장
    • dbms_output.put.line(total); --> 프로시저 안에서 total 실행
      • 프로시저가 동작하였지만 화면에 결과가 출력되지 않은 문제점 해결 방법 : dbms_output.put_line으로 출력하려면 set serveroutput on;를 먼저 실행해야 함

• cursor의 사용

  • 프로시저안에서 select한 행의 수가 여러건일 때에는 cursor를 사용

  • 결과 한 건 : select name from customer where custid = 1;

  • 결과 여러 건 select name from customer;

    create or replace procedure
    printCustname(
        p_bookid number
    )
    as
        p_custname varchar2(20);
        cursor c is select distinct name from customer c, orders o
        where c.custid = o.custid
        and o.bookid =p_bookid;
    begin
        open c;
        loop
            fetch c into p_custname;// cursor c 실행한 결과를 p_custname에 저장
            exit when c%NOTFOUND;// c 실행시 아무것도 찾지 못하면 loop 탈출
            dbms_output.put_line(p_custname); // custname을 cmd 화면에 출력
        end loop;
        close c;
    end;

프로시저 실행

• exec 프로시저명(변수이름에 맞는 값들)
• call 프로시저명(변수이름에 맞는 값들)

프로시저 호출

• 프로시저 안에서 출력하는 것이 아니기 때문에 프로시저 호출한 결과를 변수에 저장하고 그 변수의 내용을 출력해야 한다. 이것을 하기 위한 또다른 pl/SQL이 필요하다

• declare 변수선언 begin 명령어 end;/

  // 프로시저 생성
  create or replace procedure
  getTotal(
      p_custid in number,
      p_total out number
  )
  as
  begin
      select sum(saleprice) into p_total from 
      orders where custid = p_custid;
  end;
  /

  // 프로시저 호출
  declare
      result number;
  begin
      getTotal(1, result);
      dbms_output.put_line(result);
      // dbms_output.put_line('결과는 ' || result);// 문자와 함께 값 쓸 때 || 사용
  end;
  /

---

Trigger 트리거

• sql에서의 이벤트 처리를 위한 것으로써 어떤 테이블에서 데이터에 이벤트(insert, update, delete)가 일어날 때 자동으로 동작(실행)하는 프로시저

생성

• create or replace trigger 트리거이름 시점 명령어 on 테이블명 for each row declare 변수선언 begin 트리거가 해야 할 명령어들; end;

  • 프로시저 생성하고 싶으면 / 과 함께 enter 누르기
  • 시점 : before, after
    • ex) before : 삭제되기 전의 레코드를 어딘가에 보관하는 용도로 활용 가능, 자식 테이블에 insert가 발생하는 이벤트인데 그 값이 부모테이블에 반드시 참조되는 컬럼인 경우 부모 테이블에 없다면 추가 할 수 없다. 그 경우 먼저 실행하여 부모 테이블에 레코드를 추가해야 한다면 before 시점을 반드시 사용해야 한다.
  • 명령어 : insert, update, delete
  • declare와 begin 사이에 변수 선언

• 트리거에서 사용할 수 있는 키워드

  • :new : 트리거 명령어가 실행된 새로운 레코드(행) 의미
    • 만약 insert가 일어났다면 insert된 레코드 의미
    • 만약, update가 되었다면 update된 레코드 의미
    • ex) :new.dno, :new:dname
  • :old : 트리거 명령어가 실행되기 전 레코드(행) 의미
    • 만약, update가 되었다면 update 되기 전 레코드 의미
    • 만약 delete가 되었다면 delete 되기 전 레코드 의미
    • ex) :old.dno

---

function 함수

• 사용자 정의 함수
• select절에서 사용할 수 있는 함수 : select 절에 사용되어야 해서 반드시 반환 값이 존재해야 한다.
• 사용자가 select 절에 사용할 수 있는 사용자가 필요한 함수 만들기 가능

생성

create or replace function(매개변수명 자료형) return 자료형
is
    변수선언
begin
    함수가 해야할 문장(들)
    return 값;
end;
/

---

Tips

• show errors : 오류 확인
• create table 테이블2 as select * from 테이블1; : 테이블1의 select문 결과를 테이블2로 복사
• 한글로 테이블 만드는 것은 권장하지 않는다.