How to bind a variable to an Oracle SQL statement

The following article describes how to bind a variable to an Oracle SQL statement and how to use Oracle to execute an SQL statement to bind a variable, the following describes the relevant content of the article.

1. SQL * PLUS command for receiving and defining variables

ACCEPT

DEFINE UNDEFINE

&

2. Example of binding a variable SQL statement

Example of binding a variable SQL statement (1)

SQL> select id, last_name, salary from s_emp where dept_id = & department_number;

Enter value for department_number: 10

Old 1: select id, last_name, salary from s_emp where dept_id = & department_number;

New 1: select id, last_name, salary from s_emp where dept_id = 10

SQL> SET VERIFY OFF | ON; you can close or open the prompt to confirm the display of information old 1 and new 1.

Example of variable binding SQL statement (2)

SQL> select id, last_name, salary

From s_emp

Where title = '& job_title ';

Enter value for job_title: Stock Clerk

11

J2EE @ zxw

SQL> select id, last_name, salary

From s_emp

Where hiredate> to_date ('& start_hire_date', 'yyyy-MM-DD ');

Enter value for start_hire_date: 2001-01-01

When Oracle binds a variable to a string or a date-type variable when executing an SQL statement, the variable must be enclosed in single quotes or a variable can be bound to query different field names; other Symbols

3. ACCEPT syntax and example

SQL> ACCEPT variable [datatype] [FORMAT] [PROMPT text] [HIDE]

Description: variable refers to the variable name datatype refers to the variable type, such as number, char, and other formats refer to the variable display lattice.

-Type prompt text: You can customize the content of the pop-up prompt text hide to hide users' input symbols

Example of Using ACCEPT:

ACCEPT p_dname PROMPT 'provide the department name :'

ACCEPT p_salary number prompt 'salary amount :'

ACCEPT pswd char prompt 'password: 'hide

ACCEPT low_date date format 'yyyy-MM-DD 'PROMPT "Enter the low date range ('yyyy-MM-DD '):"

4. DEFINE syntax and example

SQL> DEFINE variable = value

Description: variable refers to the variable name value refers to the variable value.

After the variable yield value is defined, the input variable is no longer prompted when the SQL statement bound to the variable is executed.

DEFINE example:

SQL> DEFINE dname = sales

SQL> DEFINE dname

DEFINE dname = "sales" (CHAR)

SQL> select name from dept where lower (name) = '& dname ';

NAME

Sales

Sales

SQL> UNDEFINE dname

SQL> DEFINE dname

Symbol dname is UNDEFINED

5. PASS Parameters in SQL * PLUS to the saved *. SQL File

SQL> @/path name/file name parameter name 1 [, parameter name 2,...]

SQL> start/path name/file name parameter name 1 [, parameter name 2,...]

The above content is an introduction to variable binding when Oracle executes SQL statements. I hope you will find some gains.