Oracle级联关系-CSDN博客

CREATE TABLE cust(cust_id NUMBER(5) PRIMARY KEY, 
cust_name VARCHAR2(10), cust_age NUMBER(3));

CREATE TABLE trx(trx_id NUMBER(5) PRIMARY KEY, trx_dte DATE, 
cust_id NUMBER(5), trx_bal NUMBER(8,2));

truncate table cust;
truncate table trx;

--交易id
--交易日期
--客户id
--交易金额
insert into CUST (CUST_ID, CUST_NAME, CUST_AGE) values (1, '小微', 17);
insert into CUST (CUST_ID, CUST_NAME, CUST_AGE) values (2, '小白', 19);
insert into TRX (TRX_ID, TRX_DTE, CUST_ID, TRX_BAL)
values (1, to_date('05-06-2024', 'dd-mm-yyyy'), 1, 20000.00);
insert into TRX (TRX_ID, TRX_DTE, CUST_ID, TRX_BAL)
values (2, to_date('05-06-2024', 'dd-mm-yyyy'), 2, 30000.00);
insert into TRX (TRX_ID, TRX_DTE, CUST_ID, TRX_BAL)
values (3, to_date('05-06-2024', 'dd-mm-yyyy'), 1, 64000.00);
insert into TRX (TRX_ID, TRX_DTE, CUST_ID, TRX_BAL)
values (4, to_date('05-06-2024', 'dd-mm-yyyy'), 2, 20000.00);
COMMIT;

SELECT * FROM cust;

SELECT * FROM trx;

--update  方法1
UPDATE trx t
   SET t.trx_bal = t.trx_bal - (SELECT CUST_AGE * 10
                                  FROM cust c
                                 WHERE t.cust_id = c.cust_id)
 WHERE t.cust_id IN (SELECT cust_id FROM cust WHERE CUST_AGE < 18);
COMMIT;

--merge into 方法2
BEGIN
  MERGE INTO trx t
  USING cust c
  ON (t.cust_id = c.cust_id)
  WHEN MATCHED THEN
    UPDATE
       SET t.trx_bal = t.trx_bal - 10 * c.CUST_AGE
     WHERE c.CUST_AGE < 18;
END;

---游标
DECLARE
  CURSOR c1 IS
    SELECT * FROM cust WHERE CUST_AGE < 18;
BEGIN
  FOR i IN c1 LOOP
    UPDATE trx t
       SET t.trx_bal = t.trx_bal - 10 * i.CUST_AGE
     WHERE t.cust_id = i.cust_id;
  END LOOP;
END;

SELECT * FROM trx;

2.2练习2

示例：假设我们有一个学校的数据库，包含以下两个表：

1. Students（学生表） - 包含学生信息。

   - StudentID (主键)

   - Name

   - ClassID (外键，指向Classes表)

2. Classes（班级表） - 包含班级信息。

   - ClassID (主键)

   - ClassName

--当班级的ID更改时，所有属于该班级的学生的ClassID也自动更新。

--创建表和设置外键

--创建两个表，并在Students表的ClassID字段上设置外键约束

drop table Students;
drop table Classes;

CREATE TABLE Classes (
    ClassID number PRIMARY KEY,
    ClassName VARCHAR2(100)
);

CREATE TABLE Students (
    StudentID NUMBER PRIMARY KEY,
    Name VARCHAR2(100),
    ClassID NUMBER,
    CONSTRAINT FK_Student_Class FOREIGN KEY (ClassID)
    REFERENCES Classes (ClassID)--依赖外表主键
);

INSERT INTO Classes (ClassID, ClassName) VALUES (1, 'Math');
INSERT INTO Classes (ClassID, ClassName) VALUES (2, 'Science');

INSERT INTO Students (StudentID, Name, ClassID) VALUES (101, 'Alice', 1);
INSERT INTO Students (StudentID, Name, ClassID) VALUES (102, 'Bob', 2);
COMMIT;

SELECT * FROM Classes;

SELECT * FROM Students;

--创建触发器
CREATE OR REPLACE TRIGGER update_student_classid
  AFTER UPDATE ON Classes
  FOR EACH ROW
BEGIN
  UPDATE Students SET ClassID = :NEW.ClassID WHERE ClassID = :OLD.ClassID;
END;

--执行更新操作——更新班级Math的ClassID：
UPDATE Classes SET ClassID = 3 WHERE ClassName = 'Math';
COMMIT;

Alice的ClassID已从1更新为3，以匹配班级Math的新ID：

SELECT * FROM Classes;

SELECT * FROM Students;

3.MERGE INTO 和 UPDATE的区别和性能

MERGE INTO 和 UPDATE 都是用于修改数据库表中已有数据的SQL语句，但它们在用途和效率上有所不同，具体取决于你的应用场景。

3.1UPDATE

用途：UPDATE 语句主要用于修改表中已存在的记录。它直接根据指定的条件定位到一行或多行数据，并修改这些行的一个或多个列的值。

效率：当只需要基于简单的条件更新少量或特定几行数据时，UPDATE很直接且高效。但如果更新逻辑复杂，涉及到多个表联查或需要判断是否存在再决定是否更新时，效率可能会下降。

3.2MERGE INTO

用途：MERGE INTO(也称为 UPSERT，即UPDATE+INSERT)是一种更复杂的语句，它结合了UPDATE和INSERT的功能，能够在一次操作中根据匹配条件更新现有行，或者在没有匹配项时插入新行。这对于同步或合并两个数据集特别有用，常用于数据仓库加载、ETL过程等场景。

效率：当需要根据一个源表或子查询的结果来同时执行更新和插入操作时，MERGE INTO 可以减少网络往返和事务数量，从而在某些情况下提高效率。但是，由于MERGE INTO需要执行更复杂的逻辑（比如匹配检查、决定是更新还是插入），如果处理大量数据或匹配条件复杂，其执行计划可能更为复杂，导致性能不如直接的UPDATE。

3.3性能对比

对于简单的更新操作，UPDATE 通常更直接且执行效率更高。
当存在“如果存在则更新，否则插入”的需求时，使用MERGE INTO可以更高效地一次性处理，减少了代码复杂度和潜在的错误。
MERGE INTO 在处理大量数据或复杂逻辑时，其性能取决于数据库的优化器如何生成执行计划，有时可能需要手动调整以达到最佳性能。

3.4总结

总的来说，选择MERGE INTO还是UPDATE应基于具体的操作需求。如果只是简单更新，使用UPDATE更合适；如果需要根据条件更新或插入数据，MERGE INTO提供了更灵活的解决方案，但要注意其潜在的性能开销。在实际应用中，建议进行性能测试，以确定最适合你应用场景的方法。

4.级联更新注意事项

级联更新（Cascading Update）是在数据库中维护数据完整性的一种机制，通常与外键约束一起使用。当主表中的一个字段被更新时，所有引用该字段的从表中的相应字段也会自动更新。

级联更新是数据库维护引用完整性的有用工具，但使用时需要谨慎，因为它可能会无意中更新大量记录。

在实施级联更新之前，确保了解所有受影响的记录，并在测试环境中验证更新逻辑。
级联更新通常与数据库事务一起使用，以确保数据的一致性和可恢复性。