Oracle ϵÁУºPL/SQL±í
Oracle ϵÁУº PL/SQL±í
Ò»£¬Ê²Ã´ÊÇPL/SQL±í£¿
Ê×ÏÈPL/SQL±íºÍ¼Ç¼(Record)Ò»Ñù£¬¶¼ÊǸ´ºÏÊý¾ÝÀàÐÍ¡£¿ÉÒÔ¿´×öÊÇÒ»ÖÖÓû§×Ô¶¨ÒåÊý¾ÝÀàÐÍ¡£
PL/SQL±íÓɶàÁе¥ÐеıêÁ¿¹¹³ÉµÄÁÙʱË÷Òý±í¶ÔÏó¡£×é³ÉÀàËÆÓÚһάÊý×é¡£
Çø±ðºÍÁªÏµ¼Ç¼(Record)µÄµ¥ÐжàÁкÍÎïÀí´æ´¢Êý¾ÝµÄ±í¡£
Record + PL/SQL±í¿ÉÒÔ½øÐÐÊý¾ÝµÄ¶àÐжàÁд洢¡£
ÉúÃüÖÜÆÚÔÚ³ÌÐò¿éÄÚ¡£
¶þ£¬PL/SQL±íµÄ×é³É½á¹¹
PL/SQL±íÖ»ÓÐÁ½ÁÐ,ÆäÖеÚÒ»ÁÐΪË÷ÒýÁÐΪIntegerÀàÐÍË÷ÒýÁУ¬9iºó¿ÉÒÔÖ§³Ö×Ö·ûË÷Òý¡£µÚ¶þÁÐΪÓû§×Ô¶¨ÒåÁУ¬ÉùÃ÷Óû§¿ÉÓÃÁÐÀàÐÍ.
ͨ¹ýË÷ÒýÁÐÖеÄË÷ÒýÖµÀ´²Ù×÷PL/SQL±íÖжÔÓ¦µÄÓû§×Ô¶¨ÒåÁС£ÀàËÆÓÚ¼üÖµ¶Ô¡£
È·±£Ë÷ÒýÖµµÄΨһÐÔ¡£Èç¹ûË÷ÒýÖµÖØ¸´²»»á±¨´í£¬»á¸²¸ÇÇ°ÃæµÄÖµ¡£
Èý£¬ÈçºÎ´´½¨ºÍʹÓÃPL/SQL±í£¿
¢ÙÉùÃ÷PL/SQL±íÀàÐͶÔÏó
Óï·¨£º
TYPE PL/SQL±íÃû IS TABLE OF ¿ÉÓÃÁÐÀàÐÍ [NOT NULL]
INDEX BY BINARY_INTEGER;
¿ÉÓÃÁÐÀàÐÍ¿ÉÒÔΪOracleµÄÊý¾ÝÀàÀàÐÍÒÔ¼°Óû§×Ô¶¨ÒåÀàÐÍ;
ÊôÐÔ·½·¨:
count --·µ»Øpl/sql±íµÄ×ÜÐÐÊý£»
delect --ɾ³ýpl/sql±íµÄËùÓÐÄÚÈÝ£»
delect(ÐÐÊý) --ɾ³ýpl/sql±íµÄÖ¸¶¨µÄÐУ»
delct(¿ªÊ¼ÐУ¬½áÊøÐÐ) --ɾ³ýpl/sql±íµÄ¶àÐУ»
first --·µ»Ø±íµÄµÚÒ»¸öINDEX;
next(ÐÐÊý) --Õâ¸öÐÐÊýµÄÏÂÒ»ÌõµÄINDEX;
last --·µ»Ø±íµÄ×îºóÒ»¸öINDEX;
¢ÚÉùÃ÷PL/SQL±íÀàÐͱäÁ¿£º
Óï·¨£º
PL/SQL±íÀàÐͱäÁ¿Ãû PL/SQL±íÀàÐÍ£»
¢ÛÊý¾ÝÌî³äºÍ·ÃÎÊ
Óï·¨£º
PL/SQL±íÀàÐͱäÁ¿Ãû(Ë÷ÒýÁÐÖµ) := Ìî³äÉùÃ÷ÀàÐÍÖµ£»
PL/SQL±íÀàÐͱäÁ¿Ãû.ÊôÐÔ·½·¨Ãû£»
Àý×Ó£º
Declare
Type MyTabType Is Table Of VarChar2(10) Index By Binary_Integer;
MyTab MyTabType;
vN Number(4);
Begin
MyTab(1) := 'A';
MyTab(2) := 'B';
MyTab(3) := 'C'; --¸²¸ÇÇ°ÃæË÷ÒýֵΪ3¶ÔÓ¦µÄÁÐÖµB
vN := MyTab.First;
DBMS_OUTPUT.PUT_LINE(
Ïà¹ØÎĵµ£º
Êýѧº¯Êý
ÔÚoracle ÖÐdistinct¹Ø¼ü×Ö¿ÉÒÔÏÔʾÏàͬ¼Ç¼ֻÏÔʾһÌõ
¡¡¡¡1.¾ø¶ÔÖµ
¡¡¡¡S:select abs(-1) value
¡¡¡¡O:select abs(-1) value from dual
¡¡¡¡2.È¡Õû(´ó)
¡¡¡¡S:select ceiling(-1.001) value
¡¡¡¡O:select ceil(-1.001) value from dual
¡¡¡¡3.È¡Õû£¨Ð¡£©
¡¡¡¡S:select floor(-1.001) value
......
1.Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)¡¡¡¡
¡¡¡¡ SQLSERVERµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬Òò´Ëfrom×Ó¾äÖÐдÔÚ×îºóµÄ±í£¨»ù´¡±ídriving table£©½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏ£¬±ØÐëÑ¡Ôñ¼Ç¼ÌõÊý×îÉٵıí×÷Ϊ»ù´¡±í£¬µ±SQLSERVER´¦Àí¶à¸ö±íʱ£¬»áÔËÓÃÅÅÐò¼°ºÏ²¢µÄ·½Ê½Á ......
µ±Ç°Èκΰ汾µÄORACLE¿Í»§¶ËÔÚÈκΰ汾µÄWINDOWS7É϶¼²»ÄÜÕý³£Íê³É°²×°¡£
Ö÷ÒªÊÇÒòΪORACLE°²×°µÄÏȾöÌõ¼þÀï²Ù×÷ϵͳ°æ±¾²»·ûºÏ£¬µ«ÊÇÕâ¸öÒÉÎÊ¿ÉÒÔÐÞ¸Ärefhost.xml´¦Àí£¬ ¾ßÌåÊÇÔÚrefhost.xmlÖÐÌí¼Ó
<!--Microsoft Windows 7-->
<OPERATING_SYSTEM>
& ......
Oracle 10g statisticÊý¾Ýͳ¼Æ,Oracle»á¸ù¾ÝÕâЩͳ¼ÆÐÅÏ¢À´¾ö¶¨ÊÇ×ßRBO(Rule-BasedOptimization)£¬»¹ÊÇ×ßCBO(Cost-BasedOptimization)£¬»áȥѡÔñÄÄÖÖÖ´Ðмƻ®¸ü»®Ëã,Ó°ÏìÊÇ·ñ×ßÏà¹ØµÄË÷ÒýµÈ.Èç¹ûÊÇCBOµÄ»°£¬ËüÒÀ¿¿×¼È·µÄ£¨»òÕß˵±È½Ï׼ȷµÄ£©Í³¼ÆÐÅÏ¢À´²úÉúÓÅ»¯µÄÖ´Ðз¾¶,Èç¹ûûÓÐ×ö¹ýͳ¼Æ£¬CBOÒ²¾ÍûÓÐ×öcostÆÀ¹ÀµÄÒÀ¾Ý ......
select * from (select t.*,rownum rn from (select * from emp) t where rownum<=10) where rn>=6;
´´½¨·ÖÒ³½á¹û¼¯µÄÓαê
create or replace package fenyepackage as
type testcursor is ref cursor;
end fenyepackage;
´´½¨·ÖÒ³´æ´¢¹ý³Ì
create or replace procedure fenye3(
tableName varchar2, --±íÃû
......