创建函数

函数与过程一样，结构类似，有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

#两个符合。

#从上述可以看出，函数创建成功。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31392094/viewspace-2128379/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31392094/viewspace-2128379/