Oracle Procedure vs SQL Server Procedure

[p_ruzic]

Hello!

I am new to Oracle and SQL plus. If anyone could help me understand procedures in oracle and how it works I’d greatly appreciate it.
Actually I have been working for quite time in SQL Server. So in SQL Server you can create simple stored procedure like


Create proc pr_Orders
As
Begin

Select * From dbo.Order

End

Then you execute that procedure like

Exec pr_ Orders

And you get output in your query analyzer

How I can create same simple procedure in Oracle and have output in is-sql ?


Thank you very much.

[PAVB]

Oracle stored procedures work pretty much the same way SQL Server ones do.

Open SQLPlus then...

-- Create your procedure
CREATE PROCEDURE pr_orders 
AS
BEGIN
. . . Do your stuff here
END pr_orders;
/


-- Execute your procedure
variable my_cursor refcursor;
exec pr_orders(:my_cursor);
print my_cursor;

[p_ruzic]

Thank you for your answer. But I have tried to created this simple procedure

Create Or Replace procedure pr_empFullName
Is

BEGIN

Select Employee_ID, First_Name || ' ' || Last_Name FullName, Salary From Employees;

END pr_empFullName;
/

and I got this error

[Error] PLS-00428 (9: 6): PLS-00428: an INTO clause is expected in this SELECT statement

Since this would work just fine in SQL Server then what am I missing here, if, like you said Oracle procedures works pretty much the same as in SQL Server.

Thank you very much.

[stecal]

Results are returned into variables. Same way you use variables in T-SQL, but the syntax is a bit different.

Have you read anything on PL/SQL or do you expect to learn it all in a forum?
http://www.plsql-tutorial.com/

How did you learn T-SQL?

[PAVB]

Since this would work just fine in SQL Server then what am I missing here, if, like you said Oracle procedures works pretty much the same as in SQL Server.

Let me re-phrase it.
Conceptually, Oracle and SQL Server storedprocs work in a pretty similar way.
Having said that, PL/SQL and T-SQL are two different languages, the same way you cannot expect to be fluent in Portuguese just because you are fluent in Spanish you cannot expect T-SQL syntax to work just fine in PL/SQL.