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]ÔõôÑù´ÓÊý¾Ý¿âÖлñµÃ
Ïà¹ØÎĵµ£º
ʲôÊǺϲ¢¶àÐÐ×Ö·û´®£¨Á¬½Ó×Ö·û´®£©ÄØ£¬ÀýÈ磺
SQL> desc test;
Name Type Nullable Default Comments
------- ------------ -------- ------- --------
COUNTRY VARCHAR2(20) Y &nb ......
ʲôÊǺϲ¢¶àÐÐ×Ö·û´®£¨Á¬½Ó×Ö·û´®£©ÄØ£¬ÀýÈ磺
SQL> desc test;
Name Type Nullable Default Comments
------- ------------ -------- ------- --------
COUNTRY VARCHAR2(20) Y &nb ......
author:skate
time:2010-05-27
oracleÌí¼Ó×Ö¶ÎÒýÆðµÄÎÊÌâ
ÓÉÓÚͬÊÂÔÚÌí¼ÓÁ˱íµÄ×ֶκó£¬Ã»ÓÐÖØбàÒëÊý¾Ý¿âµÄ¶ÔÏ󣬵¼ÖºÍÕâ¸ö±íÏà¹ØµÄºÜ¶àprocedureºÍpackage¶¼Ê§Ð§¡£
ÓÉÓÚ·¢ÏÖºóÊÇÔÚÐ޸ĺó2¸öСʱ°É£¬¶øÔÚÕâÆÚ¼äºÜ¶à³ÌÐòÔÚµ÷ÓÃÕâЩ¹ý³Ì£¬ÖÂʹºÜ¶à»á»°¶¼½©ËÀÔÚÄÇ£¬ËùÒÔÒ²ÎÞ·¨
ÔÙÖØбàÒë¹ýÈ ......
ÓÃsql*plus»òµÚÈý·½¿ÉÒÔÔËÐÐsqlÓï¾äµÄ³ÌÐòµÇ¼Êý¾Ý¿â£º
Ôö¼ÓÒ»¸öÁУº
ALTER TABLE ±íÃû ADD(ÁÐÃû Êý¾ÝÀàÐÍ);
È磺
ALTER TABLE emp ADD(weight NUMBER(38,0));
ÐÞ¸ÄÒ»¸öÁеÄÊý¾ÝÀàÐÍ(Ò»°ãÏÞÓÚÐ޸ij¤¶È£¬ÐÞ¸ÄΪһ¸ö²»Í¬ÀàÐÍʱÓÐÖî¶àÏÞÖÆ):
ALTER TABLE ±íÃû MODIFY(ÁÐÃû Êý¾ÝÀàÐÍ);
È磺
ALTER TABLE emp MODIFY(wei ......
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 ......