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

Oracle³£ÓÃÎÊÌâ½â´ð

 £¨1£©[Q]ÈçºÎ²åÈëµ¥ÒýºÅµ½Êý¾Ý¿â±íÖÐ
[A]¿ÉÒÔÓÃASCIIÂë´¦Àí£¬ÆäËüÌØÊâ×Ö·ûÈç&Ò²Ò»Ñù£¬Èç
insert into t values('i'||chr(39)||'m'); -- chr(39)´ú±í×Ö·û'
»òÕßÓÃÁ½¸öµ¥ÒýºÅ±íʾһ¸ö
or insert into t values('I''m'); -- Á½¸ö''¿ÉÒÔ±íʾһ¸ö'
 
£¨2£©[Q]Ëæ»ú³éÈ¡Ç°NÌõ¼Ç¼µÄÎÊÌâ
[A]8iÒÔÉÏ°æ±¾
select * from (select * from tablename order by dbms_random.value) where rownum< N;
×¢£ºdbms_random°üÐèÒªÊÖ¹¤°²×°£¬Î»ÓÚ$ORACLE_HOME/rdbms/admin/dbmsrand.sql
dbms_random.value(100,200)¿ÉÒÔ²úÉú100µ½200·¶Î§µÄËæ»úÊý
 
£¨3£©[Q]ÔõôÑù³éÈ¡Öظ´¼Ç¼
[A]select * from table t1 where t1.rowid !=
(select max(rowid) from table t2
where t1.id=t2.id and t1.name=t2.name)
»òÕß
select count(*), t.col_a,t.col_b from table t
group by col_a,col_b
having count(*)>1
Èç¹ûÏëɾ³ýÖظ´¼Ç¼£¬¿ÉÒ԰ѵÚÒ»¸öÓï¾äµÄselectÌ滻Ϊdelete
 
£¨4£©[Q]ÔõôÑùÉèÖÃ×ÔÖÎÊÂÎñ
[A]8iÒÔÉÏ°æ±¾£¬²»Ó°ÏìÖ÷ÊÂÎñ
pragma autonomous_transaction;
……
commit|rollback;
 
£¨5£©[Q]ÔõôÑùÔÚ¹ý³ÌÖÐÔÝÍ£Ö¸¶¨Ê±¼ä
[A]DBMS_LOCK°üµÄsleep¹ý³Ì
È磺dbms_lock.sleep(5);±íʾÔÝÍ£5Ãë¡£
 
£¨6£©[Q]ÔõÑù´´½¨ÁÙʱ±í
[A]8iÒÔÉÏ°æ±¾
create global temporary tablename(column list)
on commit preserve rows; --Ìá½»±£ÁôÊý¾Ý »á»°ÁÙʱ±í
on commit delete rows; --Ìύɾ³ýÊý¾Ý ÊÂÎñÁÙʱ±í
ÁÙʱ±íÊÇÏà¶ÔÓڻỰµÄ£¬±ðµÄ»á»°¿´²»µ½¸Ã»á»°µÄÊý¾Ý¡£
 
£¨7£©[Q]ÔõôÑùÔÚPL/SQLÖÐÖ´ÐÐDDLÓï¾ä
[A]1¡¢8iÒÔÏ°汾dbms_sql°ü
2¡¢8iÒÔÉÏ°æ±¾»¹¿ÉÒÔÓÃ
execute immediate sql;
dbms_utility.exec_ddl_statement('sql');
 
£¨8£©[Q]ÔõôÑù»ñÈ¡IPµØÖ·
[A]·þÎñÆ÷(817ÒÔÉÏ)£ºutl_inaddr.get_host_address
¿Í»§¶Ë£ºsys_context('userenv','ip_address')
 
£¨9£©[Q]ÔõôÑùÔÚORACLEÖж¨Ê±ÔËÐд洢¹ý³Ì
[A]¿ÉÒÔÀûÓÃdbms_job°üÀ´¶¨Ê±ÔËÐÐ×÷Òµ£¬ÈçÖ´Ðд洢¹ý³Ì£¬Ò»¸ö¼òµ¥µÄÀý×Ó£¬Ìá½»Ò»¸ö×÷Òµ£º
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno, 'ur_procedure;',SYSDATE,'SYSDATE + 1');
commit;
END;
Ö®ºó£¬¾Í¿ÉÒÔÓÃÒÔÏÂÓï¾ä²éѯÒѾ­Ìá½»µÄ×÷Òµ
select * from user_jobs;
 
£¨10£©[Q]ÔõôÑù´ÓÊý¾Ý¿âÖлñµÃ


Ïà¹ØÎĵµ£º

Oracle¶àÐмǼºÏ²¢/Á¬½Ó/¾ÛºÏ×Ö·û´®µÄ¼¸ÖÖ·½·¨

ʲôÊǺϲ¢¶àÐÐ×Ö·û´®£¨Á¬½Ó×Ö·û´®£©ÄØ£¬ÀýÈ磺
SQL> desc test;
Name     Type          Nullable Default Comments
------- ------------ -------- ------- --------
COUNTRY VARCHAR2(20) Y       &nb ......

Oracle¶àÐмǼºÏ²¢/Á¬½Ó/¾ÛºÏ×Ö·û´®µÄ¼¸ÖÖ·½·¨

ʲôÊǺϲ¢¶àÐÐ×Ö·û´®£¨Á¬½Ó×Ö·û´®£©ÄØ£¬ÀýÈ磺
SQL> desc test;
Name     Type          Nullable Default Comments
------- ------------ -------- ------- --------
COUNTRY VARCHAR2(20) Y       &nb ......

oracleÌí¼Ó×Ö¶ÎÒýÆðµÄÎÊÌâ

author:skate
time:2010-05-27
oracleÌí¼Ó×Ö¶ÎÒýÆðµÄÎÊÌâ
     ÓÉÓÚͬÊÂÔÚÌí¼ÓÁ˱íµÄ×ֶκó£¬Ã»ÓÐÖØбàÒëÊý¾Ý¿âµÄ¶ÔÏ󣬵¼ÖºÍÕâ¸ö±íÏà¹ØµÄºÜ¶àprocedureºÍpackage¶¼Ê§Ð§¡£
ÓÉÓÚ·¢ÏÖºóÊÇÔÚÐ޸ĺó2¸öСʱ°É£¬¶øÔÚÕâÆÚ¼äºÜ¶à³ÌÐòÔÚµ÷ÓÃÕâЩ¹ý³Ì£¬ÖÂʹºÜ¶à»á»°¶¼½©ËÀÔÚÄÇ£¬ËùÒÔÒ²ÎÞ·¨
ÔÙÖØбàÒë¹ýÈ ......

ÔÚOracle 9iÖÐÐ޸ıíµÄ½á¹¹µÄÏà¹ØsqlÓï¾ä

ÓÃsql*plus»òµÚÈý·½¿ÉÒÔÔËÐÐsqlÓï¾äµÄ³ÌÐòµÇ¼Êý¾Ý¿â£º
Ôö¼ÓÒ»¸öÁУº
ALTER TABLE ±íÃû ADD(ÁÐÃû Êý¾ÝÀàÐÍ);
È磺
ALTER TABLE emp ADD(weight NUMBER(38,0));
ÐÞ¸ÄÒ»¸öÁеÄÊý¾ÝÀàÐÍ(Ò»°ãÏÞÓÚÐ޸ij¤¶È£¬ÐÞ¸ÄΪһ¸ö²»Í¬ÀàÐÍʱÓÐÖî¶àÏÞÖÆ):
ALTER TABLE ±íÃû MODIFY(ÁÐÃû Êý¾ÝÀàÐÍ);
È磺
ALTER TABLE emp MODIFY(wei ......

oracle ³£ÓÃÐÔÄܲéѯSQL·ÖÏí

select count(1) from dictionary;
select * from dba_data_files;
select count(1) from dba_objects t where t.owner='BESTTONE';
select * from dba_tablespaces t where t.tablespace_name='BESTTONE';
select count(1) from dba_tables t where t.owner='BESTTONE';
select t.table_name,t.comments from diction ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ