what is 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.

Stored procedure speed is is very fast because it cached on server.

A Stored Procedures More secure because there is no direct access with tables in database.

A Stored Procedures will be store in one place so that there’s no confusion of having business rules.

How to create Stored Procedure ?

By using of phpMyAdmin.

BY using MySql query.

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

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

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;

Previous Next

Review & Rating

0.0 / 5

0 Review

(0)

Most Popular Tutorials

what is procedure?

Syntax :

Syntax :

Syntax :

Syntax :

Syntax :

Syntax :

Syntax :

Syntax :

file name : insert.php

file name : index.php

file name : index.php

file name : index.php

file name : index.php

file name : index.php

file name : index.php

file name : index.php

file name : index.php

PHP

PHP OOPs

Codeigniter

Laravel

NODE.JS

Javascript

0.0 / 5

0 Review

Write Review Here

Most Popular Tutorials

Important Link