MySQL (C API)VC实例及代码下载 （1）(4)

11.1. 行列转换--普通

假设有张学生成绩表(CJ)如下

Name Subject Result

张三 语文 80

张三 数学 90

张三 物理 85

李四 语文 85

李四 数学 92

李四 物理 82

想变成

姓名 语文 数学 物理

张三 80 90 85

李四 85 92 82

declare @sql varchar(4000)

set @sql = 'select Name'

select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'

from (select distinct Subject from CJ) as a

select @sql = @sql+' from test group by name'

exec(@sql)

11.2. 行列转换--合并

有表A,

id pid

1 1

1 2

1 3

2 1

2 2

3 1

如何化成表B:

id pid

1 1,2,3

2 1,2

3 1

创建一个合并的函数

create function fmerg(@id int)

returns varchar(8000)

as

begin

declare @str varchar(8000)

set @str=''

11.4. 通过SQL语句来更改用户的密码

修改别人的,需要sysadmin role

EXEC sp_password NULL, 'newpassword', 'User'

如果帐号为SA执行EXEC sp_password NULL, 'newpassword', sa

11.5. 怎么判断出一个表的哪些字段不允许为空？

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE='NO' and TABLE_NAME=tablename

11.6. 如何在数据库里找到含有相同字段的表？

a. 查已知列名的情况

SELECT b.name as TableName,a.name as columnname

From syscolumns a INNER JOIN sysobjects b

ON a.id=b.id

AND b.type='U'

AND a.name='你的字段名字'

b. 未知列名查所有在不同表出现过的列名

Select o.name As tablename,s1.name As columnname

From syscolumns s1, sysobjects o

Where s1.id = o.id

And o.type = 'U'

And Exists (

Select 1 From syscolumns s2

Where s1.name = s2.name

And s1.id <> s2.id

)

11.7. 查询第xxx行数据

假设id是主键：

select *

from (select top xxx * from yourtable) aa

where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id)

如果使用游标也是可以的

fetch absolute [number] from [cursor_name]
数据库操作的死锁是不可避免的，本文并不打算讨论死锁如何产生，重点在于解决死锁，通过SQL Server 2005, 现在似乎有了一种新的解决办法。
将下面的SQL语句放在两个不同的连接里面，并且在5秒内同时执行，将会发生死锁。
use Northwind
begin tran
insert into Orders(CustomerId) values('ALFKI')
waitfor delay '00:00:05'
select * from Orders where CustomerId = 'ALFKI'
commit
print 'end tran'
SQL Server对付死锁的办法是牺牲掉其中的一个，抛出异常，并且回滚事务。在SQL Server 2000，语句一旦发生异常，T-SQL将不会继续运行，上面被牺牲的连接中， print 'end tran'语句将不会被运行，所以我们很难在SQL Server 2000的T-SQL中对死锁进行进一步的处理。
现在不同了，SQL Server 2005可以在T-SQL中对异常进行捕获，这样就给我们提供了一条处理死锁的途径：
下面利用的try ... catch来解决死锁。
SET XACT_ABORT ON
declare @r int
set @r = 1
while @r <= 3
begin
begin tran

begin try
insert into Orders(CustomerId) values('ALFKI')
waitfor delay '00:00:05'
select * from Orders where CustomerId = 'ALFKI'

commit
break
end try

begin catch
rollback
waitfor delay '00:00:03'
set @r = @r 1
continue
end catch
end
解决方法当然就是重试，但捕获错误是前提。rollback后面的waitfor不可少，发生冲突后需要等待一段时间，@retry数目可以调整以应付不同的要求。
但是现在又面临一个新的问题: 错误被掩盖了，一但问题发生并且超过3次，异常却不会被抛出。SQL Server 2005 有一个RaiseError语句，可以抛出异常，但却不能直接抛出原来的异常，所以需要重新定义发生的错误，现在，解决方案变成了这样:
declare @r int
set @r = 1
while @r <= 3
begin
begin tran

begin try
insert into Orders(CustomerId) values('ALFKI')
waitfor delay '00:00:05'
select * from Orders where CustomerId = 'ALFKI'
创建通知处理程序
　　现在，您可以创建一个通知处理程序，它将借助于上面介绍的 sendNotification
过程向客户端发送更改通知。来看一看“清单 2”中的 PL/SQL 过程 orders_nf_callback。
　　清单 2.
处理对 OE.ORDERS 表所做更改的通知的通知处理程序

CREATE OR REPLACE PROCEDURE orders_nf_callback (ntfnds IN SYS.CHNF$_DESC) IS 
tblname VARCHAR2(60); 
numtables NUMBER; 
event_type NUMBER; 
row_id VARCHAR2(20); 
numrows NUMBER; 
ord_id VARCHAR2(12); 
url VARCHAR2(256) := 'http://webserverhost/phpcache/dropResults.php?order_no='; 
BEGIN 
event_type := ntfnds.event_type; 
numtables := ntfnds.numtables; 
IF (event_type = DBMS_CHANGE_NOTIFICATION.EVENT_OBJCHANGE) THEN 
FOR i IN 1..numtables LOOP 
tblname := ntfnds.table_desc_array(i).table_name; 
IF (bitand(ntfnds.table_desc_array(i).opflags,  
DBMS_CHANGE_NOTIFICATION.ALL_ROWS) = 0) THEN 
numrows := ntfnds.table_desc_array(i).numrows; 
ELSE 
numrows :=0; 
END IF; 
IF (tblname = 'OE.ORDERS') THEN 
FOR j IN 1..numrows LOOP 
row_id := ntfnds.table_desc_array(i).row_desc_array(j).row_id; 
SELECT order_id INTO ord_id FROM orders WHERE rowid = row_id; 
sendNotification(url, tblname, ord_id);  
END LOOP; 
END IF; 
END LOOP; 
END IF; 
COMMIT; 
END; 
/ 

　　如“清单 2”所示，此通知处理程序将 SYS.CHNF$_DESC
对象用作参数，然后使用它的属性获取该更改的详细信息。在该示例中，此通知处理程序将只处理数据库为响应对注册对象进行的 DML 或 DDL
更改（也就是说，仅当通知类型为 EVENT_OBJCHANGE
时）而发布的通知，并忽略有关其他数据库事件（如实例启动或实例关闭）的通知。从以上版本开始，处理程序可以处理针对 OE.ORDERS
表中每个受影响的行发出的更改通知。在本文后面的“将表添加到现有注册”部分中，您将向处理程序中添加几行代码，以便它可以处理针对 OE.ORDER_ITEMS
表中被修改的行发出的通知。
将表添加到现有注册
　　前一部分介绍了如何使用更改通知服务使数据库在注册对象（在以上示例中为 ORDERS
表）发生更改时发出通知。但从性能角度而言，客户端应用程序可能更希望缓存 ORDER_ITEMS 表而非 ORDERS
表本身的查询结果集，这是因为它在每次访问订单时，不得不从 ORDERS 表中只检索一行，但同时必须从 ORDER_ITEMS
表中检索多个行。在实际情况中，订单可能包含数十个甚至数百个订单项。
　　由于您已经对 ORDERS 表注册了查询，因此不必再创建一个注册来注册对
ORDER_ITEMS 表的查询了。相反，您可以使用现有注册。为此，您首先需要检索现有注册的 ID。可以执行以下查询来完成此工作：

SELECT regid, table_name FROM user_change_notification_regs;

　　结果可能如下所示：

REGID TABLE_NAME 
----- -------------- 
241 OE.ORDERS 

　　获取注册 ID 后，可以使用 DBMS_CHANGE_NOTIFICATION.ENABLE_REG
函数将一个新对象添加到该注册，如下所示：

DECLARE 
ord_id NUMBER; 
BEGIN 
DBMS_CHANGE_NOTIFICATION.ENABLE_REG(241); 
SELECT order_id INTO ord_id FROM order_items WHERE ROWNUM ＜ 2; 
DBMS_CHANGE_NOTIFICATION.REG_END; 
END; 
/ 

　　完成了！从现在开始，数据库将生成一个通知来响应对 ORDERS 和 ORDER_ITEMS 所做的任何更改，并调用
orders_nf_callback 过程来处理通知。因此，下一步就是编辑 orders_nf_callback，以便它可以处理因对 ORDER_ITEMS
表执行 DML 操作而生成的通知。但在重新创建 orders_nf_callback 过程之前，您需要创建以下将在更新过程中引用的表类型：

CREATE TYPE rdesc_tab AS TABLE OF SYS.CHNF$_RDESC;

　　然后，返回清单
2，在以下代码行之后：

IF (tblname = 'OE.ORDERS') THEN 
FOR j IN 1..numrows LOOP 
row_id := ntfnds.table_desc_array(i).row_desc_array(j).row_id; 
SELECT order_id INTO ord_id FROM orders WHERE rowid = row_id; 
sendNotification(url, tblname, ord_id);  
END LOOP; 
END IF; 

　　插入以下代码：

IF (tblname = 'OE.ORDER_ITEMS') THEN