DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Shall I use Bind Variable in my procedure?

  1. #1
    Join Date
    May 2001
    Posts
    285

    Red face Shall I use Bind Variable in my procedure?

    I did a search on 'bind variable' in this forum but still need your help in deciding if I should modify my existing sprocs to use bind varaible.

    In general, I have 4 types of sprocs -- select, insert, delete and update. And each of them look like:


    -- SELECT
    create or replace procedure sp_select_tblname (i_col_PK IN integer, o_col_1 OUT varchar2, o_col_2 OUT varchar2)
    as
    tbl_rec tblname %rowtype;
    begin
    select * into tbl_rec from tblname where col_PK = i_col_PK;
    o_col_1 := tbl_rec.col_1;
    o_col_2 := tbl_rec.col_2;
    exception
    ..
    end;
    /

    -- INSERT
    create or replace procedure sp_insert_tblname (i_col_1 IN integer, i_col_2 IN varchar2, i_col_2 IN varchar2)
    as
    begin
    INSERT INTO tblname VALUES (i_col_1, i_col_2, i_col_3);
    exception
    ..
    end;
    /

    -- UPDATE
    create or replace procedure sp_update_tblname (i_col_1 IN integer, i_col_2 IN varchar2, i_col_2 IN varchar2)
    as
    begin
    UPDATE tblname set col_2 = i_col_2, col_3 = i_col_3 WHERE col_1 = i_col_1;
    exception
    ..
    end;
    /

    -- DELETE
    create or replace procedure sp_delete_tblname (i_col_1 IN integer)
    as
    begin
    DELETE tblname WHERE col_1 = i_col_1;
    exception
    ..
    end;
    /


    My questions are --

    1. Do I need to use bind variable in my select procedure (sp_select_tblname)? There was a post said the way I did already uses the bind variable, just not in the form that I'm familar with :bv.

    2. Do I need to use bind variable for my all other procedures? I feel I do, but I don't know how to use it. The only way I can do is change the static SQL inside the procedure (which what I'm using now) into dynamic sql and then use 'using' clause? Is that the way to go?

    3. If the answer to question #2 is 'yes', then what's the difference of using dynamic SQL or statis SQL in PL/SQL? Or they are the same?

    Many thanks to your replies! BTW -- it will be even better if anybody can point me to the detail instruction on how to use bind variable in oracle doc. I found a little bit discussion here and there, but nothing can teach me how to use it exactly.

    Elaine

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    those procs you listed there are already using bind variables, pl/sql variables are bind variables.

    Bind variables are a must if you want scalability and performance (except on a d/w perhaps). if a query is going to run over and over again, you do not want to be doing a hard parse on it each time

    Also want to avoid using dnyamic sql is pl/sql - it will be slower than using binds with static sql

    Go to http://asktom.oracle.com and search for bind variables on his site. You will find many ways to do it and the reasons why.

  3. #3
    Join Date
    May 2001
    Posts
    285

    Thumbs up Very much appreciated!

    Originally posted by davey23uk
    those procs you listed there are already using bind variables, pl/sql variables are bind variables.
    Good to know that!

    Originally posted by davey23uk
    Also want to avoid using dnyamic sql is pl/sql - it will be slower than using binds with static sql[/B]
    Do you mean 'avoid using dynamic sql in pl/sql' unless necessary (like DDL or SCL)?

    Originally posted by davey23uk
    Go to http://asktom.oracle.com and search for bind variables on his site. You will find many ways to do it and the reasons why. [/B]
    I will, thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width