一：编写
CREATE [ OR REPLACE ] FUNCTION function_name
(argument1 [mode1] datatype1,
argument2 [mode2] datetype2,
...)
RETURN datatype
IS | AS
[local_variable_declarations;...]
BEGIN
--actions;
RETURN expression;
END [function_name];
建立函数的几点注意事项
1.指定参数数据类型时(argument)，不能指定其长度
2.函数头部必须指定return子句，函数体内至少要包含一条return语句
3.可以指定in参数，也可以指定out参数，以及in out 参数
4.可以为参数指定缺省值。指定缺省值时使用default关键字。如arg1 varchar2 default 'SCOTT'演示几个函数
IN,OUT,IN OUT是形参的模式。若省略，则为IN模式。IN模式的形参只能将实参传递给形参，进入函数内部，但只能读不能写，函数返回时实参的值不变。OUT模式的形参会忽略调用时的实参值（或说该形参的初始值总是NULL），但在函数内部可以被读或写，函数返回时形参的值会赋予给实参。IN OUT具有前两种模式的特性，即调用时，实参的值总是传递给形参，结束时，形参的值传递给实参。调用时，对于IN模式的实参可以是常量或变量，但对于OUT和IN OUT模式的实参必须是变量。
--无参数
scott@ORCL> create or replace function get_user
2 return varchar2
3 is
4 v_user varchar2(20);
5 begin
6 select username into v_user from user_users;
7 return v_user;
8 end;
9 /--
--使用全局变量接收函数的返回值
scott@ORCL>var v1 varchar2(20)
scott@ORCL>exec:v1:=get_user
PL/SQL procedure successfully completed.
scott@ORCL>print v1;
V1
--------------------------------
SCOTT--使用本地变量接收函数的返回值
scott@ORCL>set serveroutput on;
scott@ORCL>declareuser_name varchar2(20);
2 begin
3 user_name:=get_user();
4 dbms_output.put_line('Current user: '||user_name);
5 end;
6 /
Currentuser: SCOTT
PL/SQL procedure successfully completed.--在SQL语句中直接调用函数
scott@ORCL>select get_user from dual;
GET_USER
---------------------
SCOTT--使用dbms_output调用函数(此调用作为存储过程的一个参数来进行调用)
scott@ORCL>set serveroutput on;
scott@ORCL>exec dbms_output.put_line('Current user: '||get_user);
Currentuser: SCOTT--带in参数
scott@ORCL> create or replace function raise_sal(name in varchar2) --注意此处定义时参数并为指定类型的长度
2 return number
3 as
4 v_sal emp.sal%type;
5 begin
6 select sal*1.2 into v_sal from emp
7 where upper(ename)=upper(name);
8 return v_sal;
9 exception
10 when no_data_found then
11 raise_application_error(-20000,'Current Employee is not exists');
12 end;
13 /
Function created.
scott@ORCL> select sal,raise_sal('SCOTT') from emp where ename='SCOTT';
SAL RAISE_SAL('SCOTT')
---------- ------------------
3100 3720
scott@ORCL> select raise_sal('Robinson') from dual;
select raise_sal('Robinson') from dual
*
ERROR at line 1:
ORA-20000: Current Employee is not exists
ORA-06512: at "SCOTT.GET_SAL", line 11--带out参数
scott@ORCL> create or replace function get_info
2 (name varchar2,title out varchar2)
3 return varchar2
4 as
5 deptname dept.dname%type;
6 begin
7 select e.job,d.dname into title,deptname
8 from emp e inner join dept d
9 on e.deptno=d.deptno
10 where upper(e.ename)=upper(name);
11 return deptname;
12 exception
13 when no_data_found then
14 raise_application_error(-20000,'Current Employee is not exists');
15 end;
16 /
Function created.
注意对于使用out参数的函数，不能使用SQL语句来调用。而必须定义变量接收out参数和函数的返回值。
调用如下
scott@ORCL> var job varchar2(20);
scott@ORCL> var dname varchar2(20);
scott@ORCL> exec :dname:=get_info('scott',:job);
PL/SQL procedure successfully completed.
scott@ORCL> print dname job;
DNAME
--------------------------------
RESEARCH
JOB
--------------------------------
ANALYST--带有in out 参数
scott@ORCL> create or replace function comp
2 (num1 number,num2 in out number)
3 return number
4 as
5 v_result number(6);
6 v_remainder number;
7 begin
8 v_result:=num1*num2;
9 v_remainder:=mod(num1,num2);
10 num2:=v_remainder;
11 return v_result;
12 exception
13 when zero_divide then
14 raise_application_error(-20000,'Divison by zero');
15 end;
16 /
Function created.
scott@ORCL> var result1 number;
scott@ORCL> var result2 number;
scott@ORCL> exec :result2:=10
PL/SQL procedure successfully completed.
scott@ORCL> exec :result1:=comp(16,:result2);
PL/SQL procedure successfully completed.
scott@ORCL> print result1 result2;
RESULT1
----------
160
RESULT2
----------
6二：创建方式
有几种方式可以创建函数
1：直接在sqlplus中编写创建
SQL> CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2) 2 RETURN PLS_INTEGER 3 AS 4 words PLS_INTEGER := 0; 5 len PLS_INTEGER := NVL (LENGTH (str), 0); 6 inside_a_word BOOLEAN; 7 BEGIN 8 FOR i IN 1 .. len + 1 9 LOOP 10 IF ASCII (SUBSTR (str, i, 1)) < 33 OR i > len 11 THEN 12 IF inside_a_word 13 THEN 14 words := words + 1; 15 inside_a_word := FALSE; 16 END IF; 17 ELSE 18 inside_a_word := TRUE; 19 END IF; 20 END LOOP; 21 22 RETURN words; 23 END; 24 / Function created. SQL>
2：现在文件中编写，然后@执行脚本
[oracle@centos-fuwenchao ~]$ more save.sql
CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2)
RETURN PLS_INTEGER
AS
words PLS_INTEGER := 0;
len PLS_INTEGER := NVL (LENGTH (str), 0);
inside_a_word BOOLEAN;
BEGIN
FOR i IN 1 .. len + 1
LOOP
IF ASCII (SUBSTR (str, i, 1)) < 33 OR i > len
THEN
IF inside_a_word
THEN
words := words + 1;
inside_a_word := FALSE;
END IF;
ELSE
inside_a_word := TRUE;
END IF;
END LOOP;
RETURN words;
END;
/
[oracle@centos-fuwenchao ~]$ pwd
/home/oracle
[oracle@centos-fuwenchao ~]$然后登录到sqlplus中@执行（注意此时文件路径需在当前路径下，或者使用全路径，或者设置sqlpath环境变量）
SQL> @save.sql Function created. SQL>
如果想看源代码
SQL> set echo on
三：调用
BEGIN
DBMS_OUTPUT.put_line (
'There are ' || wordcount (fuwenchao) || ' words in a tab'
);
END;
/
---
SELECT isbn, wordcount (description)
FROM books
/
四：查看
查看内容的几种方法：
1：
SQL> select line,text from user_source where name='WORDCOUNT';
LINE
----------
TEXT
--------------------------------------------------------------------------------
1
FUNCTION wordcount (str IN VARCHAR2)
2
RETURN PLS_INTEGER
3
AS
LINE
----------
TEXT
--------------------------------------------------------------------------------
4
words PLS_INTEGER := 0;可以看到，显示界面不是很友好
2：
SQL> exec dbms_output.put_line(dbms_metadata.get_ddl(object_type => 'FUNCTION',name => 'WORDCOUNT'));
CREATE OR REPLACE FUNCTION "SYS"."WORDCOUNT" (str IN VARCHAR2)
RETURN
PLS_INTEGER
AS
words PLS_INTEGER := 0;
len
PLS_INTEGER := NVL (LENGTH (str), 0);
inside_a_word BOOLEAN;
BEGIN
FOR i
IN 1 .. len + 1
LOOP
IF ASCII (SUBSTR (str, i, 1)) < 33 OR i > len
THEN
IF inside_a_word
THEN
words := words + 1;
inside_a_word := FALSE;
END IF;
ELSE
inside_a_word :=
TRUE;
END IF;
END LOOP;
RETURN words;
END;
PL/SQL procedure successfully completed.
SQL>3：这种显示很奇怪，只有一行
SQL> SELECT * FROM ALL_SOURCE where TYPE='FUNCTION' AND TEXT LIKE '%WORDCOUNT%';
no rows selected
SQL> c/WORDCOUNT/wordcount
1* SELECT * FROM ALL_SOURCE where TYPE='FUNCTION' AND TEXT LIKE '%wordcount%'
SQL> /
OWNER NAME TYPE
------------------------------ ------------------------------ ------------
LINE
----------
TEXT
--------------------------------------------------------------------------------
SYS WORDCOUNT FUNCTION
1
FUNCTION wordcount (str IN VARCHAR2)
SQL>--但是这样的话就行了
SQL> c/*/text
1* SELECT text FROM ALL_SOURCE where TYPE='FUNCTION' AND name LIKE '%WORDCOUNT%'
SQL> /
TEXT
--------------------------------------------------------------------------------
FUNCTION wordcount (str IN VARCHAR2)
RETURN PLS_INTEGER
AS
words PLS_INTEGER := 0;
len PLS_INTEGER := NVL (LENGTH (str), 0);
inside_a_word BOOLEAN;
BEGIN
FOR i IN 1 .. len + 1
LOOP
IF ASCII (SUBSTR (str, i, 1)) < 33 OR i > len
THEN
TEXT
--------------------------------------------------------------------------------
IF inside_a_word
THEN
words := words + 1;
inside_a_word := FALSE;
END IF;
ELSE
inside_a_word := TRUE;
END IF;
END LOOP;
RETURN words;
TEXT
--------------------------------------------------------------------------------
END;
23 rows selected.4：显示调用接口概要信息
SQL> desc wordcount FUNCTION wordcount RETURNS BINARY_INTEGER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- STR VARCHAR2 IN SQL>
5：小总结：
查看自定义函数名的话，用
select OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE=’FUNCTION’;
或者select OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE=’FUNCTION’;
或者select OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE=’FUNCTION’;
函数的具体内容的话：
select TEXT FROM USER_SOURCE WHERE NAME=函数名;
select TEXT FROM ALL_SOURCE WHERE NAME=函数名;
select TEXT FROM DBA_SOURCE WHERE NAME=函数名;
五：授权
SQL> grant execute on wordcount to hr; Grant succeeded. SQL> SELECT table_name, grantee, privilege 2 FROM user_tab_privs_made 3 WHERE table_name = 'WORDCOUNT' 4 / TABLE_NAME GRANTEE ------------------------------ ------------------------------ PRIVILEGE ---------------------------------------- WORDCOUNT HR EXECUTE SQL>
六：删除
七：注意事项
1.函数的调用(其具体调用方法参照上面的演示)
a.使用全局变量接收函数的返回值
b.使用本地变量接受函数的返回值
c.在SQL语句中直接调用函数
d.使用dbms_output调用函数
注：函数在调用的时候需要按位置指定参数，没有存储过程参数传递灵活
必须具有execute 函数的权限
2.函数在SQL中调用的主要场合
由于函数必须要返回数据，因此只能作为表达式的一部分调用。此外函数可以在SQL语句的以下部分调用
a. select 命令的选择列表或子查询中
b. 条件表达式where, having子句中
c. connect by , start with ,order by 以及group by 子句中
d. insert 命令的values子句中
f. update 命令的set 子句中
3.函数在SQL中调用的限制
a. SQL语句中只能调用存储在服务器端的函数，而不能调用存储于客户端的函数
b. SQL语句中调用的函数只能带有输入参数IN，而不能带有输出参数OUT 以及输入输出参数IN OUT
c. SQL语句中调用的函数只能使用SQL支持的标准数据类型，不能使用PL/SQL特有的类型，如boolean,table,record等
d. SQL语句中调用的函数不能包含insert,update和delete 语句(注意理解,这里的DML指的是函数中使用DML，非外部调用SQL语句@20130905)
参考：
乐沙弥：http://blog.csdn.net/leshami/article/details/6069531
穷追猛打：http://www.cnblogs.com/huyong/archive/2011/04/28/2031541.html
