oracle中的函數開發

標籤：style   color   io   os   使用   ar   資料   sp   cti   

ORACLE 函數開發

1、建立函數的文法：

CREATE [OR REPLACE] FUNCTION 函數名

[ (arg1 [mode] datatype[,........])] --當指定參數資料類型時，不能指定其長度。mode值：IN、OUT、IN OUT，當mode 未指定時，表示為IN

RETURN datatype --用於指定函數傳回值的資料類型，不能指定其長度

IS|AS

pl/sql block; --當建立函數時，在函數頭部必須要帶有RETURN子句，在函數體內至少要包含一條RETURN語句。

例1、無參函數

SQL> CREATE OR REPLACE function HX_01 --建立HX_01函數
2 RETURN VARCHAR2
3 IS
4 v_name varchar2(20);
5 begin
6 select ename into v_name from emp where empno=7369;
7 return v_name;
8 end;
9 /

Function created

SQL> select hx_01 from dual; --執行函數

HX_01
--------------------------------------------------------------------------------
SMITH

例2、帶有一個IN參數

SQL> CREATE OR REPLACE function HX_02 --建立HX_02函數
2 (v_no in emp.empno%type)
3 RETURN VARCHAR2
4 IS
5 v_name varchar2(20);
6 v_sal emp.sal%type;
7 v_dname dept.dname%type;
8 v_return varchar2(100);
9 begin
10 select a.ename,a.sal,b.dname into v_name,v_sal,v_dname from emp a,dept b where a.deptno=b.deptno and empno=v_no;
11 v_return:=‘姓名：‘||rpad(v_name,8)||LPAD(‘工資：‘,6)||v_sal||lpad(‘部門‘,6)||v_dname;
12 return v_return;
13 end;
14 /

Function created

SQL> begin
2 dbms_output.put_line(hx_02(7566)); --執行函數
3 end;
4
5 /

PL/SQL procedure successfully completed
輸出：姓名：JONES 工資：2975 部門RESEARCH

或：

SQL> select hx_02(7566) from dual;

HX_02(7566)
--------------------------------------------------------------------------------
姓名：JONES 工資：2975 部門RESEARCH

例3、帶有IN、OUT參數

SQL> CREATE FUNCTION hx_03 --建立HX_03函數
2 (v_n1 emp.empno%type,v_n2 out emp.ename%type,v_n3 out emp.sal%type,v_n4 out dept.dname%type)
3 return emp.job%type
4 as
5 job emp.job%type;
6 begin
7 select a.job,a.ename,a.sal,b.dname into job,v_n2,v_n3,v_n4
8 from emp a,dept b
9 where a.deptno=b.deptno
10 and a.empno=v_n1;
11 return job;
12 end;
13 /

Function created

declare
v1 emp.job%type;
v2 emp.ename%type;
v3 emp.sal%type;
v4 dept.dname%type;
begin
v1:=hx_03(7566,v2,v3,v4); --使用剛才定義的變數接收函數的輸出值
dbms_output.put_line(‘姓名:‘||v2||‘ ‘||‘職業:‘||v1||‘ ‘||‘薪水:‘||v3||‘ ‘||‘部門:‘||v4);
end;

輸出：姓名:JONES 職業:MANAGER 薪水:2975 部門:RESEARCH

例4、帶有in out參數

CREATE OR REPLACE FUNCTION HX_04(v_name in out varchar2) --建立HX_04函數
RETURN VARCHAR2
AS
BEGIN
SELECT DEPT.DNAME INTO V_NAME FROM DEPT,EMP WHERE DEPT.DEPTNO=EMP.DEPTNO AND EMP.ENAME=V_NAME;
RETURN NULL;
END;

DECLARE --執行函數
A VARCHAR2(20);
B VARCHAR2(20);
BEGIN
A:=‘WARD‘;
B:=HX_04(A);
DBMS_OUTPUT.put_line(‘A:=‘||A||‘ B:=‘||B);
END;

輸出：A:=SALES B:=

2、刪除函數文法

drop function 函數名;

oracle中的函數開發