PL-SQL Block with substitution variables

Question

I have to write PL/SQL Block that print salary of the people who have same job_title. Job_title must be entered in substitution variable. I struggle to print with 'dbms_output.put_line'. Also sql says that join is not right.

 DECLARE
  v_jobt VARCHAR2(50);
  v_sal Number ;
BEGIN
  SELECT j.job_title,e.salary INTO v_jobt, v_sal 
  FROM jobs j
  JOIN  EMPLOYEES e
  ON JOBS.JOB_ID=EMPLOYEES.salary
  WHERE  j.job_title = '&job_title';
  DBMS_OUTPUT.PUT_LINE ('Job Title is : ' ||v_jobt);
END;

FYI: In SQL WHERE is after JOIN.

PM 77-1
– PM 77-1

2019-05-13 23:21:21 +00:00
Commented May 13, 2019 at 23:21 — PM 77-1
– PM 77-1, Commented May 13, 2019 at 23:21
Even then I have errors.

Yavor Petranov
– Yavor Petranov

2019-05-13 23:28:51 +00:00
Commented May 13, 2019 at 23:28 — Yavor Petranov
– Yavor Petranov, Commented May 13, 2019 at 23:28

Gen Wan · Accepted Answer · 2019-05-13 23:36:30Z

1

1, Put 'where clause' after 'join'

2, Use the Alias names in the 'on' condition

3, Add one single quote before ...is:'

If you get the error message, please tell us what message you got.

Try this:

DECLARE
 v_lname VARCHAR2(50);
 v_sal Number;
BEGIN
  SELECT j.job_title,e.salary INTO v_lname, v_sal 
  FROM jobs j 
  JOIN  EMPLOYEES e
  ON j.JOB_ID = e.JOB_ID
  WHERE  j.job_title = '&job_title';
  DBMS_OUTPUT.PUT_LINE ('...is : ' ||v_lname);
END;

answered May 13, 2019 at 23:36

Gen Wan

2,0192 gold badges15 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Yavor Petranov Over a year ago

Thank you ! Do you know how I can add Average salary. "e.avg(salary)" is not right syntax.

PM 77-1 Over a year ago

Put alias on the field and not function. See if you need GROUP BY clause.

Gen Wan Over a year ago

@YavorPetranov It should be avg(e.salary). You also need to add 'group by j.job_title' after where clause if you want to get the average salary for each job title.

Collectives™ on Stack Overflow

PL-SQL Block with substitution variables

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related