wangzhiqing999
PostgreSQL 存储过程与函数
创建一个存储过程PostgreSQL 好像没有专门的 CREATE OR REPLACE PROCEDURE 全部都是 FUNCTION 的样子。
对于没有返回值的。 可以通过 RETURNS void 来实现。
要更新一个现有函数的定义,使用 CREATE OR REPLACE FUNCTION。
我们不能用这个方法修改一个函数的名字或者参数类型(如果你这么干,那么你就会创建一个新的,不同的函数)。
同样,CREATE OR REPLACE FUNCTION 也不会允许你修改一个现有函数的返回类型。
要做这些事情,你必须删除并重新创建函数。
(如果使用 OUT 参数,那就意味着除了删除函数,你不能修改任何 OUT 参数的类型或者名字。)
如果你删除然后重建一个函数,新函数和旧的将是不同的实体;你就需要删除现有引用了老函数的规则,视图,触发器等等。
使用 CREATE OR REPLACE FUNCTION 可以在不破坏引用该函数的对象的前提下修改函数定义。
好像没有 print 之类的语句。
只好 把结果写入临时表
Test=# CREATE TABLE test_helloworld(
Test(# data varchar(30)
Test(# );
CREATE TABLE
Test=#
请注意, 定义存储过程内使用的变量, 需要定义在 BEGIN 之前, 需要加 DECLARE 关键字。
多个变量之间用分号分隔。
Test=# CREATE OR REPLACE FUNCTION HelloWorld() RETURNS void AS
Test-# $$
Test$# DECLARE
Test$# testvalue1 VARCHAR(20);
Test$# testvalue2 VARCHAR(20);
Test$# BEGIN
Test$# testvalue1 := 'First Test! ';
Test$# SELECT 'Second Test !' INTO testvalue2;
Test$# INSERT INTO test_helloworld
Test$# SELECT 'Hello World' ;
Test$# INSERT INTO test_helloworld (data)
Test$# VALUES (testvalue1 || testvalue2);
Test$# END;
Test$# $$
Test-# LANGUAGE plpgsql;
CREATE FUNCTION
Test=#
Test=# SELECT HelloWorld();
helloworld
------------
(1 行记录)
Test=# select * from test_helloworld;
data
---------------------------
Hello World
First Test! Second Test !
(2 行记录)
Test=#
修改存储过程
要更新一个现有函数的定义,使用 CREATE OR REPLACE FUNCTION。
我们不能用这个方法修改一个函数的名字或者参数类型(如果你这么干,那么你就会创建一个新的,不同的函数)。
同样,CREATE OR REPLACE FUNCTION 也不会允许你修改一个现有函数的返回类型。
要做这些事情,你必须删除并重新创建函数。
(如果使用 OUT 参数,那就意味着除了删除函数,你不能修改任何 OUT 参数的类型或者名字。)
如果你删除然后重建一个函数,新函数和旧的将是不同的实体;你就需要删除现有引用了老函数的规则,视图,触发器等等。
使用 CREATE OR REPLACE FUNCTION 可以在不破坏引用该函数的对象的前提下修改函数定义。
具体代码略.
需要注意的一点。
删除函数的时候, 需要传递完整的参数列表, 仅仅指定一个 函数的名称, 是无法删除的。
例如:
Test=# drop FUNCTION HelloWorld;
错误: 语法错误 在 ";" 或附近的
第1行drop FUNCTION HelloWorld;
^
Test=# drop FUNCTION HelloWorld();
DROP FUNCTION
Test=#
Test=# drop FUNCTION HelloWorld2;
错误: 语法错误 在 ";" 或附近的
第1行drop FUNCTION HelloWorld2;
^
Test=# drop FUNCTION HelloWorld2();
错误: 函数 helloworld2() 不存在
Test=# drop FUNCTION HelloWorld2(varchar);
DROP FUNCTION
Test=#
参数定义 单个参数
Test=# truncate table test_helloworld;
TRUNCATE TABLE
Test=# CREATE OR REPLACE FUNCTION HelloWorld1(vUserName VARCHAR) RETURNS void AS
Test-# $$
Test$# BEGIN
Test$# INSERT INTO test_helloworld
Test$# VALUES('Hello ' || vUserName);
Test$# END;
Test$# $$
Test-# LANGUAGE plpgsql;
CREATE FUNCTION
Test=#
Test=# SELECT HelloWorld1('ABC');
helloworld1
-------------
(1 行记录)
Test=# select * from test_helloworld;
data
-----------
Hello ABC
(1 行记录)
函数参数的别名Test=# truncate table test_helloworld;
TRUNCATE TABLE
-- 请注意这里: 定义参数的时候, 没有定义参数名称, 仅仅定义了参数的数据类型
-- 然后在 定义变量的位置, 通过 vUserName ALIAS FOR $1 来为 第一个参数, 指定一个变量名称, 叫做 vUserName
Test=# CREATE OR REPLACE FUNCTION HelloWorld2 (varchar) RETURNS void AS
Test-# $$
Test$# DECLARE
Test$# vUserName ALIAS FOR $1;
Test$# BEGIN
Test$# INSERT INTO test_helloworld
Test$# VALUES('Hello ' || vUserName);
Test$# END;
Test$# $$
Test-# LANGUAGE plpgsql;
CREATE FUNCTION
Test=# SELECT HelloWorld2('XYZ');
helloworld2
-------------
(1 行记录)
Test=# select * from test_helloworld;
data
-----------
Hello XYZ
(1 行记录)
某些情况下, 希望定义参数的时候, 数据类型,与某个表中的某一列的数据类型一样。
这样,将来万一业务变化, 表的数据类型变化了,不需要修改存储过程代码。
定义的方式,是 表名.列名%TYPECREATE TABLE test_type (test_ID INT,
test_name varchar(20)
);
Test=# CREATE OR REPLACE FUNCTION HelloWorld20 (
Test(# p_user_name test_type.test_name%TYPE Test(# ) RETURNS void ASTest-# $$
Test$# BEGIN
Test$# INSERT INTO test_type VALUES(1, p_user_name);
Test$# END;
Test$# $$
Test-# LANGUAGE plpgsql;
注意: 类型关联 test_type.test_name%TYPE 转换为 character varying
CREATE FUNCTION
Test=#
Test=# select HelloWorld20('Test');
helloworld20
--------------
(1 行记录)
Test=# select * from test_type;
test_id | test_name
---------+-----------
1 | Test
(1 行记录)
参数定义- IN、OUT、IN OUT
Test=# truncate table test_helloworld;
TRUNCATE TABLE
Test=# CREATE OR REPLACE FUNCTION HelloWorld3 (
Test(# IN vUserName VARCHAR,
Test(# OUT vOutValue VARCHAR
Test(# ) AS
Test-# $$
Test$# BEGIN
Test$# INSERT INTO test_helloworld
Test$# VALUES('Hello ' || vUserName);
Test$# vOutValue := 'A';
Test$# END;
Test$# $$
Test-# LANGUAGE plpgsql;
CREATE FUNCTION
Test=#
Test=# SELECT HelloWorld3('ABC');
helloworld3
-------------
A
(1 行记录)
Test=# select * from test_helloworld;
data
-----------
Hello ABC
(1 行记录)
参数的默认值
PostgreSQL 不直接支持 参数的默认值。
但是支持 重载。
Test=# TRUNCATE TABLE test_helloworld;
TRUNCATE TABLE
Test=# CREATE OR REPLACE FUNCTION HelloWorld3(
Test(# p_user_name VARCHAR,
Test(# p_val1 VARCHAR,
Test(# p_val2 VARCHAR) RETURNS void AS
Test-# $$
Test$# BEGIN
Test$# INSERT INTO test_helloworld (data)
Test$# VALUES (p_user_name || p_val1 || p_val2);
Test$# END;
Test$# $$
Test-# LANGUAGE plpgsql;
CREATE FUNCTION
Test=#
Test=# CREATE OR REPLACE FUNCTION HelloWorld3(
Test(# p_user_name VARCHAR,
Test(# p_val1 VARCHAR) RETURNS void AS
Test-# $$
Test$# BEGIN
Test$# PERFORM HelloWorld3(p_user_name, p_val1, ' XYZ');
Test$# END;
Test$# $$
Test-# LANGUAGE plpgsql;
CREATE FUNCTION
Test=#
Test=# CREATE OR REPLACE FUNCTION HelloWorld3(
Test(# p_user_name VARCHAR) RETURNS void AS
Test-# $$
Test$# BEGIN
Test$# PERFORM HelloWorld3(p_user_name, ' OPQ ');
Test$# END;
Test$# $$
Test-# LANGUAGE plpgsql;
CREATE FUNCTION
Test=#
Test=# SELECT HelloWorld3('ABC');
helloworld3
-------------
(1 行记录)
Test=# select * from test_helloworld;
data
--------------
ABC OPQ XYZ
(1 行记录)
Test=#
返回结果集
简单查询的函数
请注意:
这里最后写的是 LANGUAGE SQL; 不是 LANGUAGE plpgsql;
因为函数里面, 没有任何逻辑, 只有一条 SQL 语句.
CREATE OR REPLACE FUNCTION GetTestMain (int) RETURNS test_main AS $$
SELECT * FROM test_main WHERE id = $1;
$$ LANGUAGE SQL;
Test=# SELECT * FROM GetTestMain(1) AS t;
id | value
----+-------
1 | ONE
(1 行记录)
Test=# CREATE OR REPLACE FUNCTION GetTestMain (int) RETURNS test_main AS $$
Test$# SELECT * FROM test_main WHERE id != $1;
Test$# $$ LANGUAGE SQL;
CREATE FUNCTION
Test=# SELECT * FROM GetTestMain(0) AS t;
id | value
----+-------
1 | ONE
(1 行记录)
Test=# SELECT * FROM GetTestMain(1) AS t;
id | value
----+-------
2 | TWO
(1 行记录)
请注意: 上面这种写法, 如果查询返回多行数据的情况下,这个函数仅仅会返回第一行。
Test=# CREATE OR REPLACE FUNCTION GetTestMain2(int) RETURNS setof test_main AS $$
Test$# SELECT * FROM test_main WHERE id != $1;
Test$# $$ LANGUAGE SQL;
CREATE FUNCTION
Test=#
Test=# SELECT * FROM GetTestMain2(1) AS t;
id | value
----+-------
2 | TWO
4 | FOUR
(2 行记录)
通过定义 RETURNS setof ... 使得函数能过返回多行记录.
假如业务逻辑比较复杂,无法简单 SQL 处理的情况下
需要使用 RETURN NEXT ... 来把当前行数据,加入结果集.
使用 RETURN; 把整个结果集返回.
Test=# CREATE OR REPLACE FUNCTION GetTestMain3(int)
Test-# RETURNS SETOF test_main AS
Test-# $$
Test$# DECLARE
Test$# v_test_main_data test_main%ROWTYPE;
Test$# BEGIN
Test$# FOR v_test_main_data IN SELECT * FROM test_main LOOP
Test$# IF v_test_main_data.id = $1 THEN
Test$# -- 模拟一点逻辑操作.
Test$# CONTINUE;
Test$# END IF;
Test$# -- 把当前行数据,加入结果集.
Test$# RETURN NEXT v_test_main_data;
Test$# END LOOP;
Test$# -- 把整个结果集返回.
Test$# RETURN;
Test$# END;
Test$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
Test=# SELECT * FROM GetTestMain3(1) AS t;
id | value
----+-------
2 | TWO
4 | FOUR
(2 行记录)
普通返回的函数
Test=# CREATE OR REPLACE FUNCTION HelloWorld4() RETURNS varchar AS
Test-# $$
Test$# BEGIN
Test$# RETURN 'Hello World!';
Test$# END;
Test$# $$
Test-# LANGUAGE plpgsql;
CREATE FUNCTION
Test=#
Test=# select HelloWorld4();
helloworld4
--------------
Hello World!
(1 行记录)