저장 프로시저

저장 프로시저(stored procedure)는 여러 문장을 처리하는 블록에 이름을 붙여 데이터베이스에 저장해 두고, 그 이름으로 불러 쓰는 절차형 SQL입니다. 값은 매개변수로 주고받습니다.

이름이 없는 블록은 적어서 보낼 때마다 전체를 다시 적어야 합니다. 같은 처리를 여러 프로그램이 쓰면 같은 블록이 여러 벌로 흩어집니다. 한 벌만 고치고 나머지를 놓치면 프로그램마다 다르게 동작합니다. 블록에 이름을 붙여 데이터베이스 안에 한 벌만 두면, 부르는 쪽은 이름과 값만 넘깁니다. 고칠 때도 한 곳만 고칩니다. 아래 그림은 과목코드를 받아 수강 인원을 돌려주는 프로시저를 만들고 부르는 장면입니다.

CREATE PROCEDURE 과목인원(  p_과목 IN VARCHAR2,  p_건수 OUT NUMBER) ISBEGIN  SELECT COUNT(*) INTO p_건수  FROM 수강  WHERE 과목코드 = p_과목;END;VARIABLE 결과 NUMBEREXECUTE 과목인원('OS', :결과)PRINT 결과

결과3

CREATE PROCEDURE 과목인원(  p_과목 IN VARCHAR2,  p_건수 OUT NUMBER) ISBEGIN↑ 몸으로  SELECT COUNT(*) INTO p_건수  FROM 수강  WHERE 과목코드 = p_과목;END;VARIABLE 결과 NUMBEREXECUTE 과목인원('OS', :결과)PRINT 결과

CREATE PROCEDURE 과목인원(  p_과목 IN VARCHAR2,  p_건수 OUT NUMBER) ISBEGIN  SELECT COUNT(*) INTO p_건수  FROM 수강  WHERE 과목코드 = p_과목;END;VARIABLE 결과 NUMBEREXECUTE 과목인원('OS', :결과)PRINT 결과

CREATE PROCEDURE 과목인원(  p_과목 IN VARCHAR2,  p_건수 OUT NUMBER) ISBEGIN저장됨  SELECT COUNT(*) INTO p_건수  FROM 수강  WHERE 과목코드 = p_과목;END;VARIABLE 결과 NUMBEREXECUTE 과목인원('OS', :결과)PRINT 결과

CREATE PROCEDURE 과목인원(  p_과목 IN VARCHAR2,  p_건수 OUT NUMBER) ISBEGIN  SELECT COUNT(*) INTO p_건수  FROM 수강  WHERE 과목코드 = p_과목;END;VARIABLE 결과 NUMBEREXECUTE 과목인원('OS', :결과)PRINT 결과

1프로시저를 만드는 꼴

CREATE PROCEDURE 다음에 이름을 쓰고, 괄호 안에 주고받을 매개변수를 적습니다. IS 뒤의 BEGIN과 END 사이가 부를 때 실행될 몸입니다. 앞 레슨의 블록과 견주면, DECLARE 자리에 머리가 들어선 것입니다.

CREATE PROCEDURE 이름(
  매개변수 모드 자료형,
  매개변수 모드 자료형
) IS
  -- 블록 안에서만 쓸 변수
BEGIN
  실행할 문장;
END;

• 이미 있는 이름으로 다시 만들면 오류입니다. 덮어쓰려면 CREATE OR REPLACE PROCEDURE⁠로 씁니다.
• IS 자리에는 AS를 써도 같습니다.
• IS와 BEGIN 사이는 선언부입니다. DECLARE라는 낱말은 쓰지 않고 변수만 적습니다.
• 프로시저를 지울 때는 DROP PROCEDURE 이름;입니다.

시험이 내는 표준 SQL 꼴은 Oracle 꼴과 세 군데가 다릅니다. 보기에 어느 쪽이 나와도 읽을 수 있어야 합니다.

CREATE PROCEDURE 이름(매개변수)
BEGIN
  실행할 문장;
END;

위 꼴을 Oracle에서 그대로 돌리면 컴파일 오류가 납니다. 이 레슨의 예제는 모두 Oracle 꼴입니다.

2매개변수의 세 가지 모드

매개변수에 붙는 모드는 값이 어느 쪽으로 흐르는지를 정합니다. 그림에서 'OS'는 안으로 들어가고 3은 밖으로 나왔습니다. 들어가는 자리가 IN, 나오는 자리가 OUT입니다.

• 모드를 적지 않으면 IN입니다. p_과목 VARCHAR2는 p_과목 IN VARCHAR2와 같습니다.
• IN 매개변수에 값을 담으면 만들 때 PLS-00363 오류가 납니다. 받기만 하는 자리이기 때문입니다.
• 매개변수의 자료형에는 길이를 적지 않습니다. VARCHAR2(10)이 아니라 VARCHAR2입니다.
• OUT·IN OUT으로 받으려면 부르는 쪽에 값을 담을 변수가 있어야 합니다. 값을 그대로 적을 수 없습니다.

3부르는 두 가지 문장

만들어 둔 프로시저는 EXECUTE 또는 CALL로 부릅니다. 둘 다 같은 일을 합니다.

EXECUTE 이름(값, 값);
CALL 이름(값, 값);

• EXECUTE는 EXEC로 줄여 쓸 수 있습니다. CALL은 표준 SQL의 호출문입니다.
• 블록 안에서 부를 때는 EXECUTE도 CALL도 쓰지 않고 이름(값, 값);처럼 이름만 적습니다.
• 괄호 안의 값은 적은 차례대로 매개변수에 담깁니다.
• OUT 자리에는 값 대신 받을 변수를 적습니다. 그림의 :결과가 그 자리입니다.

예제

프로시저를 만들고 부르는 데까지가 실습 하나입니다. VARIABLE 이름 자료형은 OUT 값을 받을 칸을 만드는 도구 명령이고, 그 칸은 쓸 때 :결과처럼 앞에 콜론을 붙입니다. PRINT 이름은 그 칸의 값을 보입니다. 블록마다 처음 데이터에서 시작합니다 — 앞 블록이 만들거나 바꾼 것은 남아 있지 않습니다.

IN과 OUT — 과목코드를 넣고 인원을 받는다

그림의 프로시저입니다. 운영체제(OS)를 듣는 학생 수를 세어 돌려줍니다.

CREATE PROCEDURE 과목인원(
  p_과목 IN VARCHAR2,
  p_건수 OUT NUMBER
) IS
BEGIN
  SELECT COUNT(*) INTO p_건수
  FROM 수강
  WHERE 과목코드 = p_과목;
END;
/
VARIABLE 결과 NUMBER
EXECUTE 과목인원('OS', :결과)
PRINT 결과

IN만 쓰는 프로시저 — 데이터를 고친다

돌려줄 값이 없으면 OUT도 필요 없습니다. 네트워크(NW) 점수를 3점씩 올립니다.

CREATE PROCEDURE 점수조정(
  p_과목 IN VARCHAR2,
  p_증가 IN NUMBER
) IS
BEGIN
  UPDATE 수강
  SET 점수 = 점수 + p_증가
  WHERE 과목코드 = p_과목;
  COMMIT;
END;
/
EXECUTE 점수조정('NW', 3)
SELECT 학번,
  점수
FROM 수강
WHERE 과목코드 = 'NW'
ORDER BY 학번;

과목코드와 올릴 점수를 바꿔 가며 같은 프로시저를 다시 부를 수 있습니다. 블록을 다시 적지 않아도 되는 것이 이름을 붙여 저장하는 까닭입니다.

IN OUT — 넣은 칸으로 되받는다

점수에 10점을 얹되 100점을 넘기지 않는 프로시저입니다. 받은 칸 하나로 넣고 되받습니다.

CREATE PROCEDURE 보너스(
  p_점수 IN OUT NUMBER
) IS
BEGIN
  p_점수 := p_점수 + 10;
  IF p_점수 > 100 THEN
    p_점수 := 100;
  END IF;
END;
/
VARIABLE 점수 NUMBER
EXECUTE :점수 := 95
EXECUTE 보너스(:점수)
PRINT 점수

95를 넣었고 105가 아니라 100이 나왔습니다. IN OUT은 들어온 값을 읽을 수 있어서 p_점수 + 10이 계산되고, 그 결과를 같은 자리에 다시 담아 돌려줍니다.

시험 포인트

• 필기: 만드는 문장은 CREATE PROCEDURE로 시작해 괄호 안에 매개변수를 적고, IS 뒤의 BEGIN과 END 사이에 실행할 문장을 둡니다. 부르는 문장은 EXECUTE 또는 CALL입니다. 지우는 문장은 DROP PROCEDURE입니다. 매개변수 모드를 묻는 문제가 가장 많습니다. IN은 받기만, OUT은 돌려주기만, IN OUT은 받고 돌려주기입니다. 모드를 적지 않으면 IN입니다. OUT 매개변수는 프로시저에 들어갈 때 비어 있습니다. 표준 꼴은 IS 없이 바로 BEGIN으로 가고 모드를 INOUT으로 붙여 씁니다.
• 실기: 이름을 쓰게 하면 저장 프로시저(Stored Procedure)이고, 매개변수 모드는 IN·OUT·IN OUT 세 가지입니다. 문장을 쓰게 하면 CREATE PROCEDURE로 시작해 END;로 끝까지 쓰고, 호출문은 EXECUTE 이름(값, 값);처럼 괄호와 세미콜론까지 씁니다. 매개변수는 이름 모드 자료형 차례이고 자료형에 길이를 적지 않습니다.

확인 문제

1. 건물 이름을 받아 그 건물을 쓰는 학과가 몇 개인지 세어 돌려주는 프로시저를 만듭니다. 받은 건물 이름은 프로시저가 바꾸지 않습니다. 두 매개변수의 모드로 알맞은 것을 고르세요.

• ① p_건물 IN, p_개수 IN
• ② p_건물 IN, p_개수 OUT
• ③ p_건물 IN OUT, p_개수 OUT
• ④ p_건물 OUT, p_개수 IN

2. 다음 (가)~(라)에 맞는 말을 쓰세요. (가)는 한글 이름과 영문으로 함께, (나)~(라)는 매개변수 모드를 적는 키워드로 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

• (가) 여러 문장을 처리하는 블록에 이름을 붙여 데이터베이스에 저장해 두고, EXECUTE나 CALL 문으로 불러 쓰는 것입니다.
• (나) 부른 쪽이 넘긴 값을 읽기만 하는 자리입니다. 이 자리에 값을 담으면 만들 때 오류가 납니다. 모드를 적지 않으면 이 자리가 됩니다.
• (다) 들어갈 때는 비어 있고, 담아 둔 값이 끝날 때 부른 쪽의 변수로 나가는 자리입니다.
• (라) 부른 쪽이 넘긴 값을 읽을 수도 있고, 고친 값을 같은 자리로 되돌려 줄 수도 있는 자리입니다.

3. 과목 표에서 학점을 받아 그 학점인 과목이 몇 개인지 세어 돌려주는 프로시저 학점과목수를 쓰고, 학점 3으로 불러 결과를 보이는 문장까지 쓰세요. 받을 칸의 이름은 개수로 합니다. 답을 끝까지 쓴 뒤 정답을 펼치세요.

CREATE PROCEDURE 학점과목수(
  p_학점 IN NUMBER,
  p_개수 OUT NUMBER
) IS
BEGIN
  SELECT COUNT(*)
    INTO p_개수
  FROM 과목
  WHERE 학점 = p_학점;
END;
/
VARIABLE 개수 NUMBER
EXECUTE 학점과목수(3, :개수)
PRINT 개수