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±Ê¼Ç
l ¹ØÓÚTRUNCº¯Êý
SELECT
RELATED_ID ,
DOC_ID ,
CAT_ID ,
CAT_CODE ,
RELEASE_DATE ,
&n ......
ÎÒÔÚ°ÑoracleÊý¾Ýµ¼ÈësqlserverÖÐʱ£¬·¢ÏÖÔÚoracleÖÐ×ֶζ¨ÒåΪΨһË÷Òýʱ£¬²»Í¬¼Ç¼µÄ´Ë×Ö¶ÎÈç¹ûΪ¿Õ²»±»ÈÏΪÊÇÖØ¸´µÄ£¬µ«ÔÚsqlserverÖÐÈç¹û´Ë×Ö¶ÎΪΨһË÷Òý×ֶΣ¬²»ÔÊÐíÓÐ2¸öÒÔÉϵĿÕÖµ¡£ÓôÃÆ¡£ËùÒÔÖ»ºÃ½«sqlserverÖеÄΨһË÷Òý×Ö¶ÎÊÖ¹¤ÐÞ¸ÄΪ¼¸¸ö·Ç¿ÕµÄÖµ£¬µ«ÕâÑù³ÌÐò¿Ï¶¨Òª½øÐÐÐÞ¸ÄÁË¡£ÐèÒªÔÚ³ÌÐòÖÐΪ´Ë×Ö¶ÎÉèÖò»Öظ´ ......
CASE±í´ïʽ¿ÉÒÔÔÚSQLÖÐʵÏÖif-then-elseÐ͵ÄÂß¼£¬¶ø²»±ØÊ¹ÓÃPL/SQL¡£CASEµÄ¹¤×÷·½Ê½ÓëDECODE()ÀàËÆ£¬µ«Ó¦¸ÃʹÓÃCASE,ÒòΪËüÓëANSI¼æÈÝ¡£
CASEÓÐÁ½ÖÖ±í´ïʽ:
1. ¼òµ¥CASE±í´ïʽ,ʹÓñí´ïʽȷ¶¨·µ»ØÖµ.
Óï·¨:
CASE search_expression
WHEN expression1 THEN result1
WHEN expression2 THEN ......
ÏÞÖÆË÷ÒýÊÇһЩûÓоÑéµÄ¿ª·¢ÈËÔ±¾³£·¸µÄ´íÎóÖ®Ò»¡£ÔÚSQLÖÐÓкܶàÏÝÚå»áʹһЩË÷ÒýÎÞ·¨Ê¹Óá£ÏÂÃæÌÖÂÛһЩ³£¼ûµÄÎÊÌ⣺
1 ʹÓò»µÈÓÚ²Ù×÷·û(<>¡¢!=)
ÏÂÃæµÄ²éѯ¼´Ê¹ÔÚcust_ratingÁÐÓÐÒ»¸öË÷Òý£¬²éѯÓï¾äÈÔȻִÐÐÒ»´ÎÈ«±íɨÃè¡£
  ......
Oracle ´¥·¢Æ÷ÓÐÓï¾ä¼¶´¥·¢Æ÷ºÍÐм¶´¥·¢Æ÷
Óï¾ä¼¶´¥·¢Æ÷ µ±É¾³ý²¿ÃűíÖеIJ¿ÃźÅʱ£¬Í¬Ê±É¾³ýµôÔ±¹¤±íÖв¿ÃźÅΪ£ºold.deptnoµÄ¼Ç¼
create or replace trigger del_dept_id
after delete on dept
for each row
begin
delete from emp where deptno=:old.deptno;
end;
µ±Íù²¿Ãűí²åÈëʱ£¬Í¬Ê±ÔÚÔ±¹¤±íÖ ......