Hello Everyone,
In the world of databases, PLSQL is a powerful tool for making applications. Here we are going to discuss the PLSQL procedures in this post. It help us to put together a bunch of SQL and procedural commands into a single unit of code that we can used over and over again. In this dev post, we will take a closer look into PLSQL procedures talking about how it will be written and why it is useful.
PLSQL procedures are like labeled containers of code stored in a database for later use. They bundle together SQL commands and procedural statements which makes code easier to manage, reuse and fix. The procedures are written by following below syntax.
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] parameter_type [, ...])]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END procedure_name;
"CREATE" statement is used to create a new procedure. "OR REPLACE" are used to do changes in an existing procedure without deleting it first.
[IN | OUT | IN OUT] are three ways to pass parameters in procedure. IS Keyword is used to start the declaration section of the procedure. In BEGIN section the main executable code will written.
Now i will write a procedure that will extract data from a table and insert into another table.
CREATE OR REPLACE PROCEDURE proc
AS
BEGIN
-- Extract data from source table
FOR source_rec IN
(SELECT * FROM customer where ROWNUM < 100
)
LOOP
INSERT
INTO TBD_CUST_TEST
(
CUST_ID,
NAME_NAME,
CNIC
)
VALUES
(
source_rec.cust_id,
source_rec.NAME_1,
source_rec.Cnic
);
END LOOP;
COMMIT;
END proc;
Basically it is the ETL procedure code. It select first 99 rows from the customer table.For each row selected, it inserts data into the TBD_cust_TEST table.It inserts values from the cust_id column of the customer table into the CUST_ID column of the TBD_cust_TEST table.It inserts values from the NAME_1 column of the customer table into the NAME_NAME column of the TBD_cust_TEST table.It inserts values from the Cnic column of the customer table into the CNIC column of the TBD_cust_TEST table. After all the data has been inserted. it commits the transaction to make the changes permanent.
It would be my pleasure if you will share your opinion and reviews on this topic.
Please cast witness vote to @bangla.Witness or set proxy to @rme.
_
Vote @bangla.witness as witness
Set Proxy @rme as proxy
Special Thanks.
Cc: @rme
Cc: @hungry-griffin
Cc: @pennsif
Thanks for giving your precious time to my post.
Follow | Upvote | Share | Comments
Follow me on Instagram.
Instagram
🆃🅷🅰️🅽🅺 🆈🅾️🆄