- 浏览: 272046 次
- 性别:
- 来自: 杭州
文章分类
最新评论
-
sunlin:
水文 其他erp就没这样的问题? 不能单方面拿sap说 ...
【转】揭秘SAP在华真相:天价收费与用户之灾 -
zjut_xiongfeng:
用当前日期所在月的第一天,比如2010-7-30的当前月第一天 ...
pb函数库之日期、时间函数 -
550627069:
你好!问下 在PB中显示当前日期的前一个月 应该怎么做?求教
pb函数库之日期、时间函数
在pgadmin里试了一下的例子,还不错,对于初学者来说是不错的资料。
PL/pgSQL can be used to define trigger procedures. A trigger procedure is created with the When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are: A trigger function must return either Row-level triggers fired The return value of a section 9.10 Trigger Procedures shows an example of a trigger procedure in PL/pgSQL. A PL/pgSQL Trigger Procedure: This example trigger ensures that any time a row is inserted or updated in the table, the current user name and time are stamped into the row. And it checks that an employee's name is given and that the salary is a positive value. Another way to log changes to a table involves creating a new table that holds a row for each insert, update, or delete that occurs. This approach can be thought of as auditing changes to a table. section 9.10 Trigger Procedures shows an example of an audit trigger procedure in PL/pgSQL. A PL/pgSQL Trigger Procedure For Auditing: This example trigger ensures that any insert, update or delete of a row in the One use of triggers is to maintain a summary table of another table. The resulting summary can be used in place of the original table for certain queries--often with vastly reduced run times. This technique is commonly used in Data Warehousing, where the tables of measured or observed data (called fact tables) can be extremely large. section 9.10 Trigger Procedures shows an example of a trigger procedure in PL/pgSQL that maintains a summary table for a fact table in a data warehouse. A PL/pgSQL Trigger Procedure For Maintaining A Summary Table: The schema detailed here is partly based on the Grocery Store example from The Data Warehouse Toolkit by Ralph Kimball. CREATE FUNCTION
command, declaring it as a function with no arguments and a return type of trigger
. Note that the function must be declared with no arguments even if it expects to receive arguments specified in CREATE TRIGGER
---trigger arguments are passed via TG_ARGV
, as described below. NEW
RECORD
; variable holding the new database row for INSERT
/UPDATE
operations in row-level triggers. This variable is NULL
in statement-level triggers. OLD
RECORD
; variable holding the old database row for UPDATE
/DELETE
operations in row-level triggers. This variable is NULL
in statement-level triggers. TG_NAME
name
; variable that contains the name of the trigger actually fired. TG_WHEN
text
; a string of either BEFORE
or AFTER
depending on the trigger's definition. TG_LEVEL
text
; a string of either ROW
or STATEMENT
depending on the trigger's definition. TG_OP
text
; a string of INSERT
, UPDATE
, or DELETE
telling for which operation the trigger was fired. TG_RELID
oid
; the object ID of the table that caused the trigger invocation. TG_RELNAME
name
; the name of the table that caused the trigger invocation. This is now deprecated, and could disappear in a future release. Use TG_TABLE_NAME
instead. TG_TABLE_NAME
name
; the name of the table that caused the trigger invocation. TG_TABLE_SCHEMA
name
; the name of the schema of the table that caused the trigger invocation. TG_NARGS
integer
; the number of arguments given to the trigger procedure in the CREATE TRIGGER
statement. TG_ARGV[]
text
; the arguments from the CREATE TRIGGER
statement. The index counts from 0. Invalid indices (less than 0 or greater than or equal to tg_nargs
) result in a null value. NULL
or a record/row value having exactly the structure of the table the trigger was fired for. BEFORE
may return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the INSERT
/UPDATE
/DELETE
does not occur for this row). If a nonnull value is returned then the operation proceeds with that row value. Returning a row value different from the original value of NEW
alters the row that will be inserted or updated (but has no direct effect in the DELETE
case). To alter the row to be stored, it is possible to replace single values directly in NEW
and return the modified NEW
, or to build a complete new record/row to return. BEFORE
or AFTER
statement-level trigger or an AFTER
row-level trigger is always ignored; it may as well be null. However, any of these types of triggers can still abort the entire operation by raising an error. CREATE TABLE emp (
empname text,
salary integer,
last_date timestamp,
last_user text
);
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
-- Check that empname and salary are given
IF NEW.empname IS NULL THEN
RAISE EXCEPTION 'empname cannot be null';
END IF;
IF NEW.salary IS NULL THEN
RAISE EXCEPTION '% cannot have null salary',
NEW.empname;
END IF;
-- Who works for us when she must pay for it?
IF NEW.salary < 0 THEN
RAISE EXCEPTION '% cannot have a negative
salary', NEW.empname;
END IF;
-- Remember who changed the payroll when
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
emp
table is recorded (i.e., audited) in the emp_audit
table. The current time and user name are stamped into the row, together with the type of operation performed on it. CREATE TABLE emp (
empname text NOT NULL,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS
TRIGGER AS $emp_audit$
BEGIN
--
-- Create a row in emp_audit to reflect the
-- operation performed on emp, make use of the
-- special variable TG_OP to work out the
-- operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an
AFTER trigger
END;
$emp_audit$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();
--
-- Main tables - time dimension and sales fact.
--
CREATE TABLE time_dimension (
time_key integer NOT NULL,
day_of_week integer NOT NULL,
day_of_month integer NOT NULL,
month integer NOT NULL,
quarter integer NOT NULL,
year integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON
time_dimension(time_key);
CREATE TABLE sales_fact (
time_key integer NOT NULL,
product_key integer NOT NULL,
store_key integer NOT NULL,
amount_sold numeric(12,2) NOT NULL,
units_sold integer NOT NULL,
amount_cost numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);
--
-- Summary table - sales by time.
--
CREATE TABLE sales_summary_bytime (
time_key integer NOT NULL,
amount_sold numeric(15,2) NOT NULL,
units_sold numeric(12) NOT NULL,
amount_cost numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON
sales_summary_bytime(time_key);
--
-- Function and trigger to amend summarized column(s) on
UPDATE, INSERT, DELETE.
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime()
RETURNS TRIGGER AS $maint_sales_summary_bytime$
DECLARE
delta_time_key integer;
delta_amount_sold numeric(15,2);
delta_units_sold numeric(12);
delta_amount_cost numeric(15,2);
BEGIN
-- Work out the increment/decrement amount(s).
IF (TG_OP = 'DELETE') THEN
delta_time_key = OLD.time_key;
delta_amount_sold = -1 * OLD.amount_sold;
delta_units_sold = -1 * OLD.units_sold;
delta_amount_cost = -1 * OLD.amount_cost;
ELSIF (TG_OP = 'UPDATE') THEN
-- forbid updates that change the time_key -
-- (probably not too onerous, as DELETE + INSERT
is how most
-- changes will be made).
IF ( OLD.time_key != NEW.time_key) THEN
RAISE EXCEPTION 'Update of time_key : % -> %
not allowed', OLD.time_key, NEW.time_key;
END IF;
delta_time_key = OLD.time_key;
delta_amount_sold = NEW.amount_sold -
OLD.amount_sold;
delta_units_sold = NEW.units_sold - OLD.units_sold;
delta_amount_cost = NEW.amount_cost -
OLD.amount_cost;
ELSIF (TG_OP = 'INSERT') THEN
delta_time_key = NEW.time_key;
delta_amount_sold = NEW.amount_sold;
delta_units_sold = NEW.units_sold;
delta_amount_cost = NEW.amount_cost;
END IF;
-- Insert or update the summary row with the new values.
<<insert_update>>
LOOP
UPDATE sales_summary_bytime
SET amount_sold = amount_sold +
delta_amount_sold,
units_sold = units_sold + delta_units_sold,
amount_cost = amount_cost + delta_amount_cost
WHERE time_key = delta_time_key;
EXIT insert_update WHEN found;
BEGIN
INSERT INTO sales_summary_bytime (
time_key,
amount_sold,
units_sold,
amount_cost)
VALUES (
delta_time_key,
delta_amount_sold,
delta_units_sold,
delta_amount_cost
);
EXIT insert_update;
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
-- do nothing
END;
END LOOP insert_update;
RETURN NULL;
END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;
CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();
INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
SELECT * FROM sales_summary_bytime;
DELETE FROM sales_fact WHERE product_key = 1;
SELECT * FROM sales_summary_bytime;
UPDATE sales_fact SET units_sold = units_sold * 2;
SELECT * FROM sales_summary_bytime;
发表评论
-
PostgreSQL
2010-08-19 11:16 1238PostgreSQL 第四十三章. ... -
postgresql 锁测试
2010-04-15 20:44 1489PostgreSQL 手册提供锁的定义与解释: 在附件中将对 ... -
锁的类度
2010-04-15 15:18 773SELECT au_lname FROM authors WI ... -
pgsql触发器函数
2010-04-09 13:20 1192-- Function: maint_sales_summar ... -
项目用到的数据库pgsql 官方开发文档
2010-03-30 17:43 746项目用到的数据库pgsql 文档资料。
相关推荐
java存储过程和PL/SQL存储过程的比较,Java存储过程的使用方法。
C#调用oracle存储过程的通用方法 C#调用oracle存储过程的通用方法
本文实例讲述了Python使用cx_Oracle调用Oracle存储过程的方法。分享给大家供大家参考,具体如下: 这里主要测试在Python中通过cx_Oracle调用PL/SQL。 首先,在数据库端创建简单的存储过程。 create or replace ...
用户可以像使用自定义函数那样重复调用这些存储过程,实现它所定义的操作。本实验所需数据库为前面实验已经建立好的学生选课管理数据库。 1.编写带输入参数的存储过程proc_St:在查询分析器中创建一个存储过程,...
主要介绍了SQLServer存储过程中事务的使用方法,简短的代码带大家更好的学习使用SQLServer存储过程中事务,感兴趣的小伙伴们可以参考一下
本文实例讲述了MySQL存储过程的异常处理方法。分享给大家供大家参考。具体如下: mysql> mysql> delimiter $$ mysql> mysql> CREATE PROCEDURE myProc -> (p_first_name VARCHAR(30), -> p_last_name VARCHAR(30)...
Java调用Oracle存储过程的方法
GBase8s 存储过程测试方法 -- '------------------ 存储过程--------------------------------' --在交互式sql交互式工具中执行,创建表 CREATE TABLE COM_T2 (C1 INT,C2 varchar2(10)); INSERT INTO COM_T2 VALUES...
birt报表中调用存储过程的方法. 目录 1. 概述 3 2. BIRT支持的存储过程返回值类型 3 3. BIRT调用存储过程的语法 4 4. 创建存储过程数据源/集 4 5. 在BIRT中使用存储过程 5 调用返回单结果集的存储过程。 5 调用...
java 调用存储过程java 调用存储过程java 调用存储过程java 调用存储过程java 调用存储过程java 调用存储过程java 调用存储过程
CallableStatement 调用mysql5.0的存储过程和方法 配有创建存储过程和方法的源代码
能不能写个动态的业务,只输入存储过程名称,自动...只写一个通用方法,就可以调用所有的存储过程。只根据输入不同的存储过程名称、参数内容,自动调用不同的存储过程。 已经使用在多个项目中 全开源项目 请放心下载
Java调用存储过程的2种方法 Java调用存储过程的2种方法 Java调用存储过程的2种方法
dapper的调用各种存储过程的方法的帮助类文档,可通过该文档了解并学会使用Dapper,并且调用Dapper
SQL2005存储过程的解密方法。文件->新建->数据库引擎查询->在登录窗口输入:admin:[数据库服务器IP或数据库服务器名称]->输入账号sa和sa的密码,登录到查询界面;将该文档内容粘贴到查询窗口,修改第一行:use [你的...
旨在帮助读者通过案例学习,快速掌握存储过程的应用方法和技巧。 2、博主博客:https://blog.csdn.net/gongjin28_csdn/article/details/127928648 3、资源分为两类:真实应用场景的存储过程案例61个(真实业务案例,...
存储过程-02.存储过程分类及参数使用方法
在MySQL 入门教程中,我们能够看到很多关于如何创建储存过程 和如何利用 IN 和 OUT 参数调用存储过程的示例。这些示例都很简单,能够很好的帮助你理解 MySQL 中创建带参数存储过程的语法。这些示例已在 MySQL 5.5 中...
jdbc 存储过程 java 程序 JDBC 调用存储过程方法
本文实例讲述了mysql存储过程之返回多个值的方法。分享给大家供大家参考,具体如下: mysql存储函数只返回一个值。要开发返回多个值的存储过程,需要使用带有INOUT或OUT参数的存储过程。咱们先来看一个orders表它的...