Notice
Recent Posts
Recent Comments
Link
일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | 5 | 6 | 7 |
8 | 9 | 10 | 11 | 12 | 13 | 14 |
15 | 16 | 17 | 18 | 19 | 20 | 21 |
22 | 23 | 24 | 25 | 26 | 27 | 28 |
29 | 30 |
Tags
- 세션쿠키
- jenkins git ignore file 추가
- docker error
- rsa 복호화
- 내도메인 한국
- springboot 배포
- jstl dependency
- jstl 종류
- 지속쿠키
- Docker
- aws lightsail
- aws
- AES
- 플러터
- 제약조건 제거
- 하이브리드암호화
- XSS 예방
- lightsail 도메인 연결
- RSA
- swap file
- 스왑 메모리
- aws lightsail 배포
- 하이브리드 암호화
- Stored Procedure log
- fk컬럼 삭제
- Flutter
- git
- rsa java
- spring
- springboot3 jstl
Archives
- Today
- Total
easycode
Stored Procedure (Oracle) 본문
Stored Procedure란?
- oracle의 스토어드 프로시저는 한 마디로 쿼리문의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로 사용합니다.
- 반복적인 작업을 할 경우 PL/SQL* 을 저장해 놓고 필요한 경우 호출하여 사용할 수 있으면 할 때가 있습니다. 이때 스토어드 프로시저(=저장 프로시저)를 사용합니다. stored procudure를 사용하면 복잡한 DML 문들을 필요할 때마다 다시 입력할 필요 없이 저장해 둔 프로시저를 호출해서 실행 결과를 얻을 수 있습니다.
=> 즉, 프로시저(함수) 라는 이름에 걸맞게 일련의 쿼리문을 하나의 함수처럼 만드는 것입니다. 어떤 동작을 해야할 때 일일히 DML문을 다시 입력하는 게 아닌, 해당 프로시저를 호출하면 됩니다!
* PL/SQL이란?
더보기
PL/SQL
- PL/SQL은 상용관계형데이터베이스(RDBMS) 오라클 DBMS에서 SQL 언어를 확장하기 위해 사용하는 컴퓨터 프로그래밍 언어이다.
프로시저 구조
CREATE [OR REPLACE] PROCEDURE 프로시저명 (
매개변수1 [mode] data_type,
매개변수2 [mode] data_type ...
)
IS
--- 변수 선언부 ---
local_variable declaration
BEGIN
--- 실행부 --- // 수행할 sql 쿼리를 아래에 넣는다.
statement1;
statement2;
...
EXCEPTION
--- 예외처리부 ---
END [프로시저명]; -- // 프로시저명은 넣어도 안넣어도 된다
- create로 프로시저를 정합니다. 프로시저 실행 시 받을 변수는 여기서 선언합니다.
- 프로시저 안에서 선언할 변수는 IS 뒤에 작성합니다.
- 실행할 DML문은 BEGIN 뒤에 작성합니다.
- END로 끝냅니다.
생성되어 있는 프로시저 조회
--- 저장 프로시저 조회 ---
SELECT *
FROM user_source;
프로시저에서 IF문 사용
IF 조건1 THEN 처리
IF 조건2 THEN 처리
ELSE
END IF;
프로시저에서 로그 찍는 법
DBMS_OUTPUT.PUT_LINE('내용');
프로시저 생성 예시
1번 예시
--- 생성문 시작 ---
CREATE OR REPLACE PROCEDURE TEST_PROC
(
--- 프로시저 실행 시 받을 매개 변수를 CREATE 안에 작성 ---
param1 IN NUMBER,
param2 IN VARCHAR2
)
--- 프로시저에서 선언할 변수는 IS 뒤에 작성 ---
IS
param3 IN VARCHAR(20) = "Study Hard";
--- 실행 내용은 BEGIN 뒤에 작성 ---
BEGIN
--- 예시로 insert문을 넣음 ---
INSERT INTO TEST_TABLE
(NO, NAME, SITE)
VALUE
(param1, param2, param3);
END TEST_PROC;
2번 예시 : 사원번호를 받아서 이름, 급여, 직업을 OUT 파라미터에 넘겨주기
- 프로시저 생성
CREATE OR REPLACE PROCEDURE sel_empno (
p_empno IN NUMBER, -- IN 매개변수, 사원번호
p_ename OUT VARCHAR2, --OUT 매개변수, 이름
p_sal OUT NUMBER, --OUT 매개변수, 급여
p_job OUT VARCHAR2 --OUT 매개변수, 직업
)
IS
BEGIN
SELECT
first_name|| ' ' ||last_name, salary, job_id
INTO
p_ename, p_sal, p_job
FROM
emp
WHERE
employee_id = p_empno;
END;
-프로시저 실행
DECLARE
var_ename VARCHAR2(15);
var_sal NUMBER;
var_job VARCHAR2(9);
BEGIN
--- 익명 블록에서 프로시저 실행, EXEC/EXECUTE를 붙이지 않는다
sel_empno(101, var_ename, var_sal, var_job);
--- 프로시저 로그 찍기 ---
dbms_output.put_line('조회결과: '||var_ename||'/'||var_sal||'/'||var_job);
END;
프로시저 실행 (=호출)
1. IN 변수만 있는 프로시저 (파라미터만 있고 return 값이 없는 프로시저)
EXEC TEST_PROC();
EXEC TEST_PROC(200,'HELLO');
--- EXECUTE TEST_PROC(200, 'HI'); 이렇게도 쓴다 ---
2. OUT 변수 있는 프로시저 (파리미터와 return 값 모두 있는 프로시저)
DECLARE
param1 VARCHAR2(200);
param2 VARCHAR(200);
BEGIN
TEST_PROC(200, param1, param2);
END;
번외 : OUT 파라미터가 있는 프로시저를 SQL 환경에서 사용하기
OUT 파라미터가 있는 프로시저를 PL/SQL 환경이 아닌 SQL환경에서도 변수를 선언하여 사용할 수 있습니다.
프로시저의 OUT 파라미터 값을 SQL 환경에서 받기 위해서는 바인드 변수*가 필요합니다.
*바인드변수란? 호스트 환경에서 생성되어 데이터를 저장하므로 호스트 변수라고도 합니다. VARIABLE 키워드를 사용하여 생성할 수 있습니다.
--- 바인드 변수 생성 ---
VARIABLE v_ename VARCHAR2(15);
VARIABLE v_sal NUMBER;
VARIABLE v_job VARCHAR2(9);
- 프로시저 실행
--- 프로시저 호출 ---
EXEC sel_empno(101, :v_ename, :v_sal, :v_job);
참고사이트
1. https://kookyungmin.github.io/db/2018/07/31/oracle_35/
2. https://studyingazae.tistory.com/59
'DB&SQL' 카테고리의 다른 글
[DB] 생성된 테이블에서 제약조건 제거 및 FK 컬럼 삭제 (0) | 2024.02.18 |
---|