Creating stored procedure

8a4589dc-fc34-47e6-b470-53597b99558e

Hi All, I am a new oracle user and i needed some help please.

I have a table called staff with info such as name, grade etc.

I want to create a stored procedure so grade was a parameter and it will bring back staff at that grade.

For example the simple SQL would be select firstname, lastname FROM staff where grade = 1

How would i go about creating a stored procedure so i could use grade as a parameter.

Would it be something like this:

Create or replace procedure grade.proc

(grade AS number)

IS

Begin

?

Thank you for your help, i am sorry for this is very basic.

BluShadow

what do you mean by "bring back"?

A procedure doesn't 'output' anything, so what you mean by "bring back" will depend on how the procedure is defined, or whether in fact you need a function.

8a4589dc-fc34-47e6-b470-53597b99558e

Sorry my knowledge is very limited. I want a firstname and lastname to be brought back when i put in a grade. I guess i mean a function then. Sorry

BluShadow

That still doesn't explain what you mean by "brought back".

What code is calling this procedure/function?

Is it another procedure/function?

Do you need to call  it in an SQL query?

Is it even Oracle code calling it? or is it .NET/Java etc?

(edit to add)

Does it just need to fetch the details from a single row on the table, or does it need to return multiple details from many rows?

There are many ways to return data from a function (or even a procedure if necessary), but the 'correct' way depends a lot on what is calling the code and how it needs to be used.

Karthick2003

8a4589dc-fc34-47e6-b470-53597b99558e wrote:

Hi All, I am a new oracle user and i needed some help please.
I have a table called staff with info such as name, grade etc.

I want to create a stored procedure so grade was a parameter and it will bring back staff at that grade. 
For example the simple SQL would be select firstname, lastname FROM staff where grade = 1


How would i go about creating a stored procedure so i could use grade as a parameter. 


Would it be something like this:


Create or replace procedure grade.proc 
(grade AS number)
IS
Begin


?


Thank you for your help, i am sorry for this is very basic.

In any programming language there are 2 type of methods.

1. Setter methods

2. Getter methods.

Oracle implementation of Setter Method is PROCEDURE and Getter Method is FUNCTION.

So first step for you to decide is, is your method going to be a getter or setter. From what you say I think its Getter method. So I would suggest you go for a FUNCTION.

Now once that decided, we need to look into what we are getting.

Are we getting a scalar value or a non-scalar value? Based on that we need to define our RETURN type for the function.

In your case you are returning a non-scalar value. Next is the non-scalar value going to be a single non-scalar value or a collection of non-scalar value. I assume there can be multiple staff with same grade. So it can be a collection of non-scalar values or can be termed as a result set.

So when returning a result set you can return it as a REFCURSOR or as a COLLECTION type.

I will show a small demonstration using EMP table.

Example using REFCURSOR

SQL> create or replace function get_emp_rc (deptno in emp.deptno%type) return sys_refcursor
  2  as
  3    rc sys_refcursor;
  4  begin
  5    open rc for select empno, ename from emp where deptno = get_emp_rc.deptno;
  6    return rc;
  7  end;
  8  /

Function created.

SQL> var rc refcursor
SQL>
SQL> exec :rc := get_emp_rc(10)

PL/SQL procedure successfully completed.

SQL> print rc

    EMPNO ENAME
---------- ------
      7782 CLARK
      7839 KING

Example using SQL collection type

SQL> create or replace type empobj as object (empno number, ename varchar2(100))
  2  /

Type created.

SQL> create or replace type emptbl as table of empobj
  2  /

Type created.

SQL> create or replace function get_emp_coll (deptno in emp.deptno%type) return emptbl
  2  as
  3    rc emptbl;
  4  begin
  5    select empobj(empno, ename) bulk collect into rc from emp where deptno = get_emp_coll.deptno;
  6    return rc;
  7  end;
  8  /

Function created.

SQL> select *
  2    from table(get_emp_coll(10));

    EMPNO ENAME
---------- --------------------
      7782 CLARK
      7839 KING

You can make the second approach even better using a pipelined table function. But lets not get into that now.