An expression below creates a procedure emp_infor that accept two parameters: employeeno and job_title, then pass them toexplicit cursor named emp_list to retrieve the firstname, lastname, hiredate, job_id and salary, of all employees earning a salaryless that 11000 and have an “O” as the second letter in the lastname. Create an anonymous block that will call the procedure todisplay the details of employee as per entry of job_title and employeeno parameters. Use this table Employee (employeeno,firstname, lastname, hiredate, job_id ,salary, job_title)
Question
An expression below creates a procedure emp_infor that accept two parameters: employeeno and job_title, then pass them toexplicit cursor named emp_list to retrieve the firstname, lastname, hiredate, job_id and salary, of all employees earning a salaryless that 11000 and have an “O” as the second letter in the lastname. Create an anonymous block that will call the procedure todisplay the details of employee as per entry of job_title and employeeno parameters. Use this table Employee (employeeno,firstname, lastname, hiredate, job_id ,salary, job_title)
Solution
Sure, here is a PL/SQL block that creates the procedure and an anonymous block that calls the procedure:
-- Create the procedure
CREATE OR REPLACE PROCEDURE emp_infor (employeeno IN NUMBER, job_title IN VARCHAR2) IS
CURSOR emp_list IS
SELECT firstname, lastname, hiredate, job_id, salary
FROM Employee
WHERE salary < 11000 AND SUBSTR(lastname, 2, 1) = 'O';
emp_record emp_list%ROWTYPE;
BEGIN
OPEN emp_list;
LOOP
FETCH emp_list INTO emp_record;
EXIT WHEN emp_list%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee No: ' || employeeno || ', Job Title: ' || job_title || ', First Name: ' || emp_record.firstname || ', Last Name: ' || emp_record.lastname || ', Hire Date: ' || emp_record.hiredate || ', Job ID: ' || emp_record.job_id || ', Salary: ' || emp_record.salary);
END LOOP;
CLOSE emp_list;
END;
/
-- Call the procedure in an anonymous block
DECLARE
emp_no NUMBER := 123; -- replace with actual employee number
job VARCHAR2(100) := 'Manager'; -- replace with actual job title
BEGIN
emp_infor(emp_no, job);
END;
/
Please replace 123 and 'Manager' with the actual employee number and job title you want to use. This code will display the details of all employees who earn less than 11000 and have an "O" as the second letter in their last name.
Similar Questions
Examine the structure of employee table. You issue the following query:DECLAREl_vc_firstname employee.emp_name%type;l_salary employee.salary%type;BEGINSELECT emp_name, salary INTO l_vc_firstname, l_salary FROM employee WHERE emp_id=201;dbms_output.put_line('Number of rows processed: '||sql%rowcount);END;/What will be the outcome of above query?
From the given table "emp_det" which has column values "emp_id", "fist_name", "last_name", "email","person_id", "dept_id", "salary". Write a SQL query to retrieve the employee ID, first name, and last name of employees whose first names contain the letter 'S' and who work in departments where at least one employee's first name contains the letter 'S'.Table Name: emp_detInput format :The input records are already prepopulated as per the given requirement.Output format :The result of the query is a list of employee details, including their employee ID, first name, and last name as shown below.emp_id first_name last_name123 S Arvind124 Sj Kumar234 Sk Seema256 Sd venkatRefer to the sample output for the column headers.
Write queries for the following tables:T1 ( Empno, Ename , Salary, Designation)T2 (Empno, Deptno.)(1) Display the Deptno in which Employee Seeta is working.(2) Display Empno, Ename, Deptno.
Problem StatementIn a company's Employee Management System, a database table named "Employees" that stores information about employees, including their EmployeeID, EmployeeName, Department, and Salary.The HR department needs a mechanism to delete employee records from the system when an employee leaves the company or for any other valid reason. To address this requirement, you are tasked with creating a stored procedure named "DeleteEmployee," which takes an EmployeeID as input and deletes the corresponding employee record from the "Employees" table.The following table is already created, and the records are inserted into the table.TABLE: Employees EmployeeID INT PRIMARY KEY, EmployeeName VARCHAR(50), Department VARCHAR(50), Salary DECIMAL(10, 2)Prepopulated Records:Input format :The input records are already prepopulated, as given in the problem statement.Output format :The stored procedure deletes the employee record based on the provided EmployeeID.The output should display the employee details after deletion as shown below.EmployeeID EmployeeName Department Salary1 John Doe IT 60000.002 Jane Smith HR 55000.003 Bob Johnson Marketing 70000.004 Alice Williams Finance 80000.00EmployeeID EmployeeName Department Salary1 John Doe IT 60000.003 Bob Johnson Marketing 70000.004 Alice Williams Finance 80000.00Refer to the sample output for the column headers.
Create Table Employee with attributes firstName,LastName,SSN,Address,Salary,Birthday,Sex,SupervisorSSN,DepartmentNo
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.