在oracle中动态添加where子句到游标
发布时间:2020-12-12 15:13:57 所属栏目:百科 来源:网络整理
导读:我有plsql程序,它接受某些参数,例如v_name,v_country,v_type. 我希望有一个带有select语句的游标,如下所示: select column from table1 t1,table2 t2where t1.name = v_nameand t1.country = v_countryand t1.id = t2.idand t2.type = v_type 如果某些参数
我有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; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |