CODEWITHSUNDEEP
oracle-databaseplsql
Pode Ionut
Pode Ionut

Reputation: 19

I'm trying to create a procedure in Pl/Sql and I'm getting this error

https://i.sstatic.net/M4oUC.jpg - Picture

I'm trying to calculate the current product stock. I have product_reception table in which I can calculate my stock. And sales (vanzari) table.

Upvotes: 0

Views: 34

Answers (1)

Littlefoot
Littlefoot

Reputation: 143088

Posting code as an image is usually a bad idea.

Anyway: quite a few objections:

  • don't enclose procedure (or table, column, ...) name into double quotes
  • name parameters so that their name is different from column names, for example: not cod_pds in number but par_cod_pds in number
  • declare variable in the declaration section, which is between is and begin. Don't use var (key)word. It is a good idea to name them so that the name reflects the fact that these are local variables, such as l_stoc_pds number
  • don't use exec within a PL/SQL procedure, especially not to run a select statement. exec is used in SQL*Plus to run a PL/SQL procedure, such as exec calculstoclazi
  • don't select into a variable preceded by a colon, but simply name the variable
  • those two select statements are exactly the same; I have no idea what you meant to do by doing that. Besides, you preceded cod_pds with stoc_pds which is a variable name (so that's totally wrong); if a column name is to be preceded by something, that's table name (or its alias)

Therefore, code that might look like something valid is this; obviously, the result will be 0 (zero). As I told you, those two selects are exactly the same.

create or replace procedure calculstoclazi
  (par_cod_pds  in  number,
   par_rezultat out number
  )
is
  l_stocs_pds     number;
  l_total_vanzare number;
begin
  select sum(cantitate) 
    into l_stoc_pds
    from receptie_marfa
    where cod_pds = par_cod_pds;

  select sum(cantitate) 
    into l_total_vanzare
    from receptie_marfa
    where cod_pds = par_cod_pds;

  par_rezultat := l_stoc_pds - l_total_vanzare;
end;

Upvotes: 2

Related Questions