Oracle Procedure vs SQL Server Procedure

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.

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

Open SQLPlus then...

Code:

---

-- 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;

---

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.

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?

Quote:

---

Originally Posted by p_ruzic

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.