This script demonstrates using a record type as a function return value

    

SQL>
SQL>
SQL> CREATE TABLE emp
  2   (emp_id              INTEGER             NOT NULL
  3   ,fname               VARCHAR2(30 CHAR)   NOT NULL
  4   ,mid_name           VARCHAR2(1 CHAR)
  5   ,lname                VARCHAR2(30 CHAR)   NOT NULL
  6   ,CONSTRAINT emp_pk PRIMARY KEY (emp_id));

Table created.

SQL>
SQL>
SQL>
SQL> SET ECHO ON
SQL>
SQL>
SQL> 
SQL> DECLARE
  2     TYPE emp_record IS RECORD (emp_id INTEGER,fname VARCHAR2(30 CHAR),mid_name  VARCHAR2(1 CHAR),lname VARCHAR2(30 CHAR));
  3
  4     emp emp_RECORD;
  5
  6     FUNCTION get_row (emp_id_in INTEGER)
  7     RETURN emp_RECORD IS
  8       CURSOR c (emp_id_cursor INTEGER) IS SELECT * FROM emp WHERE emp_id = emp_id_cursor;
  9
 10     BEGIN
 11       FOR i IN c(emp_id_in) LOOP
 12         RETURN i;
 13       END LOOP;
 14
 15     END get_row;
 16
 17   BEGIN
 18     emp := get_row(1);
 19     dbms_output.put_line(CHR(10));
 20     dbms_output.put_line('emp_ID  : '||emp.emp_id);
 21     dbms_output.put_line('fname     : '||emp.fname);
 22      dbms_output.put_line('mid_name : '||emp.mid_name);
 23     dbms_output.put_line('lname      : '||emp.lname);
 24
 25   END;
 26   /
DECLARE
*
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at line 15
ORA-06512: at line 18


SQL>
SQL>
SQL>
SQL>
SQL> drop table emp;

Table dropped.

SQL>
SQL>

Related examples in the same category

1.	Return varchar2 value from function
2.	Function return Integer
3.	Return value from a function
4.	Use function return value in select statement
5.	Save the returning value from a procedure to a variable
6.	RETURN statement.
7.	Multiple RETURN Statements
8.	Return a type
9.	Concatenates two strings into one:
10.	Return a varray from a function
11.	Append result from generator function to a table
12.	We use user function in DML statements
13.	Use function to check passwords
14.	Use user-defined function to combine and format columns

This script demonstrates using a record type as a function return value : Function Return « Stored Procedure Function « Oracle PL / SQL

Related examples in the same category