SQL´æ´¢¹ý³Ì
ʾÀý
A. ʹÓôøÓи´ÔÓ SELECT Óï¾äµÄ¼òµ¥¹ý³Ì
ÏÂÃæµÄ´æ´¢¹ý³Ì´ÓËĸö±íµÄÁª½ÓÖзµ»ØËùÓÐ×÷Õߣ¨ÌṩÁËÐÕÃû£©¡¢³ö°æµÄÊé¼®ÒÔ¼°³ö°æÉç¡£¸Ã´æ´¢¹ý³Ì²»Ê¹ÓÃÈκβÎÊý¡£
USE pubs
IF EXISTS (SELECT name from sysobjects
WHERE name = 'au_info_all' AND type = 'P')
DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
from authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
GO
au_info_all ´æ´¢¹ý³Ì¿ÉÒÔͨ¹ýÒÔÏ·½·¨Ö´ÐУº
EXECUTE au_info_all
-- Or
EXEC au_info_all
Èç¹û¸Ã¹ý³ÌÊÇÅú´¦ÀíÖеĵÚÒ»ÌõÓï¾ä£¬Ôò¿ÉʹÓãº
au_info_all
B. ʹÓôøÓвÎÊýµÄ¼òµ¥¹ý³Ì
ÏÂÃæµÄ´æ´¢¹ý³Ì´ÓËĸö±íµÄÁª½ÓÖÐÖ»·µ»ØÖ¸¶¨µÄ×÷Õߣ¨ÌṩÁËÐÕÃû£©¡¢³ö°æµÄÊé¼®ÒÔ¼°³ö°æÉç¡£¸Ã´æ´¢¹ý³Ì½ÓÊÜÓë´«µÝµÄ²ÎÊý¾«È·Æ¥ÅäµÄÖµ¡£
USE pubs
IF EXISTS (SELECT name from sysobjects
WHERE name = 'au_info' AND type = 'P')
DROP PROCEDURE au_info
GO
USE pubs
GO
CREATE PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
from authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO
au_info ´æ´¢¹ý³Ì¿ÉÒÔͨ¹ýÒÔÏ·½·¨Ö´ÐУº
EXECUTE au_info 'Dull', 'Ann'
-- Or
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'
-- Or
EXEC au_info 'Dull', 'Ann'
-- Or
EXEC au_info @lastname = 'Dull', @firstname
Ïà¹ØÎĵµ£º
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'CLR', 0
GO
-- To update the currently configured value for this feature.
......
×÷Õß: cnd8, ¡¡³ö´¦:ITר¼ÒÍøÂÛ̳,¡¡ÔðÈαà¼: ³Â×Óç÷,¡¡2010-04-21 13:00 ¡¡¡¡ÏÖÔÚÐèÒªÔÚһ̨·þÎñÆ÷(A)ÉϵÄÊý¾Ý¿â(Master)Àï·ÃÎÊÁíÍâһ̨·þÎñÆ÷(B)µÄÁíÍâÁ½¸öÊý¾Ý¿â(NZ ºÍ AU)¡£AU ºÍ NZ ÊÇÍøÕ¾µÄ²¿·ÖʵʱÊý¾Ý£¬Ä¿Ç°Ä³Ò»²½²Ù×÷µÄREPLICATION·½°¸ÊÇ´Ó·þÎñÆ÷Bµ½A£¬AU / NZ µÄ±í½á¹¹Í¬Master Êý¾Ý½á¹¹»ù±¾Ò»Ö¡£¡¡¡¡ÏÖÔÚÐèÒªÔ ......
¡¡¡¡¹ØÏµÐÍÊý¾Ýͨ³£ÒԹ淶»¯ÐÎʽ±£´æ£¬¾ÍÊÇ˵ÄãÓ¦¸Ã¾¡¿ÉÄÜÉÙµØÖظ´Êý¾Ý;ͨ³£Çé¿öÏ£¬±íÓë±íÖ®¼ä½öͨ¹ý¸÷ÖÖ¼üֵʵÏÖ¹ØÁª¡£
¡¡¡¡¹ØÏµÐÍÊý¾Ýͨ³£ÒԹ淶»¯ÐÎʽ±£´æ£¬¾ÍÊÇ˵ÄãÓ¦¸Ã¾¡¿ÉÄÜÉÙµØÖظ´Êý¾Ý;ͨ³£Çé¿öÏ£¬±íÓë±íÖ®¼ä½öͨ¹ý¸÷ÖÖ¼üֵʵÏÖ¹ØÁª¡£½øÒ»²½µØ½²£¬¹æ·¶»¯µÄº¬Òå¾ÍÊÇ£ºÄã²»ÄÜÔÚÊý¾Ý¿âÖб£´æ¼ÆËãºóµÄÖµ£¬¶øÄãÖ»ÄÜÔÚ ......
µÚÒ»ÖÖ°ì·¨£ºÏÈÑ¡Öгö´íµÄÊý¾Ý¿â→Ñ¡ÖÐÒÔºóÓÒ¼üµã»÷ÊôÐԻᵯ³öÊý¾Ý¿âÊôÐÔ ¶Ô»°¿ò→Ñ¡ÖÐÊý¾Ý¿âÊôÐÔ¶Ô»°¿òÖеÄÑ¡Ïî→°ÑÑ¡ÏîÖеÄÅÅÐò¹æÔòÉèÖóÉ:Chinese_PRC_90_CI_AS→×îºóµã»÷È·¶¨¼´¿É¡£
£¨×¢Ò⣺ÔÚÑ¡ÔñÊý¾Ý¿âÊôÐÔµÄʱºò±ØÐëÈ·±£ÄãËùÐ޸ĵÄÊý¾Ý¿âδ±»Ê¹ÓòſÉÒÔÐ޸ķñÔò»áʧ°ÜµÄ£©
µÚ¶þÖÖ°ì·¨£ºÊ×ÏÈ´ò¿ªÄã ......
¡¡
¡¡¡¡1. ʹÓÃ%TYPE
¡¡¡¡ÔÚÐí¶àÇé¿öÏ£¬PL/SQL±äÁ¿¿ÉÒÔÓÃÀ´´æ´¢ÔÚÊý¾Ý¿â±íÖеÄÊý¾Ý¡£ÔÚÕâÖÖÇé¿öÏ£¬±äÁ¿Ó¦¸ÃÓµÓÐÓë±íÁÐÏàͬµÄÀàÐÍ¡£ÀýÈ磬students±íµÄfirst_nameÁеÄÀàÐÍΪVARCHAR2(20),ÎÒÃÇ¿ÉÒÔ°´ÕÕÏÂÊö·½Ê½ÉùÃ÷Ò»¸ö±äÁ¿£º
¡¡¡¡DECLARE
¡¡¡¡ v_FirstName VARCHAR2(20);
¡¡
¡¡µ«ÊÇÈç¹ûfirst_nameÁе͍Òå¸Ä±äÁ ......