Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Store procedure Parameters

trankientrankien Member Posts: 3
In my search function, there are some option conditions, for example code, name, sign of coursetitles.
The number and kind of conditions is only exactly known at runtime.
I can build a string to combine the conditions like
"code = '12-3' or name = 'Math' ".
But I don't know how to pass the string to my store procedure to use in whereStatement.

Comments

  • infidelinfidel Member Posts: 2,900
    : In my search function, there are some option conditions, for example code, name, sign of coursetitles.
    : The number and kind of conditions is only exactly known at runtime.
    : I can build a string to combine the conditions like
    : "code = '12-3' or name = 'Math' ".
    : But I don't know how to pass the string to my store procedure to use in whereStatement.

    Here's a little anonymous pl/sql block I have from when I was trying to figure out how to use dynamic sql in a cursor. Maybe it will give you some hints...

    [code]
    declare
    type cur1 is ref cursor;
    xPeople cur1;
    rPerson person%rowtype;
    begin
    open xPeople for 'select * from person where person_number like :pn' using '100020%';
    loop
    fetch xPeople into rPerson; exit when xPeople%notfound;
    dbms_output.put_line(rPerson.id);
    end loop;
    close xPeople;
    end;
    [/code]


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

  • trankientrankien Member Posts: 3
    : : In my search function, there are some option conditions, for example code, name, sign of coursetitles.
    : : The number and kind of conditions is only exactly known at runtime.
    : : I can build a string to combine the conditions like
    : : "code = '12-3' or name = 'Math' ".
    : : But I don't know how to pass the string to my store procedure to use in whereStatement.
    :
    : Here's a little anonymous pl/sql block I have from when I was trying to figure out how to use dynamic sql in a cursor. Maybe it will give you some hints...
    :
    : [code]
    : declare
    : type cur1 is ref cursor;
    : xPeople cur1;
    : rPerson person%rowtype;
    : begin
    : open xPeople for 'select * from person where person_number like :pn' using '100020%';
    : loop
    : fetch xPeople into rPerson; exit when xPeople%notfound;
    : dbms_output.put_line(rPerson.id);
    : end loop;
    : close xPeople;
    : end;
    : [/code]
    :
    :
    : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    :
    :

    Thank for your help, but i don't know what is ":pn' using '100020%'" mean.
  • infidelinfidel Member Posts: 2,900
    : : : In my search function, there are some option conditions, for example code, name, sign of coursetitles.
    : : : The number and kind of conditions is only exactly known at runtime.
    : : : I can build a string to combine the conditions like
    : : : "code = '12-3' or name = 'Math' ".
    : : : But I don't know how to pass the string to my store procedure to use in whereStatement.
    : :
    : : Here's a little anonymous pl/sql block I have from when I was trying to figure out how to use dynamic sql in a cursor. Maybe it will give you some hints...
    : :
    : : [code]
    : : declare
    : : type cur1 is ref cursor;
    : : xPeople cur1;
    : : rPerson person%rowtype;
    : : begin
    : : open xPeople for 'select * from person where person_number like :pn' using '100020%';
    : : loop
    : : fetch xPeople into rPerson; exit when xPeople%notfound;
    : : dbms_output.put_line(rPerson.id);
    : : end loop;
    : : close xPeople;
    : : end;
    : : [/code]
    : :
    : :
    : : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    : :
    : :
    :
    : Thank for your help, but i don't know what is ":pn' using '100020%'" mean.

    The point of the example is that you can use a string to open a cursor, which is the answer to your question. The :pn is a bind variable, to which I am passing the value '100020%'. So there's two ways you can do it, either create a string by concatenating your passed in where clause to a select statement, or create a full query and pass in the variables you want to use and use the "using" syntax.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

  • trankientrankien Member Posts: 3
    : : : : In my search function, there are some option conditions, for example code, name, sign of coursetitles.
    : : : : The number and kind of conditions is only exactly known at runtime.
    : : : : I can build a string to combine the conditions like
    : : : : "code = '12-3' or name = 'Math' ".
    : : : : But I don't know how to pass the string to my store procedure to use in whereStatement.
    : : :
    : : : Here's a little anonymous pl/sql block I have from when I was trying to figure out how to use dynamic sql in a cursor. Maybe it will give you some hints...
    : : :
    : : : [code]
    : : : declare
    : : : type cur1 is ref cursor;
    : : : xPeople cur1;
    : : : rPerson person%rowtype;
    : : : begin
    : : : open xPeople for 'select * from person where person_number like :pn' using '100020%';
    : : : loop
    : : : fetch xPeople into rPerson; exit when xPeople%notfound;
    : : : dbms_output.put_line(rPerson.id);
    : : : end loop;
    : : : close xPeople;
    : : : end;
    : : : [/code]
    : : :
    : : :
    : : : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    : : :
    : : :
    : :
    : : Thank for your help, but i don't know what is ":pn' using '100020%'" mean.
    :
    : The point of the example is that you can use a string to open a cursor, which is the answer to your question. The :pn is a bind variable, to which I am passing the value '100020%'. So there's two ways you can do it, either create a string by concatenating your passed in where clause to a select statement, or create a full query and pass in the variables you want to use and use the "using" syntax.
    :
    :
    : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    :
    :
    I have another problem when i use dynamic SQL.
    I have code:
    Select id from coursetitle where code = strCode;
    It run when strCode = '42352dfdwe' or strCode = 42352dfdwe
    But when i use dynamic SQL
    'Select id from coursetitle where code = ' || strCode;
    These is error when strCode = 42352dfdwe
  • infidelinfidel Member Posts: 2,900
    [b][red]This message was edited by infidel at 2003-7-18 7:40:19[/red][/b][hr]
    : I have another problem when i use dynamic SQL.
    : I have code:
    : Select id from coursetitle where code = strCode;
    : It run when strCode = '42352dfdwe' or strCode = 42352dfdwe
    : But when i use dynamic SQL
    : 'Select id from coursetitle where code = ' || strCode;
    : These is error when strCode = 42352dfdwe

    If it's a string then you need quotes around it

    [code]'select id from coursetitle where code = ''' || strCode || '''';[/code]

    Note that those are all single-quotes.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]



Sign In or Register to comment.