Database Reference
In-Depth Information
Manipulation
A procedure is stored inside a database, and is not tied to a specific table. Therefore,
the interface to manipulate procedures and functions can be found at the database
level, on the Structure page, under the Routines slider.
The first icon brings this procedure's text into a query box for editing. The second
icon would be used to delete this procedure. When editing the procedure, we notice
that the text has been somewhat modified.
DROP PROCEDURE `add_page`//
CREATE DEFINER=`marc`@`%` PROCEDURE `add_page`(IN param_isbn
VARCHAR(25), IN param_pages INT, OUT param_message VARCHAR(100))
BEGIN
IF param_pages > 100 THEN
SET param_message = 'the number of pages is too big';
ELSE
UPDATE book SET page_count = page_count + param_pages WHERE
isbn=param_isbn;
SET param_message = 'success';
END IF;
END
First, a DROP PROCEDURE appears. This is normal since MySQL does not offer a
statement that would permit a change to the body of a procedure. Therefore, we
have to delete a procedure every time we want to change it. It's true that the ALTER
PROCEDURE statement exists, but it can only change the procedure's characteristics—
for example, adding a comment. Then, a DEFINER clause is shown. It was generated
at the creation time, and indicates who created this procedure.
At this point, we make any changes we need to the code and hit Go to save
this procedure.
 
Search WWH ::




Custom Search