create or replace function func1 return integer as
ret integer;
begin
select nvl(max(col1),0) into ret from tab1;
return ret;
exception when others then
return -1;
end;
/
select func1 from dual;
SHOW FUNCTION STATUS\G
show create function func1;
CREATE FUNCTION func1 (s CHAR(20))
RETURNS CHAR(50) DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!');
SELECT func1('world');
delimiter //
CREATE FUNCTION func2(i INT)
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
RETURN i;
END//
delimiter ;
delimiter //
CREATE FUNCTION f2()
RETURNS CHAR(36) CHARACTER SET utf8
BEGIN
RETURN UUID();
END//
delimiter ;
set global log_bin_trust_function_creators = 1;
\df+
CREATE OR REPLACE FUNCTION func1(i integer) RETURNS integer AS $$
BEGIN
RETURN i + 1;
END;
$$ LANGUAGE plpgsql;
select func1(123);
--スカラー値関数
CREATE FUNCTION func1
(
@param1 int
)
RETURNS int
AS
BEGIN
DECLARE @ret int;
SELECT @ret = SUM(col1)
FROM tab1 t
WHERE t.col2 >= @param1
IF (@ret IS NULL)
SET @ret = 0
RETURN @ret
END
go
declare @rv int
select @rv = [dbo].[func1](3)
print @rv
go
--テーブル値関数
CREATE FUNCTION dbo.func2
(
@param1 int
)
RETURNS TABLE
AS
RETURN
(
SELECT * from tab1 t
where t.col2 > @param1
)
go
select * from dbo.func2(2)
go
