SQL> create or replace package test_package as
2 procedure proc1;
3 end test_package;
4 /
패키지가 생성되었습니다.
SQL> create or replace package body test_package as
2 procedure proc1 is
3 begin
4 dbms_output.put_line('Hello World');
5 end proc1;
6 end test_package;
7 /
패키지 본문이 생성되었습니다.
SQL> exec test_package.proc1;
Hello World
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> begin test_package.proc1; end;
2 /
Hello World
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
....................................................................................................................................................
TABLE Data types
SQL> create or replace package test_package as
2 type ename_table is TABLE of emp.ename%type
3 index by binary_integer;
4 end test_package;
5 /
패키지가 생성되었습니다.
SQL> declare
2 v_enames test_package.ename_table;
3 begin
4 select ename into v_enames(1) from emp where empno=7369;
5 dbms_output.put_line(v_enames(1));
6 end;
7 /
SMITH
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
SQL> declare
v_enames test_package.ename_table;
begin
select ename into v_enames(1) from emp where empno=7369;
select ename into v_enames(2) from emp where empno=7499;
select ename into v_enames(3) from emp where empno=7369;
dbms_output.put_line(v_enames(1));
dbms_output.put_line(v_enames(2));
dbms_output.put_line(v_enames(3));
end;
/
SMITH
ALLEN
SMITH
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
SQL> declare
2 v_enames test_package.ename_table;
3 i binary_integer := 0;
4 begin
5 select ename into v_enames(1) from emp where empno=7369;
6 select ename into v_enames(2) from emp where empno=7499;
7 select ename into v_enames(3) from emp where empno=7369;
8 loop
9 i := i+1;
10 dbms_output.put_line(v_enames(i));
11 exit when i=3;
12 end loop;
13 end;
14 /
SMITH
ALLEN
SMITH
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
SQL> declare
2 v_enames test_package.ename_table;
3 i binary_integer := 0;
4 begin
5 select ename into v_enames(1) from emp where empno=7369;
6 select ename into v_enames(2) from emp where empno=7499;
7 select ename into v_enames(3) from emp where empno=7369;
8 loop
9 i := i+1;
10 exit when not v_enames.exists(i);
11 dbms_output.put_line(v_enames(i));
12 end loop;
13 end;
14 /
SMITH
ALLEN
SMITH
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
SQL> declare
2 v_enames test_package.ename_table;
3 i number := 0;
4 begin
5 select ename into v_enames(1) from emp where empno=7369;
6 select ename into v_enames(2) from emp where empno=7499;
7 select ename into v_enames(3) from emp where empno=7369;
8 loop
9 i := i+1;
10 exit when not v_enames.exists(i);
11 dbms_output.put_line(v_enames(i));
12 end loop;
13 end;
14 /
SMITH
ALLEN
SMITH
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
SQL> declare
2 v_enames test_package.ename_table;
3 i number := 0;
4 begin
5 i := i+1;
6 select ename into v_enames(i) from emp where empno=7369;
7 i := i+1;
8 select ename into v_enames(i) from emp where empno=7499;
9 i := i+1;
10 select ename into v_enames(i) from emp where empno=7369;
11 i := 0;
12 loop
13 i := i+1;
14 exit when not v_enames.exists(i);
15 dbms_output.put_line(v_enames(i));
16 end loop;
17 end;
18 /
SMITH
ALLEN
SMITH
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
SQL> create or replace procedure proc1(enames OUT test_package.ename_table)
2 is
3 i number := 0;
4 begin
5 i := i+1;
6 select ename into enames(i) from emp where empno=7369;
7 i := i+1;
8 select ename into enames(i) from emp where empno=7499;
9 i := i+1;
10 select ename into enames(i) from emp where empno=7369;
11 i := 0;
12 loop
13 i := i+1;
14 exit when not enames.exists(i);
15 dbms_output.put_line(enames(i));
16 end loop;
17 end proc1;
18 /
프로시저가 생성되었습니다.
SQL>
......................................................................................................................................................
위에 정의한 프로시저를 호출하는 자바 프로그램
package oracletable;
import java.sql.*;
import oracle.jdbc.*;
public class OracleTest
{
public static void main(String args[])
{
Connection con = null;
OracleCallableStatement cstmt = null;
try
{
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
con = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");
cstmt = (OracleCallableStatement)con.prepareCall("{call proc1(?)}");
int elemMaxLen = 20;
int maxLen = 20;
int elemSqlType = Types.VARCHAR;
cstmt.registerIndexTableOutParameter (1, maxLen, elemSqlType, elemMaxLen);
cstmt.execute();
String[] values = (String[])cstmt.getPlsqlIndexTable (1);
for(int i=0; i<values.length; i++)
{
System.out.println(values[i]);
}
} catch(Exception ex)
{
ex.printStackTrace(System.err);
} finally
{
if(cstmt != null) try{cstmt.close();}catch(Exception _ex){}
if(con != null) try{con.close();}catch(Exception _ex){}
}
}
}