oracle数据库

文章没有写完的就在附件中查看
基础知识

对tnsnames.ora的解释
TEST = //test为连接的名字 如 sqlplus pccw/pccw@test test可以随便的修改
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.38)(PORT = 1521))
//Host 为 主机的名字或者是ip地址 port 为端口号码 是管理员修改的
)
(CONNECT_DATA =
(SERVICE_NAME = pccw)
//pccw 为连接到的主机服务器的数据库的名称
)
)

Truncate 和 delete 删除后使用的select时 谁的查询速度是比较快速的？
例子：创建表testa
create table testa
(
t1 number ,
t2 varchar2(20)
)
循环1000次 向testa中插入数据
begin
for v in 1..1000
loop
insert into testa values(v,'a');
end loop;
end;

delete from testa;
select * from testa; --0.031秒

循环1000次 向testa中插入数据
begin
for v in 1..1000
loop
insert into testa values(v,'a');
end loop;
end;

truncate table testa;
select * from testa; --0.016秒
总结 由于oracle 中有H W M的说法 所以在delete删除表的数据后 但是表的大小是没有改变的所以查询的速度是比较慢的
而truncate 后 虽然删除了表的数据但是表的大小回到了原来默认的大小 所以 查询的速度是比较快速的
例如 emp 表的大小是120kb，用了delete 删除表中的1000条数据，但是表的大小还是不会改变，然后用select 查询 慢。
用了 truncate 删除表中的1000条数据，但是表的大小恢复到了表原来默认的大小64K，然后用select 查询 快。

基础知识（2）
Oracle 中的 函数的应用
单行函数
字符函数
Instr（x，y）
Length（x）
Lpad（x，i，y） 从左边开始取i个长度 如果x的长度小于I 则用 y中的字符来补充在左边
rpad（x，i，Y） 从右边开始取i个长度 如果x的长度小于I 则用Y中的字符来补充在右边
substr（x，pos，len）截取字符的长度 从pos的位置开始截取 截取长度为len的长度
trim ltrim rtrim 取左右的空格 取左边的空格 取右边的空格
replace（x，y，z）
select replace('adfertru','ad','AD') from dual
把adferu 中的 ad 用大写的AD来替换
日期转换函数
To_char select to_char(‘2010-06-03’,’yyyy-MM-dd’) from dual;
To_date to_date(日期，格式)；
To_number
其他函数
Nvl（x，y） 如果 x 的值为空那么 就用y 的值来 显示x 的值
Decode（x，y1,r1。。。default） 如果x 的值与y1 的值相匹配 那么 就显示 r1 都没有匹配就显示 default的值
分组函数
sum 求和
avg 求平均值
max 求最大的值
min 求最小的值
count 求数量 但是如果为空值的话 那么控制是不计算到count中的
分析函数
Row_number（）
select d from (select empno ,ename,deptno,sal,ROW_NUMBER() over(order by sal desc) d from emp ) group by d order by d;
Rank（）
select empno ,ename, sal , rank() over(order by sal desc) d from emp
Dense_rank（）
select empno,ename, sal,dense_rank() over(order by sal desc) d from emp;
还有一种写的方式
select empno,ename, sal,dense_rank() over( partition by deptno order by sal desc) d from emp;
partition by 是可以根据deptno 进行分组的 然后求出 deptno进行分组的来进行排序

用户汇总的函数
这些函数用在group by 的后面
select deptno,sum(sal) from emp group by cube(deptno,sal);

select deptno ,sum(sal) from emp group by rollup(deptno,sal)

select deptno,sum(sal) from emp group by grouping sets(deptno ,sal)

select sum(sal) from emp group by cube(sal)

select sum(sal) from emp group by rollup(sal)

递归查询的用法
例子 参考 scott 下的 emp表 注意level 为伪列 名为深度
查出 员工的号码为7902 的所有上级
select empno ,ename ,level from emp connect by empno=PRIOR mgr start with empno=7902

查出 员工的号码为7902 的所有下属
select empno,ename ,level from emp connect by PRIOR empno= mgr start with empno=7902

Connect by的用法 可以用connect by 创建出一些想要的连续的数字 如下
select rownum from dual connect by rownum =1
loop
res:=res*j;
j:=j-1;
end loop;
dbms_output.put_line(jl || '的阶乘为 '|| res);
end;
游标的基础
游标
（1）显式游标（2）隐式游标（3）游标变量 可以使用ref
隐式游标
For 游标 in 语句 loop code.. end loop;
--for表示已经打开了游标了 不用打开了
显式游标
Cursor 游标（参数） is 语句 注意：这里的参数不能写精度
Cursor curname （ t varchar2） is select * from emp [where emp。Ename = t]
例子 如下 用scott 中 emp 表作为基础
declare
d emp%rowtype;
cursor curname(t number) is select * from emp where emp.empno=t;
begin
open curname(7839);
fetch curname into d;
while curname%found loop
dbms_output.put_line(d.ename);
fetch curname into d;
end loop;
close curname;
end;
函数可以在 sql语句中调用 也可以在PL/SQL中调用
存储过程不可以在SQL语句中调用
包和包体
自治事务
PRAGMA AUTONOMOUS_TRANSACTION (编译指令)
（重要）如果在函数中做增加删除修改必须写上自治事务
如果游标作为参数 一定为out类型
自治事务例子用于在包体中的
包中 并没有实现
create or replace package p_chenguo
is
type curtype is ref cursor;
procedure proc_autochenguo(pa number ,pb varchar2);--自治事务
procedure proc_nonautochengo(pa number,pb varchar2);--非自治事务
end;
实现包中的方法和存储过程
create or replace package body p_chenguo
is
procedure proc_autochenguo(pa number ,pb varchar2) is --自治事务
PRAGMA AUTONOMOUS_TRANSACTION;
begin
insert into testb values(pa,pb);
commit;
end proc_autochenguo;

procedure proc_nonautochengo(pa number,pb varchar2) is--非自治事务
begin
insert into testb values (pa,pb);
commit;
end proc_nonautochengo;
end ;

自治事务的练习测试
基于下表的自治事务和非自治事务的测试
Create table testb(t1 number,t2 varchar2(20));
--对自治事务的测试
declare
pa number;
pb varchar(20);
begin
pa:=11;
pb:='test11';
insert into testb values(pa,pb);-- （1）
p_chenguo.proc_autochenguo(22,'test22');--（2）当在这个存储过程中insert into 到这个 testb 表中的时候 然后再rollback的时候 对自治事务是不起作用的
--就是说（1） 的语句是没有出入进去的 （2） 的语句是插入进去了的
rollback;
end;
--非自治事务的测试
declare
pa number;
pb varchar(20);
begin
pa:=33;
pb:='test33';
insert into testb values(pa,pb);--（1）
p_chenguo.proc_nonautochengo(44,'test44');--（2）当在这个存储过程中insert into 到这个 testb 表中的时候 然后再rollback的时候 对非自治事务的有影响的
--也就是说（1）的语句出入进去了 但是（2） 的语句也插入进去了
rollback;
end;

触发器中是不能写commit 和 rollback的

其他的函数（内置函数）
产生随即数的函数DBMS_RANDOM
MOD(ABS(DBMS_RANDOM.RANDOM),1000) 得到1到1000的随即数
DBMS_RANDOM.RANDOM 产生了一个 正数 和负数 用 ABS得到数的绝对值
然后用mod 取余数 得到1000已内的随即数
ROUND(DBMS_RANDOM.VALUE(1,1000),0)得到1到1000的随机数
DBMS_RANDOM.VALUE(1,1000) 得到1到1000的带小数的数字
Round 保留小数位数为0 取到了1到1000的整数

Oracle 的高级知识
集合
（1）索引表集合
Declare
Type mytest is table of number index by binary_interger；
（2）嵌套
Declare
Type mytest is table of number；--不定义长度但是要初始化
M mytest:=mytest(12,33,33,33); --长度为4 但是不能超过这个长度
Begin
For i in m.first..m.last loop
Dbms_output.put_line(m(i));
End loop;
End；
（3）变长数组
Declare
Type mytest is varray(6) of number; --这里指定了长度但是不能超过这个长度
（4）对象作为集合
Create or replace type 类型名 is table of 对象
批联编
Forall 的使用和for大致差不多 但是 forall 中是没有loop的 而且里面只有一句话
而且这里面的这句程序只能是insert or update or delete 的语句
Bulk collect 的使用 主要是个集合赋值的
如
Declare
Type mytest is table of emp%rowtype；
A mytest；
Begin
Select * bulk collect into a from emp;--把emp 的值放进了 集合a中
End；

创建对象
Create or replace type objectTest1 as OBJECT（
属性，
Member function 名称[(参数)] return 类型；
）not final 是可以继承的

继承对象
Create or replace type 类型名称 under objectTest1(
属性，
Member function 名称[(参数)] return 类型；

) final –final 是不可以被继承的
创建对象表
Create table test of objectTest1;
那么test 表就拥有了objectTest1的属性作为了表的字段

临时表
Create global temporary table 表名 （字段）[(1)] 或者[(2)]
(1)[on commit delete rows(默认的)]
(2)[on commit preserve rows]
这两个的共同点就是 结构是永久的 里面的数据时临时的，数据对一个session（会话）是有效的 但是如果是第一种（1）的方式 提交了就删除了只能查询一次
第二种方式（2）提交了数据还可以查询 但是到另外的窗口及不能查询了
查询练习

连接scott用户

（1）查询到自己的编号和姓名 但是要把自己的上司查询出来
第一种写法 ：select e1.empno,e1.ename , e1.mgr ,(select e2.ename from emp e2 where e2.empno = e1.mgr) as mgrname from emp e1
第一种方式采用了子查询的方式进行查询

第二种写法：select e1.empno,e1.ename, e1.mgr, e2.ename from emp e1 left join emp e2 on e1.mgr=e2.empno
第二种方式称为表连接方式进行查询 利用了left join .. on ..


（2）练习查询 员工工资小于1000时 显示 ‘低’ 在1000~3000时 显示 ‘中’工资大于3000时显示 ‘高’
利用了 case when 条件 then 结果 else （case 。。。） end 的语句来写这个sql 语句
select e1.empno,e1.ename,e1.job,e1.hiredate, e1.sal ,case when e1.sal insert into testa values(1,'222');
1 row inserted
SQL> insert into testa values(2,'222');
1 row inserted
SQL> insert into testa values(3,'333');
1 row inserted
SQL> insert into testa values(4,'444');
1 row inserted
SQL> insert into testa values(1,'111');
1 row inserted
SQL> insert into testa values(2,'222');
1 row inserted
SQL> insert into testa values(3,'333');
1 row inserted
SQL> insert into testa values(4,'444');
看看所有的数据
先查询到 数据重复的数据
select rowid ,a.t1,a.t2 from testa a where rowid != (select max(rowid) from testa b where a.t1 = b.t1 and a.t2 = b.t2);

删除重复数据
Delete from testa a where rowid != (select max(rowid) from testa b where a.t1 = b.t1 and a.t2 = b.t2);

（4） 查询每人的数学语文的成绩

select s.stu ,s.sc 语文,(select s1.sc from score s1 where s1.course='数学' and s1.stu = s.stu) 数学 from score s where s.course='语文'


（5）删除所有的重复的数据
delete from tmp t where rowid != (select max(rowid) from tmp t1 where t.t = t1.t and t.d = t1.d)

创建一个tmp表 create table tmp （t number ， d varchar2（20））；
插入数据
insert into tmp values(10,'aaaa');
insert into tmp values(20,'aaaa');
insert into tmp values(30,'aaaa');
insert into tmp values(10,'aaaa');
insert into tmp values(20,'aaaa');
insert into tmp values(30,'aaaa');
insert into tmp values(40,'aaaa');
查询到重复的数据
select rowid ,t ,d from tmp t where rowid != (select max(rowid) from tmp t1 where t.t = t1.t and t.d = t1.d)
删除重复的数据
delete from tmp t where rowid != (select max(rowid) from tmp t1 where t.t = t1.t and t.d = t1.d)

（6）查询出连续数字的第一个数字

select x.n1 from n x minus (select (select y.n1 from n y where y.n1 = z.n1+1 ) r from n z )
结果

（7） 查询出这样的结果
（1）按照v1分组 ，n1的数据有值，但是满足同时有空值和值
（2）按照v1分组，N2的数据有值，但是满足同时有空值和值

select v1 from t_test
group by v1
having
(count(v1) > count(n1) and count(n1) > =1 and count(n2)>=1)
or
(count(v1) > count(n2) and count(n1) > =1 and count(n2)>=1);
结果

（8）输入一个工资 求出这个的工资应该缴纳的税率
---输入你的工资求出你要缴纳的税-----------
declare
sal number(10,2):=&请输入你的工资;
JB number(10,2):=2000;--基本工资；
d number(10,2);--除去基本工资剩余的钱；
s number(10,2);--得到你的税率
begin
d:=sal-JB;
if d= length(to_char(t_max)) and t_max0) then
c_cou:=lel+1;
proc_chen_guo_getsub(c_id(i),c_cou);
end if;
end loop;

end proc_chen_guo_getsub;
end;
测试
declare
res_t varchar2(2000);
begin
res_t:=p_chen_guo_all.fun_chen_guo_digui(7839);
end;