Procedure Function

varghese · #1 (**permalink**) 11-09-2007, 06:05 AM

Begin End Examples
mysql>
mysql> DELIMITER //
mysql> CREATE FUNCTION myProcedure (rate DECIMAL(10,2))
-> RETURNS DECIMAL(10,2)
->
-> SQL SECURITY DEFINER
->
-> tax: BEGIN
-> DECLARE discount DECIMAL(10,2);
-> SET discount = rate * .05;
-> RETURN discount;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql>
mysql> select myProcedure(123.45);
+----------------+
| myProcedure(123.45) |
+----------------+
| 6.17 |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> drop function myProcedure;
Query OK, 0 rows affected (0.00 sec)

mysql>

Murali · #2 (**permalink**) 11-10-2007, 01:17 AM

Quote:

Originally Posted by varghese

Begin End Examples
mysql>
mysql> DELIMITER //
mysql> CREATE FUNCTION myProcedure (rate DECIMAL(10,2))
-> RETURNS DECIMAL(10,2)
->
-> SQL SECURITY DEFINER
->
-> tax: BEGIN
-> DECLARE discount DECIMAL(10,2);
-> SET discount = rate * .05;
-> RETURN discount;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql>
mysql> select myProcedure(123.45);
+----------------+
| myProcedure(123.45) |
+----------------+
| 6.17 |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> drop function myProcedure;
Query OK, 0 rows affected (0.00 sec)

mysql>

I worked out same code given, but returns with error

Code:

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

May be here is suitable syntax for creating a stored function,

Code:

DROP FUNCTION IF EXISTS myProcedure;
CREATE FUNCTION myProcedure (rate DECIMAL(10,2)) RETURNS DECIMAL(10,2)
	DETERMINISTIC
	COMMENT 'Simple Function'
 	SQL SECURITY DEFINER
BEGIN
	DECLARE discount DECIMAL(10,2);
	SET discount = rate * .05;
RETURN discount;
END;

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
how to create store procedure?	senraj	Database Support	4	04-25-2008 06:26 AM
stored procedure	itbarota	Database Support	3	03-10-2008 09:43 PM
how to get out parameter of stored procedure ?	Murali	PHP Programming	0	12-16-2007 04:57 AM
Can a stored procedure call itself or recursive stored procedure? How many level SP n	H2o	Database Support	1	08-03-2007 10:55 AM
Diff inline function and ordinary function	vigneshgets	C and C++ Programming	1	05-24-2007 11:34 AM