execute immediate 'select * from :a t' using '***';
还是用参数传进去拼出SQL执行吧
shell里面,假设给&threads,&successMsg传值 sqlpgm="$reportDir/$PGM.${runDateTime:-}.sql" typeset successMsg="Success: Updating the Ranking table." if [[ $returnCode -eq 0 ]]; then IsFile ${DWSUMTBLS_SQL-}/tixd999d_common_ExecRanking.sql returnCode=$? fi if [[ $returnCode -eq 0 ]]; then cat > $sqlpgm <<-EOF DEFINE successMsg="$successMsg" DEFINE threads=$numberOfThreads DEFINE EOF cat ${DWSUMTBLS_SQL-}/tixd999d_common_ExecRanking.sql >> $sqlpgm || \ returnCode=1 if [[ $returnCode -eq 0 ]]; then FormatMsg -u1 -p$PGM -l$LINENO "Updating the ranking table" ExecSqlplus -p$sqlpgm -m"$successMsg" -l$LINENO || \ returnCode=$? if [[ $returnCode -ne 4 && $returnCode -ne 0 ]]; then FormatMsg -u2 -p$PGM -l$LINENO "Error: Failed updating the ranking table." fi fi RemoveFile $sqlpgm fi
create or replace procedure p_test (strTable in varchar2) as num number; begin execute immediate 'select count(*) from ' || strTable into num; dbms_output.put_line(num); end; /exec p_test('***');
程序调用的话不要用这个 用绑定变量替换 若要替换的是字段名或表名,则需要使用动态语句 execute immediate 'select * from '||tname;
如果变量为表名,字段名时,需要用动态的sql执行execute immediate 'select * from table';
执行时会跳出窗口,让你输入一个字符串来代替&tname的值
后面那个&应该去掉,要不会出错
哦 对 是多了个最后的&,哈哈.那如果用程序调用如何传入这个tname呢?
还是用参数传进去拼出SQL执行吧
typeset successMsg="Success: Updating the Ranking table." if [[ $returnCode -eq 0 ]]; then
IsFile ${DWSUMTBLS_SQL-}/tixd999d_common_ExecRanking.sql
returnCode=$?
fi if [[ $returnCode -eq 0 ]]; then
cat > $sqlpgm <<-EOF
DEFINE successMsg="$successMsg"
DEFINE threads=$numberOfThreads
DEFINE
EOF cat ${DWSUMTBLS_SQL-}/tixd999d_common_ExecRanking.sql >> $sqlpgm || \
returnCode=1 if [[ $returnCode -eq 0 ]]; then
FormatMsg -u1 -p$PGM -l$LINENO "Updating the ranking table"
ExecSqlplus -p$sqlpgm -m"$successMsg" -l$LINENO || \
returnCode=$?
if [[ $returnCode -ne 4 && $returnCode -ne 0 ]]; then
FormatMsg -u2 -p$PGM -l$LINENO "Error: Failed updating the ranking table."
fi
fi
RemoveFile $sqlpgm
fi
as
num number;
begin
execute immediate 'select count(*) from ' || strTable into num;
dbms_output.put_line(num);
end;
/exec p_test('***');
用绑定变量替换
若要替换的是字段名或表名,则需要使用动态语句
execute immediate 'select * from '||tname;