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Ñ¡Ïî¿ÉÒÔ²»É¾³ýÔÊÓͼ±ã¿É¸ü¸ÄÆä¶¨Òå²¢ÖØ½¨£¬»òÖØÐÂÊÚÓè¶ÔÏó
ȨÏÞ¡£
ÊÓͼµÄ²éѯ£º
Ïà¹ØÎĵµ£º
ÈÝÒ×µÃ˵£¬¾¹ýdbms_random°üµ÷ÓÃËæ»úÊýµÄ²½Öè´óÌåÓÐ4ÖÖ£º
Ò»¡¢dbms_random.normal
Õâ¸öº¯Êý²»´ø²ÎÊý£¬»á»Øµ½normal distributionµÄÒ»¸önumberÃÅÀ֮࣬ËùÒÔ´óµÖËæ»úÊý»áÔÚ-Ò»µ½Ò»Æä¼ä¡£
¼òÂÔ²âÊÔÁËÒ»ÏÂ×Ó£¬·¢×÷100000´Î×î´óÄܵ½ÎåÉÏÏ£º
SQL> declare
¶þ i number:=Áã;
Èý j number:=Áã;
ËÄ begin
Îå for ......
СÒý
Oracle RdbmsÊ©ÓÃÁ˸÷ÖÖ²»Í¬ÀàÐ͵ÄËø¶¨»úÖÆ£¬latchµÈÓÚ´ËÖеÄÒ»ÖÖ£¬ÕýÎĽ«¼¯ÖÐÒý¼ûlatch(ãÅ)µÄ¸ÅÄ¶®µÃlatchµÄ¶ÒÏÖ²½Ö貢˵Ã÷»½ÆðlatchĦ²ÁµÄÔµ¹Ê¡£
ʲôÊÇlatch
LatchÊÇÓÃÀ´±£»¤SGAÇøÖй²ÏíÊý¾Ý½á¹¹µÄÒ»ÖÖ´®Ðл¯Ëø¶¨»úÖÆ¡£LatchµÄ¶ÒÏÖÊÇÓë²Ù×÷ϵͳÏà¸ÉµÄ£¬ÓÈÉõºÍÒ»¸ö¹ý³ÌÊDz»ÊÇØ½ÐèµÈºòÒ»¸ölatch¡¢¼±ÐèµÈº ......
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) => ......
¡¡·ÏòÃÜÂëÎļþÖÐÔö¼Ó¡¢É¾³ýÓû§:
¡¡¡¡µ±³õʼ»¯²ÎÊýREMOTE_LOGIN_PASSWORDFILEÉèÖÃΪEXCLUSIVEʱ£¬ÏµÍ³ÔÊÐí³ýINTERNAL/SYSÒÔÍâµÄÆäËûÓû§ÒÔ¹ÜÀíÔ±Éí·Ý´ÓÔ¶¶Ë»ò±¾»úµÇ¼µ½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 ......