方法 一:帶入與 TABLE 同型態的變數
CREATE OR REPLACE FUNCTION F_Get_Cust_Name (V_NO IN CUSTOMER.NO%TYPE,V_COMPNO IN CUSTOMER.COMPNO%TYPE)
RETURN CUSTOMER.NAME%TYPE
IS
V_NAME CUSTOMER.NAME%TYPE;
BEGIN
SELECT CUSTOMER.NAME INTO V_NAME
FROM CUSTOMER
WHERE
NO=V_NO AND COMPNO=V_COMPNO ;
RETURN V_NAME;
EXCEPTION WHEN NO_DATA_FOUND THEN RETURN ' '; ---錯誤回空白
END;
方法 二:帶入自定型態的變數
CREATE OR REPLACE FUNCTION F_Get_Cust_Name (V_NO IN VARCHAR2,V_COMPNO IN VARCHAR2)
RETURN VARCHAR2
IS
V_NAME VARCHAR2;
BEGIN
SELECT CUSTOMER.NAME INTO V_NAME
FROM CUSTOMER
WHERE
NO=V_NO AND COMPNO=V_COMPNO ;
RETURN V_NAME;
EXCEPTION WHEN NO_DATA_FOUND THEN RETURN ' '; ---錯誤回空白
END;
例：
--1為正確登陸
--2為密碼不正確
--3為賬戶不存在
--返回數值型
create or replace function GetAccount(acc_id in MEMBERS.ACCOUNT_ID%type,acc_pwd in MEMBERS.ACCOUNT_PASSWORD%type)
return number
is
v_result number;
v_count number;
v_acc_pwd MEMBERS.ACCOUNT_PASSWORD%type;
begin
select count(*) as count, MEMBERS.ACCOUNT_PASSWORD into v_count,v_acc_pwd
from MEMBERS
where MEMBERS.ACCOUNT_ID=acc_id
group by MEMBERS.ACCOUNT_PASSWORD;
if v_count>0
then
if v_acc_pwd=acc_pwd then
v_result:=1;
else
v_result:=2;
end if;
else
v_result:=3;
end if;
return v_result;
EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 3;
end;
