Store procedure Parameters

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

  • : 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]

  • : : 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.
  • : : : 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]

  • : : : : 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
  • [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.

Howdy, Stranger!

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

Categories