오라클 PL/SQL을 이용한 오라클 함수 만들기 예
Oracle SQL Developer 를 실행하고 왼편의 함수 항목에 마우스를 우측을 눌러서 '새함수' 를 선택하면 함수를 작성할 수 있는 윈도우가 열린다
다음과 같은 함수를 작성한다
사번을 전달하면 소속 부서명을 리턴하는 함수
CREATE OR REPLACE FUNCTION FC_GETDNAME1
(
p_EMPNO IN NUMBER
) RETURN VARCHAR2 AS
v_DNAME emp.ename%type;
v_DEPTNO emp.deptno%type;
BEGIN
select deptno into v_DEPTNO
from emp
where empno=p_EMPNO;
select dname into v_DNAME
from dept
where deptno=v_DEPTNO;
dbms_output.put_line('부서번호='||v_DNAME);
return v_DNAME;
END FC_GETDNAME1;
위와 같이 작성된 함수를 테스트할 때는 SQL Developer 에서 컴파일한 후에 초록색 실행버튼을 누르면 다음과 같이 함수를 호출할 때 필요한 파라미터 설정 윈도우가 열린다 여기서 사번을 입력하고 실행하면 콘솔에서 그 결과를 확인할 수 있다.
SQL Developer 에서 위의 함수를 테스트 실행해보면 콘솔에서 다음과 같은 결과를 볼 수 있다
XE_scott_tiger 데이터베이스에 접속 중
부서번호=RESEARCH
프로세스가 종료되었습니다.
XE_scott_tiger 데이터베이스에서 접속 해제 중
위와같이 작성되고 테스트된 함수는 SQL 문장 안에 포함된 상태로 호출될 수 있으므로 SQL Sheet 에서 다음과 같이 테스트할 수도 있다
select fc_getdname1(7369) from dual;
리턴 값과 OUT 파라미터를 동시에 가진 함수 선언 및 사용법
create or replace FUNCTION FC_GETDNAME2
(
p_EMPNO IN NUMBER
, p_DEPTNO OUT NUMBER
) RETURN VARCHAR2 AS
v_DNAME emp.ename%type;
BEGIN
select deptno into p_DEPTNO
from emp
where empno=p_EMPNO;
select dname into v_DNAME
from dept
where deptno=p_DEPTNO;
dbms_output.put_line('부서명='||v_DNAME);
return v_DNAME;
END FC_GETDNAME2;
리턴 값과 OUT 파라미터를 함께 가진 함수를 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");
// 함수에서 리턴되는 값은 하나의 OUT 파라미터로 취급할 수 있으므로 총 3개의 파라미터를 설정하면 된다
String funcCall = "{?=call FC_GETDNAME2(?,?)}";
cstmt = conn.prepareCall(funcCall);
cstmt.registerOutParameter(1, Types.VARCHAR);
cstmt.setInt(2, 7369);
cstmt.registerOutParameter(3, Types.INTEGER);
cstmt.execute();
String dname = cstmt.getString(1);
int deptno = cstmt.getInt(3);
cstmt.close();
conn.close();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Oracle Stored Procedure Test</title>
</head>
<body>
부서번호 <%=deptno %>
부서이름 <%=dname %>
</body>
</html>