PostgreSQL之存储过程篇
在PostgreSQL中，过程语言（例如PL/pgSQL，C，Perl，Python和Tcl）被称为存储过程。该过程添加了许多过程元素，例如控制结构，循环和复杂的计算，以扩展SQL标准。它使您可以在PostgreSQL中开发复杂的功能和存储过程，而这是使用普通SQL可能无法实现的。
1.入门
PostgreSQL允许使用各种过程语言元素（通常称为存储过程）使用用户定义的函数和存储过程来扩展数据库功能。
存储过程定义用于创建触发器的功能或自定义聚合功能。此外，存储过程还添加了许多过程功能，例如控制结构和复杂的计算。这些使您可以更轻松，更有效地开发自定义功能。
缺省情况下，PostgreSQL支持三种过程语言：SQL，PL/pgSQL和C。还可以使用扩展将其他过程语言（例如Perl，Python和TCL）加载到PostgreSQL中。1.1、使用PostgreSQL存储过程的优点
•减少应用程序和数据库服务器之间的往返次数。所有SQL语句都包装在存储在PostgreSQL数据库服务器中的函数内，因此应用程序仅需发出函数调用即可返回结果，而不必发送多个SQL语句并等待每次调用之间的结果。
•由于用户定义的函数和存储过程已预编译并存储在PostgreSQL数据库服务器中，因此提高了应用程序性能。
•可在许多应用程序中重用。一旦开发了功能，就可以在任何应用程序
中重用它。
1.2、使用PostgreSQL存储过程的缺点
•由于存储过程编程需要许多开发人员不具备的专业技能，因此软件开发速度缓慢。
•难以管理版本且难以调试。
•可能无法移植到其他数据库管理系统，例如MySQL或Microsoft SQL Server。
2.PL/pgSQL块结构
PL/pgSQL是一种块结构语言，因此，PL/pgSQL函数或存储过程被组织为块。
2.1、PL/pgSQL的语法
[ <<label>> ]
[ DECLARE
declarations ]
BEGIN
statements;
...
END [ label ];
•每个块都有两个部分：声明和正文。声明部分是可选的，而主体部分是必需的。该块以END关键字后的分号（;）结尾。
•块的开头和结尾可能有可选标签。如果要EXIT在块主体的语句中指定它，或者要限定在块中声明的变量的名称，请使用块标签。
•在声明部分，您可以声明主体部分中使用的所有变量。声明部分中的每个语句均以分号（;）结尾。
•正文部分是您放置代码的地方。主体部分中的每个语句也以分号（;）终止。
2.2、PL/pgSQL块结构示例
匿名块
DO $$
<<first_block>>
DECLARE
counter integer := 0;
BEGIN
counter := counter + 1;
RAISE NOTICE 'The current value of counter is %', counter;
END first_block $$;
NOTICE:  The current value of counter is 1
DO
•请注意，DO语句不属于该块。它用于执行匿名块。
•在声明部分，声明了一个变量counter并将其值设置为零。
•在主体部分内部，将计数器的值增加到1并使用RAISE NOTICE语句输出其值。
•first_block标签仅用于演示目的。在此示例中，它什么也不做。
双美元（$$）是单引号（'）的替代。开发PL/pgSQL块，函数或存储过程时，必须以字符串文字形式传递其主体。如果您使用双美元（$$），则可以避免引用问题。您也可以在$$之间使用令牌，例如$function$或$procedure$。
3.PL/pgSQL变量
PL/pgSQL变量是内存位置的有意义的名称。变量保存一个可以通过块或功能更改的值。变量始终与特定的数据类型相关联。
在使用变量之前，必须在PL/pgSQL块的声明部分中对其进行声明。声明变量的语法: variable_name data_type [:= expression];
•首先，指定变量的名称。将有意义的名称分配给变量是一个好习惯。
例如，应该使用index或i代替命名变量counter。
•其次，将特定的数据类型与变量关联。数据类型可以是任何有效的PostgreSQL数据类型，例如INTEGER，NUMERIC，VARCHAR和CHAR。
•第三，可选地为变量分配默认值。如果不这样做，则变量的初始值将初始化为NULL。
声明和初始化各种变量
DO $$
DECLARE
counter    INTEGER := 1;
first_name VARCHAR(50) := 'John';
last_name  VARCHAR(50) := 'Doe';
payment    NUMERIC(11,2) := 20.5;
postgre trunc函数的使用方法
BEGIN
RAISE NOTICE '% % % has been paid % USD', counter, first_name, last_name, payment;
END $$;
•counter变量是被初始化为1的整数
•first_name和last_name是VARCHAR，50个字符，并初始化John 和Doe文字字符串。
•payment是有两个小数数字和初始化20.5
请注意，当输入该块时，PostgreSQL将评估默认值并为变量设置它们。
test=# DO $$
DECLARE
created_at time := NOW();
BEGIN
RAISE NOTICE '%', created_at;
PERFORM pg_sleep(10);
RAISE NOTICE '%', created_at;
END $$;
注意:  23:07:05.060536
注意:  23:07:05.060536
DO
•首先，声明一个变量，其默认值初始化为当前时间。
•其次，打印出变量的值并在10秒内暂停执行
•第三，再次打印出created_at变量的值。
3.1、复制数据类型
PostgreSQL使您可以定义一个数据类型的变量，该数据类型引用表的列的数据类型或另一个变量的数据类型：
variable_name lumn_name%TYPE;
variable_name variable%TYPE;
定义一个变量city_name，其名称name与city表的列名称相同，其数据类型如下：city_name city.name%TYPE := 'San Francisco';
通过使用复制类型功能，您可以获得以下优点：
•首先，您无需关心列的数据类型。您声明一个变量以仅保留查询中该列的值。
•其次，当列的数据类型更改时，您无需更改函数中的变量声明即可适应新的更改。
•第三，由于内部变量的类型可以从一个调用更改为另一个调用，因此可以将变量的类型引用为函数参数的数据类型以创建多态函数。3.2、为变量分配别名
PostgreSQL允许您为任何变量定义别名，如下所示：
new_name ALIAS FOR old_name;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。