Oracle º¯Êý ËÙ²é
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 emp
Where UPPER(ename) Like 'AL%'ORDER BY SOUNDEX(ename)
µ¥Ðк¯ÊýÒ²¿ÉÒÔÔÚÆäËûÓï¾äÖÐʹÓã¬ÈçupdateµÄSET×Ӿ䣬INSERTµÄVALUES×Ӿ䣬DELETµÄWHERE×Ó¾ä,
ÈÏÖ¤¿¼ÊÔÌرð×¢ÒâÔÚSELECTÓï¾äÖÐʹÓÃÕâЩº¯Êý£¬ËùÒÔÎÒÃǵÄ×¢ÒâÁ¦Ò²¼¯ÖÐÔÚSELECTÓï¾äÖС£
NULLºÍµ¥Ðк¯Êý
ÔÚÈçºÎÀí½âNULLÉÏ¿ªÊ¼ÊǺÜÀ§Äѵģ¬¾ÍËãÊÇÒ»¸öºÜÓоÑéµÄÈËÒÀÈ»¶Ô´Ë¸Ðµ½À§»ó¡£
NULLÖµ±íʾһ¸öδ֪Êý¾Ý»òÕßÒ»¸ö¿ÕÖµ£¬ËãÊõ²Ù×÷·ûµÄÈκÎÒ»¸ö²Ù×÷ÊýΪNULLÖµ£¬½á¹û¾ùΪÌá¸öNULLÖµ,
Õâ¸ö¹æÔòÒ²ÊʺϺܶຯÊý£¬Ö»ÓÐCONCAT,DECODE,DUMP,NVL,REPLACEÔÚµ÷ÓÃÁËNULL²ÎÊýʱÄܹ»·µ»Ø·ÇNULLÖµ¡£
ÔÚÕâЩÖÐNVLº¯Êýʱ×îÖØÒªµÄ£¬ÒòΪËûÄÜÖ±½Ó´¦ÀíNULLÖµ£¬
NVLÓÐÁ½¸ö²ÎÊý£ºNVL(x1,x2),x1ºÍx2¶¼Ê½±í´ïʽ£¬µ±x1Ϊnullʱ·µ»ØX2,·ñÔò·µ»Øx1¡£
ÏÂÃæÎÒÃÇ¿´¿´empÊý¾Ý±íËü°üº¬ÁËнˮ¡¢½±½ðÁ½ÏÐèÒª¼ÆËã×ܵIJ¹³¥
column name
emp_id salary bonuskey type
pk nulls/unique nn,u nnfk table datatype number number numberlength 11.2 11.2
²»ÊǼòµ¥µÄ½«Ð½Ë®ºÍ½±½ð¼ÓÆðÀ´¾Í¿ÉÒÔÁË£¬Èç¹ûijһÐÐÊÇnullÖµÄÇô½á¹û¾Í½«ÊÇnull£¬±ÈÈçÏÂÃæµÄÀý×Ó£º
update empset salary=(salary+bonus)*1.1
Õâ¸öÓï¾äÖУ¬¹ÍÔ±µÄ¹¤×ʺͽ±½ð¶¼½«¸üÐÂΪһ¸öеÄÖµ£¬µ«ÊÇÈç¹ûûÓн±½ð£¬
¼´ salary + null,ÄÇô¾Í»áµÃ³ö´íÎóµÄ½áÂÛ£¬Õâ¸öʱºò¾Í
Ïà¹ØÎĵµ£º
ORACLEÖÐÊý¾Ý×ÖµäÊÓͼ·ÖΪ3´óÀà, ÓÃǰ׺Çø±ð£¬·Ö±ðΪ£ºUSER£¬ALL ºÍ DBA£¬Ðí¶àÊý¾Ý×ÖµäÊÓͼ°üº¬ÏàËƵÄÐÅÏ¢¡£
USER_*:ÓйØÓû§ËùÓµÓеĶÔÏóÐÅÏ¢£¬¼´Óû§×Ô¼º´´½¨µÄ¶ÔÏóÐÅÏ¢
ALL_*£ºÓйØÓû§¿ÉÒÔ·ÃÎʵĶÔÏóµÄÐÅÏ¢£¬¼´Óû§×Ô¼º´´½¨µÄ¶ÔÏóµÄÐÅÏ¢¼ÓÉÏÆäËûÓû§´´½¨µÄ¶ÔÏ󵫸ÃÓû§ÓÐȨ·ÃÎʵÄÐÅÏ¢
DBA_* ......
ÎÒÃÇÒª×öµ½²»µ«»áдSQL,»¹Òª×öµ½Ð´³öÐÔÄÜÓÅÁ¼µÄSQL,ÒÔÏÂΪ±ÊÕßѧϰ¡¢ÕªÂ¼¡¢²¢»ã×ܲ¿·Ö×ÊÁÏÓë´ó¼Ò·ÖÏí£¡
£¨1£©Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
orACLEµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving table)½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏÂ, ......
ceil(number) ´óÓÚ»òµÈÓÚµÄ×îСÕûÊý
floor(number) СÓÚ»òµÈÓÚµÄ×î´óÕûÊý
trunc(number,m) ÔÚÕûÊýnumberµÄmλÖýصôm¼°ÒÔºóµÄλÊý:
Èç:trunc(15.78,1)=15.7
trunc(15.78,-1)=15;
round ......
°´×ÊÁÏ˵V$BH²é¿´±íÀ´ÏÔʾÊý¾Ý¿âÀïÿ¸ö¶ÔÏóÀàÐ͵ÄÊý¾Ý»º³åÇøÀïÊý¾Ý¿éµÄÊýÁ¿.
È»ºó²éѯV$bh
select
owner, object_name
from
dba_objects o,
v$bh &nbs ......
Oracle Workflows Access Protection Concepts
As a workflow developer, you can use access protection to allow or discourage “customers” of your workflows from modifying your “seed data” workflow definitions.
×÷Ϊ¹¤×÷Á÷¿ª·¢Õߣ¬Äã¿ÉÒÔÀûÓ÷ÃÎÊ¿ØÖƱ£»¤È¨ÏÞÀ´ÔÊÐí»ò×èÖ¹¹¤×÷Á÷µÄÊ¹Ó ......