-
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;
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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.
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|