Oracle數(shù)據(jù)庫之存儲過程和函數(shù)的基本語法

1、Oracle數(shù)據(jù)庫之存儲過程和函數(shù)的基本語法Oracle數(shù)據(jù)庫存儲過程(PROCEDURE)存儲過程，不僅可以簡化客戶端程序的開發(fā)和維護，還可以提應(yīng)程序的運性能。-參數(shù)的create or replace procedure proc_printasbegindbms_output.put_line(這個是存儲過程);end;-執(zhí)exec proc_print;就會看到如下信息-anonymous block completed這是個存儲過程-參過程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,過程執(zhí)異

3、常);end;-執(zhí)exec insert_emp(7865,李四,clerk,8000,09-6 -81,800,90,20);-顯結(jié)果-anonymous block completed成功錄-參和出參存儲過程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)建多個存儲過程后可以下語句查詢下我們創(chuàng)建的存儲過程select * from user_procedures where object_type=PROCEDURE-(PROCEDURE)定要寫,寫會報錯刪除存儲過程drop procedure 過程名函數(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通過上的 例我們可以總結(jié)下存儲過程和函數(shù)的區(qū)別和相同點存儲過程(PROCEDURE)和函數(shù)(FUNCTION)的區(qū)別：1.存儲過程必須單獨調(diào)，函數(shù)可以在查詢語句中直接調(diào)2.存儲過程是通過參數(shù)返回的，有個甚多個，函數(shù)有且只有個返回值3.存儲過程般是來完成特定的數(shù)據(jù)操作(如DDL語句等)，函數(shù)般情況下是來計算并返回個計算結(jié)果的。4.對于參函數(shù)的定義和調(diào)都可以沒有圓括號。但參存儲