Example 1
Create a PL/SQL block that will prompt the user to input an employee number and
then displays the relevant information about the employee;
refer to the sample output given below.
Example output:
Smith (Clerk) works in New York earning R2500.00 since 17-DEC-80
SET SERVEROUTPUT on;
DECLARE
vename emp.ename%TYPE;
vjob emp.job%TYPE;
vloc dept.loc%TYPE;
salary emp.sal%TYPE;
hire_date emp.hiredate%TYPE;
staff_no NUMBER ;
BEGIN
-- Attribution de la valeur entrée par l'utilisateur
staff_no := &staff_no;
SELECT ename, job, loc, sal, hiredate
INTO vename, vjob, vloc, salary, hire_date
FROM emp, dept
WHERE emp.deptno = dept.deptno;
DBMS_OUTPUT.PUT_LINE(vename||' ('||vjob||') '||'works in '||vloc||' earning R'||
salary||' since '||hire_date);
END;
/ - for this problem statement above i wrote a corresponding query but ,after running this code in sql command line i encounter a ORA -01422 problem(exact fetch returns more than requested rows)
demobld.sql - Here is the table