إنتقال للمحتوى

  • تسجيل الدخول عبر الفيس بوك تسجيل الدخول عبر تويتر Log In with LinkedIn Log In with Google      تسجيل دخول    
  • إنشاء حساب

صورة
- - - - -

كيف يمكنني استخراج القيمة الحالية لـsequence


3 رد (ردود) على هذا الموضوع

#1 abo_mahmoud

abo_mahmoud

    عضو

  • الأعضــاء
  • 30 مشاركة

تاريخ المشاركة 21 February 2007 - 01:50 AM

كيف يمكنني استخراج القيمة الحالية لـsequence بدون عمل nextval في نفس جلسة العمل .............

مع العلم أنه في dba_sequences يوجد last number والذي هو آخر رقم متاح
وهو في حالة الـcache آخر رقم في الـ cache .......
أفيدوني

#2 -=|mOOdY|=-

-=|mOOdY|=-

    مشرف سابق وعضو مميز

  • المجموعة الماسية
  • 1,324 مشاركة

تاريخ المشاركة 21 February 2007 - 03:21 AM

SELECT SEQUENC_NAME.CURRVAL  FROM DUAL;

صورة

!!! كم أنت عبقري !!!


#3 abo_mahmoud

abo_mahmoud

    عضو

  • الأعضــاء
  • 30 مشاركة

تاريخ المشاركة 22 February 2007 - 03:04 AM

إن الحل لمشكلتي هي كالتالي: (أرجو أن تكونوا قد فهمتم مشكلتي)
ALTER SEQUENCE S NOCACHE;
--Get the next value
SELECT LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'S';
ALTER SEQUENCE S CACHE 20;
--assuming 20 is the original cache size
أتمنى من لديه حل آخر ليقترحه علينا

#4 أسامة موسى

أسامة موسى

    مشرف سابق وعضو مميز

  • المجموعة الماسية
  • 2,381 مشاركة
  • البـلـد: Country Flag

تاريخ المشاركة 22 February 2007 - 08:43 AM


Oracle/PLSQL: Sequences (Autonumber)

--------------------------------------------------------------------------------

In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

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;

This would create a sequence object called supplier_seq. The first sequence number that it would use is 1 and each subsequent number would increment by 1 (ie: 2,3,4,...}. It will cache up to 20 values for performance.

If you omit the MAXVALUE option, your sequence will automatically default to:

MAXVALUE 999999999999999999999999999

So you can simplify your CREATE SEQUENCE command as follows:

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

Now that you've created a sequence object to simulate an autonumber field, we'll cover how to retrieve a value from this sequence object. To retrieve the next value in the sequence order, you need to use nextval.

For example:

supplier_seq.nextval

This would retrieve the next value from supplier_seq. The nextval statement needs to be used in an SQL statement. For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(supplier_seq.nextval, 'Kraft Foods');

This insert statement would insert a new record into the suppliers table. The supplier_id field would be assigned the next number from the supplier_seq sequence. The supplier_name field would be set to Kraft Foods.



Frequently Asked Questions

--------------------------------------------------------------------------------

One common question about sequences is:

Question: While creating a sequence, what does cache and nocache options mean? For example, you could create a sequence with a cache of 20 as follows:

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



Or you could create the same sequence with the nocache option:

CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
NOCACHE;



Answer: With respect to a sequence, the cache option specifies how many sequence values will be stored in memory for faster access.

The downside of creating a sequence with a cache is that if a system failure occurs, all cached sequence values that have not be used, will be "lost". This results in a "gap" in the assigned sequence values. When the system comes back up, Oracle will cache new numbers from where it left off in the sequence, ignoring the so called "lost" sequence values.

Note: To recover the lost sequence values, you can always execute an ALTER SEQUENCE command to reset the counter to the correct value.

Nocache means that none of the sequence values are stored in memory. This option may sacrifice some performance, however, you should not encounter a gap in the assigned sequence values.



--------------------------------------------------------------------------------

Question: How do we set the LASTVALUE value in an Oracle Sequence?

Answer: You can change the LASTVALUE for an Oracle sequence, by executing an ALTER SEQUENCE command.

For example, if the last value used by the Oracle sequence was 100 and you would like to reset the sequence to serve 225 as the next value. You would execute the following commands.

alter sequence seq_name
increment by 124;

select seq_name.nextval from dual;

alter sequence seq_name
increment by 1;

Now, the next value to be served by the sequence will be 225.


۩ اللهـــــــــــــــــــــــم صلي على سيدنا محمد وعلـى آله وصحبه أجمـعين ۩
۩ سبحـــــــــــــــــان الله وبحمده ....... سبحــــــــــــــــــــــــان الله العظيم ۩



يمكنك زيارتي على صفحتي الخاصة بالفيس بوك على الرابط AskOsama

يمكنك زيارتي على مدونتي الجديدة على الرابط http://askossama.blogspot.com