Pl/sql to check if the record exits already in the table , if yes then find the max salary ?

AS08

What is wrong with this -

set serveroutput on 

Declare

app number;

BEGIN

 If EXISTS(SELECT * employee_id from employees where manager_id=100) THEN

 Select max(salary)into app from employees;

 dbms_output.put_line(app);

 End IF;

end;

Error Output -

Declare

app number;

BEGIN

 If EXISTS(SELECT * employee_id from employees where manager_id=100) THEN

 Select max(salary)into app from employees;

 dbms_output.put_line(app);

 End IF;

end;

Error report -

ORA-06550: line 4, column 21:

PLS-00103: Encountered the symbol "EMPLOYEE_ID" when expecting one of the following:

  from

ORA-06550: line 4, column 68:

PLS-00103: Encountered the symbol ")" when expecting one of the following:

  * & - + ; / at for mod remainder rem <an exponent (**)> and

  or group having intersect minus order start union where

  connect || multiset

06550. 00000 - "line %s, column %s:\n%s"

*Cause:  Usually a PL/SQL compilation error.

*Action:

Please advise - How to check if the record exists or not whose manager id = 100 , if yes then it should select the max salary from the records whose manager id = 100 and store it into a variable?

Frank Kulash

Hi, @AS08

Using the ideas already posted, you can do something like this:

...
SELECT  COUNT (*), MAX (salary)
INTO 	num_found, max_salary
FROM	employees
WHERE 	manager_id = 100;

IF num_found = 0
THEN
  dbms_output.put_line ('No data found.');
ELSE
  new_sal := max_salary + 1;
  ...
END IF;
...

You only need a separate num_found variable if salary can be NULL; otherwise, max_salary will be NULL if and only if no data was found.

BluShadow

SELECT * employee_id from employees where manager_id=100

What does "select * employee_id" actually mean in normal syntax?

Read it to yourself "select all the columns aliased as employee_id..."

You can't select all the columns and give them all an alias name.

Perhaps you just meant

"select employee_id ..."

?

BluShadow

Personally, I wouldn't even pre-check for the existence of records, it's just additional querying that's not needed.

e.g.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    app number;
  3  begin
  4    select max(sal)
  5    into   app
  6    from   emp
  7    where  deptno = &deptno;
  8    dbms_output.put_line(case when app is null then 'No employees' else 'Max Salary: '||to_char(app) end);
  9* end;
SQL> /
Enter value for deptno: 40
old   7:   where  deptno = &deptno;
new   7:   where  deptno = 40;
No employees

PL/SQL procedure successfully completed.

SQL> /
Enter value for deptno: 20
old   7:   where  deptno = &deptno;
new   7:   where  deptno = 20;
Max Salary: 3000

PL/SQL procedure successfully completed.

You can tell from the result of there were no employees for the department because it will be a null value returned in this case.

Hub Tijhuis

EXIST is not a PL/SQL function: you must use it in SQL

the combination select * employee_id is not valid thus select employee_id

Just store the max salary: if there are no records the stored value will be null

Declare

app number;

BEGIN

 Select max(salary) into app from employees where manager_id=100;

 dbms_output.put_line(app);

 end;

if you still want the condition of existing records

Declare

cnt number;

app number;

BEGIN

 Select count(1) cnt, max(salary) into app from employees where manager_id=100;

 if cnt 0 then

dbms_output.put_line(app);

end if; 

end;

AS08

Hi @BluShadow ,

the query is - Select * from employee where manager_id=100.

earlier it was a typo mistake.

suppose now- I have extracted the max salary from employer table with where condition matched , after that We have also stored the max value in a app variable , but now what i am asked to do is that and why i was using if condition coz - I have to check if that particular record exists in the table already then extract the max salary and then store the extracted max salary and increment that max salary value by 1 and store the incremented value in a variable for future use and if the record does not exists then simply say record does not exists.

Frank Kulash

Hi, @AS08

Using the ideas already posted, you can do something like this:

...
SELECT  COUNT (*), MAX (salary)
INTO 	num_found, max_salary
FROM	employees
WHERE 	manager_id = 100;

IF num_found = 0
THEN
  dbms_output.put_line ('No data found.');
ELSE
  new_sal := max_salary + 1;
  ...
END IF;
...

You only need a separate num_found variable if salary can be NULL; otherwise, max_salary will be NULL if and only if no data was found.