declare
type t_cur is ref cursor;
c_sign_stat t_cur;
strSQL VARCHAR2(100);
tmp_sql varchar2(100):='sign_man=''zhansan'' or sign_man=''lisi'' or sign_man=''wangwu''';begin
strSQL:='SELECT * FROM gd_sign_in_tab WHERE sign_out_time IS NOT NUL and sign_in_time=to_date(i_begin_date,'yyyymm') and ' || tmp_sql;
open c_sign_stat for strSQL;
fetch XXX into XXX
close c_sign_stat;
end;
按照上面的例子,可以动态的给游标传进sql,但是我写的存储过程会先创建一个表,把表中的数据写到一个文件中,然后存储过程执行结束再删除这个表.
问题来了,我为我新建的表可以用动态游标,可是rowtype怎么定义?rowtype 只能在declare和begin块中间声明,可是那时的表还没有创建那?请问这个问题如何解决?
type t_cur is ref cursor;
c_sign_stat t_cur;
strSQL VARCHAR2(100);
tmp_sql varchar2(100):='sign_man=''zhansan'' or sign_man=''lisi'' or sign_man=''wangwu''';begin
strSQL:='SELECT * FROM gd_sign_in_tab WHERE sign_out_time IS NOT NUL and sign_in_time=to_date(i_begin_date,'yyyymm') and ' || tmp_sql;
open c_sign_stat for strSQL;
fetch XXX into XXX
close c_sign_stat;
end;
按照上面的例子,可以动态的给游标传进sql,但是我写的存储过程会先创建一个表,把表中的数据写到一个文件中,然后存储过程执行结束再删除这个表.
问题来了,我为我新建的表可以用动态游标,可是rowtype怎么定义?rowtype 只能在declare和begin块中间声明,可是那时的表还没有创建那?请问这个问题如何解决?
EXEC DBMS_OUTPUT.ENABLE(NULL);
DECLARE
TYPE assoc_array_str_type IS VARRAY(20) OF VARCHAR2(1000); c NUMBER;
d NUMBER;
j INTEGER;
col_cnt INTEGER;
f BOOLEAN;
rec_tab DBMS_SQL.DESC_TAB;
col_num NUMBER;
col_val1 VARCHAR2(1000);
col_val2 VARCHAR2(1000);
col_err NUMBER;
act_len INTEGER;
col_val_arr assoc_array_str_type;
PROCEDURE print_rec(rec in DBMS_SQL.DESC_REC) IS
BEGIN
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('col_type = '
|| rec.col_type);
DBMS_OUTPUT.PUT_LINE('col_maxlen = '
|| rec.col_max_len);
DBMS_OUTPUT.PUT_LINE('col_name = '
|| rec.col_name);
DBMS_OUTPUT.PUT_LINE('col_name_len = '
|| rec.col_name_len);
DBMS_OUTPUT.PUT_LINE('col_schema_name = '
|| rec.col_schema_name);
DBMS_OUTPUT.PUT_LINE('col_schema_name_len = '
|| rec.col_schema_name_len);
DBMS_OUTPUT.PUT_LINE('col_precision = '
|| rec.col_precision);
DBMS_OUTPUT.PUT_LINE('col_scale = '
|| rec.col_scale);
DBMS_OUTPUT.PUT('col_null_ok = ');
IF (rec.col_null_ok) THEN
DBMS_OUTPUT.PUT_LINE('true');
ELSE
DBMS_OUTPUT.PUT_LINE('false');
END IF;
END;
BEGIN
c := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(c, 'SELECT * FROM DBA_USERS', DBMS_SQL.NATIVE);
d := DBMS_SQL.EXECUTE(c);
DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);/*
* Following loop could simply be for j in 1..col_cnt loop.
* Here we are simply illustrating some of the PL/SQL table
* features.
*/
col_num := rec_tab.first;
IF (col_num IS NOT NULL) THEN
LOOP
print_rec(rec_tab(col_num));
col_num := rec_tab.next(col_num);
EXIT WHEN (col_num IS NULL);
END LOOP;
END IF;
col_val_arr:=assoc_array_str_type(null);
FOR J IN 1..col_cnt LOOP
COL_VAL_ARR.EXTEND;
DBMS_SQL.DEFINE_COLUMN (c, j,col_val_arr(j),1000);
END LOOP;
LOOP
j:=DBMS_SQL.FETCH_ROWS(c);
EXIT WHEN j=0;
FOR J IN 1..col_cnt LOOP
DBMS_SQL.COLUMN_VALUE(c,j,col_val_arr(j),col_err,act_len);
DBMS_OUTPUT.PUT(rec_tab(j).col_name||':'||col_val_arr(j)||',');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
END LOOP;
DBMS_SQL.CLOSE_CURSOR(c);
END;
/
其次,可以定义表类型,将游标的结果fetch到一个表类型里declare
v_row table%rowtype;open c_sign_stat for strSQL;
fetch XXX into v_rowclose c_sign_stat;
v_row.字段1