Tu jest dobry artykuł o funkcjach.
A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.
The General Syntax to create a function is:
CREATE [OR REPLACE] FUNCTION function_name [parameters]
RETURN return_datatype;
IS
Declaration_section
BEGIN
Execution_section
Return return_variable;
EXCEPTION
exception section
Return return_variable;
END;
1) Return Type: The header section defines the return type of the function. The return datatype can be any of the oracle datatype like varchar, number etc.
2) The execution and exception section both should return a value which is of the datatype defined in the header section.
For example, let’s create a frunction called ”employer_details_func’ similar to the one created in stored proc
1> CREATE OR REPLACE FUNCTION employer_details_func
2> RETURN VARCHAR(20);
3> IS
5> emp_name VARCHAR(20);
6> BEGIN
7> SELECT first_name INTO emp_name
8> FROM emp_tbl WHERE empID = '100';
9> RETURN emp_name;
10> END;
11> /
In the example we are retrieving the ‘first_name’ of employee with empID 100 to variable ‘emp_name’.
The return type of the function is VARCHAR which is declared in line no 2.
The function returns the ‘emp_name’ which is of type VARCHAR as the return value in line no 9.
How to execute a PL/SQL Function?
A function can be executed in the following ways.
1) Since a function returns a value we can assign it to a variable.
employee_name := employer_details_func;
If ‘employee_name’ is of datatype varchar we can store the name of the employee by assigning the return type of the function to it.
2) As a part of a SELECT statement
SELECT employer_details_func FROM dual;
3) In a PL/SQL Statements like,
dbms_output.put_line(employer_details_func);
This line displays the value returned by the function.
Zawartość tablicy jako wynik funkcji.
Za http://www.tek-tips.com/faqs.cfm?fid=846.
If you need to return dynamic amounts of data, you probably want to use PL/SQL tables. A PL/SQL table is similar to an array, but it can have only one bound.
PL/SQL tables are sparse. That is if you write to index 1 and index 10, only two values are stored in memory. Oracle doesn’t store NULL values for indexes 2 through 9. If you reference an index that has not been stored yet, you will get a NO_DATA_FOUND exception.
You need to define a TYPE to hold your data before you can use a table. You do that with the oracle TYPE statement.
<type_name> IS TABLE OF ORACLE_DATA_TYPE INDEX BY BINARY_INTEGER;
You can declare the type anywhere declarations are valid. You can put them in a package header or in a DECLARE section. Putting them in a package header makes them sharable between packages and easily reusable. I always create a package header that defines useful table structures an I call it TABLE_TYPES
CREATE OR REPLACE PACKAGE TABLE_TYPES
AS
TYPE tNumber IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE tString IS TABLE OF VARCHAR2(250) INDEX BY BINARY_INTEGER;
END;
If you put the table definitions into their own package you don’t have to worry about cluttering up all your other packages with useless table definitions. Notice, I use the TABLE_TYPES package in the package example below.
When you create a table variable, you can place data into it sparsely, as I said before.. Take this anonymous block for example.
DECLARE
tab TABLE_TYPE.tNumber;
BEGIN
tab(1) := 30;
tab(30) := 1;
tab(2) := tab(30) – tab(1);
DBMS_OUTPUT.ENABLE();
DBMS_OUTPUT.PUT_LINE(‘Index two contains: ‘ || tab(2));
END;
If you run the above anonymous block you should get the output:
Index two contains: -29
PL/SQL procedure successfully completed.
if you only see the last line, type set serveroutput on before running the anonymous block.
The COUNT property of a table returns the total number of rows that are found in that table. If the table is defined sparesly, unfilled rows ARE NOT INCLUDED. In the above anonymous block tab.COUNT will be THREE.
1. CREATE OR REPLACE PACKAGE EXAMPLE_PKG AS
2. FUNCTION myFunction() RETURN TABLE_TYPES.tNumber;
3. PRAGMA RESTRICT_REFERENCES(myFunction,WNDS);
4. END;
5. /
And here is the packge body
1. CREATE OR REPLACE PACKAGE BODY EXAMPLE_PKG AS
2. FUNCTION myFunction() RETURN tNumber
3. – this function returns a table of numbers 1 to 10
4. IS
5. example_table TABLE_TYPES.tNumber;
6. BEGIN
7. FOR indx IN 1 .. 10
8. LOOP
9. example_table(indx) := i;
10. END LOOP;
11. END;
12. /
Finally, we’ll create an anonymous block that calls the myFunction function, gets a table of results and displays them on the screen.
1. DECLARE
2. tab TABLE_TYPES.tNumber;
3. BEGIN
4. tab := EXAMPLE_PKG.myFunction();
5. DBMS_OUTPUT.ENABLE();
6. for i in 1 .. tab.COUNT
7. LOOP
8. DBMS_OUTPUT.PUT_LINE(‘Row ‘ || i || ‘. value = ‘ || tab(i));
9. END LOOP;
10.END;
I DRUGI OPIS
How to return a table from function.
SQL> create type numtab is table of number;
2 /
Type created.
SQL> create type rectype as object (col1 number, col2 date, col3 varchar2(120));
2 /
Type created.
SQL> create type complextab as table of rectype;
2 /
Type created.
SQL> create or replace function f1 return numtab
2 is
3 v_temptab numtab;
4 begin
5 v_temptab := new numtab();
6 for j in 1..20 loop
7 v_temptab.extend();
8 v_temptab(v_temptab.last) := j;
9 end loop;
10 return v_temptab;
11 end;
12 /
Function created.
SQL> create or replace function f2 return complextab
2 is
3 v_temptab complextab;
4 begin
5 v_temptab := new complextab();
6 for j in 1..20 loop
7 v_temptab.extend();
8 v_temptab(v_temptab.last) := new rectype( col1 => j
9 , col2 => sysdate - j
10 , col3 => to_char(to_date(j,'J'),'JSP')
11 );
12 end loop;
13 return v_temptab;
14 end;
15 /
Function created.
SQL> select *
2 from table(f1);
COLUMN_VALUE
------------
1
2
3
4
5
6
7
8
9
10
11
COLUMN_VALUE
------------
12
13
14
15
16
17
18
19
20
20 rows selected.
SQL> select *
2 from table(f2);
COL1 COL2 COL3
---------- --------- --------------------
1 09-MAY-05 ONE
2 08-MAY-05 TWO
3 07-MAY-05 THREE
4 06-MAY-05 FOUR
5 05-MAY-05 FIVE
6 04-MAY-05 SIX
7 03-MAY-05 SEVEN
8 02-MAY-05 EIGHT
9 01-MAY-05 NINE
10 30-APR-05 TEN
11 29-APR-05 ELEVEN
COL1 COL2 COL3
---------- --------- --------------------
12 28-APR-05 TWELVE
13 27-APR-05 THIRTEEN
14 26-APR-05 FOURTEEN
15 25-APR-05 FIFTEEN
16 24-APR-05 SIXTEEN
17 23-APR-05 SEVENTEEN
18 22-APR-05 EIGHTEEN
19 21-APR-05 NINETEEN
20 20-APR-05 TWENTY
20 rows selected.
SQL>
The first is a small numeric table, the second uses not only NUMBER, but also DATE and VARCHAR2.