Oracle使用自定义函数动态查询（使用游标）

系统管理员 2022-05-10 04:46 871阅读 0赞

**（作者：陈玓玏）**  
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这个东西，改天研究一下再写用法。