PL_SQL compares with T_SQL

Question

PL_SQL compares with T_SQL

December 30, 2019 at 7:33 pm

This was removed by the editor as SPAM

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

sqlfriends - Tuesday, September 12, 2017 1:28 PM
I see on this page: https://en.wikipedia.org/wiki/PL/SQL

PL/SQL (Procedural Language/Structured Query Language

I think the "PL" part of that is how you had to get at the results from a GUI. I've not worked with Oracle for 14 or 15 years but I was always mortified that I'd write a beautiful SELECT to do something but there was no way for the GUI to get at the results of the SELECT. Our standard was to write a package and have a "Global Reference Cursor" that was responsible for passing the data from whatever SELECT you wrote to the GUI. For batch jobs, there was no need of the "Global Reference Cursor".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Jeff Moden - Tuesday, September 12, 2017 10:03 PM
sqlfriends - Tuesday, September 12, 2017 1:28 PM
I see on this page: https://en.wikipedia.org/wiki/PL/SQL

PL/SQL (Procedural Language/Structured Query Language
I think the "PL" part of that is how you had to get at the results from a GUI. I've not worked with Oracle for 14 or 15 years but I was always mortified that I'd write a beautiful SELECT to do something but there was no way for the GUI to get at the results of the SELECT. Our standard was to write a package and have a "Global Reference Cursor" that was responsible for passing the data from whatever SELECT you wrote to the GUI. For batch jobs, there was no need of the "Global Reference Cursor".

Ah, yes, the Reference Cursor. Hated it, but as you said, the only way to return a result set back to the calling application.

sqlfriend SSC Guru Points: 52769 More actions · Answer 3

thanks all.
I also see the cursor is used for the reason to log or handle errors for the specific record (id, or other columns) that caused the error, while set based cannot do that.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 4

If I remember correctly, in Oracle everything is a cursor behind the scenes, even if you don't explicitly declare one. See the Handling Explicit Cursors section in this page:
http://docs.oracle.com/cd/B10501_01/appdev.920/a97269/pc_actun.htm

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 5

Chris Harshman - Wednesday, September 13, 2017 10:59 AM
If I remember correctly, in Oracle everything is a cursor behind the scenes, even if you don't explicitly declare one. See the Handling Explicit Cursors section in this page:
http://docs.oracle.com/cd/B10501_01/appdev.920/a97269/pc_actun.htm

If you fire up an SQL Profiler run on SQL Server and have it return "Text" that contains the word "Cursor", I believe you'll find that SQL Server operates in a similar manner when it comes to front end code and, sometimes, backend code. The difference in SQL Server seems to be that you don't need to explicitly declare a cursor to return a result set to the GUI or other external thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sue_H SSC Guru Points: 90860 More actions · Answer 6

Chris Harshman - Wednesday, September 13, 2017 10:59 AM
If I remember correctly, in Oracle everything is a cursor behind the scenes, even if you don't explicitly declare one. See the Handling Explicit Cursors section in this page:
http://docs.oracle.com/cd/B10501_01/appdev.920/a97269/pc_actun.htm

When using PL/SQL that is correct and why I mentioned that a couple of times in the posts here. The links for the poster explains this some and how to avoid explicit cursors.