OracleµÄ»ù±¾²Ù×÷£«Oracle×Ö¶ÎÀàÐÍ£¨zz£©
ÔÚOracle¹ØÓÚʱ¼äÊôÐԵĽ¨±í
Example:
create table courses(
cid varchar(20) not null primary key,
cname varchar(20) not null,
ctype integer,
ctime date DEFAULT SYSDATE,
cscore float not null
)
insert into courses values('ss01','java',0,TO_DATE('2009-8-28','yyyy-mm-dd'),94)
insert into courses values('ss02','music',1,TO_DATE('2009-8-27','yyyy-mm-dd'),88)
insert into courses values('ss03','c++',0,TO_DATE('2009-8-29','yyyy-mm-dd'),78)
ÔÚPL/SQL½¨±íÒԺ󣬼ǵÃcommit£¬²ÅÄÜÔÚpl/sqlÉϲéѯµ½Êý¾Ý¡£
¶ÔÓÚÈÕÆÚÀàÐÍ
½«×Ö·û´®×ª»»³ÉÈÕÆÚ£º
Insert Into table£¨col_date£©Values(to_date(‘2003-01-16’,’yyyy-mm-dd’));
½«ÈÕÆÚת»»³É×Ö·û´®£º
Insert Into table £¨col_char£©Values(to_char(Date,’format’));
select * from courses where ctime>to_date('2009-8-28','yyyy-mm-dd')
´´½¨±í
1. ORACLE³£ÓõÄ×Ö¶ÎÀàÐÍ
ORACLE³£ÓõÄ×Ö¶ÎÀàÐÍÓÐ
VARCHAR2 (size) ¿É±ä³¤¶ÈµÄ×Ö·û´®, ±ØÐë¹æ¶¨³¤¶È
CHAR(size) ¹Ì¶¨³¤¶ÈµÄ×Ö·û´®, ²»¹æ¶¨³¤¶ÈĬÈÏֵΪ£±
NUMBER(p,s) Êý×ÖÐÍpÊÇλÊý×ܳ¤¶È, sÊÇСÊýµÄ³¤¶È, ¿É´æ¸ºÊý
×38λ. ²»¹»Î»Ê±»áËÄÉáÎåÈë.
DATE ÈÕÆÚºÍʱ¼äÀàÐÍ
LOB ³¬³¤×Ö·û, ×î´ó¿É´ï4G
CLOB ³¬³¤Îı¾×Ö·û´®
BLOB ³¬³¤¶þ½øÖÆ×Ö·û´®
BFILE ³¬³¤¶þ½øÖÆ×Ö·û´®, ±£´æÔÚÊý¾Ý¿âÍâµÄÎļþÀïÊÇÖ»¶ÁµÄ.
Êý×Ö×Ö¶ÎÀàÐÍλÊý¼°ÆäËÄÉáÎåÈëµÄ½á¹û
ÔʼÊýÖµ1234567.89
Êý×Ö×Ö¶ÎÀàÐÍλÊý ´æ´¢µÄÖµ
Number 1234567.89
Number(8) 12345678
Number(6) ´í
Number(9,1) 1234567.9
Number(9,3) ´í
Number(7,2) ´í
Number(5,-2) 1234600
Number(5,-4) 1230000
Number(*,1) 1234567.9
2. ´´½¨±íʱ¸ø×ֶμÓĬÈÏÖµ ºÍÔ¼ÊøÌõ¼þ
´´½¨±íʱ¿ÉÒÔ¸ø×ֶμÓÉÏĬÈÏÖµ
ÀýÈç : ÈÕÆÚ×ֶΠDEFAULT SYSDATE
ÕâÑùÿ´Î²åÈëºÍÐÞ¸Äʱ, ²»ÓóÌÐò²Ù×÷Õâ¸ö×ֶζ¼Äܵõ½¶¯×÷µÄʱ¼ä
´´½¨±íʱ¿ÉÒÔ¸ø×ֶμÓÉÏÔ¼ÊøÌõ¼þ
ÀýÈç: ·Ç¿Õ NOT NULL
²»ÔÊÐíÖظ´ UNIQUE
¹Ø¼ü×Ö PRIMARY KEY
°´Ìõ¼þ¼ì²é CHECK (Ìõ¼þ)
Íâ¼ü REFERENCES ±íÃû(×Ö¶ÎÃû)
3. ´´½¨±íµÄÀý×Ó
CREATE TABLE DEPT(
EPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)) ;
CREATE TABLE
Ïà¹ØÎĵµ£º
1. ÉèÖÃÒ³ÃæÏÔʾ×ÜÐÐÊý
1) ²é¿´Ä¿Ç°µÄpagesize,ĬÈÏÊÇ14:
Sqlplus´úÂë
1. show pagesize;
show pagesize;
2) ½«pagesizeÉèÖúÃ100,Ôò¿ÉÒÔÒ»´ÎÏÔʾ¹»¶àÐмǼÁË:
Sqlplus´úÂë
1. set pagesize 1 ......
2010Äê5ÔÂ25ÈÕ ¸»Ê¿¿µ11ÌøÂ¥×ÔɱµÄÕâÌì¡£´«ÎÅoracle 10g r203×Ô¶¯·ÖÎö×÷Òµ²»ÊǺܿ¿Æ×,¾ßÌåΪʲô²»µÃ¶øÖª£¡½ñÌìÀÏ¿âµÄ»úÆ÷Òª±»°á×ߣ¬¿´µ½ÉÏÃæÓиöĿ¼ÊÇDBAµ¼Êý¾Ý¶øÓõģ¬»¹Áô×ÅÀÏ¿âǨµ½Ð¿âµÄDMPÊý¾ÝÎļþ309G¡£ÏëÀÏ¿âÕæÕýÓõ½µÄ±í±È½ÏÉÙ£¬Êý¾ÝÁ¿»áСºÜ¶à¡£ÎªÁËÖªµÀ¾ßÌåÁ¿£¬ËùÒÔ×Ô¼ºÊÔÍæÏÂEXPµ¼Êý¾Ý¡£ÏÈÊÔÁËÏÂÓû§±í590Íò1. ......
²é¿´Ëø±í½ø³ÌSQLÓï¾ä1£º
select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo,
dba_ ......
Q£ºÎҵıíÖÐÓÐÒ»¸ö×Ö¶ÎÊÇvarchar2(4000) £¬ÆäÖд洢Á˲»ÉÙÖÐÎÄ£¬ÏÖÔÚÓÉÓÚÅÅ°æÐèÒª£¬ÎÒÏëÕÒ³öº¬Óлسµ·ûµÄ¼Ç¼ɾ³ý»Ø³µ·û£¬ÇëÎʸßÊÖSQLÓï·¨Ôõôд£¿
A£ºOracleÖлسµ·ûÊÇchr(10),¿ÉÒÔʹÓú¯Êýreplace(×Ö¶Î,chr(10),'')
È磺 update x set a=replace(a,chr(10),'')¡£
º¯Êý£º
1.CHR
¸ø³öÕûÊ ......