存儲過程與存儲函數說明
指存儲在資料庫中供所有使用者程式調用的子程式叫存儲過程、存儲函數。
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";
public static Connection getConnection(){
try {
Class.forName(driverClass);
returnDriverManager.getConnection(jdbcurl,username,password);
} catch (Exception e) {
// TODO: handle exception
throw newRuntimeException(e);
}
}
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;
public classTestProcedure {
@Test
public void test() throws Exception{
String sql="{calladdSal2(?,?)}";
//建立callableStatement
Connectionconn=JDBCUtils.getConnection();
CallableStatementcall=conn.prepareCall(sql);
//設定參數
call.setObject(1, 7369);
call.setObject(2, 100);
//執行
call.execute();
//釋放資源
JDBCUtils.close(conn, call, null);
}
@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 {
//測試存儲函數
@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);
}
}
}
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)