“Empowerment through quality technical education" AJEENKYA DY Patil School of Engineering Dr. D. Y. Patil Knowledge City, Charholi Bk., Via. Lohegaon, Pune - 412 105. AJEENK A Department of EXTC Engineering DY Patil School of ineing Experiment No. 8 Title: Date of Performance: Roll Noz Date of Submission: liversity Seat No: ‘Signature of Staff: DATABASE MANAGEMENT LAB TEEATCExperiment No. 8 Aim: To Study and implement PLISQL programming along with Procedures Problem Statement: Write a Stored Procedure namely proc_Grade for the categorization of student. If marks scored by (00 and marks>=990 then student will be placed in distinction category if marks scored are between 989 and900 category is first class, if marks 899 and 825 category is Higher Second Class. Write a PL/SQL block for using procedure created with above requirement. ‘Stud_Marks(name, total_marks) Result(Roll,Name, Class). students in examination is <=! Objective: 1. To learn and understand Procedures. Requirement: Oracle, Windows OS Theory: A Stored Procedure is a subprogram in PL/SQL that is stored permanently in the database and can be executed as needed. It encapsulates a series of SQL and PL/SQL statements that perform a specific task. Unlike an anonymous PL/SQL block, which is not saved in the database, a stored procedure can be called and reused multiple times. Why Use Stored Procedures? ‘© Modularity: Procedures allow developers to break down large, complex tasks into smaller, manageable modules, improving readability and maintainability. ‘© Reusability: Stored procedures can be invoked from multiple programs, saving development time and ensuring consistency. '* Security: Permissions to execute the procedure can be granted to users, allowing them to perform operations without directly accessing tables. ‘* Performance: Stored procedures are precompiled and stored in the database. Once compiled, they execute faster because the parsing, validation, and optimization have already been done. ‘* Maintenance: Since logic resides in the database, any change to the business logic can be handled jons individually. centrally within the procedure, eliminating the need to update appl DATABASE MANAGEMENT LAB TEEATC‘Components of a Stored Procedure «Procedure Name: A unique identifier for the procedure. ‘* Parameters (optional): Procedures can accept input and return output values using parameters. ‘¢ IN: Passes a value to the procedure. ‘© OUT: Retums a value from the procedure. ‘+ IN OUT: Passes a value to the procedure and returns a new value after processing. ‘+ Executable Section: The body of the procedure where the main logic resides. ‘+ Exception Handling (optional): Handles any errors that occur during execution. Conclusion: CREATE [OR REPLACE] PROCEDURE procedure_name (parameter! (IN | OUT | IN OUT] datatype, parameter2 [IN | OUT | IN OUT] datatype, ~ AS -- Variable declarations (optional) BEGIN -- Executable section: SQL and PL/SQL statements -- Perform operations such as SELECT, INSERT, UPDATE, DELETE, etc. -- Example logic —-IF-THEN conditions, loops, case statements, ete EXCEPTION -- Exception handling block (optional) -- Handle any runtime errors or exceptions here WHEN exception_name THEN -- Error handling logic END procedure_name: DATABASE MANAGEMENT LAB TEEATC|Questions: 1. Whatis procedure in PL/SQL? 2. Write syntax to create procedure 3. Whatare the parameters of procedure? JATABASE MANAGEMENT LAB TEERTO