Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

ORACLEÊÓͼ


2008Äê06ÔÂ01ÈÕ ÐÇÆÚÈÕ 14:52
OracleµÄÊý¾Ý¿â¶ÔÏó·ÖΪÎåÖÖ£º±í£¬ÊÓͼ£¬ÐòÁУ¬Ë÷ÒýºÍͬÒå´Ê¡£ 
ÊÓͼÊÇ»ùÓÚÒ»¸ö±í»ò¶à¸ö±í»òÊÓͼµÄÂß¼­±í£¬±¾Éí²»°üº¬Êý¾Ý£¬Í¨¹ýËü¿ÉÒÔ¶Ô±íÀïÃæµÄÊý¾Ý½øÐвéѯºÍÐ޸ġ£ÊÓͼ»ùÓÚµÄ±í³ÆÎª»ù±í¡£ 
ÊÓͼÊÇ´æ´¢ÔÚÊý¾Ý×ÖµäÀïµÄÒ»ÌõselectÓï¾ä¡£ ͨ¹ý´´½¨ÊÓͼ¿ÉÒÔÌáÈ¡Êý¾ÝµÄÂß¼­Éϵļ¯ºÏ»ò×éºÏ¡£ 
ÊÓͼµÄÓŵ㣺 
1.¶ÔÊý¾Ý¿âµÄ·ÃÎÊ£¬ÒòΪÊÓͼ¿ÉÒÔÓÐÑ¡ÔñÐÔµÄѡȡÊý¾Ý¿âÀïµÄÒ»²¿·Ö¡£ 
2.Óû§Í¨¹ý¼òµ¥µÄ²éѯ¿ÉÒÔ´Ó¸´ÔÓ²éѯÖеõ½½á¹û¡£ 
3.ά»¤Êý¾ÝµÄ¶ÀÁ¢ÐÔ£¬ÊÔͼ¿É´Ó¶à¸ö±í¼ìË÷Êý¾Ý¡£ 
4.¶ÔÓÚÏàͬµÄÊý¾Ý¿É²úÉú²»Í¬µÄÊÓͼ¡£ 
ÊÓͼ·ÖΪ¼òµ¥ÊÓͼºÍ¸´ÔÓÊÓͼ£º 
¼òµ¥ÊÓͼֻ´Óµ¥±íÀï»ñÈ¡Êý¾Ý 
¸´ÔÓÊÓͼ´Ó¶à±í 
¼òµ¥ÊÓͼ²»°üº¬º¯ÊýºÍÊý¾Ý×é 
¸´ÔÓÊÓͼ°üº¬ 
¼òµ¥ÊÓͼ¿ÉÒÔʵÏÖDML²Ù×÷ 
¸´ÔÓÊÓͼ²»¿ÉÒÔ. 
ÊÓͼµÄ´´½¨£º 
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name 
[(alias[, alias]...)] 
AS subquery 
[WITH CHECK OPTION [CONSTRAINT constraint]] 
[WITH READ ONLY] 
ÆäÖУº 
OR REPLACE £ºÈôËù´´½¨µÄÊÔͼÒѾ­´æÔÚ£¬ORACLE×Ô¶¯Öؽ¨¸ÃÊÓͼ£» 
FORCE £º²»¹Ü»ù±íÊÇ·ñ´æÔÚORACLE¶¼»á×Ô¶¯´´½¨¸ÃÊÓͼ£» 
NOFORCE £ºÖ»Óлù±í¶¼´æÔÚORACLE²Å»á´´½¨¸ÃÊÓͼ£º 
alias £ºÎªÊÓͼ²úÉúµÄÁж¨ÒåµÄ±ðÃû£» 
subquery £ºÒ»ÌõÍêÕûµÄSELECTÓï¾ä£¬¿ÉÒÔÔÚ¸ÃÓï¾äÖж¨Òå±ðÃû£» 
WITH CHECK OPTION £º 
²åÈë»òÐ޸ĵÄÊý¾ÝÐбØÐëÂú×ãÊÓͼ¶¨ÒåµÄÔ¼Êø£» 
WITH READ ONLY £º 
¸ÃÊÓͼÉϲ»ÄܽøÐÐÈκÎDML²Ù×÷¡£ 
ÀýÈ磺 
CREATE OR REPLACE VIEW dept_sum_vw 
(name,minsal,maxsal,avgsal) 
AS SELECT d.dname,min(e.sal),max(e.sal),avg(e.sal) 
from emp e,dept d 
WHERE e.deptno=d.deptno 
GROUP BY d.dname; 
ÊÓͼµÄ¶¨ÒåÔ­Ôò£º 
1.ÊÓͼµÄ²éѯ¿ÉÒÔʹÓø´ÔÓµÄSELECTÓï·¨£¬°üÀ¨Á¬½Ó/·Ö×é²éѯºÍ×Ó²éѯ£» 
2.ÔÚûÓÐWITH CHECK OPTIONºÍ READ ONLY µÄÇé¿öÏ£¬²éѯÖв»ÄÜʹÓà
ORDER BY ×Ӿ䣻 
3.Èç¹ûûÓÐΪCHECK OPTIONÔ¼ÊøÃüÃû£¬ÏµÍ³»á×Ô¶¯ÎªÖ®ÃüÃû£¬ÐÎʽΪSYS_Cn; 
4.OR REPLACEÑ¡Ïî¿ÉÒÔ²»É¾³ýÔ­ÊÓͼ±ã¿É¸ü¸ÄÆä¶¨Òå²¢ÖØ½¨£¬»òÖØÐÂÊÚÓè¶ÔÏó 
ȨÏÞ¡£ 
ÊÓͼµÄ²éѯ£º 


Ïà¹ØÎĵµ£º

OracleËæ»úº¯ÊýµÄÓ÷¨

 ÈÝÒ×µÃ˵£¬¾­¹ýdbms_random°üµ÷ÓÃËæ»úÊýµÄ²½Öè´óÌåÓÐ4ÖÖ£º
Ò»¡¢dbms_random.normal
Õâ¸öº¯Êý²»´ø²ÎÊý£¬»á»Øµ½normal distributionµÄÒ»¸önumberÃÅÀ֮࣬ËùÒÔ´óµÖËæ»úÊý»áÔÚ-Ò»µ½Ò»Æä¼ä¡£
¼òÂÔ²âÊÔÁËÒ»ÏÂ×Ó£¬·¢×÷100000´Î×î´óÄܵ½ÎåÉÏÏ£º
SQL> declare
¶þ i number:=Áã;
Èý j number:=Áã;
ËÄ begin
Îå for ......

Oracle Latch¼°latchì¶Ü

 Ð¡Òý
Oracle RdbmsÊ©ÓÃÁ˸÷ÖÖ²»Í¬ÀàÐ͵ÄËø¶¨»úÖÆ£¬latchµÈÓÚ´ËÖеÄÒ»ÖÖ£¬ÕýÎĽ«¼¯ÖÐÒý¼ûlatch(ãÅ)µÄ¸ÅÄ¶®µÃlatchµÄ¶ÒÏÖ²½Ö貢˵Ã÷»½ÆðlatchĦ²ÁµÄÔµ¹Ê¡£
ʲôÊÇlatch
LatchÊÇÓÃÀ´±£»¤SGAÇøÖй²ÏíÊý¾Ý½á¹¹µÄÒ»ÖÖ´®Ðл¯Ëø¶¨»úÖÆ¡£LatchµÄ¶ÒÏÖÊÇÓë²Ù×÷ϵͳÏà¸ÉµÄ£¬ÓÈÉõºÍÒ»¸ö¹ý³ÌÊDz»ÊÇØ½ÐèµÈºòÒ»¸ölatch¡¢¼±ÐèµÈº ......

Oracle ³£Óú¯Êý

001¡¢×Ö·û
length/lengthb   ×Ö·ûÊý(1¸öºº×Ö1¸ö×Ö·û) / ×Ö½ÚÊý(1¸öºº×Ö2¸ö×Ö½Ú)
ltrim/rtrim/trim ɾ³ý¿Õ¸ñ
lower/upper      ´óСдת»»
select length('abc') from dual;
select substr(ename, 1, 3) from emp; ´ÓµÚÒ»¸ö×Ö·û¿ªÊ¼½Ø£¬Ò»¹²½Ø3¸ö×Ö·û
substr('abcdefg',2,3) => ......

Oracle°²È«È«½Ó´¥£¨Ï£©

¡¡·ÏòÃÜÂëÎļþÖÐÔö¼Ó¡¢É¾³ýÓû§:
¡¡¡¡µ±³õʼ»¯²ÎÊýREMOTE_LOGIN_PASSWORDFILEÉèÖÃΪEXCLUSIVEʱ£¬ÏµÍ³ÔÊÐí³ýINTERNAL/SYSÒÔÍâµÄÆäËûÓû§ÒÔ¹ÜÀíÔ±Éí·Ý´ÓÔ¶¶Ë»ò±¾»úµÇ¼µ½OracleÊý¾Ý¿âϵͳ£¬Ö´ÐÐÊý¾Ý¿â¹ÜÀí¹¤×÷;ÕâЩÓû§Ãû±ØÐë´æÔÚÓÚÃÜÂëÎļþÖУ¬ÏµÍ³²ÅÄÜʶ±ðËûÃÇ¡£ÓÉÓÚ²»¹ÜÊÇÔÚ´´½¨Êý¾Ý¿âʵÀýʱ×Ô¶¯´´½¨µÄÃÜÂëÎļþ£¬ ......

Oracle´æ´¢¹ý³Ì£¬ÒÔÓαêµÄ·½Ê½·µ»ØÒ»¸ö¼Ç¼¼¯


--´´½¨Ò»¸ö°ü
create or replace package types
as
type cursorType is ref cursor;
end types;
--´´½¨´æ´¢¹ý³Ì,ÓαêÀàÐÍ·µ»Ø²ÎÊý
create or replace procedure SP_Fee_Instance(v_company in varchar, v_sdate in nva ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ