This example illustrates how you can create and use dynamic cursor in Oracle PL/SQL. The example is pretty simple, but I hope you can get the idea and apply it to more complicated cases. DECLARE t_cursorISREF C
create or replace package dynamic_cursor is type t_crs isref cursor; procedure dyn_sel ( tab_name in varchar2, field_name in varchar2, val in varchar2, crs in out t_crs); procedure openCursor; end dynamic_cursor; / create or replace package body dynamic_cursor as procedure dyn_sel (...
DECLAREl_sql_cursorPLS_INTEGER;l_ref_cursorSYS_REFCURSOR;l_returnPLS_INTEGER;l_col_cntPLS_INTEGER;l_desc_tabDBMS_SQL.desc_tab;l_countNUMBER;l_EMPLOYEE_IDEMPLOYEES.EMPLOYEE_ID%TYPE;l_FIRST_NAMEEMPLOYEES.FIRST_NAME%TYPE;l_LAST_NAMEEMPLOYEES.LAST_NAME%TYPE;BEGIN-- 执行过程l_sql_cursor:=DBMS_...
Re: Dynamic Cursor in Stored Procedure 2148 Peter Brawley February 23, 2009 02:58PM Sorry, you can't reply to this topic. It has been closed. Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessar...
This ensures that * ORACLE does not keep parse locks on tables after data * manipulation operations, so that subsequent data definition * operations on those tables do not result in a parse-lock * error. */ EXEC ORACLE OPTION (RELEASE_CURSOR=YES); void dyn_error(); main() { /* ...
in_cursor_id:=DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(ln_cursor_id,lv_sql,DBMS_SQL.NATIVE); DBMS_SQL.BIXD_VARLABLE(ln_cursor_id:‘empno‘,1001); DBMS_SQL.DEFINE_COLUMN(ln_cursor_ici,1,ln_emp_name); DBMS_SQL.DEFINE_COLUMN(ln_cursor_id,2,ln_emp_no); ...
在上面的示例中,首先使用OPEN_CURSOR函数打开一个游标,然后使用PARSE函数解析SQL语句,使用BIND_VARIABLE函数绑定变量的值,使用DEFINE_COLUMN函数定义结果列,使用EXECUTE函数执行SQL语句,使用FETCH_ROWS函数获取结果行数,最后使用COLUMN_VALUE函数获取结果值。 总结:生成动态PL/SQL可以使用动态SQL语句或者DBMS_SQL包。动态SQL...
cursor_name INTEGER; rows_processed INTEGER; v_stmt CLOB := NULL; v_role VARCHAR2(20) := 'CPPM_ADMIN'; v_user VARCHAR2(20) := 'wbmarsh1'; BEGIN BEGIN cursor_name := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(cursor_name,'GRANT :role TO :user',DBMS_SQL.NATIVE); ...
1) Dynamic Cursor 动态游标 1. Analysis and Application of Oracle Blockade andDynamic Cursor; Oracle封锁及动态游标技术分析与应用 2. Aimed at database comparative problem in database development project, a kind of database auto compare procedure has been designed And some development technologies and...
Re: Dynamic Cursor in Stored Procedure 2148 Peter Brawley February 23, 2009 02:58PM Sorry, you can't reply to this topic. It has been closed. Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessar...