||是Oracle的连字符,用于字符串的拼接!V_SQL := 'SELECT count(*) into recCount from '||tableName; 之后的 V_SQL就是SELECT count(*) into recCount from tableName(此时的tableName已是变量值!)
create procedure name_pro(p_table in varchar2,p_count out number) as str varchar2(50); begin str:='select count(*) from '||p_table; execute immediate str into p_count; end; /
原因:在存储过程中,直接写from某张表,这张表在静态编译时被确定是否合法,若不合法,则报错。所以,你用变量名代替,系统认为是一个表名,在静态编译时到数据库中寻找这张表,这当然出错了。
8i及以上版本。可以用execute immediate 'sql string'。
execute immediate 'select count(*) from '||t_tablename into recCount;即可。
8i以下版本用dbms_sql包实现,相对烦琐。
之后的
V_SQL就是SELECT count(*) into recCount from tableName(此时的tableName已是变量值!)
as
str varchar2(50);
begin
str:='select count(*) from '||p_table;
execute immediate str into p_count;
end;
/