以下是solution2.sql 中可用的 SQL 脚本模板并插入
模板下面列出的操作的实现。 所有实施的行动必须
直接在相应的评论后面加上动作的说明。
SPOOL solution2
SET SERVEROUTPUT ON
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 300
SET PAGESIZE 400
/* (1) First, the script modifies the structures of a sample database such it would be possible to store information
about the total number of employees located in each country. The best design is expected in this step.
Remember to enforce the appropriate consistency constraints. */
/* (2) Next, the script modifies the contents of a sample data with information about the total employees located
in each country. Consider only the countries where at least one bank branch is located. List the names of
countries together with the total number of employees located in each country. */
/* (3) Next, the script stores in a data dictionary PL/SQL procedure that can be used to insert information about
a new employee into a sample database. Information about a new employee such as employee number, first name,
last name, date of birth, position, bank name, branch name, branch country and branch city must be passed
through the actual parameters of a stored procedure. */
/* (4) Next, the procedure is used to insert a new row into EMPLOYEE table. It must commit an insertion and it must
automatically update information about the total number of employees located in each country. An efficient
implementation of the procedure is expected.
The values of attributes describing a new employee must be passed through the input parameters of
the procedure. */
/* (5) Finally, the script list updated information about the total number of employees. */
SPOOL OFF
(1) 首先，脚本修改示例数据库的结构，以便可以存储有关位于每个国家/地区的员工总数的信息。 最好的设计预计在这一步。 请记住执行适当的一致性约束。
(2) 接下来，脚本使用每个国家/地区的员工总数信息修改示例数据的内容。 只考虑至少有一家银行分行所在的国家。 列出国家名称以及每个国家的雇员总数。
(3) 接下来，脚本将 PL/SQL 过程存储在数据字典中，该过程可用于将有关新员工的信息插入示例数据库中。 新员工的信息，如员工编号、名字、姓氏、出生日期、职位、银行名称、分行名称、分行国家和分行城市等，必须通过存储过程的实参传递。
(4) 接下来，该过程用于向 EMPLOYEE 表中插入一个新行。 它必须提交插入，并且必须自动更新有关位于每个国家/地区的员工总数的信息。 预计该程序将得到有效实施。 描述新雇员的属性值必须通过过程的输入参数传递。
(5) 最后，脚本列表更新有关员工总数的信息。
(1)要修改数据库结构以存储每个国家的员工总数信息，我们可以创建一个名为COUNTRY_EMPLOYEE的新表，其中包含两列:country_code和total_employees。我们可以通过将country_code设置为引用COUNTRY表的外键，并在添加或删除新员工时使用触发器更新total_employees列来强制一致性约束。
CREATE TABLE COUNTRY_EMPLOYEE (
country_code CHAR(2),
total_employees NUMBER(10),
CONSTRAINT pk_country_employee PRIMARY KEY (country_code),
CONSTRAINT fk_country_employee FOREIGN KEY (country_code) REFERENCES COUNTRY(country_code)
);
CREATE OR REPLACE TRIGGER trg_update_country_employee
AFTER INSERT OR DELETE ON EMPLOYEE
FOR EACH ROW
DECLARE
v_country_code CHAR(2);
BEGIN
SELECT branch_country INTO v_country_code FROM BRANCH WHERE branch_code = :new.branch_code;
IF INSERTING THEN
UPDATE COUNTRY_EMPLOYEE SET total_employees = total_employees + 1 WHERE country_code = v_country_code;
ELSIF DELETING THEN
UPDATE COUNTRY_EMPLOYEE SET total_employees = total_employees - 1 WHERE country_code = v_country_code;
END IF;
END;
(2)要修改样本数据的内容，使其显示每个国家至少有一家银行分支机构的员工总数，可以使用一条简单的SELECT语句，并将COUNTRY_EMPLOYEE和branch表连接起来，过滤出没有银行分支机构的国家:
SELECT COUNTRY.country_name, COUNTRY_EMPLOYEE.total_employees
FROM COUNTRY_EMPLOYEE
JOIN BRANCH ON COUNTRY_EMPLOYEE.country_code = BRANCH.branch_country
JOIN COUNTRY ON BRANCH.branch_country = COUNTRY.country_code
GROUP BY COUNTRY.country_name, COUNTRY_EMPLOYEE.total_employees;
(3)要存储一个可以插入新员工到数据库中的PL/SQL过程，我们可以使用CREATE procedure语句:
CREATE OR REPLACE PROCEDURE INSERT_EMPLOYEE (
p_employee_no NUMBER,
p_first_name VARCHAR2(50),
p_last_name VARCHAR2(50),
p_birth_date DATE,
p_position VARCHAR2(50),
p_bank_name VARCHAR2(50),
p_branch_name VARCHAR2(50),
p_branch_country CHAR(2),
p_branch_city VARCHAR2(50)
)
IS
BEGIN
INSERT INTO EMPLOYEE (
employee_no,
first_name,
last_name,
birth_date,
position,
branch_code
) VALUES (
p_employee_no,
p_first_name,
p_last_name,
p_birth_date,
p_position,
(SELECT branch_code FROM BRANCH WHERE branch_name = p_branch_name AND branch_country = p_branch_country AND branch_city = p_branch_city AND bank_code = (SELECT bank_code FROM BANK WHERE bank_name = p_bank_name))
);
COMMIT;
END;
(4)要使用INSERT_EMPLOYEE过程向EMPLOYEE表中插入新行，并更新COUNTRY_EMPLOYEE表，只需使用适当的输入参数调用这个过程:
EXECUTE INSERT_EMPLOYEE(12345, 'John', 'Doe', TO_DATE('1990-01-01', 'YYYY-MM-DD'), 'Manager', 'Bank of America', 'Los Angeles Branch', 'US', 'Los Angeles');
(5)要列出更新后的员工总数信息，我们可以使用action(2)中的同样的SELECT语句。
引用new bing部分指引作答：
1 首先，需要创建一个新的表来存储每个国家的总员工数信息。可以使用以下SQL语句创建该表：
CREATE TABLE COUNTRY_EMPLOYEES (
COUNTRY VARCHAR(50) NOT NULL,
TOTAL_EMPLOYEES INT NOT NULL,
CONSTRAINT PK_COUNTRY_EMPLOYEES PRIMARY KEY (COUNTRY)
);
其中，COUNTRY_EMPLOYEES表的主键为COUNTRY列。
2 接下来，需要计算每个国家的总员工数，并将结果存储到COUNTRY_EMPLOYEES表中。可以使用以下SQL语句实现：
INSERT INTO COUNTRY_EMPLOYEES (COUNTRY, TOTAL_EMPLOYEES)
SELECT B.COUNTRY, COUNT(E.EMPLOYEE_NUMBER) AS TOTAL_EMPLOYEES
FROM BANK_BRANCH B
INNER JOIN EMPLOYEE E ON B.BANK_NAME = E.BANK_NAME AND B.BRANCH_NAME = E.BRANCH_NAME
GROUP BY B.COUNTRY;
这个SQL语句将从BANK_BRANCH和EMPLOYEE表中获取数据，并根据国家分组。然后使用COUNT聚合函数计算每个国家的总员工数，并将结果插入到COUNTRY_EMPLOYEES表中。
3 现在，需要创建一个存储过程来插入新员工信息并更新COUNTRY_EMPLOYEES表。以下是一个可能的实现：
CREATE OR REPLACE PROCEDURE INSERT_NEW_EMPLOYEE(
EMP_NUM IN INT,
FIRST_NAME IN VARCHAR(50),
LAST_NAME IN VARCHAR(50),
DATE_OF_BIRTH IN DATE,
POSITION IN VARCHAR(50),
BANK_NAME IN VARCHAR(50),
BRANCH_NAME IN VARCHAR(50),
BRANCH_COUNTRY IN VARCHAR(50),
BRANCH_CITY IN VARCHAR(50)
) AS
BEGIN
-- Insert new employee
INSERT INTO EMPLOYEE (EMPLOYEE_NUMBER, FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, POSITION, BANK_NAME, BRANCH_NAME)
VALUES (EMP_NUM, FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, POSITION, BANK_NAME, BRANCH_NAME);
-- Update total employees for branch country
UPDATE COUNTRY_EMPLOYEES
SET TOTAL_EMPLOYEES = TOTAL_EMPLOYEES + 1
WHERE COUNTRY = BRANCH_COUNTRY;
COMMIT;
END;
这个存储过程将插入新员工信息并更新COUNTRY_EMPLOYEES表。它使用输入参数传递有关新员工的信息，并在插入后自动更新有关所属分行国家的员工总数。
4 现在可以使用INSERT_NEW_EMPLOYEE存储过程插入新员工信息了。以下是一个可能的示例：
EXEC INSERT_NEW_EMPLOYEE(101, 'John', 'Doe', '1990-01-01', 'Manager', 'ABC Bank', 'Main Branch', 'USA', 'New York');
这个示例将插入一个新员工，并将其分配到名为ABC Bank的银行的Main Branch分行，所在国家为美国纽约。插入后，存储过程将自动更新COUNTRY_EMPLOYEES表中USA国家的总员工数。
5 最后，可以使用以下SQL语句列出更新后的有关员工总数的信息：
SELECT country, COUNT(*) AS total_employees
FROM employee e
JOIN branch b ON e.branch_id = b.branch_id
GROUP BY country;
这个 SQL 查询将从 employee 表和 branch 表中获取数据，并根据 branch 表中的 country 字段对其进行分组，计算每个国家的总雇员人数。最终结果将包括国家名称和该国家的员工总数。
引用chatGPT作答，对于第1个操作，以下是一个可能的实现：
ALTER TABLE BRANCH
ADD (EMPLOYEE_COUNT NUMBER(10,0) DEFAULT 0,
CONSTRAINT BRANCH_EMPLOYEE_COUNT_NN CHECK (EMPLOYEE_COUNT >= 0));
CREATE TABLE COUNTRY_EMPLOYEE_COUNT (
COUNTRY VARCHAR2(50) PRIMARY KEY,
EMPLOYEE_COUNT NUMBER(10,0) DEFAULT 0,
CONSTRAINT COUNTRY_EMPLOYEE_COUNT_NN CHECK (EMPLOYEE_COUNT >= 0)
);
在 BRANCH 表中添加了一个名为 EMPLOYEE_COUNT 的新列，用于存储该分行的员工总数。然后，创建了一个名为 COUNTRY_EMPLOYEE_COUNT 的新表，该表用于存储每个国家的员工总数。这个表有一个名为 COUNTRY 的主键列和一个名为 EMPLOYEE_COUNT 的列。
对于第2个操作，以下是一个可能的实现：
UPDATE COUNTRY_EMPLOYEE_COUNT
SET EMPLOYEE_COUNT = (
SELECT COUNT(*)
FROM EMPLOYEE E
JOIN BRANCH B ON E.BRANCH_ID = B.BRANCH_ID
WHERE B.COUNTRY = COUNTRY_EMPLOYEE_COUNT.COUNTRY
);
SELECT C.COUNTRY, C.EMPLOYEE_COUNT
FROM COUNTRY_EMPLOYEE_COUNT C
JOIN BRANCH B ON C.COUNTRY = B.COUNTRY
WHERE EXISTS (
SELECT 1
FROM EMPLOYEE E
WHERE E.BRANCH_ID = B.BRANCH_ID
);
首先，更新了 COUNTRY_EMPLOYEE_COUNT 表，以便该表中的 EMPLOYEE_COUNT 列包含每个国家中员工的总数。然后，查询了 COUNTRY_EMPLOYEE_COUNT 表和 BRANCH 表，并使用 EXISTS 子句过滤了那些至少有一个分行的国家。最后，返回了国家名称和员工总数。
对于第3个操作，以下是一个可能的实现：
CREATE OR REPLACE PROCEDURE ADD_EMPLOYEE (
P_EMP_NO NUMBER,
P_FIRST_NAME VARCHAR2,
P_LAST_NAME VARCHAR2,
P_BIRTH_DATE DATE,
P_POSITION VARCHAR2,
P_BANK_NAME VARCHAR2,
P_BRANCH_NAME VARCHAR2,
P_BRANCH_COUNTRY VARCHAR2,
P_BRANCH_CITY VARCHAR2
) AS
V_BRANCH_ID BRANCH.BRANCH_ID%TYPE;
BEGIN
SELECT BRANCH_ID
INTO V_BRANCH_ID
FROM BRANCH
WHERE NAME = P_BRANCH_NAME AND COUNTRY = P_BRANCH_COUNTRY AND CITY = P_BRANCH_CITY;
INSERT INTO EMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME, BIRTH_DATE, POSITION, BRANCH_ID)
VALUES (P_EMP_NO, P_FIRST_NAME, P_LAST_NAME, P_BIRTH_DATE, P_POSITION, V_BRANCH_ID);
UPDATE COUNTRY_EMPLOYEE_COUNT
SET EMPLOYEE_COUNT = EMPLOYEE_COUNT + 1
WHERE COUNTRY = P_BRANCH_COUNTRY;
END;
该存储过程 ADD_EMPLOYEE 接受一些输入参数，这些参数描述了一个新员工的信息，如员工编号、名字、姓氏、出生日期、职位、银行名称、分行名称、分行国家和分行城市等。该存储过程首先查找 BRANCH 表以获取分行 ID，然后将新员工的信息插入 EMPLOYEE 表。
/* (4) 接下来，该过程用于向 EMPLOYEE 表中插入一个新行。它必须提交插入，并且必须自动更新有关位于每个国家/地区的员工总数的信息。预计该程序将得到有效实施。
描述新雇员的属性值必须通过过程的输入参数传递。*/
CREATE OR REPLACE PROCEDURE insert_employee(
p_empno IN NUMBER,
p_fname IN VARCHAR2,
p_lname IN VARCHAR2,
p_dob IN DATE,
p_position IN VARCHAR2,
p_bank_name IN VARCHAR2,
p_branch_name IN VARCHAR2,
p_branch_country IN VARCHAR2,
p_branch_city IN VARCHAR2
) AS
v_branch_id BRANCH.branch_id%TYPE;
BEGIN
-- 基于 branch_name、branch_country 和 branch_city 获取 branch_id
SELECT branch_id
INTO v_branch_id
FROM BRANCH
WHERE name = p_branch_name AND country = p_branch_country AND city = p_branch_city;
-- 将新员工行插入到 EMPLOYEE 表中
INSERT INTO EMPLOYEE(empno, fname, lname, dob, position, branch_id)
VALUES(p_empno, p_fname, p_lname, p_dob, p_position, v_branch_id);
-- 为新员工所在的分支机构更新 BRANCH 表中的 employees_total 列
UPDATE BRANCH
SET employees_total = employees_total + 1
WHERE branch_id = v_branch_id;
-- 为新员工所在的国家/地区更新 COUNTRY 表中的 employees_total 列
UPDATE COUNTRY
SET employees_total = employees_total + 1
WHERE country_id = (
SELECT country_id
FROM BRANCH
WHERE branch_id = v_branch_id
);
COMMIT;
DBMS_OUTPUT.PUT_LINE('新员工插入成功。');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('插入新员工时出错: ' || SQLERRM);
END;
/
/* 说明:
以上过程 "insert_employee" 通过输入参数描述新员工，并向 EMPLOYEE 表中插入一行。在成功插入后，它将针对所插入的新员工的对应分支机构和国家/地区更新 BRANCH 和 COUNTRY 表的 "employees_total" 列。COMMIT 语句确认插入，而 DBMS_OUTPUT.PUT_LINE 语句在控制台上打印成功消息。如果出现任何异常，ROLLBACK 语句将回滚事务，并在控制台上打印错误消息。*/
/* (5) 最后，脚本列表更新有关员工总数的信息。*/
SELECT c.country_name, SUM(b.employees_total) AS total_employees
FROM COUNTRY c
JOIN BRANCH b ON c.country_id = b.country_id
GROUP BY c.country_name;
/* 说明:
以上 SQL 查询列出国家名称和每个国家的总员工数。它使用 BRANCH 表的 "employees_total" 列，并使用 BRANCH 表的 "country_id" 列和 COUNTRY 表的 "country_name" 列按国家进行分组。SUM() 函数计算每个国家的总员工数。*/