Oracle--SQL技巧之一(查询连续的...-ORA-01219-Oracle编程入门经典第2章 SQLPlus和基本查询

当前位置: 数据库>oracle

本页文章导读:

▪Oracle--SQL技巧之一(查询连续的记录) 需求说明：需要查询出某个客户某一年那些天是有连续办理过业务实现SQL如下：创建表：create table test_num(tyear number, tdate date); 测试数据：insert into.........

▪ORA-01219 ORA-01219:database not open:queries allowed on fixed tables/views only“ORA-01219:数据库未打开：仅允许在固定表/视图中查询”的警告窗口！解决办法：登录Oracle SQL*Plususername TIDYpas.........

▪Oracle编程入门经典第2章 SQLPlus和基本查询怎样使用SQL*Plus工具连接数据库以及执行查询怎样配置SQL*Plus，以格式化查询结果SQL语句的不同种类怎样在数据库上编写查询来查看数据怎样修改存储在数据库中的数据2.1 SQL*Plus简介SQL*Plus是一.........

[1]Oracle--SQL技巧之一(查询连续的记录)

来源: 发布时间: 2013-10-16

需求说明：

需要查询出某个客户某一年那些天是有连续办理过业务

实现SQL如下：

创建表：

create table test_num
(tyear number,
tdate date);

测试数据：

insert into test_num
select 2014,trunc(sysdate)-1 from dual union all
select 2014,trunc(sysdate)-002 from dual union all
select 2014,trunc(sysdate)-003 from dual union all
select 2014,trunc(sysdate)-004 from dual union all
select 2014,trunc(sysdate)-005 from dual union all
select 2014,trunc(sysdate)-007 from dual union all
select 2014,trunc(sysdate)-008 from dual union all
select 2014,trunc(sysdate)-009 from dual union all
select 2013,trunc(sysdate)-120 from dual union all
select 2013,trunc(sysdate)-121 from dual union all
select 2013,trunc(sysdate)-122 from dual union all
select 2013,trunc(sysdate)-124 from dual union all
select 2013,trunc(sysdate)-125 from dual union all
select 2013,trunc(sysdate)-127 from dual union all
select 2015,trunc(sysdate)-099 from dual union all
select 2015,trunc(sysdate)-100 from dual union all
select 2015,trunc(sysdate)-101 from dual union all
select 2015,trunc(sysdate)-102 from dual union all
select 2015,trunc(sysdate)-104 from dual union all
select 2015,trunc(sysdate)-105 from dual;

写SQL:
SELECT TYEAR, MIN(TDATE) AS STARTDATE, MAX(TDATE), COUNT(TYEAR) AS ENDNUM
FROM (SELECT A.*, A.TDATE - ROWNUM AS GNUM
FROM (SELECT * FROM TEST_NUM ORDER BY TYEAR, TDATE) A)
GROUP BY TYEAR, GNUM
ORDER BY TYEAR, MIN(TDATE)

本文链接

[2]ORA-01219

来源: 发布时间: 2013-10-16

ORA-01219:database not open:queries allowed on fixed tables/views only

“ORA-01219:数据库未打开：仅允许在固定表/视图中查询”的警告窗口！

解决办法：

登录Oracle SQL*Plus

username TIDY

password TIDY

PL/SQL打开Tables etc

这一段是找出打开数据库的时候报错的地方

(1)SQL> select open_mode from v$database;

OPEN_MODE
　　----------
　　MOUNTED

(2)SQL> alter databaseopen;

　　ERROR at line 1:
　　ORA-01157: cannot identify/lock data file 4 - see DBWR tracefile
　　ORA-01110: data file 4: 'E:/MYBANK.dbf'

这里就是讲'E:/MYBANK.dbf'这个文件出问题

我们要做的就是删掉这个残留文件

(3)SQL> alter databasedatafile 'E:/MYBANK.dbf' offline drop;

Database altered.

(4)SQL> alter databaseopen;

Database altered.

SQL>

注意，当你同时出现ora-01110这个错误，那么你要检查你数据文件存在不

本文链接

[3]Oracle编程入门经典第2章 SQLPlus和基本查询

来源: 发布时间: 2013-10-16

怎样使用SQL*Plus工具连接数据库以及执行查询
怎样配置SQL*Plus，以格式化查询结果
SQL语句的不同种类
怎样在数据库上编写查询来查看数据
怎样修改存储在数据库中的数据

2.1 SQL*Plus简介

SQL*Plus是一个用于连接Oracle数据库的工具，具有可以满足Oracle用户和管理员需求的大量功能，包括：

在数据库中执行SQL和PL/SQL
更新数据库中的数据
执行数据查询
将查询结果集格式化为报表
建立、编辑、检索和执行SQL脚本
帮助Oracle用户调整SQL查询
管理数据库
描述数据库中的表和PL/SQL对象
将数据从一个数据库复制到另一个数据库
向用户发送消息，接受这些用户的输入

2.1.1 SQL、PL/SQL和SQL*Plus之间的区别

SQL：结构化查询语言，或者是用来将数据放入数据库，从数据库检索数据，控制事务处理以及管理数据库的语言。

PL/SQL：Oracle的过程化编程语言，用户可以使用它编写在数据库中执行的定制程序以及过程代码。

SQL*Plus：是用户可以用来编写SQL和PL/SQL脚本的接口，即一个接口工具。

以账户（即用户名称）：scott，默认密码（即口令）：tiger 登录数据库（创建数据库，会自动分配一个演示账号scott）

SQL*Plus: Release 9.2.0.1.0 - Production on 星期六 12月 29 10:34:34 2012

连接到:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

SQL> select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> begin

2 for cur in (select * from DEPT) loop

3 dbms_output.put_line(cur.dname || ' is the department name now.');

4 end loop;

5 end;

6 /

PL/SQL 过程已成功完成。

SQL>

2.1.2 启动SQL*Plus

1. 命令行模式的SQL*Plus

Unix：$ORACLE_HOME/bin/sqlplus
Windows：%ORACLE_HOME5/bin/sqlplus.exe

这个目录应该在用户的PATH环境变量中。

SYS是数据词典的所有者，只应用于管理的目的。SYS的默认密码是CHANGE_ON_INSTALL。
SYSTME是一个管理员账号。SYSTEM的默认密码是MANAGER。
SCOTT是一个演示账号。SCOTT的默认密码是TIGER。

试验：连接Oracle

(1) 在DOS或者XTERM中，在提示符下输入sqlplus，打开一个SQL*Plus会话。

(2) 使用用户名SCOTT和密码TIGER登录。

工作原理

2. 通过网络连接

配置C:\oracle\ora92\network\ADMIN的文件sqlnet.ora

# SQLNET.ORA Network Configuration File: C:\oracle\ora92\network\admin\sqlnet.ora

# Generated by Oracle configuration tools.

# NAMES.DEFAULT_DOMAIN = Infomation

# SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

启动配置助理Oracle Net Configuration Assistant，如下图所示：

2.2 快速而简单地查询数据库

试验：

启动SQL*Plus，使用密码TIGER，作为SCOTT连接数据库（或以win+R，输入sqlplus）。

SQL> select table_name from user_tables;

TABLE_NAME

------------------------------

BONUS

DEPT

EMP

SALGRADE

工作原理

USER_TABLES是Oracle数据词典中的一个视图。当SQL*Plus中遇到排版乱序（如行不够显示）特殊问题的时候，可以有三种选择：

忍受它。
查询少量的列。
格式化结果（用户可以让标题每10、100、10000行重复一次或者根本不重复）。

2.2.1 格式化用户结果

1. COLUMN

SQL> select ename,sal from emp where ename like 'A%'

2 /

ENAME SAL

---------- ----------

ALLEN 1600

ADAMS 1100

SQL> column sal format $9,999.99

SQL> select ename,sal from emp where ename like 'A%'

2 /

ENAME SAL

---------- ----------

ALLEN $1,600.00

ADAMS $1,100.00

SQL> describe user_objects

名称是否为空? 类型

----------------------------------------- -------- ----------------

OBJECT_NAME VARCHAR2(128)

SUBOBJECT_NAME VARCHAR2(30)

OBJECT_ID NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(18)

CREATED DATE

LAST_DDL_TIME DATE

TIMESTAMP VARCHAR2(19)

STATUS VARCHAR2(7)

TEMPORARY VARCHAR2(1)

GENERATED VARCHAR2(1)

SECONDARY VARCHAR2(1)

2. PAUSE

SQL> set pause on

一页之后，停止滚屏

SQL> set pause off

全部显示（一页的大小默认PAGESIZE是14）

3. PAGESIZE

试验：设置用户页面大小

从数据词典的ALL_OBJECTS视图中选择ROWNUM和OBJECT_NAME。

SQL> set pagesize 10

SQL> select rownum,object_name from all_objects where rownum<20

2 /

ROWNUM OBJECT_NAME

---------- ------------------------------

1 /1005bd30_LnkdConstant

2 /10076b23_OraCustomDatumClosur

3 /10297c91_SAXAttrList

4 /103a2e73_DefaultEditorKitEndP

5 /10501902_BasicFileChooserUINe

6 /105072e7_HttpSessionBindingEv

7 /106ba0a5_ArrayEnumeration

ROWNUM OBJECT_NAME

---------- ------------------------------

8 /106faabc_BasicTreeUIKeyHandle

9 /10744837_ObjectStreamClass2

10 /1079c94d_NumberConstantData

11 /10804ae7_Constants

12 /108343f6_MultiColorChooserUI

13 /10845320_TypeMapImpl

14 /10948dc3_PermissionImpl

ROWNUM OBJECT_NAME

---------- ------------------------------

15 /1095ce9b_MultiComboBoxUI

16 /109a284b_OracleXMLStaticQuery

17 /109cbb8e_SpanShapeRendererSim

18 /10a45bfe_ProfilePrinterErrors

19 /10a793fd_LocaleElements_iw

已选择19行。

19条记录，2页，这里显示屏幕大小占10行。

3. LINESIZE

LINESIZE默认为80。通过此方式，用户不会因为输出数据超过了窗口的右边限制而遗失数据。

4. FEEDBACK

查询结果末尾输出N行。

SQL> show feedback

用于6或更多行的 FEEDBACK ON

SQL> select empno,ename,job from emp where rownum<7;

SQL> set feedback off

SQL> select empno,ename,job from emp where rownum<7;

SQL> set feedback 3

SQL> select empno,ename,job from emp where rownum<3;

5. NUMFOFRMAT

NUMFORMAT的默认值是10。

SQL> set numformat 9999999.99

SQL> select sal from emp;

6. LONG

LONG的默认值是80.如果用户要查询具有LONG列的表或者视图，那么就只会显示这个特定列的前80个字符。如果使LONG列更长，就可以显示这个列中的更多数据。

SQL> select text from all_views where view_name='USER_TABLES';

TEXT

---------------------------------------------------------------------

select o.name, decode(bitand(t.property, 4194400), 0, ts.name, null),

decode(bitand(t.propert

SQL> set pagesize 1000;

SQL> set long 10000;

SQL> select text from all_views where view_name='USER_TABLES';

2.3 什么是SQL

SQL不是Oracle的专有技术；它是关系数据库管理系统的标准语言（最早是由IBM在二十世纪70年代建立，并于随后被ANSI/ISO标准化组织采纳，并被进一步提炼和升华。

SQL-92 ANSI标准的SQL标准中共有四个层次：

(1) 入门层次。大多数供应商所构建的SQL引擎都会符合层次1.

(2) 过滤层次。随着特性要求的增加，过滤层次介于入门层次和中间层次之间。

(3) 中间层次。中间层次的SQL-92兼容层次要求在数据库中提供大量超过了入门或者过滤兼容性的新特性。这些特性包括但不局限于如下内容：

动态SQL
用于参照完整性的级联删除
DATE和TIME数据类型
域
可变长度字符串
CASE表达式
数据类型之间的CAST函数

(4) 完全。这是最高和最终级别的ANSI SQL-92兼容层次。它向中间层次的标准又增加了更多的特性，这些特性包括但不局限于如下内容：