web analytics

Bind Variables in PL/SQL

Let’s see an example of bind variables , since they are often quite useful in many cases, with them we can unify many processes and operations:

First of all we need to create a table to do the tests:

– Test table to use the bind variables

create table PROOF (

    NUM number ( 22 ),

    TEXT varchar ( 100 )

);

Let’s see a block pl / sql that inserts in our table of tests:

– Load with parameters by value:

declare

    v_i number ;

begin

    loop

        INSERT INTO PROOF VALUES ( 3 , ’50’ );

        v_i : = v_i + 1 ;

        exit when v_i > 1000000 ;

    end loop;

    commit;

end;

Now let’s see a block pl / sql that inserts in our table of tests using bind variables:

– Load with parameters using bind variables

declare

    v_i number ;

begin

    loop

        execute immediate ‘INSERT INTO PROOF VALUES (: x,: y)’ using 3 , ’50’ ;

        v_i : = v_i + 1 ;

        exit when v_i > 1000000 ;

    end loop;

    rollback;

end;

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

error: Content is protected !!