DB2 SQL/XMLʹÓÃ
º¯Êý£º
XMLPARSE£º½âÎö×Ö·û»òÕß´ó¶ÔÏó¶þ½øÖÆÊý¾Ý£¬²úÉúXML
XMLSERIALIZE£º½«XMLֵת»»Îª×Ö·û»òÕß´ó¶ÔÏó¶þ½øÖÆÊý¾Ý
XMLVALIDATE£º¸ù¾ÝXML schemaУÑéXMLÖµµÄÓÐЧÐÔ
XMLEXISTS£º¼ì²âXQueryÊÇ·ñ·µ»Ø½á¹û£¨ÈçÓÉÒ»¸ö»ò¶à¸öÏîËù×é³ÉµÄÐòÁУ©
XMLQUERY£ºÖ´ÐÐÒ»¸öXQuery²¢ÒÔÒ»¸öÐòÁзµ»Ø½á¹û
XMLTABLE£ºÖ´ÐÐÒ»¸öXQuery£¬ÒÔ¹ØÏµÊý¾Ý±íÐÎʽ·µ»Ø½á¹û£¨Èç¹û¿ÉÄܵϰ£©
XMLCAST£ºXMLÀàÐÍת»»
XMLEXISTS£º
SELECT name from clients
WHERE xmlexists(
'$c/Client/Address[zip="95116"]'
passing clients.contact as "c"
)
˵Ã÷£º
ÔÚWHERE×Ó¾äÖе÷ÓÃXMLEXISTSº¯Êý£¬²ÎÊýÖ¸¶¨ÁËXML·¾¶±í´ïʽ£¬DB2¾Ý´Ëµ¼º½µ½XMLÎĵµÓʱàÔªËØ²¢ÇÒ²éÕÒÖµ 95116¡£ “$c/Client/Address”Ö¸¶¨ÁËDB2ÔÚXMLÎĵµÖж¨Î»ÓʱàÔªËØµÄ·¾¶¡£ÃÀÔª·ûºÅ($)ÓÃÀ´Ö¸¶¨±äÁ¿¡£passing clients.contact as "c"¶Ô±äÁ¿c½øÐж¨Òå¡£ÕâÀ“clients”ÊDZíÃû£¬“contact”ÊÇ XMLÁеÄÁÐÃû¡£»»ÑÔÖ®£¬XMLÎĵµ´«µÝ¸øÁ˱äÁ¿c¡£ DB2¼ìË÷“contact”ÁÐÖеÄXMLÊý¾Ý£¬´Ó¸ù½Ú“client”ÏòÏ£¬Í¨¹ý½Úµã“Address”µ½½Úµã“zip”£¬¾¹ýÄ¿±êÓÊ±àÆ¥Åä¼ì²éÅжϸÿͻ§ÊÇ·ñ¾ÓסÔÚÄ¿±êµØÇø¡£
ÔÚDB2 9.5ÖнøÐÐÁ˼ò»¯£º
SELECT name from clients
WHERE xmlexists(
'$CONTACT/Client/Address[zip="95116"]'
)
XMLQuery£º
SELECT xmlquery('$c/Client/email' passing contact as "c")
from clients
WHERE status = 'Gold'
ʹÓÃXQuery FLWOR±í´ïʽÖеÄfor£º
SELECT name, xmlquery(
‘for $e in $c/Client/email[1] return $e’
passing contact as “c”
)
from clients
WHERE status = ‘Gold’
SELECT xmlquery('for $e in $c/Client/email[1]/text()
return <p>{$e}</p>'
passing contact as "c")
from clients
WHERE status = 'Gold'
XQueryµÄFLWOR±í´ïʽ FLWORÊǼ¸¸ö´ÊÊ××ÖĸµÄËõд£º
FOR£º¶ÔÐòÁнøÐеü´ú
LET£º°ó¶¨±äÁ¿
WHERE£º¶¨Òå¹ýÂËÆ÷
ORDER£º½«¹ýÂ˽á¹ûÅÅÐò
RETURN£º·µ»Ø²éѯ½á¹û
ʵÀý£º
CREATE TABLE dept(deptID CHAR(8),deptdoc XML);
½«ÈçÏÂXMLÎĵµ²åÈëµ½deptdocÁУº
Ïà¹ØÎĵµ£º
1.Ê×ÏÈÐèÒª½¨Á¢plan table,·ñÔò²»ÄÜʹÓÃ
½¨Á¢·½·¨£º
$oracle\rdbms\adminÏÂÓиö
utlxplan.sql
ÆäÄÚÈÝΪ:
create table PLAN_TABLE (
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operat ......
Ê×ÏÈÒªÌí¼Ó
using System.Data;
using System.Data.SqlClient;
½ÓÏÂÀ´£º
SqlConnection conn = new SqlConnection("server=QLPC\\SQL2005;uid=sa;pwd=£¨ÄãµÄÃÜÂ룩;database=£¨ÄãµÄÊý¾Ý±í£©"); &n ......
SQL×¢Èë¹¥»÷·À·¶¼¼ÇÉ
Ò»°ãµÄSQL×¢Èë¹¥»÷¶¼ÊÇͨ¹ý¹¹½¨Ò»Ìõ¸´ÔÓµÄsqlÓï¾ä£¬
ͨ¹ýÍøÒ³Â©¶´À´Ö´ÐÐsqlÓï¾ä£¬´ïµ½¹¥»÷Êý¾Ý¿âµÄÄ¿µÄ¡£
Èçͨ¹ýÎÄÕÂIDÀ´²éѯijһƪÎÄÕµÄÍøÒ³£¬
ͨ³£²ÉÓõÄsqlÓï¾äΪ£º
sql="select top 1 * from articles where articId="&request("id")
ÄÇô¿ÉÒÔ¼òµ ......
(1)¹ØÓÚ´æ´¢¹ý³Ì·µ»ØÖµÎÊÌ⣨output,ruturn£©
output´æ´¢¹ý³Ì£º
alter proc usp_update
@count int output
as
set @count=(select count(*) from stu)
return´æ´¢¹ý³Ì£º
alter proc usp_update
as
declare @count int
set @count=(select count(*) from stu)
r ......