oracle¼òµ¥¶ÔÏóÀàÐÍ
ÔÚplsqlÖУ¬ÃæÏò¶ÔÏóµÄ³ÌÐòÉè¼ÆÊÇ»ùÓÚ¶ÔÏóÀàÐÍÀ´Íê³ÉµÄ¡£¶ÔÏóÀàÐÍÊÇÓû§×Ô¶¨ÒåµÄÒ»ÖÖ¸´ºÏÊý¾ÝÀàÐÍ£¬Ëü·â×°ÁËÊý¾Ý½á¹¹ºÍÓÃÓÚ²Ù×ÝÕâЩÊý¾Ý½á¹¹µÄ¹ý³ÌºÍº¯Êý£¬ÔÚ½¨Á¢¸´ÔÓÓ¦ÓóÌÐòʱ£¬Í¨¹ýʹÓöÔÏóÀàÐÍ¿ÉÒÔ½µµÍÓ¦Óÿª·¢ÄѶȣ¬½ø¶øÌá¸ßÓ¦Óÿª·¢µÄЧÂʺÍËÙ¶È¡£
¶ÔÏóÀàÐͰüÀ¨¶ÔÏóÀàÐ͹淶£¨object type specification£©ºÍ¶ÔÏóÀàÐÍÌ壨object type body£©Á½¸ö²¿·Ö¡£ÆäÖжÔÏóÀàÐ͹淶ÊǶÔÏóÓëÓ¦ÓõĽӿڣ¬ËüÓÃÓÚ¶¨Òå¶ÔÏóµÄ¹«ÓÃÊôÐԺͷ½·¨£»¶ø¶ÔÏóÀàÐÍÌåÔòÓÃÓÚʵÏÖ¶ÔÏóÀàÐ͹淶Ëù¶¨ÒåµÄ¹«Ó÷½·¨£¬Èç¹û¶ÔÏóÀàÐ͹淶ÖÐûÓж¨Òå·½·¨£¬Ôò¿ÉÒÔ²»¶¨Òå¶ÔÏóÀàÐÍÌå¡£
¶ÔÏóÀàÐÍÊôÐÔÓÃÓÚÃèÊö¶ÔÏóËù¾ßÓеÄÌØÕ÷£¬Ã¿¸ö¶ÔÏóÀàÐÍÖÁÉÙ°üº¬Ò»¸öÊôÐÔ£¬ÖÁ¶à¿ÉÒÔ°üº¬1000¸öÊôÐÔ¡£¶ÔÏóÀàÐ͵ÄÊôÐÔ²»ÄÜʹÓÃlong¡¢long raw¡¢rowid¡¢urowid¡¢plsqlµÄÌØÓÐÀàÐÍ£¨binary_integer,Boolean,%type,%rowtype,ref cursor,record,pls_integer£©µÈ¡£²¢ÇÒÔÚ¶¨Òå¶ÔÏóÀàÐÍÊôÐÔʱ£¬¼È²»ÄÜÖ¸¶¨¶ÔÏóÊôÐÔµÄĬÈÏÖµ£¬Ò²²»ÄÜÖ¸¶¨notnullÑ¡Ïî¡£
ÐжÔÏóÊÇÖ¸Ö±½Ó»ùÓÚ¶ÔÏóÀàÐÍËù½¨Á¢µÄ±í£¬ÁжÔÏóÊÇÖ¸ÔÚ½¨±íʱָ¶¨Á˶ÔÏóÀàÐÍÁеĶÔÏó±í¡£ÏÂÃæ¸ø³öÁ˲Ù×÷ÐжÔÏóµÄ´úÂ룬ÁжÔÏó²Ù×÷·½Ê½ÀàËÆ¡£
ʾÀý1£ºÐжÔÏóÊý¾Ý²Ù×÷£¨°üº¬ÁжÔÏóÊý¾ÝµÄ²Ù×÷ÀàËÆ£©
--½¨Á¢¶ÔÏóÀàÐÍ
create or replace type person_obj as object(
name varchar2(10),
gender number(1),
birthday date);
--½¨Á¢ÐжÔÏ󣨻ùÓÚperson_obj£©
create table person_table of person_obj;
--ΪÐжÔÏó²åÈëÊý¾Ý
insert into person_table values(person_obj('jax01',1,sysdate)); --¹¹Ôì·½·¨
insert into person_table values('jax02',1,sysdate); --ÆÕͨ·½·¨
--¼ìË÷ÐжÔÏóÊý¾Ý
select * from person_table where name = 'jax01';
--¸üÐÂÐжÔÏóÊý¾Ý
update person_table set birthday = to_date('2004-05-12','YYYY-MM-DD')
where gender = 0;
Ïà¹ØÎĵµ£º
´´½¨ÁÙʱ±í¿Õ¼ä
CREATE TEMPORARY TABLESPACE test_temp
TEMPFILE ' C:\oracle\product\10.1.0\oradata\orcl\test_temp01.dbf '
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;
´´½¨Óû§±í¿Õ¼ä
CREATE TABLESPACE test_data
LOGGING
DATAFI ......
¡¡²Ù×÷·ûÓÅ»¯
¡¡¡¡IN ²Ù×÷·û
¡¡¡¡ÓÃINд³öÀ´µÄSQLµÄÓŵãÊDZȽÏÈÝÒ×д¼°ÇåÎúÒ×¶®£¬Õâ±È½ÏÊʺÏÏÖ´úÈí¼þ¿ª·¢µÄ·ç¸ñ¡£
¡¡¡¡µ«ÊÇÓÃINµÄSQLÐÔÄÜ×ÜÊDZȽϵ͵쬴ÓORACLEÖ´ÐеIJ½ÖèÀ´·ÖÎöÓÃINµÄSQLÓë²»ÓÃINµÄSQLÓÐÒÔÏÂÇø±ð£º
¡¡¡¡ORACLEÊÔͼ½«Æäת»»³É¶à¸ö±íµÄÁ¬½Ó£¬Èç¹ûת»»²»³É¹¦ÔòÏÈÖ´ÐÐINÀïÃæµÄ×Ó²éѯ£¬ÔÙ²éѯÍâ²ãµÄ±í¼Ç¼£ ......
·ÖÒ³²éѯ¸ñʽ£º
SELECT * from
(
SELECT A.*, ROWNUM RN
from (SELECT * from TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
ÆäÖÐ×îÄÚ²ãµÄ²éѯSELECT * from TABLE_NAME±íʾ²»½øÐзҳµÄÔʼ²éѯÓï¾ä¡£ROWNUM <= 40ºÍRN >= 21¿ØÖÆ·ÖÒ³²éѯµÄÿҳµÄ·¶Î§¡£
ÉÏÃæ¸ø³öµÄÕâ¸ö·ÖÒ³²éѯÓï¾ä£¬ÔÚ´ó¶à ......
ÎÒÔÚ¹¤×÷ÖÐÔÚOracleÖжà±í²éѯÔÙ°´Ê±¼äµ¹Ðòʱ£¬ÓÃorder by ±íÃû.datatime desc ×ÜÊÇʵÏÖ²»ÁË¡£
²»¹ý×îºóÎÊÑî¾é×îºóºÍÎÒ˵¿ÉÒÔ°ÑÎÒÃDzéѯµÄ×ܸösqlÓï¾ä×öΪһ¸ö±íÀ´ÅÅÐò£º
select rownum,aa.* from (.... order by ±íÃû.datatime desc ) aa ......