Home > Database > Mysql Tutorial > body text

动态查询,动态生成cursor或者动态执行SQL的DML操作

WBOY
Release: 2016-06-07 17:46:45
Original
1383 people have browsed it

定义PROCEDURE P1 ( P_Asset_Type IN varchar2,P_Asset_SubType IN varchar2,P_OrderIds IN varchar2)。

需要根据传入参数动态查询,动态生成cursor或者动态执行SQL的DML操作。

1.简单的查询

SELECT   ABC from TableA where ORDER_ID IN ( P_OrderIds) ;

2. 动态生成cursor

定义游标和变量:

type   v_cursor   is   ref   cursor;  
C1 v_cursor;    v_command varchar2(2000);

v_command := ''SELECT    ABC from TableA  where ORDER_ID IN ( '' ||P_OrderIds|| '' ) '';

OPEN C1 for v_command;

3.动态DML操作

 v_command2 := ''UPDATE TableA  
               SET ASSET_TYPE_CD = ''''''||P_Asset_Type||'''''',''
                ||''ASSET_SUBTYPE_CD = ''''''||P_Asset_SubType||''''''''
                ||''WHERE ORDER_ID IN ( ''||P_OrderIds||'' ) '';


execute immediate v_command2;(动态sql)

 

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!