How to create sequence step by step

Hello Friends in this tutorial i'm going to demonstrate you how to create sequence and how to use ORACLE - SQL sequence step by step.

ORACLE – SQL TUTORIAL STEP BY STEP.

SEQUENCE is an oracle shareable object which is generate numeric values and it can be unique. you can use this value into primary key and unique values.

Let see how to create SEQUENCE and how to user sequence step by step, following below steps.

STEP 1. To create sequence step by step.

SQL> create sequence ocp
     minvalue 10
     start with 10
     increment by 10
     maxvalue 100
     nocycle
     nocache;

Sequence created.

After creating sequence i’m going to create a new table and insert some records into table using this sequence step by step following command.

STEP 2. create a table using following command step by step.

SQL> create table acer(roll_num number,stu_name varchar2(11));

Table created.

Now you done table creation and sequence creation after done that now i’m going to show you how to insert records in that table using sequence, for more clarification you can view my video on my YouTube channel and if get any knowledge from my video so subscribe my channel and like my video and if you want to give any suggestion regarding my contents and my videos so please write in comment box and you can also send my E-mails on my E-mail id ( ocptechno@gmail.com ).

STEP 3.

Let see how to insert records into table using sequence following commands.

SQL> insert into acer values(ocp.nextval,'&stu_name');
Enter value for stu_name: AMIT
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'AMIT'1 row created.SQL> /
Enter value for stu_name: SUMIT
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'SUMIT')

1 row created.

SQL> /
Enter value for stu_name: ARSH
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'ARSH')

1 row created.

SQL> /
Enter value for stu_name: DEEP
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'DEEP')

1 row created.

SQL> /
Enter value for stu_name: SACHIN 
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'SACHIN')

1 row created.

SQL> /
Enter value for stu_name: VIKRAM
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'VIKRAM')

1 row created.

SQL> /
Enter value for stu_name: RAHUL
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'RAHUL')

1 row created.

SQL> /
Enter value for stu_name: AJAY
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'AJAY')

1 row created.

SQL> /
Enter value for stu_name: TANIYA
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'TANIYA')

1 row created.

SQL> /
Enter value for stu_name: SHRIPAL
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'SHRIPAL')

1 row created.

After inserting 10 rows in this table when we are inserting 11th row it’ll be showing an error look like this. because of that maxvalue exceeds.

SQL> /
Enter value for stu_name: ANKUR
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'ANKUR')
insert into acer values(ocp.nextval,'ANKUR')
 *ERROR at line 1:
ORA-08004: sequence OCP.NEXTVAL exceeds MAXVALUE and cannot be instantiated

If you see records from your table so it’s showing like this, using below command step by step.

SQL> SELECT * FROM ACER;

ROLL_NUM STU_NAME
----     --------
 10      AMIT
 20      SUMIT
 30      ARSH
 40      DEEP
 50      SACHIN
 60      VIKRAM
 70      RAHUL
 80      AJAY
 90      TANIYA
 100     SHRIPAL

10 rows selected.

STEP 4.

so if you want to insert another some records into this table, then you have to increase maxvalue, using below command step by step.

SQL> ALTER SEQUENCE OCP
     MAXVALUE 200;

Sequence altered.

After setting max-value of sequence, you can insert more rows into your table.

And if you want to check your sequence current value and coming feature value then you can use below command step by step.

CURRVAL is use for the knowing current value of the sequence.

SQL> SELECT OCP.CURRVAL FROM DUAL;

CURRVAL
----------
 100

NEXTVAL is use for the knowing NEXT feature VALUES.

SQL> SELECT OCP.NEXTVAL FROM DUAL;

NEXTVAL
----------
 110

For more detail please scroll and see YouTube video.

www.ocptechnology.com



www.youtube.com/user/ocptechno

How to use merge command in ORACLE SQL step by step

It was introduce in ORACLE 9i, the ability to conditionally update or insert into a database table. merge statement avoid separate updates, increases the performance and ease of use and is useful in data warehouse applications.

Step 1. create a table using below command then insert some records in this table.

SQL> create table student(id number,name varchar2(22), score number);
Table created.

Step 2. Now insert some record in this table using following command.

SQL> insert into student values(&id,'&name',&A);

 Enter value for id: 1
 Enter value for name: AMIT
 Enter value for a: 232
 old 1: insert into student values(&id,'&name',&A)
 new 1: insert into student values(1,'AMIT',232)

 1 row created.

 SQL> /
 Enter value for id: 2
 Enter value for name: RAHUL
 Enter value for a: 2321
 old 1: insert into student values(&id,'&name',&A)
 new 1: insert into student values(2,'RAHUL',2321)
 
1 row created.
 
SQL> /
 Enter value for id: 3
 Enter value for name: ANKIT
 Enter value for a: 843
 old 1: insert into student values(&id,'&name',&A)
 new 1: insert into student values(3,'ANKIT',843)
 
1 row created.
 
SQL> /
 Enter value for id: 4
 Enter value for name: AJAY
 Enter value for a: 452
 old 1: insert into student values(&id,'&name',&A)
 new 1: insert into student values(4,'AJAY',452)
 
1 row created.
 
SQL> /
 Enter value for id: 5
 Enter value for name: ARUN
 Enter value for a: 543
 old 1: insert into student values(&id,'&name',&A)
 new 1: insert into student values(5,'ARUN',543)
 
1 row created.

Then commit all changes using below command.

SQL> COMMIT; 

Commit complete.

Now select record from this table which is you created.

SQL> SELECT *FROM STUDENT;

 ID  NAME      SCORE
 --  ------   -------
 1   AMIT      232
 2   RAHUL     2321
 3   ANKIT     843
 4   AJAY      452
 5   ARUN      543

Step 3.

Now create one more table which is have same structure like student table and also have some same records using below command.

SQL> CREATE TABLE STUDENT_N AS SELECT * FROM STUDENT WHERE 1=2;
 
Table created.
 
SQL> INSERT INTO STUDENT_N VALUES(&ID,'&NAME',&S);
 Enter value for id: 4
 Enter value for name: AJAY
 Enter value for s: ''
 old 1: INSERT INTO STUDENT_N VALUES(&ID,'&NAME',&S)
 new 1: INSERT INTO STUDENT_N VALUES(4,'AJAY','')
 
1 row created.
 
SQL> /
 Enter value for id: 5
 Enter value for name: ARUN
 Enter value for s: 600
 old 1: INSERT INTO STUDENT_N VALUES(&ID,'&NAME',&S)
 new 1: INSERT INTO STUDENT_N VALUES(5,'ARUN',600)

 1 row created.
 
SQL> /
 Enter value for id: 6
 Enter value for name: SACHIN
 Enter value for s: 787
 old 1: INSERT INTO STUDENT_N VALUES(&ID,'&NAME',&S)
 new 1: INSERT INTO STUDENT_N VALUES(6,'SACHIN',787)

 1 row created.

 SQL> /
 Enter value for id: 7
 Enter value for name: SHRIPAL
 Enter value for s: 504
 old 1: INSERT INTO STUDENT_N VALUES(&ID,'&NAME',&S)
 new 1: INSERT INTO STUDENT_N VALUES(7,'SHRIPAL',504)
 
1 row created.
 
SQL> /
 Enter value for id: 8
 Enter value for name: SPSINGH
 Enter value for s: 889
 old 1: INSERT INTO STUDENT_N VALUES(&ID,'&NAME',&S)
 new 1: INSERT INTO STUDENT_N VALUES(8,'SPSINGH',889)
 
1 row created.
 
SQL> COMMIT;

Commit complete.

Retrieve records from second table.

SQL> SELECT *FROM STUDENT_N;

 ID   NAME     SCORE
---   ------- ------ 
 4    AJAY
 5    ARUN     600
 6    SACHIN   787
 7    SHRIPAL  504
 8    SPSINGH  889

Step 4. Now merge both table using following command, and get your output together.

SQL> merge into student a
   using (select id,name,score from student_n) b
   on (a.id=b.id)
   when matched then
   update set a.name=b.name,
   a.score=b.score
   when not matched then
   insert (a.id,a.name,a.score)
   values(b.id,b.name,b.score);

5 rows merged.

SQL> select * from student;
 
ID   NAME SCORE
 --  ------  ----- 
 1   AMIT     232
 2   RAHUL   2321
 3   ANKIT    843
 4   AJAY
 5   ARUN     600
 6   SACHIN   787
 7   SHRIPAL  504
 8   SPSINGH  889

 8 rows selected.

NOTE:- For more clarity of merge statement topic please view video, please scroll.