浏览 3784 次
锁定老帖子 主题:oracle数据库
精华帖 (1) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (0)
|
|
---|---|
作者 | 正文 |
发表时间:2010-06-12
最后修改:2010-07-27
基础知识 对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; 声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
推荐链接
|
|
返回顶楼 | |
发表时间:2010-06-21
受教了。
附件已下载,谢谢了。 |
|
返回顶楼 | |