OracleÖУ¬ÓÃÒ»ÌõSqlʵÏÖÈÎÒâµÄÐÐתÁÐÆ´½Ó ¶àÐÐÆ´½Ó
±í½á¹¹ºÍÊý¾ÝÈçÏ£¨±íÃûTest£©£º
NO VALUE NAME
1 a ²âÊÔ1
1 b ²âÊÔ2
1 c ²âÊÔ3
1 d ²âÊÔ4
2 e ²âÊÔ5
4 f ²âÊÔ6
4 g ²âÊÔ7
SqlÓï¾ä£º
select No,
ltrim(max(sys_connect_by_path(Value, ';')), ';') as Value,
ltrim(max(sys_connect_by_path(Name, ';')), ';') as Name
from (select No,
Value,
Name,
rnFirst,
lead(rnFirst) over(partition by No order by rnFirst) rnNext
from (select a.No,
a.Value,
a.Name,
row_number() over(order by a.No, a.Value desc) rnFirst
from Test a) tmpTable1) tmpTable2
&n
Ïà¹ØÎĵµ£º
ÓÐÈË×ܽáÁËÊý¾Ý¿âÖÐNullÖµµÄ¸ÅÄ±È½Ï¾«Á¶£¬ÕªÂ¼ÈçÏ£º
NullÊÇÊý¾Ý¿âÖÐÌØÓеÄÊý¾ÝÀàÐÍ£¬µ±Ò»Ìõ¼Ç¼µÄij¸öÁÐΪNull£¬Ôò±íʾÕâ¸öÁеÄÖµÊÇδ֪µÄ¡¢ÊDz»È·¶¨µÄ¡£¼ÈÈ»ÊÇδ֪µÄ£¬¾ÍÓÐÎÞÊýÖֵĿÉÄÜÐÔ¡£Òò´Ë£¬Null²¢²»ÊÇÒ»¸öÈ·¶¨µÄÖµ¡£
ÕâÊÇNullµÄÓÉÀ´¡¢Ò²ÊÇNullµÄ»ù´¡£¬ËùÓк ......
ÔÚOracle8i»òÒÔÉÏ°æ±¾ÖУ¬¿ÉÒÔ´´½¨ÒÔÏÂÁ½ÖÖÁÙʱ±í£º
1¡£»á»°ÌØÓеÄÁÙʱ±í
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification>)
ON COMMIT PRESERVE ROWS£»
2¡£ÊÂÎñÌØÓеÄÁÙʱ±í
CREATE GL ......
Oracle ´æ´¢¹ý³Ì·µ»Ø½á¹û¼¯Óà ref cursor ʵÏÖ¡£
ÊÔÑé²½ÖèÈçÏ£º
1. ½¨Á¢ ref cursor ÀàÐͺ͹ý³Ì
CREATE OR REPLACE PACKAGE types
AS
TYPE ref_cursor IS REF CURSOR;
END;
/
CREATE TABLE STOCK_PRICES(
RIC VARCHAR(6) PRIMARY KEY,
PRICE NUMBER(7 ......
µÚÒ»ÖÖ·½·¨: ʹÓÃoutput²ÎÊý
USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.usp_GetList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.usp_GetList;
GO
CREATE PROCEDURE Production.usp_GetList @product varchar(40)
, @maxprice money
, ......
¡¡1¡¢varchar(max)¡¢nvarchar(max)ºÍvarbinary(max)Êý¾ÝÀàÐÍ×î¶à¿ÉÒÔ±£´æ2GBµÄÊý¾Ý£¬¿ÉÒÔÈ¡´útext¡¢ntext»òimageÊý¾ÝÀàÐÍ¡£
CREATE TABLE myTable
(
id INT,
content VARCHAR(MAX)
)
¡¡¡¡2¡¢XMLÊý¾ÝÀàÐÍ
¡¡¡¡XMLÊý¾ÝÀàÐÍÔÊÐíÓû§ÔÚSQL ServerÊý¾Ý¿âÖб£´æXMLƬ¶Î»òÎĵµ¡£
¡¡¡¡´íÎó´¦Àí Error Handling
¡ ......