Interactive SQL statements are SQL statements that ask you for a variable, parameter, or some form of data before fully executing. Say you have a SQL statement that is interactive. The statement is used to create users into a database. The SQL statement could prompt you for information such as user ID, name of user, and phone number. The statement could be for one or many users, and would be executed only once. Otherwise, each user would have to be entered individually with the CREATE USER statement. The SQL statement could also prompt you for privileges. Not all vendors have interactive SQL statements; you must check your particular implementation. The following sections show some examples of interactive SQL using Oracle.
Parameters are variables that are written in SQL and reside within an application. Parameters can be passed into an SQL statement during runtime, allowing more flexibility for the user executing the statement. Many of the major implementations allow use of these parameters. The following sections show examples of passing parameters for Oracle and Sybase.
Parameters in Oracle can be passed into an otherwise static SQL statement.
SELECT EMP_ID, LAST_NAME, FIRST_NAME FROM EMPLOYEE_TBL WHERE EMP_ID = '&EMP_ID'
The preceding SQL statement returns the EMP_ID, LAST_NAME, and FIRST_NAME for whatever EMP_ID you enter at the prompt.
SELECT * FROM EMPLOYEE_TBL WHERE CITY = '&CITY' AND STATE = '&STATE'
The preceding statement prompts you for the city and the state. The query returns all data for those employees living in the city and state that you entered.
Parameters in Sybase can be passed into a stored procedure.
CREATE PROC EMP_SEARCH (@EMP_ID) AS SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEE_TBL WHERE EMP_ID = @EMP_ID
Type the following to execute the stored procedure and pass a parameter:
SP_EMP_SEARCH "443679012"