At the day when this post was published, I didn’t have many ideas about “how database works”. As for RDBMS, as for NoSQL solutions. But someday I accidentally got one SQL task.
The goal – database storage for communications company and database automation by procedures package. Not a big deal – just a student IT-competition jury were my “customers”. The catch was in one fact: I should made it in Oracle database. So, I started with Express version and SQL Developer – IDE for database management. And with zero knowledges about PL/SQL language. But it was finished by happy-end.
No, I don’t want to build arrogant boasting for petty job. Here will be my first impressions about Oracle Database. This post is severed on small conclusions-advices. It’s not a tutorial, for advised learning you have a lot of sources else . It’s a kind of vital knowledge for professional needs.
What is the PL/SQL language?
PL/SQL is a query language imagined as SQL extension. The difference in details: data types, internal functions, loops syntax and so on. Quick start is possible if you have a luggage of SQL knowledge. But also you can get stuck compiled your programs on Oracle environment. That’s why it’s worth a special subject.
1. Don’t mess up with data types
Oracle Database has unusual name of types. Basically for string data you’ll use VARCHAR2. What is the difference compared with simple VARCHAR? VARCHAR counts NULL and empty string as two special values, VARCHAR2 – as one. Doesn’t really matter, but check this, if you need to keep your table cell empty, but without NULL use.
Instead of INT you’ve got NUMBER. It’s an outstanding mix of INT and FLOAT. If you want to use INT – just leave the type by default. For FLOAT or DOUBLE usage you need to point number of figures after comma. In data type determination it looks like NUMBER (10, 5) with 10 figures limit before comma and 5 – after.
Also Oracle suggests to use DATE for time tracking. But it’s just a record of any day and no more information else. If you’d like to organize your DB more precisely – use TIMESTAMP.
2. Exception handling is cool!
Your functions and procedures might be finished incorrectly and you won’t have any idea why it is. There might be incorrect parameter, table constraint violation or anything else. Of course, you can parse kilometers of Oracle log file. But frankly, is it a great solution for troubleshooting? Exception handling block gives you a flexibility to follow on SQL-program behaviors. EXCEPTION operator works like CASE, executing instructions dependent on type of exception. You can call type by yourself with RAISE operator.
DECLARE exception_name EXCEPTION; BEGIN IF condition THEN RAISE exception_name; END IF; EXCEPTION WHEN exception_name THEN statement; WHEN others THEN statement; END;
OTHERS is a PL/SQL keyword for any error else. The best way to record errors – logging to special table. Table can include error time, procedure name, SQL error code (SQLCODE) and message (SQLERRM).
3. Want to use procedures? Use packages
Packages can merge all automated operations going on your database. It’s pretty comfortable to store functions, procedures, variables and triggers in one logical construction. PL/SQL package includes 2 parts. One part is a package itself including content specification. Another part is a package body which contains a list of operations. The simple syntax:
CREATE OR REPLACE PACKAGE c_package AS -- Adds a customer PROCEDURE addCustomer(c_id customers.id%type, c_name customers.name%type, c_age customers.age%type, c_addr customers.address%type, c_sal customers.salary%type); -- Removes a customer PROCEDURE delCustomer(c_id customers.id%TYPE); --Lists all customers END c_package; CREATE OR REPLACE PACKAGE BODY c_package AS PROCEDURE addCustomer(c_id customers.id%type, c_name customers.name%type, c_age customers.age%type, c_addr customers.address%type, c_sal customers.salary%type) IS BEGIN INSERT INTO customers (id,name,age,address,salary) VALUES(c_id, c_name, c_age, c_addr, c_sal); END addCustomer; PROCEDURE delCustomer(c_id customers.id%type) IS BEGIN DELETE FROM customers WHERE id = c_id; END delCustomer; END c_package;
One sad fact – package doesn’t support conception “many procedures – one exception handling”. Sometimes exception logic is identical for each package procedure. But operator should be declared many times with the same commands. One way to fix it – functions using.
4. Use BETWEEN to compare TIMESTAMP and DATE
Usually we’re trying to compare non-equal types of data through format conversion. In case of TIMESTAMP you can use whatever you want. But in my experience most part of conversion was useless. In fact it’s a bad game with date mask, order of data pieces and so on. The best way to restrict value in desired time range – BETWEEN and INTERVAL combination. Example for one day catch.
--TIME_REC attribute is a TIMESTAMP, p_date variable is a date-- SELECT * INTO FROM FCT_TRAFFIC WHERE TIME_REC BETWEEN p_date AND p_date + (INTERVAL '1' DAY);
5. Oracle can’t understand February 29
One of my goals was attributes counting for each day. When I set date as February 29, 2016 Oracle stopped my procedure with incorrect date exception. For big data this case might be terrible and horrible, cause operations for one full day could be lost! It looks like “Year 2038 problem”. So keep it in mind, when you’re choosing between DB systems and check this issue on all engines you could use.
6. Want to control table parameters dynamically? Use EXECUTE IMMEDIATE!
Sometimes you want to use CREATE TABLE and ALTER TABLE for multiple tables modification. In functions and procedures direct operation appliance is forbidden by PL/SQL interpreter. But there is a pleasant workaround – EXECUTE IMMEDIATE operator! It’s almost the same command as EXECUTE. Use it to set up table parameters dynamically. Example of external tables modification.
-- p_* and v_* is just a variables declared and set before execution -- v_varchar := 'CREATE OR REPLACE DIRECTORY FILES_DIR AS ''' || p_dir || ''''; EXECUTE IMMEDIATE v_varchar; span style="font-weight: 400;"> v_varchar := 'ALTER TABLE ' || p_table || ' DEFAULT DIRECTORY FILES_DIR'; v_varchar := 'ALTER TABLE ' || p_table || ' LOCATION (''' || p_file || ''')'; EXECUTE IMMEDIATE v_varchar; v_varchar := 'ALTER TABLE ' || p_table || ' ACCESS PARAMETERS (FIELDS TERMINATED BY ''' || p_symbol || ''')'; EXECUTE IMMEDIATE v_varchar;
7. NVL to get rid from NULL
NULL is really nasty for attribute comparison and aggregation. Frequently we wish to set another default value instead. Of course, it could be done in CREATE TABLE, but what if it doesn’t? MS SQL has ISNULL function, MySQL – IFNULL. Oracle – NVL (decrypted as Null VaLue Logic). NVL accepts 2 arguments: name of aimed attribute and value which you want to set in place of NULL.
NVL (attribute, 0) OR NVL (attribute, 'N/A')
8. REGEXP_REPLACE for queries stereotyping
One of multitudinous PL/SQL syntax pearls compared with weak LIKE keyword. You have a reliable regular expression engine for value replacement by special format. This operator includes all main regexp canons.
regexp_replace(S_BD, '00.', '01.') --replaces 00 to 01-- regexp_replace(SD_DOC, '[^[:digit:]]') --leaves only numbers--
9. ELSE IF? ELSEIF? ELIF? ELSIF! AND THEN! AND END IF!
Condition operator syntax is one of contemporary programming milestones. Switching between languages we have to adopt IF/ELSE organization. Microsoft-used TRANSACT-SQL has IF…ELSE syntax. PL/SQL way is a little bit more complicated but more powerful too. You can include any number of conditions.
IF monthly_value <= 4000 THEN ILevel := 'Low Income'; ELSIF monthly_value > 4000 and monthly_value <= 7000 THEN ILevel := 'Avg Income'; ELSIF monthly_value > 7000 and monthly_value <= 15000 THEN ILevel := 'Moderate Income'; ELSE ILevel := 'High Income'; END IF;
10. Be careful with timestamp mask
Timestamp mask understanding format is important for TO_TIMESTAMP conversion. You could be confused with month format. Think it over: MM for integer format, MON for abbreviation, MONTH – for full month name. Also Oracle includes American type of time by default. The main difference in 12-hour format. So, if you want to set European format this way…
TO_TIMESTAMP(03.02.2016 18.03.25, 'DD.MM.YYYY HH.MI.SS')
…you will catch DBMS error for incorrect hour format. The right way for you is:
TO_TIMESTAMP(03.02.2016 18.03.25, 'DD.MM.YYYY HH24.MI.SS')
11. Want to load data from file? Use external tables!
Oracle doesn’t support LOAD DATA INFILE operator like MySQL. Instead they have no less comfortable external tables. Originally it’s just a link to file (TXT, CSV or anything else) with a list of data formatting options. During the query execution Oracle Database loads data from source file on the fly. One advice: double check the syntax and test the data loading. Cause any SQL option could be placed on incorrect order or file could be corrupted. Here is a simple external table.
CREATE TABLE "CAZORLA19"."SRC_FCT_TRF" ( "TIME_REC" CHAR(20 BYTE), "SUBS_NUMBER" VARCHAR2(6 BYTE), ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY "FILES_DIR" ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE SKIP 1 FIELDS TERMINATED BY ';' MISSING FIELD VALUES ARE NULL (TIME_REC, SUBS_NUMBER) ) LOCATION ( 'file_gprs.csv' ) ) REJECT LIMIT UNLIMITED ;
12. Such useful cursors
If you want to insert or update many records on table through procedure – use cursor loop. It’s not a PL/SQL distinction, it’s a rule for all SQL instances. But it’s still useful. Just catch a SELECT query result and deploy it on INSERT/UPDATE or even DELETE!
DECLARE c_name customers.name%type; c_addr customers.address%type; CURSOR c_customers is SELECT name, address FROM customers; BEGIN OPEN c_customers; LOOP FETCH c_customers into c_name, c_addr; EXIT WHEN c_customers%notfound; INSERT INTO john_customers (name, address) VALUES (c_name, c_addr) WHERE c_name = “John”; END LOOP; CLOSE c_customers; END;
13. Many aggregators for one column in one table? Use CASE!
Query can group aggregation functions just by one column and there we could stop. But aggregators could accept a simple CASE condition as an argument. All you need – just set desired parameter for counting!
The goal: count statuses recorded in general column, when query is grouped by procedure name. Solution:
SELECT UNIQUE PROCEDURE_NAME PROCEDURE, COUNT(*) CALLS, COUNT (CASE STATUS_KEY WHEN 'OK' THEN 1 END) SUCCESSED, COUNT (CASE STATUS_KEY WHEN 'PRM_IN_ERR' THEN 1 END) PARAM_ERRORS, COUNT (CASE STATUS_KEY WHEN 'ERR' THEN 1 END) DBMS_ERRORS FROM CTL_PRC_CALL_LOG GROUP BY PROCEDURE_NAME;
Oracle solution for data management is not the most popular, but it’s not the worst. They started to adopt MySQL features (which was swallowed by Oracle, lol). But also their RDBMS have a huge list of special distinguishes that will be saved for many years. Some part it’s great, when you need more tools than default SQL suggests. Some part it’s bad, because the main RDBMS benefit – storage portability – is disappeared. Cause many things doing in your MySQL or Postgres won’t work in Oracle and backwards situation is the same. Anyway: we need Oracle! Hope someday I’ll publish a post about Oracle database mechanisms and workflow.
P.S.: thanks a lot for some code examples, TutorialsPoint!