giovedì 6 febbraio 2014

Orcle: parameters for PLSQL dynamic statement




Reference: http://docs.oracle.com/cd/B10500_01/appdev.920/a96624/11_dynam.htm

How to pass input and output parameter to a dynamic PLSQL statement?
Generic code syntax is:

 EXECUTE IMMEDIATE dynamic_string  
 [INTO {define_variable[, define_variable]... | record}]  
 [USING [IN | OUT | IN OUT] bind_argument  
   [, [IN | OUT | IN OUT] bind_argument]...]  
 [{RETURNING | RETURN} INTO bind_argument[, bind_argument]...];  


And to have a practical example:

 DECLARE  
   sql_stmt VARCHAR2(200);  
   my_empno NUMBER(4) := 7902;  
   my_ename VARCHAR2(10);  
   my_job  VARCHAR2(9);  
   my_sal  NUMBER(7,2) := 3250.00;  
 BEGIN  
   sql_stmt := 'UPDATE emp SET sal = :1 WHERE empno = :2  
    RETURNING ename, job INTO :3, :4';  
   /* Bind returned values through USING clause. */  
   EXECUTE IMMEDIATE sql_stmt  
    USING my_sal, my_empno, OUT my_ename, OUT my_job;  
   /* Bind returned values through RETURNING INTO clause. */  
   EXECUTE IMMEDIATE sql_stmt  
    USING my_sal, my_empno RETURNING INTO my_ename, my_job;  

 END;  




Nessun commento:

Posta un commento