ORACLE PL/SQL ´æ´¢¹ý³Ì½Ì³Ì
(1)SEQNAME.NEXTVALÀïÃæµÄÖµÈçºÎ¶Á³öÀ´£¿¿ÉÒÔÖ±½ÓÔÚinsert into test values(SEQNAME.NEXTVAL) ÊÇ¿ÉÒÔÓà ÕâÑù£º
SELECT tmp#_seq.NEXTVAL
INTO id_temp
from DUAL; È»ºó¿ÉÒÔÓÃid_temp
(2)PLS-00103: ³öÏÖ·ûºÅ ">"ÔÚÐèÒªÏÂÁÐ֮һʱ£º
´úÂëÈçÏ£º
IF (sum>0)
THEN
begin
INSERT INTO emesp.tp_sn_production_log
VALUES (r_serial_number, , id_temp);
EXIT;
end;
Ò»Ö±±¨sum>0 ÕâÊǸöºÜÓôÃƵÄÎÊÌâ ÒòΪ±äÁ¿ÓÃÁËsum ËùÒÔ²»ÐУ¬ºó¸ÄΪi_sum>0
(3)oracle Óï·¨
1. OracleÓ¦Óñ༷½·¨¸ÅÀÀ
´ð£º1) Pro*C/C++/... : CÓïÑÔºÍÊý¾Ý¿â´ò½»µÀµÄ·½·¨£¬±ÈOCI¸ü³£ÓÃ;
2) ODBC
3) OCI: CÓïÑÔºÍÊý¾Ý¿â´ò½»µÀµÄ·½·¨£¬ºÍProCºÜÏàËÆ£¬¸üµ×²ã£¬ºÜÉÙÓÃ;
4) SQLJ: ºÜеÄÒ»ÖÖÓÃJava·ÃÎÊOracleÊý¾Ý¿âµÄ·½·¨£¬»áµÄÈ˲»¶à;
5) JDBC
6) PL/SQL: ´æ´¢ÔÚÊý¾ÝÄÚÔËÐÐ, ÆäËû·½·¨ÎªÔÚÊý¾Ý¿âÍâ¶ÔÊý¾Ý¿â·ÃÎÊ;
2. PL/SQL
´ð£º1) PL/SQL(Procedual language/SQL)ÊÇÔÚ±ê×¼SQLµÄ»ù´¡ÉÏÔö¼ÓÁ˹ý³Ì»¯´¦ÀíµÄÓïÑÔ;
2) Oracle¿Í»§¶Ë¹¤¾ß·ÃÎÊOracle·þÎñÆ÷µÄ²Ù×÷ÓïÑÔ;
3) Oracle¶ÔSQLµÄÀ©³ä;
4. PL/SQLµÄÓÅȱµã
´ð£ºÓŵ㣺
1) ½á¹¹»¯Ä£¿é»¯±à³Ì£¬²»ÊÇÃæÏò¶ÔÏó;
2) Á¼ºÃµÄ¿ÉÒÆÖ²ÐÔ(²»¹ÜOracleÔËÐÐÔÚºÎÖÖ²Ù×÷ϵͳ);
3) Á¼ºÃµÄ¿Éά»¤ÐÔ(±àÒëͨ¹ýºó´æ´¢ÔÚÊý¾Ý¿âÀï);
4) ÌáÉýϵͳÐÔÄÜ;
µÚ¶þÕÂ
PL/SQL³ÌÐò½á¹¹
1. PL/SQL¿é
´ð£º1) ÉêÃ÷²¿·Ö, DECLARE(²»¿ÉÉÙ);
2) Ö´Ðв¿·Ö, BEGIN...END;
3) Òì³£´¦Àí£¬EXCEPTION(¿ÉÒÔûÓÐ);
2. PL/SQL¿ª·¢»·¾³
´ð£º¿ÉÒÔÔËÓÃÈκδ¿Îı¾µÄ±à¼Æ÷±à¼£¬ÀýÈ磺VI &
Ïà¹ØÎĵµ£º
1¡¢²éÕÒ±íµÄËùÓÐË÷Òý£¨°üÀ¨Ë÷ÒýÃû£¬ÀàÐÍ£¬¹¹³ÉÁУ©£º
select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = Òª²éѯµÄ±í
2¡¢²éÕÒ±íµÄÖ÷¼ü£¨°üÀ¨Ãû³Æ£¬¹¹³ÉÁУ©£º
select cu.* from user_cons_columns cu, user_con ......
ʹÓÃexp¹¤¾ß£¬ÒÔtablesµÄÀàÐ͵¼³öij¸öÓû§ÏÂËùÓеıíºÍÊý¾Ý£¬·¢ÏÖÆäÖÐsequenceûÓб»µ¼³ö¡£ÍøÉÏËÑË÷Ö®£¬·¢ÏÖtoadòËÆÓд˹¦ÄÜ£¬ÓÚÊÇ°²×°ÁË9.6.1.1°æ±¾£¬½á¹û¾ÓȻû·¢Ïִ˹¦ÄÜ¡££¨¿ÉÄÜÊÇÎÒûÕÒµ½£¬ÖÁÉÙºÍÄÇλÀÏ´óµÄ½Øͼ²»Í¬£©£¬×îºóÕÒµ½ÈçϽű¾£¬¿ÉÒÔ½«Ä³¸öÓû§µÄÈ«²¿sequence²éѯ³öÀ´£¬²¢Æ´³É´´½¨Óï¾ä¡£
´úÂëÈçÏ£ ......
·ÖÎöº¯ÊýÊÇoracle816ÒýÈëµÄÒ»¸öȫеĸÅÄî,ΪÎÒÃÇ·ÖÎöÊý¾ÝÌṩÁËÒ»ÖÖ¼òµ¥¸ßЧµÄ´¦Àí·½Ê½.ÔÚ·ÖÎöº¯Êý³öÏÖÒÔÇ°,ÎÒÃDZØÐëʹÓÃ×ÔÁª²éѯ,×Ó²éѯ»òÕßÄÚÁªÊÓͼ,ÉõÖÁ¸´ÔӵĴ洢¹ý³ÌʵÏÖµÄÓï¾ä,ÏÖÔÚÖ»ÒªÒ»Ìõ¼òµ¥µÄsqlÓï¾ä¾Í¿ÉÒÔʵÏÖÁË,¶øÇÒÔÚÖ´ÐÐЧÂÊ·½ÃæÒ²ÓÐÏ൱´óµÄÌá¸ß.
ÏÂÃæÖ÷Òª½éÉÜÒ»ÏÂÒÔϼ¸¸öº¯ÊýµÄʹÓà ......
--> Title : SQL ServerÖ®·Ö²¼Ê½ÊÂÎñ
--> Author : wufeng4552
--> Date : 2009-11-11
SQL ServerÖ®·Ö²¼Ê½ÊÂÎñ
(Ò»)¸ÅÄî:
·Ö²¼Ê½ÊÂÎñÊÇÉæ¼°À´×ÔÁ½¸ö»ò¶à¸öÔ´µÄ×ÊÔ´µÄÊÂÎñ¡£Microsoft® SQL Server™ 2000Ö§³Ö·Ö²¼Ê½ÊÂÎñ£¬Ê¹Óû§µÃÒÔ´´½¨ÊÂÎñÀ´¸üжà¸öSQL ServerÊý¾Ý¿âºÍÆäËüÊý¾ÝÔ ......
1¡¢²éÕÒ±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö×ֶΣ¨peopleId£©À´ÅжÏ
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2¡¢É¾³ý±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö× ......