Create FUNCTION InsertComp (@in varchar(30))
RETURNS varchar(30)
AS
BEGIN
declare
@pos int,
@count int,
@result varchar(30),
@len int
set @result=@in
--点前面的位置
set @pos=CHARINDEX('.',@result)-1
if (@pos=-1) set @pos=len(@result)
set @count=@pos-3
while(@count>=1)
begin
set @len=len(@result)-@count
set @result=left(@result,@count)+','+substring(@result,@count+1,@len)
set @count=@count-3
end
return @result
END
触发器:
Sql内、外、左、右连接
在数据库应用中，经常要涉及从两个或更多的表中查询数据，这就需要使用连接查询。
其格式如下：
SELECT COLUMN_NAME，C O L U M N _ N A M E [ , . . . n ]
FROM TA B L E _ N A M E，TA B L E _ N A M E [ , . . . n ]
WHERE TABLE_NAME.COLUMN_NAME JOIN_OPERATOR TA B L E _ N A M E . C O L U M N _ N A M E
上述的连接操作符（J O I N _ O P E R ATO R）可以是：=、>、<、> =、< =、! =、< >、! >、! <、= *、* =。
在A N S I中，“=”连接应该写成Inner Join；“* =”连接应该写成LEFT OUT JOIN；“= *”连接应该写成“ RIGHT OUT JOIN”。
在SQL SERVER中，这些写法都可以用。但是，A N S I规定的写法是INNER JOIN等标志。一般而言，在任何连接中，都使用“主键=外键”的连接查询条件。
---------------------------------------------------------------------------------
非限制连接（CROSS JOIN），就是指不带W H E R E子句的查询。在数学上，就是表的笛卡尔积。若R表和S表非限制连接，而且R表有X行，S表有Y行，那么结果集是X * Y行。即：R表的一行对应着S表的所有行。在应用中，非限制连接产生的是无意义结果集，但在数据库的数学模式上有重要的作用。
-------------------------------------------------------------------------
自然连接（INNER JOIN）也叫内连接。我们先看下面的例子：
SELECT PUB_NAME,TITLE
FROM TITLES,PUBLISHERS
WHERE TITLES.PUB_ID=PUBLISHERS.PUB_ID
或写成：
SELECT PUB_NAME,TITLE
FROM TITLES INNER JOIN PUBLISHERS
ON TITLES.PUB_ID=PUBLISHERS.PUB_ID
其中，INNER JOIN是SQL Server的缺省连接，可简写为J O I N。在J O I N后面指定哪些表作连接。O N后面指定了连接的条件。
以上操作的过程如下：
1) 从T I T L E S表中取出一条符合其条件的记录。
2) 扫描P U B L I S H E R S表，分别检查每条记录是否在连接属性上同表T I T L E S取出的记录相
3) 相等就显示信息。继续从表T I T L E S中取下一条记录，重复步骤2。其实，两个或多个表要做连接，那么这些表之间必然存在着主键和外键的关系。所以，只需要将这些键的关系列出，就可以得出表连接的结果。如上例中， P U B _ I D是P U B L I S H E R S表的主键， P U B_ I D又是TI T L E S表的外键，参照P U B L I S H E R S表中的P U B _ I D .所以，这两个表的连接条件就是T I T L E S.PUB_ID=PUBLISHERS. PUB_ID。
-----------------------------------------------------------------------
外连接（OUTER JOIN）允许限制一张表中的行，而不限制另一张表中的行。
下面举两个例子来说明外连接的用法。请比较这两个例子。
例显示所有的书名（无销售记录的书也包括在内，“*”在左边表示不限制左边表的数
SELECT TITLES.TITLE_ID,title=convert(char(38),TITLE),QTY
FROM TITLES,SALES
WHERE TITLES.TITLE_ID *= SALES.TITLE_ID
或写为：
SELECT TITLES.TITLE_ID,title=convert(char(38),TITLE),QTY
FROM TITLES LEFT OUTER JOIN SALES
ON TITLES.TITLE_ID = SALES.TITLE_ID
外连接的方法有两种:
• A *= B 包括第一张表A的所有行在内，而不考虑语句限制（如： A . t i t l e _ i d = B . t i t l e _ i d）。
相当于LEFT OUT JOIN。
• A =* B 包括第二张表B的所有行在内，而不考虑语句限制（如： A . t i t l e _ i d = B . t i t l e _ i d）。
相当于RIGHT OUT JOIN。
创建外连接规则：
1) 外连接显示外部表中的所有行，包括与相关表不相配的行在内。
2) 外连接只能在两张表之间进行。
3) 不能在内部表上使用IS NULL检索条件。
Sql server利用完整性约束机制防止无效的数据进入数据库的基表，如果任何DML执行结果破坏完整性约束，该语句被回滚并返回一上个错误。Sql server实现的完整性约束完全遵守ANSI X3。135-1989和ISO9075-1989标准。
用完整性约束实施数据完整性规则的优点
利用完整性约束实施数据完整性规则有下列优点：
◆定义或更改表时，不需要程序设计，便很容易地编写程序并可消除程序性错误，其功能是由Sql server控制。所以说明性完整性约束优于应用代码和数据库触发器。
◆对表所定义的完整性约束是存储在数据字典中，所以由任何应用进入的数据都必须遵守与表相关联的完整性约束。
◆具有最大的开发能力。当由完整性约束所实施的事务规则改变时，管理员只需改变完整性约束的定义，所有应用自动地遵守所修改的约束。
◆由于完整性约束存储在数据字典中，数据库应用可利用这些信息，在SQL语句执行之前或由Sql server检查之前，就可立即反馈信息。
◆由于完整性约束说明的语义是清楚地定义，对于每一指定说明规则可实现性能优化。
◆由于完整性约束可临时地使不能，以致在装入大量数据时可避免约束检索的开销。当数据库装入完成时，完整性约束可容易地使其能，任何破坏完整性约束的任何新行在例外表中列出。
Sql server的DBA和应用开始者对列的值输入可使用的完整性约束有下列类型：
◆NOT NULL约束:如果在表的一列的值不允许为空，则需在该列指定NOT NULL约束。
◆UNIQUE码约束：在表指定的列或组列上不允许两行是具有重复值时，则需要该列或组列上指定UNIQUE码完整性约束。在UNIQUE码约束定义中的列或组列称为唯一码。所有唯一完整性约束是用索引方法实施。
◆PRIMARY KEY约束：在数据库中每一个表可有一个PRIMARY KEY约束。包含在PRIMARY KEY完整性约束的列或组列称为主码，每个表可有一个主码。Sql server使用索引实施PRIMARY KEY约束。
◆FOREIGN KEY约束（可称引用约束）：在关系数据库中表可通过公共列相关联，该 规则控制必须维护的列之间的关系。包含在引用完整性约束定义的列或组列称为外来码。由外来码所引用的表中的唯一码或方码，称为引用码。包含有外来码的表称为子表或从属表。由子表的外来码所引用的表称为双亲表或引用表。如果对表的每一行，其外来码的值必须与主码中一值相匹配，则需指定引用完整性约束。
◆CHECK约束：表的每行对一指定的条件必须是TRUE或未知，则需在一列或列组上指定CHECK完整性约束。如果在发出一个DML语句时，CHECK约束的条件计算得FALSE时，该语句被回滚
SQL Server 2000中的触发器使用
触发器是数据库应用中的重用工具，它的应用很广泛。这几天写一个化学数据统计方面的软件，需要根据采样，自动计算方差，在这里，我使用了触发器。
下面我摘录了SQL Server官方教程中的一段关于触发器的文字，确实有用的一点文字描述。
可以定义一个无论何时用INSERT语句向表中插入数据时都会执行的触发器。
当触发INSERT触发器时，新的数据行就会被插入到触发器表和inserted表中。inserted表是一个逻辑表，它包含了已经插入的数据行的一个副本。inserted表包含了INSERT语句中已记录的插入动作。inserted表还允许引用由初始化INSERT语句而产生的日志数据。触发器通过检查inserted表来确定是否执行触发器动作或如何执行它。inserted表中的行总是触发器表中一行或多行的副本。
日志记录了所有修改数据的动作(INSERT、UPDATE和DELETE语句)，但在事务日志中的信息是不可读的。然而，inserted表允许你引用由INSERT语句引起的日志变化，这样就可以将插入数据与发生的变化进行比较，来验证它们或采取进一步的动作。也可以直接引用插入的数据，而不必将它们存储到变量中。
示例
在本例中，将创建一个触发器。无论何时订购产品（无论何时向Order Details表中插入一条记录），这个触发器都将更新Products表中的一列(UnitsInStock)。用原来的值减去订购的数量值即为新值。
USE Northwind
CREATE TRIGGER OrdDet_Insert
ON [Order Details]
FOR INSERT
AS
UPDATE P SET
UnitsInStock = P.UnitsInStock – I.Quantity
FROM Products AS P INNER JOIN Inserted AS I
ON P.ProductID = I.ProductID
DELETE触发器的工作过程
当触发DELETE触发器后，从受影响的表中删除的行将被放置到一个特殊的deleted表中。deleted表是一个逻辑表，它保留已被删除数据行的一个副本。deleted表还允许引用由初始化DELETE语句产生的日志数据。
使用DELETE触发器时，需要考虑以下的事项和原则：
•当某行被添加到deleted表中时，它就不再存在于数据库表中；因此，deleted表和数据库表没有相同的行。
•创建deleted表时，空间是从内存中分配的。deleted表总是被存储在高速缓存中。
•为DELETE动作定义的触发器并不执行TRUNCATE TABLE语句，原因在于日志不记录TRUNCATE TABLE语句。
示例
在本例中，将创建一个触发器，无论何时删除一个产品类别（即从Categories表中删除一条记录），该触发器都会更新Products表中的Discontinued列。所有受影响的产品都标记为1，标示不再使用这些产品了。
USE Northwind
CREATE TRIGGER Category_Delete
ON Categories
FOR DELETE
AS
UPDATE P SET Discontinued = 1
FROM Products AS P INNER JOIN deleted AS d
ON P.CategoryID = d.CategoryID
UPDATE触发器的工作过程
可将UPDATE语句看成两步操作：即捕获数据前像(before image)的DELETE语句，和捕获数据后像(after image)的INSERT语句。当在定义有触发器的表上执行UPDATE语句时，原始行（前像）被移入到deleted表，更新行（后像）被移入到inserted表。
触发器检查deleted表和inserted表以及被更新的表，来确定是否更新了多行以及如何执行触发器动作。
可以使用IF UPDATE语句定义一个监视指定列的数据更新的触发器。这样，就可以让触发器容易的隔离出特定列的活动。当它检测到指定列已经更新时，触发器就会进一步执行适当的动作，例如发出错误信息指出该列不能更新，或者根据新的更新的列值执行一系列的动作语句。
语法
IF UPDATE (<column_name>)
例1
本例阻止用户修改Employees表中的EmployeeID列。
USE Northwind
GO
CREATE TRIGGER Employee_Update
ON Employees
FOR UPDATE
AS
IF UPDATE (EmployeeID)
BEGIN
RAISERROR ('Transaction cannot be processed.\
***** Employee ID number cannot be modified.', 10, 1)
ROLLBACK TRANSACTION
END
INSTEAD OF触发器的工作过程
可以在表或视图上指定INSTEAD OF触发器。执行这种触发器就能够替代原始的触发动作。INSTEAD OF触发器扩展了视图更新的类型。对于每一种触发动作(INSERT、UPDATE或 DELETE)，每一个表或视图只能有一个INSTEAD OF触发器。
INSTEAD OF触发器被用于更新那些没有办法通过正常方式更新的视图。例如，通常不能在一个基于连接的视图上进行DELETE操作。然而，可以编写一个INSTEAD OF DELETE触发器来实现删除。上述触发器可以访问那些如果视图是一个真正的表时已经被删除的数据行。将被删除的行存储在一个名为deleted的工作表中，就像AFTER触发器一样。相似地，在UPDATE INSTEAD OF触发器或者INSERT INSTEAD OF触发器中，你可以访问inserted表中的新行。
不能在带有WITH CHECK OPTION定义的视图中创建INSTEAD OF触发器
SQL Server 两个触发器例子
--当在“出货表_明细”增、删、改时，相应地在“订单表_明细”对已交货数量和未交货数量作出修改。
Create trigger tr_出货表_明细
on 出货表_明细
For DELETE,INSERT,UPDATE
as
Declare @JiaoHuo varchar(20)
Begin
Set @JiaoHuo = (Select Sum(交货数量) from 出货表_明细 Group by 序号 Having 序号 = (Select 序号 From Deleted))
Set @JiaoHuo = ISNULL(@JiaoHuo,(Select Sum(交货数量) from 出货表_明细 Group by 序号 Having 序号 = (Select 序号 From Inserted)))
Set @JiaoHuo = ISNULL(@JiaoHuo,0)
UPDATE N SET 已交货数量 = @JiaoHuo, 未交货数量 = N.订购数量 - @JiaoHuo
From 订单表_明细 AS N INNER Join Deleted AS D
ON N.序号 = D.序号
End
--当“订单表_明细”中增、删、改时、判断该笔订单的未交货数量合计是否为0，修改“订单表”的订单状态为“已完成”，否则为“未完成”
Create trigger tr_Status
ON 订单表_明细
FOR INSERT,UPDATE,DELETE
AS
Declare @Order varchar(20)
Begin
Set @Order = (Select 订单号 from Deleted)
Set @Order = ISNULL(@Order,(Select 订单号 from Inserted))
IF(Select Sum(未交货数量) From 订单表_明细 Group by 订单号 Having 订单号 = @Order)= 0
Begin
UPDATE 订单表 Set 订单状态 = '已完成',完成日期 = GetDate() Where 订单号 = @Order
End
Else
Begin
UPDATE 订单表 Set 订单状态 = '未完成',完成日期 = NUll Where 订单号 = @Order
End
End
查询语言（SQL）函数
SQL 函数执行一个任意 SQL 查询的列表，返回列表里最后一个查询的结果。 它必须是一条 SELECT．在比较简单的情况下(非集合的情况)， 返回最后一条查询结果的第一行．(请记住多行结果的"第一行" 是不明确的，除非你用 ORDER BY 对结果排序．) 如果最后一个查询碰巧不返回行，那么返回 NULL．
另外，一个 SQL 函数可以声明为返回一个集合，方法是把该函数的 返回类型声明为 SETOF sometype． 这个时候最后一条查询结果的所有行都会被返回．更多的细节在下面讲．
SQL 函数的函数体应该是一个用分号分隔的一条或多条 SQL 语句的列表． 请注意，因为 CREATE FUNCTION 命令的语法要求 函数体要封闭在单引号里面，所以在函数体中使用的单引号 (') 必须逃逸，方法是写两个单引号(') 或者 在需要逃逸的单引号之前放一个反斜扛 (\')．
SQL 函数的参数在查询里可以用 $n 语法引用： $1指第一个参数，$2 指第二个参数，以此类推。 如果参数是 复合类型，那么可以用点表示法， 例如，"$1.emp"，访问参数里的字段。
12.2.1. 例子
看看下面这个简单的 SQL 函数的例子， 它将用于对一个银行帐号做扣款（借记消费 debit）动作：
CREATE FUNCTION tp1 (integer, numeric) RETURNS integer AS '
UPDATE bank
SET balance = balance - $2
WHERE acctountno = $1;
SELECT 1;'
LANGUAGE 'sql';
一个用户可以象下面这样用这个函数给帐户 17 扣款 $100.00：
SELECT tp1( 17,100.0);
实际上我们可能喜欢函数有一个比常量 "1" 更有用一些的结果． 所以更有可能的定义是
CREATE FUNCTION tp1 (integer, numeric) RETURNS numeric AS '
UPDATE bank
SET balance = balance - $2
WHERE accountno = $1;
SELECT balance FROM bank WHERE accountno = $1;
' LANGUAGE SQL;
它修改余额并返回新的余额．
SQL 里面的任何命令集都可以打成一个包， 做成一个函数．这些命令可以包含数据修改(也就是说， INSERT，UPDATE， 和DELETE)以及 SELECT 查询． 不过，最后的命令必须是一条返回函数声明的返回类型的 SELECT．
CREATE FUNCTION clean_EMP () RETURNS integer AS '
DELETE FROM EMP
WHERE EMP.salary <= 0;
SELECT 1 AS ignore_this;
' LANGUAGE SQL;
SELECT clean_EMP();
x
---
1
12.2.2. 基本类型的 SQL 函数
最简单的 SQL 函数可能是不带参数，只是返回一个基本类型如 integer 的函数：
CREATE FUNCTION one()
RETURNS integer
AS 'SELECT 1 as RESULT;'
LANGUAGE 'sql';
SELECT one();
one
-----
1
注意我们给函数定义了目标列（名称为 RESULT）， 但是激活函数的查询语句的目标列覆盖了函数的目标 列．因此，结果的标记是one 而不是RESULT．
定义以基本类型为参数的 SQL 函数几乎一样简单， 注意我们在函数内如何用$1和$2使用参数：
CREATE FUNCTION add_em(integer, integer)
RETURNS integer
AS 'SELECT $1 + $2;'
LANGUAGE 'sql';
SELECT add_em(1, 2) AS answer;
+-------+
|answer |
+-------+
|3 |
+-------+
12.2.3. 复合类型的SQL函数
当我们声明的函数用复合类型做参数时， 我们不仅要声明我们需要哪个参数（像上面我们使用 $1和$2一样），而且要声明参数的字段．比如， 假设 EMP 是一个包含雇员信息的表，并且因此也是该表每行 的复合类型的名字．这里就是一个函数 double_salary，它计算你薪水翻番之后的数值：
CREATE FUNCTION double_salary(EMP) RETURNS integer AS '
SELECT $1.salary * 2 AS salary;
'LANGUAGE SQL;
SELECT name, double_salary(EMP) AS dream
FROM EMP
WHERE EMP.cubicle ~= point '(2,1)';
name | dream
------+-------
Sam | 2400
请注意这里使用 $1.salary 的语法 选择参数行数值的一个字段．还要注意SELECT命令是如何 使用一个表的名字表示该表的整个当前行作为复合数值．
我们也可以写一个返回复合类型的函数．(不过，我们下面将看到， 在这些函数的使用上有一些不幸的限制．) 下面是一个返回一行 EMP 函数的例子∶
CREATE FUNCTION new_emp() RETURNS EMP AS '
SELECT text ''None'' AS name,
1000 AS salary,
25 AS age,
point ''(2,2)'' AS cubicle'
LANGUAGE 'sql';
在这个例子中我们给每个字段都赋予了一个常量， 当然我们可以用任何计算或表达式来代替这些常量． 注意定义这样的函数的两个重要的问题∶
• 目标列表的顺序必须和与该复合类型相关的表中字段的顺序完全一样．
• 你必须对表达式进行类型转换以匹配复合类型的定义． 否则你将看到下面的错误信息：
ERROR: function declared to return emp returns varchar instead of text at column 1
在目前的 PostgreSQL 版本里有一些让人 不快的限制，这些限制约束了我们可以使用复合类型返回值的方法． 简单说，在调用一个返回一行的函数的时候，我们无法检索整行． 我们要么是把某个字段映射出该行，要么是把整行传递给另外一个函数． (试图显示整行数值将产生没有意义的数字．)比如，
SELECT name(new_emp());
name
------
None
这个例子使用了函数概念进行字段抽取．解释这些问题的简单方法是 我们通常交互使用attribute(table)和 table.attribute 的概念∶
--
-- 下面的与这句话相同∶
-- SELECT EMP.name AS youngster FROM EMP WHERE EMP.age < 30
--
SELECT name(EMP) AS youngster
FROM EMP
WHERE age(EMP) < 30;
youngster
-----------
Sam
通常，我们必须使用函数语法映射作为函数返回值的字段的原因 是在和函数调用接合在一起时，分析器不能理解用于映射的点分语法．
SELECT new_emp().name AS nobody;
ERROR: parser: parse error at or near "."
另外一个使用函数返回行结果的方法是声明另外一个函数， 该函数接受一个行类型参数，然后把函数结果传递给这个第二个函数∶
CREATE FUNCTION getname(emp) RETURNS text AS
'SELECT $1.name;'
LANGUAGE SQL;
SELECT getname(new_emp());
getname
---------
None
(1 row)
12.2.4. 返回集合的 SQL 函数
如前所述，一个 SQL 函数可以声明为返回 SETOF sometype． 这时候，该函数的最后的SELECT查询一直执行到结束，并且它 输出的每行都当做该集合的一个元素返回．
返回集合的函数只能在一个SELECT查询的目标列表中调用． 对于SELECT自己生成的每个行都会调用这个返回集合的函数， 并且为该函数的结果集的每个元素生成一个输出行．比如∶
CREATE FUNCTION listchildren(text) RETURNS SETOF text AS
'SELECT name FROM nodes WHERE parent = $1'
LANGUAGE SQL;
SELECT * FROM nodes;
name | parent
-----------+--------
Top |
Child1 | Top
Child2 | Top
Child3 | Top
SubChild1 | Child1
SubChild2 | Child1
(6 rows)
SELECT listchildren('Top');
listchildren
--------------
Child1
Child2
Child3
(3 rows)
SELECT name, listchildren(name) FROM nodes;
name | listchildren
--------+--------------
Top | Child1
Top | Child2
Top | Child3
Child1 | SubChild1
Child1 | SubChild2
(5 rows)
在最后的SELECT里，请注意没有出现Child2， Child3等的行． 这是因为listchildren 为这些输入返回一个空集合， 因此不生成任何输出行．
1161
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
