can i get procedure sample in oracle ?

Question

hi

i try to understand procedure..... but still dont.....

i need simple sample for procedure.

i need procedure that i'll insert Fname and Lname, and i get table with the result search

how i can do it ? i need package ????..... or cursor ????........

work on oracle 10g

thank's in advance

Answer 1

After re-reading your question again, I realize I missed your original intent. It appears you want to return a result set based on the first and last name passed to a function/procedure.

This should get you headed in the right direction.

   CREATE OR REPLACE PACKAGE pkg_example
    AS
      type name_query_cursor is ref cursor;
    END;

    CREATE OR REPLACE PROCEDURE sp_doSomething(in_fname  IN VARCHAR,
                                               in_lname  IN VARCHAR,
                                               my_cursor IN OUT pkg_example.name_query_cursor)
    IS
    BEGIN
           OPEN my_cursor FOR
            SELECT lname,fname,userid
            FROM yourTableName
            WHERE fname = in_fname AND lname = in_lname;

    END;

EDIT: To check/run it, you can do something like:

CREATE OR REPLACE PROCEDURE check_procedure (in_fname IN VARCHAR,
                                             in_lname IN VARCHAR)
 AS
 v_cur pkg_example.name_query_cursor;

 -- This is assuming the 3 columns above are all the columns
 -- in the table.  If not, either create your own record type
 -- in pkg_example, or create three separate vars
 -- for each column and FETCH into them
 v_row yourTableName%rowtype;
 BEGIN

 sp_doSomething(in_fname, in_lname, v_cur);

 LOOP
     FETCH v_cur into v_row;
     EXIT when v_cur%NOTFOUND;
     dbms_output.put_line ('FName: ' || v_row.fname || '  LName: ' || v_row.lname);
 END LOOP;

 END check_procedure;


-- Then from your SQL prompt execute the check_procedure
SQL>  execute check_procedure ('bob', 'cline')

Answer 2

You wouldn't typically use a procedure for a search query:

CREATE OR REPLACE FUNCTION my_search_function (IN_FNAME IN TABLE.FNAME%TYPE,
                                               IN_LNAME IN TABLE.LNAME%TYPE)
  RETURN SYS_REFCURSOR IS

  results_cursor SYS_REFCURSOR;

BEGIN

  OPEN results_cursor FOR
    SELECT t.*
      FROM TABLE t
     WHERE (IN_FNAME IS NULL OR t.fname LIKE '%' || IN_FNAME || '%')
       AND (IN_LNAME IS NULL OR t.lname LIKE '%' || IN_LNAME || '%');

  RETURN results_cursor;

END;