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