1.5 Validate the existing of empno(7521) if it dosen’texists then insert new rec
ID: 3819908 • Letter: 1
Question
1.5 Validate the existing of empno(7521) if it dosen’texists then insert new record with name of Steven and his associated empno.
1.6 Ask the user to enter any department then show the information for the department entered. (learning passing parameter in cursor)
1.7 Ask the user to enter any department then show the information for the department entered using implicitycursor.
Exercise 2:: 2.1 Create a procedure printe_Emp_Info(deptno1 number). It uses cursor to print all the employee information.
Explanation / Answer
1.5) IF NOT EXISTS (SELECT *
FROM EMPLOYEE WHERE EMPNO = 7521)
INSERT INTO EMPLOYEE
(EMPNAME,
EMPNO)
VALUES ('MARK',
7521)
1.6) CREATE PROCEDURE SomeName(@deptName VARCHAR(MAX))
AS BEGIN
SELECT *
FROM department
WHERE departmentName = @deptName
END
1.7) CREATE PROCEDURE deptExists(@deptId number(3))
DECLARE depart_name varchar(max);
AS BEGIN
SELECT *
FROM department
WHERE departmentId = @deptId;
IF sql%found THEN
SELECT departmentName INTO depart_name
FROM department
WHERE departmentId = @deptId;
dbms_output.put_line(depart_name);
ELSIF sql%notfound THEN
dbms_output.put_line( 'NO records found');
END IF;
END
2.1) create or replace PROCEDURE printe_Emp_Info(deptno1 number)
AS
BEGIN
FOR aRow IN (SELECT EMPLOYEE_ID, Name, Email_Address, Hire_Date, Update_Date
FROM Employee
WHERE Employee_dept = deptno1)
LOOP
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || aRow.EMPLOYEE_ID);
DBMS_OUTPUT.PUT_LINE('NAME: ' || aRow.NAME);
DBMS_OUTPUT.PUT_LINE('EMAIL_ADDRESS: ' || aRow.EMAIL_ADDRESS);
DBMS_OUTPUT.PUT_LINE('HIRE_DATE: ' || aRow.HIRE_DATE);
DBMS_OUTPUT.PUT_LINE('UPDATE_DATE: ' || aRow.UPDATE_DATE);
END LOOP;
END;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.