在oracle中动态添加where子句到游标

前端之家收集整理的这篇文章主要介绍了在oracle中动态添加where子句到游标前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我有plsql程序,它接受某些参数,例如v_name,v_country,v_type.

我希望有一个带有select语句的游标,如下所示:

select column from table1 t1,table2 t2
where t1.name = v_name
and t1.country = v_country
and t1.id = t2.id
and t2.type = v_type

如果某些参数为空,我只能在光标中添加相关的where子句吗?或者有更好的方法来实现这一目标吗?

使用它的最佳方法是使用DBMS_sql.

您创建一个表示您的sql语句的字符串.您仍然使用绑定变量.这是痛苦的.

它是这样的(我没有编译这个,但它应该是接近的): –

CREATE OR REPLACE FUNCTION find_country( v_name  t1.country%TYPE,v_type  t2.type%TYPE)  /* Hmm,column called type? */
DECLARE
  v_sql         varchar2(2000);
  v_select          INTEGER;   /* "Pointer" to a DBMS_sql select statement */
  v_execute         INTEGER;

BEGIN
  v_sql := 'select column from table1 t1,table2 t2 ||
           'where t1.id = t2.id';

  IF v_name IS NOT NULL THEN
    v_sql := v_sql || ' AND t1.country = :v_name'
  END IF;

  IF v_type IS NOT NULL THEN
    v_sql := v_sql || ' AND t2.type = :v_type';
  END IF;

  /* Setup Cursor */
  v_select := dbms_sql.open_cursor;     
  dbms_sql.parse( v_select,v_sql,DBMS_sql.native);

  IF v_name IS NOT NULL THEN
    dbms_sql.bind_variable( v_select,':v_name',v_name );
  END IF;

  IF v_type IS NOT NULL THEN
    dbms_sql.bind_variable( v_select,':v_type',v_type );
  END IF;

  DBMS_sql.DEFINE_COLUMN(v_select,1,v_column);  /* This is what we have selected */

  /* Return value from EXECUTE is undefined for a SELECT */     
  v_execute := DBMS_sql.EXECUTE( v_select );

  IF DBMS_sql.FETCH_ROWS( v_select ) > 0 THEN

    /* A row was found  
    DBMS_sql.COLUMN_VALUE( v_select,v_column);

    /* Tidy Up */
    DBMS_sql.CLOSE_CURSOR(v_select);

    RETURN v_ID_address;

  ELSE

     DBMS_sql.CLOSE_CURSOR(v_select);

     /* No row */
     RETURN NULL;
  END IF;

  EXCEPTION
    WHEN OTHERS THEN
      IF DBMS_sql.IS_open(v_select) THEN
        DBMS_sql.CLOSE_CURSOR(v_select);
      END IF;
      RAISE;
END;

与仅编写sql内联相比,这种方法非常痛苦,除非您有大量的列,否则使用以下语法编写几个不同的版本会更容易:

FOR r IN (SELECT blah FROM blah WHERE t1 = v_t1) LOOP
   func( r.blah );
END LOOP;

猜你在找的Oracle相关文章