차근차근/Oracle

Oracle procedure

예쁜꽃이피었으면 2021. 12. 9. 17:52
https://m.blog.naver.com/chsmanager/221597950000
https://wickies.tistory.com/118

우선.. Stored procedure, procedure가 다른 것인지가 궁금했는데.. 아직 잘 모르겠다.

1)
MSSQL Stored procedure(SP) / Oracle procedure
  =>이렇게 나눠서 부르는 것도 같고..

2) 
- Procedure
  : 소프트웨어에서 특정 동작을 수행하는 일정 코드 부분을 의미하며 함수, 메서드, 루틴, 서브루틴과 같은 바를 의미한다.
- Stored Procedure
  : DB 내에서 재사용할 수 있는 SQL 코드를 뜻한다.
- 대부분의 RDB에서 Stored procedure를 지원한다.
- SP구문 내에서 세미콜론을 사용하기 때문에 DELIMITER를 반드시 사용해야 한다.
  => 이렇게 비슷한 의미로 사용되는 것도 같다..

우선 여기서는 오라클 프로시저에 대해 적어보려고 한다.

저장 프로시져(Stored Procedure)

- Transact-SQL문장의 집합

더보기

Transact-SQL

 

변수

트랜잭트 SQL의 흐름 제어 키워드로 BEGIN / END, BREAK, CONTINUE, GOTO, IF / ELSE, RETURN, WAITFOR, WHILE이 있다.

 

IF와 ELSE

IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1
   PRINT 'It is the weekend.'
ELSE
   PRINT 'It is a weekday.'

BEGIN과 END

IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1
BEGIN
   PRINT 'It is the weekend.'
   PRINT 'Get some rest on the weekend!'
END
ELSE
BEGIN
   PRINT 'It is a weekday.'
   PRINT 'Get to work on a weekday!'
END

WHILE

DECLARE @i INT
SET @i = 0

WHILE @i < 5
BEGIN
   PRINT 'Hello world.'
   SET @i = @i + 1
END

DELETE, UPDATE 문 변경

Idle 플래그에 속하는 모든 users 삭제의 예는 다음과 같다.

DELETE users
  FROM users AS u
  INNER JOIN user_flags AS f
    ON u.id = f.id
WHERE f.name = 'idle'

BULK INSERT

BULK INSERT는 대량의 데이터 적재 처리, 여러 줄을 테이블로 삽입, 외부 시퀀스 파일로부터 데이터 읽기를 구현하는 트랜잭트 SQL 문이다.

TRY CATCH

SQL 서버 2005를 기점으로 마이크로소프트는 TRY CATCH 추가 로직을 도입하여 예외 형 동작을 지원한다. 이러한 동작은 개발자들이 자신들의 코드를 단순화할 수 있게 하고 각 SQL 실행문 이후에 @@ERROR 검사를 남길 수 있게 한다.

-- begin transaction
BEGIN TRAN

BEGIN TRY
   -- execute each statement
   INSERT INTO MYTABLE(NAME) VALUES ('ABC')
   INSERT INTO MYTABLE(NAME) VALUES ('123')

   -- commit the transaction
   COMMIT TRAN
END TRY
BEGIN CATCH
   -- rollback the transaction because of error
   ROLLBACK TRAN
END CATCH

 

프로시져 용도

- 어떤 동작을 절차적 일괄처리 작업하는데 사용

- SQL Server에서 사용하는 프로그밍 기능

 

프로시져 특징

개발자가 자주 실행해야하는 특정 작업을

필요할 때 호출하기 위해 절차적인 언어를 이용해 작성한

이름이 있는 프로그램 모듈(Block)을 의미한다.

- SQL Server의 성능 향상(첫 실행 시 컴파일, 재컴파일 안함)

오라클 SQL문 수행시 행동
1) 같은 실행 계획이 공유풀에 있는지 확인
2) 이후 SQL문 문법 검사나 데이터 사전(Data Dictionary)을 검사하여 해당 사용자 소유의 테이블인지 여부를 확인한다.
3) 실행권한이 있는지 확인한다.
4) 이상이 없으면 실행계획을 작성, 적용한다.
더보기

공유풀 (https://goddaehee.tistory.com/64)

공유 풀 영역(SHARED POOL AREA)

- 공유 풀은 사용자가 작성한 SQL문이 저장되어 관리되는 곳이다.

 

공유풀의 역할 

★ 표준 SQL 문법에 합당한 문장인가?
Ex) select * fro emp;
from이 아닌 fro 을 썼기 때문에 표준 SQL 문법에 맞지 않고 에러를 발생하게 된다.

★ 데이터 사전(Data Dictionary)의 정보를 검색하여 데이터베이스에 생성되어 있는 테이블인가?
Ex) select * from emp;
접속한 해당 사용자가 질의할 수 있는 emp 테이블이 존재하지 않는다면 오류를 발생한다.

★ 해당 테이블에 현재 접속한 사용자는 접근 권한이 있나?
Ex) select * from scott.emp;
권한이 불충분하면 오류를 발생한다

 

짧은 시간에 위와 같은 사항들을 확인한다.

그리고 동일한 SQL문이 다시 실행될 때 확인된 SQL문을 재사용하기 위해 SQL문을 Libarary Cache내에 저장한다.

동일한 SQL문을 사용한다면 위와 같은 작업을 확인하는 시간과 자원을 아낄 수 있기 때문이다.

(참고] select * from emp; 와 SELECT * from EMP; 는 우리가 보기에는 같은 문장이지만 Oracle은 서로 다른 문장으로 생각한다. => 대소문자 구분)

 

SQL문에 대한 검사를 마친 후 실행 시 가장 빠른 검색 경로를 통해 데이터를 얻기 위해 실행 계획을 세우게 된다. 이 실행 계획에 따라 사용자가 요구한 SQL문을 처리하며 질의문인 경우 결과를 반환한다.

- 모듈식 프로그래밍 가능

- 보안강화(저장 프로시져 마다 권한 할당 가능)

- 네트워크 전송량 감소(긴 쿼리문장의 단순화)

- 매개변수를 받을 수 있는 PL/SQL BLOCK이다. (https://hoon93.tistory.com/38)

- 프로시저 내의 변수는 Scalar(스칼라)변수인데, 임시 데이터 1개만 저장 / 모든 형태의 데이터 유형 지정 가능

- PL/SQL의 대입연산자는 ' := '이다(T-SQL은 ' = ')

- PL/SQL에서 사용하는 프로시 내의 SELECT문장은 반드시 결과 값이 있어야 하며, 그 결과는 반드시 1개여야 한다.

  조회결과가 없거나 2개이상인 경우에는 에러가 발생한다.(T-SQL은 결과값이 없어도 상관없음)

  그러나 특정한 로직을 처리하면서 결과는 있어야 하지만 그 결과값을 함수(사용자 정의함수)처럼 반환(return)하지는 않는다.

 

프로시져의 장점

- 빠르다

- 파라미터를 사용할 수 있다

- 여러 어플리케이션과 공유할 수 있다.

 

프로시져 기본 문법

1. 인수없는 프로시저 생성방법

CREATE [OR REPLACE] Procedure "Procedure_name"(argument1[MODE] data_type1,argument2[MODE] data_type2...)

IS[AS]
...
BEGIN
...
EXCEPTION
...
END;
/
더보기

프로시저 예제

CREATE OR REPLACE Procedure p_DEPT_insert(
							v_DEPTNO in number
                            ,v_dname int varchar2
                            ,v_loc in varchar2
                            ,v_result out varchar2)

IS cnt number := 0;

BEGIN
	SELECT COUNT(*) INTO CNT 
    FROM DEPT 
    WHERE DEPTNO = v_DEPTNO and ROWNUM = 1;
    
    if 
    	cnt > 0 then v_result := '이미 등록된 부서다';
    else
    	INSERT INTO DEPT(DEPTNO, DNAME, LOC)
        VALUES (v_DEPTNO, v_dname, v_loc);
    
    	commit;
		v_result := '입력완료';
    end if;
EXCEPTION
WHEN OTHERS
THEN ROOLBACK; v_result := 'ERROR발생';

END;
/

1) CREATE [OR REPLACE] 구문을 이용하여 생성한다.

2) OR REPLACE : 같은 프로시저가 있을 때, 기존의 프로시저를 무시하고 새로운 내용으로 덮어쓰겠다는 의미

3) MODE : mode는 매개변수의 역할을 결정하는 자리이다. mode자리에 들어갈 수 있는 변수는 3가지이다.

   - IN : 운영체제에서 프로시져로 전달될 변수의 모드

   - OUT : 프로시저에서 처리된 결과라 운영체제로 전달

   - INOUT : IN과 OUT 두 가지 기능 모두 수행

   ※ IN OUT, OUT 모드로 선언된 파라미터에는 DEFAULT를 적용할 수 없다.

더보기

※인수의 타입 선언부분정리 

1. IN => 내부 프로그램에 제공

변수이름 IN VARCHAR2;  --인수선언할때 byte수 지정안함 (인수는 크기를 주지 않습니다.)

변수이름 IN 테이블이름.컬럼명%TYPE;

변수이름 IN 테이블이름.컴럼명%TYPE := 값;

변수이름 IN 테이블이름.컬럼명%TYPE DEFAULT 값;

 

2. OUT => 호출자에게 제공

(프로시저 실행 시점에 OUT 매개변수를 변수 형태로 전달하고, 프로시저 실행부에서 이 매개변수에 특정 값을 할당, )

변수이름 IN VARCHAR2;

변수이름 IN 테이블이름.컬럼명%TYPE;

 

3. IN OUT => 입력과 동시에 출력용으로 사용할 수 있다.

변수이름 IN VARCHAR2;

변수이름 IN 테이블이름.컬럼명%TYPE;

※ IN OUT, OUT 모드로 선언된 파라미터에는 DEFAULT를 적용할 수 없다.

출처: https://goddaehee.tistory.com/163 [갓대희의 작은공간]

 

4) IS : PL/SQL의 Block을 시작한다는 의미이며, 프로시저 내(정확하게는 Begin문 뒤에 나올 SQL문)에서 사용할 변수를 선언하는 곳이다. LOCAL변수는 IS와 Begin사이에 선언해서 사용한다.

5) EXCEPTION : Begin~end사이에 실행되는 SQL문 실행 도중 발생한 에러를 처리하는 예외 처리부

6) END; : 실행문의 종료를 의미한다.

7) / : end; 뒤에 위치하는 슬러시(/)는 데이터베이스에게 프로시저를 컴파일하라는 명령

 

프로시저 호출방법

- EXEC 프로시져이름; --인수없는 경우의 호출

- EXEC 프로시저이름(값,값..); --인수있는 경우의 호출

 

저장된 프로시저 찾기

- 작성된 프로시저를 찾기 위해 데이터 사전을 이용할 수 있다.

 ※ 주의 : 데이터 사전은 대문자로 값을 저장하기 때문에 대문자로 검색해야한다.

1. SELECT * FROM user_obejcts WHERE object_type = 'PROCEDURE';

2. SELECT * FROM user_source WHERE name = '프로시저명';

 

 

열심히 프로시저에 대해 썼는데..
코드 보자마자 멘붕이 왔다. 프로시저는 return값이 없다고 한 것 같은데.. 왜
내가 받은 코드에는 리턴값이 있지..? '-'..

https://mjn5027.tistory.com/47 여기에 보면
함수나 프로시저나 구조적으로는 동일하며 프로시저도 리턴값을 가질 수 있다고 나와있다..

프로시저(Procedure)와 함수(Function)의 차이
프로시저(Procedure) 함수(Function)
특정 작업을 수행 특정 계단을 수행
리턴값을 가질 수도 안 가질 수도 있음 리턴값을 반드시 가져야 함
리턴값을 여러개 가질 수 있음 리턴값을 오직 하나만 가질 수 있음
서버(DB)단에서 기술 화면(Client)단에서 기술
수식내에서 사용불가 수식내에서만 사용 가능
단독으로 문장 구성 가능 단독으로 문장 구성 불가

https://thebook.io/006696/part02/ch09/03/05/  여기에서는
함수에서 사용한 RETURN문을 프로시저에서도 사용할 수 있는데 그 쓰임새와 처리 내용은 다르다.
함수에서는 일정한 연산을 수행하고 결과값을 반환하는 역할을 했지만,
프로시저에서는 RETURN문을 만나면 이후 로직을 처리하지 않고 수행을 종료. 즉, 프로시저를 빠져나가 버린다.
반복문에서 일정 조건에 따라 루프를 빠져나가기 위해 EXIT를 사용하는 것과 유사하다.


https://sup-report.tistory.com/102
여기까지 보고 나니까.. 내가 받은 코드는 이름만 프로시저이고 .. 실제는 함수인게 아닌가 싶다.. 
잘 모르겠다.. '-' 망했다.
흠..이 글을 쓰기 전에 나는 프로시저는 데이터베이스에 만들어서 사용하는 사용자지정 함수라고 생각했는데..
그럼 그냥 똑같은거 아닌가.. 어디서 실행되는냐의 차이는 있겠지만...........................................................
일단 여기까지.. 

 

 

 

 

[참조]

https://itability.tistory.com/51

https://goddaehee.tistory.com/163

https://ko.wikipedia.org/wiki/%ED%8A%B8%EB%9E%9C%EC%9E%AD%ED%8A%B8_SQL

https://goddaehee.tistory.com/64

https://hoon93.tistory.com/39

https://hoon93.tistory.com/38

https://m.blog.naver.com/chsmanager/221597950000

https://wickies.tistory.com/118

 

 

반응형
저작자표시

'차근차근 > Oracle' 카테고리의 다른 글

DBMS_OUTPUT.PUT_LINE  (0) 2021.12.13
오라클 DUAL테이블 (가상테이블)  (0) 2021.12.10
오라클 반복문(3) - LOOP , WHILE , FOR,CONTINUE  (0) 2021.12.10
ora 12518 tns 리스너는 클라이언트 연결을 처리할 수 없습니다  (0) 2017.04.25
설치  (0) 2015.11.19

'차근차근/Oracle'의 다른글

  • 현재글Oracle procedure

관련글

댓글

티스토리툴바