In Oracle PLSQL, how to declare IN parameter as NUMBER(9,2)?-CodePudding

I want to declare amount parameter as type NUMBER(9,2); How come this Oracle PL/SQL procedure doesn't work?

CREATE OR REPLACE PROCEDURE spupdate_price_by_cat(amount IN NUMBER(9, 2))AS 
BEGIN
END;
/

When I run it, i get error:

Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character. The symbol ":=" was substituted for "(" to continue.

CodePudding user response：

You cannot declare a NUMBER data type with scale and precision in the signature of a PL/SQL function or procedure.

Either use NUMBER:

CREATE OR REPLACE PROCEDURE spupdate_price_by_cat(
  amount IN NUMBER
)
AS
BEGIN
  NULL;
END;
/

Or use a %TYPE declaration to declare it to have the same data type as a column of a table (but it still does not respect the column's scale and precision in the signature):

CREATE OR REPLACE PROCEDURE spupdate_price_by_cat(
  amount IN table_name.column_name%TYPE
)
AS
BEGIN
  NULL;
END;
/

If you want a particular scale and precision enforcing in a PL/SQL variable then declare it locally in the procedure:

CREATE OR REPLACE PROCEDURE spupdate_price_by_cat(
  amount IN table_name.column_name%TYPE
)
AS
  v_amount1 NUMBER(9,2)                 := amount;
  v_amount2 table_name.column_name%TYPE := amount;
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_amount1);
END;
/

db<>fiddle here