函式與過程一樣，結構類似，有3中引數模型。以下是建立一個函式的過程。
在編寫的過程中發現錯誤並修改程式重新執行。
----建立函式：

---定義函式的語句格式：

Create or replace function function_name

[(argument_name [in |out |in out ] argument_type [,... ...]) ]

Return datatype

As | is

Begin

Function_body

Return expression;

End [function_name];

#以上就是建立並定義函式的語法。

---建立簡單的有參函式：

--檢視錶emp的表結構：

scott@PROD>desc emp;

Name Null? Type

----------------------------------------- -------- ----------------------------

EMPNO NOT NULL NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(9)

MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

DEPTNO NUMBER(2)

--檢視emp表的記錄：

scott@PROD>select empno,ename,job,sal,comm,deptno

2 from emp;

EMPNO ENAME JOB SAL COMM DEPTNO

---------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 800 20

7499 ALLEN SALESMAN 1600 300 30

7521 WARD SALESMAN 1250 500 30

7566 JONES MANAGER 2975 20

7654 MARTIN SALESMAN 1250 1400 30

7698 BLAKE MANAGER 2850 30

7782 CLARK MANAGER 2450 10

7788 SCOTT ANALYST 3000 20

7839 KING PRESIDENT 5000 10

7844 TURNER SALESMAN 1500 0 30

7876 ADAMS CLERK 1100 20

EMPNO ENAME JOB SAL COMM DEPTNO

---------- ---------- --------- ---------- ---------- ----------

7900 JAMES CLERK 950 30

7902 FORD ANALYST 3000 20

7934 MILLER CLERK 1300 10

14 rows selected.

#表中共有14條記錄。

--建立函式：

scott@PROD>create or replace function income(v_no numner)

2 return number

3 as

4 v_salary emp.sal%type;

5 v_bonus emp.comm%type;

6 v_income emp.sal%type;

7 as

8 begin

9 select sal,nvl(comm,0) into v_salary,v_bonus

10 from emp where empno =v_no;

11 v_income = v_salary + v_bonus;

12 return v_income;

13 exception

14 when no_data_found then

15 dbms_output.put_line('There is no this empno in this table!');

16 end income;

17 /

Warning: Function created with compilation errors.

#編寫過程中有錯誤。

scott@PROD>show error #檢視程式中的錯誤

Errors for FUNCTION INCOME:

LINE/COL ERROR

-------- -----------------------------------------------------------------

7/1 PLS-00103: Encountered the symbol "AS" when expecting one of the

following:

begin function pragma procedure subtype type <an identifier>

<a double-quoted delimited-identifier> current cursor delete

exists prior

11/10 PLS-00103: Encountered the symbol "=" when expecting one of the

following:

:= . ( @ % ;

The symbol ":= was inserted before "=" to continue.

LINE/COL ERROR

-------- -----------------------------------------------------------------

16/11 PLS-00103: Encountered the symbol "end-of-file" when expecting

one of the following:

begin function pragma procedure

#編寫函式的過程中出錯了。

--修改函式的程式重新執行：

scott@PROD>create or replace function income(v_no number)

2 return number

3 as

4 v_salary emp.sal%type;

5 v_bonus emp.comm%type;

6 v_income emp.sal%type;

7 begin

8 select sal,nvl(comm,0) into v_salary,v_bonus

9 from emp where empno =v_no;

10 v_income := v_salary + v_bonus;

11 return v_income;

12 exception

13 when no_data_found then

14 dbms_output.put_line('There is no this empno in this table!');

15 end income;

16 /

Function created.

#修改後函式成功建立。

--呼叫函式：

scott@PROD>set serveroutput on

scott@PROD>begin

2 dbms_output.put_line('The income of the employee is: '|| income(7654));

3 end;

4 /

The income of the employee is: 2650

PL/SQL procedure successfully completed.

#函式執行完畢。

--使用SQL語句直接檢視工號為7654號的工資與補貼，以及兩個之和：

scott@PROD>select empno,ename,sal,comm,nvl(sal+comm,sal)

2 from emp where empno = 7654;

EMPNO ENAME SAL COMM NVL(SAL+COMM,SAL)

---------- ---------- ---------- ---------- -----------------

7654 MARTIN 1250 1400 2650

--再次呼叫函式檢視員工號為7839號的員工收入：

scott@PROD>begin

2 dbms_output.put_line('The income of the employee is: '|| income(7839));

3 end;

4 /

The income of the employee is: 5000

PL/SQL procedure successfully completed.

#函式執行完畢。

--使用SQL語句直接檢視工號為7839號員工的收入情況：

scott@PROD>select empno,ename,sal,comm,nvl(sal+comm,sal)

2 from emp where empno = 7839;

EMPNO ENAME SAL COMM NVL(SAL+COMM,SAL)

---------- ---------- ---------- ---------- -----------------

7839 KING 5000 5000

#兩個符合。

#從上述可以看出，函式建立成功。

建立函式

相關文章