MS SQL ServerÁ¬½ÓOracle
1. °²×°oracle 10G¿Í»§¶Ë
Òª·ÃÎÊoracleÊý¾Ý¿â±ØÐë°²×°oracle¿Í»§¶Ë¡£°²×°oracle¿Í»§¶ËÓÐÁ½ÖÖ·½Ê½£¬¼´ÍêÕû°²×°ºÍÁ¢¼´°²×°£¬ÕâÁ½ÖÖ·½Ê½¶¼¿ÉÒÔ¡£°²×°ÍêºóÅäÖÃÒªÁ´½ÓÊý¾Ý¿â·þÎñÆ÷¡£
$ORACLE_HOME\network\admin\tnsname.ora
ÔÚ´ËÅäÖÃÎļþÀïÌí¼ÓÈçÏÂÄÚÈÝ£º
sunora =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = sunora)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mis)
)
)
2. ´´½¨Á´½Ó
´´½¨Á´½ÓÓÐÁ½ÖÖ·½Ê½£º
µÚÒ»ÖÖ·½Ê½£ºsqlÓï¾ä´´½¨
´ò¿ªsqlserver manager studio£¬Ð½¨²éѯ´°¿Ú£¬ÔÚ´°¿ÚÖÐÖ´ÐÐÏÂÁÐÓï¾ä£º
sp_addlinkedserver ‘sei’, ‘Oracle’, ‘MSDAORA’, ‘seidb’
sp_addlinkedsrvlogin 'seidb', false, 'sa', 'poweronhr', 'p123456'
Ö´ÐÐÍêºó´´½¨Á´½ÓÍê³É¡£
µÚ¶þÖÖ·½Ê½£ºÍ¼ÐνçÃæ´´½¨
ÔÚн¨Á´½Ó·þÎñÆ÷´°¿ÚàÑ¡Ôñҳೣ¹æÒ³ÀïÊäÈëÈçÏÂÄÚÈÝ£¬¾ßÌå²Î¿´ÏÂͼ
ÔÚн¨Á´½Ó·þÎñÆ÷´°¿ÚàÑ¡ÔñҳలȫÐÔÒ³Ìí¼ÓµÇ¼ӳÉ䣬ÈçÏÂͼ
±¾µØµÇ¼£ºµÇ¼sql serverÊý¾Ý¿âµÄÓû§Ãû£»
Ô¶³ÌÓû§£ºµÇ¼oracleÊý¾Ý¿âµÄÓû§Ãû£»
Ô¶³ÌÃÜÂ룺µÇ¼oracleÊý¾Ý¿âµÄÃÜÂ룻
ÉÏÊö²Ù×÷Íê³Éºóµã»÷È·ÈÏÍê³É´´½¨Á´½Ó¡£
3. ²âÊÔ
ÔÚsql server¹ÜÀíÆ÷ÖпÉÒԲ鿴ÄãËù´´½¨µÄoracleÊý¾Ý¿âÁ´½Ó²¢²é¿´ÀïÃæµÄÊý¾Ý±í¼°Êý¾Ý£º
SqlÓï¾ä¸ñʽ£º
Select * from openquery(SEI,’select * from poweronhr.department’)
¸½¼ÓÉÏÎÒÅäÖõÄÊý¾ÝÔ´µÄ½Øͼ
Ïà¹ØÎĵµ£º
Êý¾Ý×Öµädict×ÜÊÇÊôÓÚOracleÓû§sysµÄ¡£
¡¡¡¡1¡¢Óû§£º
¡¡¡¡¡¡select username from dba_users;
¡¡¡¡¸Ä¿ÚÁî
¡¡¡¡¡¡alter user spgroup identified by spgtest;
¡¡¡¡2¡¢±í¿Õ¼ä£º
¡¡¡¡¡¡select * from dba_data_files;
¡¡¡¡¡¡select * from dba_tablespaces;//±í¿Õ¼ä
¡¡¡¡¡¡select tablespace_name,sum(bytes), sum(b ......
DECLARE @HDOC INT --Îĵµ¾ä±ú
DECLARE @XMLSTRING VARCHAR(200) --XML×Ö·û´®
SET @xmlString ='<?xml version="1.0"?>
<ROOT>
<USER ID="1" Name="SBQCEL"/>
<USER ID="2" Name="PEACELI"/>
<USER ID="3" Name="SHEEPCHANG"/>
</ROOT>'
--ʹÓÃϵͳ´æ´¢ ......
Ò»¡¢»ù´¡
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE DATABASE database-name
2¡¢ËµÃ÷£ºÉ¾³ýÊý¾Ý¿â
drop database dbname
3¡¢ËµÃ÷£º±¸·Ýsql server
--- ´´½¨ ±¸·ÝÊý¾ÝµÄ device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- ¿ªÊ¼ ±¸·Ý
BACKUP DATABASE pubs TO testBack
4¡¢Ëµ ......
SQLÓï¾äÖеÄÈý¸ö¹Ø¼ü×Ö:MINUS(¼õÈ¥),INTERSECT(½»¼¯)ºÍUNION ALL(²¢¼¯);
¹ØÓÚ¼¯ºÏµÄ¸ÅÄî,ÖÐѧ¶¼Ó¦¸Ãѧ¹ý,¾Í²»¶à˵ÁË.ÕâÈý¸ö¹Ø¼ü×ÖÖ÷ÒªÊǶÔÊý¾Ý¿âµÄ²éѯ½á¹û½øÐвÙ×÷,ÕýÈçÆäÖÐÎĺ¬ÒåÒ»Ñù:Á½¸ö²éѯ,MINUSÊÇ´ÓµÚÒ»¸ö²éѯ½á¹û¼õÈ¥µÚ¶þ¸ö²éѯ½á¹û,Èç¹ûÓÐÏཻ²¿·Ö¾Í¼õÈ¥Ïཻ²¿·Ö;·ñÔòºÍµÚÒ»¸ö²éѯ½á¹ûûÓÐÇø±ð. INTERSECTÊÇÁ½¸ö² ......
ÔÚSQL Server2005ÖÐÓÐFOR XML Ó÷¨£¬¿ÉÒÔ½²Ò»¸ö±í×÷Ϊһ¸ö×ֶΡ£
ÎÒµÄÉè¼ÆÏë·¨Ö÷ÒªÊÇÓÃÔÚ1¶Ô¶àµÄ¹ØϵÖбí¶ÁÈ¡µÄÎÊÌâ¡£
±íA ±íB
ÔÚAÖÐÓÐÒ»Ìõ¼Ç¼¶øÔÚBÖÐÓжàÌõ¼Ç¼¿ÉÒÔ²ÎÕÕÏÂÃæµÄд·¨£º£¨ÎÒµÄÏîÄ¿ÖÐÓõ½µÄ£¬ÐÞ¸ÄÁË×Ö¶ÎÖ÷ÒªÊÇÑÝʾÓã©
select A.*,
(SELECT a, CAST(G_Univalence AS NVARCHAR(48)) AS G_Univa ......