PHP Tutorials
- What is MySqli
- mysql query
- mysql query example
- InnoDB
- mysql column Type
- CRUD Example
- Connection Using Function
- mysql keys
- SELECT
- WHERE
- UPDATE
- Count no of Rows
- ALIAS
- AND, AND & OR
- BETWEEN
- COMPARISON OPERATOR
- DELETE
- DELETE LIMIT
- DISTINCT
- EXISTS
- FROM
- GROUP BY
- HAVING
- IN
- INTERSECT
- IS NULL & IS NOT NULL
- LIKE
- NOT
- ORDER BY
- SELECT LIMIT
- SUBQUERY
- TRUNCATE
- UNION && UNION ALL
- Concat & Group_Concat
- mysql Function
- Mysql Insert Id
- MySql Aggregate Function
- Mysql Join
- JOIN in MySql
- Trigger
- Procedure
- Transaction
- views
- Index
- SQL Injection
- Normalization
- Query Bind
- Interview Questions
Important Link
what is mysql procedure?
A procedure is a subroutine (like a subprogram) in a regular scripting language, stored in a database. procedures are written in MySQL and stored in the MySQL database/server. A MySQL procedure has a name, a parameter list, and SQL statement.
Stored procedures are fast. MySQL server takes some advantage of caching.
a procedure is a stored program that you can pass parameters into. It does not return a value like a function does.
Create Procedure
Syntax :
CREATE PROCEDURE procedure_name[ (parameter datatype [, parameter datatype]) ]
BEGIN
Declaration_section
Executable_section
END;
Parameter
procedure_name: name of the procedure.
Parameter: number of parameters. It can be one or more than one.
declaration_section: all variables are declared.
executable_section: code for the function is written here.
create Table
Syntax :
create table student (id int(11) AUTO_INCREMENT PRIMARY key,
name varchar(32) NOT NULL,
email varchar(64) UNIQUE,
address varchar(64) NOT NULL
);
select * from student
Creating procedure:
DELIMITER $$
CREATE PROCEDURE get_itech_student()
BEGIN
SELECT * FROM student_info;
END$$
Calling procedure:
call get_itech_student();
call get_itech_student('sana','3');
$result = mysqli_query($con,"CALL sp_get_ittutorial_data('".$tid."','','".$_GET['status']."')");
Drop a procedure
a procedure can also be dropped. When a procedure id dropped, it is removed from the database.
Drop procedure[ IF EXISTS ] procedure_name;
procedure_name: name of the procedure to be dropped.
drop procedure get_student;
Syntax :
Getting started with stored procedures
SELECT
customerName,
city,
state,
postalCode,
country
FROM
itechxpert_customers
ORDER BY customerName;
create procedure
DELIMITER $$
CREATE PROCEDURE Get_itech_Customers()
BEGIN
SELECT
customerName,
city,
state,
pin,
country
FROM
itechxpert_customers
ORDER BY customerName;
END$$
DELIMITER ;
a stored procedure is a segment of declarative SQL statements stored inside the MySQL Server.
call procedure
Syntax :
CALL Get_itech_Customers();
what is DELIMITER?
default delimiter, you use the DELIMITER command: .
A stored procedure in mysql contains multiple statements separated by semicolons (;).
and you can also use delimiter such as && or // for end of statements.
To compile the whole stored procedure as a single statement then you need to temporarily change the delimiter from the semicolon (;) to another delimiter such as $$ or //
MySQL CREATE PROCEDURE statement
Syntax :
DELIMITER //
CREATE PROCEDURE Get_my_Products()
BEGIN
SELECT * FROM itechxpert_products;
END //
DELIMITER ;
How to call Stored Procedure in mysql
Syntax :
CALL Get_my_Products();
How to show stored procedure in mysql?
Syntax :
SHOW CREATE PROCEDURE proc_name
SHOW PROCEDURE STATUS Statement
Syntax :
SHOW PROCEDURE STATUS
[LIKE 'pattern' | WHERE expr]
SHOW PROCEDURE STATUS LIKE 'sp1'
How to insert data using mysql procedure
file name : insert.php
//run the store procedure to insert data into database
// here itech_insert_student() is the procedure name
$result = mysqli_query($con, "CALL itech_insert_student(
'".$name."',".$address.",'".$mobile."',
".$status.",now(),
'".$crtdBy."')") or die("Query fail: ".mysqli_error($con));
How to update table using procedure in mysql?
$result = mysqli_query($con,"CALL sp_update_emptable('".$Mobile."','".$Employeeid."');") or die("Query fail: " . mysqli_error($sql));
How To Insert Records Using Stored Procedure in MySql with PHP ?
If you want to insert large data in a database table, you can use Stored Procedure. There are number of advantages to insert record into table with the use of Stored Procedure.
How to create Stored Procedure ?
Step 1 :-
file name : index.php
click on Routines
New -> Add Routine
Fiil up the details and click on go button.
By using Query :-
DELIMITER $$
CREATE PROCEDURE `insert_itech_records`(IN `name` VARCHAR(64))
BEGIN
insert into itech_record (name) values (mahtab);
END$$
DELIMITER ;
Now Create table in which we want to insert data.
file name : index.php
CREATE TABLE `itech_record`(`name` VARCHAR(64))
Call Stored Procedure by PHP script to insert names.
<?php
$name = array('mahtab','raj','sana','sara','arham');
foreach($name as $key=>$val){
$data = implode("','",$val);
echo '===== Standard data insertion start ===== <br/>'.$data.'<br/>';
$query = "CALL insert_itech_records('".$data."')";
try{
$success = $conn->query($query);
if($success){
echo "inserted succesfully";
}
else{
echo "Data not successfully Inserted.";
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>
file name : index.php
file name : index.php
CREATE PROCEDURE `insert_itech_product`(IN `name` VARCHAR(64), IN `address` VARCHAR(128), IN `mobile` INT(12)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER
BEGIN insert into itech_record (name,address,mobile) values (mahtab,delhi,7838897299); END
Example :-
Table :- employee_details
CREATE TABLE employee_details (
id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
name varchar(64) NOT NULL,
address text NOT NULL,
email varchar(64) DEFAULT NULL,
mobile bigint(10) NOT NULL
)
create procedure
create procedure insert_emp_data(Name varchar(32), Address varchar(128), Email varchar(64), Mobile int(11))
BEGIN
insert into employee_details (name, address, email, mobile) values (Name,Address,Email,Mobile);
END
call procedure
file name : index.php
<?php
$con = mysqli_connect("localhost","root","", "dbname");
if(isset($_post['btn_submit']))
{
$sql = "CALL insert_emp_data('".$_POST['name']."','".$_POST['address']."','".$_POST['email']."','".$_POST['mobile']."',)" ;
if(mysqli_query($con,$sql))
{
header("Location:index.php?inserted=1");
}
}
if(isset($_GET['inserted'])
{
echo "<script> alert("data inserted"); </script>";
}
?>
How To show all stored procedures?
file name : index.php
SHOW PROCEDURE STATUS;
How to show all stored functions?
file name : index.php
SHOW FUNCTION STATUS;
How to show stored procedures in a specific database?
file name : index.php
SHOW PROCEDURE STATUS WHERE Db = 'database_name';
The same with functions:
SHOW FUNCTION STATUS WHERE Db = 'database_name';
IF Statement
The IF statement is used in stored programs that implement the basic conditional construct in MySQL. Based on a certain condition, it allows us to execute a set of SQL statements. It returns one of the three values True, False, or NULL.
DELIMITER $$
CREATE PROCEDURE myResult(original_rate NUMERIC(6,2),OUT discount_rate NUMERIC(6,2))
NO SQL
BEGIN
IF (original_rate>200) THEN
SET discount_rate=original_rate*.5;
END IF;
select discount_rate;
END$$
DELIMITER $$;
If else
DELIMITER $$
CREATE PROCEDURE myResult(original_rate NUMERIC(6,2),OUT discount_rate NUMERIC(6,2))
NO SQL
BEGIN
IF (original_rate>200) THEN
SET discount_rate=original_rate*.5;
ELSE
SET discount_rate=original_rate;
END IF;
select discount_rate;
END$$
DELIMITER ;
How to create procedure in mysql
DELIMITER //
CREATE PROCEDURE `get_ittutorial_data`(tid INT,tutorial_id INT)
BEGIN
IF (tutorial_d != '') THEN
Select
tutorial_name,
title,
content,
date,
created
From tutorial tt
left join itechxpert itx on tt.id=itx.fki_id
left join funtoosapp fa on tt.fki_tech_id=fa.id
WHERE (fk_status !=1) AND tutorialid=tid AND tt.tech_id=tutorial_id;
ELSE
Select
tutorial_name,
title,
content,
date,
created
From tutorial tt
left join itechxpert itx on tt.id=itx.fki_id
left join funtoosapp fa on tt.fki_tech_id=fa.id
WHERE (fk_status !=1) AND tutorialid=tid AND tt.tech_id=tutorial_id;
ORDER BY id DESC;
END IF;
END//
DELIMITER;