Mysql Alter Procedure
A Procedure is a set of one or more sql statements that is
executed under one name. Mysql Alter Procedure is used to modify and redefine
the column type in procedure.
Understand with Example
The Tutorial illustrate an example from 'Mysql Alter Procedure'. To grasp
Example, we create a table employee1. The create table is used to create
a table with required fieldname and datatype respectively.
Query to Create Table named employee1:
mysql>CREATE TABLE employee1 (
-> Empid int(10),
-> Empname varchar(60),
-> date date
-> );
|
Query to insert data into Table named employee1:
The Query insert into add the records or rows to the table
'employee1'.
mysql>insert into employee1 values(01,'Girish','2008-12-20');
Query OK, 1 row affected (0.02 sec)
mysql>insert into employee1 values(02,'Komal','2008-12-21');
Query OK, 1 row affected (0.02 sec)
mysql>insert into employee1 values(03,'vineet','2008-12-21');
Query OK, 1 row affected (0.02 sec)
mysql>insert into employee1 values(04,'Amit','2008-12-20');
Query OK, 1 row affected (0.02 sec)
mysql>insert into employee1 values(02,'Komal','2008-12-23');
Query OK, 1 row affected (0.02 sec)
mysql>insert into employee1 values(02,'Sandeep','2008-12-24');
Query OK, 1 row affected (0.02 sec)
mysql>insert into employee1 values(02,'suman','2008-12-25');
Query OK, 1 row affected (0.02 sec)
mysql>insert into employee1 values(01,'AAA',' 2008-12-03');
Query OK, 1 row affected (0.02 sec)
|
Query to view data of Table named employee1:
To view the detail of table name 'employee1' we use select query that
return the detail of records .
mysql> select * from employee1;
|
Output:-
+-------+---------+-------------+
| Empid | Empname | Dateofbirth |
+-------+---------+-------------+
| 1 | Girish | 2008-12-20 |
| 2 | Komal | 2008-12-21 |
| 3 | vineet | 2008-12-21 |
| 4 | Amit | 2008-12-20 |
| 2 | Komal | 2008-12-23 |
| 2 | Sandeep | 2008-12-24 |
| 2 | suman | 2008-12-25 |
| 1 | AAA | 2008-12-03 |
+-------+---------+-------------+
8 rows in set (0.00 sec)
|
Query to Create procedure named employeetest:
The Query create procedure is used to create a procedure
employeetest that accept value 'name' as input parameter. The procedure include
a sql statement that return the records from table employee1 on the basis of WHERE
clause. The where clause returns you the set of records on the basis of 'empname'
from table 'employee1'
mysql> create procedure employeetest (name varchar(65))
-> begin
-> select * from employee1 where empname=name;
-> end
-> gg
Query OK, 0 rows affected (0.00 sec)
|
Query to see procedure status of procedure named employeetest:
The show procedure status provides you the detail information of procedure
like Db,Name,Type,Definer,Modified,Created and Security type etc.
mysql> show procedure status;
-> gg
+--------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment |
+--------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+
| girish | employeetest | PROCEDURE | root@localhost | 2008-12-31 11:55:32 | 2008-12-31 11:55:32 | DEFINER | |
+--------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+
1 row in set (0.00 sec)
|
Query to alter procedure from Security_type status i.e
DEFINER to INVOKER:
The Alter Query given below is used to redefine a previously defined
stored procedure that was created using the CREATE PROCEDURE statement. It
hardly any affect related stored procedures or stored functions. In this
example we redefine the procedure 'employeetest' and change the security type
from DEFINER to INVOKER.
mysql> alter procedure employeetest sql security invoker;
-> gg
Query OK, 0 rows affected (0.00 sec)
|
Output:-
+--------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment |
+--------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+
| girish | employeetest | PROCEDURE | root@localhost | 2008-12-31 12:09:16 | 2008-12-31 11:55:32 | INVOKER | |
+--------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+
1 row in set (0.02 sec)
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
