Typically, applications access the database across the network. This can
result in poor performance if a lot of data is being returned. A stored procedure
runs on the database server. A client application can call the stored procedure
which then performs the database accessing without returning unnecessary
data across the network. Only the results the client application needs are
returned by the stored procedure.
Syntax:
CREATE PROCEDURE <proc-name>(
<paramater1> <return-type> <data type>
<paramater2> <return-type> <data type>
---------------------------------------------------
---------------------------------------------------
<paramater3> <return-type> <data type>
<paramater4> <return-type> <data type>
)
[CONTAINS SQL / READS SQL DATA / MODIFIES SQL DATA]
[LANGUAGE SQL]
BEGIN [ATOMIC / NON ATOMIC]
<body>
END
Example :
CREATE PROCEDURE Samp_proc(
in_value IN VARCHAR(30)
)
MODIFIES SQL DATA
BEGIN ATOMIC
DECLARE n INTEGER;
DECLARE temp VARCHAR(30);
SET temp=in_value;
SET n = 5;
WHILE(n >= 1)
DO
INSERT INTO repeat VALUES(temp);
SET n=n-1;
END WHILE;
END
@