쁘도리의 블로그

저장 프로시저 (Stored Procedure, SP)

"저장 프로시저 또는 스토어드 프로시저(stored procedure)는
일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다."

_위키백과

"데이터베이스에 대한 일련의 작업을 정리한 절차를
관계형 데이터베이스 관리 시스템에 저장한(지속성) 것으로,
영구저장모듈(Persistent Storage Module)이라고도 불린다."

장점 -

네트워크 부하 감소

처리 시간 감소

데이터 무결성 유지

편리한 유지보수 및 개발 업무와의 구분

절차적 기능 구현 가능

단점 -

불편한 유지보수 ( 개발적 역량으로 인해 )

DB 확장 어려움

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

CREATE OR REPLACE PROCEDURE 프로시저이름 

IS

[

변수이름 데이터타입;  -- 프로시저내에서 사용할 변수선언

변수이름 데이터타입;

변수이름 데이터타입;

..

]

BEGIN

 기능 구현;

END;

2. (인수있는) 프로시저 생성방법

CREATE OR REPLACE PROCEDURE 프로시저이름(

변수이름 IN 데이터타입, 변수이름 IN 데이터타입, .... --IN 생략가능

)

IS

[

변수이름 데이터타입;  -- 프로시저내에서 사용할 변수선언

..

]

BEGIN

기능 구현;

END;

3. 프로시져 호출방법

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

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

4. 프로시저 output 매개변수 사용하기

 - 웹개발 하면서 프로시저 결과 핸들링을 위해 많이 사용 했다.

 - 프로시저를 실행하여 특정결과값을 out변수에 저장하여 보낸다.(프로시저에서 실행환경으로 값을 반환)

 - out있는 프로시저 작성방법

CREATE OR REPLACE PROCEDURE 프로시저이름 (

변수이름 IN 데이터타입, 변수이름 IN 데이터타입, .... --in 생략가능

변수이름 OUT 데이터타입, 변수이름 OUT 데이터타입 ... --프로시저를 호출하는곳으로 값을 보낸다.

)

IS

[

변수이름 데이터타입;  -- 프로시저내에서 사용할 변수선언

..

]

BEGIN

기능 구현;

END;

-----

프로시저 문법

DECLARE

> 변수는 선언 시에 기본값을 지정할 수 있습니다. 만약 지정하지 않는다면 그 변수의 값은 NULL 값을 갖게 됩니다.

v_ :  내부 변수로서 프로시저 내에서 사용할 변수에 대해 주로 v라는 표현을 붙여 사용한다.

p_ :  위에서 cmd 창에서 입력을 받던 대체 변수들에 대해  p라는 표현을 붙였던 것을 기억할 것이다.

상수(constant)

> 상수는 변수와 다르게 한번 값이 정해지면 변경할 수 없다는 차이가 있습니다. 상수를 선언하는 방식은 변수의 선언처럼 DECLARE 구문 뒤에서 이루어지며, 상수 이름과 타입 사이에 CONSTANT라고 지정하면 됩니다.

CALL

> 저장 프로시저 호출 

Call 문의 저장 프로시저에서 반환되는 유일한 지원되는 형식은 행 집합입니다. 행 집합 직렬화는 XML for Analysis에 의해 정의됩니다. Call 문의 저장 프로시저가 다른 형식을 반환하는 경우 무시되고 호출 애플리케이션에 XML로 반환되지 않습니다 - https://docs.microsoft.com/

EXCEPTION 

> 예외처리, 프로시저가 예외처리가 없는 경우 프로시저에서 강제적으로 종료되고 오류 메시지가 출력된다고 한다.

GET DIAGNOSTICS

> GET DIAGNOSTICS 명령문은 실행된 이전 SQL문에 대한 정보를 얻습니다.

CURRENT 또는 STACKED액세스할 진단 영역을 지정합니다.

• CURRENT 첫 번째 진단 영역에 액세스하도록 지정합니다. 실행되었고 GET DIAGNOSTICS 명령문이 아닌 이전 SQL문에 해당합니다. 이 값이 기본값입니다.
• STACKED 두 번째 진단 영역에 액세스하도록 지정합니다. 두 번째 진단 영역은 핸들러 내에서만 사용 가능합니다. 핸들러가 입력되기 전에 실행되었고 GET DIAGNOSTICS 명령문이 아닌 이전 SQL문에 해당합니다. GET DIAGNOSTICS 명령문이 핸들러 내 첫 번째 명령문인 경우, 첫 번째 진단 영역과 두 번째 진단 영역은 동일한 진단 정보를 포함합니다.

RAISE NOTICE

> 해당 변수들을 원하는 문자열로 포맷으로 구성한 것을 PL/pgSQL의 실행환경에서 표시하는 코드입니다.

-----

프로시저 삭제

DROP PROCEDURE [프로시저 명] ;

출처:

http://www.gisdeveloper.co.kr/?p=4573 

https://www.ibm.com/docs/ko/i/7.3?topic=statements-get-diagnostics 

https://goddaehee.tistory.com/163 

https://siahn95.tistory.com/entry/DBMSSQL-%EC%A0%80%EC%9E%A5-%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80Stored-Procedure%EB%9E%80

ALTER TABLE 테이블명 OWNER TO 소유자명;

ALTER TABLE 테이블명 SET SCHEMA 새로운_스키마명;

특정 유저 소유자의 전체 오브젝트들을 다른 유저 소속으로 변경할경우

REASSIGN OWNED BY 현재 소유자명 TO 새로운 소유자명;

Description: change the ownership of database objects owned by a database role
Syntax:
REASSIGN OWNED BY { old_role | CURRENT_USER | SESSION_USER } [, ...]
TO { new_role | CURRENT_USER | SESSION_USER }

출처: https://orcl.tistory.com/entry/postgresql-테이블의-스키마와-owner-변경하기 

DB Index 가비지 파일 정리 명령어

vacuum verbose analyze;

베큠 관련 정리글을 남겨둔다.

https://blog.gaerae.com/2015/09/postgresql-vacuum-fsm.html

번외 :

현재 트랜잭션 실형중인 쿼리 조회 

select * from pg_stat_activity order by query_start asc; 

실행중인 쿼리 취소하기 

select pg_cancel_backend(pid int);

> pid int 값은 pg_stat_activity 쿼리조회 결과에서 실행중인 쿼리를 취소할 pid 컬럼의 int값을 pg_cancel_backend 함수 실행하면

쿼리가 취소된다. 

https://m.blog.naver.com/anytimedebug/221222479261

SELECT 컬럼1||컬럼2||컬럼3 as t1, count(*) as num 
FROM 테이블  
GROUP BY t1 

구글링을하였지만, 보다 자세하게 포스팅은 안되있어 추가 포스팅

postgresql 프로그램이 설치되어있는 '경로'에서 CMD(명령어창)을 실행 한다음 아래와 같이 실행하면

입력한 DB의 테이블에 실행하고싶은 sql파일이 실행이된다.

  
ex) psql --host=호스트명 --port=포트 --username=유저명 --dbname=디비명 --password -f 실행시킬sql파일.sql

그리고 실행시킬 sql파일은 postgresql 프로그램이 설치되어있는 경로에 복사하여 같은경로에 위치해야된다.

CREATE TABLE _새로만들테이블명_as (select * from _복사할테이블명_);

최고

ALTER TABLE 테이블명 ALTER 컬럼명 SET DEFAULT 값

ex) 
ALTER TABLE test_table ALTER test_column SET DEFAULT 'Y';

// Set NULL

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT NULL;

날짜타입비교 (postgreSQL과 ORACLE 비교)

ORACLE 

-  (to_char(now(), 'YYYY') - 1)

postgreSQL 

-  to_char(current_timestamp + '-1 years', 'YYYY')

날짜타입 쿼리 사용시 도움이 되었길 바랍니다.