- 浏览: 3447439 次
- 性别:
- 来自: 珠海
-
文章分类
- 全部博客 (1633)
- Java (250)
- Android&HTML5 (111)
- Struts (10)
- Spring (236)
- Hibernate&MyBatis (115)
- SSH (49)
- jQuery插件收集 (55)
- Javascript (145)
- PHP (77)
- REST&WebService (18)
- BIRT (27)
- .NET (7)
- Database (105)
- 设计模式 (16)
- 自动化和测试 (19)
- Maven&Ant (43)
- 工作流 (36)
- 开源应用 (156)
- 其他 (16)
- 前台&美工 (119)
- 工作积累 (0)
- OS&Docker (83)
- Python&爬虫 (28)
- 工具软件 (157)
- 问题收集 (61)
- OFbiz (6)
- noSQL (12)
最新评论
-
HEZR曾嶸:
你好博主,这个不是很理解,能解释一下嘛//左边+1,上边+1, ...
java 两字符串相似度计算算法 -
天使建站:
写得不错,可以看这里,和这里的这篇文章一起看,有 ...
jquery 遍历对象、数组、集合 -
xue88ming:
很有用,谢谢
@PathVariable映射出现错误: Name for argument type -
jnjeC:
厉害,困扰了我很久
MyBatis排序时使用order by 动态参数时需要注意,用$而不是# -
TopLongMan:
非常好,很实用啊。。
PostgreSQL递归查询实现树状结构查询
PostgreSQL的存储过程简单入门 http://blog.csdn.net/rachel_luo/article/details/8073458
存储过程事物 http://www.php100.com/manual/PostgreSQL8/tutorial-transactions.html
PL/pgSQL - SQL存储过程语言 https://wiki.postgresql.org/wiki/9.1%E7%AC%AC%E4%B8%89%E5%8D%81%E4%B9%9D%E7%AB%A0
postgreSQL存储过程写法示例http://blog.sina.com.cn/s/blog_448574810101f64u.html
结构
PL/pgSQL是一种块结构的语言,比较方便的是用pgAdmin III新建Function,填入一些参数就可以了。基本上是这样的:
变量类型
除了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,$2,等等这样的标识符。有时候为了增强可读性,我们可以为 $n 参数名声明别名。然后通过这个别名或者数字标识符可以指向这个参数值。
有两种方法创建一个别名。最好的方法是用CREATE FUNCTION命令给予这个参数一个名字,例如:
另一个方法是,在PostgreSQL 8.0之前唯一的方法,明确的用别名进行声明,用以下的语法进行声明:
name ALIAS FOR $n;
这个风格的同一个例子看起来像下面这样 :
注意:这两个例子不是完全一样的。在第一种情况,subtotal可以用sales_tax.subtotal进行引用,但是在第二种情况下不能这么做。(如果我们给这个内部块附加了一个标签,subtotal能够替代这个标签)
一些更多的例子:
当一个PL/pgSQL函数用输出参数来进行声明时,给予这个输出参数$n名和一个任意的别名跟正常输入参数是同样的方法。即使这个输出参数以NULL开始时也是一个有效的变量,它应该在函数的执行过程中被分配。这个参数最好的值将被返回。例如,这个sales-tax例子也可以用这种方法完成:
注意:我们省略了RETURNS real---我们可以将它包括在内,但它是多余的。
当返回多个值的时候输出参数将非常有用,一个简单的例子是:
如在Section 35.4.4中的讨论,这将为这个函数的结果创建一个匿名的记录类型。如果使用了RETURNS字句,那么必须给它指明RETURNS记录。
另外一种方法声明PL/pgSQL函数是用RETURNS TABLE,例如:
这跟声明一个或者多个OUT参数和制定RETURNS SETOF这些类型是同样的方法。
当返回的PL/pgSQL函数的类型被声明为一个多态类型(anyelement, anyarray, anynonarray, 或者anyenum),特殊参数$0将被创建。它的数据类型将实际的返回函数的类型,从实际的输入类型返回(见Section 35.2.5)。这运行这个函数访问这个实际的返回类型如Section 39.3.3显示的那样。$0初始值为空并且能够被函数修改,如果需要,它可以用于保留返回值,虽然这不是必须的。$0也可以被给予一个别名。例如,这个函数能在任意一个有+操作符的数据类型上工作:
声明一个或者多个多态类型的输出参数也是同样的效果。这种情况下这个特殊的$0参数将不会被用到,这个输出参数本身也是同样的作用,例如:
39.3.2. 别名
newname ALIAS FOR oldname;
这个ALIAS语法比以前的章节中介绍的更加普通:你可以为任意一个变量声明一个别名,不只是函数的参数。这实际的用途是用预定义的名字为变量定义不同的名字,如触发器过程中的NEW或者OLD。 例子:
因此,ALIAS使同样的对象有两种不同的方式命名,如果不限制的使用,将会变得混乱。这种方法最好只用于覆盖预定义的名字。
最后,贴出解决上面这个问题的存储过程吧:
下面的例子是要调用一个存储过程自动创建对应的一系列表:
自动创建序列
第一个例子
调用:
第二个例子
调用
存储过程事物 http://www.php100.com/manual/PostgreSQL8/tutorial-transactions.html
PL/pgSQL - SQL存储过程语言 https://wiki.postgresql.org/wiki/9.1%E7%AC%AC%E4%B8%89%E5%8D%81%E4%B9%9D%E7%AB%A0
postgreSQL存储过程写法示例http://blog.sina.com.cn/s/blog_448574810101f64u.html
结构
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,$2,等等这样的标识符。有时候为了增强可读性,我们可以为 $n 参数名声明别名。然后通过这个别名或者数字标识符可以指向这个参数值。
有两种方法创建一个别名。最好的方法是用CREATE FUNCTION命令给予这个参数一个名字,例如:
CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$ BEGIN RETURN subtotal * 0.06; END; $$ LANGUAGE plpgsql;
另一个方法是,在PostgreSQL 8.0之前唯一的方法,明确的用别名进行声明,用以下的语法进行声明:
name ALIAS FOR $n;
这个风格的同一个例子看起来像下面这样 :
CREATE FUNCTION sales_tax(real) RETURNS real AS $$ DECLARE subtotal ALIAS FOR $1; BEGIN RETURN subtotal * 0.06; END; $$ LANGUAGE plpgsql;
注意:这两个例子不是完全一样的。在第一种情况,subtotal可以用sales_tax.subtotal进行引用,但是在第二种情况下不能这么做。(如果我们给这个内部块附加了一个标签,subtotal能够替代这个标签)
一些更多的例子:
CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$ DECLARE v_string ALIAS FOR $1; index ALIAS FOR $2; BEGIN -- some computations using v_string and index here END; $$ LANGUAGE plpgsql; CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$ BEGIN RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7; END; $$ LANGUAGE plpgsql
当一个PL/pgSQL函数用输出参数来进行声明时,给予这个输出参数$n名和一个任意的别名跟正常输入参数是同样的方法。即使这个输出参数以NULL开始时也是一个有效的变量,它应该在函数的执行过程中被分配。这个参数最好的值将被返回。例如,这个sales-tax例子也可以用这种方法完成:
CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$ BEGIN tax := subtotal * 0.06; END; $$ LANGUAGE plpgsql;
注意:我们省略了RETURNS real---我们可以将它包括在内,但它是多余的。
当返回多个值的时候输出参数将非常有用,一个简单的例子是:
CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$ BEGIN sum := x + y; prod := x * y; END; $$ LANGUAGE plpgsql;
如在Section 35.4.4中的讨论,这将为这个函数的结果创建一个匿名的记录类型。如果使用了RETURNS字句,那么必须给它指明RETURNS记录。
另外一种方法声明PL/pgSQL函数是用RETURNS TABLE,例如:
CREATE FUNCTION extended_sales(p_itemno int) RETURNS TABLE(quantity int, total numeric) AS $$ BEGIN RETURN QUERY SELECT quantity, quantity * price FROM sales WHERE itemno = p_itemno; END; $$ LANGUAGE plpgsql;
这跟声明一个或者多个OUT参数和制定RETURNS SETOF这些类型是同样的方法。
当返回的PL/pgSQL函数的类型被声明为一个多态类型(anyelement, anyarray, anynonarray, 或者anyenum),特殊参数$0将被创建。它的数据类型将实际的返回函数的类型,从实际的输入类型返回(见Section 35.2.5)。这运行这个函数访问这个实际的返回类型如Section 39.3.3显示的那样。$0初始值为空并且能够被函数修改,如果需要,它可以用于保留返回值,虽然这不是必须的。$0也可以被给予一个别名。例如,这个函数能在任意一个有+操作符的数据类型上工作:
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement) RETURNS anyelement AS $$ DECLARE result ALIAS FOR $0; BEGIN result := v1 + v2 + v3; RETURN result; END; $$ LANGUAGE plpgsql
声明一个或者多个多态类型的输出参数也是同样的效果。这种情况下这个特殊的$0参数将不会被用到,这个输出参数本身也是同样的作用,例如:
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement, OUT sum anyelement) AS $$ BEGIN sum := v1 + v2 + v3; END; $$ LANGUAGE plpgsql;
39.3.2. 别名
newname ALIAS FOR oldname;
这个ALIAS语法比以前的章节中介绍的更加普通:你可以为任意一个变量声明一个别名,不只是函数的参数。这实际的用途是用预定义的名字为变量定义不同的名字,如触发器过程中的NEW或者OLD。 例子:
DECLARE prior ALIAS FOR old; updated ALIAS FOR new;
因此,ALIAS使同样的对象有两种不同的方式命名,如果不限制的使用,将会变得混乱。这种方法最好只用于覆盖预定义的名字。
最后,贴出解决上面这个问题的存储过程吧:
CREATE OR REPLACE FUNCTION message_deletes(ids "varchar", userid int8) RETURNS int4 AS $BODY$ DECLARE r RECORD; del bool; num int4 := 0; sql "varchar"; BEGIN sql := 'select id,receiveuserid,senduserid,senddelete,receivedelete from message where id in (' || ids || ')'; FOR r IN EXECUTE sql LOOP del := false; IF r.receiveuserid=userid and r.senduserid=userid THEN del := true; ELSEIF r.receiveuserid=userid THEN IF r.senddelete=false THEN update message set receivedelete=true where id = r.id; ELSE del := true; END IF; ELSEIF r.senduserid=userid THEN IF r.receivedelete=false THEN update message set senddelete=true where id = r.id; ELSE del := true; END IF; END IF; IF del THEN delete from message where id = r.id; num := num + 1; END IF; END LOOP; return num; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;
下面的例子是要调用一个存储过程自动创建对应的一系列表:
CREATE OR REPLACE FUNCTION create_table_for_client(id int) RETURNS integer AS $BODY$ DECLARE num int4 := 0; sql "varchar"; BEGIN sql := 'create table _' || id || '_company(id int, name text)'; EXECUTE sql; sql := 'create table _' || id || '_employee(id int, name text)';EXECUTE sql; sql := 'create table _' || id || '_sale_bill(id int, name text)';EXECUTE sql; ....... return num; END; $BODY$ LANGUAGE plpgsql VOLATILE
自动创建序列
第一个例子
CREATE OR REPLACE FUNCTION auto_gen_seq() RETURNS bigint AS $BODY$ DECLARE rd RECORD; num int4 := 0; sql "varchar"; seq_sql varchar; BEGIN sql := 'SELECT tablename FROM pg_tables WHERE tablename NOT LIKE ''pg%'' AND tablename NOT LIKE ''sql_%'' ORDER BY tablename;'; FOR rd IN EXECUTE sql LOOP seq_sql:='CREATE SEQUENCE SQ_'||rd.tablename||' START 1000000 CACHE 30;'; BEGIN EXECUTE seq_sql; EXCEPTION WHEN TOO_MANY_ROWS THEN RAISE EXCEPTION 'employee % not unique', seq_sql; WHEN OTHERS THEN return -1; END; num := num + 1; END LOOP; return num; END; $BODY$ LANGUAGE plpgsql VOLATILE NOT LEAKPROOF COST 100;
调用:
select auto_gen_seq()
第二个例子
-- Function: auto_gen_seq(character) -- DROP FUNCTION auto_gen_seq(character); CREATE OR REPLACE FUNCTION auto_gen_seq("tbName" character) RETURNS character varying AS $BODY$/* 调用示例: SELECT tablename as tableName, 'sq_'||REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(tablename, 'tb_am_', ''), 'tb_sm_', ''), 'tb_pm_', ''), 'tb_pc_', ''), 'tb_ps_', ''), 'rh_', ''), 'TB_', ''), 'RH_', '' ), 'tb_', '') AS sqName ,auto_gen_seq(tablename||'') as successFlag,current_date,current_time FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER BY successFlag,tablename; */ DECLARE rd RECORD; seq_sql varchar; flag_str varchar; sq_name varchar; sq_datetime varchar; BEGIN seq_sql:='create table _sequence_table ( id SERIAL not null, code VARCHAR(200) null, increment_num INT8 null, minvalue_num INT8 null, maxvalue_num INT8 null, start_num INT8 null, cache_num INT8 null, cycle_flag VARCHAR(100) null,create_datetime timestamp without time zone,constraint PK__SEQUENCE_TABLE primary key (id) );CREATE UNIQUE INDEX INDEX__sequence_table ON _sequence_table (code);'; BEGIN EXECUTE seq_sql; EXCEPTION WHEN OTHERS THEN flag_str:='失败'; END; --sq_name:=replace(replace($1, 'TB_', ''), 'RH_', ''); --sq_name:=replace(replace(sq_name, 'tb_', ''), 'rh_', ''); sq_name:='sq_'||REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE($1, 'tb_am_', ''), 'tb_sm_', ''), 'tb_pm_', ''), 'tb_pc_', ''), 'tb_ps_', ''), 'rh_', ''), 'TB_', ''), 'RH_', '' ), 'tb_', ''); /* seq_sql:='drop SEQUENCE '||sq_name||';'; BEGIN EXECUTE seq_sql; EXCEPTION WHEN OTHERS THEN flag_str:='失败'; END; */ seq_sql:='CREATE SEQUENCE '||sq_name||' START 1000000 CACHE 30;'; BEGIN EXECUTE seq_sql; EXCEPTION WHEN OTHERS THEN return '失败,创建序列'; END; sq_datetime:=to_timestamp(current_date||' '||current_time,'yyyy-mm-dd hh24:mi:ss') ; seq_sql:='INSERT INTO _sequence_table( code,increment_num,minvalue_num,start_num, cache_num,create_datetime) VALUES ( '''||sq_name||''',1,1000000,1000000, 30,'''||sq_datetime||''');'; BEGIN EXECUTE seq_sql; EXCEPTION WHEN OTHERS THEN return '失败,插入序列信息'; END; return '成功'; END; $BODY$ LANGUAGE plpgsql VOLATILE STRICT COST 100; ALTER FUNCTION auto_gen_seq(character) OWNER TO postgres;
调用
SELECT tablename as tableName, 'sq_'||REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(tablename, 'tb_am_', ''), 'tb_sm_', ''), 'tb_pm_', ''), 'tb_pc_', ''), 'tb_ps_', ''), 'rh_', ''), 'TB_', ''), 'RH_', '' ), 'tb_', '') AS sqName ,auto_gen_seq(tablename||'') as successFlag,current_date,current_time FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER BY successFlag,tablename;
发表评论
-
全局唯一ID设计方案
2016-06-28 09:42 1252在分布式系统中,经常需要使用全局唯一ID查找对应的数据。产生这 ... -
MYSQL中取当前周/月/季/年的第一天与最后一天
2016-06-27 17:34 3030http://my.oschina.net/zx0211/bl ... -
按周,按月,按日,按小时分组统计数据
2016-06-27 17:07 3300http://my.oschina.net/mjRao/blo ... -
SELECT INTO 和 INSERT INTO SELECT 两种表复制语句区别
2016-06-27 16:27 1037http://my.oschina.net/MiniBu/bl ... -
CitusDB logo基于 PostgreSQL 的集群数据库 CitusDB
2016-06-20 11:43 1096http://www.oschina.net/p/citusd ... -
MYSQL级联查询,包括向上向下的级联
2016-05-31 11:47 2266http://my.oschina.net/u/178116/ ... -
阿里巴巴Druid数据源的配置与使用
2016-05-24 17:42 1563http://my.oschina.net/wjme/blog ... -
MySQL全文索引
2016-05-11 17:21 1006MySQL全文索引Match Against与Like比较 h ... -
mysql中间件研究(Atlas,cobar,TDDL), 分库分表插件
2016-05-09 14:15 3482http://www.guokr.com/blog/47576 ... -
Druid使用起步—在javaWeb项目中配置监控
2016-05-06 11:41 3287Druid使用起步—在javaWeb项目中配置监控: http ... -
基于spring,Atomikos,mybatis的分布式动态数据源JTA实现
2016-04-27 16:37 1095原文;http://www.blogjava.net/zuxi ... -
用Haproxy来做PostgreSQL的负载均衡
2016-04-22 09:48 1622http://my.oschina.net/Kenyon/bl ... -
MySQL的root密码重置
2016-04-14 10:29 985http://my.oschina.net/shawnplay ... -
分布式事务管理
2016-03-31 16:43 1054http://my.oschina.net/pingpangk ... -
基于 PostgreSQL 的集群数据库 CitusDB
2016-03-25 10:24 2467http://www.oschina.net/p/citusd ... -
shell中读写mysql数据库
2016-03-13 15:32 1418http://mingxinglai.com/cn/2013/ ... -
PostgreSQL In BigData 大数据Postgresql
2016-03-10 10:00 19911.BigSQL (整合了pg和hadoop的一个开源项目) ... -
MySQL管理客户端 Adminer
2016-02-14 10:31 1573http://www.oschina.net/p/admine ... -
PostgreSQ 表的继承和分区
2016-01-08 15:02 1219PostgreSQL分区表(Table Partitionin ... -
利用系统缓存提高PostgreSQL操作效率
2015-12-17 09:01 1222http://my.oschina.net/Suregogo/ ...
相关推荐
### 数据库存储过程的写法及连接各种数据库的方法 #### 概述 本文将详细介绍如何在不同的数据库系统中编写存储过程以及如何建立与这些数据库的连接。存储过程是一种预编译的SQL代码块,它可以存储在数据库服务器上...
MHT文件是一种单个文件存储网页的格式,可能包含了如何使用JDBC连接数据库的网页教程,包括HTML代码示例和步骤解释。 5. **常用数据库JDBC连接写法.txt** 这个文本文件很可能包含了常用的JDBC连接代码片段,例如...
- **介绍**:PyDbLite 是一个用纯 Python 编写的轻量级数据库引擎,它可以作为开发过程中的数据存储解决方案。 - **优点**: - 不需要安装额外的数据库软件。 - 方便进行数据迁移和备份。 - 对于小型项目来说,...
数据库是存储和管理结构化数据的系统,常见的关系型数据库有MySQL、PostgreSQL、Oracle等,非关系型数据库(NoSQL)包括MongoDB、Cassandra和Redis等。数据库在各种应用中起到核心作用,如网站、移动应用、数据分析...
现代Java应用程序应考虑使用更高效的JDBC驱动程序,例如MySQL JDBC驱动或PostgreSQL JDBC驱动等。 3. **安全性问题**:在实际部署时,不应将数据库用户名和密码硬编码到代码中,而应该通过环境变量或配置文件等方式...
相比起Mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲,SQLite的处理速度比他们都快。 SQL语句操作 SQL语句是SQLite数据库的核心操作语句。常用的SQL语句操作有增、删、改、查四种。即: * Create...