Only 3.9% of viewers are subscribing to my channel 😓.
I request you to please give click on Subscribe button.
It really helps me grow 😢.
How To Create Sequence In PostgreSQL And Usage Of Sequence || Table Primary Key || Stored Procedure
CREATE SEQUENCE tbl_emp_seq
START WITH 1 -- VALUE WILL START WITH 1
INCREMENT BY 1 -- IT WILL GET INCREMENT BY 1
MINVALUE 1 -- MINIMUM VALUE CAN BE 1
MAXVALUE 10 -- MAXIMUM VALUE CAN BE 10
CYCLE; -- CYCLE MEANS, AFTER REACHING MAXVALUE, WHICH IS 10 HERE, THE SEQUENCE WILL START AGAIN FROM 1
-- NOW LET'S SEE THE SEQUENCE VALUE
-- USE THE BELOW STATEMENT FOR SEQUENCE VALUE
SELECT NEXTVAL('tbl_emp_seq');
-- THE VALUE REACHED MAXVALUE, NOW IT WILL AGAIN START FROM 1
-- NOW LET'S SEE THE CASE OF NO CYCLE
-- IN CASE OF NO CYCLE, THE SEQUENCE WON'T START AGAIN FROM 1
-- IT WILL THROW AN ERROR AFTER REACHING THE MAX VALUE
DROP SEQUENCE tbl_emp_seq;
CREATE SEQUENCE tbl_emp_seq
START WITH 1 -- VALUE WILL START WITH 1
INCREMENT BY 1 -- IT WILL GET INCREMENT BY 1
MINVALUE 1 -- MINIMUM VALUE CAN BE 1
MAXVALUE 10 -- MAXIMUM VALUE CAN BE 10
NO CYCLE;
SELECT NEXTVAL('tbl_emp_seq');
-- HERE THE MAX VALUE IS REACHED, SO IF WE TRY TO GET THE VALUE AFTER THAT, WE WILL GET AN ERROR
ERROR: nextval: reached maximum value of sequence "tbl_emp_seq" (10)
SQL state: 2200H
-- THIS IS WHY WE USUALLY KEEP THE MAXVALUE VERY HIGH AS MUCH POSSIBLE
-- NOW LET'S CREATE AN ACTUAL SEQUENCE AND USE IN A TABLE TO GENERATE VALUES AUTOMATICALLY
CREATE SEQUENCE tbl_emp_seq
START WITH 1 -- VALUE WILL START WITH 1
INCREMENT BY 1 -- IT WILL GET INCREMENT BY 1
MINVALUE 1 -- MINIMUM VALUE CAN BE 1
MAXVALUE 10000000000 -- MAXIMUM VALUE CAN BE 10
NO CYCLE;
CREATE TABLE EMP
(
EMP_ID INTEGER DEFAULT NEXTVAL('tbl_emp_seq'),
EMP_NAME VARCHAR(50),
SALARY NUMERIC(5,2)
);
INSERT INTO EMP(EMP_NAME,SALARY) VALUES ('AKRAM',100.56);
INSERT INTO EMP(EMP_NAME,SALARY) VALUES ('SOHAIL',670.56);
INSERT INTO EMP(EMP_NAME,SALARY) VALUES ('KNOWLEDGE 360',757.87);
SELECT * FROM EMP;
-- WE CAN SEE, THE EMP_ID VALUES ARE GENERATED FROM SEQUENCE
-- NOW LET'S USE THE SEQUENCE IN A STORED PROCEDURE
CREATE OR REPLACE PROCEDURE public.EMP_PROC()
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
V_EMP_SEQ_VAL INTEGER;
BEGIN
SELECT NEXTVAL('tbl_emp_seq') into V_EMP_SEQ_VAL;
insert into emp(emp_id,emp_name,salary) values (V_EMP_SEQ_VAL,'New Emp',455.24);
END;
$BODY$;
call public.EMP_PROC();
select * from emp;
SELECT NEXTVAL('tbl_emp_seq');
SELECT CURRVAL('tbl_emp_seq');
-- also we can see the current value of a sequence
-- If you have any doubt, please ask me in the comments.
-- Subscribe the channel to get the videos updates.
No comments:
Post a Comment