PL/SQL Procedure

Creating a procedure in PL-SQL:

Firstly, we create a table, a sequence for the primary keys and a trigger to handle the value of primary key.

===========================


DROP TABLE DUMMY_TABLE;

CREATE TABLE DUMMY_TABLE(
DUMMY_ID INTEGER,
TIME_DELAY INTEGER
);

ALTER TABLE DUMMY_TABLE ADD(
CONSTRAINT DUMMY_PK PRIMARY KEY(DUMMY_ID)
);

DROP SEQUENCE DUMMY_SEQUENCE ;

CREATE SEQUENCE DUMMY_SEQUENCE INCREMENT BY 1 START WITH 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 CACHE 20;

CREATE OR REPLACE TRIGGER DUMMY_SEQUENCE_TRIGGER
BEFORE INSERT ON DUMMY_TABLE
FOR EACH ROW
BEGIN
<>
BEGIN
IF INSERTING AND :NEW.DUMMY_ID IS NULL THEN
SELECT DUMMY_SEQUENCE.NEXTVAL INTO :NEW.DUMMY_ID FROM SYS.DUAL;
END IF;
END COLUMN_SEQUENCES;
END;


Here we have created a dummy table, a sequence and a trigger. On every insert on the table, the trigger will be called and next value of oour sequence will be the ID/Primary key of our table.

Let us now see how to create a procedure to insert records into the db and retrieve the id of the newly inserted row.

CREATE OR REPLACE PROCEDURE DUMMY_INSERT(V_TIME_DELAY IN DUMMY_TABLE.TIME_DELAY%TYPE , V_DUMMY_ID OUT DUMMY_TABLE.DUMMY_ID%TYPE)
AS
BEGIN
INSERT INTO DUMMY_TABLE(TIME_DELAY) VALUES (V_TIME_DELAY) RETURNING DUMMY_ID INTO V_DUMMY_ID;
END;

===========================


Check the validity of your procedure:

variable l_batchid number;

exec dummy_insert(10, :l_batchid);

print :l_batchid

Output should look something like:

anonymous block completed
L_BATCHID
-
1


for your select * from dummy_table, expected output is: