CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqloracle11g
Karan Gupta
Karan Gupta

Reputation: 529

Stored Procedure to return specific rows plsql

Say I have a table as:

 create table product
(
  prod_code number(3),
  prod_name varchar2(20),
  prod_dept varchar2(20)
);

I want to write a procedure which takes in two parameters:

param_1 = col_name
param_2 = col_value

The procedure needs to check if the column exists and if it does then returns the row(s) with the values equal to param_2.

Example:

Say the procedure name is getproddetails and the parameters passed are like this:

execute getproddetails('prod_id', 40)

When I pass these two values then it should return the entire row with prod_id=40.

I can check weather the column exists or not but how to return the entire row(s) based on the values?

Any help would be great. I have written the following code but it doesn't work:

create or replace procedure getproductdetails(p_colname varchar2, p_value number)
is
  v_column_exists number :=0;
  cursor c1 is select * from product where p_colname = p_value;
  myrow c1%rowtype;
    BEGIN
    select count(*) into v_column_exists
    from user_tab_cols
    where column_name = p_colname and table_name = 'PRODUCT';

   if(v_column_exists > 0) then
     open c1;
      loop
        fetch c1 into myrow;
        exit when c1%notfound;
        dbms_output.put_line(myrow.prod_name || ' ' || myrow.prod_cost);
      end loop;
    else
      dbms_output.put_line('Column Not Found');
    end if;
   end getproductdetails;

Note that I am displaying only 2 columns but there are like 8 to 9 columns.

Upvotes: 1

Views: 1882

Answers (2)

J. Chomel
J. Chomel

Reputation: 8395

It can work if you set param_2 as VARCHAR2; use implicit conversion.

Here is what I would do, using REF_CURSOR:

create or replace procedure getempdetails (
   pcol_name in varchar2
 , pcol_value in varchar2
 , pres_cursor out sys_refcursor
) is 
begin
  open pres_cursor for 'SELECT emp_id, emp_name, emp_dept FROM emp WHERE '
  ||pcol_name||'='''||pcol_value||'''';
end;
/

Edit: more elegant with the use of :pcol_name;

but I could not find how to make it work with the implicit conversion.

create or replace procedure getempdetails (
   pcol_name in varchar2
 , pcol_value in varchar2
 , pres_cursor out sys_refcursor
) is 
begin
   OPEN pres_cursor 
    FOR 'SELECT emp_id, emp_name, emp_dept FROM emp WHERE :pcol_name='''||pcol_value||''''
  USING pcol_name;
end;

/

Then use the cursor that way:

SQL>  insert into emp values(1, 'John', 'IT');

1 row created.

SQL> variable r refcursor;
SQL> exec  getempdetails('emp_id', '1', :r);

PL/SQL procedure successfully completed.

SQL>  print r
         1 John                 IT

SQL>

Search a little more if you want to use this cursor inside further PL/SQL. E.g. great examples like here or here.

NB: Basically, in PL/SQL, ' escapes '. So if you want a ' inside a string in PL/SQL, you write ' I want a quote there -> '' <- '

Upvotes: 1

Leandro Franciscato
Leandro Franciscato

Reputation: 26

how to return the entire row(s) based on the values?

You can create a out parameter and it can be of the type of the table, like:

Procedure gettemplates( columnname in varchar2, value in varchar2, entirerowreturn out yourtable%rowtype) is ...

Upvotes: 0

Related Questions