Variables in Oracle

The question is simple: we have an Oracle query and we want to use one search criterium as a parameter. Let us take a simple example. We want to use a parameter in the SQL statement: select * from doel where teller = parameter. So we can write the SQL only once and we can apply the SQL with different parameter values.
For this example, we use a table doel.

drop table doel;

CREATE TABLE "DOEL" 
   (	"TELLER" NUMBER, 
	"NAAM" VARCHAR2(20 BYTE)
   );
Insert into DOEL (TELLER,NAAM) values ('1','Piet');
Insert into DOEL (TELLER,NAAM) values ('2','Jan');
Insert into DOEL (TELLER,NAAM) values ('3','Klaas');
commit;

Solution 1. We write a SQL statement with a bind variable. We define the bind variable first and we then use that bind variable in a SQL statement.

var zoek number;
exec :zoek := 2;
select * from doel where teller =:zoek;

Solution 2. We create a situation where we use a substitution variable.

accept p_zoek prompt "Please enter teller number: " default 10;

select * from doel where teller = &p_zoek
/

Solution 3. We explicitly define a substitution variable. We use that substitution variable in a subsequent SQL statement.

DEFINE P_zoek = 2;

SELECT * 
FROM doel 
WHERE teller = &P_zoek;

Solution 4. We write a PL/SQL procedure. This PL/SQL procedure has a parameter. This procedure can be executed with exec zoek(2);.

CREATE OR REPLACE PROCEDURE ZOEK 
  (P_ZOEK IN NUMBER) AS 
  select_s  VARCHAR(225);
BEGIN
  select_s := ' select * from doel where teller = '||P_ZOEK;
  EXECUTE IMMEDIATE select_s;
END ZOEK;