In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
Few people know about sequences, because both Mysql and sql server have auto-grow fields (such as integers that automatically grow the primary key of ID), but Oracle does not provide this usage, so Oracle provides a more flexible and convenient strategy-creating object sequences.
The sequence is also real after it is created, stored on our disk and can be called in applicable situations. The sequence always starts with the specified length, and after it is created, it can be accumulated according to the specified step size to obtain a new integer.
Format:
Create sequence emp_seq
Explanation:
Create a sequence that is often identified by the suffix seq
Format:
Select object_name,object_type,status from user_objects where lower (object_name) = 'emp_seq'
Explanation:
It turns out that it is a serious object, so we can use the database user_objects.
Format:
Select sequence_name,min_value,max_value,increment_by from user_sequences where lower (sequence_name) = 'emp_seq'
Explanation:
Used to view the details of the sequence we created, we suddenly found that we can also see the maximum value, the minimum value, the step size of each increase.
Use sequenc
If you don't have to create it for nothing, you need to understand two values before using it. The first currval and the second nextval are clear in English. Currval gets the current value and nextval gets the next value. Each call to nextval will increase the unit step size of the current sequence (default is 1).
The two attribute methods of the calling sequence are seq.currval and seq.nextval. Use the sequence for the first time. Note that you have to call the property of next first, otherwise you will report an error!
Format:
Select emp_seq.nextval from dual
Explanation:
We use the virtual table dual as the from source of seq.nextval, and the single-line output shows that the result will be 1. 5.
Because the value of nextval defaults to the whole sheet, we need to make sure that the table ID is the primary key
Format:
Alter table student_name modify (id number primary key)
Format:
Insert into student (id,name) values (emp_seq,' Hello',)
Explanation:
If you already have data in the student table, you will be surprised to find that it failed when we directly called the created sequence when we inserted the id. What's the matter, we use select emp_seq.currval from dual;-- to find that the data is 2, there are already 2 in the table, of course not, how to do it is impossible to put the original data (this column deleted), the small amount of data can also be used, trouble not to say, can not show the power of the sequence! Next we introduce how to set the initial value. There are already 1000 items of id value in this table. It is not better and more efficient to let it increase by default starting from 1000.
Format:
Drop sequence emp_seq;-- deletes the original sequence
Create sequence emp_seq start with 1001
Explanation:
The keyword start with English word understanding is the beginning, then it means that the sequence is automatically increased from 1001 (the default is 1), and the starting value for insertion is 1001 so that we can use the sequence according to our needs, which is very convenient, simple and efficient.
Format:
Alter sequence emp_seq minvalue 8888Mutual-modify the minimum value, note that the current modified minimum value cannot be greater than the value in the original table (1001)!
Alter sequence emp_seq maxvalue 9999Mutual-modify the largest only
Format:
Alter sequence emp_seq increment by 5
Explanation:
This is the default growth pace that we have been talking about earlier, that is, how much the id value automatically increases by default each time you insert it.
In this place, we should pay attention to the problem of setting the maximum value. If my maximum value is 20, I can only increase 5 at a time from 1 to 17, and the fourth time will exceed the maximum value to report an error.
So when I exceed the maximum value, I can't, if I exceed the maximum value, let it become the minimum value, re-cycle, yes, we are the operators, as long as we want, he has to be able to, here is a keyword to perform this operation for us.
Format:
Create sequence emp_seq
Start with 5
Minvalue 1
Maxvalue 30
Incremenet by 1
/-- end of creation
At this time, we sort of used all the parameters we learned before. it's actually very easy to take a look. How difficult is it? we started to use the nextval attribute value many times until we used currval to see that the current maximum value is 30. When we use nextval, we will miraculously find that it becomes 1.
Format:
Alter sequence emp_seq cycle;-- is enabled
Alter sequence emp_sql nocycle;-- shuts down
Cache, what is cache, cache is better to read data, the speed is much faster than memory, then the sequence also has cache, a large number of data can have caching effect for the database as a whole is undoubtedly a great improvement.
Then the cache of the sequence, using nextval each time, does not operate directly, but acquires the cache of multiple lists at one time, fetches the values from the cache, and depends on currval and step increment.
Define the size of the memory block in which the sequence is stored, prefix it into a serial number, store it in the memory block, use the next sequence number to give a quick response, and then generate another group when one group is used up. this can improve the efficiency of generating serial numbers, the default value is 20.
Format:
Alter sequence emp_seq cache 10
Explanation:
Set the cache size
Yesterday in the work, added a sequence id in the table, at that time the amount of data is relatively small, and then insert into the data table column, if the data amount of this method is not feasible and particularly troublesome, then today's learning sequence will be of great use!
Small sequences are not simple. It really takes a lot of knowledge to make good use of them. I hope you can put what you have learned into practice.
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.