1、函數存儲過程觸發器第1頁，共20頁，2022年，5月20日，9點28分，星期一無參數函數的使用create or replace function fun_zmxreturn varchar2is spmc_name varchar2(50);begin select spmc into spmc_name from cgmxb where rownum=1; return spmc_name;end;說明：into spmc_name表示把獲取的數據給定義的參數第2頁，共20頁，2022年，5月20日，9點28分，星期一有參數函數的使用create or replace function f

2、un_zmx(param number)return varchar2is name_sp varchar2(20);begin select spmc into name_sp from cgmxb where mxid=param; return name_sp;end;第3頁，共20頁，2022年，5月20日，9點28分，星期一按當前字段類型返回函數create or replace function fun_zmx(param user1.cgmxb.cj%type)return user1.cgmxb.mxid%typeis name_sp user1.cgmxb.mxid%type

3、;begin select avg(mxid) into name_sp from cgmxb where cj=param; return name_sp;end;說明：user1.cgmxb.cj%type表示當前用戶的表中字段的類型第4頁，共20頁，2022年，5月20日，9點28分，星期一常用的系統函數字符串連接函數concatselect mxid,concat(spmc,cj) from cgmxb 等價于select mxid,spmc|cj from cgmxb 字符串截取函數substrselect mxid,substr(spmc,1,1) from cgmxb 第5頁，共

4、20頁，2022年，5月20日，9點28分，星期一常用的系統函數大小寫轉換函數upper、lower大寫select mxid,upper(spmc) from cgmxb 小寫select mxid,lower(upper(spmc) from cgmxb 第6頁，共20頁，2022年，5月20日，9點28分，星期一常用的系統函數獲取系統日期sysdateselect sysdate from dual日期格式轉換select to_date(20010202,yyyy-mm-dd) from dualto_date函數需要日期字符串轉換函數select to_char(sysdate,yy

5、yy-MM-dd) from dual第7頁，共20頁，2022年，5月20日，9點28分，星期一常用的系統函數絕對值函數：absselect abs(-2.36) from dual向上取值ceilselect ceil(2.36) from dual向下取值floorselect floor(2.36) from dual取模modselect mod(8,3) from dual第8頁，共20頁，2022年，5月20日，9點28分，星期一常用的系統函數次訪換算powerselect power(2,3) from dual表示2的3次方四舍五入換算roundselect round(3.

6、1415926,3) from dual3表示精度（保留小數的位數）第9頁，共20頁，2022年，5月20日，9點28分，星期一分頁方法比較SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM = 21/SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A )WHERE RN BETWEEN 21 AND 40第一種效率高于第二種第10頁，共20頁，2022年，5月20日，9點28分，星期一存儲

7、過程存儲過程具有如下特點：1）存儲過程是預編譯過的，并且經優化后存儲于SQL內存中，使用時無需再次編譯，提高了工作效率；2）存儲過程的代碼直接存放于數據庫中，一般由客戶端直接通過存儲過程的名字進行調用，減少了網絡流量，加快了系統執行速度，例如在進行百萬以上的大批量數據查詢時，使用存儲過程分頁要比其他方式分頁快得多；3）使用存儲過程可以減少SQL注入式攻擊，提高了系統的安全性，執行存儲過程的用戶要具有一定的權限才能使用存儲過程，沒有數據操作權限的用戶只能在其控制下間接地存取數據；第11頁，共20頁，2022年，5月20日，9點28分，星期一存儲過程）在同時進行主、從表及多表間的數據維護及有效性驗

8、證時，使用存儲過程比較方便，而且可以有效利用SQL中的事務處理的機制；5）使用存儲過程，可以實現存儲過程設計和編碼工作分開進行，只要將存儲過程名、參數、及返回信息告訴編碼人員即可；6）但使用存儲過程封裝業務邏輯將限制應用程序的可移植性；另外，如果更改存儲過程的參數或者其返回的數據及類型的話，需要修改應用程序的相關代碼，比較繁瑣。第12頁，共20頁，2022年，5月20日，9點28分，星期一存儲過程存儲過程的定義：CREATE OR REPLACE PROCEDURE procedure_name(parameter1 model datatype1,parameter2 model datat

9、ype2 .)IS ASBEGIN PL/SQL Block;END procedure_name;其中： procedure_name是存儲過程的名字，parameter用于指定參數，model用于指定參數模式，datatype用于指定參數類型， IS AS用于開始PL/SQL代碼塊。注：當定義存儲過程的參數時，只能指定數據類型，不能指定數據長度第13頁，共20頁，2022年，5月20日，9點28分，星期一存儲過程1）建立存儲過程時，既可以指定存儲過程的參數，也可以不提供任何參數。2）存儲過程的參數主要有三種類型：輸入參數(IN)、輸出參數(OUT)、輸入輸出參數(IN OUT)，其中IN用

10、于接收調用環境的輸入參數，OUT用于將輸出數據傳遞到調用環境，IN OUT不僅要接收數據，而且要輸出數據到調用環境。3）在建立存儲過程時，輸入參數的IN可以省略。第14頁，共20頁，2022年，5月20日，9點28分，星期一存儲過程-無輸入參數create or replace procedure pro_zmx(param out user1.cgmxb.mxid%type)as begin select avg(mxid) into param from cgmxb where cj=北京;end;第15頁，共20頁，2022年，5月20日，9點28分，星期一存儲過程-輸入輸出參數crea

11、te or replace procedure pro_zmx(param1 in user1.cgmxb.cj%type,param out user1.cgmxb.mxid%type)as begin select avg(mxid) into param from cgmxb where cj=param1;end; 第16頁，共20頁，2022年，5月20日，9點28分，星期一存儲過程 in out參數create or replace procedure pro_zmx(param in out user1.cgmxb.mxid%type)as begin select avg(mx

12、id) into param from cgmxb where mxid=param;end; 第17頁，共20頁，2022年，5月20日，9點28分，星期一觸發器當需要級聯操作的時候可能用到觸發器語法：create or replace trigger 觸發器名稱 before|after insert or update or delete of 字段名稱 on 表 begin PL/SQL語句 end第18頁，共20頁，2022年，5月20日，9點28分，星期一觸發器表觸發器行觸發器表create or replace trigger tri_zmxafter insert on cgmxbbegin insert into cgb (cid,zdr) values (1,2);end;insert into cgmxb (mxid,spmc,cj) values (112,2