07新特性iSQLPlus.ppt

iSQL*Plus 目标 通过本章学习，您将可以: 在查询中使用变量。 熟悉 iSQL*Plus 环境。 非管理员登录方式 1.启动服务器上的Http server 2. 通过浏览器访问http服务器 3.访问isqlplus http://localhost:7778/isqlplus 输入用户名口令等信息就可以登陆到数据库，并执行SQL语句及脚本文件 变量 变量 在变量名前加前缀 () 使用户输入值。 变量 字符和日期型变量 在子符和日期两端加单引号。 指定列名、表达式和文本 使用变量可以提供下面的内容: WHERE 条件 ORDER BY 子句 列表达式 表名 整个 SELECT 语句 指定列名、表达式和文本 7-* Copyright ? Oracle Corporation, 2001. All rights reserved. 7 Copyright ? Oracle Corporation, 2001. All rights reserved. I want to query different values. ... salary = ? … … department_id = ? … ... last_name = ? ... User SELECT employee_id, last_name, salary, department_id FROM employees WHERE employee_id = employee_num ; 1 2 101 SELECT last_name, department_id, salary*12 FROM employees WHERE job_id = job_title ; SELECT employee_id, last_name, job_id, column_name FROM employees WHERE condition ORDER BY order_column ; Schedule: Timing Topic 35 minutes Lecture 35 minutes Practice 70 minutes Total Lesson Aim In this lesson, you will learn how to include iSQL*Plus commands to produce more readable SQL output. You can create a command file containing a WHERE clause to restrict the rows displayed. To change the condition each time the command file is run, you use substitution variables. Substitution variables can replace values in the WHERE clause, a text string, and even a column or a table name. Substitution Variables The examples so far have been hard-coded. In a finished application, the user would trigger the report, and the report would run without further prompting. The range of data would be predetermined by the fixed WHERE clause in the iSQL*Plus script file. Using iSQL*Plus, you can create reports that prompt the user to supply their own values to restrict the range of data returned by using substitution variables. You can embed substitution variables in a command file or in a single SQL statement. A variable can be thought of as a container in which the values are temporarily stored. When the state