IT评测·应用市场-qidao123.com

标题: PostgreSQL——存储过程、函数 [打印本页]

---

作者: 温锦文欧普厨电及净水器总代理    时间: 2024-10-17 19:23
标题: PostgreSQL——存储过程、函数
概述

先容

PostgreSQL（简称PG）是一个功能强大的开源对象-关系数据库系统，支持险些所有的SQL尺度而且提供了许多其他现代特性。在PostgreSQL中，存储过程和函数是数据库编程的重要组成部分，它们允许用户封装复杂的数据库操纵逻辑，以提高代码的重用性、镌汰网络传输的数据量，并增强数据库操纵的安全性。
存储过程（Procedure）：

• 重要用于执行一系列的操纵，通常没有明白的返回值（只管可以通过OUT参数返回数据）。
  
• 在PostgreSQL中，存储过程使用CREATE PROCEDURE语句创建。
  

函数（Function）：

• 重要用于对输入参数举行处理，并返回一个结果值。
  
• 在PostgreSQL中，函数可以使用CREATE FUNCTION语句创建，而且可以在SQL查询中作为表达式的一部分被调用。
  

使用

存储过程的使用：

• 创建存储过程：使用CREATE PROCEDURE语句界说存储过程的名称、参数（包括IN、OUT和INOUT类型）以及过程体（包罗一系列的SQL语句或PL/pgSQL代码块）。
  
• 调用存储过程：使用CALL语句或SELECT * FROM语法调用存储过程。
  

函数的使用：

• 创建函数：使用CREATE FUNCTION语句界说函数的名称、参数（只有IN类型）、返回类型以及函数体。
  
• 调用函数：函数可以在SQL查询中直接作为表达式调用，例如，在SELECT语句的列表达式中。
  

优缺点

优点：

• 性能提拔：存储过程和函数在第一次被调用后会被编译，后续调用时不须要再次编译，镌汰了应用与数据库服务器的通信开销，提拔了数据库的整体性能。
  
• 业务逻辑共享：实现了一套业务逻辑被不同应用程序共享，镌汰了应用程序的开发复杂度，保证了不同应用程序使用的划一性。
  
• 安全性增强：通过存储过程和函数，可以屏蔽数据库中的敏感数据，区分应用程序访问数据的权限，从而更细粒度地增强数据库权限的管理。
  
• 业务与应用程序解耦：当业务需求更新时，只需更改存储过程或函数的界说，而不须要更改应用程序。
  
• 丰富的编程本领：PostgreSQL支持多种编程语言（如PL/pgSQL、PL/Perl、PL/Python）来编写存储过程和函数，提供了强大的SQL编程本领。
  

缺点：

• 移植性较差：不同数据库的存储过程和函数在界说方式、语言及语法上存在差异，因此将PostgreSQL的存储过程和函数移植到其他数据库系统时可能碰面对较高的成本。
  
• 学习曲线较陡：固然存储过程和函数提供了强大的编程本领，但也须要开发者具备肯定的编程底子和数据库知识，学习曲线相对较陡。
  

综上所述，PostgreSQL的存储过程和函数是数据库编程中不可或缺的工具，它们能够显著提高数据库的性能、安全性和可维护性。然而，在使用时也须要注意其移植性和学习成本等题目。
根本语法

函数

1. CREATE [ OR REPLACE ] FUNCTION
2.     name ( [ [ argmode ] [ argname ] argtype [ [ in ] [ out ] [ INOUT ]][ { DEFAULT | = } default_expr ]  ] )
3.     [ RETURNS rettype
4.       | RETURNS TABLE ( column_name column_type [, ...] ) ]
5.   { LANGUAGE lang_name
6.     | TRANSFORM { FOR TYPE type_name } [, ... ]
7.     | WINDOW
8.     | { IMMUTABLE | STABLE | VOLATILE }
9.     | [ NOT ] LEAKPROOF
10.     | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
11.     | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }
12.     | PARALLEL { UNSAFE | RESTRICTED | SAFE }
13.     | COST execution_cost
14.     | ROWS result_rows
15.     | SUPPORT support_function
16.     | SET configuration_parameter { TO value | = value | FROM CURRENT }
17.     | AS 'definition'
18.     | AS 'obj_file', 'link_symbol'
19.     | sql_body
20.   }

复制代码

存储过程

1. CREATE [ OR REPLACE ] FUNCTION
2.     name ( [ [ argmode ] [ argname ] argtype [ [ in ] [ out ] [ INOUT ]][ { DEFAULT | = } default_expr ]  ] )
3.   { LANGUAGE lang_name
4.     | TRANSFORM { FOR TYPE type_name } [, ... ]
5.     | WINDOW
6.     | { IMMUTABLE | STABLE | VOLATILE }
7.     | [ NOT ] LEAKPROOF
8.     | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
9.     | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }
10.     | PARALLEL { UNSAFE | RESTRICTED | SAFE }
11.     | COST execution_cost
12.     | ROWS result_rows
13.     | SUPPORT support_function
14.     | SET configuration_parameter { TO value | = value | FROM CURRENT }
15.     | AS 'definition'
16.     | AS 'obj_file', 'link_symbol'
17.     | sql_body
18.   }

复制代码

测试用例

1. CREATE OR REPLACE FUNCTION process_data() RETURNS void AS $$  
2. DECLARE  
3.     r RECORD;  
4.     cursor_name CURSOR FOR SELECT id, value FROM source_table;  
5.     -- 注意：在真实应用中，通常不需要为这么简单的查询使用动态SQL  
6.     -- 但为了演示，我们构造一个动态SQL语句  
7.     dynamic_sql TEXT := 'SELECT id, value FROM source_table';  
8. BEGIN  
9.     -- 打开游标  
10.     OPEN cursor_name;  
11.   
12.     -- 循环遍历游标  
13.     LOOP  
14.         FETCH cursor_name INTO r;  
15.         EXIT WHEN NOT FOUND; -- 如果没有更多行，则退出循环  
16.   
17.         -- 这里我们简单地将value加倍作为处理后的值  
18.         -- 实际上，这里可以包含更复杂的逻辑或调用其他函数  
19.         INSERT INTO target_table (processed_value) VALUES (r.value * 2);  
20.   
21.         -- 注意：在这个简单例子中，我们没有将id插入到target_table中，  
22.         -- 但你可以根据需要调整INSERT语句来包含它。  
23.   
24.         -- 如果需要使用动态SQL进行插入（尽管这通常不是必需的），可以这样做：  
25.         -- EXECUTE format('INSERT INTO target_table (processed_value) VALUES (%L)', r.value * 2);  
26.     END LOOP;  
27.   
28.     -- 关闭游标  
29.     CLOSE cursor_name;  
30. END;  
31. $$ LANGUAGE plpgsql;

复制代码

存储过程和函数的区别

在PostgreSQL数据库中，存储过程和函数是两种相似的可重用数据库对象，它们都是由SQL语句或PL/pgSQL（PostgreSQL的过程语言）编写的一组操纵。然而，它们在用途、返回值以及调用方式上存在一些关键区别。以下是这些区别的具体归纳：
1. 用途和返回值

• 函数（Functions）：
  
  
  
  • 通常用于执行一些操纵并返回一个值。这个值可以是任何数据类型，包括数字、字符串、日期等。
    
  • 函数的重要目标是举行盘算或数据处理，并将结果返回给调用者。
    
  
  
• 存储过程（Procedures）：
  
  
  
  • 重要用于执行一系列操纵，这些操纵可能包括多个SQL语句或其他代码块，但它们通常不返回任何值（大概说，它们的返回类型为void）。
    
  • 存储过程的筹划初志是执行一系列复杂的数据库操纵，而不肯定须要返回结果给调用者。
    
  
  

2. 调用方式

• 函数：
  
  
  
  • 可以在SQL查询中直接调用，特别是在SELECT语句中，函数可以作为列表达式的一部分出现。
    
  • 函数的调用结果可以直接在SQL查询中使用，如作为条件、与其他列举行比力或作为其他函数的参数。
    
  
  
• 存储过程：
  
  
  
  • 通常须要通过CALL语句或类似的语法来调用。
    
  • 存储过程不能在SELECT语句中直接作为列表达式调用，它们更得当于执行一系列独立的数据库操纵。
    
  
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 IT评测·应用市场-qidao123.com (https://dis.qidao123.com/)

Powered by Discuz! X3.4