CODEWITHSUNDEEP
oracle-databaseplsql
Stumbler
Stumbler

Reputation: 2146

Example pl/sql function - elaboration?

I am reading a book which teaches PL/SQL, but like many resources on the subject, provides sketchy details around its examples. Could someone help me work out this one (taken verbatim from the text)? The text provides no sample table upon which the code is used.

The following code creates a function that will return an employee name based on the employee number given:-

CREATE OR replace FUNCTION Getname (p_empno emp.empno%TYPE)
RETURN VARCHAR
IS
  CURSOR empname_cur(
    p_empno emp.empno%TYPE) IS
    SELECT ename
    FROM   emp
    WHERE  empno = p_empno;
  r_empname empname_cur%ROWTYPE;
BEGIN

    OPEN empname_cur(p_empno);    
    FETCH empname_cur INTO r_empname;

    IF empname_cur%NOTFOUND THEN
      r_empname.ename := 'UNKNOWN EMPLOYEE';
    END IF;

    CLOSE empname_cur;   
    RETURN r_empname.ename;
END;

First, the above function declares a cursor to retrieve the employee name, the cursor is then opened, a fetch is performed, if no rows are found then the name is set to UNKNOWN EMPLOYEE, the cursor is closed and the name is returned to the calling program.

The function can be called from a SQL statement as follows:-

SELECT GetName(empno) name
amount
FROM bonus;

So... what is p_empno in the call? is it the table name? Is it a placeholder for an argument, the type of which is made identical to whatever the column emno is?

The line that is most confusing me is 

r_empname empname_cur%ROWTYPE;

What on earth is it doing? What is r_empname? When calling GetName, what is with that trailing "name" unparameterized parameter?

Upvotes: 1

Views: 1625

Answers (2)

Osy
Osy

Reputation: 1633

  • p_empno in the call, is the name of FUNCTION parameter, which will be the CURSOR parameter

    OPEN empname_cur(p_empno);

This sentence indicates that the cursor called empname_cur has a parameter called p_empno. In this context p_empno takes its value from the FUNCTION parameter:

FUNCTION GetName
                    (p_empno emp.empno%TYPE)
  • r_empname is the name of one variable, and empname_cur%ROWTYPE its type, It means that variable domain is taken from cursor empname_cur rowtype, in our case : ename column type from emp table.

  • Last sentence:

    SELECT GetName (empno) name amount FROM bonus;

Must be :

SELECT GetName (empno) name,
amount
FROM bonus;

So "name" is the ALIAS from the result from GetName FUNCTION

Upvotes: 2

sehrope
sehrope

Reputation: 1777

What on earth is it doing? What is r_empname?

PL/SQL allows you to define the type of a variable based on the type of a row, column, or cursor. That line is defining a variable named r_empname that is a record type (eg like a row) that matches the type of row returned back by the empname_cur cursor.

The advantage of the %ROWTYPE syntax is that if you change the SQL query for the cursor the rest of your code will still be strongly typed and compile without additional changes.

When calling GetName, what is with that trailing "name" unparameterized parameter?

The extra name in the SQL query is an alias for the column for the value returned by the function. It's what would display in the column header if you ran that query.

I think you're missing a comma though before the amount field. It probably should be like this:

SELECT GetName (empno) name
     , amount
FROM bonus;

Upvotes: 3

Related Questions