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.