CODEWITHSUNDEEP
sqloracle-database
Tony Wolff
Tony Wolff

Reputation: 742

Using define in simple sql statement

Is there a way of defining a section of SQL as a constant for repeated use in a simple oracle SQL statement. In a similar way that can be done in a script and in a similar way to the WITH statement?

Something like

Define meat as "foodtype<>'fruit' and foodtype<>'veg'"

select * from meals where meat

Upvotes: 2

Views: 4383

Answers (2)

Exhausted
Exhausted

Reputation: 1885

As per your question it seems that the condition should be dynamic. It can be acheived in PLSQL Block like below

DECLARE
  v1 varchar2(100) := ' foodtype <> ''fruit'' and foodtype <> ''Veg''';
  mealstyp meals%ROWTYPE; 
BEGIN
  EXECUTE IMMEDIATE 'select * from meals where'||v1 into mealstyp; 
  LOOP
    --your type record
  END LOOP;
END

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

In SQL*Plus you could use DEFINE.

For example,

SQL> define l_str = 'ename = ''SCOTT'''
SQL>
SQL> select empno, deptno from emp where &l_str;
old   1: select empno, deptno from emp where &l_str
new   1: select empno, deptno from emp where ename = 'SCOTT'

     EMPNO     DEPTNO
---------- ----------
      7788         20

SQL>

NOTE DEFINE is a SQL*Plus command. Define sets a user variable or displays its value.

However, not possible in plain SQL as the query won't parse and would throw an error. Since, during parse time, Oracle would expect it to be static, you cannot have it dynamic.

You can use Dynamic SQL in PL/SQL, such that you could have it as a string variable and use it repeatedly in the scope of the session.

For example,

SQL> DECLARE
  2    var_str  VARCHAR2(1000);
  3    var_str1 VARCHAR2(1000);
  4    v_empno emp.empno%type;
  5  BEGIN
  6    var_str  := 'ename = ''SCOTT''';
  7    var_str1 := 'select empno FROM emp WHERE '|| var_str;
  8    EXECUTE IMMEDIATE var_str1 INTO v_empno;
  9
 10    dbms_output.put_line(v_empno);
 11
 12  END;
 13  /
7788

PL/SQL procedure successfully completed.

SQL>

Upvotes: 1

Related Questions