第二周oracle学习

W3c测验疑惑！

Order by 和 group by

SELECT * FROM qj_test6 ORDERBY autoid

ORDER BY 语句用于根据指定的列对结果集进行排序。ORDER BY 语句默认按照升序对记录进行排序。如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。

关于ORA-00979 不是 GROUP BY 表达式错误的解释

使用聚合函数中用group by来分组数据时特别说明了select 列表项中不存在的列可以出现在group by的列表项中，但反过来就不行了，在select列表项中出现的列必须全部出现在group by后面(聚合函数除外)SQL Select语句完整的执行顺序：

SELECT autoid,birth FROM qj_test6 GROUPBY birth,autoid

SELECT COUNT(*) FROM Persons  ？？？？

SQL 函数

一，SELECT * FROM qj_test6SELECT count(*) FROM qj_test6

二，SELECTAVG(autoid) AS i_id FROM qj_test6

三，SELECT语句执行顺序

1、from子句组装来自不同数据源的数据；2、where子句基于指定的条件对记录行进行筛选；

3、group by子句将数据划分为多个分组；4、使用聚集函数进行计算；

5、使用having子句筛选分组；6、计算所有的表达式；7、使用order by对结果集进行排序

select 各模块都能使用 from 后定义的表的别名。但只有order by能使用select 后定义的列的别名

四，FIRST_VALUE 功能描述：返回组中数据窗口的第一个值。有些类似于SQL Server中的first()，但用法完全不一样，而功能也强大一些。 =======oracle 中很多函数与SQL中的函数不一致，出现关键字无效这样的错误，可以首先查询是不是内置函数不一致

五，（分析函数）SELECTdistinct FIRST_VALUE(ID) over(ORDERBY birth) FROM qj_test6     ====没有distinct的话返回值会有和记录数目一样多！

六，LAST_VALUE的用法和first 类似！

七,SELECTMAX(birth) FROM qj_test6, SELECTMIN(birth) FROM qj_test6   ==返回这列的最大值

八，SELECTSUM(birth) AS TOTAL FROM qj_test6返回这一列的sum

九， TOP是T-SQL的关键字，PL/SQL里没有。select * from table WHERE ROWNUM <=10 order by columnname   ROWNUM是ORACLE自动生成的伪列  ===取排序的十列

十，CREATETABLE qj_test7 ASSELECT * FROM qj_test WHEREROWNUM<50ORDERBY ass_staff_number

,

十，索引的好处：

第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

 唯一性是因为主键的唯一性？索引和参考完整性的关系？？

 

十一，createTABLESPACE TBS_AGENT_DATA datafile'D:\tempdb\TBS_AGENT_DATA.DBF'size128M

 

createtable TEST_USER_QJ(NAMEVarchar2(50),TEL NUMBER(15),QQ_NUM NUMBER(15),MAIL_ADD varchar2(5))   tablespace TBS_AGENT_DATA

 

ALTERTABLE test_user_qj

ADD WORKPALCE VARCHAR2(50)

 

十二，回滚表空间！！！

 

 

 

 

十三，mergeinto test_user_qj_test a

using temp20120716 b

on (a.name=b.name)

whenmatchedthen

updateset a.workplace=b.workplace;

 

在进行SQL语句编写时，我们经常会遇到大量的同时进行Insert/Update的语句，也就是说当存在记录时，就更新(Update)，不存在数据时，就插入(Insert)。

merge into 语法不仅没有if exists语法啰嗦，而且比if exists 还要高效很多。我经常用来在oracle之间同步数据库表。

 

十四，查询一张表的列数目！

selectcount(column_name) from user_tab_columns where table_name=upper('qj_test');

网上查找大量资料，都是错误的解法，目前只了解到此方法查询出列数目！

 

快速粘贴复制excel的方法，SELECT * FROM qj_test8 FORUPDATE

前提是表的字段和excel的字段要匹配上

打开锁,再按一下加号.鼠标点到第一列的列头，使全列成选中状态，然后粘贴，最后commit提交即可

 

十五，　字符串函数

INSTR：INSTR方法的格式为

INSTR(源字符串, 目标字符串, 起始位置, 取第N个匹配项的位置) Select INSTR('ORC+001','+',1,1) From dual返回的是＂4＂　如果该字符串没有匹配字符　返回的是“0”。

SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1) "Instring" FROM DUALITPUB个人空间7{*m;Yyk t/|'im2v
从右边第一个字符开始找，包括第一个字符，找到第一个‘ＯＲ’，计算从左到右

共有多少个字符，包括找到的ＯＲ。本例＝１４,即‘CORPORATE FLOOR'ITPUB个人空间O8||5F­t3L#B
i0I.J,V2t-E m:Br0SELECT INSTR('CORPORATE FLOOR FLOOR FLOOR','OR', 3, 4) "Instring" FROM DUAL

返回结果是：２６

SUBSTR(源字符串,起始位置,要取多少位) 例 Select SUBSTR('ORC+001',1,3) From dual;

返回的是“ORC”

SELECT SUBSTR(mail_add,1,INSTR(mail_add,'@',1,1)-1)

AS mail_add

FROM test_user_qj_test

WHERE workpalce='成都'

ORDERBYname

 

 

十六，导入excel 用merge into

1、先在oracle中建一张表，如temp，
2、使用pl/sql developer工具的sql窗口，编辑这张表，将excel数据直接复制，粘贴进这张表并保存
也可以使用excel保存为*.csv格式，再用sql*loader把数据装入temp表
3、用temp表数据更新已有表(假设表名叫test)
mergeinto test_user_qj_test a

using temp20120716 b

on (a.name=b.name)

whenmatchedthen

updateset a.workplace=b.workplace;

 

十七，文件配置地址

C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN

tnsnames.ora

 

十八，初涉存储过程

过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据

 

 

CREATETABLE logtable_test_qj (userid VARCHAR2(10), logdate date);

 

CREATEORREPLACEPROCEDURE logexecution_test_qj

IS

BEGIN

INSERTINTO logtable (userid, logdate) VALUES (USER, SYSDATE);

END;

 

:= 是赋值符号,相当于其他语言中的=
& 是从需要外部输入输入的变量

 

测试过程：

begin

  -- Call the procedure

  zhanshi(v_pid => :v_pid);

end;

修改为：

begin

  zhanshi(:v_pid => 0);

end;

 

 

 

十九，新写一个存储过程

CREATEORREPLACEPROCEDURE qj_test1

(qj_autoid IN qj_test5.qj_autoid%TYPE) 修改为(qj_autoid INNUMBER)

AS

No_result EXCEPTION;

BEGIN

   DELETEFROM qj_test5 WHERE autoid = qj_autoid;

   IFSQL%NOTFOUNDTHEN

      RAISE no_result;

   ENDIF;

   DBMS_OUTPUT.PUT_LINE('编码为'||qj_autoid||'的员工已被删除!');

EXCEPTION

   WHEN no_result THEN

      DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');

   WHENOTHERSTHEN

      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);

END qj_test1;

 

修改之前需要加冒号（见上测试过程）

 

Q&A

FND_MESSAGE.show   fnd_message.debug  

网上说是可以弹出窗口提示信息~  但是在实际运用中 没有用~  求解！

 

二十，

SQL%NOTFOUND是一个布尔值。与最近的sql语句（update,insert,delete,select）发生交互，当最近的一条sql语句没有涉及任何行的时候，则返回true。否则返回false。这样的语句在实际应用中，是非常有用的。例如要update一行数据时，如果没有找到，就可以作相应操作。

 

Oracle内置函数SQLCODE和SQLERRM是特别用在OTHERS处理器中，分别用来返回Oracle的错误代码和错误消息。 OTHERS处理器应该是异常处理块中的最后的异常处理器，因为它是用来捕获除了别的异常处理器处理以外的所有的Oracle异常，所以在程序的最外层使用一个OTHERS处理器的话，将可以确保所有的错误都会被检测到

 

当数据库或PL/SQL在运行时发生错误时，一个异常被PL/SQL运行时引擎自动抛出。异常也可以通过RAISE语句抛出
RAISE exception_name;

 

二十二，IF SQL%NOTFOUND THEN RAISE no_result; END IF; DBMS_OUTPUT.PUT_LINE('编码为'||qj_autoid||'的员工已被删除!');

如果操作没有涉及到一些行的话  DBMS这句话是会执行的  难道是IF如果条件不成立，那么后面跟着的这句话会要执行吗？   Q&A

 

二十四，写的第二个存储过程

CREATE OR REPLACE PROCEDURE qj_test9()

AS   BEGIN  INSERT INTO qj_test5 VALUES(22,'lby','00','00','99')

Dbms_Output.put_line('插入成功！！')； EXCEPTION  IF SQL%NOTFOUND THEN

dbms_output.put_line('无效操作！！！！')； END IF;  END qj_test9;

 

修正错误：1，如果没有参数，过程名不能加括号qj_test9()

完整错误更正：

CREATEORREPLACEPROCEDURE qj_test9

AS qj_result EXCEPTION; BEGIN

INSERTINTO qj_test5 VALUES(22,'lby','00','00','99');

IFSQL%NOTFOUNDTHENRAISE qj_result;

ELSE DBMS_OUTPUT.PUT_LINE('插入成功！！');ENDIF; EXCEPTION

WHEN qj_result THEN  dbms_output.put_line('无效操作！！！！');

WHENOTHERSTHEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);

END qj_test9;

 

二十五，SELECT * FROM DBA_JOBS_RUNNING

SELECT * FROM dict   查出的DBA开头的那群字段有什么作用？

 

 

二十六，

过程与函数的相同功能有：

1、 都使用IN模式的参数传入数据、OUT模式的参数返回数据。

2、 输入参数都可以接受默认值，都可以传值或传引导。

3、 调用时的实际参数都可以使用位置表示法、名称表示法或组合方法。

4、 都有声明部分、执行部分和异常处理部分。

5、 其管理过程都有创建、编译、授权、删除、显示依赖关系等。

使用过程与函数的原则：

1、如果需要返回多个值和不返回值，就使用过程；如果只需要返回一个值，就使用函数。

2、过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。

3、可以SQL语句内部（如表达式）调用函数来完成复杂的计算问题，但不能调用过程。所以这是函数的特色。

 

 

二十七，第三个存储过程

CREATETABLE qj_test12

(

tempyear NUMBER(20) NOTNULLPRIMARYKEY,

tempseq NUMBER(20),

tempid NUMBER(20)

)

 

 

 

CREATEORREPLACEPROCEDURE qj_test11

AS

v_count NUMBER(20):=1;

qj_result EXCEPTION;

BEGIN

LOOP

v_count:=v_count+1;

EXITWHEN v_count>100000;

INSERTINTO qj_test12 VALUES(temyear.nextval,temseq.nextval,temid.nextval);

ENDLOOP;

EXCEPTION

WHENOTHERSTHEN

ROLLBACK;

END qj_test11;

 

begin

qj_test11;

end;

 

插入99999行数据！

SELECT * FROM qj_test12 WHERE tempid=171163;    单表查询的速度总是会很快

如果查询的是多表联合的话  可能话费时间会多一点

试验中！！！！

SELECT *

FROM qj_test14 a,qj_test15 b

WHERE a.qj_id=b.qj_id AND a.qj_name='12345'

 

在表14和表15中修改了第50000行和第50001行的数据

结果在 0.0062 0.0078s 之间

SELECT *

FROM qj_test14 a

INNERJOIN qj_test15 b

ON a.qj_id=b.qj_id AND a.qj_name='12345'

 

用了内连接之后的结果是一样的

结果几乎没有改变！~

 明天来加索引不知道结果会怎么样~~~

重新生成500W的数据  验证索引

 

 

二十八，oracle函数(生成随机数值或者字符串)

1、小数( 0 ~ 1) select dbms_random.value from dual

2、指定范围内的小数 ( 0 ~ 100 ) select dbms_random.value(0,100) from dual

3、指定范围内的整数 ( 0 ~ 100 )select trunc(dbms_random.value(0,100)) from dual

4、长度为20的随机数字串select substr(cast(dbms_random.value as varchar2(38)),3,20) from dual

5、正态分布的随机数select dbms_random.normal from dual

6、随机字符串select dbms_random.string(opt, length) from dual

 

 

SELECT round(dbms_random.value*100) FROM dual

Round函数 四舍五入

 

二十九,

CREATEINDEX qj_testindex ON qj_test15(qj_id)

创建索引

select * from user_indexes where table_NAME ='QJ_TEST15';

可以查看表添加的索引  ！！！

 

select * from user_ind_columns where index_name='QJ_TESTINDEX'

建好的索引，查看它建立在哪个表的哪个列上

 

 

数字（number）

添加索引之前查找一行需要11.778秒

加索引之后  查找一行只需要 0.0016秒

字符串(Varchar2)

字符串的速率也开始一样的！  大幅度提升了

 

 

三十，

select * from v$session

WHERE machine LIKE'%QIJIN%'

 

三十一，

“||”用来连接两个字符串隐式地把数值型转换成字符型类型

等于号和赋值符号“：=”的差异

 

 

三十二，

SELECT * FROM qj_test15 a,qj_test16 b

WHERE a.qj_id=b.qj_id AND a.qj_id=11111

15 16表都在 qj_id上建立了索引

 

 

SELECT * FROM qj_test17 a,qj_test18 b

WHERE a.qj_id=b.qj_id AND a.qj_id=11111

 

17 18表没有建立索引

17表是15表的复制  18表是16表的复制数据完全一样

执行查询同样的语句

加索引的15 16 表  只用了 0.032s  而没加索引的 17 18 表却用了24.149s

 

 

三十三，

编写触发器时，需要注意以下几点：

触发器不接受参数。

一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只

能有一个。并各触发器之间不能有矛盾。

在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。

触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL

语句进行调用。

在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），

不能使用DDL语句（CREATE、ALTER、DROP）。

触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。

在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。

在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能向表中的任何long和blob列。

不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别。

 

三十四，

当一个sql语句提交后，oracle会首先检查一下共享缓冲池（shared pool）里有没有与之完全相同的语句，如果有的话只须执行软分析即可，否则就得进行硬分析。

SQL共享的三个条件

当前被执行的语句和共享池中的语句必须完全相同（包括大小写、空格、换行等）

两个语句所指的对象必须完全相同（同义词与表是不同的对象）

两个SQL语句中必须使用相同的名字的绑定变量(bind variables)

 

 

三十五，

名词解释

共享池

执行计划

 

 

 

 

 

在SQL语句的准备过程中，花费最多的步骤是生成执行计划，特别是处理有多个表连接的查询。  ？？？？？？？？？？？？！

 

 

基于oracle的应用系统很多性能问题，是由应用系统sql性能低劣引起的，所以，sql的性能优化很重要，分析与优化sql的性能我们一般通过查看该sql的执行计划，本文就如何看懂执行计划，以及如何通过分析执行计划对sql进行优化做相应说明。

 

三十六，

 

动态性能表

select *

from v$db_object_cache

wherenameLIKE'SELECT * FROM qj_test5%'

 

v$db_object_cache中的常用列：OWNER：对象拥有者
NAME：对象名称
TYPE：对象类型(如，sequence,procedure,function,package,package body,trigger)
KEPT：告知是否对象常驻shared pool(yes/no)，有赖于这个对象是否已经利用PL/SQL 过程DBMS_SHARED_POOL.KEEP“保持”（永久固定在内存中）
SHARABLE_MEM：共享内存占用
PINS：当前执行对象的session数
LOCKS：当前锁定对象的session数

 

三十七，

分区表和sql分段

 

三十八，带通配符（%）的like语句

select * from employee where last_name like '%cliton%';

优化

select * from employee where last_name like 'c%';

 

三十九，用Where子句替换HAVING子句，避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

 

四十，

在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况

下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 

使用NOT EXISTS 子句可以有效地利用索引。尽可能使用NOT EXISTS

来代替NOT IN，尽管二者都使用了NOT（不能使用索引而降低速度），

NOT EXISTS要比NOT IN查询效率更高。

 

四十一，

避免在索引列上使用计算

低效： SELECT … FROM  DEPT  WHERE SAL * 12 > 25000;

高效:  SELECT … FROM DEPT WHERE SAL > 25000/12;

 

四十二，用>= 替代 >

高效: WHERE DEPTNO >=4  低效:WHERE DEPTNO >3

 

四十三，通过使用>=、<=等，避免使用NOT命令

 

四十四，尽量多使用COMMIT

回滚段上用于恢复数据的信息.

被程序语句获得的锁

redo log buffer 中的空间

ORACLE为管理上述3种资源中的内部花费

 

四十五，分区表

create table niegc_part(

part_id integer primary key,

part_date date,

part_dec varchar2(100)

)

partition by range(part_date)

(

partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd')) tablespace dw1,

partition part_02 values less than(to_date('2007-01-01','yyyy-mm-dd')) tablespace dw2,

partition part_03 values less than(maxvalue) tablespace dw1

);

insert into niegc_part values(1,to_date('2005-12-30','yyyy-mm-dd'),'less 20

06-01-01');

insert into niegc_part values(2,to_date('2006-01-01','yyyy-mm-dd'),'equal 2

007-01-01');

insert into niegc_part values(3,sysdate,'sysdate');

select * from niegc_part partition(part_01);

 

 

 

假设有一个customer表，表中有数据200000行，我们将此表通过customer_id进行分区，每个分区存储100000行，
我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘。下面是创建表和分区的代码，如下：

create table customer
(
customer_id number not null primary key,
first_name varchar2(30) not null,
last_name varchar2(30) not null,
phone varchar2(15) not null,
email varchar2(80),
status char(1)
)
partition by range (customer_id)
(
partition cus_part1 values less than (100000) tablespace cus_ts01,
partition cus_part2 values less than (200000) tablespace cus_ts02
)