Oralce DDL-related learning (a) - SEQUENCE learning

2011-08-27  来源:本站原创  分类:Database  人气:94 

- First create a user table as a test case

Create  table  t_user(
Id number(6),
userid varchar2(20),
loginpassword varchar2(20),
isdisable number(6)
)
TABLESPACE "TONY";

- Create a sequence from the growth management,
- In the sequence should be noted:
- 1, the first NEXTVAL returns the initial value; subsequent NEXTVAL will automatically increase your definition of INCREMENT BY value,
- And then return to the increased value. CURRVAL SEQUENCE always returns the current value, but after the first NEXTVAL initialized to use CURRVAL,
- Or an error occurs. A NEXTVAL will increase the value of a SEQUENCE, so if you are in the same statement, which use multiple NEXTVAL, its value is not the same.
- 2, if you specify CACHE value, ORACLE can place some advance in the memory sequence, so that access faster.
- Cache inside to take after, oracle automatically then take a group to the cache. The use of cache may jump number,
- Such as database sudden abnormal down fall (shutdown abort), cache in the sequence will be lost.
- So when you can create sequence with nocache to prevent this

create sequence user_seq
increment by 1
start with 1
nomaxvalue
nominvalue
nocache;

- Create trigger management just created sequenceuser_seq

create or  replace trigger tr_user
before insert on t_user
for each row
begin
select user_seq.nextval into : new.id from dual;
end;

- Insert data
insert into t_user (userid, loginpassword, isdisable)
values ​​('tony', '123 ', 0);
insert into t_user (userid, loginpassword, isdisable)
values ​​('jack', '123 ', 0);
- Query Verify, success ...
SELECT * FROM t_user;

- Call it a day, oracle management is complex and cumbersome, but for sure that this management must be improved for performance, otherwise it will and sqlserver, mysql in agreement

相关文章
  • Oralce DDL-related learning (a) - SEQUENCE learning 2011-08-27

    - First create a user table as a test case Create table t_user( Id number(6), userid varchar2(20), loginpassword varchar2(20), isdisable number(6) ) TABLESPACE "TONY"; - Create a sequence from the growth management, - In the sequence should be n

  • Database Objects - Sequence Learning Notes 2010-07-22

    1. Create SEQUENCE CREATE SEQUENCE sequence_name [START WITH n1] [INCREMENT BY n2] [(MAXVALUE n3 | NOMAXVALUE)] [(MINVALUE n4 | NOMINVALUE)] [(CACHE n5 | NOCACHE)] [(CYCLE | NOCYCLE)] [ORDER]; n1 ~ n5 is an integer CACHE is used to specify the cache

  • oracle sequence learning in 2011-01-19

    Sequence: Sequence is used to generate unique, sequential integer database objects in Oracle without identity constraints, usually on the use of sequences to automatically generate primary key or unique key value. Sequence can be sorted in ascending

  • Issues related to query sequence 2011-01-14

    WITH T AS ( SELECT 'C1' COL1, 'A1' COL2 FROM DUAL UNION ALL SELECT 'C2' COL1, 'B1' COL2 FROM DUAL UNION ALL SELECT 'C2' COL1, 'B2' COL2 FROM DUAL UNION ALL SELECT 'C2' COL1, 'B3' COL2 FROM DUAL UNION ALL SELECT 'C1' COL1, 'A2' COL2 FROM DUAL UNION AL

  • Basic knowledge of learning 2009-07-10

    Learning to learn Java first line j2se To learn j2ee must first learn j2se, just beginning to learn is not recommended to use j2se first IDE, then gradually transition to the use of the IDE development, after all, why use it conveniently. J2se study

  • Spring Annotations Learning Letters (5) business tier transaction processing 2010-02-09

    Controller layer, persistence layer has a number of presentations, the rest is the business layer of the! Key issues in the business layer is the transaction control! Spring annotation-type transaction processing is actually very simple! Related refe

  • Spring Annotations Learning Letters (6) test 2010-02-13

    Since the system is based on notes self-contained, then the test based on whether it can rely on Spring annotation easy to complete? Resolutely No problem! Spring-Test duty-bound to complete this task! And, through the Spring-Test of the transaction

  • Spring Annotations Learning Letters (d) Analysis of persistence layer 2010-03-29

    Today, we play with the database persistence layer to make trouble. Not engage in anything too complicated, Spring annotations for the persistence layer of the transformation is not hard to understand! We use the most direct interpretation of Spring

  • Java Learning Path 2010-03-29

    First learn j2se To learn j2ee we must first learn j2se, has just started to learn j2se first recommended not to use IDE, then gradually transition to the use of IDE development, after all, easy to use it Well. Learning j2se recommend two books, "jav

  • Learning Java and after some reflection on how to continue the journey Java 2010-07-06

    Java - the last 10 years in the development of computer software legend, many developers in the status of mind as "Tulong Dao," "Heavenly Sword." Java is a platform, I would just like to learn Java, my point view, want to help beginner

  • Spring Annotations Learning Letters (1) build a simple Web application 2010-08-04

    This series of study notes are derived from Daniel's blog: http://snowolf.javaeye.com/blog/577989 Recent work has changed, it is necessary to learn about the Spring annotation! Found some examples, more general, soil, mostly reprinted excerpt, follow

  • Spring Annotations Learning Letters (2) control layer comb 2010-08-04

    Spring notes yesterday have a whole understanding, at least to complete a simple web application structure. Of course, not perfect, this is only the beginning! Today saw Spring 3.0 annotations, I feel that they have been subverted. Years ago, in orde

  • Spring Annotations Learning Letters (c) the form processing page 2010-08-04

    Xiao Xie day yesterday, watching the two blog skyrocketing hits, very pleased. Today to look at the form page handling. Related reference: Spring Annotations Learning Letters (1) build a simple Web application Spring Annotations Learning Letters (2)

  • Spring Annotations Learning Letters (4) persistence of 2010-08-04

    Today, we play with the database and work on the persistence layer. Not to pursue things too complicated, Spring annotations for the persistence of the transformation is not hard to understand! We use the most direct interpretation of Spring JdbcTemp

  • Spring Annotations learning Letters (5) business level transaction 2010-08-04

    Controller layer, persistence layer has a number of introduction, the rest is business layer it! Business layer of the key problem is that transaction control! Spring's annotation-type transaction processing is simple! Related reference: Spring Annot

  • Six points of Java language learning 2010-12-10

    Rookie entry: Java language learning six points Learning Java is more complicated, mainly in the related series of platforms, standards and protocols. Experienced Java programmers are aware, only master the Java language itself is difficult to develo

  • sap learning website 2010-12-29

    SAP Learning Website SAP Learning Website Daquan: 1.SAP Chinese official website: http://www.sap.com/china/index.epx 2.SAP online help: http://help.sap.com/ 3.SAP Service Marketplace: http://service.sap.com 4.SAP Fans Forum: http://www.sapfans.com/ 5

  • Pattern recognition and machine learning notes Chapter linear regression model 2011-04-27

    Linear Regression Model Zhe Zhang third chapter Jieshao linear regression model, the goal is to return to the issue to a D-Wei input variables, to predict a continuous variable or more target values. The first chapter has introduced the polynomial cu

  • Blackboard Online Learning Management Platform 2011-02-19

    Blackboard Online Learning Management Platform 1, "Blackboard" Product Overview Blackboard Online Learning Management Platform is currently on the market only support one million users of the teaching platform. Holds nearly 50% market share. The

  • Experiential learning 2011-07-29

    Prior statement, which I did not write, but we might look smile. I have always believed that for a beginner, IT sector, the wave can not catch up with the technology, but can not afford to go to catch up. I always saw my DDMM who threw the textbooks,