'DBMS/오라클 PLSQL' 카테고리의 글 목록

DBMS/오라클 PLSQL

2. 기본

2015. 2. 13. 16:20

구분자>

식별자 >

상수, 변수, 예외, 커서, 커서 변수, 하위 프로그램 및 패키지를 포함하여 PL/SQL 프로그램 항목과 단위 이름을 지정하는데 사용

예)

phone#

credit_limit

LastName

oracle$number

식별자는 한 문자나 여러 문자, 숫자, 달러 기호($), 밑줄, 번호 기호( # )로 구성됩니다.

다음 예와 같이 하이픈, 슬래시, 공백과 같은 다른 문자는 허용되지 않습니다.

mine&yours -- illegal ampersand

debit-amount -- illegal hyphen

on/off -- illegal slash

user id -- illegal space

다음 예와 같이 달러 기호, 밑줄 및 번호 기호를 중간과 끝에 사용할 수 있습니다.

money$$$tree

SN##

try_again_

대문자, 소문자 또는 대소문자를 모두 사용하여 식별자를 작성할 수 있습니다. PL/SQL에서는 문자열과 문자 리터럴 안에 사용될 경우 외에는 대소문자를 구분하지 않으므로 다음 예와 같이 대소문자 표기만 다른 식별자는 동일한 것으로 간주합니다.

lastname

LastName -- same as lastname

LASTNAME -- same as lastname and LastName

식별자의 길이는 30자를 초과할 수 없습니다.

문자열 리터럴 >

문자 값은 식별자로 표현하거나 문자열 리터럴로 명시적으로 작성될 수 있습니다. 문자열 리터럴은 인접한 0이나 다른 문자를 작은 따옴표로 묶는 것입니다.

예)

’Hello, world!’

’XYZ Corporation’

’10-NOV-91’

’He said ”Life is like licking honey from a thorn.”’

’$1,000,000’

널 문자열(’’)을 제외한 모든 문자열 리터럴은 CHAR 데이터 유형입니다. 작은 따옴표(’)는 문자열 리터럴의 구분자로 사용되며 문자열 안에 작은 따옴표를 표시하는 방법은 다음 예와 같이 작은 따옴표 두 개를 사용하여 나타냅니다.

’Don’’t leave without saving your work.’

PL/SQL 문자열 리터럴에서는 대소문자를 구분합니다. 예를 들어, 다음 두 리터럴은 서로 다른 것으로 간주됩니다.

’baker’

’Baker’

Boolean 리터럴 >

Boolean 리터럴은 미리 정의된 값인 TRUE, FALSE, NULL 을 의미합니다. (NULL은 빠진 값, 알 수 없는 값 또는 적용할 수 없는 값을 나타냅니다.) Boolean 리터럴은 문자열이 아닌 값이라는 점에 주의.

예를 들어, TRUE는 숫자 25와 마찬가지로 하나의 값입니다.

주석 >

단일 행 : --

복수 : /* */

내장 데이터 유형 >

1. 숫자 유형

1) BINARY_INTEGER

- 부호가 붙은 정수를 저장합니다. BINARY_INTEGER 데이터 유형의 크기 범위는 - 2147483647 . .2147483647입니다.

2) NUMBER

- 실제로 임의 크기의 고정 또는 부동 소수점 숫자를 저장합니다.

- NUMBER[(precision,scale)]

; 자리수의 총 개수를 나타내는 전체 자리수와 소수점의 오른쪽에 있는 자리수를 나타내는 소수점 이하 자리수를 지정

- 소수점이 없는 정수를 선언하려면 다음 형식을 사용합니다.

NUMBER(precision) -- same as NUMBER(precision,0)

3) PLS_INTEGER

- 부호가 붙은 정수를 저장하려면 P L S I N T E G E R 데이터 유형을 사용합니다. PLS_INTEGER 데이터 유형의 크기 범위는 -2147483647 . . 2147483647입니다.

2. 문자 유형

1) CHAR

- CHAR 데이터 유형을 사용하여 고정 길이의 문자 데이터를 저장합니다.

- 최대 길이 32767바이트까지 지정

CHAR[(maximum_length)]

- 주의 할 점은 최대 길이는 문자가 아니라 바이트로 지정한다는 점

2) LONG과 LONG RAW

- LONG 데이터 유형은 가변 길이 문자열을 저장하는데 사용됩니다.

- LONG RAW 데이터 유형은 이진 데이터나 바이트 문자열을 저장하는데 사용됩니다.

- LONG 열에는 텍스트, 문자 배열 또는 짧은 문서까지 저장할 수 있습니다. UPDATE ,INSERT 및 (대부분의) SELECT 문에서는 LONG 열을 참조할 수 있지만 표현식, SQL함수 호출 또는 WHERE, GROUP B Y 및 CONNECT BY 와 같은 특정 SQL 절에서는 LONG열을 참조할 수 없습니다.

3) RAW

- 이진 데이터나 바이트 문자열을 저장합니다. 예를 들어,RAW 변수는 그래픽 문자나 디지털화된 그림을 저장할 수 있습니다.

- RAW(maximum_length)

4) ROWID와 UROWID

- 모든 데이터베이스 테이블에는 ROW I D라는 이진 값을 저장하는 ROWID 의사 열이 있습니다. 각 ROWID는 행의 저장 영역 주소를 나타냅니다. 물리적 ROWID는 일반 테이블의 한 행을 나타내고 논리적 ROWID는 인덱스 구성 테이블의 한 행을 나타냅니다.

- 형식은 네 부분으로 구성됩니다.

OOOOOOFFFBBBBBBRRR

■ O O O O O O : 데이터베이스 세그먼트를 식별하는 데이터 객체 번호. 테이블 클러스터와 같은 세그먼트에 있는 스키마 객체는 동일한 데이터 객체 번호를 갖습니다.

■ F F F : 행이 포함된 데이터 파일을 식별하는 파일 번호. 각 파일 번호는 데이터베이스 내에서는 고유합니다.

■ B B B B B B : 행이 포함된 데이터 블록을 식별하는 블록 번호. 블록 번호는 테이블스페이스가 아닌 데이터 파일과 관련되어 있으므로 두 행이 동일한 테이블스페이스에 있으나 서로 다른 데이터 파일에 있는 경우 블록 번호가 동일할 수 있습니다.

■ RRR: 블록 내의 행을 식별하는 행 번호

5) VARCHAR2

- 가변 길이의 문자 데이터를 저장합니다. 데이터가 내부적으로 표시되는 방법은 데이터베이스 문자 집합에 따라 다릅니다.

3. NLS 문자 유형

1) NCHAR

- NCHAR 데이터 유형을 사용하여 고정 길이(필요한 경우 공백을 채워 넣음)의 NLS 문자 데이터를 저장합니다.

- 최대 길이 32767바이트까지 지정할 수 있는 선택 매개변수를 가집니다. NCHAR 데이터 유형의 구문은 다음과 같습니다.

NCHAR[(maximum_length)]

2) NVARCHAR2

- 가변 길이의 NLS 문자데이터를 저장하려면 NVARCHAR2 데이터 유형을 사용합니다.

- 최대 길이 32767바이트까지 지정할 수 있는 필수 매개변수를 가지며 구문은 다음과 같습니다.

NVARCHAR2(maximum_length)

4. LOB 유형

L O B( Large Objects) 데이터 유형인 BFILE, BLOB, CLOB 및 NCLOB는 텍스트, 그래픽 이미지, 비디오 클립 및 사운드 웨이브폼 등 구조화되지 않은 데이터 블록을 최대 4 GB까지 저장하며 효율적이고, 임의적이며, 세분화된 데이터 액세스가 가능합니다.

LOB 유형은 데이터에 대한 임의적 액세스를 지원하지만 L O N G은 순차적 액세스만 지원합니다.

1) BFILE

- BFILE 데이터 유형은 데이터베이스 외부의 운영 체제 파일에 대형 이진 객체를 저장하는데 사용됩니다.

- 읽기 전용이므로 수정할 수 없습니다.

- 크기는 시스템에 따라 다르지만 4 G B ( 2 * * 3 2 -1바이트)를 초과할 수 없습니다.

- B F I L E은 트랜잭션에서는 사용되지 않고 복구가 불가능하며 복제될 수 없습니다.

- 열수 있는 최대 BFILE 수는 시스템에 따라 Oracle 초기화 매개변수 SESSION_MAX_OPEN_FILES에 의해 설정됩니다.

2) BLOB

- BLOB 데이터 유형은 데이터베이스 행 안이나 밖에 있는 대형 이진 객체를 저장하는데 사용됩니다. 모든 BLOB 변수는 대형 이진 객체를 가리키는 로케이터를 저장합니다.

- BLOB의 최대 크기는 4 G B입니다.

- 트랜잭션에 사용되며 복구와 복제가 가능합니다.

3) CLOB

- CLOB 데이터 유형은 행 안이나 행 밖에서 데이터베이스에 있는 싱글 바이트 문자 데이터의 대형 블록을 저장하는데 사용됩니다.

- CLOB의 최대 크기는 4 G B입니다.

- 트랜잭션에 사용되며 복구와 복제가 가능합니다.

4) NCLOB

- NCLOB 데이터 유형은 행 안이나 행 밖에서 데이터베이스에 있는 여러 바이트의 NCHAR 데이터 대형 블록을 저장하는데 사용됩니다. 고정 너비와 가변 너비의 문자 집합이 모두 지원됩니다. 모든 NCLOB 변수는 NCHAR 데이터 대형 블록을 가리키는 로케이터를 저장합니다.

- NCLOB의 최대 크기는 4 G B입니다.

- NCLOB는 트랜잭션에 사용되며 복구와 복제가 가능합니다.

5. 기타 유형

1) BOOLEAN

- BOOLEAN 데이터 유형은 매개변수를 가질 수 없으며 BOOLEAN 변수에는TRUE, FALSE 및 NULL 값만 할당될 수 있습니다.

2) DATE

- 고정된 길이의 날짜/시간 값을 저장하는데 사용됩니다. DATE값에는 자정부터 초 단위 시간이 포함됩니다. 날짜 부분의 기본값은 현재 달의 1일이고 시간 부분의 기본값은 자정입니다.

- 기본 날짜 형식은 Oracle 초기화 매개변수 NLS_DATE_FORMAT에 의해 설정

- PL / SQL에서는 산술 표현식에서 정수 리터럴을 일로 해석합니다. 예를 들어, SYSDATE + 1은 내일입니다.

저작자표시

'DBMS > 오라클 PLSQL' 카테고리의 다른 글

1. PL/SQL 개요 (0)	2015.02.11
프로시져 실행 샘플 - OUT 값이 여러행인 경우 (0)	2015.01.21
pl/sql, procedure, function (0)	2013.01.27

1. PL/SQL 개요

2015. 2. 11. 17:10

P L / S Q L에서는 SQL 문을 사용하여 O racle 데이터와 그 데이터를 처리하는 흐름 제어문을 조작할 수 있으며 또한, 상수 및 변수를 선언하고 프로시저 및 함수를 정의하며 런타임 오류를 잡을 수 있습니다. PL/SQL에서는 이와 같이 S Q L의 데이터 조작능력과 절차적 언어의 데이터 처리 능력을 결합합니다.

P L / S Q L은 블록 구조로 이루어진 언어입니다. 즉, PL/SQL 프로그램을 구성하는 기본 단위(프로시저, 함수, 익명 블록)는 임의의 수의 중첩된 하위 블록을 포함할 수 있는 논리 블록입니다.

블록(또는 하위 블록)을 사용하여 논리적으로 관련된 선언과 명령문을 그룹화하여 사용되는 위치 가까이에 선언을 넣을 수 있습니다. 이러한 선언은 해당 블록에 대해 지역적이며 그 블록이 완료될 때 소멸됩니다.

[ D E C L A R E

-- d e c l a r a t i o n s ]

B E G I N

-- s t a t e m e n t s

[ E X C E P T I O N

-- h a n d l e r s ]

E N D ;

PL/SQL 블록은 선언부, 실행부, 예외 처리부, 세 부분으로 구성.

(PL/SQL에서 예외란 경고 또는 오류 조건을 의미합니다.) 이 중 실행부는 반드시 필요합니다.

선언부가 우선하며 일단 선언되면 항목은 실행부에서 조작할 수 있습니다. 실행 도중 발생된 예외는 예외 처리부에서 처리됩니다.

하위 블록은 PL/SQL 블록이나 하위 프로그램의 실행 및 예외 처리부에서는 중첩될수 있지만 선언부에서는 중첩될 수 없습니다. 또한 선언부의 임의의 블록에서는 로컬 하위 프로그램을 정의할 수 있지만 정의된 블록에서만 해당 로컬 하위 프로그램을 호출할 수 있습니다.

변수와 상수

PL / SQL에서는 상수와 변수를 선언한 다음 SQL과 표현식 사용이 가능한 절차문에서 상수와 변수를 사용할 수 있습니다.

상수나 변수를 먼저 선언해야만 다른 선언문을 비롯한 다른 명령문에서 참조할 수 있습니다.

변수 선언

변수는 CHAR, DATE 또는 NUMBER와 같은 SQL 데이터 유형이나 BOOLEAN 또는 BINARY INTEGER와 같은 PL/SQL 데이터 유형을 가질 수 있습니다.

예를 들어, 4자리 숫자를 갖는 partno라는 변수와 TRUE나 FALSE와 같은 부울 값을 갖는 instock이라는 변수는 다음과 같이 선언합니다.

part_no NUMBER(4);

in_stock BOOLEAN;

또한 TABLE, VARRAY, RECORD 복합 데이터 유형을 사용하여 중첩 테이블, 가변 길이 배열(약어는 가변 배열)과 레코드를 선언할 수 있습니다.

변수에 값 할당

두 가지 방법으로 변수에 값을 할당할 수 있습니다. 첫 번째 방법은 콜론에 등호를 붙인 할당 연산자( := )를 사용하는 것입니다.

다음 예와 같이 연산자의 좌변에는 변수를,우변에는 표현식을 넣습니다.

tax := price * tax_rate;

bonus := current_salary * 0.10;

amount := TO_NUMBER(SUBSTR(’750 dollars’, 1, 3));

valid := FALSE;

두 번째 방법은 데이터베이스 값을 선택하거나 인출하여 변수에 값을 할당하는 것입니다. 다음은 사원의 급여를 선택할 때 10 %의 보너스를 계산하는 예입니다.

SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id;

bonus 변수를 다른 계산에서 사용하거나 그 값을 데이터베이스 테이블에 삽입할 수있습니다.

상수 선언

상수 선언 방법은 변수 선언과 유사하나 CONSTANT 키워드를 추가하고 즉시 해당 상수에 값을 할당해야 합니다. 이후에는 더 이상 상수 값을 할당할 수 없습니다.

다음은 credit_limit이라는 상수를 선언하는 예입니다.

credit_limit CONSTANT REAL := 5000.00;

커서

커서라는 PL/SQL 구성을 사용하여 작업 영역의 이름을 지정하고 저장된 정보에 액세스 할 수 있으며, 암시적 커서와 명시적 커서 두 종류가 있습니다. PL/SQL은 하나의 행만 반환하는 질의를 포함하여 모든 SQL 데이터 조작문에 대해 커서를 암시적으로 선언하고 하나 이상의 행을 반환하는 질의에는 다음 예와 같이 개별적으로 행을 처리할 커서를 명시적으로 선언할 수 있습니다.

DECLARE

CURSOR c1 IS

SELECT empno, ename, job FROM emp WHERE deptno = 20;

복수 행 질의 처리는 파일 처리와 유사합니다. 예를 들어, COBOL 프로그램은 파일을 열고 레코드를 처리한 다음 파일을 닫습니다. 마찬가지로 PL/SQL 프로그램은 커서를 열고 질의에 의해 반환된 행을 처리한 다음 커서를 닫습니다. 파일 포인터가 열린 파일에서의 현재 위치를 표시하는 것처럼 커서는 결과 집합에서 현재 위치를 표시합니다.

OPEN, FETCH, CLOSE 문을 사용하여 커서를 제어합니다. OPEN 문은 커서와 연관된 질의를 실행하여 결과 집합을 식별하고 첫 번째 행 앞에 커서를 놓습니다. FETCH 문은 현재 행을 읽어 들인 후 다음 행으로 커서를 보냅니다. C L O S E 문은 마지막 행 처리가 끝나면 커서를 사용할 수 없도록 합니다.

커서 FOR 루프

명시적 커서가 필요한 대부분의 경우에는 OPEN, FETCH, CLOSE 문 대신 커서 FOR루프를 사용하여 코딩을 단순화할 수 있습니다. 커서 FOR 루프는 루프 인덱스를 데이터베이스에서 인출된 행을 나타내는 레코드로 암시적으로 선언한 다음 커서를 열고 결과 집합에서 행 값을 레코드 필드로 반복하여 인출한 후 모든 행이 처리되면 커서 를 닫습니다. 다음은 커서 FOR에 있는 루프가 emp_rec를 레코드로 암시적으로 선언하는 예입니다.

DECLARE

CURSOR c1 IS

SELECT ename, sal, hiredate, deptno FROM emp;

...

BEGIN

FOR emp_rec IN c1 LOOP

...

salary_total := salary_total + emp_rec.sal;

END LOOP;

레코드의 각 필드를 참조하려면 구성 요소 선택자 역할을 하는 점( . ), 점 표기법을 사용합니다.

커서 변수

커서 변수는 커서와 마찬가지로 복수 행 질의의 결과 집합에서 현재 행을 가리키지만, 커서와 달리 특정 질의와 결합되지 않고 임의의 유형과 호환 가능한 질의에 대해 열릴 수 있습니다. 커서 변수는 새로운 값을 할당하고 오라클 데이터베이스에 저장된 하위 프로그램에 전달할 수 있다는 점에서 전형적인 PL/SQL 변수로 데이터 검색을 중앙 집중화할 수 있는 융통성 있고 편리한 방법을 제공합니다.

일반적으로 커서 변수를 형식 매개변수 중 하나로 선언한 내장 프로시저로 전달하여 커서 변수를 엽니다. 다음 프로시저는 선택된 질의에 대해 커서 변수 generic_cv를 엽니다.

PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp,choice NUMBER) IS

BEGIN

IF choice = 1 THEN

OPEN generic_cv FOR SELECT * FROM emp;

ELSIF choice = 2 THEN

OPEN generic_cv FOR SELECT * FROM dept;

ELSIF choice = 3 THEN

OPEN generic_cv FOR SELECT * FROM salgrade;

END IF;

속성

%TYPE

%TYPE 속성은 변수 또는 데이터베이스 열의 데이터 유형을 제공합니다. 이 속성은 데이터베이스 값을 저장할 변수를 선언할 때 특히 유용합니다. 예를 들어, books라는 테이블에 title이라는 열이 있을 경우 title 열과 동일한 데이터 유형으로 mytitle이라는 변수를 선언하려면 다음과 같이 점 표기법과 %TYPE 속성을 사용하면 됩니다.

my_title books.title%TYPE;

title의 정확한 데이터 유형을 몰라도 되고 둘째, title의 데이터베이스 정의를 (예를 들어, 더 긴 문자열로) 변경하는 경우 mytitle의 데이터 유형도 실행 시간에 그에 따라 변경됩니다.

%ROWTYPE

PL/SQL에서 레코드는 데이터를 그룹화하는데 사용되며 데이터 값을 저장할 수 있는 여러 관련 필드로 구성됩니다.

%ROWTYPE 속성은 테이블의 한 행을 나타내는 레코드유형을 제공하며 테이블에서 선택하거나 또는 커서나 커서 변수에서 인출한 데이터행 전체를 저장할 수 있습니다.

행의 열과 레코드의 해당 필드는 동일한 이름과 데이터 유형을 가집니다.

다음은 dept_rec 레코드를 선언하는 예입니다. dept_rec 레코드의 필드는 dept 테이블의 열과 동일한 이름 및 데이터 유형을 가집니다.

DECLARE

dept_rec dept%ROWTYPE; -- declare record variable

필드를 참조하려면 다음 예와 같이 점 표기법을 사용합니다.

my_deptno := dept_rec.deptno;

사원의 이름, 급여, 고용일, 직책을 읽어 들이는 커서를 선언하려는 경우 다음과 같이 %ROWTYPE을 사용하여 동일한 정보를 저장하는 레코드를 선언할 수 있습니다.

DECLARE

CURSOR c1 IS

SELECT ename, sal, hiredate, job FROM emp;

emp_rec c1%ROWTYPE; -- declare record variable that represents a row fetched from the emp table

다음 명령문을 실행하면

FETCH c1 INTO emp_rec;

emp_rec의 ename 필드에 emp 테이블의 ename 열 값이 지정되고 sal 필드에 sal열 값이 지정됩니다.

예제 테이블 >

CREATE TABLE SCOTT.DEPT
(
DEPTNO   NUMBER (2) NOT NULL,
DNAME    VARCHAR2 (14),
LOC      VARCHAR2 (13)
)

;

CREATE TABLE SCOTT.EMP
(
EMPNO      NUMBER (4) NOT NULL,
ENAME      VARCHAR2 (10),
JOB        VARCHAR2 (9),
MGR        NUMBER (4),
HIREDATE   DATE,
SAL        NUMBER (7,2),
COMM       NUMBER (7,2),
DEPTNO     NUMBER (2),
CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES SCOTT.DEPT (DEPTNO)
)

;

CREATE TABLE SCOTT.DEPT
(
DEPTNO   NUMBER (2) NOT NULL,
DNAME    VARCHAR2 (14),
LOC      VARCHAR2 (13)
)

;

예제 데이터 >

저작자표시

'DBMS > 오라클 PLSQL' 카테고리의 다른 글

2. 기본 (0)	2015.02.13
프로시져 실행 샘플 - OUT 값이 여러행인 경우 (0)	2015.01.21
pl/sql, procedure, function (0)	2013.01.27

프로시져 실행 샘플 - OUT 값이 여러행인 경우

2015. 1. 21. 17:33

CREATE OR REPLACE PROCEDURE SCOTT.SP_TEST

(

P_PJT_CD IN VARCHAR2,

PC_CURSOR OUT SYS_REFCURSOR

) IS

V_RESULT_CD VARCHAR2(30 CHAR) := '';

V_RESULT_MSG VARCHAR2(500 CHAR) := '';

v_CNT NUMBER(22):=0;

v_id varchar(15 char) :='';

E_USER_ERROR_NO_DATA_FOUND EXCEPTION;

V_CODE VARCHAR(30 CHAR) := '';

V_MSG VARCHAR(30 CHAR) := '';

BEGIN

dbms_output.put_line('Start!!!!!-->');

begin

select row_id

into v_id

from TEST

where row_id ='9999';

-- exception

-- when no_data_found then

-- dbms_output.put_line('no data found');

-- V_CODE

-- RAISE E_USER_ERROR_NO_DATA_FOUND;

end;

OPEN PC_CURSOR

FOR

SELECT

'0000' AS RESULT_CD,

'111' AS RESULT_MSG

FROM DUAL;

dbms_output.put_line('end-->'||V_RESULT_MSG);

--EXCEPTION

-- WHEN OTHERS THEN

-- OPEN PC_CURSOR

-- FOR

-- SELECT

-- '333' AS RESULT_CD,

-- '444' AS RESULT_MSG

-- FROM DUAL;

END;

-------------------------------------------------------------

DB툴에서 아래 내용을 실행

------------------------------------------------

-- 프로시져 실행

-------------------------------------------------

DECLARE

i_counter integer;

v_cursor SYS_REFCURSOR;

v_RESULT_CD varchar2(100);

v_RESULT_MSG varchar2(500);

BEGIN

SCOTT.SP_TEST( P_PJT_CD => '',

PC_CURSOR => v_cursor);

i_counter := 0;

LOOP

FETCH v_cursor

INTO v_RESULT_CD,v_RESULT_MSG;

EXIT WHEN v_cursor%NOTFOUND;

i_counter := i_counter + 1;

DBMS_OUTPUT.PUT_LINE('결과:'||v_RESULT_CD|| v_RESULT_MSG);

END LOOP;

DBMS_OUTPUT.PUT_LINE('COUNT: ' || i_counter);

CLOSE v_cursor;

END;

저작자표시

'DBMS > 오라클 PLSQL' 카테고리의 다른 글

2. 기본 (0)	2015.02.13
1. PL/SQL 개요 (0)	2015.02.11
pl/sql, procedure, function (0)	2013.01.27

pl/sql, procedure, function

2013. 1. 27. 13:03

[ PL/SQL ]

- 구조적인 SQL언어와 달리 SQL을 절차적인 방식에 의해 사용하도록 설계한 언어

1) PL/SQL의 구조

DECLARE -- 선언(변수선언,커서,사용자정의 예외,..)
BEGIN -- 실행(SELECT,DML,..)
EXCEPTION -- 예외처리
END;
/

예1)

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2     I_MSG VARCHAR2(100); -- 변수선언
3 BEGIN
4     I_MSG:='HELLO'; -- 값대입
5     DBMS_OUTPUT.PUT_LINE('I_MSG:' || I_MSG); -- 화면출력
6 END;
7 /

[결과]
I_MSG:HELLO

SQL> DECLARE
2     VEMPNO NUMBER(4);
3     VENAME VARCHAR2(20);
4     VDEPTNO NUMBER(4);
5     VSAL NUMBER(4);
6     BONUS NUMBER(10);
7 BEGIN
8     SELECT EMPNO,ENAME,DEPTNO,SAL INTO VEMPNO,VENAME,VDEPTNO,VSAL
9     FROM EMP
10     WHERE EMPNO=7369;
11     IF(VDEPTNO=10) THEN
12             BONUS:=VSAL*0.1;
13     ELSIF(VDEPTNO=20) THEN
14             BONUS:=VSAL*0.2;
15     ELSIF(VDEPTNO=30) THEN
16             BONUS:=VSAL*0.3;
17     END IF;
18     DBMS_OUTPUT.PUT_LINE('사원번호 급여 부서번호 보너스');
19     DBMS_OUTPUT.PUT_LINE(VEMPNO ||' '||VSAL||' '||VDEPTNO||' '||BONUS);
20 END;
21 /

사원번호 급여 부서번호 보너스
7369 800 20 160

[ 저장 프로시져(Stored Procedure) ]

- 특정한 처리를 수행하는 PL/SQL 서브프로그램. 파라미터를 받아서 특정 작업을 수행한다.

- 형식)

CREATE OR REPLACE PROCEDURE 프로시져명(파라미터,..)
IS
변수선언;
..
BEGIN
실행문;
..
END;
/

예)-- 연습용 테이블

CREATE TABLE MEM
(
NUM NUMBER PRIMARY KEY,
NAME VARCHAR2(20),
PHONE VARCHAR2(15),
ADDR VARCHAR2(20)
);

예1) 프로시져만들기

CREATE OR REPLACE PROCEDURE ADDMEM
(
NUM NUMBER, -- 파라미터
NAME VARCHAR2,
PHONE VARCHAR2,
ADDR VARCHAR2
)
IS
BEGIN
INSERT INTO MEM
VALUES(NUM,NAME,PHONE,ADDR);
END;
/

-- 프로시져 오류보기

SQL>SHOW ERROR

-- 프로시져 수행

SQL> EXECUTE ADDMEM(1,'홍','010','대구');

예2)

CREATE OR REPLACE PROCEDURE ADDMEM
(
    NUM MEM.NUM%TYPE,
    NAME MEM.NAME%TYPE,
    PHONE MEM.PHONE%TYPE:='010',-- 파라미터값이없으면 디폴트값으로 채워짐
    ADDR MEM.ADDR%TYPE:='서울'
)
IS
BEGIN
INSERT INTO MEM
VALUES(NUM,NAME,PHONE,ADDR);
COMMIT;
END;
/

SQL> EXECUTE ADDMEM(2,'김아무');

-- 예제테이블

CREATE TABLE HAKSA
(
    HAKBUN NUMBER(10) PRIMARY KEY,
    KOR NUMBER(3),
    ENG NUMBER(3),
    TOT NUMBER(3),
    HAKJUM CHAR(2)
);

SQL> INSERT INTO HAKSA(HAKBUN,KOR,ENG) VALUES(1,100,90);

SQL> INSERT INTO HAKSA(HAKBUN,KOR,ENG) VALUES(2,80,70);

-- 학번을 파라미터로 받아서 총점과 학점을 계산해주는 저장 프로시져

CREATE OR REPLACE PROCEDURE HAKSA_PRO(NUM HAKSA.HAKBUN%TYPE)
IS
    HAK HAKSA.HAKJUM%TYPE;
    SUM1 HAKSA.TOT%TYPE;
    AVG1 NUMBER;
BEGIN
    SELECT (KOR+ENG) INTO SUM1
      FROM HAKSA
     WHERE HAKBUN=NUM;

AVG1:=SUM1/2; --평균구하기

    -- IF절 이용해서 학점구하기
    IF(AVG1>=90) THEN
        HAK:='A';
    ELSIF(AVG1>=80) THEN
        HAK:='B';
    ELSIF(AVG1>=70) THEN
        HAK:='C';
    ELSE
        HAK:='F';
    END IF;

    UPDATE HAKSA
       SET TOT=SUM1
         , HAKJUM=HAK -- 계산된 데이터로 수정하기
     WHERE HAKBUN=NUM;

    COMMIT;
END;
/

-- 프로시져 실행하기

SQL> EXECUTE HAKSA_PRO(1);

[ 사용자 정의 함수(Stored Function) ]

- 사용자가 만든 함수이며 어떤 연산을 수행한뒤 결과값을 반환한다.

형식)

CREATE OR REPLACE FUNCTION 함수명(파라미터,..)
RETURN 리턴형
IS
변수선언;
..
BEGIN
실행문장;
..
RETURN 값;
END;
/

예) 입력받은 값으로부터 10%의 세율을 구하는 함수

CREATE OR REPLACE FUNCTION TAX(P_VALUE NUMBER)
RETURN NUMBER
IS
VAL NUMBER;
BEGIN
VAL:=P_VALUE*0.1; -- 세율구하기
RETURN VAL; --세율반환하기
END;
/

SQL> SELECT TAX(3000) FROM DUAL;

TAX(3000)
----------
300

## IF문 형식

IF 조건절 THEN
실행문;
ELSE
실행문;
END IF;

Q1) 사원의 급여를 파라미터로 받아서 5000이상이며 15%에 해당하는 금액을 리턴하고 5000미만이면 10%를 리턴하는 함수 작성

CREATE FUNCTION MYTAX(P_VAL NUMBER) RETURN NUMBER
IS
    V_RESULT NUMBER;
BEGIN
    IF(P_VAL>=5000) THEN
        V_RESULT:=P_VAL*0.15;
    ELSE
       V_RESULT:=P_VAL*0.1;
    END IF;

RETURN V_RESULT;

END;
/

-- FUNCTION호출

SQL> SELECT MYTAX(6000) FROM DUAL;

MYTAX(6000)
-----------
900

Q2) 파라미터로 사원번호를 받아서 근무하는 부서명을 반환하는 함수작성

CREATE OR REPLACE FUNCTION EMPDNAME(NUM EMP.EMPNO%TYPE)
RETURN VARCHAR2 -- 반환타입
IS
    V_RESULT VARCHAR2(20); --부서명저장할 변수
BEGIN
    SELECT D.DNAME
      INTO V_RESULT
      FROM EMP E,
           DEPT D
     WHERE E.DEPTNO=D.DEPTNO
       AND E.EMPNO=NUM;

    RETURN V_RESULT;
END;
/

-- FUNCTION호출해보기

SQL> SELECT EMPDNAME(7369) FROM DUAL;

저작자표시

'DBMS > 오라클 PLSQL' 카테고리의 다른 글

2. 기본 (0)	2015.02.13
1. PL/SQL 개요 (0)	2015.02.11
프로시져 실행 샘플 - OUT 값이 여러행인 경우 (0)	2015.01.21

PREV 1 NEXT

New