в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:11:56Z

1 версия

Новая страница

== Datatype family of parameters==

Datatype families are groups of similar datatypes.
For example, CHAR and VARCHAR2 are used to describe exactly the same kind of textual data, so they belong to the same family.
You can overload only between different families.
The following code is an example of declaring a different datatype family:



<source lang="sql">
SQL> declare
2 function getArea(i_rad NUMBER, i_prec NUMBER) return NUMBER is
3 v_pi NUMBER:=3.14;
4 begin
5 return trunc(v_pi * (i_rad ** 2),i_prec);
6 end;
7 function getArea(i_rad NUMBER, i_ignore_yn VARCHAR2) return NUMBER is
8 v_pi NUMBER:=3.14;
9 begin
10 if i_ignore_yn="Y" and i_rad < 5 then
11 return 0;
12 else
13 return v_pi * (i_rad ** 2);
14 end if;
15 end;
16 begin
17 DBMS_OUTPUT.put_line("Area (R=3):"||getArea(3,1));
18 DBMS_OUTPUT.put_line("Area (R=3):"||getArea(3,"N"));
19 end;
20 /
Area (R=3):28.2
Area (R=3):28.26
PL/SQL procedure successfully completed.</source>

== More than one match was found so Oracle raised a special error.==



<source lang="sql">
SQL>
SQL> declare
2 function getArea(i_rad NUMBER) return NUMBER
3 is
4 v_pi NUMBER:=3.14;
5 begin
6 return v_pi * (i_rad ** 2);
7 end;
8 function getArea(i_length NUMBER, i_width NUMBER:=3) return NUMBER
9 is
10 begin
11 return i_length * i_width;
12 end;
13 begin
14 DBMS_OUTPUT.put_line("Area (R=3):"||getArea(3));
15 end;
16 /
DBMS_OUTPUT.put_line("Area (R=3):"||getArea(3));
*
ERROR at line 14:
ORA-06550: line 14, column 40:
PLS-00307: too many declarations of "GETAREA" match this call
ORA-06550: line 14, column 4:
PL/SQL: Statement ignored

SQL></source>

== Names of parameters==

You can overload program units by using different names of parameters as long as you use named notation when you call the program units.



<source lang="sql">
SQL>
SQL> declare
2 function getArea(i_rad NUMBER, i_prec NUMBER)
3 return NUMBER
4 is
5 v_pi NUMBER:=3.14;
6 begin
7 return trunc(v_pi * (i_rad ** 2),i_prec);
8 end;
9 function getArea(i_length NUMBER, i_width NUMBER)
10 return NUMBER
11 is
12 begin
13 return i_length * i_width;
14 end;
15 begin
16 DBMS_OUTPUT.put_line("Area (R=3): "||getArea(i_rad=>3,i_prec=>1));
17 DBMS_OUTPUT.put_line("Area (2x3): "||getArea(i_length=>2,i_width=>3));
18 end;
19 /
Area (R=3): 28.2
Area (2x3): 6
PL/SQL procedure successfully completed.</source>

== Number of parameters==



<source lang="sql">
SQL>
SQL> declare
2 function getArea(i_rad NUMBER)
3 return NUMBER
4 is
5 v_pi NUMBER:=3.14;
6 begin
7 return v_pi * (i_rad ** 2);
8 end;
9 function getArea(i_length NUMBER, i_width NUMBER)
10 return NUMBER
11 is
12 begin
13 return i_length * i_width;
14 end;
15 begin
16 DBMS_OUTPUT.put_line("Area (R=3):"||getArea(3));
17 DBMS_OUTPUT.put_line("Area (2x3):"||getArea(2,3));
18 end;
19 /
Area (R=3):28.26
Area (2x3):6
PL/SQL procedure successfully completed.
SQL>
SQL></source>

== Overloading calls==

You can declare local or packaged stored procedures with exactly the same name, as long as their parameters are different by at least one of these factors:
<OL><LI>the number of parameters,</LI><LI>names of parameters,</LI><LI>order of parameters, or</LI><LI>the datatype family of the parameters.</LI></OL>
27. 19. Overloading
27. 19. 1.
Overloading calls
27. 19. 2.
<A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/Numberofparameters.htm">Number of parameters</a>
27. 19. 3.
<A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/Namesofparameters.htm">Names of parameters</a>
27. 19. 4.
<A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/Datatypefamilyofparameters.htm">Datatype family of parameters</a>
27. 19. 5.
<A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/Therearesomerestrictionsonoverloading.htm">There are some restrictions on overloading:</a>
27. 19. 6.
<A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/MorethanonematchwasfoundsoOracleraisedaspecialerror.htm">More than one match was found so Oracle raised a special error.</a>

== There are some restrictions on overloading:==

You can"t overload standalone procedures or functions.
The second definition simply overwrites the first one.
You can"t overload functions that differ only by the datatype of the return value.
If you need to implement this requirement, use overloaded procedures with OUT parameters.



<source lang="sql">
SQL> declare
2 function getArea(i_rad NUMBER) return NUMBER
3 is
4 v_pi NUMBER:=3.14;
5 begin
6 return v_pi * (i_rad ** 2);
7 end;
8 function getArea (i_length NUMBER, i_width NUMBER) return NUMBER
9 is
10 begin
11 return i_length * i_width;
12 end;
13 begin
14 DBMS_OUTPUT.put_line("Area (R=3): " ||getArea(3));
15 DBMS_OUTPUT.put_line("Area (R=3): " ||getArea("3"));
16 end;
17 /
Area (R=3): 28.26
Area (R=3): 28.26
PL/SQL procedure successfully completed.</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Function Procedure Packages/Overloading - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия