Home » SQL & PL/SQL » SQL & PL/SQL » how to make stored procedures in ORACLE
icon4.gif  how to make stored procedures in ORACLE [message #207681] Wed, 06 December 2006 10:01 Go to next message
Dodge
Messages: 10
Registered: November 2006
Location: Slovenia
Junior Member
Hi everyone,
i have a small question on how to make a stored procedure in sql.
I tried to use:
CREATE PROCEDURE my_procedure
AS
BEGIN
SELECT *
FROM my_table;
END myprocedure;

But it doesnt work...i guess there is something missing...like INTO statement...but i was wondering like what kind of table should i use or better said what kind of table do i use?

Thnx for all your help.
Re: how to make stored procedures in ORACLE [message #207683 is a reply to message #207681] Wed, 06 December 2006 10:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That depends entirely on what you are trying to achieve.
It's rarely true that the best solution is to select an entire table into your procedure and process it.
Re: how to make stored procedures in ORACLE [message #207701 is a reply to message #207681] Wed, 06 December 2006 12:22 Go to previous messageGo to next message
Dodge
Messages: 10
Registered: November 2006
Location: Slovenia
Junior Member
Yes i know...but still i wanted to write this as a simple test...and select for procedure i wrote doesnt work...

Thnx for your help!
Re: how to make stored procedures in ORACLE [message #207704 is a reply to message #207701] Wed, 06 December 2006 12:36 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You wanted it simple, so you can have it. But, are you ready for refcursors? Here's an example. First, create a procedure the way you'd like it to:
CREATE OR REPLACE PROCEDURE my_proc (par IN OUT sys_refcursor) IS
BEGIN
  OPEN par FOR  SELECT * FROM DEPT;
END;
/

Now let's see what we got:
SQL> var dept refcursor;
SQL> exec my_proc(:dept);

PL/SQL procedure successfully completed.

SQL> print :dept

    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 MANAGING       ZAGREB
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

Is that what you meant?
Re: how to make stored procedures in ORACLE [message #207707 is a reply to message #207704] Wed, 06 December 2006 12:48 Go to previous messageGo to next message
Dodge
Messages: 10
Registered: November 2006
Location: Slovenia
Junior Member
Thnx , ya i guess thats what i meant,
hmm damn i must say its in a way a bit different from what
i m used to in MSSQL.

Thank you very much for helping me!
Re: how to make stored procedures in ORACLE [message #207709 is a reply to message #207704] Wed, 06 December 2006 12:50 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
OR you could do as jrow suggested and select one column

Create or replace procedure my_procedure(vi_emp_no number) is

v_emp_name  emp.emp_name%type;

begin


select emp_name into v_emp_name from emp
where emp_no = vi_emp_no;


dbms_output.put_line(v_emp_no);

exception when no_data_found then
   dbms_output.put_line('INVALID EMPLOYEE');

end;
Re: how to make stored procedures in ORACLE [message #207725 is a reply to message #207707] Wed, 06 December 2006 15:22 Go to previous message
Dodge
Messages: 10
Registered: November 2006
Location: Slovenia
Junior Member
Ya but i wanted to upgrade my procedure modifying select to selecting data from more then one table...and i was wondering how i can do that...and if i use SELECT* ... instead of declaring every column i choose and specifying it in SELECT clause, into some table
i get more values but still...i basically just wanted to see how i can make simple select procedure ... with as little "constraints"(meaning selecting specific columns(one or more) and using INTO clause, etc.)

Previous Topic: How to minus rows where the site id is blank from the total records between a from and to date.
Next Topic: and or where
Goto Forum:
  


Current Time: Sat Dec 03 19:50:58 CST 2016

Total time taken to generate the page: 0.07926 seconds