OracleÊý¾Ý¿âÓαê
OracleÊý¾Ý¿âÓαêʹÓ÷½·¨
2009-12-30 15:27
SQLÊÇÓÃÓÚ·ÃÎÊOracleÊý¾Ý¿âµÄÓïÑÔ£¬PL/SQLÀ©Õ¹ºÍ¼ÓÇ¿ÁËSQLµÄ¹¦ÄÜ£¬ËüͬʱÒýÈëÁ˸üÇ¿µÄ³ÌÐòÂß¼, ÏÂÃæÔÚ±¾ÎÄÖн«¶ÔÓαêµÄʹÓýøÐÐһϽ²½â,Ï£Íû¿ÉÒԺʹó¼Ò¹²Í¬Ñ§Ï°½ø²½¡£
¡¡¡¡Óαê×ÖÃæÀí½â¾ÍÊÇÓζ¯µÄ¹â±ê¡£ÓαêÊÇSQLµÄÒ»¸öÄڴ湤×÷Çø£¬ÓÉϵͳ»òÓû§ÒÔ±äÁ¿µÄÐÎʽ¶¨Òå¡£ÔÚijЩÇé¿öÏ£¬ÐèÒª°ÑÊý¾Ý´Ó´æ·ÅÔÚ´ÅÅ̵ıíÖе÷µ½¼ÆËã»úÄÚ´æÖнøÐд¦Àí£¬×îºó½«´¦Àí½á¹ûÏÔʾ³öÀ´»ò×îÖÕд»ØÊý¾Ý¿â¡£ÕâÑùÊý¾Ý´¦ÀíµÄËٶȲŻáÌá¸ß£¬·ñÔòƵ·±µÄ´ÅÅÌÊý¾Ý½»»»»á½µµÍЧÂÊ¡£ÓÃÊý¾Ý¿âÓïÑÔÀ´ÃèÊöÓαê¾ÍÊÇÓ³ÉäÔÚ½á¹û¼¯ÖÐÒ»ÐÐÊý¾ÝÉϵÄλÖÃʵÌ壬ÓÐÁËÓα꣬Óû§¾Í¿ÉÒÔ·ÃÎʽá¹û¼¯ÖеÄÈÎÒâÒ»ÐÐÊý¾ÝÁË£¬½«Óαê·ÅÖõ½Ä³Ðк󣬼´¿É¶Ô¸ÃÐÐÊý¾Ý½øÐвÙ×÷£¬ÀýÈçÌáÈ¡µ±Ç°ÐеÄÊý¾ÝµÈ¡£
¡¡¡¡ÓαêÓÐÁ½ÖÖÀàÐÍ£ºÏÔʽÓαêºÍÒþʽÓαꡣÓαêÒ»µ©´ò¿ª£¬Êý¾Ý¾Í´ÓÊý¾Ý¿âÖд«Ë͵½Óαê±äÁ¿ÖУ¬È»ºóÓ¦ÓóÌÐòÔÙ´ÓÓαê±äÁ¿Öзֽâ³öÐèÒªµÄÊý¾Ý£¬²¢½øÐд¦Àí¡£
¡¡¡¡µ±ÏµÍ³Ê¹ÓÃÒ»¸öÒþʽÓαêʱ£¬¿ÉÒÔͨ¹ýÒþʽÓαêµÄÊôÐÔÀ´Á˽â²Ù×÷µÄ״̬ºÍ½á¹û£¬½ø¶ø¿ØÖƳÌÐòµÄÁ÷³Ì¡£ÒþʽÓαê¿ÉÒÔʹÓÃÃû×ÖSQLÀ´·ÃÎÊ£¬µ«Òª×¢Ò⣬ͨ¹ýSQLÓαêÃû×ÜÊÇÖ»ÄÜ·ÃÎÊÇ°Ò»¸ö´¦Àí²Ù×÷»òµ¥ÐÐSELECT²Ù×÷µÄÓαêÊôÐÔ¡£ËùÒÔͨ³£ÔÚ¸Õ¸ÕÖ´ÐÐÍê²Ù×÷Ö®ºó£¬Á¢¼´Ê¹ÓÃSQLÓαêÃûÀ´·ÃÎÊÊôÐÔ¡£ÓαêµÄÊôÐÔÓÐËÄÖÖ,·Ö±ðÊÇSQL %ISOPEN£¬SQL %FOUND£¬SQL %NOTFOUND£¬SQL %ROWCOUNT¡£
¡¡¡¡SQL%ISOPEN·µ»ØµÄÀàÐÍΪ²¼¶ûÐÍ,ÅжÏÓαêÊÇ·ñ±»´ò¿ª£¬Èç¹û´ò¿ª%ISOPENµÈÓÚtrue,·ñÔòµÈÓÚfalse,¼´Ö´Ðйý³ÌÖÐΪÕæ,½áÊøºóΪ¼Ù¡£
¡¡¡¡SQL%NOTFOUND·µ»ØֵΪ²¼¶ûÐÍ,ÅжÏÓαêËùÔÚµÄÐÐÊÇ·ñÓÐЧ£¬Èç¹ûÓÐЧ£¬Ôò%FOUNDDµÈÓÚtrue£¬·ñÔòµÈÓÚfalse,¼´Óë%FOUNDÊôÐÔ·µ»ØÖµÏà·´¡£
¡¡¡¡SQL%FOUND·µ»ØÖµµÄÀàÐÍΪ²¼¶ûÐÍ£¬ÖµÎªTRUE´ú±í²åÈë ɾ³ý ¸üлòµ¥Ðвéѯ²Ù×÷³É¹¦¡£
¡¡¡¡SQL%ROWCOUNT·µ»ØÖµÀàÐÍΪÕûÐÍ£¬·µ»Øµ±Ç°Î»ÖÃΪֹÓαê¶ÁÈ¡µÄ¼Ç¼ÐÐÊý,¼´³É¹¦Ö´ÐеÄÊý¾ÝÐÐÊý¡£ ʾÀý´úÂëÈçÏ£º
set serveroutput on;
¡¡¡¡declare
¡¡¡¡varno varchar2(20);
¡¡¡¡varprice varchar2(20);
¡¡¡¡CURSOR mycur(vartype number) is
¡¡¡¡select emp_no,emp_zc from cus_emp_basic
¡¡¡¡where com_no = vartype;
¡¡¡¡begin
¡¡¡¡if mycur%isopen = false then
¡¡¡¡open&n
Ïà¹ØÎĵµ£º
ѧϰOracle DBAÒ²°ë¸ö¶àѧÆÚÁË£¬½ñÌìÃÍÈ»²Å·¢ÏÖ£¬ÔÀ´ÎÒµÄÊ黹ÊǺÜеģ¬ÉϿβÙ×÷ʱºòÒ²Ö»ÊÇÖªµÀ´ó¸ÅÔõô×ö£¬µ«ÊÇÒªÕæµÄÈ«²¿×Ô¼º×ö£¬¶ø²»È¥·Ê黹ÊÇÓÐÒ»¶¨µÄÄѶȵģ¬ËùÒÔÄØ£¬½ñÌ쿪ʼ½«DBA´ÓÍ·¸´Ï°Ò»±é£¬Í¬Ê±ÔÙ²Ù×÷Ò»±é¡£
µÚÒ»Õ£¬Ñ§µÄÊÇOracleµÄÌåϵ½á¹¹£ ......
ÔÚOracleÖв鿴ËùÓеıí:
select * from tab/dba_tables/dba_objects/cat;
¿´Óû§½¨Á¢µÄ±í :
select table_name from user_tables; //µ±Ç°Óû§µÄ±í
select table_name from all_tables; //ËùÓÐÓû§µÄ±í   ......
1¡¢À䱸·Ý±ØÐ뽫Êý¾Ý¿â·þÎñÆ÷Í£Ö¹£¬²»È»»Ö¸´µÄʱºòÎÞ·¨»Ö¸´
Í£Ö¹·þÎñÆ÷µÄÃüÁ
ÔÚsqlplusϵǽsqlplus£ºsqlplus "/as sysdba"
Í£Ö¹oracle·þÎñ£º shutdown immediate;£¨´ËÃüÁîµÈ´ýÊÂÎñÖ´ÐÐÍê±ÏºóÔٹرգ©
ÊÇÔÚ²»ÐÐÖ±½ÓÇ¿ÐÐÖÕÖ¹:shutdown abort;£¨´ËÃüÁîÖ±½Ó¹Ø±ÕʵÀý·þÎñ£©
¹Ø±Õlistener£º
linuxÃüÁlsnrctl
È»ºóÍ ......
select dm_id, father_id, dm_name from gy_dmcode t
where level <= 2 --²ã¼¶
start with t.dm_id = '0' --¸ù½Úµã
connect by prior t.dm_id = t.father_id; --¶¨Òå¹Øϵ£¬prior ±íʾÓÅÏȼ¶ ......
extent--×îС¿Õ¼ä·ÖÅ䵥λ --tablespace management
block --×îСi/oµ¥Î» --segment management
create tablespace james
datafile '/export/home/oracle/oradata/james.dbf'
size 100M ¡¡¡¡¡¡¡¡¡¡¡¡--³õʼµÄÎļþ´óС¡¡
autoextend On¡¡¡¡¡¡¡¡ --×Ô¶¯Ôö³¤
next 10M¡ ......