1 ?
。 PLSQL , 。
2
:
IN OUT IN-OUT
3 :
CREATE OR REPLACE PROCEDURE [(parameter,...)]
IS
Begin
Plsql
End;
: , 。
:
CREATE OR REPLACE PROCEDURE pro_1
IS
Begin
insert into person values (11,'aa','aav');
End;
: sql*plus
exec pro_1;
: JDBC 。
private Connection conn = null;
private ResultSet rs = null;
private CallableStatement state = null;
//
public void testPro()
{
conn = Tools.getConnection();
try {
state = conn.prepareCall("{call pro_1}");
state.execute();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
4 IN 。
: , 。
:
CREATE OR REPLACE PROCEDURE pro_2(id number,name varchar2,email varchar2)
IS
Begin
insert into person values (id,name,email);
End;
: sql*plus
exec pro_2(12,'aaa','aaa');
: JDBC 。
// IN
public void testPro_in(int id,String name,String email)
{
conn = Tools.getConnection();
try {
state = conn.prepareCall("{call pro_2(?,?,?)}");
state.setLong(1, id);
state.setString(2, name);
state.setString(3, email);
state.execute();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
5 out 。
: , Person 。
:
CREATE OR REPLACE PROCEDURE pro_3(num out number)
IS
mynum number;
Begin
select count(*) into mynum from person;
num := mynum;
End;
CREATE OR REPLACE PROCEDURE pro_3(num out number)
IS
Begin
select count(*) into num from person;
End;
: sql*plus
declare
a number;
begin
pro_3(a);
dbms_output.put_line(a);
end;
: JDBC 。
public void testPro_out()
{
conn = Tools.getConnection();
try {
state = conn.prepareCall("{call pro_3(?)}");
state.registerOutParameter(1, Types.NUMERIC);
state.execute();
int num = state.getInt(1);
System.out.println(num);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
6 in-out 。
:
CREATE OR REPLACE PROCEDURE pro_4(num in out number)
IS
a number := 100;
Begin
num := a*num;
End;
sql*plus
declare
a number := 12;
begin
pro_4(a);
dbms_output.put_line(a);
end;
:
, , ID 。