oracle--08儲存過程
1.基本結構
CREATE OR REPLACE PROCEDURE 儲存過程名字
(
引數1 IN NUMBER,
引數2 IN NUMBER
) IS
變數1 INTEGER :=0;
變數2 DATE;
BEGIN
END 儲存過程名字
2.SELECT INTO STATEMENT
將select查詢的結果存入到變數中，可以同時將多個列儲存多個變數中，必須有一條
記錄，否則丟擲異常(如果沒有記錄丟擲NO_DATA_FOUND)
例子：
BEGIN
SELECT col1,col2 into 變數1,變數2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;
...
3.IF 判斷
IF V_TEST=1 THEN
BEGIN
do something
END;
END IF;
4.while 迴圈
WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
END LOOP;
5.變數賦值
V_TEST := 123;
6.用for in 使用cursor
...
IS
CURSOR cur IS SELECT * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM :=cur_result.列名1+cur_result.列名2
END;
END LOOP;
END;
7.帶引數的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(變數值);
LOOP
FETCH C_USER INTO V_NAME;
EXIT FETCH C_USER%NOTFOUND;
do something
END LOOP;
CLOSE C_USER;
8.用pl/sql developer debug
連線資料庫後建立一個Test WINDOW
在視窗輸入呼叫SP的程式碼,F9開始debug,CTRL+N單步除錯
簡單例項，通過DBMS_OUTPUT來看結果
CREATE OR REPLACE PROCEDURE bb (lic_para IN VARCHAR2,out_para OUT VARCHAR2)
AS
temp VARCHAR2 (100);
BEGIN
SELECT lic_no
INTO temp
FROM t_vehicle_info
WHERE lic_no = lic_para;
out_para:=temp;
DBMS_OUTPUT.put_line (out_para);
END bb;
下面是呼叫：
begin
-- Call the procedure
bb(lic_para => :lic_para,
out_para => :out_para);
end;
可以在命令列裡敲sqlplus “yonghuming/mima@dbname”接著除錯儲存過程。但是最好用的是在pl/sql Developer工具裡面用點儲存過程裡的TEST來呼叫,它能自動生成呼叫的語句並有欄目讓你輸入引數值，包括輸入引數和輸出引數，並把結果返回到輸出引數裡面，在結果欄可見，這點pl/sql Developer比TOAD要強，TOAD在儲存過程上右鍵後點選EXECUTE Procedure也可以執行，只是結果在那看我不曉得，而在pl/sql Developer按F9可以除錯，ctrl+N可以單步跟蹤，的確爽。
Oracle儲存過程基本語法 儲存過程
1 CREATE OR REPLACE PROCEDURE 儲存過程名
2 IS
3 BEGIN
4 NULL;
5 END;
行1:
CREATE OR REPLACE PROCEDURE 是一個SQL語句通知Oracle資料庫去建立一個叫做skeleton儲存過程, 如果存在就覆蓋它;
行2:
IS關鍵詞表明後面將跟隨一個PL/SQL體。
行3:
BEGIN關鍵詞表明PL/SQL體的開始。
行4:
NULL PL/SQL語句表明什麼事都不做，這句不能刪去，因為PL/SQL體中至少需要有一句;
行5:
END關鍵詞表明PL/SQL體的結束
儲存過程建立語法：
create or replace procedure 儲存過程名（param1 in type，param2 out type）
as
變數1 型別（值範圍）; --vs_msg VARCHAR2(4000);
變數2 型別（值範圍）;
Begin
Select count(*) into 變數1 from 表A where列名=param1；
If (判斷條件) then
Select 列名 into 變數2 from 表A where列名=param1；
Dbms_output。Put_line(‘列印資訊');
Elsif (判斷條件) then
Dbms_output。Put_line(‘列印資訊');
Else
Raise 異常名（NO_DATA_FOUND）;
End if;
Exception
When others then
Rollback;
End;
注意事項：
1， 儲存過程引數不帶取值範圍，in表示傳入，out表示輸出
型別可以使用任意Oracle中的合法型別。
2， 變數帶取值範圍，後面接分號
3， 在判斷語句前最好先用count（*）函式判斷是否存在該條操作記錄
4， 用select 。。。into。。。給變數賦值
5， 在程式碼中拋異常用 raise+異常名
CREATE OR REPLACE PROCEDURE儲存過程名
(
--定義引數
is_ym IN CHAR(6) ,
the_count OUT NUMBER,
)
AS
--定義變數
vs_msg VARCHAR2(4000); --錯誤資訊變數
vs_ym_beg CHAR(6); --起始月份
vs_ym_end CHAR(6); --終止月份
vs_ym_sn_beg CHAR(6); --同期起始月份
vs_ym_sn_end CHAR(6); --同期終止月份
--定義遊標(簡單的說就是一個可以遍歷的結果集)
CURSOR cur_1 IS
SELECT 。。。
FROM 。。。
WHERE 。。。
GROUP BY 。。。;
BEGIN
--用輸入引數給變數賦初值，用到了Oralce的SUBSTR TO_CHAR ADD_MONTHS
TO_DATE 等很常用的函式。
vs_ym_beg := SUBSTR(is_ym,1,6);
vs_ym_end := SUBSTR(is_ym,7,6);
vs_ym_sn_beg := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,'yyyymm'), -12),'yyyymm');
vs_ym_sn_end := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,'yyyymm'), -12),'yyyymm');
--先刪除表中特定條件的資料。
DELETE FROM 表名 WHERE ym = is_ym;
--然後用內建的DBMS_OUTPUT物件的put_line方法列印出影響的記錄行數，其中用到一個系統變數SQL%rowcount
DBMS_OUTPUT.put_line('del上月記錄='||SQL%rowcount||'條');
INSERT INTO表名(area_code,ym,CMCODE,rmb_amt,usd_amt)
SELECT area_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000
FROM BGD_AREA_CM_M_BASE_T
WHERE ym >= vs_ym_beg
AND ym <= vs_ym_end
GROUP BY area_code,CMCODE;
DBMS_OUTPUT.put_line('ins當月記錄='||SQL%rowcount||'條');
--遍歷遊標處理後更新到表。遍歷遊標有幾種方法，用for語句是其中比較直觀的一種。
FOR rec IN cur_1 LOOP
UPDATE 表名
SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn
WHERE area_code = rec.area_code
AND CMCODE = rec.CMCODE
AND ym = is_ym;
END LOOP;
COMMIT;
--錯誤處理部分。OTHERS表示除了宣告外的任意錯誤。SQLERRM是系統內建變數儲存了當前錯誤的詳細資訊。
EXCEPTION
WHEN OTHERS THEN
vs_msg := 'ERROR IN xxxxxxxxxxx_p('||is_ym||'):'||SUBSTR(SQLERRM,1,500);
ROLLBACK;
--把當前錯誤記錄進日誌表。
INSERT INTO LOG_INFO(proc_name,error_info,op_date)
VALUES('xxxxxxxxxxx_p',vs_msg,SYSDATE);
COMMIT;
RETURN;
END;
oracle儲存過程語法
1 、判斷語句:
if 比較式 then begin end; end if;
create or replace procedure test(x in number) is
begin
if x >0 then
begin
x := 0 - x;
end;
end if;
if x = 0 then
begin
x: = 1;
end;
end if;
end test;
2 、For 迴圈
For ... in ... LOOP
-- 執行語句
end LOOP;
(1) 迴圈遍歷遊標
create or replace procedure test() as
Cursor cursor is select name from student; name varchar(20);
begin
for name in cursor LOOP
begin
dbms_output.putline(name);
end;
end LOOP;
end test;
(2) 迴圈遍歷陣列
create or replace procedure test(varArray in myPackage.TestArray) as
--( 輸入引數varArray 是自定義的陣列型別，定義方式見標題6)
i number;
begin
i := 1; -- 儲存過程陣列是起始位置是從1 開始的，與java 、C 、C++ 等語言不同。因為在Oracle 中本是沒有陣列的概念的，陣列其實就是一張
-- 表(Table), 每個陣列元素就是表中的一個記錄，所以遍歷陣列時就相當於從表中的第一條記錄開始遍歷
for i in 1..varArray.count LOOP
dbms_output.putline('The No.'|| i || 'record in varArray is:'||varArray(i));
end LOOP;
end test;
3 、While 迴圈
while 條件語句 LOOP
begin
end;
end LOOP;
E.g
create or replace procedure test(i in number) as
begin
while i < 10 LOOP
begin
i:= i + 1;
end;
end LOOP;
end test;
4 、陣列
首先明確一個概念：Oracle 中本是沒有陣列的概念的，陣列其實就是一張表(Table), 每個陣列元素就是表中的一個記錄。
使用陣列時，使用者可以使用Oracle 已經定義好的陣列型別，或可根據自己的需要定義陣列型別。
(1) 使用Oracle 自帶的陣列型別
x array; -- 使用時需要需要進行初始化
e.g:
create or replace procedure test(y out array) is
x array;
begin
x := new array();
y := x;
end test;
(2) 自定義的陣列型別 ( 自定義資料型別時，建議通過建立Package 的方式實現，以便於管理)
create or replace package myPackage is
Public type declarations type info is record( name varchar(20), y number);
type TestArray is table of info index by binary_integer;
-- 此處宣告瞭一個TestArray 的型別資料，其實其為一張儲存Info 資料型別的Table 而已，及TestArray 就是一張表，有兩個欄位，一個是name ，一個是y 。需要注意的是此處使用了Index by binary_integer 編制該Table 的索引項，也可以不寫，直接寫成：type TestArray is
table of info ，如果不寫的話使用陣列時就需要進行初始化：varArray myPackage.TestArray; varArray := new myPackage.TestArray();
end TestArray;
5. 遊標的使用 Oracle 中Cursor 是非常有用的，用於遍歷臨時表中的查詢結果。其相關方法和屬性也很多，現僅就常用的用法做一二介紹：
(1)Cursor 型遊標( 不能用於引數傳遞)
create or replace procedure test() is
cusor_1 Cursor is select std_name from student where ...; --Cursor 的使用方式1 cursor_2 Cursor;
begin
select class_name into cursor_2 from class where ...; --Cursor 的使用方式2
可使用For x in cursor LOOP .... end LOOP; 來實現對Cursor 的遍歷
end test;
(2)SYS_REFCURSOR 型遊標，該遊標是Oracle 以預先定義的遊標，可作出引數進行傳遞
create or replace procedure test(rsCursor out SYS_REFCURSOR) is
cursor SYS_REFCURSOR;
name varhcar(20);
begin
OPEN cursor FOR select name from student where ... --SYS_REFCURSOR 只能通過OPEN 方法來開啟和賦值
LOOP
fetch cursor into name --SYS_REFCURSOR 只能通過fetch into 來開啟和遍歷 exit when cursor%NOTFOUND; --SYS_REFCURSOR 中可使用三個狀態屬性： ---%NOTFOUND( 未找到記錄資訊) %FOUND( 找到記錄資訊) ---%ROWCOUNT( 然後當前遊標所指向的行位置)
dbms_output.putline(name);
end LOOP;
rsCursor := cursor;
end test;
例項
下面寫一個簡單的例子來對以上所說的儲存過程的用法做一個應用：
現假設存在兩張表，一張是學生成績表(studnet) ，欄位為：stdId,math,article,language,music,sport,total,average,step
一張是學生課外成績表(out_school), 欄位為:stdId,parctice,comment
通過儲存過程自動計算出每位學生的總成績和平均成績，同時，如果學生在課外課程中獲得的評價為A ，就在總成績上加20 分。
create or replace procedure autocomputer(step in number) is
rsCursor SYS_REFCURSOR;
commentArray myPackage.myArray;
math number;
article number;
language number;
music number;
sport number;
total number;
average number;
stdId varchar(30);
record myPackage.stdInfo;
i number;
begin
i := 1;
get_comment(commentArray); -- 呼叫名為get_comment() 的儲存過程獲取學生課外評分資訊
OPEN rsCursor for select stdId,math,article,language,music,sport from student t where t.step = step;
LOOP
fetch rsCursor into stdId,math,article,language,music,sport; exit when rsCursor%NOTFOUND;
total := math + article + language + music + sport;
for i in 1..commentArray.count LOOP
record := commentArray(i);
if stdId = record.stdId then
begin
if record.comment = 'A' then
begin
total := total + 20;
go to next; -- 使用go to 跳出for 迴圈
end;
end if;
end;
end if;
end LOOP;
<<continue>> average := total / 5;
update student t set t.total=total and t.average = average where t.stdId = stdId;
end LOOP;
end;
end autocomputer;
-- 取得學生評論資訊的儲存過程
create or replace procedure get_comment(commentArray out myPackage.myArray) is
rs SYS_REFCURSOR ；
record myPackage.stdInfo;
stdId varchar(30);
comment varchar(1);
i number;
begin
open rs for select stdId,comment from out_school
i := 1;
LOOP
fetch rs into stdId,comment; exit when rs%NOTFOUND;
record.stdId := stdId;
record.comment := comment;
recommentArray(i) := record;
i:=i + 1;
end LOOP;
end get_comment;
-- 定義陣列型別myArray
create or replace package myPackage is begin
type stdInfo is record(stdId varchar(30),comment varchar(1));
type myArray is table of stdInfo index by binary_integer;
end myPackage;
的朋友可以參考下
1.分頁類
package org.zh.basic;
/**
* 頁面類
*
* @author keven
*
*/
public class PageInfo {
// 定義
private String p_tableName; // -表名
private String p_strWhere; // --查詢條件
private String p_orderColumn; // --排序的列
private String p_orderStyle; // --排序方式
private int p_curPage; // --當前頁
private int p_pageSize; // --每頁顯示記錄條數
private int p_totalRecords; // --總記錄數
private int p_totalPages; // --總頁數
// / <summary>
// / 定義函式
// / </summary>
public PageInfo() {
}
public PageInfo(String p_tableName, String p_strWhere,
String p_orderColumn, String p_orderStyle, int p_curPage,
int p_pageSize, int p_totalRecords, int p_totalPages) {
this.p_tableName = p_tableName;
this.p_strWhere = p_strWhere;
this.p_orderColumn = p_orderColumn;
this.p_orderStyle = p_orderStyle;
this.p_curPage = p_curPage;
this.p_pageSize = p_pageSize;
this.p_totalRecords = p_totalRecords;
this.p_totalPages = p_totalPages;
}
public String getP_tableName() {
return p_tableName;
}
public void setP_tableName(String pTableName) {
p_tableName = pTableName;
}
public String getP_strWhere() {
return p_strWhere;
}
public void setP_strWhere(String pStrWhere) {
p_strWhere = pStrWhere;
}
public String getP_orderColumn() {
return p_orderColumn;
}
public void setP_orderColumn(String pOrderColumn) {
p_orderColumn = pOrderColumn;
}
public String getP_orderStyle() {
return p_orderStyle;
}
public void setP_orderStyle(String pOrderStyle) {
p_orderStyle = pOrderStyle;
}
public int getP_curPage() {
return p_curPage;
}
public void setP_curPage(int pCurPage) {
p_curPage = pCurPage;
}
public int getP_pageSize() {
return p_pageSize;
}
public void setP_pageSize(int pPageSize) {
p_pageSize = pPageSize;
}
public int getP_totalRecords() {
return p_totalRecords;
}
public void setP_totalRecords(int pTotalRecords) {
p_totalRecords = pTotalRecords;
}
public int getP_totalPages() {
return p_totalPages;
}
public void setP_totalPages(int pTotalPages) {
p_totalPages = pTotalPages;
}
}
2 呼叫
package org.zh.sys.server;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Map;
import java.util.HashMap;
import org.hibernate.Session;
import org.zh.basic.PageInfo;
import org.zh.dao.HibernateSessionFactory;
import oracle.jdbc.driver.OracleTypes;
import oracle.jdbc.OracleCallableStatement;
public class GeneratePage {
public GeneratePage() {
}
public static ArrayList Prc_Page(PageInfo page) {
ArrayList list = new ArrayList();
Map mp;
Session s = null;
Connection conn = null;
ResultSet rs = null;
CallableStatement proc = null;
try {
s = HibernateSessionFactory.getSession();
conn = s.connection();
proc = conn.prepareCall("{call pages.prc_page(?,?,?,?,?,?,?,?,?)}");
proc.setString(1, page.getP_tableName());
proc.setString(2, page.getP_strWhere());
proc.setString(3, page.getP_orderColumn());
proc.setString(4, page.getP_orderStyle());
proc.setInt(5, page.getP_curPage());
proc.setInt(6, page.getP_pageSize());
proc.registerOutParameter(7, OracleTypes.NUMBER);
proc.registerOutParameter(8, OracleTypes.NUMBER);
proc.registerOutParameter(9, OracleTypes.CURSOR);
proc.execute();
// page.setP_totalRecords(proc.getInt("p_totalRecords"));
// page.setP_totalPages(proc.getInt("p_totalPages"));
// list = (ArrayList) proc.getObject("v_cur");
page.setP_totalRecords(proc.getInt(7));
page.setP_totalPages(proc.getInt(8));
rs = ((OracleCallableStatement) proc).getCursor(9); // 得到輸出結果集引數
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
while (rs.next()) {
mp = new HashMap(numberOfColumns);
for (int r = 1; r < numberOfColumns; r++) {
mp.put(rsmd.getColumnName(r), rs.getObject(r));
}
list.add(mp);
}
return list;
} catch (SQLException ex) {
ex.printStackTrace();
return list;
} catch (Exception ex2) {
ex2.printStackTrace();
return list;
} finally {
try {
if (proc != null) {
proc.close();
}
if (rs != null) {
rs.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException ex1) {
ex1.printStackTrace();
}
}
}
}
1、 PL/SQL語句塊
PL/SQL語句塊只適用於Oracle資料庫，使用時臨時儲存在客戶端，而不是儲存在資料庫。
基本語法:
declare
變數宣告、初始化
begin
業務處理、邏輯程式碼
exception
異常捕獲
end;
變數宣告：<變數名> <型別及長度> [:=<初始值>]
例：v_name varchar2(20):=’張三’;
例：見第3節
2、 迴圈語句
loop迴圈語法：
loop
exit when 表示式
end loop;
while迴圈語法：
while 表示式 loop
end loop;
for迴圈語法:
for <變數> in <變數取值範圍（小值..大值，如1..100）> loop
end loop;
for迴圈的變數可不做宣告及初始化。
例：見第3節
3、 if判斷語句
基本語法：
if <表示式> then
…
else if <表示式> then
…
else
…
end if;
end if;
例：
declare
v_identity number(4):=0;
begin
loop
if v_identity=1then
dbms_output.put_line('v_identity=1');
elseif v_identity=3then
dbms_output.put_line('v_identity=3');
elseif v_identity=6then
exit;
else
dbms_output.put_line('v_identity is not 1 or 3');
endif;
endif;
endif;-- 注意，有多少個if就要有多少個end if結束標誌。
v_identity:=v_identity+1;
endloop;
exception
whenothersthen dbms_output.put_line('error!');
end;
/
4、 分支case
基本語法：
case <變數>
when 常量 then
…
when 常量 then
…
else
…
end case;
例：
declare
v_number number(4):=3;
v_string varchar(20):='abc';
begin
case v_number
when1then
dbms_output.put_line('v_number is '||1);
when2then
dbms_output.put_line('v_number is '||2);
when3then
dbms_output.put_line('v_number is '||3);
endcase;
case v_string
when'ab'then
dbms_output.put_line('v_string is '||'ab');
when'bc'then
dbms_output.put_line('v_string is '||'bc');
else--預設匹配
dbms_output.put_line('v_string is other value');
endcase;
exception
whenothersthen dbms_output.put_line('error!');
end;
/
5、 異常(exception)
宣告異常語法：<異常名> exception;
丟擲異常語法：raise <異常名>;
捕獲異常語法：when <異常名> then 異常處理語句;
例：
declare
v_input varchar2(1):='&throw';--動態輸入
v_exception_1 exception; --自定義異常
v_exception_2 exception;
othersexception;-- 系統異常
begin
if v_input='1'then
raise v_exception_1;-- 丟擲異常
elseif v_input='2'then
raise v_exception_2;
else
raiseothers;
endif;
endif;
exception
--捕獲異常
when v_exception_1then dbms_output.put_line('throw exception: v_exception_1');
when v_exception_2then dbms_output.put_line('throw exception: v_exception_2');
whenothersthen dbms_output.put_line('throw exception: others');
end;
/
6、 遊標(cursor)
宣告遊標語法：cursor <遊標名> is select語句;
宣告ref遊標語法:<遊標名> is ref cursor;
開啟遊標語法：open <遊標名>;
移動遊標並獲取資料語法：fetch <遊標名> into <用於儲存讀取的資料的變數的名>;
關閉遊標語法:close <遊標名>;
遊標屬性（遊標的屬性必須在關閉遊標之前）：
%isopen: 判斷遊標是否開啟
%notfound: 找不到資料時
%found:
%rowcount: 返回當前遊標已掃描的資料行數量
遊標分類：1、顯示遊標（自定義遊標）；2、隱示遊標（系統遊標）；3、REF遊標
例：
declare
v_row t_test%rowtype;-- 匹配t_test表中一行所有的資料型別
cursor v_curisselect *from t_test;--宣告遊標
begin
open v_cur;--開啟遊標
loop
fetch v_curinto v_row;--將遊標所在行的資料轉存到v_row中
exitwhen v_cur%notfound;-- 當遊標到最後一行時跳出
dbms_output.put_line('id = '||v_row.t_id||' name = '||v_row.t_name||' msg = '||v_row.t_msg);
endloop;
close v_cur;--關閉遊標
exception
whenothersthen dbms_output.put_line('throw exception: others');
end;
/
-- REF遊標 --
createorreplacepackage upk_select_test
astype uc_testisrefcursor;-- 宣告ref遊標
end upk_select_test;
/
-- 儲存過程中呼叫ref遊標，並將查詢結果以遊標的方式返回
createorreplaceprocedure up_select_test_2
(uc_result out upk_select_test.uc_test)
is
begin
open uc_resultforselect *from t_test;
end up_select_test_2;
/
7、 通配型別操作符
%type: 通配某行某列資料型別，如v_name t_test.t_name%type;通配表t_test中的t_name。
%rowtype: 通配一行所有列的資料型別，如 v_row t_test%rowtype;匹配t_test表中一行
所有的資料型別。
8、 儲存過程(procedure)
基本語法：
create procedure <過程名>(<引數列表,無參時忽略>)
as|is
變數宣告、初始化
begin
業務處理、邏輯程式碼
exception
異常捕獲、容錯處理
end <過程名>;
引數:<引數名> in|out|in out <引數型別，無長度說明> ，如：v_name varchar2
in：入參
out：出參
in out：出入參
注：as|is表示as或is
呼叫語法：
1)、exec <過程名>;
2)、execute <過程名>;
3)、在PL/SQL語句塊中直接呼叫。
例：
createorreplaceprocedure up_wap(v_param1 inoutvarchar2,v_param2inoutvarchar2)
is
v_temp varchar2(20);
begin
dbms_output.put_line('交換前引數1：'||v_param1||' 引數2：'||v_param2);
v_temp:=v_param1;
v_param1:=v_param2;
v_param2:=v_temp;
dbms_output.put_line('交換後引數1：'||v_param1||' 引數2：'||v_param2);
exception
whenothersthen dbms_output.put_line('There is a error when the procedure up_wap executing!');
end up_wap;
/
-- 呼叫儲存過程
declare
v_param1 varchar2(20):='param1';
v_param2 varchar2(20):='param2';
begin
up_wap(v_param1 => v_param1,v_param2 => v_param2);
end;
/
9、 自定義函式(function)
基本語法：
create function <函式名>(<引數列表,無參時忽略>)
return <返回值型別，無長度說明>
as|is
變數宣告、初始化
begin
業務處理、邏輯程式碼
return <返回的值>;
exception
異常捕獲、容錯處理
end <函式名>;
引數：in 入參
注：只有入參的型別。
在儲存過程和自定義函式中的引數的傳遞（入參和出參）不能使用%type或%rowtype匹配，不能使用空值null，但是儲存過程可以返回空值。
例：
createfunction uf_select_name_by_id_test(v_idinnumber)
returnvarchar2
is
v_name t_test.t_name%type;
begin
select t_nameinto v_namefrom t_testwhere t_id=v_id;
return v_name;
exception
whenothersthen dbms_output.put_line('error');
end uf_select_name_by_id_test;
/
select uf_select_name_by_id_test(1)姓名from dual;-- select呼叫
declare--pl/sql語句塊呼叫
v_name varchar2(20);
begin
v_name:=uf_select_name_by_id_test(1);
dbms_output.put_line('name = '||v_name);
end;
/
10、包(package)
封裝，可以封裝過程(procedure)、函式(function)和變數。
注意，在包(package)中宣告的過程(procedure)和函式(function)必須在包的實現體
（package body）中定義實現。
基本語法：
create package <包名>
as|is
變數宣告
儲存過程宣告
自定義函式宣告
end <包名>;
/
create package <包名,與宣告部分一致>
as|is
儲存過程的程式碼實現
自定義函式的程式碼實現
end <包名>;
/
例：
-- 建立包upk_hello
createorreplacepackage upk_hello
is
v_hello_world varchar2(20):='hello world';-- 宣告變數
procedure up_hello_world(v_nameinvarchar2);--宣告過程
function uf_hello_world(v_nameinvarchar2)returnvarchar2;--宣告函式
end upk_hello;
/
-- 實現包(upk_hello)裡宣告的方法
createorreplacepackagebody upk_hello
is
procedure up_hello_world(v_nameinvarchar2)
is
v_string varchar2(100);
begin
v_string:=v_name||' say hello world!';
dbms_output.put_line(v_string);
exception
whenothersthen dbms_output.put_line('error');
end up_hello_world;
function uf_hello_world(v_nameinvarchar2)returnvarchar2
is
v_string varchar2(100);
begin
v_string:=v_name||' say hello world!';
return v_string;
exception
whenothersthen dbms_output.put_line('error');
end uf_hello_world;
end upk_hello;
/
-- 包的呼叫
declare
v_msg varchar2(100);
begin
upk_hello.up_hello_world('bing');
v_msg:=upk_hello.uf_hello_world('admin');
dbms_output.put_line(v_msg);
dbms_output.put_line(upk_hello.v_hello_world);
end;
1.基本結構
CREATE OR REPLACE PROCEDURE Oracle 儲存過程名字
(
引數1 IN NUMBER,
引數2 IN NUMBER
) IS
變數1 INTEGER :=0;
變數2 DATE;
BEGIN
END 儲存過程名字
2.SELECT INTO STATEMENT
將select查詢的結果存入到變數中，可以同時將多個列儲存多個變數中，必須有一條
記錄，否則丟擲異常(如果沒有記錄丟擲NO_DATA_FOUND)
例子：
BEGIN
SELECT col1,col2 into 變數1,變數2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;
...
3.IF 判斷
IF V_TEST=1 THEN
BEGIN
do something
END;
END IF;
4.while 迴圈
WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
END LOOP;
5.變數賦值
V_TEST := 123;
6.用for in 使用cursor
...
IS
CURSOR cur IS SELECT * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM :=cur_result.列名1+cur_result.列名2
END;
END LOOP;
END;
7.帶引數的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(變數值);
LOOP
FETCH C_USER INTO V_NAME;
EXIT FETCH C_USER%NOTFOUND;
do something
END LOOP;
CLOSE C_USER;
8.用pl/sql developer debug
連線資料庫後建立一個Test WINDOW
在視窗輸入呼叫SP的程式碼,F9開始debug,CTRL+N單步除錯
關於Oracle 儲存過程的若干問題備忘
1.在oracle中，資料表別名不能加as，如：
select a.appname from appinfo a;-- 正確
select a.appname from appinfo as a;-- 錯誤
也許，是怕和Oracle中的儲存過程中的關鍵字as衝突的問題吧
2.在Oracle儲存過程中，select某一欄位時，後面必須緊跟into，如果select整個記錄，利用遊標的話就另當別論了。
select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 有into，正確編譯
select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 沒有into，編譯報錯，提示：Compilation
Error: PLS-00428: an INTO clause is expected in this SELECT statement
3.在利用select...into...語法時，必須先確保資料庫中有該條記錄，否則會報出"no data found"異常。
可以在該語法之前，先利用select count(*) from 檢視資料庫中是否存在該記錄，如果存在，再利用select...into...
4.在儲存過程中，別名不能和欄位名稱相同，否則雖然編譯可以通過，但在執行階段會報錯
select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;-- 正確執行
select af.keynode into kn from APPFOUNDATION af where af.appid=appid and af.foundationid=foundationid;-- 執行階段報錯，提示
ORA-01422:exact fetch returns more than requested number of rows
5.在Oracle儲存過程中，關於出現null的問題
假設有一個表A，定義如下：
create table A(
id varchar2(50) primary key not null,
vcount number(8) not null,
bid varchar2(50) not null -- 外來鍵
);
如果在儲存過程中，使用如下語句：
select sum(vcount) into fcount from A where bid='xxxxxx';
如果A表中不存在bid="xxxxxx"的記錄，則fcount=null(即使fcount定義時設定了預設值，如：fcount number(8):=0依然無效，fcount還是會變成null)，這樣以後使用fcount時就可能有問題，所以在這裡最好先判斷一下：
if fcount is null then
fcount:=0;
end if;
Qracle PL/SQL
--儲存過程
1.PL/SQL
SQL是資料庫普通話，每個資料庫在實現SQL國際標準之外，也有自己特有的語句。（Hibernate dialect）
通訊的標準：ODBC(ado , ado.net)，JDBC(jdo , Hibernate)
ODBC、JDBC是最有效率的，但是開發繁瑣，才有後來括號中的高度函式化的擴充
PL/SQL:Procudural Language Extension to SQL.
在SQL語句基礎上，加上了結構化流程控制，可用多條SQL語句完成一個功能。
PL/SQL功能強大，經常用來書寫複雜的業務邏輯。
語法：
set serveroutput on; //開啟控制檯輸出
declare [變數名] [變數資料型別]; //定義變數，寫在begin之前
begin
//程式碼塊
end; //程式碼編輯區
:= //賦值符號
dbms_output.put_line('內容'||變數); //控制檯輸出，和Java不同的是連線使用的是||符號而不是加
號，注意字串必須用單引號來修飾
範例：
declare result int;
begin
select x into result from ttt where rownum=1;
result:=result+1;
dbms_output.put_line('result='||result);
end;
學生表的建立
create table my_student
(
stuno int primary key,
stuname varchar2(20) not null,
stuage int not null
)
範例2
declare
v_stuname varchar2(20);
v_stuage int;
begin
select stuname, stuage into v_stuname,v_stuage
from my_student
where stuno=2;
dbms_output.put_line('sutdent name'||v_stuname||', student age:'||v_stuage);
exception
when NO_DATA_FOUND then
dbms_output.put_line('There is no student with stu no 2');
end;
範例3：
new -> program window ->procedure
--根據學號顯示對應學生資訊
create or replace procedure uuu_show_student(x_stuno in int)
is
v_stuname varchar2(20);
v_stuage int;
begin
select stuname, stuage into v_stuname,v_stuage
from my_student
where stuno=x_stuno;
dbms_output.put_line('sutdent name'||v_stuname||', student age:'||v_stuage);
exception
when NO_DATA_FOUND then
dbms_output.put_line('There is no student with stu no 2');
end uuu_show_student;
執行儲存過程
執行execute uuu_show_student;
範例4.兩數求和
create or replace procedure show_uuu_number(x_num1 in int,x_num2 in int)
is
x_big_number int;
x_num_index int;
begin
if x_num1 > x_num2 then
x_big_number := x_num1;
x_num_index := 1;
else
x_big_number := x_num2;
x_num_index := 2;
end if;
dbms_output.put_line('big number:'||x_big_number||' index:'||x_num_index);
end show_uuu_number;
範例5.迴圈分支
create or replace procedure show_uuu_number(x_num1 in int,x_num2 in int)
is
x_big_number int;
x_num_index int;
begin
if x_num1 > x_num2 then
x_big_number := x_num1;
x_num_index := 1;
elsif x_num1 > x_num2 then
x_big_number := x_num2;
x_num_index := 2;
else
dbms_output.put_line('equal');
end if;
if x_num1 <> x_num2 then
dbms_output.put_line('big number:'||x_big_number||' index:'||x_num_index);
end if;
end show_uuu_number;
》case
when....
when....
else
.....
and case;
範例6
有返回值的用function
計算三角形面積
create or replace function uuu_area(width in number,height in number) return number is
Result number;
begin
Result := 0.5*width*height;
return(Result);
end uuu_area;
執行函式
select uuu_area(3,5) from dual;
uuu_AREA(3,5)
-------------
15
範例7
》case
when....
when....
else
.....
and case;
應用
create or replace function show_uuu_day(datestr in varchar2)
return varchar2 is
Result varchar2(200);
checkdate date;
v_day varchar2(1);
begin
checkdate := to_date(trim(datestr), 'YYYY-MM-DD');
v_day := to_char(checkdate, 'D');
case v_day
when '1' then
Result := datestr || '是星期天';
when '2' then
Result := datestr || '是星期一';
when '3' then
Result := datestr || '是星期二';
when '4' then
Result := datestr || '是星期三';
when '5' then
Result := datestr || '是星期四';
when '6' then
Result := datestr || '是星期五';
when '7' then
Result := datestr || '是星期六';
else
dbms_output.put_line(datestr || '是星期六');
end case;
return(Result);
end show_uuu_day;
執行函式
SQL> select show_uuu_day('2010-11-4') from dual;
SHOW_uuu_DAY('2010-11-4')
--------------------------------------------------------------------------------
2010-11-4是星期四
範例8
迴圈結構
create or replace function uuu_get_sum(num1 in number, num2 in number) return number is
Result number := 0;
temp number;
begin
temp := num1;
loop
Result:=Result+temp;
temp:=temp+1;
-- if temp > num2 then
-- exit;
--end if;
exit when temp > num2;
end loop;
return(Result);
end uuu_get_sum;
執行函式
SQL> select uuu_get_sum(1,100) from dual;
uuu_GET_SUM(1,100)
------------------
5050
while loop
例：
create or replace function uuu_get_sum(num1 in number, num2 in number) return
number is
Result number := 0;
temp number;begin
temp := num1;
while num2 <= 200 loop
Result:=Result+temp;
temp:=temp+1;
-- if temp > num2 then
-- exit;
--end if;
exit when temp > num2;
end loop; return(Result);
end uuu_get_sum;
執行函式
SQL> select uuu_get_sum(1,1000) from dual;
uuu_GET_SUM(1,1000)
-------------------
0
範例8
數字for loop迴圈
create or replace procedure sum(begin1 number,end2 number)
as
tosum number;
begin
tosum:=0;
for i in begin1..end2
loop
tosum:=tosum+i;
end loop;
dbms_output.put_line(tosum);
end sum;
001.判斷一個數是否為質數
create or replace function x_isprime(num in int) return int is
Result int;
begin
for i in 2..num-1
loop
if num mod i = 0 then
Result := 0;
return(Result);
end if;
end loop;
if num >= 2 then
Result := 1;
end if;
return(Result);
end x_isprime;
002判斷質數
create or replace procedure x_prime(begini in int, endi in int)
is
count2 int := 0;
begin
for i in begini..endi
loop
if x_isprime(i) = 1 then
dbms_output.put(i||' ');
count2:=count2+1;
if count2 mod 8 = 0 then
dbms_output.put_line('');
end if;
end if;
end loop;
end x_prime;
======
debug 許可權設定
grant debug connect session to test1;
003判斷質數
=======
CREATE OR REPLACE PROCEDURE show_prime(bval IN INT, eval IN INT) IS
flag INT;
icount int:=0;
BEGIN
-- 取數迴圈
FOR i IN bval .. eval LOOP
-- 質數判斷迴圈
flag := 1;
FOR j IN 2 .. i - 1 LOOP
IF i MOD j = 0 THEN
flag := 0;
EXIT;
END IF;
END LOOP;
IF flag = 1 THEN
dbms_output.put(i||' ');
icount:=icount+1;
if icount mod 8 =0 then
dbms_output.put_line('');
end if;
END IF;
END LOOP;
END show_prime;
======================================================================
--遊標
pl/sql cursor 操作
1. 什麼是遊標？
oracle在執行一條SQL語句的時候，它將建立一個記憶體區域 (context area),該記憶體區域包含執行這條語句所需要的所有資訊。
資訊如下：
1. 該語句執行之後返回的記錄集
2. 一個指標，指向了該語句在記憶體中的被解析後的結果。
cursor(遊標) 是一個handle (pointer), 指向了這個上下文區域。
通過cursor, PL/SQL程式能夠控制context area, 掌握在語句執行的時，將如何對該區域產生影響。
2. 遊標的型別
1) implicit cursor
每條SQL語句執行的時候，將自動產生一個implicit遊標。 該遊標，使用者不可控制。
一個cursor將自動和每條DML語句關聯 （update,delete,insert), 我們可以通過cursor瞭解上頭語句產生的結果。
所有update和delete語句相關聯cursor，包含了該操作影響的行的集合。
最後開啟的cursor， 名字叫SQL cursor.
--------------------------------------------------------------------------------------------
update my_student set stuname='mary' where stuno=60;
dbms_output.put_line(SQL%ROWCOUNT);
2) explicit cursor
使用者自己定義的遊標，針對的是返回超過一條記錄的查詢。 使用者可以通過該cursor控制記錄返回過程。
Record Type
記錄是複雜的資料結構。記錄往往表現成為表的一行。
create or replace procedure show_student2 is
vr_student my_student%ROWTYPE;
begin
select *
into vr_student
from my_student where stuno=6;
dbms_output.put_line(vr_student.stuno||','||vr_student.stuname);
end show_student2;
a. 宣告遊標 （declare a cursor)
該操作初始化這個遊標，為其建立記憶體空間。
CURSOR c_cursor_name is select statement
(該遊標和select語句相關聯)
------------------
declare
v_name varchar2(20);
CURSOR c_mycursor is
select * from student where name like '%h%';
遊標開啟後，不能繼續再二次開啟。
b. 開啟遊標 (open cursor)
建立context area, 執行語句， 獲得rows.
open c_mycursor
c. 獲取cursor中的行。
fetch cursorname into pl/sql variables
fetch cursorname into pl/sql record
d. 關閉cursor
一旦所有的行被處理結束，cursor應該被關閉。
關閉的操作通知pl/sql engine， 程式對該cursor的需求已經結束，可以釋放context are所佔用的記憶體資源。
cursor一旦關閉，則不可以繼續fetch . 也不能重複關閉cursor.
> 常用的cursor屬性
cursorname%NOTFOUND
cursorname%FOUND
cursorname%ROWCOUNT
cursorname%ISOPEN
--------------------
create or replace procedure show_student2 is
CURSOR c_student is
select * from my_student order by stuno desc;
vr_student my_student%ROWTYPE;
TYPE simple_stu is record
(
stuname my_student.stuname%TYPE,
stuage my_student.stuage%TYPE,
stuage2 int
);
vr_simple_student simple_stu;
cursor c_simple_student is
select stuname,stuage,stuage+2 from my_student order by stuno desc;
begin
/*
select *
into vr_student
from my_student where stuno=6;
dbms_output.put_line(vr_student.stuno||','||vr_student.stuname);
*/
open c_student;
loop
fetch c_student into vr_student;
exit when c_student%NOTFOUND;
dbms_output.put_line(vr_student.stuno||','||vr_student.stuname);
end loop;
if c_student %ISOPEN then
close c_student;
end if;
open c_simple_student;
loop
fetch c_simple_student into vr_simple_student;
exit when c_simple_student%NOTFOUND;
dbms_output.put_line(vr_simple_student.stuname||','||vr_simple_student.stuage
||','||vr_simple_student.stuage2);
end loop;
close c_simple_student;
end show_student2;
--------------------------------------------
cursor for loop
nested cursor
DECLARE
v_sid student.student_id%TYPE;
CURSOR c_student IS
SELECT student_id, first_name, last_name
FROM student
WHERE student_id < 110;
CURSOR c_course IS
SELECT c.course_no, c.description
FROM course c, section s, enrollment e
WHERE c.course_no = s.course_no
AND s.section_id = e.section_id
AND e.student_id = v_sid;
BEGIN
/*
使用for loop cursor, 可以便捷的遍歷遊標，省去了open,fetch,close的書寫。
連儲存變數的定義也可以省略，可在for後直接書寫變數名。
*/
FOR r_student IN c_student LOOP
v_sid := r_student.student_id;
DBMS_OUTPUT.PUT_LINE(chr(10));
DBMS_OUTPUT.PUT_LINE(' The Student ' || r_student.student_id || ' ' ||
r_student.first_name || ' ' ||
r_student.last_name);
DBMS_OUTPUT.PUT_LINE(' is enrolled in the ' || 'following courses: ');
-- nested cursor
FOR r_course IN c_course LOOP
DBMS_OUTPUT.PUT_LINE(r_course.course_no || ' ' ||
r_course.description);
END LOOP;
END LOOP;
END;
---------------------------------------------------------------
帶引數的遊標
CURSOR c_student(p_stuage in my_student.stuage%type) is
select * from my_student where stuage=p_stuage order by stuno desc;
for vr_student in c_student(20)
loop
dbms_output.put_line(vr_student.stuno||','||vr_student.stuname);
end loop;
CURSOR c_student２(p_stuage in my_student.stuage%type，p_stuname in my_student.stuname%TYPE) is
select * from my_student where stuage=p_stuage order by stuno desc;
cursor具備的引數：
1) cursor得到了複用。
2) 提高了效能，壓縮了返回的行的數量。
====================================================================
--異常
1.PL/SQL Exception
常用的預定義異常
a）NO_DATA_FOUND
根據查詢條件，沒有查詢記錄被找到
b）TOO_MANY_ROWS
select into 結構只能返回一條記錄，賦予儲存過程變數。如果select ..into..結構返回記錄為多條，將產生這個異常
c）ZERO_DIVIDE
除數是0(把ORA-01476 error對映成ZERO_DIVIDE錯誤）
例： SQL> select 6/0 from dual;
select 6/0 from dual
ORA-01476: 除數為 0
d)VALUE_ERROR
從運算或者資料庫中取得值賦予變數的時候型別不匹配或者長度不足，導致的異常
e)DUP_VAL_ON_INDEX
主鍵不可重複，違反主鍵唯一約束
f）OTHERS
(類似java Exception異常）
資料庫表
create table my_student(
stuno int primary key,
stuname varchar2(20),
stuage int
);
insert into my_student values(1,'dadiv',20);
insert into my_student values(2,'mary',20);
insert into my_student values(3,'henry',20);
異常舉例例子：
create or replace procedure uuu_show_student(x_stuno in int)
is
v_stuname varchar2(20);--替換v_stuname varchar2(2)/int值不匹配或者長度不足異常
v_stuage int;
begin
insert into my_student values(2,'kate',49);--DUP_VAL_ON_INDEX異常
select stuname, stuage into v_stuname,v_stuage
from my_student
where stuno=x_stuno;--加上or stuname like '%y'產生值記錄太多異常
dbms_output.put_line('sutdent name'||v_stuname||', student age:'||v_stuage);
exception
when NO_DATA_FOUND then
dbms_output.put_line('There is no student with stu no '||x_stuno);
when VALUE_ERROR then
dbms_output.put_line('值不匹配');
when TOO_MANY_ROWS then
dbms_output.put_line('記錄太多');
when DUP_VAL_ON_INDEX then
dbms_output.put_line('主鍵不可重複，插入失敗');
when OTHERS then
dbms_output.put_line('其它異常捕獲');--一些預定義異常的父類
end uuu_show_student;
異常的作用域
DECLARE
v_student_id NUMBER := &sv_student_id;
v_name VARCHAR2(30);
v_total NUMBER(1);
-- outer block
BEGIN
SELECT RTRIM(first_name)||' '||RTRIM(last_name)
INTO v_name
FROM student
WHERE student_id = v_student_id;
DBMS_OUTPUT.PUT_LINE ('Student name is '||v_name);
-- inner block
BEGIN
SELECT COUNT(*)
INTO v_total
FROM enrollment
WHERE student_id = v_student_id;
DBMS_OUTPUT.PUT_LINE ('Student is registered for '||
v_total||' course(s)');
EXCEPTION
WHEN VALUE_ERROR OR INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred');
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('There is no such student');
END;
自定義異常：
例：
create or replace procedure uuu_show_student(x_stuno in int)
is
v_stuname varchar2(20);--替換v_stuname varchar2(2)/int值不匹配或者長度不足異常
v_stuage int;
e_invalid_stuno EXCEPTION;--自定義異常
begin
if x_stuno < 0 then
raise e_invalid_stuno;
else
--insert into my_student values(2,'kate',49);--DUP_VAL_ON_INDEX異常
select stuname, stuage into v_stuname,v_stuage
from my_student
where stuno=x_stuno;--加上or stuname like '%y'產生值記錄太多異常
dbms_output.put_line('sutdent name'||v_stuname||', student age:'||v_stuage);
end if;
exception
when NO_DATA_FOUND then
dbms_output.put_line('There is no student with stu no '||x_stuno);
when VALUE_ERROR then
dbms_output.put_line('值不匹配');
when TOO_MANY_ROWS then
dbms_output.put_line('記錄太多');
when e_invalid_stuno then --控制檯輸入execute uuu_show_student(-2);
dbms_output.put_line('學生編號不合法');
when DUP_VAL_ON_INDEX then
dbms_output.put_line('主鍵不可重複，插入失敗');
when OTHERS then
dbms_output.put_line('其它異常捕獲');--一些預定義異常的父類
end uuu_show_student;
==================================================================
--觸發器的一個例子
create or replace trigger student_aud
before insert on my_student
for each row
declare
v_highage int;
begin
select stu_highage
into v_highage
from stu_stat;
if :NEW.stuage >25 then
v_highage:=v_highage+1;
end if ;
update stu_stat set stu_count=stu_count+1,stu_highage=v_highage;
end student_aud;
Java呼叫Oracle儲存過程
步驟：
1、編寫Oracle儲存過程；
2、編寫資料庫獲取連線工具類；
3、編寫簡單應用呼叫儲存過程。
實現：
1、Oracle儲存過程
(省了，本文主要講述怎麼在java中呼叫儲存過程)
在此假設此儲存過程有三個引數，最後會返回一個遊標。
2、資料庫連線工具類
/**
* 資料庫連線工具類
*/
public class DBUtil {
/**
* 連線工廠
*/
public static Connection makeConnection(){
Connection conn = null;
try {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:zfvdb", "root", "zfvims");
} catch (SQLException e) {
System.out.println("通過JDBC獲取Oralce資料庫連結物件出錯!");
e.printStackTrace();
}
return conn;
}
/**
* 連線測試
*/
public static void main(String args[]){
if(DBUtil.makeConnection()==null){
System.out.println("獲取資料庫連結失敗！");
}
System.out.println("JDBC獲取Oracle資料庫連結成功！");
}
}
3、呼叫儲存過程
/**
* 呼叫儲存過程測試
*/
public class Test {
/**
* 測試方法
*/
public void test(){
Connection conn = DBUtil.makeConnection();
String sql = "{call p_test(?,?,?)}";
CallableStatement cs;
try {
cs = conn.prepareCall(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
cs.setLong(1, "1300");
cs.setString(2, "2006-01-01");
cs.setString(3, "2007-01-01");
ResultSet rs = cs.executeQuery();
while (rs.next()) {
//todo：進行相關操作
}
rs.close();
cs.close();
} catch (SQLException e) {
System.out.println("呼叫儲存過程p_test出現異常！");
e.printStackTrace();
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
System.out.println("發生不能關閉JDBC連結的異常，請檢查！");
e.printStackTrace();
}
}
}
public static void main(String args[]){
new Test().test();
}
}
說明：Java呼叫其他資料庫的儲存過程方式和此例一樣，儲存過程有的不返回值，比如對資料庫執行一些update、insert、delete等操作。這個時候呼叫有所不同。詳細請參看J2SDK參考文件。
ORACLE儲存過程，函式，包，遊標
部落格分類： Oracle
1、 PL/SQL語句塊
PL/SQL語句塊只適用於Oracle資料庫，使用時臨時儲存在客戶端，而不是儲存在資料庫。
基本語法:
Sql程式碼
declare
變數宣告、初始化
begin
業務處理、邏輯程式碼
exception
異常捕獲
end;
變數宣告：<變數名> <型別及長度> [:=<初始值>]
例：v_name varchar2(20):=’張三’;
2、 迴圈語句
loop迴圈語法：
Sql程式碼
loop
exit when 表示式
end loop;
while迴圈語法：
Sql程式碼
while 表示式
loop
end loop;
for迴圈語法:
Sql程式碼
for <變數> in <變數取值範圍（小值..大值，如1..100）> loop
end loop;
for迴圈的變數可不做宣告及初始化。
3、 if判斷語句
基本語法：
Sql程式碼
if <表示式> then
…
else if <表示式> then
…
else
…
end if;
end if;
例：
Sql程式碼
declare
v_identity number(4):=0;
begin
loop
if v_identity=1 then
dbms_output.put_line('v_identity=1');
else if v_identity=3 then
dbms_output.put_line('v_identity=3');
else if v_identity=6 then
exit;
else
dbms_output.put_line('v_identity is not 1 or 3');
end if;
end if;
end if; -- 注意，有多少個if就要有多少個end if結束標誌。
v_identity:=v_identity+1;
end loop;
exception
when others then dbms_output.put_line('error!');
end;
4、 分支case
基本語法：
Sql程式碼
case <變數>
when 常量 then
…
when 常量 then
…
else
…
end case;
例：
Sql程式碼
declare
v_number number(4):=3;
v_string varchar(20):='abc';
begin
case v_number
when 1 then
dbms_output.put_line('v_number is '||1);
when 2 then
dbms_output.put_line('v_number is '||2);
when 3 then
dbms_output.put_line('v_number is '||3);
end case;
case v_string
when 'ab' then
dbms_output.put_line('v_string is '||'ab');
when 'bc' then
dbms_output.put_line('v_string is '||'bc');
else -- 預設匹配
dbms_output.put_line('v_string is other value');
end case;
exception
when others then dbms_output.put_line('error!');
end;
5、 異常(exception)
宣告異常語法：<異常名> exception;
丟擲異常語法：raise <異常名>;
捕獲異常語法：when <異常名> then 異常處理語句;
例：
Sql程式碼
declare
v_input varchar2(1):='&throw';-- 動態輸入
v_exception_1 exception; -- 自定義異常
v_exception_2 exception;
others exception; -- 系統異常
begin
if v_input='1' then
raise v_exception_1; -- 丟擲異常
else if v_input='2' then
raise v_exception_2;
else
raise others;
end if;
end if;
exception
-- 捕獲異常
when v_exception_1 then dbms_output.put_line('throw exception: v_exception_1');
when v_exception_2 then dbms_output.put_line('throw exception: v_exception_2');
when others then dbms_output.put_line('throw exception: others');
end;
6、 遊標(cursor)
宣告遊標語法：cursor <遊標名> is select語句;
宣告ref遊標語法:<遊標名> is ref cursor;
開啟遊標語法：open <遊標名>;
移動遊標並獲取資料語法：fetch <遊標名> into <用於儲存讀取的資料的變數的名>;
關閉遊標語法:close <遊標名>;
遊標屬性（遊標的屬性必須在關閉遊標之前）：
%isopen: 判斷遊標是否開啟
%notfound: 找不到資料時
%found:
%rowcount: 返回當前遊標已掃描的資料行數量
遊標分類：1、顯示遊標（自定義遊標）；2、隱示遊標（系統遊標）；3、REF遊標
例：
Sql程式碼
declare
v_row test%rowtype; -- 匹配t_test表中一行所有的資料型別
cursor v_cur is
select * from test;-- 宣告遊標
begin
open v_cur;-- 開啟遊標
loop
fetch v_cur into v_row;-- 將遊標所在行的資料轉存到v_row中
exit when v_cur%notfound; -- 當遊標到最後一行時跳出
dbms_output.put_line('id = '||v_row.t_id||' name = '||v_row.t_name||' msg = '||v_row.t_msg);
end loop;
close v_cur;-- 關閉遊標
exception
when others then dbms_output.put_line('throw exception: others');
end;
-- REF遊標 --
Sql程式碼
create or replace package upk_select_test
as
type uc_test is ref cursor; -- 宣告ref遊標
end upk_select_test;
-- 儲存過程中呼叫ref遊標，並將查詢結果以遊標的方式返回
create or replace procedure up_select_test_2
(uc_result out upk_select_test.uc_test)
is
begin
open uc_result for select * from t_test;
end up_select_test_2;
7、 通配型別操作符
%type: 通配某行某列資料型別，如v_name t_test.t_name%type;通配表t_test中的t_name。
%rowtype: 通配一行所有列的資料型別，如 v_row t_test%rowtype;匹配t_test表中一行所有的資料型別。
8、 儲存過程(procedure)
基本語法：
Sql程式碼
create procedure <過程名>(<引數列表,無參時忽略>)
as|is
變數宣告、初始化
begin
業務處理、邏輯程式碼
exception
異常捕獲、容錯處理
end <過程名>;
引數:<引數名> in|out|in out <引數型別，無長度說明> ，如：v_name varchar2
in：入參
out：出參
in out：出入參
注：as|is表示as或is
呼叫語法：
1)、exec <過程名>;
2)、execute <過程名>;
3)、在PL/SQL語句塊中直接呼叫
例：
Sql程式碼
create or replace procedure up_wap(v_param1 in out varchar2,v_param2 in out varchar2)
is
v_temp varchar2(20);
begin
dbms_output.put_line('交換前引數1：'||v_param1||' 引數2：'||v_param2);
v_temp:=v_param1;
v_param1:=v_param2;
v_param2:=v_temp;
dbms_output.put_line('交換後引數1：'||v_param1||' 引數2：'||v_param2);
exception
when others then dbms_output.put_line('There is a error when the procedure up_wap executing!');
end up_wap;
-- 呼叫儲存過程
Sql程式碼
declare
v_param1 varchar2(20):='param1';
v_param2 varchar2(20):='param2';
begin
up_wap(v_param1 => v_param1,v_param2 => v_param2);
end;
9、 自定義函式(function)
基本語法：
Sql程式碼
create function <函式名>(<引數列表,無參時忽略>)
return <返回值型別，無長度說明>
as|is
變數宣告、初始化
begin
業務處理、邏輯程式碼
return <返回的值>;
exception
異常捕獲、容錯處理
end <函式名>;
引數：in 入參
注：只有入參的型別。
在儲存過程和自定義函式中的引數的傳遞（入參和出參）不能使用%type或%rowtype匹配，不能使用空值null，但是儲存過程可以返回空值。
例：
Sql程式碼
create function uf_select_name_by_id_test(v_id in number)
return varchar2
is
v_name t_test.t_name%type;
begin
select t_name into v_name from t_test where t_id=v_id;
return v_name;
exception
when others then
dbms_output.put_line('error');
end uf_select_name_by_id_test;
select uf_select_name_by_id_test(1) 姓名 from dual;-- select呼叫
declare --pl/sql語句塊呼叫
v_name varchar2(20);
begin
v_name:=uf_select_name_by_id_test(1);
dbms_output.put_line('name = '||v_name);
end;
10、包(package)
封裝，可以封裝過程(procedure)、函式(function)和變數。
注意，在包(package)中宣告的過程(procedure)和函式(function)必須在包的實現體（package body）中定義實現。
基本語法：
Sql程式碼
create package <包名>
as|is
變數宣告
儲存過程宣告
自定義函式宣告
end <包名>;
create package body <包名,與宣告部分一致>
as|is
儲存過程的程式碼實現
自定義函式的程式碼實現
end <包名>;
例：
Java程式碼
-- 建立包upk_hello
create or replace package upk_hello
is
v_hello_world varchar2(20):='hello world'; -- 宣告變數
procedure up_hello_world(v_name in varchar2);-- 宣告過程
function uf_hello_world(v_name in varchar2) return varchar2;-- 宣告函式
end upk_hello;
-- 實現包(upk_hello)裡宣告的方法
create or replace package body upk_hello
is
procedure up_hello_world(v_name in varchar2)
is
v_string varchar2(100);
begin
v_string:=v_name||' say hello world!';
dbms_output.put_line(v_string);
exception
when others then dbms_output.put_line('error');
end up_hello_world;
function uf_hello_world(v_name in varchar2) return varchar2
is
v_string varchar2(100);
begin
v_string:=v_name||' say hello world!';
return v_string;
exception
when others then dbms_output.put_line('error');
end uf_hello_world;
end upk_hello;
-- 包的呼叫
declare
v_msg varchar2(100);
begin
upk_hello.up_hello_world('bing');
v_msg:=upk_hello.uf_hello_world('admin');
dbms_output.put_line(v_msg);
dbms_output.put_line(upk_hello.v_hello_world);
end;
儲存過程中的3種迴圈：
1、
Sql程式碼
is
i int;
begin
i :=1;
loop
..
exit when i =10;
i :=i+1;
end loop;
2、
Sql程式碼
i :=1;
while i<=5 loop
..
i :=i+1;
end loop;
3、
Sql程式碼
for i in 1..100 loop
..........
end loop;
這是我以前的學習筆記，LZ湊合著看看吧，應該能看懂一些吧
===================================================
55 java跟oracle 呼叫（儲存過程，函式等）
55.1 Java呼叫無參的函式
1：函式為：
create or replace function MyF1 return varchar2 is
Result varchar2(20);
begin
dbms_output.put_line('now in My F1');
Result := 'Now MyF1 return';
return(Result);
end MyF1;
2：Java程式
/**
* 演示呼叫有一個沒有引數的函式
* @throws Exception
*/
private static void t1() throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "test", "test");
try {
CallableStatement stmt = conn
.prepareCall("{?=call MyF1()}");
stmt.registerOutParameter(1, Types.VARCHAR);
stmt.execute();
System.out.println(stmt.getString(1));
} finally {
conn.close();
}
}
55.2 Java呼叫無參但有返回值的儲存過程
1：儲存過程
create or replace procedure MyP1(str out Varchar2) is
begin
dbms_output.put_line('Hello Procedure.');
str :='Haha，Hello Procedure';
end MyP1;
2：程式
/**
* 如何呼叫無參但有返回值的儲存過程 測試的儲存過程
* @throws Exception
*/
private static void t2() throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "test", "test");
try {
CallableStatement stmt = conn.prepareCall("{call MyP1(?)}");
// 注意，這裡的stmt.getInt(1)中的數值1並非任意的，而是和儲存過程中的out列對應的，
// 如果out是在第一個位置，那就是 stmt.getInt(1)，如果是第三個位置，就是getInt.getInt(3)，
// 當然也可以同時有多個返回值，那就是再多加幾個out 引數了。
stmt.registerOutParameter(1, Types.VARCHAR);
stmt.execute();
System.out.println(stmt.getString(1));
} finally {
conn.close();
}
}
55.3 Java呼叫有參的（傳入）函式
1：函式
create or replace function MyF2(a number,b varchar2) return varchar2 is
Result varchar2(50);
begin
dbms_output.put_line('a==='||a||',b=='||b);
Result := a||b;
return(Result);
end MyF2;
2：程式
/**
* 呼叫有參的函式
* @throws Exception
*/
private static void t3() throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "test", "test");
try {
CallableStatement stmt = conn
.prepareCall("{?=call MyF2(?,?)}");
stmt.setInt(2, 15);
stmt.setString(3, "HelloF2");
stmt.registerOutParameter(1, Types.VARCHAR);
stmt.execute();
System.out.println(stmt.getString(1));
} finally {
conn.close();
}
}
55.4 Java呼叫有參的（傳入傳出）儲存過程
1：儲存過程
create or replace procedure MyP2(a in number,b in varchar2,c out varchar2) is
begin
dbms_output.put_line('a=='||a||',b=='||b);
c := 'ret=='||a||',b=='||b;
end MyP2;
2：程式
/**
* 呼叫有引數和返回值的儲存過程
* @throws Exception
*/
private static void t4() throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "test", "test");
try {
CallableStatement stmt = conn.prepareCall("{call MyP2(?,?,?)}");
stmt.setInt(1, 5);
stmt.setString(2, "測試");
stmt.registerOutParameter(3, Types.VARCHAR);
stmt.execute();
System.out.println(stmt.getString(3));
} finally {
conn.close();
}
}
55.5 Java向儲存過程傳入傳出物件的陣列
1：在資料中建立物件
create or replace type UserModel as object(
uuid varchar2(20),
name varchar2(20)
);
2：在資料庫中建立物件的集合型別
create or replace type userCol as table of UserModel;
create or replace type retUserCol as table of UserModel;
3：在資料庫中建立包
包頭：
create or replace package MyTestPackage is
TYPE dbRs IS REF CURSOR;
procedure MyP3(a1 in userCol,a2 out dbRs);
end MyTestPackage;
包體：
create or replace package body MyTestPackage is
procedure MyP3(a1 in userCol,a2 out dbRs) as
umCol retUserCol := retUserCol();
begin
for i in 1.. a1.count loop
insert into tbl_test values (a1(i).uuid,a1(i).name);
end loop;
commit;
umCol.Extend;
umCol(1):=UserModel('retUuid11','retName11');
umCol.Extend;
umCol(2):=UserModel('retUuid22','retName22');
open a2 for select * from table(cast(umCol as retUserCol));
end;
begin
null;
end MyTestPackage;
4：程式：
/**
* 測試向pl/sql傳入物件集合，從pl/sql返回任意的物件的集合
* @param list
* @throws Exception
*/
private static void t5(List list) throws Exception {
CallableStatement stmt = null;
Connection con = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "test", "test");
if (con != null) {
ARRAY aArray = getArray(con, "USERMODEL", "USERCOL", list);//該函式呼叫的第二三個引數必須大寫
stmt = con.prepareCall("{call MyTestPackage.MyP3(?,?)}");
((OracleCallableStatement) stmt).setARRAY(1, aArray);
stmt.registerOutParameter(2, OracleTypes.CURSOR);
stmt.execute();
ResultSet rs=(ResultSet)stmt.getObject(2);
while(rs.next()){
String uuid = rs.getString("uuid");
String name = rs.getString("name");
System.out.println("the uuid="+uuid+",name="+name);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private static ARRAY getArray(Connection con, String OracleObj, String Oraclelist,
List objlist) throws Exception {
ARRAY list = null;
if (objlist != null && objlist.size() > 0) {
StructDescriptor structdesc = new StructDescriptor(OracleObj, con);
STRUCT[] structs = new STRUCT[objlist.size()];
Object[] result = new Object[0];
for (int i = 0; i < objlist.size(); i++) {
result = new Object[2];//陣列大小應和你定義的資料庫物件(UserModel)的屬性的個數
result[0] = ((UserModel)(objlist.get(i))).getUuid(); //將list中元素的資料傳入result陣列
result[1] = ((UserModel)(objlist.get(i))).getName(); //
structs[i] = new STRUCT(structdesc, con, result);
}
ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist,
con);
list = new ARRAY(desc, con, structs);
}
return list;
}
如果使用Tomcat的DBCP的連線池，需要把連線進行轉換
public Connection getNativeConnection(Connection con) throws SQLException {
if (con instanceof DelegatingConnection) {
Connection nativeCon = ((DelegatingConnection) con).getInnermostDelegate();
return (nativeCon != null ? nativeCon : con.getMetaData().getConnection());
}
return con;
}
==============================
Oracle中table變數在JDBC中的運用
1.先定義一個返回陣列型別的方法
create or replace type my_table_type is table of varchar2(20);
create or replace function func
return my_table_type
is
i my_table_type:=my_table_type();
begin
select name bulk collect into i from emps;
return i;
end;
2.在JDBC中呼叫,如果返回的是table變數
public void SelectAgus(String sql)
{
OracleCallableStatement call = null;
try
{
call = (OracleCallableStatement) con.prepareCall(sql);
//如果返回的是table則用ARRAY型別,如果返回的是OBJECT的就用STRUCT
//第三個引數是定義table的型別名
call.registerOutParameter(1, OracleTypes.ARRAY,"MY_TABLE_TYPE");
call.execute();
//獲取第一個引數(這裡即返回值)
ARRAY array = call.getARRAY(1);
//獲取表中的元素
Datum[] dat = array.getOracleArray();
//遍歷依次列印
for(Datum d : dat)
{
System.out.println(new String(d.getBytes()));
}
}catch(Exception e)
{
e.printStackTrace();
}
}
2.如果定義的是巢狀表結構,
如下定義:
create or replace type all_table is object(id number,name varchar2(20));
create or replace type emps_table_type is table of all_table;
--建立一個函式,返回型別為emps_table_type;
create or replace function funcc
return emps_table_type
is
i emps_table_type;
begin
--把emps中的ID,NAME屬性值全部讀取到i中
select all_table(id,name) bulk collect into i from emps;
return i;--返回table
end;
public void SelectAgus(String sql)
{
OracleCallableStatement call = null;
try
{
call = (OracleCallableStatement) con.prepareCall(sql);
call.registerOutParameter(1, OracleTypes.ARRAY,"EMPS_TABLE_TYPE");
call.execute();
ARRAY array = call.getARRAY(1);
Datum[] dat = array.getOracleArray();
for(Datum d : dat)
{ //獲取了行後,要獲取一行中的元素
STRUCT struct = (STRUCT)d;
//這裡有可能會出現亂碼,所以我分別用了兩種方式獲取元素
Datum[] d1 = struct.getOracleAttributes();
Object[] d2 = struct.getAttributes();
System.out.println("ID="+d2[0]+" "+"NAME="+
new String(d1[1].getBytes()));
}
}catch(Exception e){
e.printStackTrace();
}
}
oracle 在一個儲存過程中呼叫另一個返回遊標的儲存過程
實際專案當中經常需要在一個儲存過程中呼叫另一個儲存過程返回的遊標，本文列舉了兩種情況講述具體的操作方法。
第一種情況是返回的遊標是某個具體的表或檢視的資料，如：
CREATE OR REPLACE PROCEDURE P_TESTA (
PRESULT OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN PRESULT FOR SELECT * FROM USERS;
END P_TESTA;
其中USERS就是資料庫中一個表。在呼叫的時候只要宣告一個該表的ROWTYPE型別就可以了：
CREATE OR REPLACE PROCEDURE P_TESTB
AS
VARCURSOR SYS_REFCURSOR;
R USERS%ROWTYPE;
BEGIN
P_TESTA(VARCURSOR);
LOOP
FETCH VARCURSOR INTO R;
EXIT WHEN VARCURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(R.NAME);
END LOOP;
END P_TESTB;
第二種情況，我們返回的不是表的所有的列，或許只是其中一列或兩列，如：
CREATE OR REPLACE PROCEDURE P_TESTA (
PRESULT OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN PRESULT FOR SELECT ID,NAME FROM USERS;
END P_TESTA;
這裡我們只返回了USERS表的ID,NAME這兩個列，那麼呼叫的時候也必須做相應的修改：
CREATE OR REPLACE PROCEDURE P_TESTB
AS
VARCURSOR SYS_REFCURSOR;
CURSOR TMPCURSOR IS SELECT ID,NAME FROM USERS WHERE ROWNUM=1;
R TMPCURSOR%ROWTYPE;
BEGIN
P_TESTA(VARCURSOR);
LOOP
FETCH VARCURSOR INTO R;
EXIT WHEN VARCURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(R.ID);
END LOOP;
END P_TESTB;
與之前不同的是我們宣告瞭一個遊標型別的變數TMPCURSOR ，注意TMPCURSOR 的結構必須與儲存過程P_TESTA 返回的遊標結構一致，否則就會出現錯誤。同理只要保持兩個遊標型別結構一致，就可以實現自由呼叫。
create table test(id int,name varchar(10))
insert into test select 1,'AAAA'
insert into test select 2,'BBBB'
go
create procedure sp_test1(@count int output)
as
select @count=count(*) from test
go
create procedure sp_test2
as
begin
declare @count int
exec sp_test1 @count output
select @count
end
go
exec sp_test2
go
--輸出結果
/*
2
*/
drop procedure sp_test2,sp_test1
drop table test
go
oracle procedure 和function 的區別有哪些？
procedure 可多個返回引數，也就是out型別
function就一個
就這點區別
我覺得看使用的地方，如果只要執行一段sql的語句段，兩個都行，如過想有返回值，一個的話用function，多個的話procedure。
procedure是儲存過程 相當於程式語言裡面一個處理業務的方法 也可以返回值
function是方法 相當於程式語言裡面返回一個值的方法 一般較簡單 可以在dml語句中用這個方法加引數增刪改查
package相當於程式裡面一個介面 裡面可以定義常量陣列bean 多個procedure和多個function的空實現
package body相當於程式裡面一個類 是對應實現介面package的
迴圈：
1、..
is
i int;
begin
i :=1;
loop
..
exit when i =10;
i :=i+1;
end loop;
2、
i :=1;
while i<=5 loop
..
i :=i+1;
end loop;
3、
--如果指定了reverse選項，則迴圈控制變數會自動減1，否則自動加1
for j in reverse 1..10 loop
..
end loop;
1.基本結構
CREATE OR REPLACE PROCEDURE 儲存過程名字
(
引數1 IN NUMBER,
引數2 IN NUMBER
) IS
變數1 INTEGER :=0;
變數2 DATE;
BEGIN
END 儲存過程名字
2.SELECT INTO STATEMENT
將select查詢的結果存入到變數中，可以同時將多個列儲存多個變數中，必須有一條
記錄，否則丟擲異常(如果沒有記錄丟擲NO_DATA_FOUND)
例子：
BEGIN
SELECT col1,col2 into 變數1,變數2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;
...
3.IF 判斷
IF V_TEST=1 THEN
BEGIN
do something
END;
END IF;
4.while 迴圈
WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
END LOOP;
5.變數賦值
V_TEST := 123;
6.用for in 使用cursor
...
IS
CURSOR cur IS SELECT * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM :=cur_result.列名1 cur_result.列名2
END;
END LOOP;
END;
7.帶引數的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(變數值);
LOOP
FETCH C_USER INTO V_NAME;
EXIT FETCH C_USER%NOTFOUND;
do something
END LOOP;
CLOSE C_USER;
8.用pl/sql developer debug
連線資料庫後建立一個Test WINDOW
在視窗輸入呼叫SP的程式碼,F9開始debug,CTRL N單步除錯
CREATE OR REPLACE PROCEDURE 儲存過程名字
(
引數1 IN NUMBER,
引數2 IN NUMBER
) IS
變數1 INTEGER :=0;
變數2 DATE;
BEGIN
END 儲存過程名字
2.SELECT INTO STATEMENT
將select查詢的結果存入到變數中，可以同時將多個列儲存多個變數中，必須有一條
記錄，否則丟擲異常(如果沒有記錄丟擲NO_DATA_FOUND)
例子：
BEGIN
SELECT col1,col2 into 變數1,變數2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;
...
3.IF 判斷
IF V_TEST=1 THEN
BEGIN
do something
END;
END IF;
4.while 迴圈
WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
END LOOP;
5.變數賦值
V_TEST := 123;
6.用for in 使用cursor
...
IS
CURSOR cur IS SELECT * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM :=cur_result.列名1+cur_result.列名2
END;
END LOOP;
END;
7.帶引數的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(變數值);
LOOP
FETCH C_USER INTO V_NAME;
EXIT FETCH C_USER%NOTFOUND;
do something
END LOOP;
CLOSE C_USER;
8.用pl/sql developer debug
連線資料庫後建立一個Test WINDOW
在視窗輸入呼叫SP的程式碼,F9開始debug,CTRL+N單步除錯
簡單例項，通過DBMS_OUTPUT來看結果
CREATE OR REPLACE PROCEDURE bb (lic_para IN VARCHAR2,out_para OUT VARCHAR2)
AS
temp VARCHAR2 (100);
BEGIN
SELECT lic_no
INTO temp
FROM t_vehicle_info
WHERE lic_no = lic_para;
out_para:=temp;
DBMS_OUTPUT.put_line (out_para);
END bb;
下面是呼叫：
begin
-- Call the procedure
bb(lic_para => :lic_para,
out_para => :out_para);
end;
可以在命令列裡敲sqlplus “yonghuming/mima@dbname”接著除錯儲存過程。但是最好用的是在pl/sql Developer工具裡面用點儲存過程裡的TEST來呼叫,它能自動生成呼叫的語句並有欄目讓你輸入引數值，包括輸入引數和輸出引數，並把結果返回到輸出引數裡面，在結果欄可見，這點pl/sql Developer比TOAD要強，TOAD在儲存過程上右鍵後點選EXECUTE Procedure也可以執行，只是結果在那看我不曉得，而在pl/sql Developer按F9可以除錯，ctrl+N可以單步跟蹤，的確爽。
Oracle儲存過程基本語法 儲存過程
1 CREATE OR REPLACE PROCEDURE 儲存過程名
2 IS
3 BEGIN
4 NULL;
5 END;
行1:
CREATE OR REPLACE PROCEDURE 是一個SQL語句通知Oracle資料庫去建立一個叫做skeleton儲存過程, 如果存在就覆蓋它;
行2:
IS關鍵詞表明後面將跟隨一個PL/SQL體。
行3:
BEGIN關鍵詞表明PL/SQL體的開始。
行4:
NULL PL/SQL語句表明什麼事都不做，這句不能刪去，因為PL/SQL體中至少需要有一句;
行5:
END關鍵詞表明PL/SQL體的結束
儲存過程建立語法：
create or replace procedure 儲存過程名（param1 in type，param2 out type）
as
變數1 型別（值範圍）; --vs_msg VARCHAR2(4000);
變數2 型別（值範圍）;
Begin
Select count(*) into 變數1 from 表A where列名=param1；
If (判斷條件) then
Select 列名 into 變數2 from 表A where列名=param1；
Dbms_output。Put_line(‘列印資訊');
Elsif (判斷條件) then
Dbms_output。Put_line(‘列印資訊');
Else
Raise 異常名（NO_DATA_FOUND）;
End if;
Exception
When others then
Rollback;
End;
注意事項：
1， 儲存過程引數不帶取值範圍，in表示傳入，out表示輸出
型別可以使用任意Oracle中的合法型別。
2， 變數帶取值範圍，後面接分號
3， 在判斷語句前最好先用count（*）函式判斷是否存在該條操作記錄
4， 用select 。。。into。。。給變數賦值
5， 在程式碼中拋異常用 raise+異常名
CREATE OR REPLACE PROCEDURE儲存過程名
(
--定義引數
is_ym IN CHAR(6) ,
the_count OUT NUMBER,
)
AS
--定義變數
vs_msg VARCHAR2(4000); --錯誤資訊變數
vs_ym_beg CHAR(6); --起始月份
vs_ym_end CHAR(6); --終止月份
vs_ym_sn_beg CHAR(6); --同期起始月份
vs_ym_sn_end CHAR(6); --同期終止月份
--定義遊標(簡單的說就是一個可以遍歷的結果集)
CURSOR cur_1 IS
SELECT 。。。
FROM 。。。
WHERE 。。。
GROUP BY 。。。;
BEGIN
--用輸入引數給變數賦初值，用到了Oralce的SUBSTR TO_CHAR ADD_MONTHS
TO_DATE 等很常用的函式。
vs_ym_beg := SUBSTR(is_ym,1,6);
vs_ym_end := SUBSTR(is_ym,7,6);
vs_ym_sn_beg := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,'yyyymm'), -12),'yyyymm');
vs_ym_sn_end := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,'yyyymm'), -12),'yyyymm');
--先刪除表中特定條件的資料。
DELETE FROM 表名 WHERE ym = is_ym;
--然後用內建的DBMS_OUTPUT物件的put_line方法列印出影響的記錄行數，其中用到一個系統變數SQL%rowcount
DBMS_OUTPUT.put_line('del上月記錄='||SQL%rowcount||'條');
INSERT INTO表名(area_code,ym,CMCODE,rmb_amt,usd_amt)
SELECT area_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000
FROM BGD_AREA_CM_M_BASE_T
WHERE ym >= vs_ym_beg
AND ym <= vs_ym_end
GROUP BY area_code,CMCODE;
DBMS_OUTPUT.put_line('ins當月記錄='||SQL%rowcount||'條');
--遍歷遊標處理後更新到表。遍歷遊標有幾種方法，用for語句是其中比較直觀的一種。
FOR rec IN cur_1 LOOP
UPDATE 表名
SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn
WHERE area_code = rec.area_code
AND CMCODE = rec.CMCODE
AND ym = is_ym;
END LOOP;
COMMIT;
--錯誤處理部分。OTHERS表示除了宣告外的任意錯誤。SQLERRM是系統內建變數儲存了當前錯誤的詳細資訊。
EXCEPTION
WHEN OTHERS THEN
vs_msg := 'ERROR IN xxxxxxxxxxx_p('||is_ym||'):'||SUBSTR(SQLERRM,1,500);
ROLLBACK;
--把當前錯誤記錄進日誌表。
INSERT INTO LOG_INFO(proc_name,error_info,op_date)
VALUES('xxxxxxxxxxx_p',vs_msg,SYSDATE);
COMMIT;
RETURN;
END;
oracle儲存過程語法
1 、判斷語句:
if 比較式 then begin end; end if;
create or replace procedure test(x in number) is
begin
if x >0 then
begin
x := 0 - x;
end;
end if;
if x = 0 then
begin
x: = 1;
end;
end if;
end test;
2 、For 迴圈
For ... in ... LOOP
-- 執行語句
end LOOP;
(1) 迴圈遍歷遊標
create or replace procedure test() as
Cursor cursor is select name from student; name varchar(20);
begin
for name in cursor LOOP
begin
dbms_output.putline(name);
end;
end LOOP;
end test;
(2) 迴圈遍歷陣列
create or replace procedure test(varArray in myPackage.TestArray) as
--( 輸入引數varArray 是自定義的陣列型別，定義方式見標題6)
i number;
begin
i := 1; -- 儲存過程陣列是起始位置是從1 開始的，與java 、C 、C++ 等語言不同。因為在Oracle 中本是沒有陣列的概念的，陣列其實就是一張
-- 表(Table), 每個陣列元素就是表中的一個記錄，所以遍歷陣列時就相當於從表中的第一條記錄開始遍歷
for i in 1..varArray.count LOOP
dbms_output.putline('The No.'|| i || 'record in varArray is:'||varArray(i));
end LOOP;
end test;
3 、While 迴圈
while 條件語句 LOOP
begin
end;
end LOOP;
E.g
create or replace procedure test(i in number) as
begin
while i < 10 LOOP
begin
i:= i + 1;
end;
end LOOP;
end test;
4 、陣列
首先明確一個概念：Oracle 中本是沒有陣列的概念的，陣列其實就是一張表(Table), 每個陣列元素就是表中的一個記錄。
使用陣列時，使用者可以使用Oracle 已經定義好的陣列型別，或可根據自己的需要定義陣列型別。
(1) 使用Oracle 自帶的陣列型別
x array; -- 使用時需要需要進行初始化
e.g:
create or replace procedure test(y out array) is
x array;
begin
x := new array();
y := x;
end test;
(2) 自定義的陣列型別 ( 自定義資料型別時，建議通過建立Package 的方式實現，以便於管理)
create or replace package myPackage is
Public type declarations type info is record( name varchar(20), y number);
type TestArray is table of info index by binary_integer;
-- 此處宣告瞭一個TestArray 的型別資料，其實其為一張儲存Info 資料型別的Table 而已，及TestArray 就是一張表，有兩個欄位，一個是name ，一個是y 。需要注意的是此處使用了Index by binary_integer 編制該Table 的索引項，也可以不寫，直接寫成：type TestArray is
table of info ，如果不寫的話使用陣列時就需要進行初始化：varArray myPackage.TestArray; varArray := new myPackage.TestArray();
end TestArray;
5. 遊標的使用 Oracle 中Cursor 是非常有用的，用於遍歷臨時表中的查詢結果。其相關方法和屬性也很多，現僅就常用的用法做一二介紹：
(1)Cursor 型遊標( 不能用於引數傳遞)
create or replace procedure test() is
cusor_1 Cursor is select std_name from student where ...; --Cursor 的使用方式1 cursor_2 Cursor;
begin
select class_name into cursor_2 from class where ...; --Cursor 的使用方式2
可使用For x in cursor LOOP .... end LOOP; 來實現對Cursor 的遍歷
end test;
(2)SYS_REFCURSOR 型遊標，該遊標是Oracle 以預先定義的遊標，可作出引數進行傳遞
create or replace procedure test(rsCursor out SYS_REFCURSOR) is
cursor SYS_REFCURSOR;
name varhcar(20);
begin
OPEN cursor FOR select name from student where ... --SYS_REFCURSOR 只能通過OPEN 方法來開啟和賦值
LOOP
fetch cursor into name --SYS_REFCURSOR 只能通過fetch into 來開啟和遍歷 exit when cursor%NOTFOUND; --SYS_REFCURSOR 中可使用三個狀態屬性： ---%NOTFOUND( 未找到記錄資訊) %FOUND( 找到記錄資訊) ---%ROWCOUNT( 然後當前遊標所指向的行位置)
dbms_output.putline(name);
end LOOP;
rsCursor := cursor;
end test;
例項
下面寫一個簡單的例子來對以上所說的儲存過程的用法做一個應用：
現假設存在兩張表，一張是學生成績表(studnet) ，欄位為：stdId,math,article,language,music,sport,total,average,step
一張是學生課外成績表(out_school), 欄位為:stdId,parctice,comment
通過儲存過程自動計算出每位學生的總成績和平均成績，同時，如果學生在課外課程中獲得的評價為A ，就在總成績上加20 分。
create or replace procedure autocomputer(step in number) is
rsCursor SYS_REFCURSOR;
commentArray myPackage.myArray;
math number;
article number;
language number;
music number;
sport number;
total number;
average number;
stdId varchar(30);
record myPackage.stdInfo;
i number;
begin
i := 1;
get_comment(commentArray); -- 呼叫名為get_comment() 的儲存過程獲取學生課外評分資訊
OPEN rsCursor for select stdId,math,article,language,music,sport from student t where t.step = step;
LOOP
fetch rsCursor into stdId,math,article,language,music,sport; exit when rsCursor%NOTFOUND;
total := math + article + language + music + sport;
for i in 1..commentArray.count LOOP
record := commentArray(i);
if stdId = record.stdId then
begin
if record.comment = 'A' then
begin
total := total + 20;
go to next; -- 使用go to 跳出for 迴圈
end;
end if;
end;
end if;
end LOOP;
<<continue>> average := total / 5;
update student t set t.total=total and t.average = average where t.stdId = stdId;
end LOOP;
end;
end autocomputer;
-- 取得學生評論資訊的儲存過程
create or replace procedure get_comment(commentArray out myPackage.myArray) is
rs SYS_REFCURSOR ；
record myPackage.stdInfo;
stdId varchar(30);
comment varchar(1);
i number;
begin
open rs for select stdId,comment from out_school
i := 1;
LOOP
fetch rs into stdId,comment; exit when rs%NOTFOUND;
record.stdId := stdId;
record.comment := comment;
recommentArray(i) := record;
i:=i + 1;
end LOOP;
end get_comment;
-- 定義陣列型別myArray
create or replace package myPackage is begin
type stdInfo is record(stdId varchar(30),comment varchar(1));
type myArray is table of stdInfo index by binary_integer;
end myPackage;
的朋友可以參考下
1.分頁類
package org.zh.basic;
/**
* 頁面類
*
* @author keven
*
*/
public class PageInfo {
// 定義
private String p_tableName; // -表名
private String p_strWhere; // --查詢條件
private String p_orderColumn; // --排序的列
private String p_orderStyle; // --排序方式
private int p_curPage; // --當前頁
private int p_pageSize; // --每頁顯示記錄條數
private int p_totalRecords; // --總記錄數
private int p_totalPages; // --總頁數
// / <summary>
// / 定義函式
// / </summary>
public PageInfo() {
}
public PageInfo(String p_tableName, String p_strWhere,
String p_orderColumn, String p_orderStyle, int p_curPage,
int p_pageSize, int p_totalRecords, int p_totalPages) {
this.p_tableName = p_tableName;
this.p_strWhere = p_strWhere;
this.p_orderColumn = p_orderColumn;
this.p_orderStyle = p_orderStyle;
this.p_curPage = p_curPage;
this.p_pageSize = p_pageSize;
this.p_totalRecords = p_totalRecords;
this.p_totalPages = p_totalPages;
}
public String getP_tableName() {
return p_tableName;
}
public void setP_tableName(String pTableName) {
p_tableName = pTableName;
}
public String getP_strWhere() {
return p_strWhere;
}
public void setP_strWhere(String pStrWhere) {
p_strWhere = pStrWhere;
}
public String getP_orderColumn() {
return p_orderColumn;
}
public void setP_orderColumn(String pOrderColumn) {
p_orderColumn = pOrderColumn;
}
public String getP_orderStyle() {
return p_orderStyle;
}
public void setP_orderStyle(String pOrderStyle) {
p_orderStyle = pOrderStyle;
}
public int getP_curPage() {
return p_curPage;
}
public void setP_curPage(int pCurPage) {
p_curPage = pCurPage;
}
public int getP_pageSize() {
return p_pageSize;
}
public void setP_pageSize(int pPageSize) {
p_pageSize = pPageSize;
}
public int getP_totalRecords() {
return p_totalRecords;
}
public void setP_totalRecords(int pTotalRecords) {
p_totalRecords = pTotalRecords;
}
public int getP_totalPages() {
return p_totalPages;
}
public void setP_totalPages(int pTotalPages) {
p_totalPages = pTotalPages;
}
}
2 呼叫
package org.zh.sys.server;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Map;
import java.util.HashMap;
import org.hibernate.Session;
import org.zh.basic.PageInfo;
import org.zh.dao.HibernateSessionFactory;
import oracle.jdbc.driver.OracleTypes;
import oracle.jdbc.OracleCallableStatement;
public class GeneratePage {
public GeneratePage() {
}
public static ArrayList Prc_Page(PageInfo page) {
ArrayList list = new ArrayList();
Map mp;
Session s = null;
Connection conn = null;
ResultSet rs = null;
CallableStatement proc = null;
try {
s = HibernateSessionFactory.getSession();
conn = s.connection();
proc = conn.prepareCall("{call pages.prc_page(?,?,?,?,?,?,?,?,?)}");
proc.setString(1, page.getP_tableName());
proc.setString(2, page.getP_strWhere());
proc.setString(3, page.getP_orderColumn());
proc.setString(4, page.getP_orderStyle());
proc.setInt(5, page.getP_curPage());
proc.setInt(6, page.getP_pageSize());
proc.registerOutParameter(7, OracleTypes.NUMBER);
proc.registerOutParameter(8, OracleTypes.NUMBER);
proc.registerOutParameter(9, OracleTypes.CURSOR);
proc.execute();
// page.setP_totalRecords(proc.getInt("p_totalRecords"));
// page.setP_totalPages(proc.getInt("p_totalPages"));
// list = (ArrayList) proc.getObject("v_cur");
page.setP_totalRecords(proc.getInt(7));
page.setP_totalPages(proc.getInt(8));
rs = ((OracleCallableStatement) proc).getCursor(9); // 得到輸出結果集引數
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
while (rs.next()) {
mp = new HashMap(numberOfColumns);
for (int r = 1; r < numberOfColumns; r++) {
mp.put(rsmd.getColumnName(r), rs.getObject(r));
}
list.add(mp);
}
return list;
} catch (SQLException ex) {
ex.printStackTrace();
return list;
} catch (Exception ex2) {
ex2.printStackTrace();
return list;
} finally {
try {
if (proc != null) {
proc.close();
}
if (rs != null) {
rs.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException ex1) {
ex1.printStackTrace();
}
}
}
}
1、 PL/SQL語句塊
PL/SQL語句塊只適用於Oracle資料庫，使用時臨時儲存在客戶端，而不是儲存在資料庫。
基本語法:
declare
變數宣告、初始化
begin
業務處理、邏輯程式碼
exception
異常捕獲
end;
變數宣告：<變數名> <型別及長度> [:=<初始值>]
例：v_name varchar2(20):=’張三’;
例：見第3節
2、 迴圈語句
loop迴圈語法：
loop
exit when 表示式
end loop;
while迴圈語法：
while 表示式 loop
end loop;
for迴圈語法:
for <變數> in <變數取值範圍（小值..大值，如1..100）> loop
end loop;
for迴圈的變數可不做宣告及初始化。
例：見第3節
3、 if判斷語句
基本語法：
if <表示式> then
…
else if <表示式> then
…
else
…
end if;
end if;
例：
declare
v_identity number(4):=0;
begin
loop
if v_identity=1then
dbms_output.put_line('v_identity=1');
elseif v_identity=3then
dbms_output.put_line('v_identity=3');
elseif v_identity=6then
exit;
else
dbms_output.put_line('v_identity is not 1 or 3');
endif;
endif;
endif;-- 注意，有多少個if就要有多少個end if結束標誌。
v_identity:=v_identity+1;
endloop;
exception
whenothersthen dbms_output.put_line('error!');
end;
/
4、 分支case
基本語法：
case <變數>
when 常量 then
…
when 常量 then
…
else
…
end case;
例：
declare
v_number number(4):=3;
v_string varchar(20):='abc';
begin
case v_number
when1then
dbms_output.put_line('v_number is '||1);
when2then
dbms_output.put_line('v_number is '||2);
when3then
dbms_output.put_line('v_number is '||3);
endcase;
case v_string
when'ab'then
dbms_output.put_line('v_string is '||'ab');
when'bc'then
dbms_output.put_line('v_string is '||'bc');
else--預設匹配
dbms_output.put_line('v_string is other value');
endcase;
exception
whenothersthen dbms_output.put_line('error!');
end;
/
5、 異常(exception)
宣告異常語法：<異常名> exception;
丟擲異常語法：raise <異常名>;
捕獲異常語法：when <異常名> then 異常處理語句;
例：
declare
v_input varchar2(1):='&throw';--動態輸入
v_exception_1 exception; --自定義異常
v_exception_2 exception;
othersexception;-- 系統異常
begin
if v_input='1'then
raise v_exception_1;-- 丟擲異常
elseif v_input='2'then
raise v_exception_2;
else
raiseothers;
endif;
endif;
exception
--捕獲異常
when v_exception_1then dbms_output.put_line('throw exception: v_exception_1');
when v_exception_2then dbms_output.put_line('throw exception: v_exception_2');
whenothersthen dbms_output.put_line('throw exception: others');
end;
/
6、 遊標(cursor)
宣告遊標語法：cursor <遊標名> is select語句;
宣告ref遊標語法:<遊標名> is ref cursor;
開啟遊標語法：open <遊標名>;
移動遊標並獲取資料語法：fetch <遊標名> into <用於儲存讀取的資料的變數的名>;
關閉遊標語法:close <遊標名>;
遊標屬性（遊標的屬性必須在關閉遊標之前）：
%isopen: 判斷遊標是否開啟
%notfound: 找不到資料時
%found:
%rowcount: 返回當前遊標已掃描的資料行數量
遊標分類：1、顯示遊標（自定義遊標）；2、隱示遊標（系統遊標）；3、REF遊標
例：
declare
v_row t_test%rowtype;-- 匹配t_test表中一行所有的資料型別
cursor v_curisselect *from t_test;--宣告遊標
begin
open v_cur;--開啟遊標
loop
fetch v_curinto v_row;--將遊標所在行的資料轉存到v_row中
exitwhen v_cur%notfound;-- 當遊標到最後一行時跳出
dbms_output.put_line('id = '||v_row.t_id||' name = '||v_row.t_name||' msg = '||v_row.t_msg);
endloop;
close v_cur;--關閉遊標
exception
whenothersthen dbms_output.put_line('throw exception: others');
end;
/
-- REF遊標 --
createorreplacepackage upk_select_test
astype uc_testisrefcursor;-- 宣告ref遊標
end upk_select_test;
/
-- 儲存過程中呼叫ref遊標，並將查詢結果以遊標的方式返回
createorreplaceprocedure up_select_test_2
(uc_result out upk_select_test.uc_test)
is
begin
open uc_resultforselect *from t_test;
end up_select_test_2;
/
7、 通配型別操作符
%type: 通配某行某列資料型別，如v_name t_test.t_name%type;通配表t_test中的t_name。
%rowtype: 通配一行所有列的資料型別，如 v_row t_test%rowtype;匹配t_test表中一行
所有的資料型別。
8、 儲存過程(procedure)
基本語法：
create procedure <過程名>(<引數列表,無參時忽略>)
as|is
變數宣告、初始化
begin
業務處理、邏輯程式碼
exception
異常捕獲、容錯處理
end <過程名>;
引數:<引數名> in|out|in out <引數型別，無長度說明> ，如：v_name varchar2
in：入參
out：出參
in out：出入參
注：as|is表示as或is
呼叫語法：
1)、exec <過程名>;
2)、execute <過程名>;
3)、在PL/SQL語句塊中直接呼叫。
例：
createorreplaceprocedure up_wap(v_param1 inoutvarchar2,v_param2inoutvarchar2)
is
v_temp varchar2(20);
begin
dbms_output.put_line('交換前引數1：'||v_param1||' 引數2：'||v_param2);
v_temp:=v_param1;
v_param1:=v_param2;
v_param2:=v_temp;
dbms_output.put_line('交換後引數1：'||v_param1||' 引數2：'||v_param2);
exception
whenothersthen dbms_output.put_line('There is a error when the procedure up_wap executing!');
end up_wap;
/
-- 呼叫儲存過程
declare
v_param1 varchar2(20):='param1';
v_param2 varchar2(20):='param2';
begin
up_wap(v_param1 => v_param1,v_param2 => v_param2);
end;
/
9、 自定義函式(function)
基本語法：
create function <函式名>(<引數列表,無參時忽略>)
return <返回值型別，無長度說明>
as|is
變數宣告、初始化
begin
業務處理、邏輯程式碼
return <返回的值>;
exception
異常捕獲、容錯處理
end <函式名>;
引數：in 入參
注：只有入參的型別。
在儲存過程和自定義函式中的引數的傳遞（入參和出參）不能使用%type或%rowtype匹配，不能使用空值null，但是儲存過程可以返回空值。
例：
createfunction uf_select_name_by_id_test(v_idinnumber)
returnvarchar2
is
v_name t_test.t_name%type;
begin
select t_nameinto v_namefrom t_testwhere t_id=v_id;
return v_name;
exception
whenothersthen dbms_output.put_line('error');
end uf_select_name_by_id_test;
/
select uf_select_name_by_id_test(1)姓名from dual;-- select呼叫
declare--pl/sql語句塊呼叫
v_name varchar2(20);
begin
v_name:=uf_select_name_by_id_test(1);
dbms_output.put_line('name = '||v_name);
end;
/
10、包(package)
封裝，可以封裝過程(procedure)、函式(function)和變數。
注意，在包(package)中宣告的過程(procedure)和函式(function)必須在包的實現體
（package body）中定義實現。
基本語法：
create package <包名>
as|is
變數宣告
儲存過程宣告
自定義函式宣告
end <包名>;
/
create package <包名,與宣告部分一致>
as|is
儲存過程的程式碼實現
自定義函式的程式碼實現
end <包名>;
/
例：
-- 建立包upk_hello
createorreplacepackage upk_hello
is
v_hello_world varchar2(20):='hello world';-- 宣告變數
procedure up_hello_world(v_nameinvarchar2);--宣告過程
function uf_hello_world(v_nameinvarchar2)returnvarchar2;--宣告函式
end upk_hello;
/
-- 實現包(upk_hello)裡宣告的方法
createorreplacepackagebody upk_hello
is
procedure up_hello_world(v_nameinvarchar2)
is
v_string varchar2(100);
begin
v_string:=v_name||' say hello world!';
dbms_output.put_line(v_string);
exception
whenothersthen dbms_output.put_line('error');
end up_hello_world;
function uf_hello_world(v_nameinvarchar2)returnvarchar2
is
v_string varchar2(100);
begin
v_string:=v_name||' say hello world!';
return v_string;
exception
whenothersthen dbms_output.put_line('error');
end uf_hello_world;
end upk_hello;
/
-- 包的呼叫
declare
v_msg varchar2(100);
begin
upk_hello.up_hello_world('bing');
v_msg:=upk_hello.uf_hello_world('admin');
dbms_output.put_line(v_msg);
dbms_output.put_line(upk_hello.v_hello_world);
end;
1.基本結構
CREATE OR REPLACE PROCEDURE Oracle 儲存過程名字
(
引數1 IN NUMBER,
引數2 IN NUMBER
) IS
變數1 INTEGER :=0;
變數2 DATE;
BEGIN
END 儲存過程名字
2.SELECT INTO STATEMENT
將select查詢的結果存入到變數中，可以同時將多個列儲存多個變數中，必須有一條
記錄，否則丟擲異常(如果沒有記錄丟擲NO_DATA_FOUND)
例子：
BEGIN
SELECT col1,col2 into 變數1,變數2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;
...
3.IF 判斷
IF V_TEST=1 THEN
BEGIN
do something
END;
END IF;
4.while 迴圈
WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
END LOOP;
5.變數賦值
V_TEST := 123;
6.用for in 使用cursor
...
IS
CURSOR cur IS SELECT * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM :=cur_result.列名1+cur_result.列名2
END;
END LOOP;
END;
7.帶引數的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(變數值);
LOOP
FETCH C_USER INTO V_NAME;
EXIT FETCH C_USER%NOTFOUND;
do something
END LOOP;
CLOSE C_USER;
8.用pl/sql developer debug
連線資料庫後建立一個Test WINDOW
在視窗輸入呼叫SP的程式碼,F9開始debug,CTRL+N單步除錯
關於Oracle 儲存過程的若干問題備忘
1.在oracle中，資料表別名不能加as，如：
select a.appname from appinfo a;-- 正確
select a.appname from appinfo as a;-- 錯誤
也許，是怕和Oracle中的儲存過程中的關鍵字as衝突的問題吧
2.在Oracle儲存過程中，select某一欄位時，後面必須緊跟into，如果select整個記錄，利用遊標的話就另當別論了。
select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 有into，正確編譯
select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 沒有into，編譯報錯，提示：Compilation
Error: PLS-00428: an INTO clause is expected in this SELECT statement
3.在利用select...into...語法時，必須先確保資料庫中有該條記錄，否則會報出"no data found"異常。
可以在該語法之前，先利用select count(*) from 檢視資料庫中是否存在該記錄，如果存在，再利用select...into...
4.在儲存過程中，別名不能和欄位名稱相同，否則雖然編譯可以通過，但在執行階段會報錯
select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;-- 正確執行
select af.keynode into kn from APPFOUNDATION af where af.appid=appid and af.foundationid=foundationid;-- 執行階段報錯，提示
ORA-01422:exact fetch returns more than requested number of rows
5.在Oracle儲存過程中，關於出現null的問題
假設有一個表A，定義如下：
create table A(
id varchar2(50) primary key not null,
vcount number(8) not null,
bid varchar2(50) not null -- 外來鍵
);
如果在儲存過程中，使用如下語句：
select sum(vcount) into fcount from A where bid='xxxxxx';
如果A表中不存在bid="xxxxxx"的記錄，則fcount=null(即使fcount定義時設定了預設值，如：fcount number(8):=0依然無效，fcount還是會變成null)，這樣以後使用fcount時就可能有問題，所以在這裡最好先判斷一下：
if fcount is null then
fcount:=0;
end if;
Qracle PL/SQL
--儲存過程
1.PL/SQL
SQL是資料庫普通話，每個資料庫在實現SQL國際標準之外，也有自己特有的語句。（Hibernate dialect）
通訊的標準：ODBC(ado , ado.net)，JDBC(jdo , Hibernate)
ODBC、JDBC是最有效率的，但是開發繁瑣，才有後來括號中的高度函式化的擴充
PL/SQL:Procudural Language Extension to SQL.
在SQL語句基礎上，加上了結構化流程控制，可用多條SQL語句完成一個功能。
PL/SQL功能強大，經常用來書寫複雜的業務邏輯。
語法：
set serveroutput on; //開啟控制檯輸出
declare [變數名] [變數資料型別]; //定義變數，寫在begin之前
begin
//程式碼塊
end; //程式碼編輯區
:= //賦值符號
dbms_output.put_line('內容'||變數); //控制檯輸出，和Java不同的是連線使用的是||符號而不是加
號，注意字串必須用單引號來修飾
範例：
declare result int;
begin
select x into result from ttt where rownum=1;
result:=result+1;
dbms_output.put_line('result='||result);
end;
學生表的建立
create table my_student
(
stuno int primary key,
stuname varchar2(20) not null,
stuage int not null
)
範例2
declare
v_stuname varchar2(20);
v_stuage int;
begin
select stuname, stuage into v_stuname,v_stuage
from my_student
where stuno=2;
dbms_output.put_line('sutdent name'||v_stuname||', student age:'||v_stuage);
exception
when NO_DATA_FOUND then
dbms_output.put_line('There is no student with stu no 2');
end;
範例3：
new -> program window ->procedure
--根據學號顯示對應學生資訊
create or replace procedure uuu_show_student(x_stuno in int)
is
v_stuname varchar2(20);
v_stuage int;
begin
select stuname, stuage into v_stuname,v_stuage
from my_student
where stuno=x_stuno;
dbms_output.put_line('sutdent name'||v_stuname||', student age:'||v_stuage);
exception
when NO_DATA_FOUND then
dbms_output.put_line('There is no student with stu no 2');
end uuu_show_student;
執行儲存過程
執行execute uuu_show_student;
範例4.兩數求和
create or replace procedure show_uuu_number(x_num1 in int,x_num2 in int)
is
x_big_number int;
x_num_index int;
begin
if x_num1 > x_num2 then
x_big_number := x_num1;
x_num_index := 1;
else
x_big_number := x_num2;
x_num_index := 2;
end if;
dbms_output.put_line('big number:'||x_big_number||' index:'||x_num_index);
end show_uuu_number;
範例5.迴圈分支
create or replace procedure show_uuu_number(x_num1 in int,x_num2 in int)
is
x_big_number int;
x_num_index int;
begin
if x_num1 > x_num2 then
x_big_number := x_num1;
x_num_index := 1;
elsif x_num1 > x_num2 then
x_big_number := x_num2;
x_num_index := 2;
else
dbms_output.put_line('equal');
end if;
if x_num1 <> x_num2 then
dbms_output.put_line('big number:'||x_big_number||' index:'||x_num_index);
end if;
end show_uuu_number;
》case
when....
when....
else
.....
and case;
範例6
有返回值的用function
計算三角形面積
create or replace function uuu_area(width in number,height in number) return number is
Result number;
begin
Result := 0.5*width*height;
return(Result);
end uuu_area;
執行函式
select uuu_area(3,5) from dual;
uuu_AREA(3,5)
-------------
15
範例7
》case
when....
when....
else
.....
and case;
應用
create or replace function show_uuu_day(datestr in varchar2)
return varchar2 is
Result varchar2(200);
checkdate date;
v_day varchar2(1);
begin
checkdate := to_date(trim(datestr), 'YYYY-MM-DD');
v_day := to_char(checkdate, 'D');
case v_day
when '1' then
Result := datestr || '是星期天';
when '2' then
Result := datestr || '是星期一';
when '3' then
Result := datestr || '是星期二';
when '4' then
Result := datestr || '是星期三';
when '5' then
Result := datestr || '是星期四';
when '6' then
Result := datestr || '是星期五';
when '7' then
Result := datestr || '是星期六';
else
dbms_output.put_line(datestr || '是星期六');
end case;
return(Result);
end show_uuu_day;
執行函式
SQL> select show_uuu_day('2010-11-4') from dual;
SHOW_uuu_DAY('2010-11-4')
--------------------------------------------------------------------------------
2010-11-4是星期四
範例8
迴圈結構
create or replace function uuu_get_sum(num1 in number, num2 in number) return number is
Result number := 0;
temp number;
begin
temp := num1;
loop
Result:=Result+temp;
temp:=temp+1;
-- if temp > num2 then
-- exit;
--end if;
exit when temp > num2;
end loop;
return(Result);
end uuu_get_sum;
執行函式
SQL> select uuu_get_sum(1,100) from dual;
uuu_GET_SUM(1,100)
------------------
5050
while loop
例：
create or replace function uuu_get_sum(num1 in number, num2 in number) return
number is
Result number := 0;
temp number;begin
temp := num1;
while num2 <= 200 loop
Result:=Result+temp;
temp:=temp+1;
-- if temp > num2 then
-- exit;
--end if;
exit when temp > num2;
end loop; return(Result);
end uuu_get_sum;
執行函式
SQL> select uuu_get_sum(1,1000) from dual;
uuu_GET_SUM(1,1000)
-------------------
0
範例8
數字for loop迴圈
create or replace procedure sum(begin1 number,end2 number)
as
tosum number;
begin
tosum:=0;
for i in begin1..end2
loop
tosum:=tosum+i;
end loop;
dbms_output.put_line(tosum);
end sum;
001.判斷一個數是否為質數
create or replace function x_isprime(num in int) return int is
Result int;
begin
for i in 2..num-1
loop
if num mod i = 0 then
Result := 0;
return(Result);
end if;
end loop;
if num >= 2 then
Result := 1;
end if;
return(Result);
end x_isprime;
002判斷質數
create or replace procedure x_prime(begini in int, endi in int)
is
count2 int := 0;
begin
for i in begini..endi
loop
if x_isprime(i) = 1 then
dbms_output.put(i||' ');
count2:=count2+1;
if count2 mod 8 = 0 then
dbms_output.put_line('');
end if;
end if;
end loop;
end x_prime;
======
debug 許可權設定
grant debug connect session to test1;
003判斷質數
=======
CREATE OR REPLACE PROCEDURE show_prime(bval IN INT, eval IN INT) IS
flag INT;
icount int:=0;
BEGIN
-- 取數迴圈
FOR i IN bval .. eval LOOP
-- 質數判斷迴圈
flag := 1;
FOR j IN 2 .. i - 1 LOOP
IF i MOD j = 0 THEN
flag := 0;
EXIT;
END IF;
END LOOP;
IF flag = 1 THEN
dbms_output.put(i||' ');
icount:=icount+1;
if icount mod 8 =0 then
dbms_output.put_line('');
end if;
END IF;
END LOOP;
END show_prime;
======================================================================
--遊標
pl/sql cursor 操作
1. 什麼是遊標？
oracle在執行一條SQL語句的時候，它將建立一個記憶體區域 (context area),該記憶體區域包含執行這條語句所需要的所有資訊。
資訊如下：
1. 該語句執行之後返回的記錄集
2. 一個指標，指向了該語句在記憶體中的被解析後的結果。
cursor(遊標) 是一個handle (pointer), 指向了這個上下文區域。
通過cursor, PL/SQL程式能夠控制context area, 掌握在語句執行的時，將如何對該區域產生影響。
2. 遊標的型別
1) implicit cursor
每條SQL語句執行的時候，將自動產生一個implicit遊標。 該遊標，使用者不可控制。
一個cursor將自動和每條DML語句關聯 （update,delete,insert), 我們可以通過cursor瞭解上頭語句產生的結果。
所有update和delete語句相關聯cursor，包含了該操作影響的行的集合。
最後開啟的cursor， 名字叫SQL cursor.
--------------------------------------------------------------------------------------------
update my_student set stuname='mary' where stuno=60;
dbms_output.put_line(SQL%ROWCOUNT);
2) explicit cursor
使用者自己定義的遊標，針對的是返回超過一條記錄的查詢。 使用者可以通過該cursor控制記錄返回過程。
Record Type
記錄是複雜的資料結構。記錄往往表現成為表的一行。
create or replace procedure show_student2 is
vr_student my_student%ROWTYPE;
begin
select *
into vr_student
from my_student where stuno=6;
dbms_output.put_line(vr_student.stuno||','||vr_student.stuname);
end show_student2;
a. 宣告遊標 （declare a cursor)
該操作初始化這個遊標，為其建立記憶體空間。
CURSOR c_cursor_name is select statement
(該遊標和select語句相關聯)
------------------
declare
v_name varchar2(20);
CURSOR c_mycursor is
select * from student where name like '%h%';
遊標開啟後，不能繼續再二次開啟。
b. 開啟遊標 (open cursor)
建立context area, 執行語句， 獲得rows.
open c_mycursor
c. 獲取cursor中的行。
fetch cursorname into pl/sql variables
fetch cursorname into pl/sql record
d. 關閉cursor
一旦所有的行被處理結束，cursor應該被關閉。
關閉的操作通知pl/sql engine， 程式對該cursor的需求已經結束，可以釋放context are所佔用的記憶體資源。
cursor一旦關閉，則不可以繼續fetch . 也不能重複關閉cursor.
> 常用的cursor屬性
cursorname%NOTFOUND
cursorname%FOUND
cursorname%ROWCOUNT
cursorname%ISOPEN
--------------------
create or replace procedure show_student2 is
CURSOR c_student is
select * from my_student order by stuno desc;
vr_student my_student%ROWTYPE;
TYPE simple_stu is record
(
stuname my_student.stuname%TYPE,
stuage my_student.stuage%TYPE,
stuage2 int
);
vr_simple_student simple_stu;
cursor c_simple_student is
select stuname,stuage,stuage+2 from my_student order by stuno desc;
begin
/*
select *
into vr_student
from my_student where stuno=6;
dbms_output.put_line(vr_student.stuno||','||vr_student.stuname);
*/
open c_student;
loop
fetch c_student into vr_student;
exit when c_student%NOTFOUND;
dbms_output.put_line(vr_student.stuno||','||vr_student.stuname);
end loop;
if c_student %ISOPEN then
close c_student;
end if;
open c_simple_student;
loop
fetch c_simple_student into vr_simple_student;
exit when c_simple_student%NOTFOUND;
dbms_output.put_line(vr_simple_student.stuname||','||vr_simple_student.stuage
||','||vr_simple_student.stuage2);
end loop;
close c_simple_student;
end show_student2;
--------------------------------------------
cursor for loop
nested cursor
DECLARE
v_sid student.student_id%TYPE;
CURSOR c_student IS
SELECT student_id, first_name, last_name
FROM student
WHERE student_id < 110;
CURSOR c_course IS
SELECT c.course_no, c.description
FROM course c, section s, enrollment e
WHERE c.course_no = s.course_no
AND s.section_id = e.section_id
AND e.student_id = v_sid;
BEGIN
/*
使用for loop cursor, 可以便捷的遍歷遊標，省去了open,fetch,close的書寫。
連儲存變數的定義也可以省略，可在for後直接書寫變數名。
*/
FOR r_student IN c_student LOOP
v_sid := r_student.student_id;
DBMS_OUTPUT.PUT_LINE(chr(10));
DBMS_OUTPUT.PUT_LINE(' The Student ' || r_student.student_id || ' ' ||
r_student.first_name || ' ' ||
r_student.last_name);
DBMS_OUTPUT.PUT_LINE(' is enrolled in the ' || 'following courses: ');
-- nested cursor
FOR r_course IN c_course LOOP
DBMS_OUTPUT.PUT_LINE(r_course.course_no || ' ' ||
r_course.description);
END LOOP;
END LOOP;
END;
---------------------------------------------------------------
帶引數的遊標
CURSOR c_student(p_stuage in my_student.stuage%type) is
select * from my_student where stuage=p_stuage order by stuno desc;
for vr_student in c_student(20)
loop
dbms_output.put_line(vr_student.stuno||','||vr_student.stuname);
end loop;
CURSOR c_student２(p_stuage in my_student.stuage%type，p_stuname in my_student.stuname%TYPE) is
select * from my_student where stuage=p_stuage order by stuno desc;
cursor具備的引數：
1) cursor得到了複用。
2) 提高了效能，壓縮了返回的行的數量。
====================================================================
--異常
1.PL/SQL Exception
常用的預定義異常
a）NO_DATA_FOUND
根據查詢條件，沒有查詢記錄被找到
b）TOO_MANY_ROWS
select into 結構只能返回一條記錄，賦予儲存過程變數。如果select ..into..結構返回記錄為多條，將產生這個異常
c）ZERO_DIVIDE
除數是0(把ORA-01476 error對映成ZERO_DIVIDE錯誤）
例： SQL> select 6/0 from dual;
select 6/0 from dual
ORA-01476: 除數為 0
d)VALUE_ERROR
從運算或者資料庫中取得值賦予變數的時候型別不匹配或者長度不足，導致的異常
e)DUP_VAL_ON_INDEX
主鍵不可重複，違反主鍵唯一約束
f）OTHERS
(類似java Exception異常）
資料庫表
create table my_student(
stuno int primary key,
stuname varchar2(20),
stuage int
);
insert into my_student values(1,'dadiv',20);
insert into my_student values(2,'mary',20);
insert into my_student values(3,'henry',20);
異常舉例例子：
create or replace procedure uuu_show_student(x_stuno in int)
is
v_stuname varchar2(20);--替換v_stuname varchar2(2)/int值不匹配或者長度不足異常
v_stuage int;
begin
insert into my_student values(2,'kate',49);--DUP_VAL_ON_INDEX異常
select stuname, stuage into v_stuname,v_stuage
from my_student
where stuno=x_stuno;--加上or stuname like '%y'產生值記錄太多異常
dbms_output.put_line('sutdent name'||v_stuname||', student age:'||v_stuage);
exception
when NO_DATA_FOUND then
dbms_output.put_line('There is no student with stu no '||x_stuno);
when VALUE_ERROR then
dbms_output.put_line('值不匹配');
when TOO_MANY_ROWS then
dbms_output.put_line('記錄太多');
when DUP_VAL_ON_INDEX then
dbms_output.put_line('主鍵不可重複，插入失敗');
when OTHERS then
dbms_output.put_line('其它異常捕獲');--一些預定義異常的父類
end uuu_show_student;
異常的作用域
DECLARE
v_student_id NUMBER := &sv_student_id;
v_name VARCHAR2(30);
v_total NUMBER(1);
-- outer block
BEGIN
SELECT RTRIM(first_name)||' '||RTRIM(last_name)
INTO v_name
FROM student
WHERE student_id = v_student_id;
DBMS_OUTPUT.PUT_LINE ('Student name is '||v_name);
-- inner block
BEGIN
SELECT COUNT(*)
INTO v_total
FROM enrollment
WHERE student_id = v_student_id;
DBMS_OUTPUT.PUT_LINE ('Student is registered for '||
v_total||' course(s)');
EXCEPTION
WHEN VALUE_ERROR OR INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred');
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('There is no such student');
END;
自定義異常：
例：
create or replace procedure uuu_show_student(x_stuno in int)
is
v_stuname varchar2(20);--替換v_stuname varchar2(2)/int值不匹配或者長度不足異常
v_stuage int;
e_invalid_stuno EXCEPTION;--自定義異常
begin
if x_stuno < 0 then
raise e_invalid_stuno;
else
--insert into my_student values(2,'kate',49);--DUP_VAL_ON_INDEX異常
select stuname, stuage into v_stuname,v_stuage
from my_student
where stuno=x_stuno;--加上or stuname like '%y'產生值記錄太多異常
dbms_output.put_line('sutdent name'||v_stuname||', student age:'||v_stuage);
end if;
exception
when NO_DATA_FOUND then
dbms_output.put_line('There is no student with stu no '||x_stuno);
when VALUE_ERROR then
dbms_output.put_line('值不匹配');
when TOO_MANY_ROWS then
dbms_output.put_line('記錄太多');
when e_invalid_stuno then --控制檯輸入execute uuu_show_student(-2);
dbms_output.put_line('學生編號不合法');
when DUP_VAL_ON_INDEX then
dbms_output.put_line('主鍵不可重複，插入失敗');
when OTHERS then
dbms_output.put_line('其它異常捕獲');--一些預定義異常的父類
end uuu_show_student;
==================================================================
--觸發器的一個例子
create or replace trigger student_aud
before insert on my_student
for each row
declare
v_highage int;
begin
select stu_highage
into v_highage
from stu_stat;
if :NEW.stuage >25 then
v_highage:=v_highage+1;
end if ;
update stu_stat set stu_count=stu_count+1,stu_highage=v_highage;
end student_aud;
Java呼叫Oracle儲存過程
步驟：
1、編寫Oracle儲存過程；
2、編寫資料庫獲取連線工具類；
3、編寫簡單應用呼叫儲存過程。
實現：
1、Oracle儲存過程
(省了，本文主要講述怎麼在java中呼叫儲存過程)
在此假設此儲存過程有三個引數，最後會返回一個遊標。
2、資料庫連線工具類
/**
* 資料庫連線工具類
*/
public class DBUtil {
/**
* 連線工廠
*/
public static Connection makeConnection(){
Connection conn = null;
try {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:zfvdb", "root", "zfvims");
} catch (SQLException e) {
System.out.println("通過JDBC獲取Oralce資料庫連結物件出錯!");
e.printStackTrace();
}
return conn;
}
/**
* 連線測試
*/
public static void main(String args[]){
if(DBUtil.makeConnection()==null){
System.out.println("獲取資料庫連結失敗！");
}
System.out.println("JDBC獲取Oracle資料庫連結成功！");
}
}
3、呼叫儲存過程
/**
* 呼叫儲存過程測試
*/
public class Test {
/**
* 測試方法
*/
public void test(){
Connection conn = DBUtil.makeConnection();
String sql = "{call p_test(?,?,?)}";
CallableStatement cs;
try {
cs = conn.prepareCall(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
cs.setLong(1, "1300");
cs.setString(2, "2006-01-01");
cs.setString(3, "2007-01-01");
ResultSet rs = cs.executeQuery();
while (rs.next()) {
//todo：進行相關操作
}
rs.close();
cs.close();
} catch (SQLException e) {
System.out.println("呼叫儲存過程p_test出現異常！");
e.printStackTrace();
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
System.out.println("發生不能關閉JDBC連結的異常，請檢查！");
e.printStackTrace();
}
}
}
public static void main(String args[]){
new Test().test();
}
}
說明：Java呼叫其他資料庫的儲存過程方式和此例一樣，儲存過程有的不返回值，比如對資料庫執行一些update、insert、delete等操作。這個時候呼叫有所不同。詳細請參看J2SDK參考文件。
ORACLE儲存過程，函式，包，遊標
部落格分類： Oracle
1、 PL/SQL語句塊
PL/SQL語句塊只適用於Oracle資料庫，使用時臨時儲存在客戶端，而不是儲存在資料庫。
基本語法:
Sql程式碼
declare
變數宣告、初始化
begin
業務處理、邏輯程式碼
exception
異常捕獲
end;
變數宣告：<變數名> <型別及長度> [:=<初始值>]
例：v_name varchar2(20):=’張三’;
2、 迴圈語句
loop迴圈語法：
Sql程式碼
loop
exit when 表示式
end loop;
while迴圈語法：
Sql程式碼
while 表示式
loop
end loop;
for迴圈語法:
Sql程式碼
for <變數> in <變數取值範圍（小值..大值，如1..100）> loop
end loop;
for迴圈的變數可不做宣告及初始化。
3、 if判斷語句
基本語法：
Sql程式碼
if <表示式> then
…
else if <表示式> then
…
else
…
end if;
end if;
例：
Sql程式碼
declare
v_identity number(4):=0;
begin
loop
if v_identity=1 then
dbms_output.put_line('v_identity=1');
else if v_identity=3 then
dbms_output.put_line('v_identity=3');
else if v_identity=6 then
exit;
else
dbms_output.put_line('v_identity is not 1 or 3');
end if;
end if;
end if; -- 注意，有多少個if就要有多少個end if結束標誌。
v_identity:=v_identity+1;
end loop;
exception
when others then dbms_output.put_line('error!');
end;
4、 分支case
基本語法：
Sql程式碼
case <變數>
when 常量 then
…
when 常量 then
…
else
…
end case;
例：
Sql程式碼
declare
v_number number(4):=3;
v_string varchar(20):='abc';
begin
case v_number
when 1 then
dbms_output.put_line('v_number is '||1);
when 2 then
dbms_output.put_line('v_number is '||2);
when 3 then
dbms_output.put_line('v_number is '||3);
end case;
case v_string
when 'ab' then
dbms_output.put_line('v_string is '||'ab');
when 'bc' then
dbms_output.put_line('v_string is '||'bc');
else -- 預設匹配
dbms_output.put_line('v_string is other value');
end case;
exception
when others then dbms_output.put_line('error!');
end;
5、 異常(exception)
宣告異常語法：<異常名> exception;
丟擲異常語法：raise <異常名>;
捕獲異常語法：when <異常名> then 異常處理語句;
例：
Sql程式碼
declare
v_input varchar2(1):='&throw';-- 動態輸入
v_exception_1 exception; -- 自定義異常
v_exception_2 exception;
others exception; -- 系統異常
begin
if v_input='1' then
raise v_exception_1; -- 丟擲異常
else if v_input='2' then
raise v_exception_2;
else
raise others;
end if;
end if;
exception
-- 捕獲異常
when v_exception_1 then dbms_output.put_line('throw exception: v_exception_1');
when v_exception_2 then dbms_output.put_line('throw exception: v_exception_2');
when others then dbms_output.put_line('throw exception: others');
end;
6、 遊標(cursor)
宣告遊標語法：cursor <遊標名> is select語句;
宣告ref遊標語法:<遊標名> is ref cursor;
開啟遊標語法：open <遊標名>;
移動遊標並獲取資料語法：fetch <遊標名> into <用於儲存讀取的資料的變數的名>;
關閉遊標語法:close <遊標名>;
遊標屬性（遊標的屬性必須在關閉遊標之前）：
%isopen: 判斷遊標是否開啟
%notfound: 找不到資料時
%found:
%rowcount: 返回當前遊標已掃描的資料行數量
遊標分類：1、顯示遊標（自定義遊標）；2、隱示遊標（系統遊標）；3、REF遊標
例：
Sql程式碼
declare
v_row test%rowtype; -- 匹配t_test表中一行所有的資料型別
cursor v_cur is
select * from test;-- 宣告遊標
begin
open v_cur;-- 開啟遊標
loop
fetch v_cur into v_row;-- 將遊標所在行的資料轉存到v_row中
exit when v_cur%notfound; -- 當遊標到最後一行時跳出
dbms_output.put_line('id = '||v_row.t_id||' name = '||v_row.t_name||' msg = '||v_row.t_msg);
end loop;
close v_cur;-- 關閉遊標
exception
when others then dbms_output.put_line('throw exception: others');
end;
-- REF遊標 --
Sql程式碼
create or replace package upk_select_test
as
type uc_test is ref cursor; -- 宣告ref遊標
end upk_select_test;
-- 儲存過程中呼叫ref遊標，並將查詢結果以遊標的方式返回
create or replace procedure up_select_test_2
(uc_result out upk_select_test.uc_test)
is
begin
open uc_result for select * from t_test;
end up_select_test_2;
7、 通配型別操作符
%type: 通配某行某列資料型別，如v_name t_test.t_name%type;通配表t_test中的t_name。
%rowtype: 通配一行所有列的資料型別，如 v_row t_test%rowtype;匹配t_test表中一行所有的資料型別。
8、 儲存過程(procedure)
基本語法：
Sql程式碼
create procedure <過程名>(<引數列表,無參時忽略>)
as|is
變數宣告、初始化
begin
業務處理、邏輯程式碼
exception
異常捕獲、容錯處理
end <過程名>;
引數:<引數名> in|out|in out <引數型別，無長度說明> ，如：v_name varchar2
in：入參
out：出參
in out：出入參
注：as|is表示as或is
呼叫語法：
1)、exec <過程名>;
2)、execute <過程名>;
3)、在PL/SQL語句塊中直接呼叫
例：
Sql程式碼
create or replace procedure up_wap(v_param1 in out varchar2,v_param2 in out varchar2)
is
v_temp varchar2(20);
begin
dbms_output.put_line('交換前引數1：'||v_param1||' 引數2：'||v_param2);
v_temp:=v_param1;
v_param1:=v_param2;
v_param2:=v_temp;
dbms_output.put_line('交換後引數1：'||v_param1||' 引數2：'||v_param2);
exception
when others then dbms_output.put_line('There is a error when the procedure up_wap executing!');
end up_wap;
-- 呼叫儲存過程
Sql程式碼
declare
v_param1 varchar2(20):='param1';
v_param2 varchar2(20):='param2';
begin
up_wap(v_param1 => v_param1,v_param2 => v_param2);
end;
9、 自定義函式(function)
基本語法：
Sql程式碼
create function <函式名>(<引數列表,無參時忽略>)
return <返回值型別，無長度說明>
as|is
變數宣告、初始化
begin
業務處理、邏輯程式碼
return <返回的值>;
exception
異常捕獲、容錯處理
end <函式名>;
引數：in 入參
注：只有入參的型別。
在儲存過程和自定義函式中的引數的傳遞（入參和出參）不能使用%type或%rowtype匹配，不能使用空值null，但是儲存過程可以返回空值。
例：
Sql程式碼
create function uf_select_name_by_id_test(v_id in number)
return varchar2
is
v_name t_test.t_name%type;
begin
select t_name into v_name from t_test where t_id=v_id;
return v_name;
exception
when others then
dbms_output.put_line('error');
end uf_select_name_by_id_test;
select uf_select_name_by_id_test(1) 姓名 from dual;-- select呼叫
declare --pl/sql語句塊呼叫
v_name varchar2(20);
begin
v_name:=uf_select_name_by_id_test(1);
dbms_output.put_line('name = '||v_name);
end;
10、包(package)
封裝，可以封裝過程(procedure)、函式(function)和變數。
注意，在包(package)中宣告的過程(procedure)和函式(function)必須在包的實現體（package body）中定義實現。
基本語法：
Sql程式碼
create package <包名>
as|is
變數宣告
儲存過程宣告
自定義函式宣告
end <包名>;
create package body <包名,與宣告部分一致>
as|is
儲存過程的程式碼實現
自定義函式的程式碼實現
end <包名>;
例：
Java程式碼
-- 建立包upk_hello
create or replace package upk_hello
is
v_hello_world varchar2(20):='hello world'; -- 宣告變數
procedure up_hello_world(v_name in varchar2);-- 宣告過程
function uf_hello_world(v_name in varchar2) return varchar2;-- 宣告函式
end upk_hello;
-- 實現包(upk_hello)裡宣告的方法
create or replace package body upk_hello
is
procedure up_hello_world(v_name in varchar2)
is
v_string varchar2(100);
begin
v_string:=v_name||' say hello world!';
dbms_output.put_line(v_string);
exception
when others then dbms_output.put_line('error');
end up_hello_world;
function uf_hello_world(v_name in varchar2) return varchar2
is
v_string varchar2(100);
begin
v_string:=v_name||' say hello world!';
return v_string;
exception
when others then dbms_output.put_line('error');
end uf_hello_world;
end upk_hello;
-- 包的呼叫
declare
v_msg varchar2(100);
begin
upk_hello.up_hello_world('bing');
v_msg:=upk_hello.uf_hello_world('admin');
dbms_output.put_line(v_msg);
dbms_output.put_line(upk_hello.v_hello_world);
end;
儲存過程中的3種迴圈：
1、
Sql程式碼
is
i int;
begin
i :=1;
loop
..
exit when i =10;
i :=i+1;
end loop;
2、
Sql程式碼
i :=1;
while i<=5 loop
..
i :=i+1;
end loop;
3、
Sql程式碼
for i in 1..100 loop
..........
end loop;
這是我以前的學習筆記，LZ湊合著看看吧，應該能看懂一些吧
===================================================
55 java跟oracle 呼叫（儲存過程，函式等）
55.1 Java呼叫無參的函式
1：函式為：
create or replace function MyF1 return varchar2 is
Result varchar2(20);
begin
dbms_output.put_line('now in My F1');
Result := 'Now MyF1 return';
return(Result);
end MyF1;
2：Java程式
/**
* 演示呼叫有一個沒有引數的函式
* @throws Exception
*/
private static void t1() throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "test", "test");
try {
CallableStatement stmt = conn
.prepareCall("{?=call MyF1()}");
stmt.registerOutParameter(1, Types.VARCHAR);
stmt.execute();
System.out.println(stmt.getString(1));
} finally {
conn.close();
}
}
55.2 Java呼叫無參但有返回值的儲存過程
1：儲存過程
create or replace procedure MyP1(str out Varchar2) is
begin
dbms_output.put_line('Hello Procedure.');
str :='Haha，Hello Procedure';
end MyP1;
2：程式
/**
* 如何呼叫無參但有返回值的儲存過程 測試的儲存過程
* @throws Exception
*/
private static void t2() throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "test", "test");
try {
CallableStatement stmt = conn.prepareCall("{call MyP1(?)}");
// 注意，這裡的stmt.getInt(1)中的數值1並非任意的，而是和儲存過程中的out列對應的，
// 如果out是在第一個位置，那就是 stmt.getInt(1)，如果是第三個位置，就是getInt.getInt(3)，
// 當然也可以同時有多個返回值，那就是再多加幾個out 引數了。
stmt.registerOutParameter(1, Types.VARCHAR);
stmt.execute();
System.out.println(stmt.getString(1));
} finally {
conn.close();
}
}
55.3 Java呼叫有參的（傳入）函式
1：函式
create or replace function MyF2(a number,b varchar2) return varchar2 is
Result varchar2(50);
begin
dbms_output.put_line('a==='||a||',b=='||b);
Result := a||b;
return(Result);
end MyF2;
2：程式
/**
* 呼叫有參的函式
* @throws Exception
*/
private static void t3() throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "test", "test");
try {
CallableStatement stmt = conn
.prepareCall("{?=call MyF2(?,?)}");
stmt.setInt(2, 15);
stmt.setString(3, "HelloF2");
stmt.registerOutParameter(1, Types.VARCHAR);
stmt.execute();
System.out.println(stmt.getString(1));
} finally {
conn.close();
}
}
55.4 Java呼叫有參的（傳入傳出）儲存過程
1：儲存過程
create or replace procedure MyP2(a in number,b in varchar2,c out varchar2) is
begin
dbms_output.put_line('a=='||a||',b=='||b);
c := 'ret=='||a||',b=='||b;
end MyP2;
2：程式
/**
* 呼叫有引數和返回值的儲存過程
* @throws Exception
*/
private static void t4() throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "test", "test");
try {
CallableStatement stmt = conn.prepareCall("{call MyP2(?,?,?)}");
stmt.setInt(1, 5);
stmt.setString(2, "測試");
stmt.registerOutParameter(3, Types.VARCHAR);
stmt.execute();
System.out.println(stmt.getString(3));
} finally {
conn.close();
}
}
55.5 Java向儲存過程傳入傳出物件的陣列
1：在資料中建立物件
create or replace type UserModel as object(
uuid varchar2(20),
name varchar2(20)
);
2：在資料庫中建立物件的集合型別
create or replace type userCol as table of UserModel;
create or replace type retUserCol as table of UserModel;
3：在資料庫中建立包
包頭：
create or replace package MyTestPackage is
TYPE dbRs IS REF CURSOR;
procedure MyP3(a1 in userCol,a2 out dbRs);
end MyTestPackage;
包體：
create or replace package body MyTestPackage is
procedure MyP3(a1 in userCol,a2 out dbRs) as
umCol retUserCol := retUserCol();
begin
for i in 1.. a1.count loop
insert into tbl_test values (a1(i).uuid,a1(i).name);
end loop;
commit;
umCol.Extend;
umCol(1):=UserModel('retUuid11','retName11');
umCol.Extend;
umCol(2):=UserModel('retUuid22','retName22');
open a2 for select * from table(cast(umCol as retUserCol));
end;
begin
null;
end MyTestPackage;
4：程式：
/**
* 測試向pl/sql傳入物件集合，從pl/sql返回任意的物件的集合
* @param list
* @throws Exception
*/
private static void t5(List list) throws Exception {
CallableStatement stmt = null;
Connection con = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "test", "test");
if (con != null) {
ARRAY aArray = getArray(con, "USERMODEL", "USERCOL", list);//該函式呼叫的第二三個引數必須大寫
stmt = con.prepareCall("{call MyTestPackage.MyP3(?,?)}");
((OracleCallableStatement) stmt).setARRAY(1, aArray);
stmt.registerOutParameter(2, OracleTypes.CURSOR);
stmt.execute();
ResultSet rs=(ResultSet)stmt.getObject(2);
while(rs.next()){
String uuid = rs.getString("uuid");
String name = rs.getString("name");
System.out.println("the uuid="+uuid+",name="+name);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private static ARRAY getArray(Connection con, String OracleObj, String Oraclelist,
List objlist) throws Exception {
ARRAY list = null;
if (objlist != null && objlist.size() > 0) {
StructDescriptor structdesc = new StructDescriptor(OracleObj, con);
STRUCT[] structs = new STRUCT[objlist.size()];
Object[] result = new Object[0];
for (int i = 0; i < objlist.size(); i++) {
result = new Object[2];//陣列大小應和你定義的資料庫物件(UserModel)的屬性的個數
result[0] = ((UserModel)(objlist.get(i))).getUuid(); //將list中元素的資料傳入result陣列
result[1] = ((UserModel)(objlist.get(i))).getName(); //
structs[i] = new STRUCT(structdesc, con, result);
}
ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist,
con);
list = new ARRAY(desc, con, structs);
}
return list;
}
如果使用Tomcat的DBCP的連線池，需要把連線進行轉換
public Connection getNativeConnection(Connection con) throws SQLException {
if (con instanceof DelegatingConnection) {
Connection nativeCon = ((DelegatingConnection) con).getInnermostDelegate();
return (nativeCon != null ? nativeCon : con.getMetaData().getConnection());
}
return con;
}
==============================
Oracle中table變數在JDBC中的運用
1.先定義一個返回陣列型別的方法
create or replace type my_table_type is table of varchar2(20);
create or replace function func
return my_table_type
is
i my_table_type:=my_table_type();
begin
select name bulk collect into i from emps;
return i;
end;
2.在JDBC中呼叫,如果返回的是table變數
public void SelectAgus(String sql)
{
OracleCallableStatement call = null;
try
{
call = (OracleCallableStatement) con.prepareCall(sql);
//如果返回的是table則用ARRAY型別,如果返回的是OBJECT的就用STRUCT
//第三個引數是定義table的型別名
call.registerOutParameter(1, OracleTypes.ARRAY,"MY_TABLE_TYPE");
call.execute();
//獲取第一個引數(這裡即返回值)
ARRAY array = call.getARRAY(1);
//獲取表中的元素
Datum[] dat = array.getOracleArray();
//遍歷依次列印
for(Datum d : dat)
{
System.out.println(new String(d.getBytes()));
}
}catch(Exception e)
{
e.printStackTrace();
}
}
2.如果定義的是巢狀表結構,
如下定義:
create or replace type all_table is object(id number,name varchar2(20));
create or replace type emps_table_type is table of all_table;
--建立一個函式,返回型別為emps_table_type;
create or replace function funcc
return emps_table_type
is
i emps_table_type;
begin
--把emps中的ID,NAME屬性值全部讀取到i中
select all_table(id,name) bulk collect into i from emps;
return i;--返回table
end;
public void SelectAgus(String sql)
{
OracleCallableStatement call = null;
try
{
call = (OracleCallableStatement) con.prepareCall(sql);
call.registerOutParameter(1, OracleTypes.ARRAY,"EMPS_TABLE_TYPE");
call.execute();
ARRAY array = call.getARRAY(1);
Datum[] dat = array.getOracleArray();
for(Datum d : dat)
{ //獲取了行後,要獲取一行中的元素
STRUCT struct = (STRUCT)d;
//這裡有可能會出現亂碼,所以我分別用了兩種方式獲取元素
Datum[] d1 = struct.getOracleAttributes();
Object[] d2 = struct.getAttributes();
System.out.println("ID="+d2[0]+" "+"NAME="+
new String(d1[1].getBytes()));
}
}catch(Exception e){
e.printStackTrace();
}
}
oracle 在一個儲存過程中呼叫另一個返回遊標的儲存過程
實際專案當中經常需要在一個儲存過程中呼叫另一個儲存過程返回的遊標，本文列舉了兩種情況講述具體的操作方法。
第一種情況是返回的遊標是某個具體的表或檢視的資料，如：
CREATE OR REPLACE PROCEDURE P_TESTA (
PRESULT OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN PRESULT FOR SELECT * FROM USERS;
END P_TESTA;
其中USERS就是資料庫中一個表。在呼叫的時候只要宣告一個該表的ROWTYPE型別就可以了：
CREATE OR REPLACE PROCEDURE P_TESTB
AS
VARCURSOR SYS_REFCURSOR;
R USERS%ROWTYPE;
BEGIN
P_TESTA(VARCURSOR);
LOOP
FETCH VARCURSOR INTO R;
EXIT WHEN VARCURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(R.NAME);
END LOOP;
END P_TESTB;
第二種情況，我們返回的不是表的所有的列，或許只是其中一列或兩列，如：
CREATE OR REPLACE PROCEDURE P_TESTA (
PRESULT OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN PRESULT FOR SELECT ID,NAME FROM USERS;
END P_TESTA;
這裡我們只返回了USERS表的ID,NAME這兩個列，那麼呼叫的時候也必須做相應的修改：
CREATE OR REPLACE PROCEDURE P_TESTB
AS
VARCURSOR SYS_REFCURSOR;
CURSOR TMPCURSOR IS SELECT ID,NAME FROM USERS WHERE ROWNUM=1;
R TMPCURSOR%ROWTYPE;
BEGIN
P_TESTA(VARCURSOR);
LOOP
FETCH VARCURSOR INTO R;
EXIT WHEN VARCURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(R.ID);
END LOOP;
END P_TESTB;
與之前不同的是我們宣告瞭一個遊標型別的變數TMPCURSOR ，注意TMPCURSOR 的結構必須與儲存過程P_TESTA 返回的遊標結構一致，否則就會出現錯誤。同理只要保持兩個遊標型別結構一致，就可以實現自由呼叫。
create table test(id int,name varchar(10))
insert into test select 1,'AAAA'
insert into test select 2,'BBBB'
go
create procedure sp_test1(@count int output)
as
select @count=count(*) from test
go
create procedure sp_test2
as
begin
declare @count int
exec sp_test1 @count output
select @count
end
go
exec sp_test2
go
--輸出結果
/*
2
*/
drop procedure sp_test2,sp_test1
drop table test
go
oracle procedure 和function 的區別有哪些？
procedure 可多個返回引數，也就是out型別
function就一個
就這點區別
我覺得看使用的地方，如果只要執行一段sql的語句段，兩個都行，如過想有返回值，一個的話用function，多個的話procedure。
procedure是儲存過程 相當於程式語言裡面一個處理業務的方法 也可以返回值
function是方法 相當於程式語言裡面返回一個值的方法 一般較簡單 可以在dml語句中用這個方法加引數增刪改查
package相當於程式裡面一個介面 裡面可以定義常量陣列bean 多個procedure和多個function的空實現
package body相當於程式裡面一個類 是對應實現介面package的
迴圈：
1、..
is
i int;
begin
i :=1;
loop
..
exit when i =10;
i :=i+1;
end loop;
2、
i :=1;
while i<=5 loop
..
i :=i+1;
end loop;
3、
--如果指定了reverse選項，則迴圈控制變數會自動減1，否則自動加1
for j in reverse 1..10 loop
..
end loop;
1.基本結構
CREATE OR REPLACE PROCEDURE 儲存過程名字
(
引數1 IN NUMBER,
引數2 IN NUMBER
) IS
變數1 INTEGER :=0;
變數2 DATE;
BEGIN
END 儲存過程名字
2.SELECT INTO STATEMENT
將select查詢的結果存入到變數中，可以同時將多個列儲存多個變數中，必須有一條
記錄，否則丟擲異常(如果沒有記錄丟擲NO_DATA_FOUND)
例子：
BEGIN
SELECT col1,col2 into 變數1,變數2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;
...
3.IF 判斷
IF V_TEST=1 THEN
BEGIN
do something
END;
END IF;
4.while 迴圈
WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
END LOOP;
5.變數賦值
V_TEST := 123;
6.用for in 使用cursor
...
IS
CURSOR cur IS SELECT * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM :=cur_result.列名1 cur_result.列名2
END;
END LOOP;
END;
7.帶引數的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(變數值);
LOOP
FETCH C_USER INTO V_NAME;
EXIT FETCH C_USER%NOTFOUND;
do something
END LOOP;
CLOSE C_USER;
8.用pl/sql developer debug
連線資料庫後建立一個Test WINDOW
在視窗輸入呼叫SP的程式碼,F9開始debug,CTRL N單步除錯
相關文章
- MySQL儲存過程詳解 mysql 儲存過程MySql儲存過程
- 儲存過程儲存過程
- SQL 儲存過程裡呼叫另一個儲存過程SQL儲存過程
- MySQL儲存過程詳解 mysql 儲存過程linkMySql儲存過程
- Oracle儲存過程乾貨（一）：儲存過程基礎Oracle儲存過程
- 儲存過程與儲存函式儲存過程儲存函式
- Oracle儲存過程Oracle儲存過程
- Mysql 儲存過程MySql儲存過程
- 使用儲存過程儲存過程
- sybase儲存過程儲存過程
- java儲存過程Java儲存過程
- 管理儲存過程儲存過程
- 呼叫儲存過程儲存過程
- 實戰儲存過程排程過程儲存過程
- 在儲存過程A中呼叫儲存過程B的結果儲存過程
- 從sybase的儲存過程轉向oracle的儲存過程儲存過程Oracle
- MySql儲存過程—2、第一個MySql儲存過程的建立MySql儲存過程
- 把自編儲存過程設定為系統儲存過程儲存過程
- 儲存過程 傳 datatable儲存過程
- 儲存過程——遊標儲存過程
- Oracle儲存過程-1Oracle儲存過程
- mysql儲存過程整理MySql儲存過程
- JAVA儲存過程(轉)Java儲存過程
- JdbcTemplate調儲存過程JDBC儲存過程
- Winform呼叫儲存過程ORM儲存過程
- oracle的儲存過程Oracle儲存過程
- SER SERVER儲存過程Server儲存過程
- MySQL之儲存過程MySql儲存過程
- [MYSQL -23儲存過程]MySql儲存過程
- SQL儲存過程示例SQL儲存過程
- Oracle儲存過程例子Oracle儲存過程
- SQL Server 儲存過程SQLServer儲存過程
- 儲存過程 函式儲存過程函式
- 【SQL Server】--儲存過程SQLServer儲存過程
- 修改的儲存過程儲存過程
- Oracle建立儲存過程Oracle儲存過程
- perl呼叫儲存過程儲存過程
- 分頁儲存過程儲存過程