오라클 pl/sql 프로그램과 서브 프로그램

 

 

개념

---

PL/SQL  (Procedural Language/SQL)

- SQL의 확장. 프로그래밍 언어의 특성을 수용

- 절차적 단위로 프로그래밍을 가능하게 하는 트랜잭션 언어

- 모듈화된 프로그램 개발 가능

- 에러처리 가능

 

PL/SQL 블록 구조 (3부분)

선언부(선택) - 실행부(필수) - 예외처리부(선택)

 

선언부 : 참조할 모든 변수, 상수, 커서, 사용자가 정의한 예외

실행부 : 데이터 처리를 위한 SQL 문장

예외처리부 : 에러와 비정상적 수행에 대한 처리정의

 

 

 

블록 구조

[DECLARE]

  -- 선언부

BEGIN

  -- 실행부

[EXCEPTION]

  --예외처리부

END;

 

※ DECLARE, BEGIN, EXCEPTION 에는 세미콜론(;) 안 붙임

다른 문장은 모두 붙임

[ ]은 선택사항을 의미. 대소문자 상관없음

 

 

 

블록 유형 3가지

 

- Anonymous Block

[DECLARE]

BEGIN
--문장

[EXCEPTION]

END;

 

- Procedure Block

PROCECURE 이름
IS
BEGIN
--문장

[EXCEPTION]

END;

 

- Function Block

FUNCTION 이름
RETURN datatype
IS
BEGIN
--문장

[EXCEPTION]

END;

 

 

 

명령문

 

- 사용자에게 입력값을 받아 변수에 저장

ACCEPT 변수 PROMPT '메세지'

그 후 해당 변수를 블록 내부에서 앰퍼샌드(&)를 써서 참조할 치환변수 선언 

 

- 전역변수 선언

VARIABLE 변수 [NUMBER | CHAR | CHAR(n) | VARCHAR2(n)]

블록 내부에서 전역 변수 앞에 콜론(:)을 사용하여 값 할당

 

- 변수 출력

PRINT 변수

 

- 하나의 PL/SQL 문 실행

프로시저 실행

EXECUTE 실행할 처리문장

 

- 저장된 프로시저 실행

EXECUTE 프로시저이름 [(매개변수리스트)]

 

 

예시

ACCEPT p_sal PROMPT 'Enter the salary'  --사용자에게 입력값 받아 p_sal에 저장(치환변수)
VARIABLE g_year_sal NUMBER    --전역 변수 선언

DECLARE 
    v_sal NUMBER := &p_sal;   -- 입력값을 지역 변수에 할당
BEGIN
    :g_year_sal := v_sal*12;  -- 처리문
END;
/
PRINT g_year_sal

변수명 앞에 &를 쓰는 치환변수 : 블록 외부에서 값을 전달 받음

변수명 앞에 : 을 쓰는 전역변수 : 블록 외부로 값을 전달 

 

 

---

 

서브프로그램

서브프로그램(단위 프로그램) 의 종류

- 프로시저

- 함수

 

 

프로시저, 함수 차이

함수 : RETURN 필수, 식의 일부로써 함수 사용

 

 

서브프로그램 만들기

프로시저 : CREATE PROCEDURE

함수 : CREATE FUNCTION

 

 

삭제

- DROP PROCEDURE

- DROP FUNCTION

 

 

기존에 있던거에서 새로다시 작성하려면 DROP->CREATE 하지말고 다음과 같이

- CREATE OR REPLACE PROCEDURE

- CREATE OR REPLACE FUNCTION

 

 

프로시저 작성

CREATE OR REPLACE PROCEDURE 프로시저이름 [(매개변수...)]
IS
pl/sql_block  -- 프로시저가 수행할 블록

 

 

매개변수 문법

매개변수명 [IN | NOT | IN OUT] datatype [{:= | DEFAULT} 식]

 

 

매개변수 모드

IN : 실행환경에서 서브프로그램으로 값 전달

OUT : 서브프로그램에서 실행환경으로 값 전달

IN OUT : 실행환경->서브프로그램 값 전달하고, 서브프로그램->실행환경 변경된 값 전달

 

 

함수 작성

CREATE OR REPLACE FUNCTION 함수명[(매개변수...)]
RETURN datatype
IS
pl/sql_block  -- 함수가 처리할 블록

 

RETURN 문에는 반환될 값의 datatype 명시

 

 

 

PL/SQL 블록 작성

---

선언부 : 변수 선언, 초기화

실행부 : 변수에 새로운 값 지정

값 전달 : 매개변수

값 출력 : 출력변수

 

 

 

 

중첩 블록

DECLARE    -- 1
    x integer;
BEGIN 
    DECLARE    -- 2
        y number;
    BEGIN
    
    END;   
END;

y 변수는 2번 블록 안에서만 사용가능

 

 

 

%type 속성

- 이미 저장된 테이블의 column 타입 가져오기 : 테이블.컬럼%type

예시)

DECLARE
    v_name salesreps.name%type;

 

- 이미 선언된 변수의 데이터 타입 가져오기 : 변수명%type

예시)

DECLARE
    v_balance number(7,2);
    v_min_bal v_balance%type := 10;

 

 

RECORD 변수

필드의 집합

테이블의 행과는 다름

pl/sql 블록에서 테이블의 행을 읽어 올 때 편리

 

 

 

%rowtype 속성

- db 테이블이나 뷰의 필드 타입들을 가져옴

- record 변수일때 선언 

 

예시) 

stu_record  stu%rowtype;