CodeSOD: This Is Your Brain on PL/SQL
source link: https://www.tuicool.com/articles/hit/2YVZ32I
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
The realest of real WTFs, the reigning champion for all eternity is and forever will be Oracle . Today, we’re going to take a look at a little code in PL/SQL.
PL/SQL is a weird language, a blend of SQL and, well, a P rocedural L anguage, with a side of OO slapped on. The syntax does an excellent job giving you the feeling that it was designed in the 1970s, and each new feature or change to the language continues that tradition.
The structure of any PL/SQL code unit is going to be built around blocks . Each block represents a self-contained namespace. The basic anatomy is:
DECLARE -- variable declarations go here BEGIN -- code goes here EXCEPTIONS -- exception handling code goes here, using WHEN clauses END;
If you’re writing a stored procedure, or a trigger, you replace the DECLARE
keyword with CREATE [OR REPLACE]
. You can also nest blocks inside of other blocks, so it’s not uncommon to see code structured like this:
BEGIN DECLARE --stuff BEGIN --actions END; --more actions END;
Yes, that does get confusing very quickly. And yes, if you want to really approximate structured error handling, you have to start nesting blocks inside of each other.
The language and database have other fun quirks. They didn’t get an IDENTITY
column type until version 12c. In prior versions, you needed to use a SEQUENCE
object and write procedures or triggers to actually force the autonumbering. You’d usually use a SELECT INTO…
statement to populate a variable, with the bonus that Oracle SQL always requires a table in the FROM
clause, so you have to use the made up table dual
, e.g.:
CREATE TRIGGER "SOME_TABLE_AUTONUMBER" BEFORE INSERT ON "SOME_TABLE" FOR EACH ROW BEGIN SELECT myseq.nextval INTO :new.id FROM dual; END;
:new
in this context represents the row we’re autonumbering. That’s the “normal” way to create autonumbered columns in older versions of Oracle. Boneist found a different, slightly less normal way to do the same thing:
CREATE OR REPLACE TRIGGER "SCHEMA1"."TABLE1_TRIGGER" BEFORE INSERT ON "SCHEMA1"."TABLE1" FOR EACH ROW BEGIN DECLARE pl_error_id table1.error_id%TYPE; CURSOR get_seq IS SELECT table1_seq.nextval FROM dual; BEGIN OPEN get_seq; FETCH get_seq INTO pl_error_id; IF get_seq%NOTFOUND THEN raise_application_error(-20001, 'Sequence TABLE1_SEQ does not exist'); CLOSE get_seq; END IF; CLOSE get_seq; :new.error_id := pl_error_id; END; END table1_trigger;
There’s a lot going on here. First off, note that our DECLARE
section contains a CURSOR
statement. Cursors let you iterate across records. They’re very expensive, and in Oracle-land, they’re a resource that must be released.
This trigger uses a nested block for no particular reason. It also uses an extra variable, pl_error_id
which isn’t necessary.
But the real weird part here is the IF get_seq%NOTFOUND
block. That’s pretty simple: if our cursor didn’t return a row. This is something that, with this cursor, can’t possibly happen, so we’ll never hit this. The sequence will always return a value. That’s a good thing, if you look at the code which follows.
raise_application_error
is Oracle’s “throw” equivalent. It will crawl up the stack of executing blocks until it finds an EXCEPTIONS
section to handle the error. Note that we close the cursor after that statement- thus, we never actually close the cursor. Cursors, as mentioned, are expensive, and Oracle only lets you have so many of them.
Here we have a weird case of a developer defending against an error that can’t happen in a way which would eventually lead to more errors.
[Advertisement] Otter - Provision your servers automatically without ever needing to log-in to a command prompt. Get started today!
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK