How to use a local variable in oracle SQL?

user_13211914 0
Viewed 124

I have some MariaDB SQLs that are called by a bash script, this script set the start and end dates for the queries, but now the project will use Oracle DB.

So, I have something like this in MariaDB SQL:

SET @date_start := '2000-01-01';
SET @date_end := '2001-01-01';
SELECT * FROM user WHERE birth BETWEEN @date_start AND @date_end;

And I couldn't find anything like that in Oracle SQL, already tried DECLARE, DEFINE, WITH, but nothing works

oracle sql
1 Answers

In order to use parameters in Oracle, you have to use PL/SQL. However, if you just want to parameterize a single query, you can use a CTE:

WITH params AS (
      SELECT DATE '2000-01-01' as date_start,
             DATE '2001-01-01' as date_end
      FROM dual
     )
SELECT u.*
FROM params CROSS JOIN
     user u
WHERE u.birth BETWEEN params.date_start AND params.date_end;

Similar logic works in just about any database (although dual may not be needed and the date constants might have different formats).

user_11440350
Related