storedTriggers.docx - Stored Procedures A stored procedure is a collection or batch of SQL statements and control-of-flow language that is stored under

storedTriggers.docx - Stored Procedures A stored procedure...

This preview shows page 1 - 3 out of 8 pages.

Stored ProceduresA stored procedure is a collection or batch of SQL statements and control-of-flow language that is stored under one name, and executed as a single unit. It helps in improving the performance of a query.Benefits of Stored ProceduresA stored procedure is a precompiled object stored in the database. This means that a procedure is compiled beforehand and is readily available for the various applications to execute. No time is spent on sending the query to the server, parsing and compilingthe procedure again. Stored procedures offer the following benefits:Improved performance: Applications do not have to compile the procedure repeatedly.Reduction in network congestion: Applications need not submit multiple SQL statements to the server for the purpose of processing.Better consistency: The coding logic and SQL statements defined in the procedure are uniformly implemented across all applications as the procedure serves as a single point ofcontrol.Better security mechanism: Users can be granted permission to execute a stored procedure even if they do not own the procedure.Stored procedure SyntaxCREATE [OR REPLACE] PROCEDURE proc_name [list of parameters] IS Declaration section BEGIN Execution section EXCEPTION Exception section END; IS -marks the beginning of the body of the procedure and is similar to DECLARE in anonymousPL/SQL Blocks. The code between IS and BEGIN forms the Declaration section.The syntax within the brackets [ ] indicate they are optional. By using CREATE OR REPLACE together the procedure is created if no other procedure with the same name exists or the existing procedure is replaced with the current code.The following tables will be used for tutorials.
Background image
STUDENT(cisNum, name, gender,age )BOOK(bookId, title)BORROW (cisNum, bookId, dateBorrowd, dueDate)Note : primary keys Boldand foreign keys underlined)Suggested solutions may have some bugs. DEBUG|||1.Procedure to give names of female students.
Background image
Image of page 3

You've reached the end of your free preview.

Want to read all 8 pages?

  • Summer '17
  • Mr Maruatona
  • MYSQL , Database trigger, Trigger

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture