220503

PL/SQL

DBMS 작성하는 데 사용하는 오라클의 SQL 전용 언어

• Procedural Language/Structured Query Language

• 기존의 ANSI-SQL에 절차 지향 언어의 기능을 추가한 SQL

• ANSI-SQL + 절차적 기능(제어) = PL/SQL

• ANSI-SQL
  비절차 지향 언어, 문장 단위 구조

프로시저, Procedure

작업 순서가 정해진 독립된 프로그램의 수행 단위

• 메소드, 함수
• 순서가 있는 명령어의 집합
• PL/SQL 명령어는 반드시 프로시저 내에서만 작성이 가능하다. (동작가능)

1. 익명 프로시저
   • 1회용 코드 작성용
2. 실명 프로시저
   • 재사용 가능
   • 저장 가능
   • 데이터베이스에 이름을 가지고 객체로 저장

프로시저 블럭 구조

< 4개의 키워드(블럭)로 구성 >
1. DECLARE
2. BEGIN
3. EXCEPTION
4. END

1. DECLARE

   • 선언부
   • 프로시저에서 사용할 변수, 객체 등을 선언하는 영역
   • 생략 가능

2. BEGIN

   • 실행부(구현부)
   • BEGIN ~ END
   • 구현된 코드를 작성하는 영역(메소드 body 역할)
   • 생략 불가능
   • PL/SQL + ANSI-SQL 작성

3. EXCEPTION

   • 예외처리부
   • 예외 처리 코드를 작성하는 영역 (catch 역할)
   • 생략 가능

4. END

   • BEGIN 블럭의 종료 역할
   • 생략 불가능

PL/SQL 기본 작성방법

1. 자료형

   • ANSI-SQL와 동일

2. 변수 선언하기

   • 변수명 자료형 [NOT NULL][DEFAULT 값];
   • 주로 질의(select)의 결과값을 저장하는 용도
   • 일반 변수로서의 역할도 동일하게 한다.

3. 대입 연산자

   • ANSI-SQL와 동일
     - 컬럼명 = 값
     ex) update table set column = '값';
   • PL/SQL
     - 변수명 := 값

-- dbms_output.put_line의 결과 > 기본값: 안보이게 설정 되어 있음
-- 접속할 때 마다 실행해야 함
set serverout on; 
set serverout off;

declare
	-- 선언
    num number;
    name varchar2(20);
    today date;
begin
    num := 10;
    dbms_output.put_line(num);
    
    name := '홍길동';
    dbms_output.put_line(name);
    
    today := sysdate;
    dbms_output.put_line(today);
end;

declare
    num1 number;
    num2 number;
    num3 number := 30; -- 초기화 바로 가능
    
    num4 number default 40;
    num5 number not null := 50;
    num6 number not null default 0; -- 초기화를 뒤로 미룰 때 default / 꼭 초기화 해야 함.
    
begin
    num1 := 10;
    dbms_output.put_line(num1);
    
    dbms_output.put_line('---');
    dbms_output.put_line(num2); -- null이 찍힌거 빈칸으로 보임
    dbms_output.put_line('---');
    
    dbms_output.put_line(num3);

    -- 구현부 변수 선언 X
    -- num4 number; X
    dbms_output.put_line(num4);
    
    dbms_output.put_line(num5);
    
    num6 := 60;
    dbms_output.put_line(num6);
end;

SELECT INTO 절

변수에 SELECT의 결과값을 담을 수 있다.

• SELECT 컬럼 INTO 변수 FROM 테이블
• PL/SQL 블럭 내부에는 절대 일반 SQL을 작성할 수 없다. ★★★
• 반드시 SELECT INTO절을 사용해야 한다. ★★★
• ANSI-SQL의 결과값을 바로 사용하지 못하고 PL/SQL으로 옮겨서 사용이 가능하다. ★★★

ex) select into - 1개의 값을 select > 1개의 PL/SQL 변수에 대입

declare
    vname varchar2(15);
    vbuseo varchar2(15);
begin
   -- vanme := select name from tblInsa where num = 1001;
   -- select 컬럼 into 변수
   select name into vname from tblInsa where num = 1001;
   select buseo into vbuseo from tblInsa where num = 1001;
   
   dbms_output.put_line(vname);
   dbms_output.put_line(vbuseo);
end;

• 주의점(한가지라도 만족 못하면 에러발생)
  1. 컬럼의 개수 = 변수의 개수 일치
  2. 순서 일치
  3. 자료형 일치

ex) select into - N개의 값을 select > N개의 PL/SQL 변수에 대입

declare
    vname varchar2(15);
    vbuseo varchar2(15);
    vjikwi varchar2(15);
begin
    select name, buseo, jikwi into vname, vbuseo, vjikwi from tblInsa where num = 1001;
    
    dbms_output.put_line(vname);
    dbms_output.put_line(vbuseo);
    dbms_output.put_line(vjikwi);
end;

create table tblName(
    name varchar2(15)
);

-- select > tblName 추가
-- 1. ANSI-SQL
-- 2. PL/SQL

-- 1번 > 서브쿼리로
insert into tblname (name) values ((select name from tblInsa where buseo = '개발부' and jikwi = '부장'));

-- 2번
declare
    vname varchar2(15);
begin
    select name into vname from tblInsa where buseo = '개발부' and jikwi = '부장';
    insert into tblname (name) values (vname);
end;

타입 참조

• 변수를 선언할 때 사용
• 사용하는 테이블의 특정 컬럼 자료형을 그대로 참조해서 변수에 적용
• 자료형, 길이, NOT NULL

1. %type
   • 컬럼 1개 참조
2. %rowtype
   • 행 전체 참조(모든 컬럼 참조)

< 프로시저에서 select값을 저장하기 위한 변수를 만드는 경우 >

1. buseo varchar2(15)
   • 정적
2. vbuseo tblInsa.buseo%type >> 권장
   • 동적(참조)

ex) %type

declare
-- 실제 테이블 컬럼을 참조해서 자료형, 길이, not null 제약을 복사해 온다.
    vname       tblInsa.name%type;
    vbuseo      tblInsa.buseo%type;
    vcity       tblInsa.city%type;
    vbasicpay   tblInsa.basicpay%type;
    
begin
    select
        name, buseo, city, basicpay
        into
        vname, vbuseo, vcity, vbasicpay
    from tblInsa where num = 1001;
    
    dbms_output.put_line(vname);
    dbms_output.put_line(vbuseo);
    dbms_output.put_line(vcity);
    dbms_output.put_line(vbasicpay);
end;

ex) %rowtype

- 홍길동의 정보 + 출력
declare
    vrow tblInsa%rowtype; -- vrow > 일종 배열 역할 > 레코드 역할
begin
    -- 전체 테이블 가지고 올거면 와일드카드 *
    select * into vrow from tblInsa where num = 1001;

    -- dbms_output.put_line(vrow); 한번에 덤프 안 해준다
    dbms_output.put_line(vrow.num);
    dbms_output.put_line(vrow.name);
    dbms_output.put_line(vrow.ssn);
    dbms_output.put_line(vrow.ibsadate);
end;