oracle与sql_server的区别.doc

第 1 页 不足之处 敬请指正 xzhui QQ 77056803 疯狂 SQL 之魔兽争霸 本人平时比较沉默 但朋友们都说我很疯狂 疯狂地学习 疯狂地工作 很久就有写点东 西的打算啦 一直懒于打字 近来稍有空闲 决定杜撰此文 献给现些喜欢或不喜欢沉默 的朋友 自序 网上已经有很多 SQL 与 ORACLE 的对比 但本人要讲的即不是单纯的 SQL 也不是单纯的 ORACLE 更不是评价谁好谁坏 意思不大 而是两种数据库之相同和异同 本人曾讲授过 SQL 与 ORACLE 的课程 讲 SQL 时说 SQL 好 讲 ORACLE 时又说 ORACLE 棒 现在终于可以平 心而评啦 估计有人现在会嘿嘿冷笑 又一个误人子弟的骗子 老实说 当初每次讲完课 就有这种 感觉 教的人不得其法 学的人不得其道 说点严肃的事吧 据说比尔与艾里森在洗手间 相遇 两个又是拥抱 又是 KISS 不多久就吵了起来 比尔对查询分析器 SQL QUERY ANALYZE 赞不经绝口 艾里森嘿嘿冷笑 只说了一句话 SQL PLUS 内秀 言归正传 且听我一一道来 1 1 SQLSQL 与与 ORACLEORACLE 的内存分配的内存分配 ORACLE 的内存分配大部分是由 INIT ORA 来决定的 一个数据库实例可以有 N 种分配方案 不同的应用 OLTP OLAP 它的配置是有侧重的 SQL 概括起来说 只有两种内存分配方 式 动态内存分配与静态内存分配 动态内存分配充许 SQL 自己调整需要的内存 静态内 存分配限制了 SQL 对内存的使用 1 1 002002 SQLSQL 与与 ORACLEORACLE 的物理结构的物理结构 总得讲 它们的物理结构很相似 SQL 的数据库相当于 ORACLE 的模式 方案 SQL 的 文件组相当于 ORACLE 的表空间 作用都是均衡 DISK I O SQL 创建表时 可以指定表在不 同的文件组 ORACLE 则可以指定不同的表空间 CREATE TABLE A001 ID DECIMAL 8 0 ON 文件组 CREATE TABLE A001 ID NUMBER 8 0 TABLESPACE 表空间 注 以后所有示例 先 SQL 后 ORACLE 2 2 003003 SQLSQL 与与 ORACLEORACLE 的日志模式的日志模式 SQL 对日志的控制有三种恢复模型 SIMPLE FULL BULK LOGGED ORACLE 对日志的控制有 二种模式 NOARCHIVELOG ARCHIVELOG SQL 的 SIMPLE 相当于 ORACLE 的 NOARCHIVELOG FULL 相当于 ARCHIVELOG BULK LOGGED 相当于 ORACLE 大批量数据装载时 的 NOLOGGING 经常有网友抱怨 SQL 的日志庞大无比且没法处理 最简单的办法就是先切 第 2 页 不足之处 敬请指正 xzhui QQ 77056803 换到 SIMPLE 模式 收缩数据库后再切换到 FULL 记住切换到 FULL 之后要马上做完全备份 3 3 004004 SQLSQL 与与 ORACLEORACLE 的备份类型的备份类型 SQL 的备份类型分的极杂 完全备份 增量备份 日志备份 文件或文件组备份 ORACLE 的备份类型就清淅多啦 物理备份 逻辑备份 ORACLE 的逻辑备份 EXP 相当于 SQL 的 完全备份与增量备份 ORACLE 的物理备份相当于 SQL 的文件与文件组备份 SQL 的各种备 份都密切相关 以完全备份为基础 配合其它的备份方式 就可以灵活地备分数据 ORACLE 的物理备份与逻辑备份各司其职 SQL 可以有多个日志 相当于 ORACLE 日志组 ORACLE 的日志自动切换并归档 SQL 的日志不停地膨胀 SQL 有附加数据库 可以将数 据库很方便地移到别一个服务器 ORACLE 有可传输表空间 可操作性就得注意啦 4 4 005005 SQLSQL 与与 ORACLEORACLE 的恢复类型的恢复类型 SQL 有完全恢复与基于时间点的不完全恢复 ORACLE 有完全恢复与不完全恢复 不完全恢 复有三种方式 基于取消的 基于时间的 基于修改的 SCN 的恢复 不完全恢复可以恢 复数据到某个稳定的状态点 5 5 006006 SQLSQL 与与 ORACLEORACLE 的事务隔离的事务隔离 SET TRANSACTION ISOLATION LEVEL SQL 有四种事务隔离级别 READ COMMITTED READ UNCOMMITTED REPEATABLE READ SERIALIZABLE ORACLE 有两种事务隔离级别 READ COMMITTED SERIALIZABLE SQL 虽然有四种事务隔离 事务之间还是经常发生阻塞 ORACLE 则利用回退段很好地实现 了事务隔离 不会产生阻塞 SQL 与 ORACLE 如果发生死锁 都可以很快地识别并将之处理 掉 6 6 007007 SQLSQL 与与 ORACLEORACLE 的外键约束的外键约束 SQL 的外键约束可以实现级联删除与级联更新 ORACLE 则只充许级联删除 CREATE TABLE A001 ID INT PRIMARY KEY NAME VARCHAR 20 CREATE TABLE A002 ID INT REFERENCES A001 ID ON DELETE CASCADE ON UPDATE CASCADE AGE TINYINT CREATE TABLE A001 ID INT PRIMAY KEY NAME VARCHAR2 20 CREATE TABLE A002 ID INT REFERENCES A001 ID ON DELETE CASCADE AGE 第 3 页 不足之处 敬请指正 xzhui QQ 77056803 NUMBER 2 0 7 7 008008 SQLSQL 与与 ORACLEORACLE 的临时表的临时表 SQL 的临时表用 或 开头 使用完后自动释放 ORACLE 的临时表则存在数据库中 每个会 话的数据都互不干涉 oracle 临时表中的纪录可以被定义为自动删除 分 commit 方式和 transaction 方式 而表结构不会被自动删除 临时表的 DML DDL 操作和标准表一样 CREATE TABLE TEMP ID INT NAME VARCHAR 20 CREATE GLOBAL TEMPORARY TABLE TEMP ID INT VARCHAR2 20 8 8 009009 SQLSQL 与与 ORACLEORACLE 的类型转换的类型转换 SQL 常用类型转换函数有 CAST CONVERT STR ORACLE 常用类型转换函数有 TO CHAR TO NUMBER TO DATE SELECT CONVERT VARCHAR 20 GETDATE 112 SELECT TO CHAR SYSDATE YYYYMMDD FROM DUAL 9 9 010010 SQLSQL 与与 ORACLEORACLE 的自动编号的自动编号 SQL 的编号一般由 IDENTITY 字段来提供 可以灵活地设定种子值 增量 取值范围有 BIGINT INT SMALLINT TINYINT DEIMAL 等 ORACLE 的编号一般由 SEQUENCE 来提供 由 NEXTVAL 与 CURVAL 函数从 SEQUENCES 取值 CREATE TABLE A003 ID INT IDENTITY 9999 9 NAME VARCHAR 20 CREATE SEQUENCE SEQ 001 START 9999 INCREMENT BY 9 CREATE TABLE A004 ID INT INSERT INTO A004 VALUES SEQ 001 NEXTVAL INSERT INTO A004 VALUES SEQ 001 CURVAL 1 10 10 011011 SQLSQL 与与 ORACLEORACLE 的分区表的分区表 从严格意思上来讲 SQL 还没有分区表 它的分区表是以 UNION 为基础 将多个结果集串 起来 实际上是视图 ORACLE 的分区表有多种 PARTITION BY RANGE PARTITION BY HASH PARTITION BY LIST 其它就是混合分区 以上三种基础分区的混合使用 当然 第 4 页 不足之处 敬请指正 xzhui QQ 77056803 ORACLE 也可以象 SQL 那样分区视图 CREATE TABLE A1999 ID INT NAME VARCHAR 20 CREATE TABLE A2000 ID INT NAME VARCHAR 20 CREATE VIEW V PART AS SELECT FROM A1999 UNION SELECT FROM A2000 CREATE TABLE A PART1 ID INT NAME VARCHAR2 20 PARTITON BY RANGE ID PARTITION P1 VALUES LESS THEN 2000000 PATITION P2 VALUES LESS THEN MAXVALUE CREATE TABLE A PART2 ID INT NAME VARCHAR2 20 PARTITION BY HASH ID PARTITIONS 2 STORE IN USERS01 USERS02 CREATE TABLE A PART3 ID INT NAME VARCHAR2 20 PARTITION BY LIST ID PARTIION P1 VALUES 01 03 05 PARTITON P2 VALUES 02 04 11 11 012012 SQLSQL 与与 ORACLEORACLE 的存储过程的存储过程 SQL 的存储过程可以很方便地返回结果集 ORACLE 的存储过程只有通过游标类型返回结果 集 这种结果集 ADO 不可识别 如果想使用 ORACLE 存储过程的结果集 只有使用 ODAC 开 发包 DELPHI BCB 控件组 与 有下载 SQL 的过程参 数如果是字符必须指定参数长度 ORACLE 的过程则不充许指定字符参数的长度 CREATE PROCEDURE UP 001 ID INT AS BEGIN SELECT ID SUM QTY FROM A TEST WHERE ID ID GROUP BY ID END CREATE OR REPLACE PACKAGE UP 002 AS TYPE MYCURSOR IS REF CURSOR FUNCTION GETRECORD RETURN MYCURSOR END CEEATE OR REPLACE PACKAGE BODY UP 002 AS FUNCTION GETRECORD RETURN MYCURSOR AS MC MYCURSOR SL VARCHAR2 999 BEGIN OPEN MC FOR SELECT FROM A001 RETURN MC END END ORACLE 的存储函数返回结果这么艰难 但 SQL 的触发器竟然也充许返回结果集就令人费解 啦 触发器的调试比较麻烦 在 SQL 实际开发中 一般都将触发器要执行的代码放到过程 中进行调试 在查询分析器中可以对过程进行设断点调试 第 5 页 不足之处 敬请指正 xzhui QQ 77056803 12 12 013013 SQLSQL 与与 ORACLEORACLE 的触发器的触发器 触发器的事务是引起触发动作事务的延续 在 SQL 的触发器中是可以无 BEGIN TRAN 而可以 直接 COMMIT TRAN 的 SQL 的触发器是表级触发器 DML 影响一行或无数行触发动作只触发 一次 ORACLE 分表级触发器与行级触发器 触发的粒度更细腻一些 SQL 在处理多行时就 必须使用 CURSOR 啦 ORACLE 使用 INSERTING DELTING UPDATING 判断执行了什么 DML 操 作 SQL 只有判断 INSERTED DELETED 的记录数来判断执行了什么操作 只有 INSERTED 映 象表记录数大于 0 表示 INSERT 只有 DELETED 映象表记录数大于 0 表示 DELETE 若两个表 记录数都大于 0 表示 UPDATE 用 SQL 的触发器实现级联添加 级联删除 级联更新 CREATE TABLE A1 ID INT NAME VARCHAR 20 CREATE TABLE A2 ID INT NAME VARCHAR 20 CREATE TRIGGER TRI A1 INS ON A1 FOR INSERT DELETE UPDATE AS BEGIN DECLARE I INT D INT ID INT SELECT I COUNT FROM INSERTED SELECT D COUNT FROM DELETED IF I 0 AND D 0 执行更新 由于用到游标 故略去 IF I 0 INSERT INTO A2 SELECT FROM INSERTED IF D 0 DELETE FROM A2 WHERE ID ID END 用 ORACLE 的触发器实现级联添加 级联删除 级联更新 CREATE OR REPLACE TRI A1 INS AFTER INSERT OR DELETE OR UPDATE ON A1 FOR EACH ROW BEGIN IF INSERTING THEN INSERT INTO A2 SELECT FROM NEW END IF IF DELETING THEN DELETE FROM A2 WHERE ID OLD ID END IF IF UPDATING THEN UPATE A2 SET ID NEW ID NAME NEW NAME WHERE ID OLD ID END IF END 第 6 页 不足之处 敬请指正 xzhui QQ 77056803 13 13 014014 SQLSQL 与与 ORACLEORACLE 的游标的游标 SQL 的游标用 FETCH STATUS 判断是否还有数据 ORACLE 的游标用 FOUND NOTFOUND 来 判断游标是否结束 游标分服务端游标与客户端游标 在存储过程 函数 触发器中声明 的游标为服务端游标 其它处声明的游标为客户端游标 游标需要使用较多的内存 但它 比临时表的性能要优一些 因为临时表占用的是 DISK I O DISK I O 应该比服务器的内存 要更珍贵一些吧 14 14 015015 SQLSQL 与与 ORACLEORACLE 的重复记录删除的重复记录删除 好的数据库设计重复记录是不存在的 如果有重复记录如何删除呢 SQL 可以使用 SET ROWCOUNT N 设置客户端缓冲区的记录来删除 ORACLE 可以使用 ROWID 来进行 都必须进行 一些简单的编程 SQL 可以做用过程 更通用一些 ORACLE 如果想做得通过不太容易 字 段小些会更方便处理一些 DECLARE M INT SELECT M COUNT FROM A TEST WHERE ID X SELECT M M 1 SET ROWCOUNT M 限制客户端缓冲区的记录数 DELETE FROM A TEST WHERE ID X SET ROWCOUNT 0 取消限制 说明 删除某条记录的重复值 如果想清除表的所有重值要使用游标 取得所有的 X DELETE FROM A TEST A WHERE ROWID SELECT MAX ROWID FROM A TEST B WHERE A ID B ID AND A NAME B NAME 说明 当数据量较大时 这种方法将会使用系统大量的资源 15 15 016016 SQLSQL 与与 ORACLEORACLE 的对象加密的对象加密 SQL 与 ORACLE 的某些对象如过程 视图 函数 触发器可能涉及商业 开发商通常希望对 这些对象进行加密 SQL 的加密方法在创建时带上 WITH ENCRYPTION ORACLE 的对象加密 明显复杂一些 要使用 WRAP 工具 在 ORACLE 的 BIN 目录内 16 16 017017 SQLSQL 与与 ORACLEORACLE 的表生成的表生成 SQLSQL 语句语句 SQL 与 ORACLE 的表如何才导成 SQL 语句呢 如果一定要编程实现 SQL 需要将其它数据类 型的字段转换成 VARCHAR 类型 ORACLE 则可以隐式进行数据类型转换 CREATE TABLE A SQL ID INT NAME VARCHAR 20 假如有两万记录 SELECT INSERT INTO A SQL VALUES CAST ID 第 7 页 不足之处 敬请指正 xzhui QQ 77056803 AS VARCHAR 20 NAME FROM A SQL SELECT INSERT INTO A SQL VALUES ID NAME SQL FROM A SQL 说明 SQL 的字符串连接用 号 ORACLE 字符串连接用 单引号可以做转义符 17 17 018018 SQLSQL 与与 ORACLEORACLE 的动态的动态 SQLSQL SQL 与 ORACLE 都支持动态 SQL 语句 SQL 用 EXEC 执行的动态 SQL 语句 ORACLE 用 EXECUTE IMMEDIATE 执行动态 SQL 动态 SQL 的效率要比非动态 SQL 性能差 但使用起来非 常灵活 可以根据不同条件执行不同的任务 DECLARE SQL VARCHAR 99 SELECT SQL declare m int select m count from sysobjects select m EXEC SQL DECLARE S VARCHAR2 99 BEGIN S SELECT COUNT FROM USER TABLES EXECUTE IMMEDIATE S END 18 18 1919 返回记录集中前 返回记录集中前 N N 条记录的语法 条记录的语法 SQL 只有使用 TOP ORACLE 可以使用 ROWNUM SELECT TOP N FROM 记录集 表 视图 子查询 SELECT FROM 记录集 WHERE ROWNUM N 19 19 2020 如何返回记录集中相临两记录之间某字段的差值 如何返回记录集中相临两记录之间某字段的差值 create table a001 id int qty int insert into a001 values 1 20 insert into a001 values 4 10 select identity int 1 1 code qty into X from a001 select b qty a qty from X a x b where a code b code 1 drop table X CREATE TABLE A002 ID INT INSERT INTO A002 VALUES 1 第 8 页 不足之处 敬请指正 xzhui QQ 77056803 INSERT INTO A002 VALUES 9 with a as select rownum rn id from a002 select a2 id a1 id from a a2 a a1 where a2 rn a1 rn 1 说明 虽然语法大不相同 但最大的特点是两者都使用了自连接技术 20 20 2121 如何返回任意某个范围之间的记录集 如何返回任意某个范围之间的记录集 create table a03 id int declare i int select i 1 while i 1000 begin insert into a03 values i select i i 1 end 前部分是创建环境 后一部分是实现方法 比较牵强 select identity int 1 1 code id into X from a03 select id from x where code between 10 and 20 drop table x begin for i in 1 999 loop insert into a03 values i end loop end select from a03 where rownum 20 minus select from a03 where rownum 10 说明 在数据提取方面 ORACLE 有 ROWID ROWNUM 使之有相当强的优势 SQL 只有使用函数 IDENTITY 来构建一个临时表 这样来说还不好使用 CURSOR 来性能会好一些 通过这个例 子 大家还可以看出 SQL 与 ORACLE 的程序结构 ORACLE 更严谨 人性化一些 21 21 2222 表 表 A04A04 中的含有中的含有 A A B B C C D D 四个字段 当按四个字段 当按 A A 字段分组后 如果字段分组后 如果 D D 等等 1 1 则只统 则只统 计计 B B 的值 如果的值 如果 D D 等等 0 0 则只统计则只统计 C C 的值 的值 create table a04 a varchar 20 b int c int d int insert into a04 values 01 20 7 0 insert into a04 values 01 10 8 1 insert into a04 values 02 20 7 1 第 9 页 不足之处 敬请指正 xzhui QQ 77056803 insert into a04 values 02 10 8 0 select a sum case d when 1 then B when 0 then c end from a04 group by a SELECT A SUM decode d 1 B 0 C FROM A04 GROUP BY A 说明 ORACLE 9i 可以使用 CASE 语句来实现这种操作 但也可以用 DECODE 来作统计 使用 CASE 比 DECODE 提供了更为强大的功能 但 DECODE 的语法显然比 CASE WHEN THEN END 要 简洁得多 22 22 2323 如何删除数据库所有表 如何删除数据库所有表 ORACLEORACLE 则是删除模式所有表 则是删除模式所有表 declare sql varchar 99 tbl varchar 30 fk varchar 30 declare cur fk cursor local for select object name constid object name fkeyid from sysreferences 删除所有外键 open cur fk fetch cur fk into fk tbl while fetch status 0 begin select sql alter table tbl drop constraint fk exec SQL select sql drop table tbl fetch cur fk into fk tbl end close cur fk declare cur fks cursor local for select name from sysobjects where xtype U open cur fks fetch cur fks into tbl while fetch status 0 begin select sql drop table tbl exec SQL fetch cur fks into tbl end close cur fks DECLARE S varchar2 99 cursor cur f is select constraint name table name from user constraints where constraint type R cursor cur t is select table name from user tables begin for v in cur f loop 第 10 页 不足之处 敬请指正 xzhui QQ 77056803 S alter table v table name drop constraint v constraint name execute immediate S end loop for t in cur t loop S drop table t table name Execute immediate S end loop end 说明 SQL 删除数据库时 用到了两个系统表 SYSREFERENCES SYSOBJECTS 前一个可以 获得所有外键键信息 后者可以获得所有表的信息 在删除表时还在表名上加了一对中括 号 即使用表名含有空格键或其它特殊这符也可以顺利删除 在 ORACLE 中 要删除模式的所有表 方法和 SQL 差不多 需要用到的数据字典也有两个 USER CONSTRAINTS USER TABLES USER CONSTRAINTS 中 CONSTRAINT TYPE 值等于 R 表示 是外键 同样也要用到 CURSOR 与动态 SQL 这里提醒一下大家 FOR LOOP 内的变量变 量是 FOR LOOP 声明的 可以 ORACLE 的程序结构比 SQL 简洁 23 23 2424 如何统计数据库所有用户表的记录数 如何统计数据库所有用户表的记录数 ORACLEORACLE 统计模式内所有表的记录数 统计模式内所有表的记录数 create table tmp qty int create table tmp1 tbl varchar 30 qty int declare sql varchar 99 tbl varchar 30 qty int declare cur fks cursor local for select name from sysobjects where xtype U open cur fks fetch cur fks into tbl while fetch status 0 begin select sql select count from tbl insert into tmp exec SQL select qty qty from tmp insert into tmp1 values tbl qty delete from tmp fetch cur fks into tbl end close cur fks select from tmp1 DESC DBMS UTILITY execute dbms utility analyze schema SCOTT COMPUTE select table name num rows from user tables 说明 SQL 的 EXEC 功能可谓十分强大 竟然可以和 INSERT INTO 合用 将结果集存入一个 表中 MS 可真牛 ORACLE 就只好用个偷懒的方法 首先将要统计的模式进行统计分析 在 数据字典中就记载了每个表的行数 ORACLE 很简单吧 第 11 页 不足之处 敬请指正 xzhui QQ 77056803 24 24 2525 SQLSQL 与与 ORACLEORACLE 快速建表的方法 快速建表的方法 SELECT INTO 新表名称 FROM 子查询 表名 CREATE TABLE 新表名称 AS 子查询 说明 快速建表可以有效地消除碎片 速度极快 25 25 2626 如何实现有一组有规则的编号 如 如何实现有一组有规则的编号 如 200305310001 200305310999200305310001 200305310999 declare i int c varchar 20 select i 1 while i 1000 begin select c case when ix qty select code qty rank over order by qty ord from a test 说明 SQL 中的排序是通过 UPDATE 更新 然后再显示出来 而 ORACLE 使用了 RANK OVER 函 数 直接将数据集显示出来 而且 RANK OVER 函数还可以通过 PARTITION BY 对分组中的数 据进行排序 31 31 3232 SQLSQL 与与 ORACLEORACLE 的文件结构的文件结构 SQL 文件被格式化为 8K 为单位的页 每 8 个相邻的页称为盘区 64K 若该盘区分配给一 个对象 称为一致盘区 若分配给多个对象等为混合盘区 SQL 有全局分配页面 数据页 面 索引页页 BLOB 页面 TEXT 页面 ORACLE 的文件最小逻辑单位是由 INIT ORA 中的 BLOCK SIZE 的值决定的 可以取 2K 4K 6K 8K 16K 32K 等 ORACLE 的盘区是由一些 块组成的 ORACLE 的段是由盘区组成的 ORACLE 有数据段 索引段 回退段 UNDO 段 临时段 CLOB BLOB 段 CLUSTER 段等 第 14 页 不足之处 敬请指正 xzhui QQ 77056803 32 32 3333 SQLSQL 与与 ORACLEORACLE 如何取得一个全局唯一标识标 如何取得一个全局唯一标识标 GUIDGUID SELECT NEWID SELECT SYS GUID FROM DUAL 33 33 3434 本人有一张表单 本人有一张表单 要求统计要求统计 col1 col6col1 col6 中不等于中不等于 2 2 的列的个数 数据如下 的列的个数 数据如下 row id col1 col2 col3 col4 col5 col6 1 2 1 1 2 3 2 2 1 1 2 2 2 2 3 2 3 2 2 1 2 4 2 2 2 2 1 2 5 1 2 2 2 2 2 6 2 2 2 2 2 1 要求结果如下 row id count 1 3 2 2 3 2 4 1 5 1 6 1 select row id 6 case when col1 2 then col1 2 else 0 end case when col2 2 then col2 2 else 0 end case when col3 2 then col3 2 else 0 end case when col4 2 then col4 2 else 0 end case when col5 2 then col5 2 else 0 end case when col6 2 then col6 2 else 0 end as count from table A 说明 本例摘自 WWW DELPHIBBS COM 有名的 DELPHI 开发网站 本人不拥有版权 该 SQL 的实现方法与 ORACLE 的实现写法完全一样 不在多述 34 34 3535 有一客户表 数据如下 有一客户表 数据如下 客户 日期 资金 F001 2003 03 05 123 00 F002 2003 03 04 1223 00 第 15 页 不足之处 敬请指正 xzhui QQ 77056803 F002 2003 03 02 1123 00 F003 2003 03 05 1231 00 F003 2003 03 04 1232 00 要求选出每个客户最新的哪条记录 组成一个结果集 结果如下 F001 2003 03 05 123 00 F002 2003 03 04 1223 00 F003 2003 03 05 1231 00 实现方法 select a 客户 b 日期 a 资金 from 客户资金表 a select 客户 max 日期 日期 from 客户资金表 group by 客户 b where a 客户 b 客户 and a 日期 b 日期 说明 ORACLE 的写法与 SQL 一样 本例也摘自 WWW DELPHIBBS COM 本人不拥有版权 35 35 3636 现在看一个高难度的作业 后来解决办法和本例不同 请看需求 现在看一个高难度的作业 后来解决办法和本例不同 请看需求 视图 1 CITYWATER VIEW 行政区划名称 城市用水量 亿 M3 北京市 15000 上海市 9000 天津市 5400 重庆市 9500 表 1 DICTIONARY 字段别名字段全名 区划行政区划名称 代码行政区划代码 城市用水城市用水量 亿 M3 表 1 2 是数据库 public 中的基表 表 3 是数据库 water 中的基表 在数据库 water 中创建 视图 1 用 T SQL 语句怎样实现 把查询结果的 字段别名 修改为视图中的 字段全名 如果采用 T SQL 中的常用修改列标题方式 SELECT column name AS expression 或者 SELECT expression column name 很烦 每个基表里的字段太多 并且基表有近 200 个 字段近 3000 个 说明 其实现在要作的就是将表 3 中的 代码 城市用水 替代成表 1 中的行政区划代 码 城市用户量 亿 M3 等 create view v god as select A 100000 b 310000 b 114011 b 114111 b 114421 b 114311 b 114321 from code a fa01p b where a 200000 b 200000 表 2 CODE 区划代码 北京市 100000 上海市 200000 天津市 300000 表 3 CITYWATER 代码城市用水 10000015000 2000009000 3000005400 第 16 页 不足之处 敬请指正 xzhui QQ 77056803 declare cur col cursor local for select name from syscolumns where id object id v god declare col varchar 20 sql varchar 999 col total varchar 8000 alias varchar 99 source varchar 8000 open cur col fetch cur col into col while fetch status 0 begin select alias 字段名 from dictionary where 段码 col if col total is null select col total alias else select col total col total alias fetch cur col into col end close cur col select source rtrim text from syscomments where id object id v god select source rtrim substring source charindex as source len source select source alter view v god col total source exec source 说明 由于该实例需要的表有两个已没有记录 所以大家只有看看 T SQL 的语法及动态 SQL 的编写 ORACLE 也类似 36 36 3737 如何用 如何用 SQLSQL 操作一段操作一段 XMLXML 文件 文件 create procedure up xml test doc varchar 7999 as begin declare idoc int exec sp xml preparedocument idoc OUTPUT doc SELECT FROM OPENXML idoc root dataset books 2 with title varchar 32 title author varchar 20 author price decimal 9 2 price exec sp xml removedocument idoc end create function uf xml test doc varchar 7999 returns t table title varchar 32 author varchar 20 price decimal 9 2 第 17 页 不足之处 敬请指正 xzhui QQ 77056803 as begin declare idoc int exec sp xml preparedocument idoc OUTPUT doc insert into t SELECT FROM OPENXML idoc root dataset books 2 with title varchar 32 title author varchar 20 author price decimal 9 2 price exec sp xml removedocument idoc return end declare doc varchar 7999 select doc Delphi abc 38 00 MIDAS def 26 00 exec up xml test doc select from dbo uf xml test doc 说明 用过程可以方便地对 XML 进行操作 但编写成 FUNCTION 时就报错 大概 MS 的函数内 部不充许执行 OPENXML 等这类行集函数 另一个重要的问题是 SQL 的这种语法竟然不支 持汉字字串 真是要命 37 37 3838 使用 使用 DBMS REPAIRDBMS REPAIR 检测与修复破损的检测与修复破损的 BLOCKBLOCK ADMIN TABLES 提供管理函数修复或孤立关键表 包含创建 净化与删除函数 CHECK OBJECT 检测并报告表或索引的破损块 DUMP ORPHAN KEYS 导出破损块的数据 第 18 页 不足之处 敬请指正 xzhui QQ 77056803 FIX CORRUPT BLOCKS 在 CHECK OBJECT 检测出的破损块上做标记 REBUILD FREELISTS 重建对象的 FREELISTS SKIP CORRUPT BLOCKS 设置在表或索引扫描时是否不扫描被做了破损标记的块 SEGMENT FIX STATUS 整理 BITMAP 实体上的破损标志 上表列举了 DBMS REPAIR 包所有的过程 下边将对这些过程要引入的参数的枚举值进 行 说明 这引些参数将在过程应用中起决定作用 object typeTABLE OBJECT INDEX OBJECT CLUSTER OBJECT actionCREATE ACTION DROP ACTION PURGE ACTION table typeREPAIR TABLE ORPHAN TABLE flagsSKIP FLAG NOSKIP FLAG SQL exec dbms repair admin tables scott emp dbms repair repair table dbms repair create action USERS ORA 24129 表名 scott emp 没有以前缀 REPAIR 开始 SQL exec dbms repair admin tables REPAIR EMP dbms repair repair table dbms repair create action USERS SQL select object name from REPAIR EMP SQL exec dbms repair admin tables ORPHAN EMP dbms repair ORPHAN table dbms repair create action USERS SQL select table name from orphan emp ADMIN TABLES 过程可以创建 DBMS REPAIR 包的使用中需要的一些辅助表 SQL declare m integer begin dbms repair check object schema name SCOTT object name EMP repair table name REPAIR EMP corrupt count m dbms output put line m end 说明 统计 SCOTT 模式的 EMP 表有多少破损块 其它的过程本人就不再一一举例说明啦 引 用方法类似与上边的实例 其它一些过程的参数列表可以通用 SQL DESC DBMS REPAIR 来查 看 38 38 3939 关于 关于 UTL FILEUTL FILE 包的使用方法包的使用方法 使用 UTL FILE 时有个地方要注意 INIT ORA 文件中的 UTL FILE DIR 参数必须指定路径 即 UTL FILE 包只有在 UTL FILE DIR 所指的目录中有权限读写 以下的实例表示本人已经 修改 UTL FILE DIR C 啦 SQL DESC UTL FILE 第 19 页 不足之处 敬请指正 xzhui QQ 77056803 可以查看 UTL FILE 包的所有类型与过程 例将表中数据输出到文件 create or replace procedure up filew is id number name varchar2 20 handle utl file file type cursor region cur is select from a job begin handle utl file fopen c job out w open region cur fetch region cur into id name while region cur found loop utl file putf handle s s n id name fetch region cur into id name end loop close region cur utl file fflush hand