How to bind a variable to an Oracle SQL statement

Source: Internet
Author: User
Tags dname

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.