CODEWITHSUNDEEP
sqloracle-databasestored-procedures
AAA
AAA

Reputation: 2450

Can an Oracle Procedure produce output records?

I am new to Oracle 10g and know that in MS SQL Server I can create a procedure such as the one below to generate an output record. Is this possible with a standard Oracle Procedure or do I need to use a package/function?

CREATE PROCEDURE SAMPLE_STORED_PROCEDURE 
@USERNAME varchar(10)
AS
BEGIN
SELECT NAME as Output from Employee where Username = @USERNAME
END
GO

Thank you

Upvotes: 0

Views: 86

Answers (1)

Justin Cave
Justin Cave

Reputation: 231711

Assuming that username is unique in employee (which seems like a reasonable guess to me), you probably want a function

CREATE OR REPLACE FUNCTION function_name( p_username IN employee.username%type )
  RETURN employee.name%type
IS
  l_name employee.name%type;
BEGIN
  SELECT name
    INTO l_name
    FROM employee
   WHERE username = p_username;

  RETURN l_name;
END function_name;

You could also use a procedure with an OUT parameter

CREATE OR REPLACE PROCEDURE procedure_name( p_username IN employee.username%type,
                                            p_name    OUT employee.name%type )
AS
BEGIN
  SELECT name
    INTO p_name
    FROM employee
   WHERE username = p_username;
END procedure_name;

Generally, in PL/SQL you'd want to use a function whenever you want to write some sort of "getter" that reads data from the database and a procedure whenever you want to write some sort of "setter" that writes data to the database. So a function would make more sense here. Both functions and procedures can and should be organized into packages that allow you to group together related functionality. It would probably make sense, for example, to have a package that groups together all the functions and procedures related to adding, modifying, deleting, and reading information about employees.

If my original guess that username is not unique in employee is incorrect and you expect your select statement to return multiple rows, there are a number of different choices depending on exactly what you're going to be doing with that data. You can write a function that returns a sys_refcursor. You can write a function that returns a collection. You can write a pipelined table function. Without knowing more, however, it's impossible to know which of these options would make more sense in your case.

Upvotes: 1

Related Questions