Oracle Procedure, Function 연습
Oracle Stored Procedure를 JDBC 프로그램에서 호출하는 예제
a. Stored Procedure의 OUT 파라미터를 JDBC에서 받아 오는 예
b. Stored Procedure의 OUT 파라미터로 선언된 SYS_REFCURSOR JDBC에서 받아 오는 예
문제 1) IN 파라미터로 사번을 받아서 해당 사원의 부서명을 출력하는 Procedure를 작성하시오.
SQL> ed proc1
create or replace PROCEDURE proc1
(emp_no IN emp.empno%TYPE)
IS
dept_name dept.dname%TYPE;
begin
select dname into dept_name from dept where deptno=(select deptno from emp where empno=emp_no);
dbms_output.put_line(dept_name);
end proc1;
/
SQL> @proc1
SQL> exec proc1(7369);
..................................................................................................................................................................................................................................................
문제 2) 사번을 IN 파라미터로 받고, 이름, 급여, 직책을 OUT 파라미터에 할당하여 화면에 출력하는 Procedure를 작성하시오.
SQL> ed proc2
create or replace PROCEDURE proc2
(emp_no IN emp.empno%TYPE,
e_name OUT emp.ename%TYPE,
salary OUT emp.sal%TYPE,
job OUT emp.job%TYPE
)
IS
begin
select ename, sal, job into e_name, salary, job from emp where empno=emp_no;
dbms_output.put_line(emp_no||':'||e_name||':'||salary||':'||job);
end proc2;
/
SQL> @proc2
SQL> var ename varchar2(16);
SQL> var sal number;
SQL> var job varchar2(16);
SQL> exec proc2(7369, :ename, :sal, :job);
SQL> print :ename;
ENAME
------------------
SMITH
..................................................................................................................................................................................................................................................
문제 3) 사번을 받아서 해당 사원의 이름을 리턴하는 FUNCTION을 작성하시오.
SQL> ed func1
(emp_no IN emp.empno%TYPE)
RETURN char
is
e_name emp.ename%TYPE;
begin
select ename into e_name from emp where empno=emp_no;
--dbms_output.put_line(e_name);
RETURN e_name;
end func1;
/
SQL> @func1
SQL> select func1(7369) from dual;
..................................................................................................................................................................................................................................................
문제 4) 문제 2번에서 작성한 Procedure를 JSP에서 호출하여 그 결과를 출력하는 프로그램을 작성하시오.
<%@ page language="java" contentType="text/html; charset=EUC-KR" pageEncoding="EUC-KR"%>
<%@page import="java.sql.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Stored Procedure Test</title>
</head>
<%-- 사용될 프로시저는 SQL Plus에서 다음과 같이 작성하여 오라클에 저장된 상태이다.
create or replace PROCEDURE proc2
(emp_no IN emp.empno%TYPE,
e_name OUT emp.ename%TYPE,
salary OUT emp.sal%TYPE,
job OUT emp.job%TYPE
)
IS
begin
select ename, sal, job into e_name, salary, job from emp where empno=emp_no;
dbms_output.put_line(emp_no||':'||e_name||':'||salary||':'||job);
end proc2;
--%>
<body>
<%
String jdbc_driver = "oracle.jdbc.OracleDriver";
String db_url = "jdbc:oracle:thin:@localhost:1521:ORCL";
Connection conn = null;
Class.forName(jdbc_driver);
conn = DriverManager.getConnection(db_url,"scott","tiger");
CallableStatement cstmt = conn.prepareCall("{call proc2(?,?,?,?)}");
cstmt.setInt(1, 7369);
cstmt.registerOutParameter(2, Types.VARCHAR);
cstmt.registerOutParameter(3, Types.INTEGER);
cstmt.registerOutParameter(4, Types.VARCHAR);
cstmt.executeQuery();
String ename = cstmt.getString(2);
int sal = cstmt.getInt(3);
String job = cstmt.getString(4);
%>
이름:<%=ename %><br>
급여:<%=sal %><br>
직책:<%=job %><br>
</body>
</html>
..................................................................................................................................................................................................................................................
Procedure의 OUT 파라미터로 SYS_REFCURSOR를 설정하여 ResultSet을 사용하는 연습
문제 5) 부서번호를 IN파라미터로 받아서 해당 부서에 근무하는 모든 사원의 정보를 SYS_REFCURSOR 형의 OUT파라미터에 설정하는 Procedure를 작성하고, JSP에서 호출하여 ResutlSet으로 받아 사용하는 프로그램을 작성하시오.
<%@ page language="java" contentType="text/html; charset=EUC-KR" pageEncoding="EUC-KR"%>
<%@page import="java.sql.*, oracle.jdbc.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Stored Procedure Test</title>
</head>
<%-- 사용될 프로시저는 SQL Plus에서 다음과 같이 작성하여 오라클에 저장된 상태이다.
create or replace PROCEDURE proc3
(dept_no IN emp.deptno%TYPE,
empcsr OUT SYS_REFCURSOR
)
is
begin
--CURSOR 타입변수에 데이터를 저장하는 방법이 특이하므로 주의요망
open empcsr for select * from emp where deptno=dept_no;
end proc3;
/
--%>
<body>
<%
String jdbc_driver = "oracle.jdbc.OracleDriver";
String db_url = "jdbc:oracle:thin:@localhost:1521:ORCL";
Connection conn = null;
Class.forName(jdbc_driver);
conn = DriverManager.getConnection(db_url,"scott","tiger");
CallableStatement cstmt = conn.prepareCall("{call proc3(?,?)}");
cstmt.setInt(1, 30);
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.executeQuery();
ResultSet rs = (ResultSet)cstmt.getObject(2);
while(rs.next()) { %>
이름:<%=rs.getInt("EMPNO") %>
급여:<%=rs.getString("ENAME") %>
직책:<%=rs.getString("JOB") %><br>
<%} %>
</body>
</html>