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)

Mysql Alter Procedure

Mysql Alter Procedure

Mysql Alter Procedure

Tutorials