Oracle¶¨ÒåÔ¼Êø Íâ¼üÔ¼Êø
Íâ¼üÔ¼Êø±£Ö¤²ÎÕÕÍêÕûÐÔ¡£Íâ¼üÔ¼ÊøÏÞ¶¨ÁËÒ»¸öÁеÄȡֵ·¶Î§¡£Ò»¸öÀý×Ó¾ÍÊÇÏÞ¶¨ÖÝÃûËõдÔÚÒ»¸öÓÐÏÞÖµ¼¯ºÏÖУ¬Õâ¸öÖµ¼¯ºÏÊÇÁíÍâÒ»¸ö¿ØÖƽṹ——Ò»ÕŸ¸±í
ÏÂÃæÎÒÃÇ´´½¨Ò»ÕŲÎÕÕ±í£¬ËüÌṩÁËÍêÕûµÄÖÝËõдÁÐ±í£¬È»ºóʹÓòÎÕÕÍêÕûÐÔÈ·±£Ñ§ÉúÃÇÓÐÕýÈ·µÄÖÝËõд¡£µÚÒ»ÕűíÊÇÖݲÎÕÕ±í£¬State×÷ΪÖ÷¼ü
CREATE TABLE state_lookup
(state VARCHAR2(2),
state_desc VARCHAR2(30)) TABLESPACE student_data;
ALTER TABLE state_lookup
ADD CONSTRAINT pk_state_lookup PRIMARY KEY (state)
USING INDEX TABLESPACE student_index;
È»ºó²åÈ뼸ÐмǼ£º
INSERT INTO state_lookup VALUES ('CA', 'California');
INSERT INTO state_lookup VALUES ('NY', 'New York');
INSERT INTO state_lookup VALUES ('NC', 'North Carolina');
ÎÒÃÇͨ¹ýʵÏÖ¸¸×Ó¹ØÏµÀ´±£Ö¤²ÎÕÕÍêÕûÐÔ£¬Í¼Ê¾ÈçÏÂ
--------------- Íâ¼ü×ֶδæÔÚÓÚStudents±íÖÐ
|State_lookup | ÊÇState×Ö¶Î
--------------- Ò»¸öÍâ¼ü±ØÐë²ÎÕÕÖ÷¼ü»òUnique×Ö¶Î
| Õâ¸öÀý×ÓÖУ¬ÎÒÃDzÎÕÕµÄÊÇState×Ö¶Î
| ËüÊÇÒ»¸öÖ÷¼ü×ֶΣ¨²Î¿´DDL£©
/|\
---------------
| Students |
---------------
ÉÏͼÏÔʾÁËState_Lookup±íºÍStudents±í¼äÒ»¶Ô¶àµÄ¹ØÏµ£¬State_Lookup±í¶¨ÒåÁËÖÝËõдͨÓü¯ºÏ——ÔÚ±íÖÐÿһ¸öÖݳöÏÖÒ»´Î¡£Òò´Ë£¬State_Lookup±íµÄÖ÷¼üÊÇState×ֶΡ£
State_Lookup±íÖеÄÒ»¸öÖÝÃû¿ÉÒÔÔÚStudents±íÖгöÏÖ¶à´Î¡£ÓÐÐí¶àѧÉúÀ´×Ôͬһ¸öÖÝ£¬Ò»´Î£¬ÔÚ±íState_LookupºÍStudentsÖ®¼ä²ÎÕÕÍêÕûÐÔʵÏÖÁËÒ»¶Ô¶àµÄ¹ØÏµ¡£
Íâ¼üͬʱ±£Ö¤Students±íÖÐState×ֶεÄÍêÕûÐÔ¡£Ã¿Ò»¸öѧÉú×ÜÊÇÓиöState_lookup±íÖгÉÔ±µÄÖÝËõд¡£
Íâ¼üÔ¼Êø´´½¨ÔÚ×Ó±í¡£ÏÂÃæÔÚstudents±íÉÏ´´½¨Ò»¸öÍâ¼üÔ¼Êø¡£State×ֶβÎÕÕstate_lookup±íµÄÖ÷¼ü¡£
1¡¢´´½¨±í
CREATE TABLE students
(student_id&n
Ïà¹ØÎĵµ£º
1.¾ø¶ÔÖµ
S:select abs(-1) value
O:select abs(-1) value from dual
2.È¡Õû(´ó)
S:select ceiling(-1.001) value
O:select ceil(-1.001) value from dual
3.È¡Õû£¨Ð¡£©
S:select floor(-1.001) value
O:select floor(-1.001) value from dual
4.È ......
1.¾ø¶ÔÖµ
S:select abs(-1) value
O:select abs(-1) value from dual
2.È¡Õû(´ó)
S:select ceiling(-1.001) value
O:select ceil(-1.001) value from dual
3.È¡Õû£¨Ð¡£©
S:select floor(-1.001) value
O:select floor(-1.001) value from dual
4.È¡Õû£¨½ØÈ¡£©
S:select cast(-1.002 as int) value
O:selec ......
BLOBת»»ÎªCLOBµÄº¯Êý£¨oracleÖÐÖ´ÐУ©
CREATE OR REPLACE FUNCTION BlobToClob(blob_in IN BLOB) RETURN CLOB AS
v_clob CLOB;
v_varchar VARCHAR2(32767);
v_start PLS_INTEGER := 1;
v_buffer PLS_INTEGER := 32767;
BEGIN
DBMS_LOB.CRE ......
Oracle Connect ByÓ÷¨
oracleÖеÄselectÓï¾ä¿ÉÒÔÓÃSTART WITH...CONNECT BY PRIOR×Ó¾äʵÏֵݹé²éѯ£¬connect by Êǽṹ»¯²éѯÖÐÓõ½µÄ£¬Æä»ù±¾Óï·¨ÊÇ£º
select ... from <TableName>
where <Conditional-1>
start with <Conditional-2>
connect by <Conditional-3>
;
<Conditi ......