数据库漫游记：表、视图、函数、存储过程及触发器之跨平台兼容性分析（上）

玦尘、

于 2024-08-02 19:25:16 发布

阅读量326

点赞数 24

文章标签：数据库 oracle mysql postgresql

本文链接：https://blog.csdn.net/weixin_74199893/article/details/140814891

版权

先言之

🌟余撰此文，乃为导引初窥数据库之学人，俾其明了表、视图、函数、存储过程及触发器之义理，及其于诸般平台之上创建、修改与废弃之法式。盖初学之人，常陷于迷雾之中，难辨东西，故须详述而明之。谨记，学者务须多览官方文档！本文皆基于官方文档撰写，以求精准无误。 盖官方文档乃权威之所出，详实可信，为学者之必备良伴。

☘️ 此文颇长，望阅者徐徐览之。 既如此，吾将逐一简述表、视图、函数、存储过程及触发器之基本概念与用途，以供初学之人参考焉。

表：表者，乃数据库之基本单元也。其形似于二维之表格，由行列交织而成。每列各有其名与数据类型，或为整数，或为文字，或为日期。每行则代表一记录，载有该记录之所有相关信息。表之间可通过主键与外键之关联，相互连接，从而构成复杂之数据模型。如此，数据间之联系得以明晰，信息之组织更显有序。
视图：视图者，乃虚拟之表也。实则存储一SQL查询之结果，基于一表或多表，以展示特定之数据集合。视图之设，可简化数据之访问，且提供一法以保护敏感之数据，盖因其仅显示定义之特定部分数据也。以此，使用者得以便捷之方式观览所需信息，而无需直接接触底层之复杂数据结构。
函数：函数者，乃预先定义或由用户自定义之一组SQL语句也，用以执行特定之任务并返回一值。诸般数据库皆备有许多内置之函数，诸如数学函数、字符串函数、日期函数等。此外，亦可创建用户自定义之函数以满足特定之需求。
存储过程：存储过程者，乃预编译之一组SQL语句也，可接受输入参数、返回输出参数或执行一系列之操作。其可视为数据库内之子程序，于必要之时被调用。存储过程之设，有益于提高代码之重用性与执行效率，并能封装复杂之业务逻辑。
触发器：触发器者，乃一种特殊之存储过程也，于特定之数据库事件发生时自动执行。此等事件包括插入、更新或删除等操作。触发器之用，在于维护数据之完整性和一致性，例如于插入新记录前检查某些条件是否满足，或于更新记录后执行一些清理工作。以此，确保数据库行为符合预设之规则与要求。

🍀通过深入理解这些数据库对象于不同平台之上之实现方式与兼容性差异，学者将能为此后之数据库应用与开发奠定坚实之基础。盖数据库之学，乃现代信息技术之基石，精通其道，方能驾驭数据洪流，使之服务于世。

🤲愿初学者以此为基础，不断探索，终成大家。

🔥Mysql 8.0

在这里插入图片描述

参考：https://dev.mysql.com/doc/refman/8.0/en/

MySQL 是一个开源的关系型数据库管理系统（RDBMS），对表、视图、函数、存储过程和触发器都有全面的支持。MySQL 使用标准 SQL 语言，并支持 PL/SQL 样式的存储过程语言。

🌕表（Table）

这四个是Mysql自带的系统表：
在这里插入图片描述

information_schema：这是一个虚拟数据库，它包含了所有数据库对象（如表、视图、索引等）的相关元数据。您可以在这个数据库中查询到关于其他数据库的信息，例如表结构、权限设置等等。它是获取数据库全局信息的一个重要来源。
mysql：这是 MySQL 自带的一个系统数据库，其中存储着与系统相关的配置信息和用户权限数据。例如，用户账户、权限、事件调度器的设置等都保存在这里。管理员通常会在该数据库中进行权限管理和配置修改操作。
performance_schema：这个数据库主要用于收集和监控 MySQL 服务器性能相关的信息。它提供了详细的性能指标和资源消耗统计，帮助管理员分析和优化数据库性能。通过查看此数据库中的表，您可以了解哪些线程正在运行、内存使用情况、锁等待时间等信息。
sys：这是 MySQL 5.7 及更高版本引入的一个新的系统数据库，提供了一组视图，简化了从 performance_schema 和 information_schema 获取性能数据的过程。它包含了一些预定义的视图，使得管理员能够更容易地访问和理解性能数据。

🌖SELECT TABLE

🌰：列出数据库中的所有表信息

SELECT * FROM information_schema.TABLES

🌰：列出指定数据库中的所有表信息

SELECT * FROM information_schema.TABLES WHERE table_schema = 'your_database_name';

🌰：如果你想知道表的具体内容，可以使用 SELECT 查询：

SELECT * FROM database_name.table_name

🌗CREATE TABLE

☘️详细语法：https://dev.mysql.com/doc/refman/8.0/en/create-table.html

🌰（初步）：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

🌰（高深）：

CREATE TABLE `facility_info` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增逐渐',
  `type` tinyint DEFAULT NULL COMMENT '类型（1:公寓图片,2:房间图片）',
  `name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '名称',
  `icon` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT NULL COMMENT '更新时间',
  `is_deleted` tinyint DEFAULT '0' COMMENT '是否删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=58 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='配套信息表'

🌘ALTER TABLE

☘️详细语法：https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

🌰（初步）：

ALTER TABLE `attr_value`
ADD COLUMN `description` varchar(255) 
CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '描述';

🌰（高深）：

ALTER TABLE `attr_value`
MODIFY COLUMN `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '属性value',
MODIFY COLUMN `is_deleted` BOOLEAN DEFAULT FALSE COMMENT '是否删除',
ADD COLUMN `status` ENUM('ENABLED', 'DISABLED') DEFAULT 'ENABLED' NOT NULL COMMENT '状态',
ADD CONSTRAINT `uniq_attr_key_id_name` UNIQUE (`attr_key_id`, `name`);

🌑DROP TABLE

☘️详细语法：https://dev.mysql.com/doc/refman/8.0/en/drop-table.html

🌰（初步）：

DROP TABLE [database_name.]table_name1,[database_name.]table_name2,...

🌰(高深)：RESTRICT 参数表示如果有其他对象依赖于这些视图，则阻止删除操作。如果你想要强制删除表，即使有其他对象依赖于它们，可以使用 CASCADE 参数代替 RESTRICT。

DROP TABLE IF EXISTS [database_name.]table_name RESTRICT

🌕视图（View）

🌖SELECT VIEW

🌰：这将显示数据库中的所有视图及其名称。

SHOW FULL TABLES WHERE Table_type = 'VIEW';

🌰：找到你感兴趣的视图，然后使用 SHOW CREATE VIEW 查看其详细信息。

SHOW CREATE VIEW view_name;

🌰：如果你想知道视图的具体内容，可以使用 SELECT 查询。

SELECT * FROM view_name;

🌗CREATE VIEW

☘️详细语法：https://dev.mysql.com/doc/refman/8.0/en/create-view.html

🌰(初步)：facility_info表在上述CREATE TABLE的第二个例子中。这个视图 facility_info_view 包含了 id、type、name 和 icon 字段，只显示未被标记为删除（is_deleted = 0）的设施信息。

CREATE VIEW facility_info_view AS
SELECT id, type, name, icon
FROM facility_info
WHERE is_deleted = 0;

🌰（高深）：OR REPLACE 选项允许你在视图存在的情况下替换现有视图，而不需要先删除再重新创建。ALGORITHM = UNDEFINED 表示 MySQL 根据需要选择最适合的算法来实现视图。DEFINER = root@localhost 设置了视图的定义者，SQL SECURITY DEFINER 表示视图的安全级别，即只有定义者才能看到视图中的数据。WITH CASCADED CHECK OPTION 限制了插入或更新视图时的数据完整性。

CREATE OR REPLACE ALGORITHM = UNDEFINED 
DEFINER = root@localhost 
SQL SECURITY DEFINER 
VIEW facility_info_view (id, type, name, icon) 
AS 
SELECT id, type, name, icon
FROM facility_info
WHERE is_deleted = 0
WITH CASCADED CHECK OPTION;

🌘ALTER VIEW

☘️详细语法：https://dev.mysql.com/doc/refman/8.0/en/alter-view.html

🌰（初步）：

ALTER VIEW facility_info_new_view AS SELECT id, type, name
FROM facility_info
WHERE is_deleted = 0;

🌰（高深）：ORDER BY name ASC 添加了一个排序顺序，使得视图中的结果按照 name 字段升序排列。

ALTER ALGORITHM = UNDEFINED 
DEFINER = root@localhost 
SQL SECURITY DEFINER 
VIEW facility_info_view (id, type, name, icon) 
AS 
SELECT id, type, name, icon
FROM facility_info
WHERE is_deleted = 0
ORDER BY name ASC
WITH CASCADED CHECK OPTION;

🌑DROP VIEW

☘️详细语法：https://dev.mysql.com/doc/refman/8.0/en/drop-view.html

🌰（初步）：

DROP VIEW [database_name.]facility_info_view;

🌰（高深）：RESTRICT 参数表示如果有其他对象依赖于这些视图，则阻止删除操作。如果你想要强制删除视图，即使有其他对象依赖于它们，可以使用 CASCADE 参数代替 RESTRICT。

DROP VIEW IF EXISTS [database_name.]facility_info_view RESTRICT;

🌕函数（Function）

🌖SELECT FUNCTION

🌰：这个表包含了所有函数的信息。如果想要查询特定数据库中的函数，可以将 DATABASE() 替换为具体数据库名称。

SELECT * FROM information_schema.routines 
WHERE routine_schema = DATABASE() 
AND routine_type = 'FUNCTION';

🌰：查询指定函数的详情信息。

SHOW CREATE FUNCTION function_name;

🌗CREATE FUNCTION

☘️详细语法：该CREATE FUNCTION语句可用于创建存储函数和可加载函数
https://dev.mysql.com/doc/refman/8.0/en/create-function.html

🌰（初步）：创建存储函数的 CREATE FUNCTION 语句。RETURNS INT 指定了函数的返回类型为整数。DETERMINISTIC 关键字表示这个函数总是返回相同的输出，只要输入相同。这意味着对于给定的输入，函数总是产生相同的输出。

CREATE FUNCTION add_two_numbers
(
  num1 INT,
  num2 INT
)
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN num1 + num2;
END;

// 调用函数
SELECT add_two_numbers(5,10);

在这里插入图片描述

🌰（高深）：计算一个 JSON 数组中所有数值元素的平均值。

CREATE FUNCTION calculate_average_score (
  scores JSON
) RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  DECLARE total DECIMAL(10,2) DEFAULT 0.00;
  DECLARE count INT DEFAULT 0;
  DECLARE score DECIMAL(10,2);
  DECLARE i INT DEFAULT 0;

  -- Calculate the number of elements in the JSON array
  SET count = JSON_LENGTH(scores);

  -- Iterate over the JSON array and calculate the total
  WHILE i < count DO
    SET score = CAST(JSON_UNQUOTE(JSON_EXTRACT(scores, CONCAT('$[', i, ']'))) AS DECIMAL(10,2));
    SET total = total + score;
    SET i = i + 1;
  END WHILE;

  -- Return the average score
  RETURN total / count;
END

// 调用函数
SELECT calculate_average_score('[85.5, 90.0, 78.5, 92.0]') AS average_score;

在这里插入图片描述

🌰：可加载函数的 CREATE FUNCTION 语句。它返回一个整数值，并加载名为 udf_add.so 的共享库。udf_add.so 应该是一个编译后的 C/C++ 动态链接库。

CREATE FUNCTION add_two_numbers
RETURNS INTEGER
SONAME 'udf_add.so';

🌘ALTER FUNCTION

☘️详细语法：https://dev.mysql.com/doc/refman/8.0/en/alter-function.html

🌰（初步）：接收两个整数参数并返回它们的和。

ALTER FUNCTION add_numbers(integer, integer)
COMMENT 'This function adds two numbers together.';

🌰（高深）：Language指定了函数的执行语言为 SQL。Contains SQL指明函数内部包含 SQL 语句，但不直接读取或修改数据。SQL Security设置为 INVOKER, 这意味着函数会以调用者的权限执行。

ALTER FUNCTION complex_function()
LANGUAGE SQL
CONTAINS SQL
SQL SECURITY INVOKER
COMMENT 'This function performs complex operations.';

🌑DROP FUNCTION

☘️详细语法：该DROP FUNCTION语句用于删除存储函数和可加载函数
https://dev.mysql.com/doc/refman/8.0/en/drop-function.html

🌰：用于删除存储例程（存储过程或函数）的SQL语句。不带参数。

DROP FUNCTION IF EXISTS [database_name.]my_complex_function;

🌰：此语句删除名为的可加载函数 function_name。不带参数。

DROP FUNCTION IF EXISTS [database_name.]add_numbers;

🌕存储过程（Procedure）

🌖SELECT PROCEDURE

🌰：这个表包含了所有存储过程的信息。如果想要查询特定数据库中的存储过程，可以将 DATABASE() 替换为具体数据库名称。

SELECT * FROM information_schema.routines 
WHERE routine_schema = DATABASE() 
AND routine_type = 'PROCEDURE';

🌰：查询指定存储过程的详情信息。

SHOW CREATE PROCEDURE procedure_name;

🌗CREATE PROCEDURE

☘️详细语法：https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

🌰（初步）：该存储过程不接受任何参数，并打印一条消息。

// 创建procedure
CREATE PROCEDURE simple_sp ()
BEGIN
    SELECT 'Hello, this is a simple stored procedure!';
END

// 执行procedure
CALL simple_sp();

在这里插入图片描述

🌰（高深）：这段存储过程 citycount 接受一个国家代码作为输入参数，并计算该国家的城市总数。它通过查询 world.city 表并使用 COUNT(*) 函数来统计城市数量，最终将结果存储在输出参数 cities 中返回给调用者。

// 创建procedure
CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT) 
BEGIN
SELECT COUNT(*) INTO cities FROM world.city
WHERE CountryCode = country;
END

// 执行procedure
CALL citycount('JPN', @cities);
// 查看结果
SELECT @cities;

在这里插入图片描述

🌘ALTER PROCEDURE

☘️详细语法：https://dev.mysql.com/doc/refman/8.0/en/alter-procedure.html

🌰（初步）：假设有一个名为 my_procedure 的存储过程，现在为其添加一个注释。

ALTER PROCEDURE my_procedure
COMMENT 'This is a simple procedure.';

🌰（高深）：Contains SQL指明存储过程中包含 SQL 语句，但不直接读取或修改数据。SQL Security设置为 INVOKER，这意味着存储过程将以调用者的权限执行。

ALTER PROCEDURE my_procedure
LANGUAGE SQL
CONTAINS SQL
SQL SECURITY INVOKER
COMMENT 'This is a more complex procedure that modifies data.';

🌑DROP PROCEDURE

☘️详细语法：https://dev.mysql.com/doc/refman/8.0/en/drop-procedure.html

🌰：

DROP PROCEDURE IF EXISTS [database_name.]procedure_name;

🌕触发器（Trigger）

🌖SELECT TRIGGER

🌰：这个表包含了所有触发器的信息。如果想要查询特定数据库中的触发器，可以将 DATABASE() 替换为具体数据库名称。

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE();

🌰：查询指定触发器。

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_NAME = 'my_trigger' 
AND TRIGGER_SCHEMA = 'my_database';

🌗CREATE TRIGGER

☘️详细语法：https://dev.mysql.com/doc/refman/8.0/en/create-trigger.html

🌰（初步）：假设有一个名为 employees 的表，要在插入新员工记录时自动更新另一个名为 employee_counts 的表中的员工总数。

CREATE TRIGGER update_employee_count
AFTER INSERT
ON employees
FOR EACH ROW
BEGIN
    UPDATE employee_counts
    SET count = count + 1;
END;

🌰（高深）：假设我们有一个名为 orders 的表，我们想要在更新订单状态时执行以下操作：如果订单状态变为 “Shipped”，则更新 shipped_orders 表中的记录。如果订单状态变为 “Cancelled”，则更新 cancelled_orders 表中的记录。并且确保在执行这些操作时，按照特定顺序执行触发器。

CREATE TRIGGER process_order_status_change
AFTER UPDATE
ON orders
FOR EACH ROW
BEGIN
    IF NEW.status = 'Shipped' THEN
        INSERT INTO shipped_orders (order_id, shipped_date)
        VALUES (NEW.order_id, CURRENT_DATE());
    ELSEIF NEW.status = 'Cancelled' THEN
        INSERT INTO cancelled_orders (order_id, cancellation_date)
        VALUES (NEW.order_id, CURRENT_DATE());
    END IF;
    
    -- 确保触发器按特定顺序执行
    IF EXISTS (SELECT 1 FROM information_schema.triggers WHERE trigger_name = 'update_inventory') THEN
        SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'Trigger process_order_status_change must precede update_inventory.';
    END IF;
END;

🌘ALTER TRIGGER ❌

☝️MySQL 目前（截至 2024 年）不支持直接使用 ALTER TRIGGER 语句来修改触发器。如果需要修改触发器，通常的做法是先删除现有的触发器，然后再重新创建它。

🌑DROP TRIGGER

☘️详细语法：https://dev.mysql.com/doc/refman/8.0/en/drop-trigger.html

🌰：

DROP TRIGGER IF EXISTS [database_name.]trigger_name;

🔥Oracle Database Release 21

在这里插入图片描述

Oracle 同样是一个关系型数据库管理系统 (RDBMS)，，对表、视图、函数、存储过程和触发器都有全面的支持。Oracle 使用标准 SQL 语言，并支持 PL/SQL，这是一种专门用于 Oracle 数据库的程序化扩展。

🌕表（Table）

Oracle自带的Schema：

AUDSYS: 主要用于审计功能，存储统一审计日志和配置信息。
DBSFWUSER: 通常用于Database Firewall功能，具体取决于您的环境。
DGPDB_INT: 通常与Data Guard Broker相关，具体取决于您的环境。
DVSYS: 由Oracle Database Vault使用，用于访问控制配置和策略。
GGSYS: Oracle GoldenGate软件的一部分，用于数据复制和集成。
GSMADMIN_INTERNAL: Oracle Global Data Services的内部用户，用于管理全局数据服务。
GSMCATUSER: Oracle Global Data Services的目录用户，用于存储目录元数据。
GSMROOTUSER: Oracle Global Data Services的根用户，用于管理全局根域。
GSMUSER: Oracle Global Data Services的普通用户，用于日常操作管理。
LBACSYS: Label-Based Access Control System的schema，用于标签安全性管理。
OPS$ORACLE: 外部身份验证schema，用于操作系统用户映射。
REMOTE_SCHEDULER_AGENT: 远程调度代理的一部分，用于通过数据库调度执行任务。
SYSDG: 用于Oracle Data Guard Broker操作的内部用户，涉及高可用性和灾难恢复。
SYSKM: 用于Oracle Key Vault的密钥管理，涉及数据加密和解密。
SYSRAC: 用于Oracle Real Application Clusters (RAC)操作的内部用户，涉及集群管理。
ANONYMOUS: 代表未认证的PL/SQL Web Toolkit用户，通常用于Web应用匿名访问。
APPQOSSYS: Oracle Quality of Service Management的一部分，用于性能管理和优化。
DBSNMP: Oracle Enterprise Manager中的SNMP代理用户，用于数据库监控。
DIP: Oracle Directory Integration Platform的一部分，用于目录同步和数据交换。
ORACLE_OCM: Oracle Configuration Manager的一部分，用于收集、报告和管理配置信息。
OUTLN: 存储和管理存储轮廓（即执行计划大纲），帮助优化器选择执行路径。
SYS: Oracle数据库的核心schema，包含所有系统级对象，如数据字典视图、存储过程和函数等。
SYSTEM: 一个高权限的用户schema，通常用于存储数据库管理相关对象。生产环境中不建议用于存放业务数据。
XDB: 代表XML Database，用于存储和管理XML数据。包含XMLType表、存储过程和函数等XML相关功能。
XS$NULL: 一个内部使用的schema，通常用于Oracle内部临时对象存储。

🌖SELECT TABLE

🌰：查询某一特定schema下的表

-- 查询特定 schema 下的所有表
SELECT * FROM all_tables
WHERE owner = 'SCHEMA_NAME'

🌰：查询特定表的具体信息

-- 获取特定 schema 下表的详细信息
SELECT * FROM all_tab_columns
WHERE owner = 'SCHEMA_NAME'
AND table_name = 'TABLE_NAME';

🌗CREATE TABLE

☘️详细语法：https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-TABLE.html

🌰（初步）：

CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(20),
    last_name VARCHAR2(25),
    email VARCHAR2(25),
    hire_date DATE,
    job_id VARCHAR2(10),
    salary NUMBER(8, 2),
    commission_pct NUMBER(2, 2),
    manager_id NUMBER(6),
    department_id NUMBER(4)
);


// 添加数据，后面CREATE VIEW要用！！！
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES (1, 'John', 'Doe', 'john.doe@example.com', TO_DATE('2021-01-15', 'YYYY-MM-DD'), 'IT_PROG', 60000, 0.1, 101, 10);

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES (2, 'Jane', 'Smith', 'jane.smith@example.com', TO_DATE('2022-02-20', 'YYYY-MM-DD'), 'HR_REP', 50000, 0.05, 102, 20);

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES (3, 'Alice', 'Johnson', 'alice.johnson@example.com', TO_DATE('2023-03-25', 'YYYY-MM-DD'), 'FIN_ANA', 55000, NULL, 103, 30);

🌰（高深）：CREATE GLOBAL TEMPORARY TABLE 创建一个全局临时表，这种类型的表在会话之间不可见，但在同一个会话内可见。ON COMMIT PRESERVE ROWS 指定在提交事务后保留表中的行，直到会话结束。MEMOPTIMIZE FOR READ 优化表的存储，以便提高读取操作的性能。这可以减少磁盘 I/O，并使数据更适合内存访问。

CREATE GLOBAL TEMPORARY TABLE temp_employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(20),
    last_name VARCHAR2(25),
    email VARCHAR2(25),
    hire_date DATE,
    job_id VARCHAR2(10),
    salary NUMBER(8, 2),
    commission_pct NUMBER(2, 2),
    manager_id NUMBER(6),
    department_id NUMBER(4)
)
ON COMMIT PRESERVE ROWS;

🌘ALTER TABLE

☘️详细语法：https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ALTER-TABLE.html

笔记：Oracle 建议您尽可能使用ALTER MATERIALIZED VIEW LOG语句而不是ALTER TABLE对物化视图日志表进行操作。在Oracle数据库中，ALTER TABLE语句有严格的语法要求，需要分别执行ALTER TABLE操作。

🌰（初步）：添加一个新的列 department_id。

ALTER TABLE employees
ADD department_id NUMBER(4);

🌰（高深）：修改表的名称。添加一个检查约束，确保 salary 大于 0。添加一个唯一约束，确保 email 字段的值是唯一的。禁用表上的所有触发器。

// 添加唯一约束
ALTER TABLE employees
ADD CONSTRAINT check_salary CHECK (salary > 0);

// 重命名表
ALTER TABLE employees
ADD CONSTRAINT unique_email UNIQUE (email);

// 禁用所有触发器
ALTER TABLE employees
RENAME TO employees_new;

在这里插入图片描述

CUBE TABLE（了解即可）

☘️详细语法：https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CUBE_TABLE.html

🌰：创建一个简单的 OLAP 立方体，该立方体允许我们按部门和职位进行分析。这里我们使用部门和职位作为维度，并计算每个部门和职位的员工数量。

BEGIN
  CUBE_TABLE(
    'employee_cube'
  )
  (
    'employees',
    'department_hierarchy' HIERARCHY employees.department_id,
    'job_hierarchy' HIERARCHY employees.job_id
  );
END;
/

🌑DROP TABLE

☘️详细语法：https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/DROP-TABLE.html

🌰（初步）：

DROP TABLE employees;

🌰（高深）：使用 CASCADE CONSTRAINTS 选项来删除表及其所有相关的约束，并且使用 PURGE 选项来立即释放表所占用的空间。

DROP TABLE employees CASCADE CONSTRAINTS PURGE;

🌕视图（View）

🌖SELECT VIEW

🌰：查看某一特定schema的所有视图的详细信息。

SELECT view_name, owner FROM all_views
WHERE owner = 'SCHEMA_NAME'

🌰：如果有DBA权限，可以查看数据库中所有的视图。

SELECT owner, view_name FROM dba_views;

🌰：查看具体某一视图的具体内容。

SELECT * FROM schema_name.view_name;

🌗CREATE VIEW

☘️详细语法：https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-VIEW.html

🌰（初步）：

CREATE OR REPLACE VIEW simple_employee_view
AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 5000;

🌰（高深）：SHARING = DATA指定视图的共享级别为数据级。NVL(commission_pct, 0) AS "Commission" INVISIBLE定义一个名为 Commission 的列，并将其标记为不可见。CASE WHEN job_id LIKE '%MANAGER%' THEN 'Yes' ELSE 'No' END AS "Is Manager" VISIBLE 定义一个名为 Is Manager 的列，并将其标记为可见。WITH CHECK (salary > 5000) 添加一个检查约束，确保通过视图插入或更新的数据满足这个条件。

CREATE OR REPLACE VIEW complex_employee_view
SHARING = DATA
AS
SELECT employee_id,
       first_name,
       last_name,
       department_id,
       salary,
       hire_date,
       -- 使用别名并设置为不可见
       NVL(commission_pct, 0) AS "Commission" INVISIBLE,
       -- 设置别名的可见性
       CASE WHEN job_id LIKE '%MANAGER%' THEN 'Yes' ELSE 'No' END AS "Is Manager" VISIBLE
FROM employees
WHERE salary > 5000
WITH CHECK (salary > 5000);

🌘ALTER VIEW

☘️详细语法：https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ALTER-VIEW.html

🌰（初步）：

ALTER VIEW employees_view READ ONLY;

🌰（高深）：将视图设置为可编译。将视图设置为非可编辑。

-- 创建视图
CREATE OR REPLACE VIEW employees_view AS
SELECT employee_id, first_name, last_name, email, hire_date,
       ROW_NUMBER() OVER (PARTITION BY email ORDER BY employee_id) AS email_unique
FROM employees
WHERE email IS NOT NULL;

-- 编译视图
ALTER VIEW employees_view COMPILE;

-- 将视图设置为非可编辑
ALTER VIEW employees_view NONEDITIONABLE;

在这里插入图片描述

🌑DROP VIEW

☘️详细语法：https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/DROP-VIEW.html

🌰（初步）：

DROP VIEW simple_employee_view;

🌰（高深）：CASCADE CONSTRAINTS 自动删除与视图相关的所有依赖约束，如外键约束。

DROP VIEW complex_employee_view CASCADE CONSTRAINTS;

🌕函数（Function）

🌖SELECT FUNCTION

🌰：查询所有的函数信息。

-- 查询所有函数（对于所有用户）
SELECT owner, object_name
FROM all_objects
WHERE object_type = 'FUNCTION';

-- 查询所有函数（包括数据库中的所有对象）
SELECT owner, object_name
FROM dba_objects
WHERE object_type = 'FUNCTION';

🌰：查询指定schema的函数信息。

-- 查询所有函数（对于所有用户）
SELECT owner, object_name
FROM all_objects
WHERE object_type = 'FUNCTION'
AND owner = 'schema_name';

-- 查询所有函数（包括数据库中的所有对象）
SELECT owner, object_name
FROM dba_objects
WHERE object_type = 'FUNCTION'
AND owner = 'schema_name';

🌗CREATE FUNCTION

☘️详细语法：https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-FUNCTION.html

笔记：您还可以使用语句将函数创建为包的一部分CREATE PACKAGE。

🌰（初步）：创建一个简单的 PL/SQL 函数。接受两个数字参数并返回它们的和。

-- 创建函数
CREATE OR REPLACE FUNCTION plsql_function_source (p_num1 NUMBER, p_num2 NUMBER)
RETURN NUMBER IS
BEGIN
  RETURN p_num1 + p_num2;
END plsql_function_source;

-- 调用函数并输出结果
DECLARE
  result NUMBER;
BEGIN
  result := plsql_function_source(5, 10);
  DBMS_OUTPUT.PUT_LINE('The sum of 5 and 10 is: ' || result);
END;

🌰（高深）：创建一个函数，该函数接受两个数字作为输入，并返回它们的商。如果第二个数字是零，则函数应返回一个错误消息。

-- 创建函数
CREATE OR REPLACE FUNCTION complex_function (p_dividend NUMBER, p_divisor NUMBER)
RETURN VARCHAR2 IS
  v_result VARCHAR2(100);
BEGIN
  IF p_divisor = 0 THEN
    v_result := 'Error: Division by zero is not allowed.';
  ELSE
    v_result := TO_CHAR(p_dividend / p_divisor);
  END IF;
  
  RETURN v_result;
EXCEPTION
  WHEN OTHERS THEN
    v_result := 'An error occurred: ' || SQLERRM;
    RETURN v_result;
END complex_function;
/

-- 调用函数并输出结果
DECLARE
  result VARCHAR2(100);
BEGIN
  result := complex_function(10, 2);
  DBMS_OUTPUT.PUT_LINE('Result of 10 / 2: ' || result);
END;
/

-- 测试除数为零的情况
DECLARE
  result VARCHAR2(100);
BEGIN
  result := complex_function(10, 0);
  DBMS_OUTPUT.PUT_LINE('Result of 10 / 0: ' || result);
END;
/

🌘ALTER FUNCTION

☘️详细语法：https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ALTER-FUNCTION.html

🌰（初步）：

ALTER FUNCTION simple_function (p_num1 NUMBER) 
RETURN NUMBER IS
BEGIN
  RETURN p_num1 * 2;
END simple_function;

🌰（高深）：

ALTER FUNCTION complex_function (p_dividend NUMBER, p_divisor NUMBER)
RETURN VARCHAR2 IS
  v_result VARCHAR2(100);
BEGIN
  IF p_divisor = 0 THEN
    v_result := 'Error: Division by zero is not allowed.';
  ELSE
    v_result := TO_CHAR(p_dividend / p_divisor);
  END IF;
  
  RETURN v_result;
EXCEPTION
  WHEN OTHERS THEN
    v_result := 'An error occurred: ' || SQLERRM;
    RETURN v_result;
END complex_function;

🌑DROP FUNCTION

☘️详细语法：https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/DROP-FUNCTION.html

笔记：不要使用此语句删除属于包的函数。相反，要么使用该语句删除整个包，要么使用带有子句的语句DROP PACKAGE重新定义不包含函数的包。
CREATE PACKAGE OR REPLACE

🌰：

DROP FUNCTION [ schema. ] function_name ;

🌕存储过程（Procedure）

🌖SELECT PROCEDURE

🌰：查询指定schema的所有存储过程的信息。

SELECT * FROM all_procedures 
WHERE OBJECT_TYPE = 'PROCEDURE'
AND OWNER = 'SCHEMA_NAME'

🌰：查询某一存储过程的基本信息。

SELECT * FROM all_procedures
where OWNER = 'SCHEMA_NAME'
and OBJECT_TYPE = 'PROCEDURE'
and OBJECT_NAME = 'PROCEDURE_NAME'

🌗CREATE PROCEDURE

☘️详细语法：https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-PROCEDURE.html

🌰（初步）：

CREATE OR REPLACE PROCEDURE simple_procedure (p_num NUMBER)
AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('The number is: ' || p_num);
END simple_procedure;

🌰（高深）：它接受两个数字参数，并根据它们的值决定是否执行一些操作。此外，该过程还包括错误处理。

CREATE OR REPLACE PROCEDURE complex_procedure (p_num1 NUMBER, p_num2 NUMBER)
AS
  v_sum NUMBER;
BEGIN
  IF p_num2 = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Error: Division by zero is not allowed.');
  ELSE
    v_sum := p_num1 + p_num2;
    DBMS_OUTPUT.PUT_LINE('The sum is: ' || v_sum);
    DBMS_OUTPUT.PUT_LINE('The division is: ' || p_num1 / p_num2);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END complex_procedure;

🌘ALTER PROCEDURE

☘️详细语法：https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ALTER-PROCEDURE.html

笔记：此语句不会更改现有过程的声明或定义。要重新声明或重新定义过程，请使用CREATE PROCEDURE带有OR REPLACE子句的语句

🌰：

-- 重新编译存储过程
ALTER PROCEDURE simple_procedure COMPILE;

-- 设置存储过程为可版本化
ALTER PROCEDURE simple_procedure EDITIONABLE;

-- 设置存储过程为不可版本化
ALTER PROCEDURE simple_procedure NONEDITIONABLE;

🌑DROP PROCEDURE

☘️详细语法：https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/DROP-PROCEDURE.html

🌰：

DROP PROCEDURE [ schema_name. ] procedure_name ;

🌕触发器（Trigger）

🌖SELECT TRIGGER

🌰：查询所有触发器的信息以及获取触发器的源代码。

-- 查询所有触发器的信息
SELECT * FROM all_triggers
WHERE OWNER = 'SCHEMA_NAME'
AND TRIGGER_NAME = 'TRIGGER_NAME';

-- 获取触发器的源代码
DECLARE
  l_text CLOB;
BEGIN
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
  l_text := DBMS_METADATA.GET_DDL('TRIGGER', 'TRIGGER_NAME', 'SCHEMA_NAME');
  DBMS_OUTPUT.PUT_LINE(l_text);
END;

如果你在 SQL*Plus 或类似工具中运行此脚本，请确保你已经启用了 SERVEROUTPUT。你可以通过命令 SET SERVEROUTPUT ON 来启用它。

🌗CREATE TRIGGER

☘️详细语法：https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-TRIGGER.html

🌰（初步）：在 employees 表上插入新记录时打印一条消息。

CREATE OR REPLACE TRIGGER simple_trigger
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('Inserting new employee record.');
END simple_trigger;

🌰（高深）：在 employees 表上更新记录时检查工资的变化，并在工资减少时记录一条警告消息。

CREATE OR REPLACE TRIGGER complex_trigger
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
  IF :new.salary < :old.salary THEN
    DBMS_OUTPUT.PUT_LINE('Warning: Salary decreased for employee ' || :new.employee_id);
  END IF;
END complex_trigger;

🌘ALTER TRIGGER

☘️详细语法：https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ALTER-TRIGGER.html

笔记：使用 ALTER TRIGGER 语句时，需要注意的是，你不能改变触发器的定义（除非使用 DROP TRIGGER 和 CREATE OR REPLACE TRIGGER）。你可以启用、禁用触发器或改变其版本化属性。如果你想完全改变触发器的行为或签名，通常的做法是先删除旧的触发器，然后创建一个新的触发器。

🌰：

-- 禁用触发器
ALTER TRIGGER simple_trigger DISABLE;

-- 设置触发器为可版本化
ALTER TRIGGER complex_trigger EDITIONABLE;

🌑DROP TRIGGER

☘️详细语法：https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/DROP-TRIGGER.html

🌰：

DROP TRIGGER [ schema_name. ] trigger_name ;

🔥PostgreSQL 14

在这里插入图片描述
PostgreSQL 是一个先进的关系型数据库管理系统 (RDBMS)，它对表、视图、函数、存储过程和触发器提供了全面的支持。PostgreSQL 使用标准 SQL 语言，并支持 PL/pgSQL，这是一种专门为 PostgreSQL 设计的程序化扩展语言，类似于 Oracle 的 PL/SQL。

🌕表（Table）

在这里插入图片描述

PostgreSQL自带的库（或模式）是数据库初始化时自动创建的模式，它们为数据库提供基础功能和管理工具。以下是一些主要的自带库及其用途的详细介绍：

pg_catalog：这个模式包含了PostgreSQL系统表和视图，用于存储数据库系统的元数据。 系统表包括所有数据库对象的信息，如表、列、索引、数据类型、函数等。视图用于简化对这些系统表的访问。
information_schema：提供了符合SQL标准的信息视图，允许用户访问有关数据库对象的元数据。 包含了许多视图，例如TABLES、COLUMNS、VIEWS等，帮助用户获取数据库结构和对象的详细信息，且与具体的数据库实现无关。
public：这是一个默认模式，供用户存储自定义的数据库对象。除非另有指定，新建对象默认存储在public模式下。用户可以根据需要创建更多的自定义模式以组织数据库对象。

🌖SELECT TABLE

🌰：查询特定数据库中的所有表

SELECT * FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
  AND table_type = 'BASE TABLE'
  AND table_schema = 'SCHEMA_NAME';

🌰：获取表的详细信息

SELECT * FROM information_schema.columns
WHERE table_schema = 'SCHEMA_NAME'
ORDER BY table_name, ordinal_position;

🌗CREATE TABLE

☘️详细语法：http://postgres.cn/docs/14/sql-createtable.html

🌰：创建一个名为 employees 的简单表，包含 id, name, 和 salary 字段

-- 创建表 employees
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary NUMERIC(10, 2) CHECK (salary > 0)
);

-- 创建继承表 employees_details
CREATE TABLE employees_details (
    department VARCHAR(100) NOT NULL,
    hire_date DATE NOT NULL,
    position VARCHAR(100) NOT NULL
)
INHERITS (employees);

🌰：创建一个名为 orders 的表，该表具有分区、继承关系、存储参数等功能。指定了该表将通过 order_date 列按范围进行分区。

-- 创建一个主表 orders
CREATE TABLE orders (
    order_id SERIAL,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL,
    status VARCHAR(20) CHECK (status IN ('NEW', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED')),
    CONSTRAINT check_total_amount CHECK (total_amount >= 0),
    PRIMARY KEY (order_id, order_date)
)
PARTITION BY RANGE (order_date);

-- 创建一个子表 orders_2023
CREATE TABLE orders_2023 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- 创建一个子表 orders_2024
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- 创建一个默认分区
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

🌘ALTER TABLE

☘️详细语法：http://postgres.cn/docs/14/sql-altertable.html

🌰（初步）：

-- 添加一个新列
ALTER TABLE employees
ADD COLUMN email VARCHAR(255) NOT NULL;

-- 修改 salary 列的数据类型
ALTER TABLE employees
ALTER COLUMN salary TYPE NUMERIC(12, 2);

🌰（高深）：使用 RENAME TO 语句来更改表名。使用 SET SCHEMA 语句来更改表所属的模式。使用 CREATE TABLE 语句创建一个新的分区表，并指定 PARTITION OF 来将其附加到主表。使用 ATTACH PARTITION 语句来将已存在的分区表附加到主表。使用 DETACH PARTITION 语句来将一个分区从主表中分离出去。

-- 更改表名
ALTER TABLE orders
RENAME TO complex_orders;

-- 更改表所属的模式
ALTER TABLE complex_orders
SET SCHEMA your_schema_name;

-- 添加一个新的分区
CREATE TABLE new_orders_1 PARTITION OF complex_orders
FOR VALUES FROM ('2026-02-01') TO ('2027-07-01');

-- 重新命名一个分区
ALTER TABLE orders_2023
RENAME TO new_orders_2023_renamed;

-- 附加分区
ALTER TABLE complex_orders
ATTACH PARTITION new_orders_2025 FOR VALUES FROM ('2010-01-01') TO ('2016-01-01');

-- 从表中分离一个分区
ALTER TABLE complex_orders
DETACH PARTITION new_orders_1;

🌑DROP TABLE

☘️详细语法：http://postgres.cn/docs/14/sql-droptable.html

🌰（初步）：

DROP TABLE employees;

🌰（高深）：使用 CASCADE 关键字来删除表 orders 及其所有子表。

-- 删除一个分区表及其所有子表
DROP TABLE orders CASCADE;

-- 删除一个普通的表
DROP TABLE IF EXISTS orders_details;

🌕视图（View）

🌖SELECT VIEW

🌰：查看指定 schema 中的所有视图信息。

SELECT * FROM information_schema.views
WHERE table_schema = 'SCHEMA_NAME';

🌰：查看特定视图的具体信息。

SELECT * FROM information_schema.views
WHERE table_schema = 'SCHEMA_NAME'
AND table_name = 'VIEW_NAME';

🌗CREATE VIEW

☘️详细语法：http://postgres.cn/docs/14/sql-createview.html

🌰（初步）：

CREATE VIEW employee_salaries AS
SELECT name, salary FROM employees;

🌰（高深）：WITH CHECK OPTION 语句来确保通过这个视图进行的更新或插入操作符合 WHERE 子句的条件。

CREATE VIEW order_summary AS
SELECT
    order_id,
    customer_id,
    order_date,
    status,
    total_amount
FROM complex_orders
WHERE status IN ('NEW', 'IN_PROGRESS')
WITH CHECK OPTION;

🌘ALTER VIEW

☘️详细语法：http://postgres.cn/docs/14/sql-alterview.html

🌰（初步）：

ALTER VIEW employee_salaries
ALTER COLUMN salary SET DEFAULT 0;

🌰（高深）：

-- 更改视图的所有者
ALTER VIEW order_summary
OWNER TO new_owner;

-- 更改视图的名称
ALTER VIEW order_summary
RENAME TO new_order_summary;

-- 更改视图所在的模式
ALTER VIEW new_order_summary
SET SCHEMA your_schema_name;

-- 设置视图选项
ALTER VIEW new_order_summary
SET (view_option_name = 'option_value');

-- 重置视图选项
ALTER VIEW new_order_summary
RESET (view_option_name);

🌑DROP VIEW

☘️详细语法：http://postgres.cn/docs/14/sql-dropview.html

🌰（初步）：

DROP VIEW employee_salaries;

🌰（高深）：

DROP VIEW order_summary CASCADE;

🌕函数（Function）

🌖SELECT FUNCTION

🌰：查看特定 schema 中的所有函数信息。

SELECT * FROM information_schema.routines
WHERE routine_type = 'FUNCTION'
AND routine_schema = 'SCHEMA_NAME';

🌰：查看特定函数的具体信息

SELECT * FROM information_schema.routines
WHERE routine_type = 'FUNCTION'
AND routine_schema = 'SCHEMA_NAME'
AND routine_name = 'FUNCTION_NAME';

🌰：获取函数的定义（内容）

-- 获取函数的定义
SELECT
    pg_catalog.pg_get_functiondef(p.oid)
FROM
    pg_catalog.pg_proc p
JOIN
    pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE
    n.nspname = 'SCHEMA_NAME'
AND
    p.proname = 'FUNCTION_NAME';

🌗CREATE FUNCTION

☘️详细语法：http://postgres.cn/docs/14/sql-createfunction.html

🌰（初步）：返回这两个参数的乘积。

CREATE OR REPLACE FUNCTION calculate_total(item_count INTEGER, item_price NUMERIC(10, 2))
RETURNS NUMERIC(10, 2) AS $$
BEGIN
    RETURN item_count * item_price;
END;
$$ LANGUAGE plpgsql;

🌰（高深）：函数设置为 VOLATILE，表示函数的结果可能在多次调用之间发生变化。函数设置为 SECURITY INVOKER，表示函数将以调用者的权限运行。函数设置为 PARALLEL SAFE，表示函数可以在并行查询中安全地运行。

CREATE OR REPLACE FUNCTION process_order(order_id INTEGER)
RETURNS TABLE (status VARCHAR(20), total_amount NUMERIC(10, 2))
AS $$
DECLARE
    order_status VARCHAR(20);
    total_amount NUMERIC(10, 2);
BEGIN
    -- 检查订单是否存在
    IF NOT EXISTS (SELECT 1 FROM test.complex_orders) THEN
        RAISE EXCEPTION 'Order with ID does not exist.';
    END IF;
    
    -- 获取订单的状态
    SELECT status INTO order_status FROM test.complex_orders;
    
    -- 计算总金额
    -- 由于 `total_amount` 已经是每个订单的总金额，这里直接使用即可
    SELECT total_amount INTO total_amount FROM test.complex_orders;
    
    -- 返回结果集
    RETURN QUERY
    SELECT order_status, total_amount;
END;
$$ LANGUAGE plpgsql
VOLATILE
SECURITY INVOKER
COST 100
ROWS 1000
PARALLEL SAFE;

🌘ALTER FUNCTION

☘️详细语法：http://postgres.cn/docs/14/sql-alterfunction.html

🌰（初步）：

ALTER FUNCTION calculate_total(item_count INTEGER, item_price NUMERIC(10, 2))
OWNER TO new_owner;

🌰（高深）：

-- 更改函数的名称
ALTER FUNCTION process_order(order_id INTEGER)
RENAME TO new_process_order;

-- 更改函数的所有者
ALTER FUNCTION new_process_order(order_id INTEGER)
OWNER TO new_owner;

-- 更改函数所在的模式
ALTER FUNCTION new_process_order(order_id INTEGER)
SET SCHEMA your_schema_name;

-- 更改函数依赖的扩展
ALTER FUNCTION new_process_order(order_id INTEGER)
DEPENDS ON EXTENSION my_extension;

🌑DROP FUNCTION

☘️详细语法：http://postgres.cn/docs/14/sql-dropfunction.html

🌰（初步）：

DROP FUNCTION calculate_total(item_count INTEGER, item_price NUMERIC(10, 2));

🌰（高深）：

DROP FUNCTION process_order(order_id INTEGER),
   calculate_total(item_count INTEGER, item_price NUMERIC(10, 2)) CASCADE;

🌕存储过程（Procedure）

🌖SELECT PROCEDURE

🌰：查询指定 schema 下的所有 procedures 具体信息

SELECT * FROM information_schema.routines
WHERE routine_type = 'PROCEDURE'
AND routine_schema = 'SCHEMA_NAME';

🌰：查询指定存储过程的具体信息

SELECT * FROM information_schema.routines
WHERE routine_type = 'PROCEDURE'
AND routine_schema = 'SCHEMA_NAME'
AND routine_name = 'PROCEDURE_NAME';

🌰：查询指定存储过程的定义（内容）

SELECT
    pg_catalog.pg_get_functiondef(p.oid)
FROM
    pg_catalog.pg_proc p
JOIN
    pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE
    n.nspname = 'SCHEMA_NAME'
AND
    p.prorettype = 'void'::regtype
AND
    p.proname = 'PROCEDURE_NAME';

🌗CREATE PROCEDURE

☘️详细语法：http://postgres.cn/docs/14/sql-createprocedure.html

🌰（初步）：接受一条消息并将其打印到日志中。

CREATE OR REPLACE PROCEDURE log_message(message TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE NOTICE 'Message: %', message;
END;
$$;

🌰（高深）：设置 procedure 的安全性为 EXTERNAL SECURITY INVOKER，表示 procedure 将以调用者的权限运行。设置 search_path 配置参数为 test，这将改变 procedure 内部的搜索路径。

CREATE OR REPLACE PROCEDURE process_order(order_id INTEGER, new_status VARCHAR(20))
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE test.complex_orders
    SET status = new_status
    WHERE order_id > 10;
END;
$$
EXTERNAL SECURITY INVOKER
SET search_path TO test;

🌘ALTER PROCEDURE

☘️详细语法：http://postgres.cn/docs/14/sql-alterprocedure.html

🌰（初步）：

ALTER FUNCTION log_message(message TEXT)
OWNER TO new_owner;

🌰（高深）：

-- 更改 procedure 的名称
ALTER PROCEDURE process_order(order_id INTEGER, new_status VARCHAR(20))
RENAME TO new_process_order;

-- 更改 procedure 的所有者
ALTER PROCEDURE new_process_order(order_id INTEGER, new_status VARCHAR(20))
OWNER TO new_owner;

-- 更改 procedure 所在的模式
ALTER PROCEDURE new_process_order(order_id INTEGER, new_status VARCHAR(20))
SET SCHEMA your_schema_name;

-- 更改 procedure 依赖的扩展
ALTER PROCEDURE new_process_order(order_id INTEGER, new_status VARCHAR(20))
DEPENDS ON EXTENSION my_extension;

🌑DROP PROCEDURE

☘️详细语法：http://postgres.cn/docs/14/sql-dropprocedure.html

🌰（初步）：

DROP PROCEDURE log_message(message TEXT);

🌰（高深）：

DROP PROCEDURE process_order(order_id INTEGER, new_status VARCHAR(20)) CASCADE;

🌕触发器（Trigger）

🌖SELECT TRIGGER

🌰：查询特定 schema 下的所有触发器信息。

SELECT * FROM information_schema.triggers
WHERE trigger_schema = 'SCHEMA_NAME'
AND trigger_name = 'TRIGGER_NAME';

🌰：查看特定触发器的定义（内容）。

SELECT pg_get_triggerdef(t.oid)
FROM pg_catalog.pg_trigger t
JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'SCHEMA_NAME'
AND t.tgname = 'TRIGGER_NAME';

🌗CREATE TRIGGER

☘️详细语法：http://postgres.cn/docs/14/sql-createtrigger.html

🌰（初步）：假设有一个 employees 表，并且在每次插入新员工记录时自动更新另一个表 employee_log 中的记录。

-- 创建一个函数来处理触发器的行为
CREATE OR REPLACE FUNCTION log_new_employee()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO employee_log (action, employee_id, insert_date)
  VALUES ('INSERT', NEW.id, NOW());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建一个触发器，它会在向 employees 表中插入新记录之前执行上述函数
CREATE TRIGGER trigger_log_new_employee
BEFORE INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION log_new_employee();

🌰（高深）：

-- 创建一个函数来处理触发器的行为
CREATE OR REPLACE FUNCTION update_order_status()
RETURNS TRIGGER AS $$
DECLARE
  threshold NUMERIC := 100; -- 设定阈值
BEGIN
  IF (NEW.status = 'SHIPPED' AND OLD.status = 'PENDING') THEN
    IF (NEW.total_amount > threshold) THEN
      INSERT INTO order_changes (order_id, old_status, new_status, change_date)
      VALUES (NEW.id, OLD.status, NEW.status, NOW());
    END IF;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建一个触发器，它会在更新 orders 表中的记录后执行上述函数
CREATE TRIGGER trigger_update_order_status
AFTER UPDATE OF status ON orders
FOR EACH ROW
WHEN (OLD.status <> NEW.status)
EXECUTE FUNCTION update_order_status();

🌘ALTER TRIGGER

☘️详细语法：http://postgres.cn/docs/14/sql-altertrigger.html

🌰（初步）：

-- 重命名触发器
ALTER TRIGGER trigger_log_new_employee ON employees RENAME TO trigger_log_employee_changes;

🌰（高深）：

-- 使触发器依赖于扩展
ALTER TRIGGER trigger_update_order_status ON complex_orders DEPENDS ON EXTENSION pg_trgm;

🌑DROP TRIGGER

☘️详细语法：http://postgres.cn/docs/14/sql-droptrigger.html

🌰（初步）：

-- 删除简单的触发器
DROP TRIGGER IF EXISTS trigger_log_new_employee ON employees;

🌰（高深）：将其删除，并且如果该触发器有任何依赖项，一并删除它们。

-- 删除复杂的触发器及其依赖项
DROP TRIGGER IF EXISTS trigger_update_order_status ON complex_orders CASCADE;

综括而言

🌨️余于此列出各数据库类型对于表、视图、函数、存储过程及触发器之支持情况，以“✅”表示支持，“❌”表示不支持，以便初学者一览而知：

数据库	表	视图	函数	存储过程	触发器
MySQL	✅	✅	✅	✅	✅
Oracle	✅	✅	✅	✅	✅
PostgreSQL	✅	✅	✅	✅	✅

🌧️数据库者，犹若储存数据之仓库也。其内配置诸般工具，用以存储、检索与管理数据。表、视图、函数、存储过程及触发器乃构建数据库之基石，善用之，则能使数据排列有序，事务处理井然；若不慎，则效率低下，难以达其预期之效。开发人员宜熟谙这些组件之特性，善加利用，方能构建出高效稳定之数据库应用。

⛈️学习数据库之道，并非一朝一夕之事，必经长期之学习与实践，方能真掌握其精髓。愿诸君勤于研读，精进不已，方能洞悉数据库之奥秘，达至运用自如之境。本文仅为粗略分析，若有不当之处，尚请指正，欢迎私信交流。

敬请期待更多数据库之学～～～

玦尘、

关注

24
点赞
踩
22

收藏

觉得还不错? 一键收藏
0
评论
数据库漫游记：表、视图、函数、存储过程及触发器之跨平台兼容性分析（上）

余撰此文，乃为导引初窥数据库之学人，俾其明了`表、视图、函数、存储过程及触发器之义理，及其于诸般平台之上`创建`、`修改`与`废弃`之法式。盖初学之人，常陷于迷雾之中，难辨东西，故须详述而明之。谨记，学者务须多览`官方文档！本文皆基于官方文档撰写，以求精准无误。盖官方文档乃权威之所出，详实可信，为学者之必备良伴。
复制链接

扫一扫