1. 생성(CREATE)

SQL Server

CREATE PROCEDURE 프로시저명
    @파라미터1 데이터타입 [= 기본값],
    @파라미터2 데이터타입 OUTPUT
AS
BEGIN
    -- SQL 문장들
END;

MySQL

DELIMITER //
CREATE PROCEDURE 프로시저명(
    IN 파라미터1 데이터타입,
    OUT 파라미터2 데이터타입
)
BEGIN
    -- SQL 문장들;
END //
DELIMITER ;

Oracle

CREATE OR REPLACE PROCEDURE 프로시저명 (
    파라미터1 IN 데이터타입,
    파라미터2 OUT 데이터타입
)
IS
BEGIN
    -- SQL 문장들;
END;

• IN : 입력 파라미터
• OUT : 출력 파라미터
• IN OUT : 입력과 출력 모두

2. 실행(EXECUTE)

SQL Server/Oracle

EXEC 프로시저명 @파라미터1 = 값1, @파라미터2 = 값2;

MySQL/PostgreSQL

CALL 프로시저명(값1, @출력변수);

삭제(DROP)

DROP PROCEDURE 프로시저명;

3. 단순 조회

SQL Server

CREATE PROCEDURE GetAllUsers
AS
BEGIN
    SELECT * FROM Users;
END;
-- 실행
EXEC GetAllUsers;

MySQL

DELIMITER //
CREATE PROCEDURE GetAllUsers()
BEGIN
    SELECT * FROM Users;
END //
DELIMITER ;
-- 실행
CALL GetAllUsers();

4. 파라미터 사용

SQL Server

CREATE PROCEDURE GetUserByID
    @UserID INT
AS
BEGIN
    SELECT * FROM Users WHERE id = @UserID;
END;
-- 실행
EXEC GetUserByID @UserID = 5;

MySQL

DELIMITER //
CREATE PROCEDURE GetUserByID(IN p_id INT)
BEGIN
    SELECT * FROM Users WHERE id = p_id;
END //
DELIMITER ;
-- 실행
CALL GetUserByID(5);

5. 프로시저 삭제

drop procedure 프로시저 이름;

6. 프로시저 쿼리문 조회

PostgreSQL

SELECT pg_catalog.pg_get_functiondef('update_salary'::regproc);

Oracle

특정 프로시저 소스 코드 조회

SELECT TEXT
FROM USER_SOURCE
WHERE NAME = '프로시저이름' AND TYPE = 'PROCEDURE'
ORDER BY LINE;

DBA 권한이 있을 때 전체 조회

SELECT TEXT
FROM DBA_SOURCE
WHERE NAME = '프로시저이름' AND TYPE = 'PROCEDURE'
ORDER BY LINE;

MySQL

프로시저 생성문 전체 조회

SHOW CREATE PROCEDURE 프로시저이름;

SQL Server

프로시저 소스 코드 스크립트로 조회

sp_helptext '프로시저이름';
-- 또는
SELECT OBJECT_DEFINITION(OBJECT_ID('프로시저이름'));

7. 함수 쿼리문 조회

PostgreSQL

함수 소스코드 전체 조회

SELECT pg_catalog.pg_get_functiondef('함수이름'::regproc);

Oracle

함수 소스코드 조회

SELECT TEXT
FROM USER_SOURCE
WHERE NAME = '함수이름' AND TYPE = 'FUNCTION'
ORDER BY LINE;

MySQL

함수 생성문 전체 조회

SHOW CREATE FUNCTION 함수이름;

함수 본문만 조회

SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = '함수이름'
  AND ROUTINE_TYPE = 'FUNCTION';

SQL Server

함수 소스코드 조회

SELECT OBJECT_DEFINITION(OBJECT_ID('스키마.함수이름'));
--또는
SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = '함수이름'
  AND ROUTINE_TYPE = 'FUNCTION';

8. 트랜잭션

CREATE PROCEDURE TransferFunds
    @FromAccount INT,
    @ToAccount INT,
    @Amount DECIMAL(10,2)
AS
BEGIN
    BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - @Amount WHERE id = @FromAccount;
    UPDATE accounts SET balance = balance + @Amount WHERE id = @ToAccount;
    COMMIT;
END;

키워드 정리

IS/AS

Oracle에서 사용,
헤더와 본문(변수, BEGIN~END) 구분

DELIMITER

MySQL에서 사용
구문 끝 구분자 ;를 임시로 DELIMITER로 변경하여 하나의 프로시저로 인식하게 하기 위함

CALL

MySQL, PostgreSQL에서 사용
저장 프로시저 실행(호출)

BEGIN

Oracle, MySQL 등에서 사용
실행 블록(본문) 시작에 쓰임