(作者:陈玓玏)
SQL的自定义函数允许我们在执行SQL程序时动态地传入一些参数,把一些字段名、表名、限制条件等变为变量,既能提高一些场景下的编码效率,也能提高一些执行效率。除了自定义函数,动态SQL语句、存储过程也能实现同样的功能,不过这里我们先只看自定义函数。
一、输出结果为单个值
--返回单个值:
--创建函数:
--格式:CREATE OR REPLACE FUNCTION 函数名(输入参数名 输入参数类型)
CREATE OR REPLACE FUNCTION fun_getSal(name VARCHAR2)
--函数必须定义返回参数,否则报错,存储过程就不需要,这是重要区别
RETURN NUMBER
AS v_sal tableName.colName%TYPE;
BEGIN
--from前面这个过程把取得的字段写入要返回的变量里
SELECT colName INTO v_sal FROM tableName WHERE id=name and rownum=1;
RETURN v_sal;
END;
--执行
select fun_getSal(name=>112) as colName from dual;
就上面这段代码,如果你不加限制条件rounum=1,是会报错的,因为这种方法的返回参数是一个值,不能接受返回多行结果。
二、输出结果为多行
如果想要返回多行结果,需要用到游标,也就是,通过游标来实现查询,并且把游标作为返回参数,逐行输出。使用方法如下:
--返回多行值但是不能形成表:
--定义函数
CREATE OR REPLACE FUNCTION fun_getSal_multiLines(name VARCHAR2)
RETURN SYS_REFCURSOR is P_RESULT_SET_O SYS_REFCURSOR;
BEGIN
--打开游标
OPEN P_RESULT_SET_O FOR
--也可以把下面这句sql语句复制给一个varchar2对象,不过记得定义这个对象
SELECT colName FROM tableName WHERE id=name;
RETURN P_RESULT_SET_O;
END fun_getSal_multiLines;
--执行:
select fun_getSal_multiLines(name=>112) as colName from dual;
上面同功能,但是把SQL语句放到一个字符串对象里的写法:
--返回结果跟上面一样的形式
--定义函数
CREATE OR REPLACE FUNCTION fun_getSal_multiLines(name VARCHAR2)
RETURN SYS_REFCURSOR is P_RESULT_SET_O SYS_REFCURSOR;
X_SQL varchar2(10000);
BEGIN
--OPEN P_RESULT_SET_O FOR
X_SQL :='SELECT colName FROM tableName WHERE id='||name;
OPEN P_RESULT_SET_O FOR X_SQL;
RETURN P_RESULT_SET_O;
END fun_getSal_multiLines;
--执行:
select fun_getSal_multiLines(name=>112) as colName from dual;
这个结果虽然能返回多行,但是多行的结果不能形成表格的形式,貌似是要用到pipelined这个东西,改天研究一下再写用法。