PL/SQL problem

[rmorgan]

I am working on a class project and I having a problem figureing out what I am doing wrong.

I have this so far:

declare
v_isbnnum number(10) := &ISBN;
begin
select isbn, title, retail, category
into v_isbnnum
from books
where isbn = v_isbnnum;
end;
/

I am trying to allow a user to enter a books isbn number and have the query retrieve the details of that particular book.
Am I going in the right direction? I cannot seem to figure this out. Can someone lend some assistance? TIA

[paross1]

The easiest (without creating a package and ref cursor for returning result sets, etc.) would be something like this:

CREATE OR REPLACE
PROCDEDURE get_book_detl(in_isbnnum IN NUMBER,
out_title OUT VARCHAR2,
out_title OUT VARCHAR2,
out_retail OUT VARCHAR2,
out_category OUT VARCHAR2,
out_status OUT VARCHAR2)
IS

BEGIN

SELECT isbn,
title,
retail,
category
INTO out_isbnnum,
out_title,
out_retail,
out_category
FROM books
WHERE isbn = in_isbnnum;

out_status := 'successful';

EXCEPTION
WHEN TOO_MANY_ROWS
THEN out_status := 'more than one row selected';

WHEN NO_DATA_FOUND
THEN out_status := 'nothing selected';

END get_book_detl;

Your example looks like you are attempting to create an anonymous PL/SQL block, which can't be used to return results to ColdFusion, so you actually need to create a stored procecure for that. When SELECTINing INTO variables in PL/SQL, you should use an EXCEPTION to handle the possibility that you will either return more than one row, or none.

Phil

[rmorgan]

Thanks for input, but as this is a class project I have specific instructions I have follow. I have managed to get a little further, but ran into another problem with some if statements. One 'if' works fine, but when i attempt a elseif, it fails. This is Oracle 9i, and I am using SQL Plus and Oracle SQL Developer. In this instance I am not using CF, I just have to come up with the proper code.
I have progressed this far now:

declare
v_retail number(5,2);
v_retailincrease number(5,2);
v_title varchar2(30);
v_newretail number(5,2);
v_isbn varchar2(10) := &ISBN;
v_category varchar2(12);
begin
if v_category = 'computer' then
v_retailincrease := 20;
elseif v_category = 'cooking' then
v_retailincrease := 15;
elseif v_category = 'literature' or 'self help' then
v_retailincrease := 10;
else v_category = 'fitness' or 'family life' or 'children' or 'business' then
v_retailincrease := 5;
select isbn, title, retail, retail*v_retailincrease
into v_isbn, v_title, v_retail, v_newretail
from books
where isbn = v_isbn;
end if;
dbms_output.put_line (v_title ' ' v_newretail);
end;

The error I am getting is:

Error report:
ORA-06550: line 15, column 17:
PLS-00103: Encountered the symbol "=" when expecting one of the following:

:= . ( @ % ;
ORA-06550: line 15, column 74:
PLS-00103: Encountered the symbol "THEN" when expecting one of the following:

. ( * @ % & = - + ; < / > at in is mod not rem return
returning <an exponent (**)> <> or != or ~= >= <= <> and or
like between ||
ORA-06550: line 22, column 1:
PLS-00103: Encountered the symbol "DBMS_OUTPUT"
ORA-06550: line 22, column 31:
PLS-00103: Encountered the symbol ";" when expecting one of the following:

. ( , * % & - + / at mod rem <an identifier>
<a double-quoted delimited-identifier> <an exponent (**)> as
from into || bulk

[paross1]

Well first of all, in PL/SQL you would use elsif not elseif (drop the e). Also, your last else looks like it should be an elsif, and your query shouldn't be nested within your IF. Also, v_category = 'fitness' or 'family life' or 'children' or 'business' is improper syntax. You could use either an IN statement, or individually compare each parameter to v_category with an or, such as in v_category = 'fitness' or v_category = 'family life' or v_category = 'children' or v_category = 'business' , etc

elsif v_category = 'literature' or v_category = 'self help'
then,,,
....
elsif v_category IN ('fitness', 'family life', 'children', 'business')
then v_retailincrease := 5;
end if;

select isbn, title, retail, retail*v_retailincrease
into v_isbn, v_title, v_retail, v_newretail
from books
where isbn = v_isbn;

Also, change this: dbms_output.put_line (v_title ' ' v_newretail);
to this: dbms_output.put_line (v_title ||' ' ||v_newretail);
in order to concatenate the two variables..

Last word. I hope you realize that you will throw an exception if your query either returns more than one row, or no rows at all. This is something that you need to get used to with PL/SQL that you don't have to face when using straight SQL. You will learn that you will need to handle your exceptions, and also how to start using cursors rather SELECT INTO statements when possible.

Phil

[rmorgan]

Thanks for your help. I am almost done with this, have a few more things to throw into it. I understand what you are saying about using the cursors. This is just an intro to PL/SQL, I am sure we will get to the higher level things.

I noticed what you said about creating an anonymous block, I see in the book what it is saying but dont quite understand it. As the code is written now, it reports that 'anonymous block completed', and does not give the any dbms_output. Is this inherit of the way I a doing it? How do I get it to display the result. In this case there will always be only one result.

[paross1]

While in SQL*Plus, you should execute set serverout on to see your output. In fact, I usually run a script when I first launch SQL*Plus to set up the environment so that I have a display that suites my needs. In fact, if you select Options-->Environment in SQL*Plus, you can set these parameters on the fly for your current session.

example:
set pagesize 2000
set linesize 1000
set arraysize 5
set numwidth 15
set buffer 1000000
set serverout on size 1000000
set verify off
etc....

As for the difference between PL/SQL anonymous blocks and stored procedures is essentially this: An anonymous block lacks a header section altogether and doesn't have a handle for reference and consequently can not be called by another block. It can be thought of as a script that allows you to execute PL/SQL commands without having to compile an executable program. A stored procedure (or function) is just that - it is an executable statement that is stored in the database and must be compiled. In fact, most stored procedures and functions are not stored individually, but placed in packages, which are collections of procedures and functions, with the means of declaring various variables, structures, procedures and functions locally or globally, etc. Your package specification is where you make your public procedure or function declarations and the package body is where you actually write the procedures and functions, etc. You will eventually learn about these things as you advance through PL/SQL. If you ever wish to call Oracle stored procedures from ColdFusion, you are going to learn about packages, etc. PL/SQL is a very powerful language. I recommend reading Oracle PL/SQL Programming by Steven Feuerstein (O'Reilly), as Feuerstein is an excellent resource for all things PL/SQL

Phil

[rmorgan]

Ok, so would that have possibly anything to do with the dbms_output? It seems to be only outputting one variable from that clause, in this case v_title, and skipping v_newretail



declare
v_retail number(5,2);
v_retailincrease number(5,2);
v_title varchar2(30);
v_newretail number(5,2);
v_isbn varchar2(10) := &ISBN;
v_category varchar2(12);
v_maxretail number(5,2);
begin
if v_category = 'computer' then
v_retailincrease := .20;
elsif v_category = 'cooking' then
v_retailincrease := .15;
elsif v_category IN ('literature', 'self help') then
v_retailincrease := .10;
elsif v_category IN ('fitness', 'family life', 'children', 'business') then
v_retailincrease := .05;
end if;
select title, retail, sum(retail * v_retailincrease + retail) as v_newretail
into v_title, v_retail, v_newretail
from books
where isbn = v_isbn
group by title, retail;
dbms_output.put_line ('The price for' || ' ' || v_title || ' ' || 'has been updated to '|| v_newretail );
end;

The output put would be
The price for ' book name' has been updated to

[paross1]

Not sure why you aren't seeing all of your output. How about listing all of your values using other DBMS_OUTPUT.PUT_LINE() statements so that you can debug? You also might want to initialize your variables in the declaration section.

By the way, where are you setting the value for v_category? You are checking the value in your if-then-else section, but I don't see where you are actually setting its value. I added the initialization to NULL in the declaration for most of your variables so that you don't have any ambiguous values, etc. I have also added a prompt for category with a default value of 'computer' so that you can input in from your SQL*Plus screen, or leave it blank for the default value.

declare
v_retail number(5,2) := NULL;
v_retailincrease number(5,2) := NULL;
v_title varchar2(30) := NULL;
v_newretail number(5,2) := NULL;
v_isbn varchar2(10) := '&ISBN';
v_category varchar2(12) := NVL('&category', 'computer');
v_maxretail number(5,2) := NULL;

begin

if v_category = 'computer' then
v_retailincrease := .20;
elsif v_category = 'cooking' then
v_retailincrease := .15;
elsif v_category IN ('literature', 'self help') then
v_retailincrease := .10;
elsif v_category IN ('fitness', 'family life', 'children', 'business') then
v_retailincrease := .05;
end if;

select isbn, title, retail, (retail*v_retailincrease)+retail
into v_isbn, v_title, v_retail, v_newretail
from books
where isbn = v_isbn;

dbms_output.put_line ('v_category = ' || v_category);
dbms_output.put_line ('v_retailincrease = ' || v_retailincrease);
dbms_output.put_line ('v_isdn = ' || v_isdn);
dbms_output.put_line ('v_retail = ' || v_retail);
dbms_output.put_line ('The price for ' || v_title ||' has been updated to '|| v_newretail );

end;

Phil