'procedure'에 해당되는 글 5건
- 2015.05.26 [Oracle] Procedure 예외처리 (SQLCODE, SQLERM)
- 2012.11.27 [Oracle] PL/SQL PROCEDURE Scripts Sample 설명
- 2012.11.05 [Oracle] PL/SQL - 프로시저 (Procedure)
- 2012.10.31 [Oracle] PL/SQL - 개념과 예제
- 2011.11.14 [Oracle] PL/SQL PROCEDURE Sample
[Oracle] Procedure 예외처리 (SQLCODE, SQLERM)
SQLCODE, SQLERRM
- WHEN OTHERS문으로 트랩(Trap) 되는 오류들의 실제 오류 코드와 설명을 볼 때 사용한다
- SQLCODE : 실행된 프로그램이 성공적으로 종료하였을 때는 오류번호 0을 포함하며, 그렇지 못할 경우에는 해당 오류코드 번호를 포함한다.
- SQLERRM : SQLCODE에 포함된 오라클 오류 번호에 해당하는 메시지를 가진다
SQLCODE Value |
Description |
0 |
오류 없이 성공적으로 종료 |
1 |
사용자 정의 예외 번호 |
+100 |
DATA NOT FOUND 예외 번호 |
음수 |
위의 부분을 제외한 오라클 서버 에러 번호 |
================================================
* Oracle Community OracleClub.com
* http://www.oracleclub.com
* http://www.oramaster.net
* 운영자 : 김정식 (oramaster _at_ empal.com)
================================================
'Database / Sql' 카테고리의 다른 글
[Oracle] MERGE INTO (0) | 2015.01.12 |
---|---|
[Oracle] 16진수 <-> 10진수 변환 (0) | 2014.09.18 |
[Oracle] IO를 많이 발생시키는 쿼리 찾기 (0) | 2014.09.02 |
[Oracle] 각 테이블/인덱스 사이즈 조회 쿼리 (0) | 2014.03.06 |
[Oracle] 테이블 스페이스 용량/사용량 산출 (0) | 2014.03.04 |
[Oracle] PL/SQL PROCEDURE Scripts Sample 설명
CREATE OR REPLACE PROCEDURE SAMPLE_PROCE AS
-- SELETE 문의 결과를 TEMP_CUR 라는 CURSOR 에 넣는다.
CURSOR TEMP_CUR IS
SELECT BWCODE, BWNAME, BWADDS
FROM TEMP_TAB;
-- TEMP_CUR의 타입을 그대로 사용할 수 있는 NOW_CUR 변수선언
NOW_CUR TEMP_CUR%ROWTYPE;
BEGIN
-- 커서 사용을 위해서 연다.
OPEN TEMP_CUR;
-- 반복문 시작
LOOP
-- 커서의 ROW를 NOW_CUR로 보낸다.
FETCH TEMP_CUR INTO NOW_CUR;
-- DBMS_OUTPUT.put_line('LOOP 시작');
-- 커서가 끝나면 반복문을 종료한다.
EXIT WHEN TEMP_CUR%NOTFOUND;
--조건문 사용
IF (NOW_CUR.BWCODE <> 'A') THEN
ELSIF -- 또는 ELSE IF
ELSE
END IF;
INSERT INTO NEW_TAB ( BWCODE, BWNAME, BWADDS )
VALUES ( NOW_CUR.BWCODE, NOW_CUR.BWNAME, NOW_CUR.BWADDS );
COMMIT;
END LOOP;
-- 커서를 닫는다.
CLOSE TEMP_CUR;
EXCEPTION
WHEN OTHERS THEN
-- 에러 발생시 에러 메시지 출력
-- 토드의 DBMS_OUTPUT 에서 확인 할 수 있다.
DBMS_OUTPUT.put_line('ERROR');
ROLLBACK;
END;
실행은 ~~
EXEC SAMPLE_PROCE;
디버깅은 ~~
CREATE OR REPLACE PROCEDURE SAMPLE_PROCE 부분을 DECLARE 로 바꾸고
중간중간마다 DBMS_OUTPUT.put_line(...) 를 이용해서 처리한다.
* 참고로 SET SERVEROUTPUT ON; 작성 후 실행해야 DBMS_OUTPUT 나옴.
'Database / Sql' 카테고리의 다른 글
[Oracle] PL/SQL - SELECT INTO (0) | 2012.12.21 |
---|---|
[Oracle] 10g 공간 줄일수 있는 테이블 찾기와 Shrink 실행하기 (0) | 2012.12.03 |
[Oracle] Toad 실행계획 (0) | 2012.11.23 |
[MS-SQL] JOIN UPDATE / DELETE (0) | 2012.11.23 |
[MS-SQL] 날짜 차이 계산 (0) | 2012.11.08 |
[Oracle] PL/SQL - 프로시저 (Procedure)
프로시저 (procedure) 와 패키지 (package)
- 프로그램 유지 보수와 실행을 보다 쉽게 하기 위해서 프로그램 코드를 논리적으로 구성한 것.
- 1개 패키지 내에 여러개의 프로시저가 존재한다.
스토어드 프로시저 (stored procedure)
- 데이터베이스 내에 컴파일되어 저장된 프로시저.
- 데이터베이스에 저장된 프로시저는 오브젝트로 관리된다.
프로시저를 사용하는 이유
- 특정 문제나 작업을 해결하기 위해서 사용한다.
- 특정 기능을 담당하는 모듈(module) 단위로 되어 있다.
- 재사용이 가능하다.
- 스토어드 프로시저를 통해서만 데이터를 액세스할 수 있어 보안이 향상된다.
- 프로시저는 공유된 메모리 자원을 이용한다.
프로시저와 함수
- 둘은 출력의 형식이 다르다.
- 프로시저 : 많은 양의 정보를 처리한다.
- 함수 : 하나의 값을 Return 한다.
프로시저 생성
- 프로시저를 생성할 때 오라클은 컴파일하는 동안 접근하는 오브젝트(Table,View 등)가 존재하는지 검사하고, 접근 권한이 있는지를 검사한다.
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
CREATE OR REPLACE PROCEDURE 프로시저이름 (IN/OUT/INOUT)
AS
PL/SQL 본문코드
END 프로시저이름
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
- OR REPLACE : 프로시저가 이미 존재할 경우 덮어 쓴다. 항상 사용하는 것이 좋다.
- IN : 호출되는 프로시저에 값을 전달한다는 것을 지정
- OUT : 프로시저를 호출한 프로그램에게 값을 반환한다는 것을 지정
- INOUT : IN + OUT. 프로시저에 값을 전달하고 결과를 프로그램에게 반환한다는 것을 지정.
RETURN 문
- 프로시저가 끝나기 전에 마칠 경우에만 사용한다.
컴파일 / 재컴파일
- 스토어드 프로시저를 명시적으로 재컴파일 하려면 ALTER PROCEDURE 명령을 사용한다.
- ALTER PROCEDURE 는 패키지 내에 있는 프로시저가 아닌 독립된 프로시저에서만 사용 가능하다.
- 프로시저가 컴파일되면, 다음 실행시 컴파일 할 필요가 없어 부하를 줄일 수 있고, 컴파일 오류를 제거할 수 있다.
- 오라클은 부모 오브젝트가 명시적으로 다시 컴파일될 때 종속된 모든 오브젝트를 자동으로 재컴파일 한다. 따라서 부모 오브젝트를 컴파일 할 경우 관련된 모든 오브젝트가 모두 다시 컴파일이 일어나므로 시스템에 부하를 줄 수 있다.
프로시저 실행
- execute 명령으로 스토어드 프로시저를 실행할 수 있다.
[예제1] 사용자가 지정한 나이를 가진 사람의 나이를 100 으로 변경하는 스토어드 프로시저
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
CREATE OR REPLACE PROCEDURE change_ages (i_age IN integer)
AS
BEGIN
UPDATE member
SET AGE=100
WHERE age = i_age;
END change_ages;
/
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
- 프로시저 실행
SQL> execute change_ages(10);
- 결과 :
age = 100 으로 변경됨
파라미터
- 프로시저는 파라미터를 사용하여 정보를 전달한다.
- 프로시저를 호출할 때는 프로시저의 파라미터에 대해 값을 전달해야 한다.
- Actual Parameter : 프로시저에 전달된 파라미터 (실제 파라미터)
- Internal or Formal Parameter : 프로시저 내부에 선언된 파라미터 (내부 또는 형식 파라미터)
- Actual Parameter 의 데이터형과 Internal Parameter 의 데이터형이 같아야 한다.
스토어드 프로시저 확인
- 스토어드 프로시저에 관한 정보를 제공하는 데이터 딕셔너리 뷰
ALL_ERRORS : 현재 사용자가 액세스 가능한 모든 오브젝트에 대한 오류 목록
ALL_SOURCE : 현재 사용자가 액세스 가능한 모든 오브젝트의 텍스트 소스
USER_OBJECTS : 현재 사용자가 액세스 가능한 모든 오브젝토 목록
USER_ERRORS : 현재 사용자에게 속한 오브젝트의 오류 목록
USER_OBJECT_SIZE : 현재 사용자의 모든 PL/SQL 오브젝트
USER_SOURCE : 현재 사용자에게 속한 모든 오브젝트의 텍스트 소스
DBA_OBJECTS : 데이터베이스에 저장된 모든 오브젝트 목록
DBA_ERRORS : 데이터베이스에 저장된 모든 오브젝트에 대한 오류 목록
DBA_OBJECT_SIZE : 데이터베이스에 있는 모든 PL/SQL 오브젝트
DBA_SOURCE : 데이터베이스 전체에 대한 정보
- 어떤 오브젝트가 INVALID 상태인지 확인하고 다시 컴파일 할 필요가 있는지 확인할 수 있다.
- INVALID 인 함수나 프로시저는 잘못된 구문이 있을 수 있다.
SQL> select object_name, object_type, status from user_objects where status = 'INVALID';
프로시저 삭제
DROP PROCEDURE 프로시저이름;
프로시저 오버로딩
- 한 패키지에서 동일한 이름의 프로시저에 각각 다른 인수를 사용해서 호출할 수 있다.
- 서로 다른 데이터형을 갖고 있는 인수를 가지고 동일한 프로시저를 여러 번 실행해야할 경우 유용하다.
재귀 프로시저
- 자기 자신을 호출하는 프로시저
'Database / Sql' 카테고리의 다른 글
[Oracle] 테이블 권한주기 (0) | 2012.11.06 |
---|---|
[Oracle] PL/SQL - 패키지 (Package) (0) | 2012.11.05 |
[Oracle] PL/SQL - 블럭 구조 (0) | 2012.10.31 |
[Oracle] PL/SQL - 개념과 예제 (0) | 2012.10.31 |
[Oracle] JOIN DELETE (0) | 2012.07.21 |
[Oracle] PL/SQL - 개념과 예제
PL/SQL 이란?
- Procedural Language / Sturctured Query Language
- SQL에 프로그래밍 언어 설계 기능을 절차적으로 추가한 것
- 오라클사가 데이터베이스에서 SQL에 대해 절차적 로직을 실행하는 방법을 제공하기 위해 개발한 절차적 언어
- 데이터 캡슐화, 예외 처리, 정보 숨김, 객체 지향 등의 현대 S/W 공학 기능 제공
- SQL문을 블록 구조 및 프로시저 단위 코드에 포함시킬 수 있는 강력한 트랜잭션 처리 언어
PL/SQL 처리과정
- 오라클 선행 컴파일러에서 PL/SQL 블록(코드)를 제출하면 Oracle Server 내의 PL/SQL 엔진이 이를 처리한다.
- PL/SQL 엔진은 블록 내의 SQL문을 분리하여 하나씩 SQL문 실행자로 전송한다.
- PL/SQL 코드는 Oracle Server 에 저장할 수 있으며, 이름 앞에 Stored를 붙여 부른다. (Stored Procedure)
- Oracle Developer 와 같은 오라클 툴에는 Oracle Server의 엔진과는 별도로 자체 PL/SQL 엔진이 존재한다.
※ 패키지 확인
SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS
WHERE OWNER = 'SYS' AND OBJECT_TYPE = 'PACKAGE';
PL/SQL Block 구조
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * DECLARE
변수 선언문;
BEGIN
프로그램 코드;
EXCEPTION
예외 처리문;
END;
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
- 변수선언문 : 블럭에서 사용할 변수를 정의. 커서 정의와 중첩된 PL/SQL 프로시저 및 함수도 여기서 정의.
- 프로그램코드 : 블럭을 구성하는 PL/SQL 문.
- 예외처리문 : 런타임 오류나 예외 발생시 트리거되는 프로그램 코드.
- 각 블럭에서 문장의 끝을 알리기 위해 반드시 세미콜론(;)을 붙인다.
- BEGIN 내에 다른 블럭을 내포할 수 있다.
[예제1] 간단한 작성과 실행
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * DECLARE
x NUMBER;
BEGIN
x := 100;
END;
/
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
- 맨 끝의 '/' 슬래시는 SQL*Plus에게 PL/SQL 코드의 입력이 끝났음을 알리고 이를 오라클 데이터베이스로 보내는 역할.
[예제2] 결과 출력하기
- 출력은 DBMS_OUTPUT 패키지가 담당한다.
- 패키지 안에 dbms_output.put_line 프로시저 사용.
- 출력을 SQL*Plus 에서 보려면
SQL> SET SERVEROUTPUT ON 을 먼저 실행해야 한다.
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
DECLARE
x NUMBER;
BEGIN
x := 65400;
dbms_output.put_line('The variable x = ');
dbms_output.put_line(x);
END;
/
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
- 내용을 /export/home/oracle/plsql/test.sql 파일에 저장하고 불러들일 수도 있다. 기본확장자는 .sql 이다.
- 간단한 내용은 vi editor를 통해 수정하면 되겠지만, Buffer는 바로 직전의 SQL문만 저장하고 있기 때문에 긴 PL/SQL 프로시저는 반드시 텍스트파일로 만들어서 실행시키는 형식을 취하도록 한다.
$ vi /export/home/oracle/plsql/test.sql
내용기입
:wq
SQL> @/export/home/oracle/plsql/test
SQL> @파일명 -> 실행결과만 나온다.
SQL> RUN 파일명 -> 프로시저의 내용과 결과가 같이 나온다.
[예제3] 함수 작성하기
- PL/SQL을 사용하여 Stored 함수와 Stored 프로시저를 작성한다. 방금까지 작성했던 코드를 Stored 함수로 캡슐화 시키면 그것을 한번만 컴파일하고 데이터베이스에 저장해 놓았다가 나중에 다시 사용할 수 있다.
- DB내에 stored 함수를 만들어 놓으면 나중엔 그 함수만 불러다 쓰면 된다.
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * CREATE OR REPLACE FUNCTION TEST_FUNC
RETURN NUMBER AS
x NUMBER;
BEGIN
x := 100;
RETURN x;
END;
/
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
- OR REPLACE 는 동일한 함수가 존재하면 덮어쓰라는 의미이다.
- 1~2줄 CREATE 부터 AS 까지가 DECLARE 부분이다.
- 함수를 생성하는 것은 오라클이고 SQL*Plus는 결과만 보여줄 뿐이다.
[결과 확인]
- 함수를 작성하고 컴파일을 하였으니 이제 사용만 하면 된다. 함수이지만 ( )를 사용하지 않는 이유는 인수가 없기 때문.
SQL> SELECT TEST_FUNC FROM DUAL;
- 함수 생성시 STATUS가 INVALID 로 되어 있어 사용할 수 없기 때문에 코드에 잘못된 부분이 있는지 확인하고 수정 후 VALID 상태로 변경해야 한다. 오래 사용하지 않는 함수나 프로시저, 패키지도 INVALID 상태가 될 수 있다.
'Database / Sql' 카테고리의 다른 글
[Oracle] PL/SQL - 프로시저 (Procedure) (0) | 2012.11.05 |
---|---|
[Oracle] PL/SQL - 블럭 구조 (0) | 2012.10.31 |
[Oracle] JOIN DELETE (0) | 2012.07.21 |
[Oracle] Windows 2008 R2 64bit 에서 Oracle ODBC 설정 (0) | 2012.07.11 |
[Oracle] Windows 64bit 오라클 Client 설치 (0) | 2012.07.11 |
[Oracle] PL/SQL PROCEDURE Sample
CREATE OR REPLACE PROCEDURE NOSUN.CREATE_BAEHAD
(V_LSTSEQ VARCHAR2, V_BAENUM VARCHAR2) AS
C_JBDATE INVLOG.JBDATE%TYPE;
C_MAINNB INVLOG.MAINNB%TYPE;
S_CNT NUMBER;
S_DLVGUB CHAR(1);
CURSOR C_LIST IS
SELECT A1.MAINNB, A1.BSTSEQ, A1.DLVAMT
FROM INVLOG A1
WHERE A1.MAINNB = V_LSTSEQ;
BEGIN
S_CNT := 0;
UPDATE INVLOG
SET BAENUM = V_BAENUM
WHERE BSTSEQ = V_LSTSEQ;
OPEN C_LIST;
LOOP
<<start_point>>
FETCH C_LIST INTO C_MAINNB, C_BSTSEQ, C_DLVAMT;
EXIT WHEN C_LIST%NOTFOUND;
IF C_DLVAMT <> 0 THEN
S_DLVGUB := '1';
ELSE
S_DLVGUB := '2';
END IF;
BEGIN
SELECT APPCOD, RERAT1, RERAT2
INTO S_APPCOD, S_RERAT1, S_RERAT2
FROM RATTRF
WHERE APPCOD = (CASE WHEN C_BTOCHK = 'Y' THEN 'BTOC' ELSE 'ZZ00' END);
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_OUTPUT := '운송장번호 : '||C_MAINNB||'에 대한 운송요율이 등록되있지 않습니다.';
EXIT;
END;
GOTO START_POINT;
END LOOP;
CLOSE C_LIST;
UPDATE BEHEAD
SET JNGCHK = 'Y'
WHERE BAENUM = V_BAENUM;
END CREATE_BAEHAD;
/
'Database / Sql' 카테고리의 다른 글
[Oracle] ORA-01476 : 제수가 0 (0) | 2011.11.16 |
---|---|
[Oracle] RANK() 함수 Sample (0) | 2011.11.15 |
[Oracle] PL/SQL FUNCTION Sample (0) | 2011.11.14 |
[Oracle] SAVEPOINT & TRANSACTION (0) | 2011.09.02 |
[Oracle] FLASHBACK (0) | 2011.09.02 |