CODEWITHSUNDEEP
sqldatabaseoracle-databasestored-proceduresplsql
user10602498
user10602498

Reputation:

Transforming an ordinary SQL query to a PL/SQL procedure (Oracle)

I have a homework task in which I have to turn some sql queries into procedures in PL/SQL. I started with the first one, here is the query:

select product.name, product.year, product.single_price,performer.name, genre.style, typep.label,company.name
from product
join genre
on  product.genre_genreid = genre.genreid
join  performer
on product.performer_performerid = performer.performerid
join typep
on product.type_type_id = typep.type_id
join company
on product.companyid = company.companyid
where genre.style like '&Genre' and (product.type_type_id = 1 or product.type_type_id =3);

It has multiple joins with other tables. The problem here is that I am not quite familiar with the PL-SQL language very much, what I want is to create a procedure with 1 parameter which will substitute the variable in the where clause. The procedure will return all the required variables (columns) using dbms_output_line() command. However I can only have one variable in 1 cursor. My question is how to modify the procedures so it returns all the columns mentioned in the select clause.

Upvotes: 0

Views: 58

Answers (2)

Ergi Nushi
Ergi Nushi

Reputation: 863

First of all, I can see some syntax errors on your code:

product_name varchar;
product_year varchar;
product_single_price varchar;
performer_name;
genre_style varchar;
typep_label varchar;
company_name varchar;

Above code are called local variables and they are declared before BEGIN statement. If you are using VARCHAR type should also include the max_size in BYTES or CHARS like below. Also I recommend using VARCHAR2 type.

product_name VARCHAR(32);

Other variables like DATE or INTEGER don't require the size.

Also if you want that the size of the variable to be the size of the column from where you are retrieving the information you can do the following:

product_name product.name%TYPE;

This means that the varible will have the same type and size as the column.

Same thing about cursor. It's declared before begin. If you want to loop your cursor there are some ways. It depends in the situation. What I would suggest you to do is the following:

create procedure searchGenre (
 genre_input in varcharw
) as
    product_name VARCHAR2(32);
    product_year VARCHAR2(32);
    product_single_price VARCHAR2(32);
    performer_name VARCHAR2(32);
    genre_style VARCHAR2(32);
    typep_label VARCHAR2(32);
    company_name VARCHAR2(32);
    cursor c1 is 
    select product.name--, product.year, product.single_price,performer.name, genre.style, typep.label,company.name
    from product
    join genre
    on  product.genre_genreid = genre.genreid
    join  performer
    on product.performer_performerid = performer.performerid
    join typep
    on product.type_type_id = typep.type_id
    join company
    on product.companyid = company.companyid
    where genre.style like &genre_input and (product.type_type_id = 1 or product.type_type_id =3);


begin 
    open c1;
    loop
        fetch c1 into product_name;
        exit when c1%not_found; -- exit condition

        dbms_output_line(product_name);

        -- and so forth  . . . .

    end loop;
end;

Upvotes: 0

Steven Feuerstein
Steven Feuerstein

Reputation: 1974

First of all, use the cursor FOR loop. Then you don't have to declare variables, write open and fetch statements, etc.

CREATE PROCEDURE searchgenre (genre_input_in IN VARCHAR2)
IS
BEGIN
   FOR rec
      IN (SELECT product.name --, product.year, product.single_price,performer.name, genre.style, typep.label,company.name
            FROM product
                 JOIN genre ON product.genre_genreid = genre.genreid
                 JOIN performer
                    ON product.performer_performerid = performer.performerid
                 JOIN typep ON product.type_type_id = typep.type_id
                 JOIN company ON product.companyid = company.companyid
           WHERE     genre.style LIKE genre_input_in
                 AND (product.type_type_id = 1 OR product.type_type_id = 3))
   LOOP
      dbms_output_line (rec.product_name);
   END LOOP;
END;

I am not sure what you mean by "How can I join 2 cursors?". You have just the one cursor here. But this will certainly handle the basics of your homework.

Hope this helps!

Upvotes: 1

Related Questions