存储过程与存储函数说明
指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。
l 什么时候用存储过程/存储函数
原则：如果只有一个返回值，用存储函数；否则，就用存储过程。
创建存储过程
用CREATE PROCEDURE命令建立存储过程。语法如下：
create [or replace]PROCEDURE 过程名[(参数列表)]
AS
变量声明
PLSQL子程序体；
例:
create or replaceprocedure sayHello
as
begin
dbms_output.put_line('hello world');
end;
--调用
set serveroutput on
begin
sayHello();
end;
--调用2
set serveroutput on
exec sayHello();
--涨工资存储过程,外部调用者提交事务
--有参数的存储过程
create or replace procedure addSal(eno in number)
as
begin
update emp2 set sal=sal*1.1 where empno=eno;
dbms_output.put_line('涨工资完毕!');
end;
select * from emp2;
EMPNO ENAME JOB MGRHIREDATE SAL COMM DEPTNO
------ ---------- --------- ---------- -------------- ---------- --------------------
7369 SMITH CLERK 7902 17-12月-80 800 20
SQL> exec addSal(7369);
涨工资完毕!
PL/SQL 过程已成功完成。
SQL> select * from emp2 where empno=7369;
EMPNO ENAME JOB MGRHIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- --------------------
7369 SMITH CLERK 7902 17-12月-80 880 20
--带两个参数
--有参数的存储过程
create or replace procedure addSal2(eno in number,num in number)
as
begin
update emp2 set sal=sal+num where empno=eno;
dbms_output.put_line(eno||'涨了'|| num || '工资');
end;
函数（Function）为一命名的存储程序，可带参数，并返回一计算值。函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型等。
存储函数
创建存储函数
语法如下：
CREATE [OR REPLACE] FUNCTION 函数名(参数列表)
RETURN 函数值类型
AS
变量声明
PLSQL子程序体；
--存储函数,必须有返回值
create or replace
function querySal(eno in number)
return number
as
vsal emp2.sal%type;
begin
select sal into vsal from emp2 where empno=eno;
return vsal;
end;
--存储过程返回多个参数
create or replace
procedure queryNameSal(eno in number,pname out varchar2,psal out number)
as
begin
select ename,sal into pname,psal from emp2 where empno=eno;
end;
--存储函数返回多个值
create or replace
function queryNameSal11(eno in number,vname out varchar2,vjob out varchar2)
return varchar2
as
vsal number;
begin
select ename,job,sal into vname,vjob,vsal from emp2 where empno=eno;
dbms_output.put_line(vname);
return vname;
end;
问题：有out的是返回值，还是return指明的那个值是返回值
out 可以返回多个,声明的return类型必须和out的一致,并且多个的时候,那多个元素也应该一致.
JDB调用存储过程:
JDBCUtils :数据库连接类
package com.util;
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.sql.Statement;
public classJDBCUtils {
private static Stringjdbcurl="jdbc:oracle:thin:@127.0.0.1:1521:orcl";
private static String driverClass="oracle.jdbc.driver.OracleDriver";
private static Stringusername="scott";
private static Stringpassword="root";
/**
* 获取数据库连接
*@return
*/
public static Connection getConnection(){
try {
Class.forName(driverClass);
returnDriverManager.getConnection(jdbcurl,username,password);
} catch (Exception e) {
// TODO: handle exception
throw newRuntimeException(e);
}
}
/**
*释放资源
*@param conn
*@param st
*@param rs
*/
public static void close(Connectionconn,Statement st,ResultSet rs){
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
throw newRuntimeException(e);
}finally{
try {
if(st!=null)
st.close();
} catch (SQLException e) {
throw newRuntimeException(e);
}finally{
try {
if(conn!=null)
conn.close();
} catch(SQLException e) {
throw newRuntimeException(e);
}
}
}
}
}
使用存储过程
package com.util;
importjava.sql.CallableStatement;
importjava.sql.Connection;
importjava.sql.Statement;
importjavax.swing.JButton;
importoracle.jdbc.OracleTypes;
importoracle.jdbc.oracore.OracleType;
importorg.junit.Test;
/*要调用的存储过程
* <pre>
* create or replace
procedure addSal2(eno in number,num innumber)
as
begin
update emp2 set sal=sal+num whereempno=eno;
dbms_output.put_line(eno||'涨了'|| num || '工资');
end;
* </pre>
*/
public classTestProcedure {
@Test
public void test() throws Exception{
String sql="{calladdSal2(?,?)}";
//创建callableStatement
Connectionconn=JDBCUtils.getConnection();
/* 存储函数
* {?=call <procedure-name>[(<arg1>,<arg2>, ...)]}
* 存储函数
{call<procedure-name>[(<arg1>,<arg2>, ...)]}
*/
CallableStatementcall=conn.prepareCall(sql);
//设置参数
call.setObject(1, 7369);
call.setObject(2, 100);
//执行
call.execute();
//释放资源
JDBCUtils.close(conn, call, null);
}
/*
* 要调用的存储过程，含有out参数
*create or replace
procedure queryNameSal(eno innumber,pname out varchar2,psal out number)
as
begin
select ename,sal intopname,psal from emp2 where empno=eno;
end;
*/
@Test
public void test2() throws Exception{
//获取连接并生成callableStatement
Connection connection=JDBCUtils.getConnection();
String sql="{callqueryNameSal(?,?,?)}";
CallableStatementcall=connection.prepareCall(sql);
//设置参数
call.setObject(1,7369);
//声明的参数
call.registerOutParameter(2,OracleTypes.VARCHAR);
call.registerOutParameter(3,OracleTypes.NUMBER);
call.execute();
//执行
//获取返回的结果(out参数)
String names=call.getString(2);
Double sal=call.getDouble(3);
JDBCUtils.close(connection, call,null);
//显示结果
System.out.println(names+"的工资是:"+sal);
}
}
--使用存储函数
package com.util;
importjava.sql.CallableStatement;
importjava.sql.Connection;
importoracle.jdbc.OracleTypes;
importorg.junit.Test;
public classTestFunction {
/*
*create or replace
function querySal(eno in number)
return number
as
vsal emp2.sal%type;
begin
select sal into vsal from emp2 where empno=eno;
return vsal;
end;
*/
//测试存储函数
@Test
public void test(){
int empno=7369;
Connection conn=null;
CallableStatement call=null;
String sql="{?=callquerySal(?)}";
try {
//获取连接与statement
conn=JDBCUtils.getConnection();
call=conn.prepareCall(sql);
//设置参数
call.setObject(2, empno);
call.registerOutParameter(1,OracleTypes.NUMBER);
//执行
call.execute();
//获取返回的结果
Doublesal=call.getDouble(1);
//显示结果
System.out.print("编号:"+empno+"的工资是:"+sal);
} catch (Exception e) {
// TODO: handle exception
}finally{
JDBCUtils.close(conn, call,null);
}
}
}
172
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
