oracle经典sql语句

1、oracle将多行变成一行

select d.datagetsource_id,wmsys.wm_concat(d.catagory_id) from datagetsource2catagory d group by d.datagetsource_id

2、oracle将表数据导入导出

C:\Documents and Settings\Administrator>exp irdp/irdp file=e:/数据库备份/irdp_bak_200911020927_forUpdate.dmp full-y
导出数据库的语句，最后的是full-y，不是full=y，如果用后面也可以导出，但它会把库中的所有用户都导出	来。

3、oracle将树形sql语句最后加上根节点id

CONNECT_BY_ROOT

select c2c.community_id,
c2c.parent_community_id,
level,
CONNECT_BY_ROOT community_id root
from community2community c2c
start with c2c.community_id in
(select community_id
from community cy
where withdrawn = 'N')
connect by prior c2c.community_id = c2c.parent_community_id

4、查询数据库所有包含item_id字段的表的名称

select t.TABLE_NAME from user_tab_columns t where t.COLUMN_NAME like '%ITEM_ID%' group by 	t.TABLE_NAME;

5、查询数据库回收站中被删除的表

select * from recyclebin where original_name = 'WEBPUBLISHAPPLY';

6、闪回数据库某张表

alter table WEBPUBLISHAPPLY enable row movement;
flashback table WEBPUBLISHAPPLY to timestamp to_timestamp('2010-08-03 00:00:00','yyyy-mm-dd 	hh24:mi:ss');
alter table WEBPUBLISHAPPLY disable row movement;

7、oracle-9i将一行记录中部分数据复制到另一行记录中

update addressbook a
set (a.a_sex,a.a_brithday,a.a_brithplace,a.a_address,a.a_phone)=
(select d.a_sex,d.a_brithday,d.a_brithplace,d.a_address,d.a_phone 
from addressbook d 
where d.a_id = '5')
where a.a_id = '3';

8、将表中a字段记录为1的行中的b字段全部改为2
update addressbook d 
set d.a_brithplace = (select r.a_brithplace from addressbook r where r.a_id = '9')
where d.a_address = (select c.a_address from addressbook c where c.a_id = '5')

9、删除表且删除表所占的空间，且不能回退
truncate table books

10、oracle事务

1)savepoint a 保存事务点
2)rollback to a 回退到事务点a，且取消a之后的操作
3)rollback取消全部操作

4)只读事务
set transaction read only;--1(会话1)
update emp set sal = 3000 where ename = 'smith';--2(会话2)
select sal from emp where ename = 'smith';--3(会话1)
当在1处设置了只读事务后，紧接着2处在3之前修改了该数据，但是在3处查看的数据还是1处这个时间点的数据，也就是修改前的数据
提示：在read only事务中，是不允许进行增删改操作的
5)顺序事务
set transaction isolation level serializable;--1(会话1)
update emp set sal = 3000 where ename = 'smith';--2(会话2)
select sal from emp where ename = 'smith';--3(会话1)

在3处的查询语句只会查询到2处修改前的数据


11、分组函数
select i.item_type_id,i.submitter_id,count(1) from item i
where i.item_type_id = 'ebook' or i.item_type_id = 'rwsk'
group by rollup (i.item_type_id,i.submitter_id);

select i.item_type_id,i.submitter_id,count(1) from item i
where i.item_type_id = 'ebook' or i.item_type_id = 'rwsk'
group by cube (i.item_type_id,i.submitter_id);

12、同时利用两个或者多个分组函数
select r.type,r.metaapplication_id,count(1) from resourceshowtype r
group by grouping sets(r.type,r.metaapplication_id);

13、自动执行内连接
select c2i.collection_id,item_id,i.item_type_id from collection2item c2i
natural join item i

14、函数
1)返回字符的ASCII码
select ascii('a') from dual; 
2)将ASCII码转换为字符
select chr(65) from dual;
3)链接两个字符
select concat('ab','cd') from dual;
4)单词首字母大写
select initcap('hello world') from dual;
5)查看字符串a在字符串b中的位置instr(b,a,n,m),n为起始位置，初始为1，m为出现次数，初始为1
select instr('hello world','l','1') from dual;
6)返回字符串的长度
select length('hello world') from dual;
7)
将条目id左边填充0直到字符长度达到10位
select i.item_id,lpad(i.item_id,10,'0') from item i;
select i.item_id,rpad(i.item_id,10,'0') from item i;
8)将字符串中左边连续出现的'浙江大学'的任意组合去掉
select c.name,ltrim(c.name,'浙江大学') from community c;
9)将字符串变为首字母大写或者全小写或者全部大写
select nls_initcap(n'hello world') from dual;
select nls_lower(n'SQL Server') from dual;
select nls_upper(n'SQL Server') from dual;
10)字符串替换
select replace('浙江大学出版社','浙江','湖北') from dual;
11)截取子字符串
select substr('浙江大学出版社',1,4) from dual;

15、时间
1)在系统时间往后推100个月
select add_months(sysdate,100),sysdate from dual;
select add_months(sysdate,-100),sysdate from dual;
select add_months(sysdate,-1000000000000),sysdate from dual;
2)返回当前时区对应日期格式的时间
select current_date from dual;
3)返回当前日期格式的时间
select current_timestamp from dual;
4)从当前时间中抽取年月日，不能抽取时分秒
select extract(year from sysdate) from dual;
select extract(month from sysdate) from dual;
select extract(day from sysdate) from dual;


16、使用标量变量接收查询数据
declare
v_comtype_name communitytype.name%type;
begin
select name into  v_comtype_name
from communitytype
where community_type_id=&comtype_id;
dbms_output.put_line('资源库名称:'||v_comtype_name);
end;

17、使用记录变量接受数据
--oracle自定义类型种类：
--1、子类型
--subtype cc_num is number(16,2);
--2、记录类型
--type emp_record_type is record
--(
--ename varchar2(10),
--job varchar2(9)
--);
--emp_record emp_record_type;
--3、rowtype类型变量
--emp_record emp%rowtype;
declare
type emp_record_type is record(
   id communitytype.community_type_id%type,
   name communitytype.name%type  
);
emp_record emp_record_type;
begin
select ct.community_type_id,ct.name
into emp_record
from communitytype ct
where ct.community_type_id = 'ebook';
dbms_output.put_line('资源库ID：'||emp_record.id);
dbms_output.put_line('资源库名称：'||emp_record.name);
end;

18、操纵数据
declare
v_community_type_name communitytype.english_name%type:='&englishname';
begin
update communitytype t
set t.english_name = v_community_type_name
where t.community_type_id = 'AcademicRes';
commit;
end;

19、数据库游标
1)sql%isopen 游标是否已经打开

2)sql%found sql语句是否已经执行成功

begin
savepoint a1;
update communitytype t
set t.english_name = '333'
where t.community_type_id = 'AcademicRes';
if sql%found
then
commit;
dbms_output.put_line('成功');
else
rollback to a1;
dbms_output.put_line('失败');
end if;
end;

3)sql%notfound 

4)sql%rowcount 返回sql影响的行数