Home Sql Trigger Mysql Alter Procedure
Questions:Ask|Latest


 
 

Share on Google+Share on Google+

Mysql Alter Procedure

Advertisement
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.

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)
Advertisement

Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: October 6, 2010

Ask Questions?    Discuss: Mysql Alter Procedure  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
DMCA.com