PL_SQL compares with T_SQL

  • Jeff Moden

    SSC Guru

    Points: 995144

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Lynn Pettis

    SSC Guru

    Points: 442180

    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: 52358

    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: 41847

    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: 995144

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Sue_H

    SSC Guru

    Points: 90287

    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.

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

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