:建立無參數的函數
CREATE OR REPLACE FUNCTION cur_datetime
RETURN VARCHAR
IS
BEGIN
RETURN TO_CHAR(sysdate
YYYY"年"MM"月"DD"日"HH″時"MI"分"SS"秒");
END;
/
:建立帶有輸入參數的函數
CREATE OR REPLACE FUNCTION get_sal(name VARCHAR)
RETURN NUMBER
AS
v_sal empsal%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE upper(ename)=upper(name);
RETURN v_sal;
END;
/
:建立帶有輸出參數的函數
CREATE OR REPLACE FUNCTION get_info
(eno NUMBERtitle OUT VARCHAR) RETURN VARCHAR
AS
name empename%TYPE;
BEGIN
SELECT enamejob INTO nametitle FROM emp
WHERE empno=eno;
RETURN name;
END;
/
:建立帶有輸入輸出參數的函數
CREATE OR REPLACE FUNCTION get_upd_info
(eno NUMBERsal_chg IN OUT NUMBER) RETURN VARCHAR
AS
name empename%TYPE;
BEGIN
UPDATE emp SET sal=sal+sal_chg WHERE empno=eno
RETURNING enamesal INTO namesal_chg;
RETURN name;
END;
/
:建立結果緩存函數
CREATE OR REPLACE FUNCTION get_name(no VARCHAR)
RETURN NUMBER RESULT_CACHE RELIES_ON(emp)
AS
v_name empename%TYPE;
BEGIN
SELECT ename INTO v_name FROM emp WHERE empno=no;
RETURN v_name;
END;
/
:調用無參數的函數
BEGIN
dbms_outputput_line(cur_datetime);
END;
/
:調用帶有輸入參數的函數
BEGIN
dbms_outputput_line(工資:||get_sal(&name));
END;
/
:調用帶有輸出參數的函數
DECLARE
v_name empename%TYPE;
v_job empjob%TYPE;
BEGIN
v_name:=get_info(&enov_job);
dbms_outputput_line(姓名:||v_name||崗位:||v_job);
END;
/
:調用帶有輸入輸出參數的函數
DECLARE
v_empno empempno%TYPE;
v_name empename%TYPE;
v_salchg empsal%TYPE;
BEGIN
v_empno:=&eno;
v_salchg:=&incre;
v_name:=get_upd_info(v_empnov_salchg);
dbms_outputput_line(姓名:||v_name||新工資:||v_salchg);
END;
/
:使用位置傳遞為參數傳遞變量和數據
SELECT get_sal(&name) 工資 FROM dual;
:使用名稱傳遞為參數傳遞變量和數據
VAR salary NUMBER
EXEC :salary:=get_sal(name=>&name)
:使用組合傳遞為參數傳遞變量和數據
VAR name VARCHAR()
VAR sal_chg NUMBER
EXEC :sal_chg:=
EXEC :name:=get_upd_info(&eno:sal_chg)
PRINT name sal_chg
:在sql語句中調用pl/sql函數
SELECT get_sal(name=>scott) salary FROM dual;
:使用異常處理
CREATE OR REPLACE FUNCTION get_sal(name VARCHAR)
RETURN NUMBER
AS
v_sal empsal%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp
WHERE upper(ename)=upper(name);
RETURN v_sal;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(該雇員不存在);
END;
/
:使用紀錄類型作為返回類型
CREATE OR REPLACE FUNCTION get_info
(eno NUMBER) RETURN emp%ROWTYPE
IS
emp_record emp%ROWTYPE;
BEGIN
SELECT * INTO emp_record FROM emp WHERE empno=eno;
RETURN emp_record;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(該雇員不存在);
END;
/
DECLARE
emp_record emp%ROWTYPE;
BEGIN
emp_record:=get_info(&eno);
dbms_outputput_line(姓名:||emp_recordename||部門號:||emp_recorddeptno);
END;
/
:使用集合類型作為返回類型
CREATE OR REPLACE TYPE ename_table_type IS TABLE OF VARCHAR();
/
CREATE OR REPLACE FUNCTION get_name
(dno NUMBER) RETURN ename_table_type IS
ename_table ename_table_type;
BEGIN
SELECT ename BULK COLLECT INTO ename_table FROM emp WHERE deptno=dno;
RETURN ename_table;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(該部門不存在);
END;
/
DECLARE
ename_table ename_table_type;
BEGIN
ename_table:=get_name(&dno);
FOR i IN ename_tableCOUNT LOOP
dbms_outputput_line(姓名||ename_table(i));
END LOOP;
END;
/
:刪除函數
DROP FUNCTION get_name;
:顯示編譯錯誤
SHOW ERRORS
:確定函數狀態
SELECT object_name FROM user_objects WHERE status=INVALID AND object_type=FUNCTION;
:編譯函數
ALTER FUNCTION get_info COMPILE;
:查看函數代碼
SELECT text FROM user_source WHERE name=GET_INFO;
From:http://tw.wingwit.com/Article/program/Oracle/201311/19063.html
