Understanding Stored Procedures in Database Management Systems
Explore the concept of stored procedures and their advantages in database management systems. Learn how to create, execute, and alter stored procedures using PL/SQL blocks. Dive into examples to grasp the practical implementation of stored procedures.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
Stored Procedures PRESENTATION BY Dr. Pallavi Mirajkar Coordinator , Dept. of Computer Science, M. D. College, Parel,Mumbai
Stored Procedures What is stored procedure? Piece of code stored inside the DBMS. A stored procedure is a named PL/SQL block which performs one or more specific task. This is similar to a procedure in other programming languages. SQL procedures and functions and store them inside DBMS. allows you to define
Stored Procedures Advantages of Stored Procedures Reusability: do not need to write the code again and again. Programming language-like environment Assignment, Loop, For, IF statements Call it whenever needed From select statement, another procedure, or another function Stored procedures in Oracle follow a language called PL/SQL PL/SQL: Procedural Language SQL
Creating A Stored Procedure If exists, then drop it and create it again IS or AS both are valid CREATE [OR REPLACE] PROCEDURE <procedureName> (<paramList>) [IS|AS] <localDeclarations> Begin <procedureBody>; End; / A parameter in the parameter list is specified as: <name> <mode> <type> Mode: IN input parameter (default) OUT output parameter INOUT input and output parameter
How to Execute Stored Procedure SQL> EXEC Procedure_name();
Example 1 Creating and Altering Procedure Create an empty procedure or replace a procedure with Hello World and execute the same. Creating Procedure Code: 1 CREATE PROCEDURE Proc1 2 IS 3 BEGIN 4 NULL; 5* END; SQL> / Procedure created.
Example 1 Creating and Altering Procedure Altering Procedure PL/SQL BLOCK: 1 CREATE OR REPLACE PROCEDURE Proc1 IS 2 BEGIN 3 DBMS_OUTPUT.PUT_LINE('HELLO WORLD'); 4* END; 5 / Procedure created. Output:- SQL> EXEC Proc1(); HELLO WORLD PL/SQL procedure successfully completed.
Return Value Clauses Stored procedures can set output variables Stored procedures do not return values REMOVE PROCEDURE Syntax : SQL>Drop Procedure < Procedure_name> SQL >Drop Procedure Proc1
Summary of Stored Procedures Code modules that are stored inside the DBMS Used and called repeatedly Powerful programming language style Can be called from other procedures, functions, triggers, or from select statement (only functions)
Questions on Stored Procedures 1.What is Stored Procedure? 2.What is basic Syntax of Stored Procedure in PL SQL? 3.What is the basic difference between IS/AS in SQL Stored Procedure? 4.What are different usages of Stored Procedure? 5.What are advantages of Stored Procedure?
