SQL_Fn

資料庫函數

• cmd for func

https://stackoverflow.com/questions/14356694/mysql-function-call

• IPC

mySQL fun can not return table, but only string

https://stackoverflow.com/questions/23421771/how-to-return-table-from-mysql-function/23421816 (invalid way)

• Can Not call usp from Fn

https://stackoverflow.com/questions/6344880/execute-stored-procedure-from-a-function

• Can call usp from usp

https://stackoverflow.com/questions/11000802/calling-a-stored-procedure-in-a-stored-procedure-in-mysql

• call fun in usp

https://www.mysqltutorial.org/mysql-stored-function/

• can execute sql statement in SQL, but not in MySQL

https://docs.microsoft.com/zh-tw/sql/t-sql/statements/create-function-transact-sql?view=sql-server-ver15

• Embeded SQL

https://docs.microsoft.com/zh-tw/sql/t-sql/statements/create-function-transact-sql?view=sql-server-ver15 (func returns table)

B. 建立內嵌資料表值函式

下列範例會傳回 AdventureWorks2012 資料庫中的內嵌資料表值函式。 它會傳回三個資料行：ProductID、Name，以及年初至今銷售到商店之每項產品的總計彙總 YTD Total (依商店區分)。 SQL

    CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
    RETURNS TABLE
    AS
    RETURN
    (
        SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
        FROM Production.Product AS P
        JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
        JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
        JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
        WHERE C.StoreID = @storeid
        GROUP BY P.ProductID, P.Name
    );
    GO

• Script

  DELIMITER $$

  CREATE PROCEDURE GetCustomerLevel( IN customerNo INT,
  OUT customerLevel VARCHAR(20) ) BEGIN

    DECLARE credit DEC(10,2) DEFAULT 0;
  
    -- get credit limit of a customer
    SELECT 
        creditLimit 
    INTO credit
    FROM customers
    WHERE 
        customerNumber = customerNo;
  
    -- call the function 
    SET customerLevel = CustomerLevel(credit);
  

  END$$

  DELIMITER ;

• Template

    sys.fn_get_sql ( SqlHandle )  
  
  
    DECLARE @Handle varbinary(64);  
    SELECT @Handle = sql_handle   
    FROM sys.dm_exec_requests   
    WHERE session_id = 52 and request_id = 0;  
    SELECT * FROM sys.fn_get_sql(@Handle);  
    GO  
  

  SQL Script

    CREATE FUNCTION `fn_MemberCheck`(
  
      Level varchar(20)
  
    ) RETURNS int
  
        READS SQL DATA
        DETERMINISTIC
  
    BEGIN
  
      declare fn_level int;
      declare sql_result nvarchar(7000);
      set fn_level = Level;
  
      if fn_level = 0 then 
  
        set sql_result = 
  
          sql_statement_0;
  
      elseif fn_level = 1 then
  
        set sql_result = 
  
          sql_statement_1;
  
      elseif fn_level = 2 then
  
        set sql_result = 
  
            sql_statement_3; 
  
      end if;
  
      return sql_result; /* SQL function has return result.*/
  
    END