오라클 프로시저 프로그래밍( Oracle Procedure Programming )
오라클사에서 제공하는 툴인 Oracle SQL Developer 를 이용하여 프로시저 작성하는 방법을 정리해봅니다
PL/SQL에 대한 자세한 문법 설명은 아래의 사이트를 참고하세요
https://www.tutorialspoint.com/plsql/plsql_tutorial.pdf
plsql_tutorial.pdf테스트 환경
Windows 7, JDK 1.7, Oracle 11g XE, Eclipse(luna), Oracle SQL Developer
프로시저 디버깅과 관련된 권한부여
Oracle SQL Developer 를 실행하고 DB 관리자로 접속하여 다음과 같이 scott 이용자에게 권한을 부여한다.
다음 권한은 프로시저를 디버깅할 때 요구된다
grant debug connect session to scott;
grant debug any procedure to scott;
익명 프로시저 작성 및 실행
익명 프로시저는 실제로는 별로 쓸 일이 없지만 개발자가 프로시저 작성법을 학습하는데는 좋은 소재가 된다
SQL Developer 에서 scott 계정으로 오라클에 접속하여 SQL Sheet 에 다음과 같이 익명 프로시저를 작성한다
set serveroutput on;
begin
dbms_output.put_line('Hello World');
end;
위와 같이 입력한 후에 위의 문장 전체를 선택한 후에 초록색 실행버튼을 누르면 'Hello World' 가 출력되는 것을 확인할 수 있다
이름을 가진 프로시저(저장 프로시저, Stored Procedure) 작성 및 실행
SQL Developer 에서 scott 계정으로 오라클에 접속하여 SQL Sheet 에 다음과 같이 프로시저를 작성한다
create or replace procedure sp_deptno
is
v_deptno emp.deptno%type;
begin
select deptno into v_deptno
from emp
where ename='SMITH';
dbms_output.put_line('SMITH의 부서= '|| v_deptno );
end;
입력된 프로시저 코드 상에 커서를 위치하고 CTRL + ENTER 키를 누르면 아래처럼 프로시저가 컴파일 되었다는 메시지를 확인할 수 있다.
SQL Developer 의 왼쪽 메뉴컬럼의 프로시저 노드를 열어보면 위에서 생성한 프로시저 이름을 확인할 수 있는데, 이름을 더블클릭하면 오른쪽 에디터에 프로시저의 소스코드가 나타나고 에디터에서 편집도 가능하다. 에디터 위쪽의 초록색 실행 버튼을 누르면 실행된 결과가 하단의 콘솔에 표시된다.
파라미터를 가진 프로시저의 예
다음과 같은 프로시저를 작성하고 컴파일한 후에 왼편의 프로시저 노드에서 해당 프로시저를 더블클릭하여 에디터에 연 다음에 초록색 실행버튼을 누르면 다음과 같이 프로시저 호출을 위한 파라미터 설정창이 열리는데 여기서 입출력을 위한 파라미터에 아규먼트를 설정할 수 있다
CREATE OR REPLACE PROCEDURE SP_GETEMP
(
v_DEPTNO IN NUMBER
, v_ENAME IN OUT VARCHAR2
, v_HIREDATE OUT DATE
) AS
BEGIN
select hiredate into v_HIREDATE
from emp where deptno=v_DEPTNO and ename=v_ENAME;
dbms_output.put_line(v_HIREDATE);
END SP_GETEMP;
프로시저 편집창에서 초록색 실행 버튼을 누르면 다음과 같이 파라미터를 설정할 수 있다
위에서 [확인] 버튼을 누르면 프로시저가 실행되고 출력변수의 값을 화면하단에 있는 [출력변수] 탭에 나타나게 된다. CURSOR가 출력변수로 설정된 경우에도 [출력변수] 탭에 커서의 전체 내용이 출력된다
위와 같이 작성된 오라클 프로시저를 자바 JDBC 를 이용하여 호출하고 그 결과를 출력하는 예
<%@page import="java.sql.*"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
pageEncoding="EUC-KR"%>
<%
Connection conn;
CallableStatement cstmt;
ResultSet rs;
String jdbc_driver = "oracle.jdbc.OracleDriver";
String db_url = "jdbc:oracle:thin:@localhost:1521:XE";
Class.forName(jdbc_driver);
conn = DriverManager.getConnection(db_url,"scott","tiger");
String procStr = "{call SP_GETEMP(?,?,?)}"; // IN, IN OUT, OUT
cstmt = conn.prepareCall(procStr);
cstmt.setInt(1, 20); // IN
cstmt.setString(2,"SMITH"); // IN OUT
cstmt.registerOutParameter(2, Types.VARCHAR);
cstmt.registerOutParameter(3, Types.DATE); // OUT
cstmt.executeQuery();
String ename = cstmt.getString(2);
java.sql.Date hiredate = cstmt.getDate(3);
cstmt.close();
conn.close();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Oracle Stored Procedure Test</title>
</head>
<body>
이 름 <%=ename %>
입사일 <%=hiredate %>
</body>
</html>