Forum Stats

  • 3,783,348 Users
  • 2,254,761 Discussions
  • 7,880,371 Comments

Discussions

Creating stored procedure

8a4589dc-fc34-47e6-b470-53597b99558e
edited Sep 4, 2015 6:15AM in SQL & PL/SQL

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.

Tagged:

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,645 Red Diamond
    edited Sep 4, 2015 5:53AM

    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
    edited Sep 4, 2015 5:58AM

    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
    BluShadow Member, Moderator Posts: 41,645 Red Diamond
    edited Sep 4, 2015 6:13AM

    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
    Karthick2003 Member Posts: 13,711 Bronze Badge
    edited Sep 4, 2015 6:15AM
    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.

This discussion has been closed.