`

PostgreSQL 存储过程

 
阅读更多
-- Function: tms_org_score_statis()

-- DROP FUNCTION tms_org_score_statis();

CREATE OR REPLACE FUNCTION tms_org_score_statis()
  RETURNS character varying AS
$BODY$

declare 
r record;
statis_sql "varchar";
execute_sql "varchar";
num int4 := 0;
total_score numeric(10,2);
begin
statis_sql := 'select tms.* from 
(SELECT ORG.ID ORG_ID, COALESCE(AB.participation_legal, 0) participation_legal, COALESCE(AB.participation_leader, 0) participation_leader, 
COALESCE(AB.participation_cadre, 0) participation_cadre, COALESCE(GH.participation_staff, 0) participation_staff,
COALESCE(AB.qualified_legal, 0) qualified_legal, COALESCE(AB.qualified_leader, 0) qualified_leader, COALESCE(AB.qualified_cadre, 0)qualified_cadre, 
COALESCE(GH.qualified_staff, 0) qualified_staff, COALESCE(GH.avg_answer_time, 0) avg_answer_time,
CASE WHEN TSC.ORG_ID > 0 THEN TRUE ELSE FALSE END AS EXIST
 FROM PUBLIC.T_ORG ORG
 LEFT OUTER JOIN 
(
SELECT BB.ORG_ID, 
SUM(participation_legal) participation_legal, 
SUM(participation_leader) participation_leader, 
SUM(participation_cadre) participation_cadre, 
SUM(qualified_legal) qualified_legal, 
SUM(qualified_leader) qualified_leader, 
SUM(qualified_cadre) qualified_cadre
FROM 
(
	SELECT AA.ORG_ID, 
	CASE WHEN AA.ROLE_ID = 1 THEN 5 ELSE 0 END AS participation_legal,
	CASE WHEN AA.ROLE_ID = 2 THEN 3 ELSE 0 END AS participation_leader, 
	CASE WHEN AA.ROLE_ID = 3 THEN 2 ELSE 0 END AS participation_cadre, 
	CASE WHEN AA.ROLE_ID = 1 AND AA.AR = 10 THEN 5 ELSE 0 END AS qualified_legal,
	CASE WHEN AA.ROLE_ID = 2 AND AA.AR = 10 THEN 3 ELSE 0 END AS qualified_leader,
	CASE WHEN AA.ROLE_ID = 3 AND AA.AR = 10 THEN 2 ELSE 0 END AS qualified_cadre
	FROM 
	(
	SELECT US.ORG_ID, UR.ROLE_ID, MIN(ANSWER_RESULT) AR
	FROM PUBLIC.T_USER US 
	LEFT OUTER JOIN PUBLIC.T_USER_ROLE UR 
	ON US.ID = UR.USER_ID 
	LEFT OUTER JOIN PUBLIC.T_QUESTION_PAPER QP 
	ON US.ID = QP.USER_ID 
	where ur.role_id in (1, 2, 3)
	GROUP BY US.ORG_ID, UR.ROLE_ID 
	) AA
) BB
GROUP BY BB.ORG_ID
) AB
ON ORG.ID = AB.ORG_ID
left outer JOIN 
(
select org_id, (all_real_qty::numeric * 30 / (participator_qty)) participation_staff,
(total_SCORE / all_real_qty) * 0.5 qualified_staff, total_answer_time / all_real_qty avg_answer_time 
from 
(
SELECT ORG_ID, COUNT(*) all_real_qty,MIN(participator_qty) participator_qty, 
SUM(COALESCE(QP.score,0)) total_SCORE, COALESCE(sum(extract(epoch from (END_TIME - START_TIME))), 0) total_answer_time
FROM PUBLIC.V_USER_INFO VI
LEFT OUTER JOIN PUBLIC.T_QUESTION_PAPER QP
ON VI.USER_ID = QP.USER_ID
GROUP BY ORG_ID
) GG
) GH
ON ORG.ID = GH.ORG_ID
LEFT OUTER JOIN PUBLIC.t_competition_score TSC
ON ORG.ID = TSC.ORG_ID
) tms';

for r in execute statis_sql loop
	total_score := r.participation_legal + r.participation_leader + r.participation_cadre + r.participation_staff + r.qualified_legal + r.qualified_leader + r.qualified_cadre + r.qualified_staff;
	num := num + 1;

	IF R.EXIST = false THEN
	
	execute_sql := 'insert into public.t_competition_score(org_id,participation_legal,participation_leader, participation_cadre, participation_staff,
	qualified_legal,qualified_leader,qualified_cadre,qualified_staff,avg_answer_time, total_score,create_time,status,last_update_time) values
	(' || r.org_id || ',' || r.participation_legal || ',' || r.participation_leader || ',' || r.participation_cadre || ',' || r.participation_staff || ',' ||
	r.qualified_legal || ',' || r.qualified_leader || ',' || r.qualified_cadre || ',' || r.qualified_staff || ',' || r.avg_answer_time || ',' || 
	total_score || ', ''' || now() || ''',' || 20 || ',''' || now() || ''')';
	
	END IF;
	IF R.EXIST = true THEN 
	execute_sql := 'UPDATE public.t_competition_score set participation_legal = ' || r.participation_legal || ', participation_leader = ' || r.participation_leader || ', participation_cadre = '
	|| r.participation_cadre || ', participation_staff = ' || r.participation_staff || ', qualified_legal = ' || r.qualified_legal || ', qualified_leader = ' || r.qualified_leader
	|| ', qualified_cadre = ' || r.qualified_cadre || ', qualified_staff = ' || r.qualified_staff || ', avg_answer_time = ' || r.avg_answer_time || ', total_score = ' || total_score
	|| ', last_update_time = ''' || now() || ''' where org_id = ' || r.org_id;
	END IF;
	RAISE NOTICE '%', 'sql: ' || execute_sql;
	EXECUTE execute_sql;
end loop;
RETURN num;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

 

 

 

 

perform org_id from t_competition_score where org_id = 1;

if found then 

raise notice 't';

else 

raise notice 'f';

end if;

 

 

 

 

 

 

 

CREATE TABLE message
(
id int8 NOT NULL,
receiveuserid int8,
senduserid int8,
receivedelete bool DEFAULT false,
senddelete bool DEFAULT false,
……
CONSTRAINT usermessage_pkey PRIMARY KEY (id)
)
略去其他字段,senduserid是发信息的用户id,senddelete如为true则表示这条消息被发信人所删除;至于receive我就不用说了。一条信息只有被发信人和收信人都删除,才能真正从表里删除(这是显然的,否则收信人删了一条消息后,发信人的“发件箱”里就会找不到这条消息)。
所以删除消息(可能是多条消息)的时候要进行各种判断(是否是发信人?是否是收信人?是否真正删除?),用单条SQL语句完成这个工作显然有些困难(当然用循环嵌套select不考虑效率的话也是可以实现的),只好写存储过程了。
顺便介绍常用的PL/pgSQL结构和语法吧:

  • 结构

    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 message_deletes(ids "varchar"userid int8)
  2.   RETURNS int4 AS
  3. $BODY$
  4. DECLARE
  5.   r RECORD;
  6.   del bool;
  7.   num int4 := 0;
  8.   sql "varchar";
  9. BEGIN
  10.   sql := 'select id,receiveuserid,senduserid,senddelete,receivedelete from message where id in (' || ids || ')';
  11.   FOR r IN EXECUTE sql LOOP
  12.     del := false;
  13.     IF r.receiveuserid=userid and r.senduserid=userid THEN
  14.       del := true;
  15.     ELSEIF r.receiveuserid=userid THEN
  16.       IF r.senddelete=false THEN
  17.         update message set receivedelete=true where id = r.id;
  18.       ELSE
  19.         del := true;
  20.       END IF;
  21.     ELSEIF r.senduserid=userid THEN
  22.       IF r.receivedelete=false THEN
  23.         update message set senddelete=true where id = r.id;
  24.       ELSE
  25.         del := true;
  26.       END IF;
  27.     END IF;
  28.     IF del THEN
  29.       delete from message where id = r.id;
  30.       num := num + 1;
  31.     END IF;
  32.   END LOOP;
  33.   return num;
  34. END;
  35. $BODY$
  36.   LANGUAGE 'plpgsql' VOLATILE;
分享到:
评论

相关推荐

    Postgresql存储过程

    Postgresql存储过程详解 Postgresql存储过程是指在Postgresql数据库中定义的一组SQL语句的集合,它可以完成复杂的操作,并且可以重复使用。Postgresql存储过程可以用来实现业务逻辑,减少数据库服务器的压力和网络...

    PostgreSQL 存储过程调试

    调试PostgreSQL存储过程首先需要一个支持调试功能的客户端工具,例如pgAdmin或psql。在pgAdmin中,你可以通过图形界面设置断点,查看变量值和调用堆栈。在命令行工具psql中,可以利用 `\set` 命令设置变量,以及`\...

    postgresql存储过程

    PostgreSQL存储过程是一种数据库编程的方法,它允许用户在数据库中定义和执行复杂的操作逻辑,而不仅仅是简单的查询。存储过程在数据库管理系统中起着至关重要的作用,它们可以提高性能,增加安全性,减少网络流量,...

    Postgresql存储过程.docx

    PostgreSQL 存储过程详解 PostgreSQL 存储过程是一种强大的功能,可以实现复杂的逻辑操作和数据处理。下面将详细介绍 PostgreSQL 存储过程的结构、变量类型、连接字符、控制结构等知识点。 一、存储过程结构 存储...

    关于PostGreSQL中的存储过程

    PostgreSQL 存储过程详解 PostgreSQL 是一个开源的数据库管理系统,它提供了强大的数据存储和管理功能。其中,存储过程是一个非常重要的概念,本文将对 PostgreSQL 中的存储过程进行详细的介绍和解释。 什么是存储...

    Mybatis调用PostgreSQL存储过程实现数组入参传递

    例如,以下是一个接受整数数组作为参数的PostgreSQL存储过程: ```sql CREATE OR REPLACE FUNCTION "public"."func_arr_update"(ids _int4) RETURNS "pg_catalog"."void" AS $BODY$ DECLARE scount INTEGER; ...

    PostgreSQL存储过程用法实战详解

    通过这个实例,我们可以学习到以下几点关于PostgreSQL存储过程的知识: 1. 存储过程的创建和替换:使用`CREATE OR REPLACE FUNCTION`语法。 2. 动态SQL的使用:允许在运行时构建SQL语句。 3. 安全地处理变量:使用`...

    初识PostgreSQL存储过程

    **初识 PostgreSQL 存储过程** 在数据库管理领域,存储过程是不可或缺的一部分,它是由一组预编译的 SQL 语句组成,用于执行特定任务。根据百度百科的定义,存储过程是在大型数据库系统中,一组为了完成特定功能的 ...

    一套C#与PostgreSQL数据库完美结合的实例

    本套程序是在VS2005下C#开发,...在对PostgreSQL数据库进行操作时,用到了如何调用存储过程来完成各项操作。 将App_Data下的数据库文件导入到PostgreSQL 8.3版里面,库名为:HYGL 然后修改web.config中的配置即可使用

    grunt-pg-utils:用于PostgreSql存储过程的版本控制的繁琐任务,以及各种实用程序

    用于PostgreSql存储过程的版本控制,自动化的数据库还原/转储,查询执行的繁琐任务。 警告:这个项目是由开发商不再维护,我们可以切换到更多的方式稳定的解决方案: 入门 这个插件需要~0.4.1 如果您以前从未...

    java-sproc-wrapper:Java存储过程包装器:从Java调用PostgreSQL存储过程

    打包程序 通过简单的Java“ SProcService”接口使PostgreSQL存储过程(SProcs)可用的库,包括自动对象序列化和反序列化(使用typemapper和Convention-over-configuration)。 支持水平数据库分片(分区/访问逻辑...

    浅析PostgreSQL事务处理机制

    此外,PostgreSQL还允许用户轻松地定制和扩展,包括自定义函数、操作符、数据类型、索引以及过程语言。它可以运行在多种操作系统上,如Linux、UNIX、Windows等,并提供了丰富的编程接口支持,例如C/C++、Java、.NET...

    让postgresql支持存储过程(函数)的调试.doc

    在PostgreSQL数据库系统中,存储过程(也称为函数)是执行特定任务的预编译代码块,它们可以提高性能并简化复杂查询。然而,当这些过程或函数出现错误时,调试变得至关重要。本文将深入探讨如何使PostgreSQL支持存储...

    postgreSQL的CURD和存储过程

    本文将深入探讨PostgreSQL中的CURD操作以及存储过程,并结合jdbc、hibernate、ibatis三种不同的Java数据访问技术进行讲解。 **一、CURD操作** 1. **创建(Create)**:在PostgreSQL中,创建表是最基本的操作。例如,...

    PostgreSQL中调用存储过程并返回数据集实例

    在PostgreSQL数据库中,存储过程是一种用户自定义的SQL集合,它可以执行一系列操作,并可能返回一个或多个结果。本文将详细讲解如何在PostgreSQL中创建存储过程并返回数据集,通过一个具体的实例来阐述整个流程。 1...

    pltoolbox:一组用于 PostgreSQL 存储过程的函数

    一组用于 PostgreSQL 存储过程的函数 为了编译和安装此模块,您需要从(root)类 Unix 外壳运行以下命令 USE_PGXS=1 make install 为了启用具有 SQL 接口的模块,您需要从(非强制根)shell 运行以下命令(您可能...

Global site tag (gtag.js) - Google Analytics