Skip to content

Commit 6800b3c

Browse files
author
Steven Feuerstein
committed
dynamic SQL files
1 parent 9734dd5 commit 6800b3c

10 files changed

+1498
-0
lines changed

‎plsql/dynamic-sql/bind-not-concatenate.sql

Lines changed: 132 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,132 @@
1+
/*
2+
When you are writing a program with dynamic SQL in it (that is, you construct
3+
your statement at runtime and execute it with EXECUTE IMMEDIATE or DBMS_SQL -
4+
most likely and preferably the former), you should make sure to bind all variable
5+
values into that statement, and not concatenate.
6+
*/
7+
8+
-- Update Any Column In Table - Concatenation
9+
/*
10+
Here's a great example of a too-generic subprogram: update any numeric
11+
(well, I suppose it would work for strings, too) column in the employees
12+
table whose hire date falls within the specified range. I use concatenation
13+
to put it all together. That leads to lots of typing, lots of code to look out
14+
and lots of single quotes. It is unlikely that a program as generic as this will ever truly be needed!
15+
*/
16+
CREATE OR REPLACE PROCEDURE updnumval (
17+
col_in IN VARCHAR2
18+
, start_in IN DATE
19+
, end_in IN DATE
20+
, val_in IN NUMBER
21+
)
22+
IS
23+
c_format CONSTANT VARCHAR2 ( 100 ) := 'YYYYMMDDHH24MISS';
24+
BEGIN
25+
EXECUTE IMMEDIATE 'UPDATE employees SET '
26+
|| col_in
27+
|| ' = '
28+
|| val_in
29+
|| ' WHERE hire_date BETWEEN TO_DATE ('''
30+
|| TO_CHAR ( start_in, c_format )
31+
|| ''', '''
32+
|| c_format
33+
|| ''') AND TO_DATE ('''
34+
|| TO_CHAR ( end_in, c_format )
35+
|| ''', '''
36+
|| c_format
37+
|| ''')';
38+
END;
39+
/
40+
41+
BEGIN
42+
updnumval ('salary',
43+
DATE '2002-01-01',
44+
DATE '2002-12-31',
45+
20000);
46+
END;
47+
/
48+
49+
SELECT *
50+
FROM employees
51+
WHERE salary = 20000 ;
52+
53+
-- Well You Don't Need All Those Single Quotes
54+
/*
55+
A rewrite of the original, still full of concatenation, but now using the Q literal
56+
terminator feature to specify another character as the terminator of the literal,
57+
so I can avoid having code like ''','''. I'm not convinced this is all that much better -
58+
the benefit is more obvious when you are a large dynamic SQL statement as a single
59+
string - full of doubled-up quotes.
60+
*/
61+
CREATE OR REPLACE PROCEDURE updnumval (
62+
col_in IN VARCHAR2
63+
, start_in IN DATE
64+
, end_in IN DATE
65+
, val_in IN NUMBER
66+
)
67+
IS
68+
c_format CONSTANT VARCHAR2 ( 100 ) := 'YYYYMMDDHH24MISS';
69+
BEGIN
70+
EXECUTE IMMEDIATE 'UPDATE employees SET '
71+
|| col_in
72+
|| ' = '
73+
|| val_in
74+
|| q'[ WHERE hire_date BETWEEN TO_DATE (']'
75+
|| TO_CHAR ( start_in, c_format )
76+
|| q'[', ']'
77+
|| c_format
78+
|| q'[') AND TO_DATE (']'
79+
|| TO_CHAR ( end_in, c_format )
80+
|| q'[', ']'
81+
|| c_format
82+
|| q'[')]';
83+
END;
84+
/
85+
86+
BEGIN
87+
updnumval ('salary',
88+
DATE '2002-01-01',
89+
DATE '2002-12-31',
90+
30000);
91+
END;
92+
/
93+
94+
SELECT *
95+
FROM employees
96+
WHERE salary = 30000 ;
97+
98+
-- Switch to Binding with USING Clause
99+
/*
100+
Now I rewrite the procedure to bind everything I can possibly bind:
101+
the column value, low and high dates. My code is much simpler, easier to read,
102+
and is performance-optimized (more likely to avoid unnecessary parsing).
103+
Note that I cannot bind in the column NAME. That information is needed in
104+
order to parse the SQL statement (which comes before binding).
105+
*/
106+
CREATE OR REPLACE PROCEDURE updnumval (col_in IN VARCHAR2,
107+
start_in IN DATE,
108+
end_in IN DATE,
109+
val_in IN NUMBER)
110+
IS
111+
BEGIN
112+
EXECUTE IMMEDIATE
113+
'UPDATE employees SET '
114+
|| col_in
115+
|| ' = :val
116+
WHERE hire_date BETWEEN :lodate AND :hidate'
117+
USING val_in, start_in, end_in;
118+
END;
119+
/
120+
121+
BEGIN
122+
updnumval ('salary',
123+
DATE '2002-01-01',
124+
DATE '2002-12-31',
125+
40000);
126+
END;
127+
/
128+
129+
SELECT *
130+
FROM employees
131+
WHERE salary = 40000 ;
132+

‎plsql/dynamic-sql/dbms-assert.sql

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,40 @@
1+
/*
2+
Use DBMS_ASSERT to help guard against SQL injection.
3+
4+
The DBMS_ASSERT package was introduced in Oracle 10g Release 2. It contains functions
5+
that help developers sanitize user input and reduce the likelihood of SQL injection
6+
in applications that concatenate text (they do NOT use bind variables).
7+
*/
8+
9+
BEGIN
10+
sys.DBMS_OUTPUT.put_line (DBMS_ASSERT.schema_name ('HR'));
11+
END;
12+
/
13+
14+
BEGIN
15+
sys.DBMS_OUTPUT.put_line (DBMS_ASSERT.sql_object_name ('EMPLOYEES'));
16+
END;
17+
/
18+
19+
BEGIN
20+
sys.DBMS_OUTPUT.put_line (DBMS_ASSERT.qualified_sql_name ('HR.EMPLOYEES'));
21+
END;
22+
/
23+
24+
BEGIN
25+
sys.DBMS_OUTPUT.put_line (DBMS_ASSERT.schema_name ('HR'));
26+
END;
27+
/
28+
29+
BEGIN
30+
sys.DBMS_OUTPUT.put_line (DBMS_ASSERT.schema_name ('WHO_ME'));
31+
END;
32+
/
33+
34+
BEGIN
35+
DBMS_OUTPUT.put_line (
36+
DBMS_ASSERT.sql_object_name (
37+
'EMPLOYEES, (SELECT * FROM ALL_USERS) u'));
38+
END;
39+
/
40+

‎plsql/dynamic-sql/dynamic-method-3.sql

Lines changed: 104 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,104 @@
1+
/*
2+
Method 3 Dynamic SQL: a SELECT statement whose select list (number of elements
3+
returned by the query) and bind variables are fixed at compile-time. For more
4+
information on dynamic SQL in PL/SQL:
5+
6+
https://stevenfeuersteinonplsql.blogspot.com/2016/07/a-quick-guide-to-writing-dynamic-sql-in.html
7+
*/
8+
9+
CREATE TABLE employees
10+
AS
11+
SELECT * FROM hr.employees ;
12+
13+
-- Concatenate Table Name, Bind One Variable, Return Two Values
14+
-- Yes, all of that! Notice that I don't trust the table name provided.
15+
-- I use DBMS_ASSERT to make sure it's a valid table name.
16+
DECLARE
17+
l_last_name employees.last_name%TYPE;
18+
l_salary employees.salary%TYPE;
19+
20+
PROCEDURE show_value (table_in IN VARCHAR2, id_in IN INTEGER)
21+
IS
22+
BEGIN
23+
EXECUTE IMMEDIATE
24+
'SELECT last_name, salary FROM '
25+
|| sys.DBMS_ASSERT.sql_object_name (table_in)
26+
|| ' WHERE employee_id = :id_value'
27+
INTO l_last_name, l_salary
28+
USING id_in;
29+
30+
DBMS_OUTPUT.put_line (l_last_name || ' Earning ' || l_salary);
31+
END;
32+
BEGIN
33+
show_value ('EMPLOYEES', 138);
34+
END;
35+
/
36+
37+
DECLARE
38+
l_employee employees%ROWTYPE;
39+
BEGIN
40+
EXECUTE IMMEDIATE 'SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = :empid'
41+
INTO l_employee
42+
USING 138;
43+
44+
DBMS_OUTPUT.put_line (l_employee.last_name);
45+
END allrows_by;
46+
/
47+
48+
-- Use BULK COLLECT with EXECUTE IMMEDIATE
49+
-- Sure, why not? Get all or specified set of rows and load a collection!
50+
DECLARE
51+
TYPE employee_ntt IS TABLE OF employees%ROWTYPE;
52+
53+
l_employees employee_ntt;
54+
BEGIN
55+
EXECUTE IMMEDIATE 'SELECT * FROM employees' BULK COLLECT INTO l_employees;
56+
57+
DBMS_OUTPUT.put_line (l_employees.COUNT);
58+
END allrows_by;
59+
/
60+
61+
-- Use OPEN FOR to Fetch From Dynamic SELECT
62+
-- In Oracle8, when native dynamic SQL was first introduced, you could not
63+
-- use BULK COLLECT with EXECUTE IMMEDIATE. So, instead, you could use OPEN
64+
-- FOR to assign a cursor variable to the dynamic SELECT, and then FETCH BULK COLLECT.
65+
66+
DECLARE
67+
l_cursor SYS_REFCURSOR;
68+
69+
TYPE employee_ntt IS TABLE OF employees%ROWTYPE;
70+
71+
l_employees employee_ntt;
72+
BEGIN
73+
OPEN l_cursor FOR 'SELECT * FROM employees';
74+
75+
FETCH l_cursor BULK COLLECT INTO l_employees;
76+
77+
DBMS_OUTPUT.put_line (l_employees.COUNT);
78+
79+
CLOSE l_cursor;
80+
END allrows_by;
81+
/
82+
83+
-- And With a LIMIT Clause
84+
DECLARE
85+
l_cursor SYS_REFCURSOR;
86+
87+
TYPE employee_ntt IS TABLE OF employees%ROWTYPE;
88+
89+
l_employees employee_ntt;
90+
BEGIN
91+
OPEN l_cursor FOR 'SELECT * FROM employees';
92+
93+
LOOP
94+
FETCH l_cursor BULK COLLECT INTO l_employees LIMIT 100;
95+
96+
EXIT WHEN l_employees.COUNT = 0;
97+
98+
DBMS_OUTPUT.put_line (l_employees.COUNT);
99+
END LOOP;
100+
101+
CLOSE l_cursor;
102+
END allrows_by;
103+
/
104+

0 commit comments

Comments
 (0)