存储过程
/*
打印Hello World
create [or replace] PROCEDURE 过程名(参数列表)
AS
PLSQL子程序体；
调用存储过程:
1. exec sayHelloWorld();
2. begin
sayHelloWorld();
sayHelloWorld();
end;
/
*/
create or replace procedure sayHelloWorld
as
--declare
--变量说明
begin
dbms_output.put_line('Hello World');
end;
/
/*
给指定的员工涨100的工资，并打印涨前和涨后的薪水
create [or replace] PROCEDURE 过程名(参数列表)
AS
PLSQL子程序体；
SQL> begin
2 raiseSalary(7839);
3 raiseSalary(7566);
4 commit;
5 end;
6 /
涨前:7986 涨后:8086
涨前:5024.53 涨后:5124.53
PL/SQL 过程已成功完成。
*/
create or replace procedure raiseSalary(eno in number)
as
--变量
psal emp.sal%type;
begin
--得到涨前薪水
select sal into psal from emp where empno=eno;
--涨工资
update emp set sal=sal+100 where empno=eno;
--问题：要不要commit？？
--打印
dbms_output.put_line('涨前:'||psal||' 涨后:'||(psal+100));
end;
/
存储函数
/*
查询某个员工的年收入
CREATE [OR REPLACE] FUNCTION 函数名(参数列表)
RETURN 函数值类型
AS
PLSQL子程序体；
*/
create or replace function queryEmpIncome(eno in number)
return number
as
--变量
psal emp.sal%type;
pcomm emp.comm%type;
begin
select sal,comm into psal,pcomm from emp where empno=eno;
return psal*12+nvl(pcomm,0);
end;
/
过程和函数中的in和out
l 一般来讲，过程和函数的区别在于 函数至少有一个返回值；而过程至少没有返回值 。 l 但过程和函数都可以通过 out 指定一个或多个输出参数。我们可以 利用 out 参数 ， 在过程和函数中实现返回多个值 。 l
什么时候用存储过程/存储函数？
l 原则： • 如果只有一个返回值，用存储函数；否则，就用存储过程。 l
/*
查询某个员工的姓名 月薪 职位
*/
create or replace procedure queryEmpInfo(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
as
begin
select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
end;
/
--查询某个部门中所有员工的所有信息
CREATE OR REPLACE
PACKAGE MYPACKAGE AS
type empcursor is ref cursor;
procedure queryEmpList(dno in number,empList out empcursor);
END MYPACKAGE;
==============================================
CREATE OR REPLACE
PACKAGE BODY MYPACKAGE AS
procedure queryEmpList(dno in number,empList out empcursor) AS
BEGIN
open empList for select * from emp where deptno=dno;
END queryEmpList;
END MYPACKAGE;
package demo.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtils {
private static String driver = "oracle.jdbc.OracleDriver";
private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
private static String user = "scott";
private static String password = "tiger";
static{
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection(){
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/*
* 执行java程序：
* java -Xms100M -Xmx200M HelloWorld
*/
public static void release(Connection conn, Statement st, ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs = null;//垃圾回收: 是否可以通过代码干预垃圾回收??
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
st = null;//垃圾回收: 是否可以通过代码干预垃圾回收??
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn = null;//垃圾回收: 是否可以通过代码干预垃圾回收??
}
}
}
}
/*
*
* 性能:
* Statement < PreparedStatement < CallableStatement
*/
public class TestOracle {
/*
* create or replace procedure queryEmpInfo(eno in number, pename out
* varchar2, psal out number, pjob out varchar2)
*/
@Test
public void testProcedure() {
// {call <procedure-name>[(<arg1>,<arg2>, ...)]}
String sql = "{call queryEmpInfo(?,?,?,?)}";
Connection conn = null;
CallableStatement call = null;
try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
// 赋值
call.setInt(1, 7839);
// 对于out参数，申明
call.registerOutParameter(2, OracleTypes.VARCHAR);
call.registerOutParameter(3, OracleTypes.NUMBER);
call.registerOutParameter(4, OracleTypes.VARCHAR);
// 调用
call.execute();
// 取出结果
String name = call.getString(2);
double sal = call.getDouble(3);
String job = call.getString(4);
System.out.println(name);
System.out.println(sal);
System.out.println(job);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(conn, call, null);
}
}
/*
* create or replace function queryEmpIncome(eno in number) return number
*/
@Test
public void testFunction() {
// {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
String sql = "{?=call queryEmpIncome(?)}";
Connection conn = null;
CallableStatement call = null;
try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
call.registerOutParameter(1, OracleTypes.NUMBER);
call.setInt(2, 7839);
// 执行
call.execute();
// 取出年收入
double income = call.getDouble(1);
System.out.println(income);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(conn, call, null);
}
}
/*
* 问题： 1. 光标是否被关？ 2. 是否能在MYSQL上执行？
*/
@Test
public void testCursor() {
String sql = "{call MYPACKAGE.queryEmpList(?,?)}";
Connection conn = null;
CallableStatement call = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
call.setInt(1, 10);
call.registerOutParameter(2, OracleTypes.CURSOR);
// 执行
call.execute();
// 取出集合
rs = ((OracleCallableStatement) call).getCursor(2);//必须强转 Oracle特有
while (rs.next()) {
String name = rs.getString("ename");
String job = rs.getString("job");
System.out.println(name + "的职位是" + job);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(conn, call, rs);
}
}
}
![网站架构及MySQL数据库使用数量[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)