PostgreSQL 存储过程定义格式如下: ■结构 PL/pgSQL是一种块结构的语言,比较方便的是用pgAdmin III新建Function,填入一些参数就可以了。 基本上是这样的: CREATE OR REPLACE FUNCTION 函数名(参数1,[整型 int4, 整型数组 _int4, ...]) RETURNS 返回值类型 AS $BODY$ DECLARE 变量声明 BEGIN 函数体 END; $BODY$ LANGUAGE ‘plpgsql’ VOLATILE; ■变量类型 除了postgresql内置的变量类型外,常用的还有 RECORD ,表示一条记录。 ■赋值 赋值和Pascal有点像:“变量 := 表达式;” 有些奇怪的是连接字符串的是“||”,比如 sql := ‘SELECT * FROM’ || table || ‘WHERE …’; ■判断 判断又和VB有些像: IF 条件 THEN … ELSEIF 条件 THEN … ELSE … END IF; ■循环 循环有好几种写法: WHILE expression LOOP statements END LOOP; 还有常用的一种是:(从1循环到9可以写成FOR i IN 1..9 LOOP) FOR name IN [ REVERSE ] expression .. expression LOOP statements END LOOP; ■其他 还有几个常用的函数: SELECT INTO record …; 表示将select的结果赋给record变量(RECORD类型) PERFORM query; 表示执行query并丢弃结果 EXECUTE sql; 表示执行sql语句,这条可以动态执行sql语句(特别是由参数传入构造sql语句的时候特别有用) --简单的例子: 例1:无返回值 CREATE OR REPLACE FUNCTION 函数名称( 参数1,参数2,...) RETURNS void AS $BODY$ DECLARE --定义 BEGIN INSERT INTO "表名" VALUES(参数1,参数2,...); END $BODY$ LANGUAGE 'plpgsql' VOLATILE; -- 最后别忘了这个。 例2:有返回值 CREATE OR REPLACE FUNCTION 函数名称(deptcode VARCHAR(20) ,deptname VARCHAR(60) ,pycode VARCHAR(60),isenabled CHAR(1)) RETURNS BOOLEAN --返回值,布尔类型 AS $body$ DECLARE deptcode VARCHAR(20); deptname VARCHAR(60); pycode VARCHAR(60); isenabled CHAR(1); BEGIN UPDATE "deptDict" SET deptcode=deptcode,deptname=deptname,pycode=pycode,isenabled=isenabled,updatedhisdatetime=CURRENT_TIMESTAMP WHERE deptcode=deptcode; RETURN TRUE; END $body$ LANGUAGE 'plpgsql' VOLATILE; 最后再加上如何执行这个存储过程(函数) -- 执行存储过程方法1 SELECT * FROM 函数名称(参数1,参数2,...) -- 执行存储过程方法2 SELECT 函数名称('0参数1,参数2,...)
1、创建一个存储过程PostgreSQL 好像没有专门的 CREATE OR REPLACE PROCEDURE 全部都是 FUNCTION 的样子。 对于没有返回值的。 可以通过 RETURNS void 来实现。 2、要更新一个现有函数的定义,使用 CREATE OR REPLACE FUNCTION。 3、我们不能用这个方法修改一个函数的名字或者参数类型(如果你这么干,那么你就会创建一个新的,不同的函数)。 同样,CREATE OR REPLACE FUNCTION 也不会允许你修改一个现有函数的返回类型。 要做这些事情,你必须删除并重新创建函数。 (如果使用 OUT 参数,那就意味着除了删除函数,你不能修改任何 OUT 参数的类型或者名字。) 如果你删除然后重建一个函数,新函数和旧的将是不同的实体;你就需要删除现有引用了老函数的规则,视图,触发器等等。 4、使用 CREATE OR REPLACE FUNCTION 可以在不破坏引用该函数的对象的前提下修改函数定义。 5、举例:好像没有 print 之类的语句。只好把结果写入临时表 CREATE TABLE test_helloworld( data varchar(30) ); 请注意, 定义存储过程内使用的变量, 需要定义在 BEGIN 之前, 需要加 DECLARE 关键字。 多个变量之间用分号分隔。 CREATE OR REPLACE FUNCTION HelloWorld() RETURNS void AS $$ DECLARE testvalue1 VARCHAR(20); testvalue2 VARCHAR(20); BEGIN testvalue1 := 'First Test! '; SELECT 'Second Test !' INTO testvalue2; INSERT INTO test_helloworld SELECT 'Hello World' ; INSERT INTO test_helloworld (data) VALUES (testvalue1 || testvalue2); END; $$ LANGUAGE plpgsql; --- # SELECT HelloWorld(); helloworld ------------ (1 行记录)
# select * from test_helloworld; --------------------------- Hello World First Test! Second Test ! (2 行记录)
修改存储过程 要更新一个现有函数的定义,使用 CREATE OR REPLACE FUNCTION。 我们不能用这个方法修改一个函数的名字或者参数类型(如果你这么干,那么你就会创建一个新的,不同的函数)。 同样,CREATE OR REPLACE FUNCTION 也不会允许你修改一个现有函数的返回类型。 要做这些事情,你必须删除并重新创建函数。 (如果使用 OUT 参数,那就意味着除了删除函数,你不能修改任何 OUT 参数的类型或者名字。) 如果你删除然后重建一个函数,新函数和旧的将是不同的实体;你就需要删除现有引用了老函数的规则,视图,触发器等等。 使用 CREATE OR REPLACE FUNCTION 可以在不破坏引用该函数的对象的前提下修改函数定义。 具体代码略. 需要注意的一点。 删除函数的时候, 需要传递完整的参数列表, 仅仅指定一个 函数的名称, 是无法删除的。 例如:
# drop FUNCTION HelloWorld; 错误: 语法错误 在 ";" 或附近的 第1行drop FUNCTION HelloWorld;
# drop FUNCTION HelloWorld(); DROP FUNCTION #
参数定义 单个参数
# truncate table test_helloworld; TRUNCATE TABLE # CREATE OR REPLACE FUNCTION HelloWorld1(vUserName VARCHAR) RETURNS void AS $$ BEGIN INSERT INTO test_helloworld VALUES('Hello ' || vUserName); END; $$ LANGUAGE plpgsql;
# CREATE OR REPLACE FUNCTION HelloWorld20 ( p_user_name test_type.test_name%TYPE ) RETURNS void AS $$ BEGIN INSERT INTO test_type VALUES(1, p_user_name); END; $$ LANGUAGE plpgsql; 注意: 类型关联 test_type.test_name%TYPE 转换为 character varying
# select * from test_type; test_id | test_name ---------+----------- 1 | Test (1 行记录)
参数定义- IN、OUT、IN OUT
# truncate table test_helloworld; TRUNCATE TABLE
# CREATE OR REPLACE FUNCTION HelloWorld3 ( IN vUserName VARCHAR, OUT vOutValue VARCHAR ) AS $$ BEGIN INSERT INTO test_helloworld VALUES('Hello ' || vUserName); vOutValue := 'A'; END; $$ LANGUAGE plpgsql;
# SELECT HelloWorld3('ABC'); helloworld3 ------------- A (1 行记录)
Test=# select * from test_helloworld; data ----------- Hello ABC (1 行记录)
参数的默认值
PostgreSQL 不直接支持 参数的默认值。 但是支持 重载。 # TRUNCATE TABLE test_helloworld; TRUNCATE TABLE # CREATE OR REPLACE FUNCTION HelloWorld3( p_user_name VARCHAR, p_val1 VARCHAR, p_val2 VARCHAR) RETURNS void AS $$ BEGIN INSERT INTO test_helloworld (data) VALUES (p_user_name || p_val1 || p_val2); END; $$ LANGUAGE plpgsql;
# CREATE OR REPLACE FUNCTION HelloWorld3( p_user_name VARCHAR, p_val1 VARCHAR) RETURNS void AS $$ BEGIN PERFORM HelloWorld3(p_user_name, p_val1, ' XYZ'); END; $$ LANGUAGE plpgsql;
# CREATE OR REPLACE FUNCTION HelloWorld3( p_user_name VARCHAR) RETURNS void AS $$ BEGIN PERFORM HelloWorld3(p_user_name, ' OPQ '); END; $$ LANGUAGE plpgsql;
# select * from test_helloworld; data -------------- ABC OPQ XYZ (1 行记录)
返回结果集
简单查询的函数
请注意: 这里最后写的是 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 行记录)
# CREATE OR REPLACE FUNCTION GetTestMain (int) RETURNS test_main AS $$ SELECT * FROM test_main WHERE id != $1; $$ LANGUAGE SQL;
# SELECT * FROM GetTestMain(0) AS t; id | value ----+------- 1 | ONE (1 行记录)
Test=# SELECT * FROM GetTestMain(1) AS t; id | value ----+------- 2 | TWO (1 行记录)
请注意: 上面这种写法, 如果查询返回多行数据的情况下,这个函数仅仅会返回第一行。
# CREATE OR REPLACE FUNCTION GetTestMain2(int) RETURNS setof test_main AS $$ SELECT * FROM test_main WHERE id != $1; $$ LANGUAGE SQL; # SELECT * FROM GetTestMain2(1) AS t; id | value ----+------- 2 | TWO 4 | FOUR (2 行记录)
# CREATE OR REPLACE FUNCTION GetTestMain3(int) RETURNS SETOF test_main AS $$ DECLARE v_test_main_data test_main%ROWTYPE; BEGIN FOR v_test_main_data IN SELECT * FROM test_main LOOP IF v_test_main_data.id = $1 THEN -- 模拟一点逻辑操作. CONTINUE; END IF; -- 把当前行数据,加入结果集. RETURN NEXT v_test_main_data; END LOOP; -- 把整个结果集返回. RETURN; END; $$ LANGUAGE plpgsql;
# SELECT * FROM GetTestMain3(1) AS t; id | value ----+------- 2 | TWO 4 | FOUR (2 行记录)
普通返回的函数 # CREATE OR REPLACE FUNCTION HelloWorld4() RETURNS varchar AS $$ BEGIN RETURN 'Hello World!'; END; $$ LANGUAGE plpgsql;