Oracle的高级编程主要是涉及到数据库的十大对象的使用,在这里,和大家分享一下我在学习这些技能时的一些笔记。希望对大家有所帮助!
阅读本文之前,您还可以查阅我之前关于数据库方面的介绍:
Oracle DDL基本操作
Oracle基本数据类型
Oracle数据库备份与恢复
接下来介绍一下 Oracle的高级编程。
1、存储过程返回单值参数
*call + 包名 + 存储过程名(传入、传出值)
String str = "{call smsbusiness.deleteZhZMember(?,?,?)}";
*建立连接
Connection conn = null;
conn = DriverManager.getConnection();
*使用java.sql.*类
CallableStatement cs = conn.prepareCall(str);
*传入参数
cs.setInt(1,id);
cs.setInt(2,-2);
*设置out值
cs.registerOutputParameter(3,Types.NUMBERIC);
*执行
cs.excuse();
2、存储过程返回多值参数
*包的建立
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE Test_CURSOR IS REF CURSOR;
END TESTPACKAGE;
*游标的建立
CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS
BEGIN
OPEN p_CURSOR FOR SELECT * FROM grade;
END TESTC;
*Java的调用
CallableStatement cs = conn.prepareCall(sqlStr);
ResultSet rs = (ResultSet)cs.getObject(2);
3、无返回值存储过程示例
CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS
BEGIN
INSERT INTO HYQ.B_ID (I_ID,I_NAME) VALUES (PARA1, PARA2);
END TESTA;
package com.hyq.src;
import java.sql.*;
import java.sql.ResultSet;
public class TestProcedureOne {
public TestProcedureOne() {
}
public static void main(String[] args ){
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521: hyq ";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
CallableStatement cstmt = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, " zl ", "123456");
CallableStatement proc = null; //创建执行存储过程的对象
proc = conn.prepareCall("{ call HYQ.TESTA(?,?) }"); //设置存储过程 call为关键字.
proc.setString(1, "100"); //设置第一个输入参数
proc.setString(2, "TestOne");//设置第二个输入参数
proc.execute();//执行
}
catch (SQLException ex2) {
ex2.printStackTrace();
}
catch (Exception ex2) {
ex2.printStackTrace();
}
finally{
try {
if(rs != null){
rs.close();
if(stmt!=null){
stmt.close();
}
if(conn!=null){
conn.close();
}
}
}
catch (SQLException ex1) {
}
}
}
4、基本返回值存储过程示例
CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS
BEGIN
SELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1;
END TESTB;
//Java代码:
package com.hyq.src;
public class TestProcedureTWO {
public TestProcedureTWO() {
}
public static void main(String[] args ){
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:zl";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");
CallableStatement proc = null;
proc = conn.prepareCall("{ call zl.TESTB(?,?) }"); //设置存储过程
proc.setString(1, "100");//设置第一个参数输入参数
proc.registerOutParameter(2, Types.VARCHAR);//第二个参数输出参数,是VARCHAR类型的
proc.execute();//执行
String testPrint = proc.getString(2);//获得输出参数
System.out.println("=testPrint=is="+testPrint);
}catch(Exception e){
}finally{
}
}
}
5、集合返回值存储过程
//数据库端:
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE Test_CURSOR IS REF CURSOR;
end TESTPACKAGE;
CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS
BEGIN
OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB;
END TESTC;
//Java代码:
package com.hyq.src;
import java.sql.*;
import java.io.OutputStream;
import java.io.Writer;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import oracle.jdbc.driver.*;
public class TestProcedureTHREE {
public TestProcedureTHREE() {
}
public static void main(String[] args ){
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:zl";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "hyq", "hyq");
CallableStatement proc = null;
proc = conn.prepareCall("{ call hyq.testc(?) }"); //存储过程 hyq包下的
proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);//设置输出参数是一个游标.第一个参数,游标类型
proc.execute();//执行
rs = (ResultSet)proc.getObject(1); //获得第一个参数是一个游标,转化成ResultSet类型
while(rs.next()) //获得数据
{
System.out.println(rs.getString(1) +rs.getString(2));
}
}catch (Exception ex2) {
ex2.printStackTrace();
}finally{
}
}
}