OracleÊý¾Ý¿âÖÐÐòÁÐÓ÷¨½²½â
ÐòÁÐ(SEQUENCE)ÊÇÐòÁкÅÉú³ÉÆ÷£¬¿ÉÒÔΪ±íÖеÄÐÐ×Ô¶¯Éú³ÉÐòÁкţ¬²úÉúÒ»×éµÈ¼ä¸ôµÄÊýÖµ(ÀàÐÍΪÊý×Ö)¡£ÆäÖ÷ÒªµÄÓÃ;ÊÇÉú³É±íµÄÖ÷¼üÖµ£¬¿ÉÒÔÔÚ²åÈëÓï¾äÖÐÒýÓã¬Ò²¿ÉÒÔͨ¹ý²éѯ¼ì²éµ±Ç°Öµ£¬»òʹÐòÁÐÔöÖÁÏÂÒ»¸öÖµ¡£
´´½¨ÐòÁÐÐèÒªCREATE SEQUENCEϵͳȨÏÞ¡£ÐòÁеĴ´½¨Óï·¨ÈçÏ£º
CREATE SEQUENCE ÐòÁÐÃû
[INCREMENT BY n]
[START WITH n]
[MAXVALUE/ MINVALUE nNOMAXVALUE]
[CYCLENOCYCLE]
[CACHE nNOCACHE];
ÆäÖУº
INCREMENT BY ÓÃÓÚ¶¨ÒåÐòÁеIJ½³¤£¬Èç¹ûÊ¡ÂÔ£¬ÔòĬÈÏΪ1£¬Èç¹û³öÏÖ¸ºÖµ£¬Ôò´ú±íÐòÁеÄÖµÊÇ°´Õմ˲½³¤µÝ¼õµÄ¡£
START WITH ¶¨ÒåÐòÁеijõʼֵ(¼´²úÉúµÄµÚÒ»¸öÖµ)£¬Ä¬ÈÏΪ1¡£
MAXVALUE¶¨ÒåÐòÁÐÉú³ÉÆ÷ÄܲúÉúµÄ×î´óÖµ¡£Ñ¡ÏîNOMAXVALUEÊÇĬÈÏÑ¡Ï´ú±íûÓÐ×î´óÖµ¶¨Ò壬Õâʱ¶ÔÓÚµÝÔöÐòÁУ¬ÏµÍ³Äܹ»²úÉúµÄ×î´óÖµÊÇ10µÄ27´Î·½;¶ÔÓڵݼõÐòÁУ¬×î´óÖµÊÇ-1¡£
MINVALUE¶¨ÒåÐòÁÐÉú³ÉÆ÷ÄܲúÉúµÄ×îСֵ¡£Ñ¡ÏîNOMAXVALUEÊÇĬÈÏÑ¡Ï´ú±íûÓÐ×îСֵ¶¨Ò壬Õâʱ¶ÔÓڵݼõÐòÁУ¬ÏµÍ³Äܹ»²úÉúµÄ×îСֵÊÇ?10µÄ26´Î·½;¶ÔÓÚµÝÔöÐòÁУ¬×îСֵÊÇ1¡£
CYCLEºÍNOCYCLE±íʾµ±ÐòÁÐÉú³ÉÆ÷µÄÖµ´ïµ½ÏÞÖÆÖµºóÊÇ·ñÑ»·¡£CYCLE´ú±íÑ»·£¬NOCYCLE´ú±í²»Ñ»·¡£Èç¹ûÑ»·£¬Ôòµ±µÝÔöÐòÁдﵽ×î´óֵʱ£¬Ñ»·µ½×îСֵ;¶ÔÓڵݼõÐòÁдﵽ×îСֵʱ£¬Ñ»·µ½×î´óÖµ¡£Èç¹û²»Ñ»·£¬´ïµ½ÏÞÖÆÖµºó£¬¼ÌÐø²úÉúÐÂÖµ¾Í»á·¢Éú´íÎó¡£
CACHE(»º³å)¶¨Òå´æ·ÅÐòÁеÄÄÚ´æ¿éµÄ´óС£¬Ä¬ÈÏΪ20¡£NOCACHE±íʾ²»¶ÔÐòÁнøÐÐÄڴ滺³å¡£¶ÔÐòÁнøÐÐÄڴ滺³å£¬¿ÉÒÔ¸ÄÉÆÐòÁеÄÐÔÄÜ¡£
ɾ³ýÐòÁеÄÓï·¨ÊÇ£º
DROP SEQUENCE ÐòÁÐÃû;
ɾ³ýÐòÁеÄÈËÓ¦¸ÃÊÇÐòÁеĴ´½¨Õß»òÓµÓÐDROP ANY SEQUENCEϵͳȨÏÞµÄÓû§¡£ÐòÁÐÒ»µ©É¾³ý¾Í²»Äܱ»ÒýÓÃÁË¡£
ÐòÁеÄijЩ²¿·ÖÒ²¿ÉÒÔÔÚʹÓÃÖнøÐÐÐ޸ģ¬µ«²»ÄÜÐÞ¸ÄSATRTWITHÑ¡Ïî¡£¶ÔÐòÁеÄÐÞ¸ÄÖ»Ó°ÏìËæºó²úÉúµÄÐòºÅ£¬ÒѾ²úÉúµÄÐòºÅ²»±ä¡£ÐÞ¸ÄÐòÁеÄÓï·¨ÈçÏ£º
´´½¨ºÍɾ³ýÐòÁÐ
Àý1£º´´½¨ÐòÁУº
CREATE SEQUENCE ABC INCREMENT BY 1 START WITH 10 MAXVALUE9999999NOCYCLE NOCACHE;
Ö´Ðнá¹û£º
ÐòÁÐÒÑ´´½¨¡£
²½Öè2£ºÉ¾³ýÐòÁУº
DROP SEQUENCE ABC;
Ö´Ðнá¹û£º
ÐòÁÐÒѶªÆú¡£
˵Ã÷£ºÒÔÉÏ´´½¨µÄÐòÁÐÃûΪABC£¬ÊǵÝÔöÐòÁУ¬ÔöÁ¿Îª1£¬³õʼֵΪ10¡£¸ÃÐòÁв»Ñ»·£¬²»Ê¹ÓÃÄڴ档ûÓж¨Òå×îСֵ£¬Ä¬ÈÏ×îСֵΪ1£¬×î´óֵΪ9999999¡£
ÐòÁеÄʹÓÃ
Èç¹ûÒѾ´´½¨ÁËÐòÁУ¬ÔõÑù²ÅÄÜÒýÓÃÐòÁÐÄØ?·½·¨ÊÇʹÓÃCURRVALºÍNEXTVALÀ´ÒýÓÃÐòÁеÄÖµ¡£
ÔÚ±àºÅµÄ¹ý³ÌÖУ¬²úÉú¼ä϶µÄÔÒò¶àÖÖ¶àÑù¡£Èç¹ûÒ
Ïà¹ØÎĵµ£º
Case¾ßÓÐÁ½ÖÖ¸ñʽ¡£¼òµ¥Caseº¯ÊýºÍCaseËÑË÷º¯Êý¡£
--¼òµ¥Caseº¯Êý
CASE sex
WHEN '1' THEN 'ÄÐ'
WHEN '2' THEN 'Å®'
ELSE 'ÆäËû' END
--CaseËÑË÷º¯Êý
CASE WHEN sex = '1' THEN 'ÄÐ'
WHEN sex = '2' THEN 'Å®'
ELSE 'ÆäËû' END
ÕâÁ½ÖÖ·½Ê½£¬¿ÉÒÔʵÏÖÏàͬµÄ¹¦ÄÜ¡£¼òµ¥Caseº¯ÊýµÄд·¨Ïà¶Ô±È½Ï¼ò½à ......
Êý¾Ý×Öµädict×ÜÊÇÊôÓÚOracleÓû§sysµÄ¡£
1¡¢Óû§£º
¡¡select username from dba_users;
¸Ä¿ÚÁî
¡¡alter user spgroup identified by spgtest;
2¡¢±í¿Õ¼ä£º
¡¡select * from dba_data_files;
¡¡select * from dba_tablespaces;//±í¿Õ¼ä
¡¡select tablespace_name,sum(bytes), sum(blocks)
from dba_ ......
DML:Data Manipulation Language Êý¾Ý²Ù×÷ÓïÑÔ
°üÀ¨£ºCRUD
1. insertÓï¾ä
(1) ´ÓÆäËü±íÖи´ÖÆÊý¾Ý,ʵÏÖ·½·¨:ÔÚinsert Óï¾äÖмÓÈë²éѯÓï¾ä
insert into sales_reps(id,name,salary,commission_pct) select employee_id,last_name,salary,commission_pct
from employees where job_id like '%rep';
(2) up ......
SQLÖеĵ¥¼Ç¼º¯Êý
Ò»¡¢×Ö·ûº¯Êý
1.ASCII
·µ»ØÓëÖ¸¶¨µÄ×Ö·û¶ÔÓ¦µÄÊ®½øÖÆÊý;
SQL> select ascii(’A’) A,ascii(’a’) a,ascii(’0’) zero,ascii(’ ’) space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32
......
¡¡Ò»¡¢ÉèÖóõʼ»¯²ÎÊý job_queue_processes
¡¡¡¡sql> alter system set job_queue_processes=n;£¨n>0£©
¡¡¡¡job_queue_processes×î´óֵΪ1000
¡¡¡¡
¡¡¡¡²é¿´job queue ºǫ́½ø³Ì
¡¡¡¡sql>select name,description from v$bgprocess;
¡¡¡¡
¡¡¡¡¶þ£¬dbms_job package Ó÷¨½éÉÜ
¡¡¡¡°üº¬ÒÔÏÂ×Ó¹ý³Ì£º
......