商業(yè)規(guī)則和業(yè)務(wù)邏輯可以通過程序存儲在Oracle中,這個程序就是存儲過程。
10年積累的成都做網(wǎng)站、網(wǎng)站制作經(jīng)驗,可以快速應(yīng)對客戶對網(wǎng)站的新想法和需求。提供各種問題對應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認識你,你也不認識我。但先網(wǎng)站設(shè)計后付款的網(wǎng)站建設(shè)流程,更有秦安免費網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。
存儲過程是SQL, PL/SQL, Java 語句的組合,它使你能將執(zhí)行商業(yè)規(guī)則的代碼從你的應(yīng)用程序中移動到數(shù)據(jù)庫。這樣的結(jié)果就是,代碼存儲一次但是能夠被多個程序使用。
要創(chuàng)建一個過程對象(procedural object),必須有 CREATE PROCEDURE 系統(tǒng)權(quán)限。如果這個過程對象需要被其他的用戶schema 使用,那么你必須有 CREATE ANY PROCEDURE 權(quán)限。執(zhí)行 procedure 的時候,可能需要excute權(quán)限?;蛘?/FONT>EXCUTE ANY PROCEDURE 權(quán)限。如果單獨賦予權(quán)限,如下例所示:
grant execute on MY_PROCEDURE to Jelly
調(diào)用一個存儲過程的例子:
execute MY_PROCEDURE( 'ONE PARAMETER');
存儲過程(PROCEDURE)和函數(shù)(FUNCTION)的區(qū)別。
function有返回值,并且可以直接在Query中引用function和或者使用function的返回值。
本質(zhì)上沒有區(qū)別,都是 PL/SQL 程序,都可以有返回值。最根本的區(qū)別是: 存儲過程是命令, 而函數(shù)是表達式的一部分。比如:
select max(NAME) FROM
但是不能 exec max(NAME) 如果此時max是函數(shù)。
PACKAGE是function,procedure,variables 和sql 語句的組合。package允許多個procedure使用同一個變量和游標。
創(chuàng)建 procedure的語法:
CREATE [ OR REPLACE ] PROCEDURE [ schema.]procedure [(argument [IN | OUT | IN OUT ] [NO COPY] datatype [, argument [IN | OUT | IN OUT ] [NO COPY] datatype]... )] [ authid { current_user | definer }] { is | as } { pl/sql_subprogram_body | language { java name 'String' | c [ name, name] library lib_name }] |
Sql 代碼:
CREATE PROCEDURE sam.credit (acc_no IN NUMBER, amount IN NUMBER) AS BEGIN UPDATE accounts SET balance = balance + amount WHERE account_id = acc_no; END; |
可以使用 create or replace procedure 語句, 這個語句的用處在于,你之前賦予的excute權(quán)限都將被保留。
IN, OUT, IN OUT用來修飾參數(shù)。
IN 表示這個變量必須被調(diào)用者賦值然后傳入到PROCEDURE進行處理。
OUT 表示PRCEDURE 通過這個變量將值傳回給調(diào)用者。
IN OUT 則是這兩種的組合。
authid代表兩種權(quán)限:
定義者權(quán)限(difiner right 默認),執(zhí)行者權(quán)限(invoker right)。
定義者權(quán)限說明這個procedure中涉及的表,視圖等對象所需要的權(quán)限只要定義者擁有權(quán)限的話就可以訪問。
執(zhí)行者權(quán)限則需要調(diào)用這個 procedure的用戶擁有相關(guān)表和對象的權(quán)限。
Oracle存儲過程的基本語法
1. 基本結(jié)構(gòu)
CREATE OR REPLACE PROCEDURE 存儲過程名字 END 存儲過程名字 |
2. SELECT INTO STATEMENT
將select查詢的結(jié)果存入到變量中,可以同時將多個列存儲多個變量中,必須有一條
記錄,否則拋出異常(如果沒有記錄拋出NO_DATA_FOUND)
例子:
BEGIN WHEN OTHERS THEN xxxx; |
3. IF 判斷
IF V_TEST=1 THEN |
4. while 循環(huán)
WHILE V_TEST=1 LOOP |
5. 變量賦值
V_TEST := 123; |
6. 用for in 使用cursor
... |
7. 帶參數(shù)的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID; |
8. 用pl/sql developer debug
連接數(shù)據(jù)庫后建立一個Test WINDOW
在窗口輸入調(diào)用SP的代碼,F9開始debug,CTRL+N單步調(diào)試
9. Pl/Sql中執(zhí)行存儲過程
在sql*plus中:
declare |
在SQL/PLUS中調(diào)用存儲過程,顯示結(jié)果:
SQL>set serveoutput on --打開輸出 SQL>var info1 number; --輸出1 SQL>var info2 number; --輸出2 SQL>declare var1 varchar2(20); --輸入1 var2 varchar2(20); --輸入2 var3 varchar2(20); --輸入2 BEGIN pro(var1,var2,var3,:info1,:info2); END; / SQL>print info1; SQL>print info2; |
注:在EXECUTE IMMEDIATE STR語句是SQLPLUS中動態(tài)執(zhí)行語句,它在執(zhí)行中會自動提交,類似于DP中FORMS_DDL語句,在此語句中str是不能換行的,只能通過連接字符"||",或著在在換行時加上"-"連接字符。
關(guān)于Oracle存儲過程的若干問題備忘
如:
select a.appname from appinfo a;-- 正確
select a.appname from appinfo as a;-- 錯誤
也許,是怕和Oracle中的存儲過程中的關(guān)鍵字as沖突的問題吧
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
可以在該語法之前,先利用select count(*) from 查看數(shù)據(jù)庫中是否存在該記錄,如果存在,再利用select...into...
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
假設(shè)有一個表A,定義如下:
create table A( |
如果在存儲過程中,使用如下語句:
select sum(vcount) into fcount from A where bid='xxxxxx';
如果A表中不存在bid="xxxxxx"的記錄,則fcount=null(即使fcount定義時設(shè)置了默認值,如:fcount number(8):=0依然無效,fcount還是會變成null),這樣以后使用fcount時就可能有問題,所以在這里最好先判斷一下:
if fcount is null then
fcount:=0;
end if;
這樣就一切ok了。
this.pnumberManager.getHibernateTemplate().execute( new HibernateCallback() ...{ public Object doInHibernate(Session session) throws HibernateException, SQLException ...{ CallableStatement cs = session .connection() .prepareCall("{call modifyapppnumber_remain(?)}"); cs.setString(1, foundationid); cs.execute(); return null; } }); |
用Java調(diào)用Oracle存儲過程總結(jié)
測試表:
-- Create table create table TESTTB ( ID VARCHAR2(30), NAME VARCHAR2(30) ) tablespace BOM pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); |
例: 存儲過程為(當然了,這就先要求要建張表TESTTB,里面兩個字段(I_ID,I_NAME)。
):
CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2, PARA2 IN VARCHAR2) AS BEGIN INSERT INTO BOM.TESTTB(ID, NAME) VALUES (PARA1, PARA2); END TESTA; |
在Java里調(diào)用時就用下面的代碼:
package com.yiming.procedure.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestProcedureDemo1 { public TestProcedureDemo1() { } public static void main(String[] args) { String driver = "Oracle.jdbc.driver.OracleDriver"; String strUrl = "jdbc:Oracle:thin:@10.20.30.30:1521:vasms"; Statement stmt = null; ResultSet rs = null; Connection conn = null; CallableStatement proc = null; try { Class.forName(driver); conn = DriverManager.getConnection(strUrl, "bom", "bom"); proc = conn.prepareCall("{ call BOM.TESTA(?,?) }"); proc.setString(1, "100"); proc.setString(2, "TestOne"); proc.execute(); } catch (SQLException ex2) { ex2.printStackTrace(); } catch (Exception ex2) { ex2.printStackTrace(); } finally { try { if (rs != null) { rs.close(); if (stmt != null) { stmt.close(); } if (conn != null) { conn.close(); } } } catch (SQLException ex1) { } } } } |
例:存儲過程為:
CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2, PARA2 OUT VARCHAR2) AS BEGIN SELECT NAME INTO PARA2 FROM TESTTB WHERE ID = PARA1; END TESTB; |
在Java里調(diào)用時就用下面的代碼:
package com.yiming.procedure.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; public class TestProcedureDemo2 { public static void main(String[] args) { String driver = "Oracle.jdbc.driver.OracleDriver"; String strUrl = "jdbc:Oracle:thin:@10.20.30.30:1521:vasms"; Statement stmt = null; ResultSet rs = null; Connection conn = null; CallableStatement proc = null; try { Class.forName(driver); conn = DriverManager.getConnection(strUrl, "bom", "bom"); proc = conn.prepareCall("{ call BOM.TESTB(?,?) }"); proc.setString(1, "100"); proc.registerOutParameter(2, Types.VARCHAR); proc.execute(); String testPrint = proc.getString(2); System.out.println("=testPrint=is=" + testPrint); } catch (SQLException ex2) { ex2.printStackTrace(); } catch (Exception ex2) { ex2.printStackTrace(); } finally { try { if (rs != null) { rs.close(); if (stmt != null) { stmt.close(); } if (conn != null) { conn.close(); } } } catch (SQLException ex1) { } } } } |
注意,這里的proc.getString(2)中的數(shù)值2并非任意的,而是和存儲過程中的out列對應(yīng)的,如果out是在第一個位置,那就是proc.getString(1),如果是第三個位置,就是proc.getString(3),當然也可以同時有多個返回值,那就是再多加幾個out參數(shù)了。
由于Oracle存儲過程沒有返回值,它的所有返回值都是通過out參數(shù)來替代的,列表同樣也不例外,但由于是集合,所以不能用一般的參數(shù),必須要用pagkage了.所以要分兩部分,
1. 建一個程序包。如下:
CREATE OR REPLACE PACKAGE TESTPACKAGE AS TYPE TEST_CURSOR IS REF CURSOR; end TESTPACKAGE; |
2. 建立存儲過程,存儲過程為:
CREATE OR REPLACE PROCEDURE TESTC(P_CURSOR out TESTPACKAGE.TEST_CURSOR) IS BEGIN OPEN P_CURSOR FOR SELECT * FROM BOM.TESTTB; END TESTC; |
可以看到,它是把游標(可以理解為一個指針),作為一個out 參數(shù)來返回值的。
在Java里調(diào)用時就用下面的代碼:
在這里要注意,在執(zhí)行前一定要先把Oracle的驅(qū)動包放到class路徑里,否則會報錯的。
package com.yiming.procedure.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestProcedureDemo3 { public static void main(String[] args) { String driver = "Oracle.jdbc.driver.OracleDriver"; String strUrl = "jdbc:Oracle:thin:@10.20.30.30:1521:vasms"; Statement stmt = null; ResultSet rs = null; Connection conn = null; CallableStatement proc = null; try { Class.forName(driver); conn = DriverManager.getConnection(strUrl, "bom", "bom"); proc = conn.prepareCall("{ call bom.testc(?) }"); proc.registerOutParameter(1, Oracle.jdbc.OracleTypes.CURSOR); proc.execute(); rs = (ResultSet) proc.getObject(1); while (rs.next()) { System.out.println("<tr><td>" + rs.getString(1) + "</td><td>" + rs.getString(2) + "</td></tr>"); } } catch (SQLException ex2) { ex2.printStackTrace(); } catch (Exception ex2) { ex2.printStackTrace(); } finally { try { if (rs != null) { rs.close(); if (stmt != null) { stmt.close(); } if (conn != null) { conn.close(); } } } catch (SQLException ex1) { } } } } |
在存儲過程中做簡單動態(tài)查詢
在存儲過程中做簡單動態(tài)查詢代碼 ,例如: | |
|
一般的PL/SQL程序設(shè)計中,在DML和事務(wù)控制的語句中可以直接使用SQL,但是DDL語句及系統(tǒng)控制語句卻不能在PL/SQL中直接使用,要想實現(xiàn)在PL/SQL中使用DDL語句及系統(tǒng)控制語句,可以通過使用動態(tài)SQL來實現(xiàn)。
首先我們應(yīng)該了解什么是動態(tài)SQL,在Oracle數(shù)據(jù)庫開發(fā)PL/SQL塊中我們使用的SQL分為:靜態(tài)SQL語句和動態(tài)SQL語句。所謂靜態(tài)SQL指在PL/SQL塊中使用的SQL語句在編譯時是明確的,執(zhí)行的是確定對象。而動態(tài)SQL是指在PL/SQL塊編譯時SQL語句是不確定的,如根據(jù)用戶輸入的參數(shù)的不同而執(zhí)行不同的操作。編譯程序?qū)討B(tài)語句部分不進行處理,只是在程序運行時動態(tài)地創(chuàng)建語句、對語句進行語法分析并執(zhí)行該語句。
Oracle中動態(tài)SQL可以通過本地動態(tài)SQL來執(zhí)行,也可以通過DBMS_SQL包來執(zhí)行。下面就這兩種情況分別進行說明:
本地動態(tài)SQL是使用EXECUTE IMMEDIATE語句來實現(xiàn)的。
1、 本地動態(tài)SQL執(zhí)行DDL語句:
需求:根據(jù)用戶輸入的表名及字段名等參數(shù)動態(tài)建表。
create or replace procedure proc_test |
以上是編譯通過的存儲過程代碼。下面執(zhí)行存儲過程動態(tài)建表。
SQL> execute proc_test(’dinya_test’,’id’,’number(8) not null’,’name’,’varchar2(100)’); |
到這里,就實現(xiàn)了我們的需求,使用本地動態(tài)SQL根據(jù)用戶輸入的表名及字段名、字段類型等參數(shù)來實現(xiàn)動態(tài)執(zhí)行DDL語句。
2、 本地動態(tài)SQL執(zhí)行DML語句。
需求:將用戶輸入的值插入到上例中建好的dinya_test表中。
create or replace procedure proc_insert |
執(zhí)行存儲過程,插入數(shù)據(jù)到測試表中。
SQL> execute proc_insert(1,’dinya’); |
在上例中,本地動態(tài)SQL執(zhí)行DML語句時使用了using子句,按順序?qū)⑤斎氲闹到壎ǖ阶兞?,如果需要輸出參?shù),可以在執(zhí)行動態(tài)SQL的時候,使用RETURNING INTO 子句,如:
declare |
使用DBMS_SQL包實現(xiàn)動態(tài)SQL的步驟如下:
A、先將要執(zhí)行的SQL語句或一個語句塊放到一個字符串變量中。
B、使用DBMS_SQL包的parse過程來分析該字符串。
C、使用DBMS_SQL包的bind_variable過程來綁定變量。
D、使用DBMS_SQL包的execute函數(shù)來執(zhí)行語句。
1、使用DBMS_SQL包執(zhí)行DDL語句
需求:使用DBMS_SQL包根據(jù)用戶輸入的表名、字段名及字段類型建表。
create or replace procedure proc_dbms_sql
新聞標題:Oracle存儲過程學習筆記
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供微信公眾號、網(wǎng)站策劃、品牌網(wǎng)站制作、搜索引擎優(yōu)化、商城網(wǎng)站、品牌網(wǎng)站建設(shè) 聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián) 猜你還喜歡下面的內(nèi)容
|