الزياده التلقائيه في الأوراكل ؟

[mwr21]

أصدقائي مرحبا

أحتاج مساعده أنشأه جدول و فيه المفاتح الرئيسي هو عدد ولكن أريد أن يزداد تلقائيا

كما في البردوكس إسمه (Autoincrement)

و أن يكون العدد المضاف يحافظ على عدم تكرار هذا العدد


أرجو منكم المساعده

[عبدالله أسعد]

The syntax for a sequence is:

CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;

For example:

CREATE SEQUENCE supplier_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;

[mwr21]

شكرا لك على الرد ولقد أفادني ولكن كيف يتم إستدعاء هذه السلاسل

وما هو المقصود في dual

شكرا لك


كل إحترامي

[Amgad]

بالاضافة لرد الأخ / bedooracle
اليك هذا الشرح من مشاركة سابقه بالمنتدى

What is a Sequence :
A sequence is a database object created by a user and can be shared by multiple users to automatically generate sequence numbers. A typical usage of sequences is to create a primary key value, sequence number are stored and generated independently of tables. Therefore, the same sequence can be used for multiple tables.
CREATE SEQUENCE statement:
Syntax:
CREATE SEQUENCE sequence
[iNCREMENT BY n]
[sTART WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINXVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]

Where
Sequence  the name of the sequence generator.
INCREMENT BY n   the interval between sequence numbers 
n an integer (default 1).
START WITH n    the first sequence number to be generated (default 1).
MAXVALUE  n    the maximum value the sequence can generate
NOMAXVALUE  specifies a maximum value of 10*23 for ascending sequence and –1 for a descending sequence.
MINVALUE  n    specifies the minimum sequence value.
NOMINVALUE  specifies a minimum value of 10 for ascending sequence and –(10*26) for a descending sequence.
CYCLE| NOCYCLE  specifies that the sequence continues to generate values after reach either its maximum or minimum value or does not generate additional values.
CACHEn|NOCACHE  specifies how many values the Oracle Server will preallocate and keep in memory (default 20).

Example :  	
CREATE SEQUENCE  s_emp_id_seq
¬INCREMENT BY 1
START WITH 101
MAXVALUE 999999999
NOCACHE
NOCYCLE;
Notes  :
-	Do not use the CYCLE option when use sequence to create primary key.
-	Caching sequence values in memory allows faster access to those values.
-	Gaps in sequence values can occur when
-	A rollback occurs  	
-	sequence is used in another table
-	Use USER_SEQUENCES view to get information about sequences.

NEXTVAL and CURRVAL Pseudocolumns:
-	NEXTVAL returns the next available sequence values. 
-	It returns a unique value every time it is referenced, even for different users.
-	CURRVAL obtains the current sequence value.
-	NEXTVAL must be issued for that sequence before CURRVAL contains a value.

Rules for using NEXTVAL and CURRVAL:
You can use NEXTVAL and CURRVAL in the following:
-	The SELECT list of a SELECT statement that is not part of a subquery.
-	The SELECT list of a subquery in an INSERT statement.
-	The VALUES clause of an INSERT statement.
-	The SET clause of an UPDATE statement.
You can not  use NEXTVAL and CURRVAL in the following:
-	A SELECT list of a view.
-	A SELECT statement with the DISTINCT keyword.
-	A SELECT statement with the GROUP BY, HAVING, or ORDER BY clauses.
-	A subquery in a SELECT , DELETE, or UPDATE statement.
-	A DEFAULT expression in a CREATE TABLE or ALTER TABLE statement.
Examples:  	
1. SELECT  s_dept_id.nextval
FROM  	SYS.DUAL;
2. SELECT  s_dept_id.currtval
FROM  	SYS.DUAL;
3. SELECT  s_dept_id.nextval,last_name
FROM  	s_emp;
4. INSERT INTO	s_dept
VALUES  	(s_dept_id.nextval, ‘Planning’,2);
5. INSERT INTO	history (h_id, name, dept_id)
select hisotry_id.nextval, last_name, dept_id 
from s_emp;
6. INSERT INTO	emp…
VALUES  	(emp_id.nextval, dept_id.currval,… );

ALTER SEQUENCE statement:
Change the increment value, maximum value, minimum value, cycle option, and cache option.
Syntax:
ALTER SEQUENCE sequence
[iNCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINXVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]
Notes  :
-	Only future sequence numbers are affected by ALTER SEQUENCE statement.
-	The START WITH option con not be changed using ALTER SEQUENCE, the sequence must be dropped and re-created in order by restart the sequence at a different number.
-	Some validation is performed. For example a new MAXVALUE cannot be less than the current sequence number.
DROP SEQUENCE statement:
Remove a sequence by using the DROP SEQUENCE command.
Example:  
DROP SEQUENCE	s_emp_id_seq;
Confirming view names and sturectures:
-	You can get all information about sequences by querying the USER_SEQUENCES table.
Examples:  	
ALTER SEQUENCE	s_emp_id_seq
 	INCREMENT BY 5
CACHE 40
CYCLE;