MySQL Basics Practice Test: Stored Programs — Flashcards

Quiz on compound statements, stored functions, triggers, events and security.

A MySQL stored program is a program that is stored in the database and can be a function, procedure, table trigger, or event. Stored programs are represented in memory by two major parts:
- The code of the stored program, including SQL statements and control flow logic
- A symbol table that describes all the local variables, cursors, labels, conditions, and so on declared in the code 

Most MySQL stored programs consist of one or more blocks. A block consists of various types of declarations and program code, which can occur in the following order: Variable and condition declarations, Cursor declarations, Handler declarations, and Program code. 
Stored procedures are a set of SQL statements that are grouped together to form a logical unit of work. They are similar to functions or methods in programming languages. Stored procedures can be used to streamline complex database operations. For example, you might use a stored procedure to add a new product to the database. 

Here are some steps to list all procedures in the MySQL database:
Use the MySQL shell command line
Use the command SHOW PROCEDURE STATUS WHERE db = 'your_database_name' 

Here are some steps to drop a stored procedure:
Use the MySQL workbench wizard
Expand sakila schema
Expand Stored Procedures
Right-click on sp_GetMovies
Click on Drop Stored Procedure
Choose to review the procedure before dropping it, or you can drop it without reviewing it
Click on Execute