ORA와 SQL 차이

기본 문법

CREATE OR REPLACE PROCEDURE 프로시저명
(
  변수명         IN   VARCHAR2,
  RC1    OUT SYS_REFCURSOR 
)
IS
 
BEGIN

 /*DO SOMETHING*/ 

OPEN RC1 FOR
 SELECT * fROM 테이블


EXCEPTION WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR (-20001, '프로시저명' || SQLERRM);
 
END 프로시저명;
/

요약설명

REF CURSOR

 - 오라클 9i 하위 버전에 지원하던 커서.

   해당 커서를 사용하기 위해서는 패키지를 선언하고, 패지키에서 선언된,

   REF CURSOR 타입의 인자를 출력 파라메터로 받는 프로시저를 작성해야 함.

   프로시저 내부에서 패키지 작성없이 직접 해당 커서 타입을 이용하는 것은 불가.

 

SYS_REFCURSOR

 - 오라클 9i 버전부터 지원하는 커서.

   이전버전에 지원하던 REF CURSOR의 불편함을 개선한 커서이다.

   하위 버전에서 커서를 반환하기 위해 패키지를 생성해야 했으나, 

   해당 커서의 추가로 인해 패키지 작성없이 프로시저에서 바로 커서를 반환할 수 있게 되었다. 

- 커서를 반환함으로써 SELECT 프로시저를 사용할때 결과테이블을 반환 받을 수 있게 된다.

예외처리

EXCEPTION

WHEN 예외 THEN  ###

WHEN OTHERS THEN @@@

RAISE_APPLICATION_ERROR(사용자 정의 예외)

오류코드 -20000 ~ -20999범위내에서 사용자가 정의한 오류를 실행할수 있음

SQLCODE, SQLERRM

SQLCODE는 실행부에서 발생한 예외에 해당하는 코드를 반환한다. 예를 들어, 0으로 나누면 이에 대한 예외 코드인 ‘-1476’을 반환한다. 만약 에러 없이 성공하면 ‘0’을 반환한다.

SQLERRM은 발생한 예외에 대한 오류 메시지를 반환한다. 이 함수는 매개변수로 예외코드 값을 받는데, 매개변수를 넘기지 않으면 디폴트로 SQLCODE가 반환한 예외코드 값과 연관된 예외 메시지를 반환한다. 

SQLERRM(SQLCODE)로 사용하기도한다.

차이점

MS-SQL	Oracle
CREATE PROCEDURE	CREATE PROCEDURE
ALTER PROCEDURE	CREATE OR REPLACE PROCEDURE
변수 NVARCHAR(20)	변수 IN VARCHAR2
SQL 마지막에 ; 없어도됨	SQL 마지막에  ; 부붙이기
'문자열' + '합치기'	'문자열' || '합치기'
END	END; (프로시저의 마지막은 반드시 ;(세미콜론)
DECLERE V_VAR NVARCHAR(20)	V_VAR VARCHAR2(20) 프로시저 안에서는 명시하지않아도 되지만, 프로시저 밖에서는 익명블록으로 만들어야한다. (DECLARE 변수  BEGIN   변수 := @@@;  END)
CONVERT(SYSDATE(), 112);	TO_DATE(SYSDATE, 'YYYY.MM.DD') TO_CHAR(SYSDATE, 'YYYY.MM.DD') MS-SQL의 CAST, CONVERT가 Oracle에서 TO_DATE, TO_CHAR이라고 생각하면된다.
ISNULL('', 0)	NVL('', 0)
EXEC UP_HR_HUANEXPEN_ACCEPT  파라미터1, 파라미터2, 파라미터3	UP_HR_HUANEXPEN_ACCEPT(파라미터1, 파라미터2, 파라미터3) 프로시저 내에서 다른 프로시저 실행시, EXEC를 명시하지 않아도 된다.
SELECT @COMPANY = COMPANY   FROM TBL	SELECT COMPANY    INTO V_COMPANY   FROM TBL 변수에 값을 대입할때 위처럼 SELECT문 뒤에 INTO를 사용한다.
@P_CD_COMPANY = '1000'	V_CD_COMPANY := '1000'  (Oracle에서 변수에 값을 대입하려면 := 를 사용한다.)
'' 과 NULL	둘다 NULL로 인식
임시테이블 #temp	오라클에서는 임시테이릅을 만들기 상당히 까다롭다 그래서 일반 테이블을 만들어놓고 임시테이블 처럼 사용하기도 한다.
PRINT	DBMS_OUTPUT.PUT_LINE()