We sometimes need to include a single quote in an SQL statement, I believe you already know the normal way, use two single quote characters to express one single quote. However, if you are using oracle 10g or a more recent version, the following way could be better:
This is a new feature of 10g that enables us to embed single-quotes in literal strings without having to resort to double, triple or sometimes quadruple quote characters. This is particularly useful for building dynamic SQL statements that contain quoted literals.
The mechanism is invoked with a simple "q" in PL/SQL only. The syntax is q'[...]', where the "[" and "]" characters can be any of the following as long as they do not already appear in the string.
Note that at the time of writing, the quoting mechanism only appears to work with 10g clients/OCI. If used with any software that uses an older Oracle client it fails with ORA-01756: quoted string not properly terminated (confirmed with sqlplus, TOAD and PL/SQL Developer).
If you want samples, see Reference
Occasionally, we will need to define
local used functions in a public procedure or a function. Fortunately, PL/SQL supports embedded functions. I spent more than an hour trying to find the correct way to use this feature. Yes. forgive me, I am a little dumb. According to my retries, the best position to put your local functions is sharp before the
BEGIN symbol. And here's a sample.
set serveroutput on;
CREATE OR REPLACE PROCEDURE tmp12345(v_full_name IN VARCHAR2) AS
-- local function definitaions start ---
FUNCTION FETCH_REAL_NAME(v_statement_name IN VARCHAR2) RETURN VARCHAR2
pattern VARCHAR2(100) := '^(Dr|Ms|Mx|Mr|Mrs)\s+';
-- replace possible multiple spaces to one space
full_name := TRIM(REGEXP_REPLACE(v_statement_name, '\s+', ' '));
IF (REGEXP_LIKE(full_name, pattern, 'i')) THEN
-- REGEXP_REPLACE(string, pattern [, replacement_string [, start_position [, nth_appearance [, match_parameter ] ] ] ])
RETURN REGEXP_REPLACE(full_name, pattern, '', 1, 0, 'i');
FUNCTION FETCH_MIDDLE_NAME(full_name IN VARCHAR) RETURN VARCHAR AS
middle_name := SUBSTR(full_name, INSTR(full_name, ' ') + 1, INSTR(full_name, ' ', 1, 2) - INSTR(full_name, ' ') - 1);
IF (LENGTH(middle_name) > 10) THEN
-- return initial instead
RETURN SUBSTR(middle_name, 1, 1);
-- local functions definitaions end ---
-- main logic here
localv := v_full_name;
In MySQL, we can
SELECT IF(A IS NULL, "something", A) FROM some_table;
If you want to do a similar thing in PL/SQL, you need to
SELECT (CASE WHEN A IS NULL THEN "something" ELSE A END) FROM some_table;
If you want to get the first non-null value from several fields, you have a better choice than using the
SELECT COALESCE(A, B) FROM some_table
see more information about the Oracle COALESCE function