Oracle使用自定义函数动态查询(使用游标)

(作者:陈玓玏)
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这个东西,改天研究一下再写用法。

  • 3
    点赞
  • 2
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页
评论

打赏作者

小白白白又白cdllp

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值