Oracle數(shù)據(jù)庫(kù)之存儲(chǔ)過(guò)程和函數(shù)的基本語(yǔ)法

1、Oracle數(shù)據(jù)庫(kù)之存儲(chǔ)過(guò)程和函數(shù)的基本語(yǔ)法Oracle數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程(PROCEDURE)存儲(chǔ)過(guò)程，不僅可以簡(jiǎn)化客戶端程序的開(kāi)發(fā)和維護(hù)，還可以提應(yīng)程序的運(yùn)性能。-參數(shù)的create or replace procedure proc_printasbegindbms_output.put_line(這個(gè)是存儲(chǔ)過(guò)程);end;-執(zhí)exec proc_print;就會(huì)看到如下信息-anonymous block completed這是個(gè)存儲(chǔ)過(guò)程-參過(guò)程create or replace procedure insert_emp(v_empno in NUMBER,v_ename in VARCH

2、AR2,v_job in VARCHAR2,v_mgr in NUMBER,v_hiredate in DATE,v_sal in NUMBER,v_comm in NUMBER,v_deptno in NUMBER)asbegininsert into pub_emp values(v_empno,v_ename,v_job,v_mgr,v_hiredate,v_sal,v_comm,v_deptno);dbms_output.put_line(成功錄);exceptionwhen dup_val_on_index thenraise_application_error(-9999,過(guò)程執(zhí)異

3、常);end;-執(zhí)exec insert_emp(7865,李四,clerk,8000,09-6 -81,800,90,20);-顯結(jié)果-anonymous block completed成功錄-參和出參存儲(chǔ)過(guò)程create or replace procedure v_emp(v_empno in NUMBER,n OUT VARCHAR2)asbeginselect ename into n from pub_emp where empno = v_empno;dbms_output.put_line(n);end;-調(diào)var n varchar2exec v_emp(7521,:n);-

4、結(jié)果如下anonymous block completedWARD創(chuàng)建多個(gè)存儲(chǔ)過(guò)程后可以下語(yǔ)句查詢下我們創(chuàng)建的存儲(chǔ)過(guò)程select * from user_procedures where object_type=PROCEDURE-(PROCEDURE)定要寫(xiě),寫(xiě)會(huì)報(bào)錯(cuò)刪除存儲(chǔ)過(guò)程drop procedure 過(guò)程名函數(shù)(FUNCTION)-參函數(shù)create or replace function fun_dtimereturn varchar2isbeginreturn to_char(sysdate,yyyy年mmdd);end;-執(zhí)set serveroutput on;begind

5、bms_output.put_line(fun_dtime);end;create or replace function ret_emp_sal(v_ename varchar2)return numberisv_sal number(7,2);beginselect nvl(sal,0) into v_sal from pub_emp where lower(ename)=lower(v_ename);return v_sal;-必須要有返回值end;var vsla numberexec :vsla:=ret_emp_sal(WARD);print vsla-有參函數(shù)create or

6、replace function fun_info(v_empno number)return VARCHAR2isv_ename pub_emp.ename%TYPE;beginselect ename into v_ename from pub_emp where empno=v_empno;return v_ename; -必須要有返回值end;declarev_empno number:=7876;v_ename pub_emp.ename%TYPE;begin:v_name=fun_info(v_empno);dbms_output.put_line(名稱)end;var v_name varchar2exec :v_name:=fun_info(7876);print v_name通過(guò)上的 例我們可以總結(jié)下存儲(chǔ)過(guò)程和函數(shù)的區(qū)別和相同點(diǎn)存儲(chǔ)過(guò)程(PROCEDURE)和函數(shù)(FUNCTION)的區(qū)別：1.存儲(chǔ)過(guò)程必須單獨(dú)調(diào)，函數(shù)可以在查詢語(yǔ)句中直接調(diào)2.存儲(chǔ)過(guò)程是通過(guò)參數(shù)返回的，有個(gè)甚多個(gè)，函數(shù)有且只有個(gè)返回值3.存儲(chǔ)過(guò)程般是來(lái)完成特定的數(shù)據(jù)操作(如DDL語(yǔ)句等)，函數(shù)般情況下是來(lái)計(jì)算并返回個(gè)計(jì)算結(jié)果的。4.對(duì)于參函數(shù)的定義和調(diào)都可以沒(méi)有圓括號(hào)。但參存儲(chǔ)