Select count(*) INTO n_count from hw_table where calling=calling_no;
hw_table是一个变量,动态的表名,ORACLE会将hw_table认为是直接表名,请教如何处理。
我用v_cursor:=dbms_sql.open_cursor;
sql_statement :='select count(*) from :p_hw_table where calling=:p_calling_no';
dbms_sql.parse(v_cursor,sql_statement,dbms_sql.v7);
dbms_sql.bind_variable(v_cursor,':p_hw_table',hw_table);
dbms_sql.bind_variable(v_cursor,':p_calling_no',calling_no);
DBMS_SQL.DEFINE_COLUMN(v_cursor,1,my_count);
ret:=dbms_sql.execute(sql_statement);
loop
if dbms_sql.fetch_rows(sql_statement) = 0 then
exit;
end if;
dbms_sql.COLUMN_VALUE(v_cursor,1, my_count);
end loop;
可惜在dbms_sql.parse就不通过了。
hw_table是一个变量,动态的表名,ORACLE会将hw_table认为是直接表名,请教如何处理。
我用v_cursor:=dbms_sql.open_cursor;
sql_statement :='select count(*) from :p_hw_table where calling=:p_calling_no';
dbms_sql.parse(v_cursor,sql_statement,dbms_sql.v7);
dbms_sql.bind_variable(v_cursor,':p_hw_table',hw_table);
dbms_sql.bind_variable(v_cursor,':p_calling_no',calling_no);
DBMS_SQL.DEFINE_COLUMN(v_cursor,1,my_count);
ret:=dbms_sql.execute(sql_statement);
loop
if dbms_sql.fetch_rows(sql_statement) = 0 then
exit;
end if;
dbms_sql.COLUMN_VALUE(v_cursor,1, my_count);
end loop;
可惜在dbms_sql.parse就不通过了。
改为sql_statement :='select count(*) from ' ¦ ¦tbname ¦ ¦'where calling=:p_calling_no';
dbms_sql.parse通过了,但是dbms_sql.execute(sql_statement); 执行跳到异常
execute immediate 'select count(*) from ' || hw_table || ' where calling=' || calling_no
into my_count;
就可以了
没必要用dbms_sql package