Oracle PL/SQL

Create a stored procedure that returns the number of people in the department and the maximum wage, with the department number as the parameter.

CREATE OR REPLACE PROCEDURE return_deptinfo (P_deptno emp.deptno%type, p_avgsal out Emp.sal%type, p_count out Emp.sal%type )

As

BEGIN

SELECT avg (SAL), COUNT (*) into P_avgsal,p_count from EMP WHERE Deptno=p_deptno;

EXCEPTION when No_data_found Then

Dbms_output. Put_Line (' the Department don ' t Exists! ');

END R

Eturn_deptinfo;

DECLARE
V_avgsal Emp.sal%type;
V_count number;
BEGIN
Show_emp (20);
Return_deptinfo (10,v_avgsal,v_count);
Dbms_output. Put_Line (v_avgsal| | ' ' | | V_count);
END;

Create a function that returns the maximum wage for the department as a department number parameter.
CREATE OR REPLACE FUNCTION return_maxsal
(P_deptno Emp.deptno%type)
RETURN Emp.sal%type
As
V_maxsal Emp.sal%type;
BEGIN
SELECT Max (SAL) into V_maxsal from EMP
WHERE Deptno=p_deptno;
RETURN v_maxsal;
EXCEPTION
When No_data_found Then
Dbms_output. Put_Line (' The Deptno is invalid! ');
END return_maxsal;

DECLARE
V_sal Emp.sal%type;
BEGIN
For v_dept inch (SELECT DISTINCT deptno from EMP)
LOOP
V_sal:=return_maxsal (V_DEPT.DEPTNO);
Dbms_output. Put_Line (v_dept.deptno| | ' ' | | V_sal);
END LOOP;
END;

Package consists of package specification and package body, which is stored independently in the database.

The

Creates a package that includes 2 variables, 2 procedures, and an exception.
CREATE OR REPLACE package pkg_emp
as
minsal number;
maxsal number;
E_beyondbound EXCEPTION;
PROCEDURE update_sal (
p_empno number, p_sal number);
PROCEDURE Add_employee (
p_empno number,p_sal number);
END pkg_emp;

CREATE OR REPLACE Package BODY pkg_emp
As
PROCEDURE Update_sal (p_empno number, p_sal number)
As
BEGIN
SELECT min (sal), Max (SAL) into minsal,maxsal from EMP;
IF p_sal between Minsal and Maxsal then
UPDATE emp SET sal=p_sal WHERE empno=p_empno;
IF Sql%notfound Then
Raise_application_error ( -20000, ' The employee doesn ' t exist ');
END IF;
ELSE
RAISE E_beyondbound;
END IF;
EXCEPTION
When E_beyondbound Then
Dbms_output. Put_Line (' The salary is beyond bound! ');
END update_sal;

PROCEDURE Add_employee (p_empno number,p_sal number)
As
BEGIN
SELECT min (sal), Max (SAL) into minsal,maxsal from EMP;
IF p_sal between Minsal and Maxsal then
INSERT into EMP (empno,sal) VALUES (p_empno,p_sal);
ELSE
RAISE E_beyondbound;
END IF;
EXCEPTION
When E_beyondbound Then
Dbms_output. Put_Line (' The salary is beyond bound! ');
END Add_employee;
END pkg_emp;

Overload two procedures in a package, with the department number and department name as parameters, to query the corresponding department employee name, employee number information
CREATE OR REPLACE package pkg_overload
as
PROCEDURE show_emp (P_deptno number);
PROCEDURE show_emp (p_dname VARCHAR2);
END pkg_overload;
CREATE OR REPLACE package BODY pkg_overload
as
PROCEDURE show_emp (p_deptno number)
as
BEGIN
for V_e MP in (SELECT * from emp WHERE deptno=p_deptno) LOOP
Dbms_output. Put_Line (v_emp.empno| | ' ' | |
V_emp.ename);
END LOOP;
END show_emp;

PROCEDURE show_emp (p_dname VARCHAR2)
as
V_deptno number;
BEGIN
SELECT deptno to V_deptno from dept
WHERE Dname=p_dname;
For v_emp in (SELECT * from emp WHERE deptno=v_deptno) LOOP
Dbms_output. Put_Line (v_emp.empno| | ' ' | |
V_emp.ename);
END LOOP;
END show_emp;
END pkg_overload;

Create a trigger that prohibits changing employee information on rest days,
Create or Replace Trigger Tr_sec_emp
Before insert or UPDATE or delete on EMP
Begin
If To_char (sysdate, ' DY ') in (' Saturday ', ' Sunday ') then
Raise_application_error (-20001, ' cannot modify employee information on rest day ');
End If;
End

Oracle PL/SQL