PL/SQLµ¥Ðк¯ÊýºÍ×麯ÊýÏê½â
¡¡ º¯ÊýÊÇÒ»ÖÖÓÐÁã¸ö»ò¶à¸ö²ÎÊý²¢ÇÒÓÐÒ»¸ö·µ»ØÖµµÄ³ÌÐò¡£ÔÚSQLÖÐOracleÄÚ½¨ÁËһϵÁк¯Êý£¬ÕâЩº¯Êý¶¼¿É±»³ÆΪSQL»òPL/SQLÓï¾ä£¬º¯ÊýÖ÷Òª·ÖΪÁ½´óÀࣺ
¡¡¡¡ µ¥Ðк¯Êý ×麯Êý
¡¡¡¡SQLÖеĵ¥Ðк¯Êý
¡¡¡¡SQLºÍPL/SQLÖÐ×Ô´øºÜ¶àÀàÐ͵ĺ¯Êý£¬ÓÐ×Ö·û¡¢Êý×Ö¡¢ÈÕÆÚ¡¢×ª»»¡¢ºÍ»ìºÏÐ͵ȶàÖÖº¯ÊýÓÃÓÚ´¦Àíµ¥ÐÐÊý¾Ý£¬Òò´ËÕâЩ¶¼¿É±»Í³³ÆΪµ¥Ðк¯Êý¡£ÕâЩº¯Êý¾ù¿ÉÓÃÓÚSELECT,WHERE¡¢ORDER BYµÈ×Ó¾äÖУ¬ÀýÈçÏÂÃæµÄÀý×ÓÖоͰüº¬ÁËTO_CHAR,UPPER,SOUNDEXµÈµ¥Ðк¯Êý¡£
SELECT ename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')from empWhere UPPER(ename) Like 'AL%'ORDER BY SOUNDEX(ename)
¡¡¡¡µ¥Ðк¯ÊýÒ²¿ÉÒÔÔÚÆäËûÓï¾äÖÐʹÓã¬ÈçupdateµÄSET×Ӿ䣬INSERTµÄVALUES×Ӿ䣬DELETµÄWHERE×Ó¾ä,ÈÏÖ¤¿¼ÊÔÌرð×¢ÒâÔÚSELECTÓï¾äÖÐʹÓÃÕâЩº¯Êý£¬ËùÒÔÎÒÃǵÄ×¢ÒâÁ¦Ò²¼¯ÖÐÔÚSELECTÓï¾äÖС£
¡¡¡¡NULLºÍµ¥Ðк¯Êý
¡¡¡¡ÔÚÈçºÎÀí½âNULLÉÏ¿ªÊ¼ÊǺÜÀ§Äѵģ¬¾ÍËãÊÇÒ»¸öºÜÓоÑéµÄÈËÒÀÈ»¶Ô´Ë¸Ðµ½À§»ó¡£NULLÖµ±íʾһ¸öδ֪Êý¾Ý»òÕßÒ»¸ö¿ÕÖµ£¬ËãÊõ²Ù×÷·ûµÄÈκÎÒ»¸ö²Ù×÷ÊýΪNULLÖµ£¬½á¹û¾ùΪÌá¸öNULLÖµ,Õ ......
1.²éѯÊý¾Ý¿âÖеıíÊÇ·ñ´æÔÚ£º
sqlÓï¾ä£º
select count(1) from sysobjects where object_name(id)='±íÃû'
»ò£º
select count(*) from sysobjects where id = object_id(N'[±íÃû]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
´æ´¢¹ý³Ì£º
IF Exists(Select 1 from sysObjects Where Name ='±íÃû' And Type In ('S','U'))
Print 'Exists Table'
Else
Print 'Not Exists Table'
ת£º
µÚÒ»ÖÖ·½·¨£ºÅжÏϵͳ¶ÔÏóÊÇ·ñ´æÔÚ
DECLARE
@DbTableFileName VARCHAR(100)
SET @DbTableFileName = 'tx'
IF objectproperty(object_id(@DbTableFileName),'IsUserTable') IS NOT NULL
PRINT 'EXISTS '
ELSE
PRINT 'NOT EXISTS '
IF object_id(@DbTableFileName) IS NOT NULL
PRINT 'EXISTS '
ELSE
PRINT 'NOT EXISTS '
IF EXISTS (SELECT Object_id(@DbTableFileName))
PRINT 'EXISTS '
ELSE
PRINT 'NOT EXISTS '
µÚ¶þÖÖ·½·¨£ºÍ¨¹ý²éѯϵͳ±í
DECLARE
@DbTableFileName VARCHAR(100)
SET @DbTab ......
declare @tmp_table table(tempvalue varchar(100) null)
insert into @tmp_table
select optname from dbo.MSreplication_options
select * from @tmp_table
alter table testTable
(
decimal_field decimal null,
datetime_field datetime null,
money_field money null,
numeric_field numeric null,
binary_field binary null,
sql_variant_field sql_variant null
)
alter table testTable
alter column decimal_field decimal(4,2)
insert into testTable(decimal_field) values(99.999)
insert into testTable(datetime_field) values('12/07/1998')
insert into testTable(datetime_field) values('12/07/1998')
insert into testTable(datetime_field) values(12/07/1998)
insert into testTable(datetime_field) values(12/07/1998)
insert into testTable(money_field) values(99999999999999.12345)
insert into testTable(money_field) values($999999.12345)
insert into testTable(money_field) values(cast('$999,999.12345' as money) ......
´´½¨Êý¾Ý¿â
create DataBase database_name
[on [primary]
[/<filespec/>[1,.......n]]
[,<filegroup>[1,.......n]]
]
[LOG ON {<filespec>[1,.......n]} ]
[FOR LOAD|FOR ATTACH]
<filespec>::=([
[NAME=logical_filename,]
FILENAME='os_file_name'
[,S ......
1.×î»ù±¾µÄDBMS_OUTPUT.PUT_LINE()·½·¨¡£
¡¡¡¡Ëæ±ãÔÚʲôµØ·½£¬Ö»ÒªÊÇBEGINºÍENDÖ®¼ä£¬¾Í¿ÉÒÔʹÓÃDBMS_OUTPUT.PUT_LINE(output);È»¶øÕâ»áÓÐÒ»¸öÎÊÌ⣬¾ÍÊÇʹÓøú¯ÊýÒ»´Î×î¶àÖ»¿ÉÒÔÏÔʾ255¸ö×Ö·û£¬·ñÔò»º³åÇø»áÒç³ö¡£
¡¡¡¡´ËÍ⣬º¯ÊýDBMS_OUTPUT.ENABLE(20000)ÕâÖÖº¯Êý£¬½ö½öÊÇÉèÖÃÕû¸ö¹ý³ÌµÄÈ«²¿Êä³ö»º³åÇø´óС£¬¶ø·ÇDBMS_OUTPUT.PUT_LINE()µÄ»º³åÇø´óС¡£
¡¡¡¡¶ÔÓÚ³¬¹ý255¸ö×Ö·ûµÄ±äÁ¿£¬Ê¹ÓÃDBMS_OUTPUT.PUT_LINE()·½·¨ÊÇûÓÐЧµÄ¡£¾Ý˵ÔÚOracle10ÖУ¬½â³ýÁËÕâ¸öÏÞÖÆ£¬¿ÉÒÔÊä³öÈÎÒâ´óСµÄ×Ö·û´®¡£
declare
¡¡¡¡output varchar2(200);
¡¡¡¡begin
¡¡¡¡output:='...'; //¸³Öµ
¡¡¡¡DBMS_OUTPUT.PUT_LINE(output);
¡¡¡¡end;
¡¡¡¡2.ʹÓñíµÄ·½·¨¡£
¡¡¡¡Ê×ÏȽ¨Á¢Ò»ÕÅ±í£¬È»ºóÔÚPL/SQLÖн«ÒªÊä³öµÄÄÚÈݲ嵽±íÖС£È»ºóÔÙ´Ó±íÖв鿴ÄÚÈÝ¡£¶ÔÓÚÕâÖÖ·½·¨Ò»´Î¿ÉÒÔÊä³ö¼¸Ç§¸ö×Ö·û¡£
(1) create table my_output{
id number(4),
txt varchar2(4000)
);
(2) declare
output varch ......
1.×î»ù±¾µÄDBMS_OUTPUT.PUT_LINE()·½·¨¡£
¡¡¡¡Ëæ±ãÔÚʲôµØ·½£¬Ö»ÒªÊÇBEGINºÍENDÖ®¼ä£¬¾Í¿ÉÒÔʹÓÃDBMS_OUTPUT.PUT_LINE(output);È»¶øÕâ»áÓÐÒ»¸öÎÊÌ⣬¾ÍÊÇʹÓøú¯ÊýÒ»´Î×î¶àÖ»¿ÉÒÔÏÔʾ255¸ö×Ö·û£¬·ñÔò»º³åÇø»áÒç³ö¡£
¡¡¡¡´ËÍ⣬º¯ÊýDBMS_OUTPUT.ENABLE(20000)ÕâÖÖº¯Êý£¬½ö½öÊÇÉèÖÃÕû¸ö¹ý³ÌµÄÈ«²¿Êä³ö»º³åÇø´óС£¬¶ø·ÇDBMS_OUTPUT.PUT_LINE()µÄ»º³åÇø´óС¡£
¡¡¡¡¶ÔÓÚ³¬¹ý255¸ö×Ö·ûµÄ±äÁ¿£¬Ê¹ÓÃDBMS_OUTPUT.PUT_LINE()·½·¨ÊÇûÓÐЧµÄ¡£¾Ý˵ÔÚOracle10ÖУ¬½â³ýÁËÕâ¸öÏÞÖÆ£¬¿ÉÒÔÊä³öÈÎÒâ´óСµÄ×Ö·û´®¡£
declare
¡¡¡¡output varchar2(200);
¡¡¡¡begin
¡¡¡¡output:='...'; //¸³Öµ
¡¡¡¡DBMS_OUTPUT.PUT_LINE(output);
¡¡¡¡end;
¡¡¡¡2.ʹÓñíµÄ·½·¨¡£
¡¡¡¡Ê×ÏȽ¨Á¢Ò»ÕÅ±í£¬È»ºóÔÚPL/SQLÖн«ÒªÊä³öµÄÄÚÈݲ嵽±íÖС£È»ºóÔÙ´Ó±íÖв鿴ÄÚÈÝ¡£¶ÔÓÚÕâÖÖ·½·¨Ò»´Î¿ÉÒÔÊä³ö¼¸Ç§¸ö×Ö·û¡£
(1) create table my_output{
id number(4),
txt varchar2(4000)
);
(2) declare
output varch ......
SQL ServerµÄ²¹¶¡°æ±¾¼ì²é²»ÈçWindows ²¹¶¡°æ±¾¼ì²éÖ±½Ó£¬Ò»¸öϵͳ¹ÜÀíÔ±£¬Èç¹û²»Á˽âSQL Server°æ±¾¶ÔÓ¦µÄ²¹¶¡ºÅ£¬¿ÉÄÜÒ²»áÓöµ½Ò»µãÂé·³£¬Òò´ËÔÚÕâ˵Ã÷һϣ¬Í¨¹ýÕâÑùµÄ°ì·¨Åбð»úÆ÷ÊÇ°²È«µÄ°ì·¨£¬²»»á¶Ôϵͳ²úÉúÈκÎÓ°Ïì¡£¡¡
1¡¢ÓÃIsql»òÕßSQL²éѯ·ÖÎöÆ÷µÇ¼µ½SQL Server£¬Èç¹ûÊÇÓÃIsql£¬ÇëÔÚcmd´°¿ÚÊäÈëisql -U sa,È»ºóÊäÈëÃÜÂ룬½øÈ룻Èç¹ûÊÇÓÃSQL²éѯ·ÖÎöÆ÷£¬Çë´Ó³ÌÐòÖÐÆô¶¯£¬ÊäÈësaºÍÃÜÂ루Ҳ¿ÉÒÔÓÃwindowsÑéÖ¤£©¡£
2¡¢ÔÚISQLÖÐÊäÈ룺
Select @@Version£»
go
»òÕßSQL²éѯ·ÖÎöÆ÷ÖÐÊäÈë(ÆäʵÈç¹û²»ÏëÊäÈ룬ֻҪ´ò¿ª°ïÖúµÄ¹ØÓھͿÉÒÔÁË)
Select @@Version£»
È»ºó°´Ö´ÐÐ,Õâʱ»á·µ»ØSQLµÄ°æ±¾ÐÅÏ¢£¬ÈçÏ£º
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
ÆäÖеÄ8.00.760¾ÍÊÇSQL ServerµÄ°æ±¾ºÍ²¹¶¡ºÅ¡£¶ÔÓ¦¹ØϵÈçÏ£º
8.00.194 £——————SQL Server 2000 RTM
8.00 ......