PLSQL -- calling function to calculate with char(1) and number variables

Question

I'm just now teaching myself SQL and PLSQL and have haven't had too much trouble debugging the regular SQL, but I'm trying to call a PLSQL function that is throwing errors that I can't figure out from the Oracle docs and other online resources. I'm using Oracle SQL-Developer. I've tried these but they are not helpful in this case:

How to return a record from an existing table from an Oracle PL/SQL function?

Writing a function in plsql

Oracle PLSQL function call into a second function

Can't seem to subract two numbers in a PLSQL function

I've tried writing the same function 2 different ways for practice:

Create Function Calc_Tax
(Quantity IN Number,Unit_Price IN Number,Taxable IN VarChar2)
    Return Number IS amt Number;
Declare
price := Quantity * Unit_Price;
Begin
    IF Taxable = 'Y' THEN
    amt := price + 0.06 * price;
  ELSE
    amt := price;
  END IF;
  Return amt;
End;

-- or --

Create Or Replace Function Calc_Tax
(Quantity IN Number,Unit_Price IN Number,tax IN Sale_Item.Taxable%TYPE)
    Return Number IS amt Number;
  Declare
  price := Quantity * Unit_Price;
  Begin
    IF tax = 'Y' THEN
    amt := price + 0.06 * price;
  ELSE
    amt := price;
  END IF;
  Return amt;
End;

I had some trouble declaring the 'tax' parameter as a varchar2(1) so I left it just varchar2 which the docs seem to say is default for 1 space (if I read that right). Of course, I'd rather do it properly and declare the size of the varchar explicitly.

This is how I'm calling it:

DECLARE
   g_last Guest.First_Name%type := 'Richard';
   g_last Guest.Last_Name%type := 'Wharton';
   g_id Guest.Guest_ID%type;
   s_g_id Stay.Guest_ID%type;
   sho_id Stay.Hotel_ID%type;
   h_id Hotel.Hotel_ID%type;
   h_name  Hotel.Hotel_Name%type;
   i_id Sale_Item.Item_ID%type;
   i_name  Sale_Item.Item_Name%type;
   i_tax  Sale_Item.Taxable%type;
   cs_id Charge.Stay_ID%type;
   c_date Charge.Trans_Date%type;
   c_quant Charge.Quantity%type;
   c_uprice Charge.Unit_Price%type;
BEGIN
   SELECT H.Hotel_ID, H.Hotel_Name,C.Item_ID, Item_Name, C.Trans_Date, C.Quantity, 
   C.Unit_Price,Taxable INTO h_id,h_name,i_id,i_name,c_date,c_quant,c_uprice
   FROM Hotel H,Charge C Where cs_id = (Select Stay_ID From Stay Where Guest_ID = 
   (Select Guest_ID From Guest Where First_Name='Richard' And Last_Name='Wharton'));
   --WHERE id = c_id;

   dbms_output.put_line
   ('Guest ' ||g_first || g_last || ' bought ' || i_name || ' with tax ' || Calc_Tax

(c_quant,c_uprice,i_tax));
END;

And it's throwing errors:

Error(18,1): PLS-00103: Encountered the symbol "DECLARE" 
Error(43,4): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     ( begin case declare end exception exit for goto if loop mod    null pragma raise return select update while with    <an identifier> <a double-quoted delimited-identifier>    <a bind variable> << continue close current delete fetch lock    insert open rollback savepoint set sql execute commit forall    merge pipe purge 

Here's the schema:

I'm just learning now, so I'm sure that any junior SQL programmers can quickly show me where I'm going wrong.

Answer 1

You don't use DECLARE inside a procedure or function -- use the AS keyword instead to indicate the variable declaration "block."

You also cannot put a constraint on parameters (e.g. you can have VARCHAR2 but not VARCHAR2(10) -- See Oracle docs

You also shouldn't have a semi-colon following your return statement.

Create Function Calc_Tax (Quantity IN Number, Unit_Price IN Number, Taxable IN VarChar2) 
Return Number
AS
  My_Variable VARCHAR2(2000);
BEGIN
    -- code
END Calc_Tax;