plsql package - example
an example to use plsql package/function/procedure of oracle,
include:
- package/functipn/procedure define
- sys_refcursor use
- call from plsql
- call from jdbc
------
table
create table customer(
customer_id NUMBER(10),
customer_name VARCHAR2(100),
address varchar2(200),
primary key(customer_id)
);
create table orders(
order_id NUMBER(10),
order_date DATE,
order_name VARCHAR2(200),
customer_id NUMBER(10),
primary key(order_id)
);
------
data
-- customer
declare
n CONSTANT NUMBER(4) := 5; -- row count
i NUMBER(4);
begin
for i in 1..n loop
insert into customer values (i, concat('eric',i), 'shenzhen');
end loop;
end;
-- orders
declare
n CONSTANT NUMBER(4) := 5; -- customer row count
i NUMBER(4); -- customer id
j NUMBER(2);
order_id NUMBER(6) := 1;
begin
for i in 1..n loop
for j in 1..i loop
insert into orders values (order_id, to_date('2010-3-21','YYYY-MM-DD'), concat('order_',order_id), i);
order_id:=order_id+1;
end loop;
end loop;
end;
------
package
create or replace
PACKAGE pkg_orders_by_customer
AS
procedure proc_orders_by_customer(p_customer_id in orders.customer_id%type, order_ref_cursor out SYS_REFCURSOR);
function fun_order_count(p_customer_id in NUMBER) return NUMBER;
END;
------
package body
create or replace PACKAGE body pkg_orders_by_customer AS
procedure proc_orders_by_customer(p_customer_id in orders.customer_id%type, order_ref_cursor out SYS_REFCURSOR)
IS
BEGIN
open order_ref_cursor for
select c.customer_id, o.order_id, o.order_name, o.order_date
from customer c join orders o on c.customer_id=o.customer_id
where o.customer_id = p_customer_id;
END proc_orders_by_customer;
function fun_order_count(p_customer_id in NUMBER) return NUMBER
IS
order_count NUMBER;
BEGIN
select count(*) into order_count from orders o where o.customer_id=p_customer_id;
return order_count;
END fun_order_count;
END;
------
call function - plsql
set serveroutput on;
declare
cid NUMBER(5);
ocount NUMBER(5);
n NUMBER(4):=5; -- customer count
begin
for cid in 1..n loop
ocount := pkg_orders_by_customer.fun_order_count(p_customer_id => cid);
dbms_output.put_line('customer_id: ' || cid || ', order_count: ' || ocount);
end loop;
end;
------
call procedure - plsql
set serveroutput on;
declare
n NUMBER(4):=5; -- customer count
cid NUMBER(5);
v_cursor SYS_REFCURSOR;
v_customer_id customer.customer_id%type;
v_order_id orders.order_id%type;
v_order_name orders.order_name%type;
v_order_date orders.order_date%type;
begin
for cid in 1..n loop
pkg_orders_by_customer.proc_orders_by_customer(p_customer_id => cid, order_ref_cursor => v_cursor);
loop
fetch v_cursor
into v_customer_id, v_order_id, v_order_name, v_order_date;
EXIT WHEN v_cursor%NOTFOUND;
dbms_output.put_line(v_customer_id || ', ' || v_order_id || ', ' || v_order_name || ', ' || v_order_date);
end loop;
close v_cursor;
dbms_output.new_line();
end loop;
end;
------
call function - jdbc
public static void functionTest() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url, user, password;
url = "jdbc:oracle:thin:@localhost:1521:xe";
user = "cumtspace";
password = "abcdefg";
// create connection
Connection conn = DriverManager.getConnection(url, user, password);
// create CallableStatement
CallableStatement cstmt = conn.prepareCall("{?=call pkg_orders_by_customer.fun_order_count(?)}");
cstmt.registerOutParameter(1, Types.INTEGER);
int customer_id = 4;
cstmt.setInt(2, customer_id);
// execute
cstmt.execute();
// get out param
Integer count = cstmt.getInt(1);
System.out.println(customer_id + ", " + count);
cstmt.close();
conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
------
call procedure - jdbc
public static void procedureTest() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url, user, password;
url = "jdbc:oracle:thin:@localhost:1521:xe";
user = "cumtspace";
password = "abcdefg";
// create connection
Connection conn = DriverManager.getConnection(url, user, password);
// create CallableStatement
CallableStatement cstmt = conn.prepareCall("{call pkg_orders_by_customer.proc_orders_by_customer(?,?)}");
// set in/out params
int cid = 4;
cstmt.setInt(1, cid);
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
// execute
cstmt.execute();
// get ResultSet from out param
ResultSet rs = (ResultSet) cstmt.getObject(2);
// handle resultset
int customer_id, order_id;
String order_name;
Date order_date;
while (rs.next()) {
customer_id = rs.getInt(1);
order_id = rs.getInt(2);
order_name = rs.getString(3);
order_date = rs.getDate(4);
System.out.format("%5d\t%5d\t%15s\t%s\n", customer_id, order_id, order_name, order_date);
}
// close
rs.close();
cstmt.close();
conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
------