[MDEV-8618] DYNAMIC SQL in stored functions Created: 2015-08-14  Updated: 2015-10-31

Status: Open
Project: MariaDB Server
Component/s: Prepared Statements
Fix Version/s: None

Type: Task Priority: Minor
Reporter: Mikhail Gavrilov Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: None


 Description   

Will someday possible to use the DYNAMIC SQL into functions? Or MariaDB has fundamental architectural constraints for this? If possible, then this feature request that such feature came soon as possible.

I would be much happier if can instead of this procedure:

CREATE PROCEDURE `appl_loan_rate_calc`(
 
IN par_id_tariff INT(10) UNSIGNED
 
, IN par_loan_sum DECIMAL(12,2)
 
, OUT par_rate DECIMAL(10,2)
 
)
 
BEGIN  
	DECLARE var_formula VARCHAR(100);
 
	SELECT rate INTO var_formula FROM `appl_loan_tariff` WHERE id_tariff = par_id_tariff;
 
	SET @loan_sum = par_loan_sum;
 
	SET @sql_statement = CONCAT('SET @rate = ',var_formula);
 
 
 
	PREPARE select_sql FROM @sql_statement;
 
	EXECUTE select_sql;
 
	DEALLOCATE PREPARE select_sql;
 
 
 
	SET par_rate = @rate;
 
END

write function:

CREATE FUNCTION `appl_loan_rate_calc`(
 
par_id_tariff INT(10) UNSIGNED
 
, par_loan_sum DECIMAL(12,2)
 
) RETURNS DECIMAL(10,2)
 
    READS SQL DATA
 
    DETERMINISTIC
 
BEGIN
 
	DECLARE var_formula VARCHAR(100);
 
	SELECT firmula INTO var_formula FROM `appl_loan_tariff` WHERE id_tariff = par_id_tariff;
 
	
	PREPARE dymanic_sql FROM CONCAT('SET var_rate = ',var_formula);
 
	EXECUTE dymanic_sql;
 
	DEALLOCATE PREPARE dymanic_sql;
 
 
 
	RETURN var_rate;
 
END

Because i need calculate for several tariffs which appropriate to conditions, of course i can do it with cursor and temporary table, but solution with function I like more.

 Comments   
Comment by Sergei Golubchik [ 2015-10-28 ]

There was a fundamental architectural constraint, right (lock manager had to know up front all tables that a function will possibly access). But as far as I understand that was fixed and now we can remove this restriction about dynamic SQL and stored functions.

Generated at Thu Feb 08 07:28:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.