[Oracle] 오라클, 프로시저 구문 예제 및 응용법(1)

프로시저, Procedure

- 함수,메소드,서브루틴 등..

- 특정 목적을 가지고 모인 순서대로 실행하는 명령어의 집합.

프로시저 종류

1. 익명 프로시저 : 이름없음 > 재사용 목적X > 동작방식(표준 SQL 동일) ,확장기능 때문에 사용

2. 실명 프로시저 : 이름있음 > 재사용 목적O > 동작방식(PL / SQL 동일 , 재사용)

*SQL Developer에서 출력문인 dbms_output.put_line()을 사용하기 위해서 서버 출력을 켜는 쿼리

set serveroutput on;

PL / SQL 블럭구조

1. 4개의 키워드로 구성

    a. [declare]

    b. begin

    c. [exception]

    d. end

2. declare 

- 선언부, declare section

- 프로그램에서 사용되는 변수, 객체 등을 선언하는 영역

- 생략 가능

3. begin

- 실행부, 구현부, executable section

- begin ~ end

- 프로그램에서 실제 구현 내용들을 선언하는 영역

- 표준 SQL + PL / SQL(연산,제어 등..)

- 생략 불가능

4. exception

- 예외 처리부,exception section

- catch절 역할

- 예외 처리 코드를 선언하는 영역

- 생략 가능

5. end 

- 블럭의 종료

- 생략 불가능

6. PL/SQL 블럭 = 선언부 + 실행부 + 예외처리부

declare

    변수,자원 선언

begin

    구현부

exception

    예외처리부

end;

자료형 & 변수

자료형

- 표준 SQL 동일(거의 유사 + 확장)

변수 선언하기

- 변수명 자료형[not null][default값]

- 표준 SQL 에서 컬럼정의하는 구문과 유사

- 변수의 역할:질의(select)의 결과나 인자값을 저장하는 용도

연산자

- 표준SQL연산자와 동일

표준 SQL 대입연산자

- 컬럼명=값; //update table set buseo ='영업부'; (대입연산자)

- 용도 : 컬럼값 대입

    

PL/SQL 대입 연산자

- 변수 :=값;

- 용도 : 변수값 대입

예제

익명 프로시저

declare

    num number;

    name varchar2(30);  --주목

    today date;

begin

    num:=10; --정수형 리터럴(표준SQL동일)

    dbms_output.put_line(num);

    

    name:='홍길동';

    dbms_output.put_line(name);

    

    today :=sysdate;

    dbms_output.put_line(today);

    dbms_output.put_line(to_char(today,'yyyy-mm-dd'));

    

    today :='2018-09-04';

    dbms_output.put_line(today);

    

    name:='홍길동입니다.안녕하세요';  여기서 메모리 이상의 값 할당

    dbms_output.put_line(name); --ORA-06502: PL/SQL: numeric or value error: character string buffer too small 에러

end;

테이블에서 조회한 데이터를 변수에 담기

예제1

declare

    vbuseo varchar2(15);

begin

    --vbuseo := (select buseo from tblInsa where name='홍길동'); --이 표현은 실패

    

    --select의 결과 컬럼값을 변수에 대입 > into

    select buseo into vbuseo from tblInsa where name='홍길동';

    dbms_output.put_line(vbuseo);

end;

예제2

declare

    vbuseo varchar2(100);   --선언한 변수의 길이가 테이블의 길이보다 더 크건 상관없다(overflow x) <> 작으면 안되는경우가 있다.

begin  

    select buseo into vbuseo from tblInsa where name='홍길동';

    dbms_output.put_line(vbuseo);

end;