创建一个包,要求该包中包含下面的功能:对于YGGL数据库中已存在的员工表,输入员工所在的部门编号,创建函数,计算该部门员工的平均收入;创建存储过程,查询该部门员工的最低收入,调用包时在屏幕上显示查询信息。
时间: 2024-05-12 15:14:10 浏览: 80
员工管理数据表+测试数据YGGL.sql
首先,在数据库中创建一个员工表,包含员工编号、姓名、部门编号、收入等字段。我们可以使用下面的SQL语句创建一个名为"employees"的员工表:
```
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
dept_id INT,
salary INT
);
```
接下来,我们可以创建一个名为"employee_pkg"的包,该包包含两个函数和一个存储过程:
1. 一个名为"get_avg_salary"的函数,用于计算指定部门的平均收入。该函数接受一个部门编号作为参数,并返回该部门员工的平均收入。函数的实现如下:
```
CREATE OR REPLACE PACKAGE employee_pkg AS
FUNCTION get_avg_salary(dept_id IN INT) RETURN INT;
PROCEDURE get_min_salary(dept_id IN INT);
END employee_pkg;
/
CREATE OR REPLACE PACKAGE BODY employee_pkg AS
FUNCTION get_avg_salary(dept_id IN INT) RETURN INT AS
avg_salary INT;
BEGIN
SELECT AVG(salary) INTO avg_salary FROM employees WHERE dept_id = dept_id;
RETURN avg_salary;
END get_avg_salary;
PROCEDURE get_min_salary(dept_id IN INT) AS
min_salary INT;
BEGIN
SELECT MIN(salary) INTO min_salary FROM employees WHERE dept_id = dept_id;
DBMS_OUTPUT.PUT_LINE('The minimum salary in department ' || dept_id || ' is ' || min_salary);
END get_min_salary;
END employee_pkg;
/
```
2. 一个名为"get_min_salary"的存储过程,用于查询指定部门的最低收入,并在屏幕上显示查询信息。该存储过程接受一个部门编号作为参数,不返回结果。存储过程的实现如下:
```
CREATE OR REPLACE PACKAGE employee_pkg AS
FUNCTION get_avg_salary(dept_id IN INT) RETURN INT;
PROCEDURE get_min_salary(dept_id IN INT);
END employee_pkg;
/
CREATE OR REPLACE PACKAGE BODY employee_pkg AS
FUNCTION get_avg_salary(dept_id IN INT) RETURN INT AS
avg_salary INT;
BEGIN
SELECT AVG(salary) INTO avg_salary FROM employees WHERE dept_id = dept_id;
RETURN avg_salary;
END get_avg_salary;
PROCEDURE get_min_salary(dept_id IN INT) AS
min_salary INT;
BEGIN
SELECT MIN(salary) INTO min_salary FROM employees WHERE dept_id = dept_id;
DBMS_OUTPUT.PUT_LINE('The minimum salary in department ' || dept_id || ' is ' || min_salary);
END get_min_salary;
END employee_pkg;
/
```
可以使用以下语句调用包中的函数和存储过程:
```
SET SERVEROUTPUT ON;
DECLARE
avg_salary INT;
BEGIN
avg_salary := employee_pkg.get_avg_salary(1);
DBMS_OUTPUT.PUT_LINE('The average salary in department 1 is ' || avg_salary);
employee_pkg.get_min_salary(1);
END;
/
```
以上代码将调用"get_avg_salary"函数计算部门1的平均收入,并输出结果。然后,它将调用"get_min_salary"存储过程查询部门1的最低收入,并在屏幕上显示查询信息。
阅读全文