1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

CREATE [ OR REPLACE  ] FUNCTION function_name 
    ( [  { argname [ argmode  ] argtype [  { DEFAULT  | :=  | =  } expression  ]}  [, ...]  ] )
    [ RETURNS rettype [ DETERMINISTIC  ]  | RETURNS TABLE (  { column_name column_type  }  [, ...] )]
    LANGUAGE lang_name 
    [ 
       {IMMUTABLE  | STABLE  | VOLATILE }
        | {SHIPPABLE | NOT SHIPPABLE}
        | WINDOW
        | [ NOT  ] LEAKPROOF  
        | {CALLED ON NULL INPUT  | RETURNS NULL ON NULL INPUT | STRICT } 
        | {[ EXTERNAL  ] SECURITY INVOKER | [ EXTERNAL  ] SECURITY DEFINER | AUTHID DEFINER  | AUTHID CURRENT_USER} 
        | {fenced | not fenced}
        | {PACKAGE}

        | COST execution_cost
        | ROWS result_rows
        | SET configuration_parameter { {TO | =} value | FROM CURRENT }}
     ][...]
    {
        AS 'definition'
        | AS 'obj_file', 'link_symbol'
    };

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

CREATE [ OR REPLACE  ] FUNCTION function_name 
    ( [  { argname [ argmode  ] argtype [  { DEFAULT | := | =  } expression  ] }  [, ...]  ] )
    RETURN rettype [ DETERMINISTIC  ]
    [ 
        {IMMUTABLE  | STABLE  | VOLATILE } 
        | {SHIPPABLE | NOT SHIPPABLE}
        | {PACKAGE}
        | {FENCED | NOT FENCED}
        | [ NOT  ] LEAKPROOF  
        | {CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT } 
        | {[ EXTERNAL  ] SECURITY INVOKER  | [ EXTERNAL  ] SECURITY DEFINER |
AUTHID DEFINER | AUTHID CURRENT_USER
} 
        | COST execution_cost  
        | ROWS result_rows  
        | SET configuration_parameter { {TO | =} value  | FROM CURRENT
        | LANGUAGE lang_name
    ][...] 

    { 
      IS  | AS
} plsql_body
/

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202

--创建一个兼容性为ORA的数据库
CREATE DATABASE ora_compatible_db DBCOMPATIBILITY 'ORA';
CREATE DATABASE
--切换数据库
\c ora_compatible_db
--定义函数为SQL查询。
CREATE FUNCTION func_add_sql(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

--利用参数名用 plpgsql 自增一个整数。
CREATE OR REPLACE FUNCTION func_increment_plsql(i integer) RETURNS integer AS $$
        BEGIN
                RETURN i + 1;
        END;
$$ LANGUAGE plpgsql;

--返回RECORD类型
CREATE OR REPLACE FUNCTION compute(i int, out result_1 bigint, out result_2 bigint)
returns SETOF RECORD
as $$
begin
    result_1 = i + 1;
    result_2 = i * 10;
return next;
end;
$$language plpgsql;

--返回一个包含多个输出参数的记录。
CREATE FUNCTION func_dup_sql(in int, out f1 int, out f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM func_dup_sql(42);

--计算两个整数的和，并返回结果。如果输入为null，则返回null。
CREATE FUNCTION func_add_sql2(num1 integer, num2 integer) RETURN integer
AS
BEGIN 
RETURN num1 + num2;
END;
/
--创建package属性的重载函数
create or replace function package_func_overload(col int, col2  int)
return integer package
as
declare
    col_type text;
begin
     col := 122;
         dbe_output.print_line('two int parameters ' || col2);
         return 0;
end;
/

--创建package_func_overload函数第二个形参参数类型不一样的重载函数。
create or replace function package_func_overload(col int, col2 smallint)
return integer package
as
declare
    col_type text;
begin
     col := 122;
         dbe_output.print_line('two smallint parameters ' || col2);
         return 0;
end;
/

--修改函数add的执行规则为IMMUTABLE，即参数不变时返回相同结果。
ALTER FUNCTION func_add_sql2(INTEGER, INTEGER) IMMUTABLE;

--将函数add的名称修改为add_two_number。
ALTER FUNCTION func_add_sql2(INTEGER, INTEGER) RENAME TO add_two_number;

--将函数add的属者改为omm。
ALTER FUNCTION add_two_number(INTEGER, INTEGER) OWNER TO omm;

--删除函数。
DROP FUNCTION add_two_number;
DROP FUNCTION func_increment_sql;
DROP FUNCTION func_dup_sql;
DROP FUNCTION func_increment_plsql;
DROP FUNCTION package_func_overload(col int, col2  int);
DROP FUNCTION package_func_overload(col int, col2 smallint);
DROP FUNCTION func_add_sql;


-- 不打开参数set behavior_compat_options = 'proc_outparam_override'时，被匿名块或存储过程直接调用的函数的OUT、IN OUT出参不能使用复合类型，并且RETURN值会被当做OUT出参的第一个值导致调用失败
create type rec as(c1 int, c2 int);
create or replace function func(a in out rec, b in out int) return int
as
begin
  a.c1:=100;
  a.c2:=200;
  b:=300;
  return 1;
end;
/
declare
  r rec;
  b int;
begin
  func(r,b);  --不支持
end;
/
ERROR:  cannot assign non-composite value to a row variable
CONTEXT:  PL/SQL function inline_code_block line 4 at SQL statement

--以下示例只有当数据库兼容模式为ORA时可以执行
create or replace package pkg_type as
type table_of_index_int is table of integer index by integer; --创建integer类型
type table_of_index_int01 is table of table_of_index_int index by integer; --创建嵌套integer类型
type table_of_index_var is table of integer index by varchar(5); --创建varchar类型
type table_of_index_var01 is table of table_of_index_var index by varchar(5); --创建嵌套varchar类型
end pkg_type;
/

--创建一个返回table of integer index by integer类型结果的函数
create or replace function func_001(a in out pkg_type.table_of_index_int, b in out pkg_type.table_of_index_var) --#add in & inout #defult value
return pkg_type.table_of_index_int
as
table_of_index_int_val pkg_type.table_of_index_int;
table_of_index_var_val pkg_type.table_of_index_var;
begin
for i in 1..2 loop
table_of_index_int_val(i) := i;
a(i) := i;
table_of_index_var_val(i) := i;
b(i) := i;
end loop;
raise info '%',table_of_index_int_val;
raise info '%',table_of_index_var_val;
raise info '%',a;
raise info '%',b;
return table_of_index_int_val;
end;
/
declare
table_of_index_int_val pkg_type.table_of_index_int;
table_of_index_var_val pkg_type.table_of_index_var;
begin
func_001(table_of_index_int_val,table_of_index_var_val);
end;
/

INFO:  {indexbyType:int,1=>1,2=>2}
CONTEXT:  PL/SQL function inline_code_block line 5 at SQL statement
INFO:  {indexbyType:varchar,"1"=>1,"2"=>2}
CONTEXT:  PL/SQL function inline_code_block line 5 at SQL statement
INFO:  {indexbyType:int,1=>1,2=>2}
CONTEXT:  PL/SQL function inline_code_block line 5 at SQL statement
INFO:  {indexbyType:varchar,"1"=>1,"2"=>2}
CONTEXT:  PL/SQL function inline_code_block line 5 at SQL statement
ERROR:  expression is of wrong type
CONTEXT:  PL/SQL function inline_code_block line 5 at SQL statement

create or replace function func_001(a in out date, b in out date) --#add in & inout #defult value
return integer
as
begin
    raise info '%', a;
    raise info '%', b;
return 1;
end;
/
declare
date1 date := '2022-02-02';
date2 date := '2022-02-02';
begin
    func_001(date1, date2);
end;
/
INFO:  2022-02-02 00:00:00
CONTEXT:  PL/SQL function inline_code_block line 5 at SQL statement
INFO:  2022-02-02 00:00:00
CONTEXT:  PL/SQL function inline_code_block line 5 at SQL statement
ERROR:  invalid input syntax for type timestamp: "1"
CONTEXT:  PL/SQL function inline_code_block line 5 at SQL statement

create or replace function func_001(a in out INT, b in out date) --#add in & inout #defult value
return INT
as
begin
    raise info '%', a;
    raise info '%', b;
return a;
end;
/
declare
date1 int := 1;
date2 date := '2022-02-02';
begin
    func_001(date1, date2);
end;
/
INFO:  1
CONTEXT:  PL/SQL function inline_code_block line 5 at SQL statement
INFO:  2022-02-02 00:00:00
CONTEXT:  PL/SQL function inline_code_block line 5 at SQL statement
ANONYMOUS BLOCK EXECUTE