动态Sql 使用方法代码,PLSQL代码库,德仔网

代码语言
代码分类
【PLSQL】动态Sql 使用方法
作者:Dezai.CN / 发布于2011/6/22/ 873
<div>
执行方法主要是使用<span style="color: #0000ff">execute</span><span style="color: #000000"> immediate</span>执行组合成的sql语句，示例如下： 

<div style="width: 900px" class="cnblogs_code"><img style="display: none" id="code_img_closed_e6f7d0c1-ade9-4370-a47b-ea78c4613627" class="code_img_closed" alt="" src="http://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif" /><img id="code_img_opened_e6f7d0c1-ade9-4370-a47b-ea78c4613627" class="code_img_opened" src="http://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif" alt="" /><span class="cnblogs_code_collapse">代码</span>
<div id="cnblogs_code_open_e6f7d0c1-ade9-4370-a47b-ea78c4613627">
<div><!--<br/  />
<br/  />
Code highlighting produced by Actipro CodeHighlighter (freeware)<br/  />
http://www.CodeHighlighter.com/<br/  />
<br/  />
--><span style="color: #008080">--</span><span style="color: #008080">动态sql</span><span style="color: #008080">
</span><span style="color: #0000ff">CREATE</span><span style="color: #000000"> </span><span style="color: #808080">OR</span><span style="color: #000000"> </span><span style="color: #ff00ff">REPLACE</span><span style="color: #000000"> </span><span style="color: #0000ff">PROCEDURE</span><span style="color: #000000"> Hp_Pro_sql
(
p_TableName </span><span style="color: #808080">in</span><span style="color: #000000"> </span><span style="color: #0000ff">varchar2</span><span style="color: #000000">, </span><span style="color: #008080">--</span><span style="color: #008080">表名</span><span style="color: #008080">
</span><span style="color: #000000">p_Result out </span><span style="color: #0000ff">varchar2</span><span style="color: #000000"> </span><span style="color: #008080">--</span><span style="color: #008080">结果</span><span style="color: #008080">
</span><span style="color: #000000">)
</span><span style="color: #0000ff">IS</span><span style="color: #000000">
v_TableName </span><span style="color: #0000ff">varchar2</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">30</span><span style="color: #000000">);
v_Sql </span><span style="color: #0000ff">varchar2</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">100</span><span style="color: #000000">);
</span><span style="color: #0000ff">BEGIN</span><span style="color: #000000">
v_TableName:</span><span style="color: #808080">=</span><span style="color: #ff0000">'</span><span style="color: #ff0000">HP_NO</span><span style="color: #ff0000">'</span><span style="color: #000000">;
v_Sql :</span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">select count(*) from </span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #808080">||</span><span style="color: #000000"> v_TableName; </span><span style="color: #008080">--</span><span style="color: #008080">组合sql</span><span style="color: #008080">
</span><span style="color: #0000ff">execute</span><span style="color: #000000"> immediate v_Sql </span><span style="color: #0000ff">into</span><span style="color: #000000"> p_Result; </span><span style="color: #008080">--</span><span style="color: #008080">执行动态sql，将结果赋给变量</span><span style="color: #008080">
</span><span style="color: #0000ff">END</span><span style="color: #000000">;</span></div>
</div>
</div>
</div>
试试其它关键字
　动态Sql　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)