mySql 儲存過程與函式

過程

CREATE DEFINER=`root`@`%` PROCEDURE `clearDate_Jk`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

DELETE   FROM sys_dept WHERE create_time > '2023-12-31';
truncate table sys_file;

END

CREATE DEFINER=`root`@`%` PROCEDURE `getAssets`(
	IN `companyId` BIGINT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
   SELECT CAST(JSON_UNQUOTE(JSON_EXTRACT(a.infos,'$."總資產"')) AS DECIMAL(10, 2)) AS zzc
 FROM bs_company_financial a  WHERE  del_flag =0 AND years = YEAR(CURDATE()) 
 ORDER BY  update_time  LIMIT 1;
END

函式

CREATE DEFINER=`root`@`%` FUNCTION `tt`(
	`Param1` INT
)
RETURNS decimal(10,0)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
declare num decimal default 0;
	set num = Param1;
return num;
END