1.sqlserver存储过程例子
首先:创建一个表student
有一个字段name 类型为varchar(100)
第二步:建立存储过程
ALTER PROCEDURE [dbo].[insertstudent]
@name varchar(100)
AS
BEGIN
insert into student values (@name);
END
第三步:在数据库中sql语句测试存储过程
exec insertstudent 'admin';
第四步:Java用jdbc的方式调用存储过程例子
@Test
public void testProc() throws Exception {
Class.forName("net.sourceforge.jtds.jdbc.Driver");
Connection conn = DriverManager.getConnection(
"jdbc:jtds:sqlserver://192.168.1.195:1433;DatabaseName=htth", "sa", "");// 三个参数
System.out.println(conn);
CallableStatement proc = null;
proc = conn.prepareCall("{call insertstudent(?)}");
proc.setString(1, "username");
proc.execute();
conn.close();
}
2.jdbc调用存储过程的多种情况
[一]、只有输入IN参数,没有输出OUT参数
CREATE OR REPLACE PROCEDURE TEST_MICHAEL_NOOUT(P_USERID IN VARCHAR2,
P_USERNAME IN VARCHAR2,
P_SALARY IN NUMBER,
P_OTHERINFO IN VARCHAR2) IS
BEGIN
INSERT INTO TMP_MICHAEL
(USER_ID, USER_NAME, SALARY, OTHER_INFO)
VALUES
(P_USERID, P_USERNAME, P_SALARY, P_OTHERINFO);
END TEST_MICHAEL_NOOUT;
/**
* 测试调用存储过程:无返回值
* @blog http://sjsky.iteye.com
* @author Michael
* @throws Exception
*/
public static void testProcNoOut() throws Exception {
System.out.println("------- start 测试调用存储过程:无返回值");
Connection conn = null;
CallableStatement callStmt = null;
try {
Class.forName(DB_DRIVER);
conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);
// 存储过程 TEST_MICHAEL_NOOUT 其实是向数据库插入一条数据
callStmt = conn.prepareCall("{call TEST_MICHAEL_NOOUT(?,?,?,?)}");
// 参数index从1开始,依次 1,2,3...
callStmt.setString(1, "jdbc");
callStmt.setString(2, "JDBC");
callStmt.setDouble(3, 8000.00);
callStmt.setString(4, "http://sjsky.iteye.com");
callStmt.execute();
System.out.println("------- Test End.");
} catch (Exception e) {
e.printStackTrace(System.out);
} finally {
if (null != callStmt) {
callStmt.close();
}
if (null != conn) {
conn.close();
}
}
}
[二]、既有输入IN参数,也有输出OUT参数,输出是简单值(非列表)
CREATE OR REPLACE PROCEDURE TEST_MICHAEL(P_USERID IN VARCHAR2,
P_SALARY IN NUMBER,
P_COUNT OUT NUMBER) IS
V_SALARY NUMBER := P_SALARY;
BEGIN
IF V_SALARY IS NULL THEN
V_SALARY := 0;
END IF;
IF P_USERID IS NULL THEN
SELECT COUNT(*)
INTO P_COUNT
FROM TMP_MICHAEL T
WHERE T.SALARY >= V_SALARY;
ELSE
SELECT COUNT(*)
INTO P_COUNT
FROM TMP_MICHAEL T
WHERE T.SALARY >= V_SALARY
AND T.USER_ID LIKE '%' || P_USERID || '%';
END IF;
DBMS_OUTPUT.PUT_LINE('v_count=:' || P_COUNT);
END TEST_MICHAEL;
/**
* 测试调用存储过程:返回值是简单值非列表
* @blog http://sjsky.iteye.com
* @author Michael
* @throws Exception
*/
public static void testProcOutSimple() throws Exception {
System.out.println("------- start 测试调用存储过程:返回值是简单值非列表");
Connection conn = null;
CallableStatement stmt = null;
try {
Class.forName(DB_DRIVER);
conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);
stmt = conn.prepareCall("{call TEST_MICHAEL(?,?,?)}");
stmt.setString(1, "");
stmt.setDouble(2, 3000);
// out 注册的index 和取值时要对应
stmt.registerOutParameter(3, Types.INTEGER);
stmt.execute();
// getXxx(index)中的index 需要和上面registerOutParameter的index对应
int i = stmt.getInt(3);
System.out.println("符号条件的查询结果 count := " + i);
System.out.println("------- Test End.");
} catch (Exception e) {
e.printStackTrace(System.out);
} finally {
if (null != stmt) {
stmt.close();
}
if (null != conn) {
conn.close();
}
}
}
[三]、既有输入IN参数,也有输出OUT参数,输出是列表
CREATE OR REPLACE PACKAGE TEST_PKG_CURSOR IS
-- Author : MICHAEL http://sjsky.iteye.com
TYPE TEST_CURSOR IS REF CURSOR;
END TEST_PKG_CURSOR;
CREATE OR REPLACE PROCEDURE TEST_P_OUTRS(P_SALARY IN NUMBER,
P_OUTRS OUT TEST_PKG_CURSOR.TEST_CURSOR) IS
V_SALARY NUMBER := P_SALARY;
BEGIN
IF P_SALARY IS NULL THEN
V_SALARY := 0;
END IF;
OPEN P_OUTRS FOR
SELECT * FROM TMP_MICHAEL T WHERE T.SALARY > V_SALARY;
END TEST_P_OUTRS;
/**
* 测试调用存储过程:有返回值且返回值为列表的
* @blog http://sjsky.iteye.com
* @author Michael
* @throws Exception
*/
public static void testProcOutRs() throws Exception {
System.out.println("------- start 测试调用存储过程:有返回值且返回值为列表的");
Connection conn = null;
CallableStatement stmt = null;
ResultSet rs = null;
try {
Class.forName(DB_DRIVER);
conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);
stmt = conn.prepareCall("{call TEST_P_OUTRS(?,?)}");
stmt.setDouble(1, 3000);
stmt.registerOutParameter(2, OracleTypes.CURSOR);
stmt.execute();
// getXxx(index)中的index 需要和上面registerOutParameter的index对应
rs = (ResultSet) stmt.getObject(2);
// 获取列名及类型
int colunmCount = rs.getMetaData().getColumnCount();
String[] colNameArr = new String[colunmCount];
String[] colTypeArr = new String[colunmCount];
for (int i = 0; i < colunmCount; i++) {
colNameArr[i] = rs.getMetaData().getColumnName(i + 1);
colTypeArr[i] = rs.getMetaData().getColumnTypeName(i + 1);
System.out.print(colNameArr[i] + "(" + colTypeArr[i] + ")"
+ " | ");
}
System.out.println();
while (rs.next()) {
StringBuffer sb = new StringBuffer();
for (int i = 0; i < colunmCount; i++) {
sb.append(rs.getString(i + 1) + " | ");
}
System.out.println(sb);
}
System.out.println("------- Test Proc Out is ResultSet end. ");
} catch (Exception e) {
e.printStackTrace(System.out);
} finally {
if (null != rs) {
rs.close();
}
if (null != stmt) {
stmt.close();
}
if (null != conn) {
conn.close();
}
}
}
[四]、输入输出参数是同一个(IN OUT)
CREATE OR REPLACE PROCEDURE TEST_P_INOUT(P_USERID IN VARCHAR2,
P_NUM IN OUT NUMBER) IS
V_COUNT NUMBER;
V_SALARY NUMBER := P_NUM;
BEGIN
IF V_SALARY IS NULL THEN
V_SALARY := 0;
END IF;
SELECT COUNT(*)
INTO V_COUNT
FROM TMP_MICHAEL
WHERE USER_ID LIKE '%' || P_USERID || '%'
AND SALARY >= V_SALARY;
P_NUM := V_COUNT;
END TEST_P_INOUT;
/**
* 测试调用存储过程: INOUT同一个参数:
* @blog http://sjsky.iteye.com
* @author Michael
* @throws Exception
*/
public static void testProcInOut() throws Exception {
System.out.println("------- start 测试调用存储过程:INOUT同一个参数");
Connection conn = null;
CallableStatement stmt = null;
try {
Class.forName(DB_DRIVER);
conn = DriverManager.getConnection(DB_CONNECTION, DB_NAME, DB_PWd);
stmt = conn.prepareCall("{call TEST_P_INOUT(?,?)}");
stmt.setString(1, "michael");
stmt.setDouble(2, 3000);
// 注意此次注册out 的index 和上面的in 参数index 相同
stmt.registerOutParameter(2, Types.INTEGER);
stmt.execute();
// getXxx(index)中的index 需要和上面registerOutParameter的index对应
int count = stmt.getInt(2);
System.out.println("符号条件的查询结果 count := " + count);
System.out.println("------- Test End.");
} catch (Exception e) {
e.printStackTrace(System.out);
} finally {
if (null != stmt) {
stmt.close();
}
if (null != conn) {
conn.close();
}
}
}