Accpt employee number from user fetch the employee details from the “Employee” table and store it to a PLSQL record type variable and print the same

Accpt employee number from user fetch the employee details from the “Employee” table and store it to a PLSQL record type variable and print the same.

SET SERVEROUTPUT ON;
    DECLARE
    type emp_det is record
    (
    emp_num number := &empnum,
    emp_name varchar2(150),
    emp_sal number,
    dept_id number
    );
    emp_rec emp_det;
    BEGIN
    select employee_id, first_name, salary, department_id into emp_num, emp_name,
    emp_sal, dept_id from hr.employees where employee_id = &empnum;
    dbms_output.put_line('Employee Number :'||emp_rec.emp_num);
    dbms_output.put_line('Employee Number :'||emp_rec.emp_name);
    dbms_output.put_line('Employee Number :'||emp_rec.emp_sal);
    dbms_output.put_line('Employee Number :'||emp_rec.dept_id);
    END;

Answer

Here’s one option (if that’s what you’re looking for):

SQL> create or replace procedure p_test (par_empno in emp.empno%type) is
  2    type emp_det is record (empno number,
  3                            ename varchar2(10),
  4                            sal   number
  5                           );
  6    led  emp_det;
  7  begin
  8    select empno, ename, sal
  9      into led
 10      from emp
 11      where empno = par_empno;
 12
 13    dbms_output.put_line(led.empno ||': '|| rpad(led.ename, 10, ' ') || led.sal);
 14  end;
 15  /

Procedure created.

SQL> exec p_test(7369);
7369: SMITH     800

PL/SQL procedure successfully completed.

SQL>