oracle学习笔录

set serveroutput on  打开服务器屏幕显示信息
set sqlblanklines on  编写SQL语句时，打开忽略空格功能

save   c:/sqlplus_test01.txt    --保存
edit    --打开记事本进行编辑
get c:/sqlplus_test01.txt    --把记事本中的信息调入到缓存中
list    --显示缓存中的信息
desc deptment   --显示部门表中的表定义]构
l2 4   --显示第二行到第四行的信息
change  --修改缓存中的内容
c/N/M  --把N改成M
del  2 5  --删除2到5行的信息
help  index   --帮助
? set   --以set开头的关键字帮助信息
col  ID format A10  heading '部门编号'     --把字段名ID显示为部门编号，字符类型，长度为10
col bytes format 999,999,999     --把字段名bytes显示为指定的格式

 

***************************************************************
set linesize 50
ttitle center "我的标题"  skip 1-
left "测试报表" right  "页" -
format 999 sql.pno skip 2
select * from dept;
   
--输出结果
                                 我的标题
测试报表                                                            页  1
 
   deptno           dname             log
-------------        ---------------       -----------
      10               accounting       new york
      20               research           dalias
      30               sales                 chicago
      40               operations        boston

***************************************************************

ttitle off  --关闭标题
select * from dept;

--输出结果
 
   deptno           dname             log
-------------        ---------------       -----------
      10               accounting       new york
      20               research           dalias
      30               sales                 chicago
      40               operations        boston

***************************************************************
select * from deptment;

--输出结果
id             name
-------      -----------
01          A部门
02          A部门
03          B部门
04          B部门
05          C部门

break on name    --把name字段去掉重复值

select * from deptment;

--输出结果
id             name
-------      -----------
01          A部门
02        
03          B部门
04         
05          C部门

comp count label "计数" of id on name   --对分组字段进行汇总
select * from deptment;

--输出结果

id                 name
------------     ---------------
01                A部门
02                A部门
------------     *************
            2     计数
03               B部门
04               B部门 
------------     *************
            2      计数
05               C部门
------------     *************      
            1      计数

 

***************************************************************
DDL    数据定义语言
    1.create 
 create table abc(a varchar2(10),b char(10));
    2.alter
 alter table abc add c number;
  alter table abc drop column c;
    3.drop
DCL   数据控制语言
     1.grant 
 grant select on dept to tt;
 conn tt/tt11
 select * from scott.dept;
     2.revoke
 revoke select  on dept from tt;
 conn  tt/tt11
 select * from scott.dept
DML  数据操作语言
      1.select
 select * from abc;
      2.insert
 insert into abc(a,b) values('abc','xy');
 insert into abc values('bcd','123');
      3.delete
 delete from abc where a = 'abc';
      4.update
 update abc set b='ttt' where a='abc';

***************************************************************
                                                    常用系统函数
1、字符
 lenth,ltrim,replace,rtrim,substr,trim

2、日期
 sysdate,current_date,next_day
3、转换
 to_char,to_date,to_number
4、聚集函数
 sum,avg,max,min,count
5、其他
 user,decode,nvl

select length('abcdef') from dual;

--输出结果

length('abcded')
---------------------
                         6

select length('abc好ef') from dual;

--输出结果

length('abcded')
---------------------
                         6

select lengthb('abc好ef') from dual;

--输出结果

lengthb('abcded')
---------------------
                         7

select ltrim('  abc好ef') from dual;

--输出结果

ltrim('
-----------
abc好ef

select rtrim('   abc好ef   ') from dual;

--输出结果

rtrim('ab
----------
   abc好ef

select trim('   abc好ef   ') from dual;

--输出结果

trim('a
---------
abc好ef

select substr('abcdefg',2,3) from dual;

--输出结果
sub
-----
bcd

select sysdate from dual;

--输出结果

sysdate
---------------
21-6月 -10

select current_date from dual;

--输出结果

current_date
---------------
21-6月 -10

alter session set nls_date_format='dd-mon-yyyy hh:mi:ss';
select current_date from dual;

--输出结果

current_date
-------------------
21-6月 -2010  03：44：22

select next_day(sysdate,'星期三') from dual;

--输出结果

next_day(sysdate,'星期三')
-------------------------------------
23-6月 -2010  03:47:03

select to_char(systdate,'yyyy-mm-dd hh:mi:ss') from dual;

--输出结果

to_char(sysdate,'yy
---------------------------
2010-06-21  03:54:08

select to_date('12-3月-10')  from dual;

--输出结果

to_date('12-3月-10)
---------------------------
12-3月 -0010 12:00:00

select to_number('00333')  from dual;

--输出结果

to_number('00333')
---------------------------
                             333

select user from dual;

--输出结果

user
--------------------------------------------
li

select * from e;

--输出结果

eid             ename          sex
----------      ----------------  ------------
001 赵1      男       
002 钱2      男 
003 孙3      男 
003 李4      女 
003 周5      女

select sum(decode(sex,'男',1,0))  男人数  ,sum(decode(sex,'女',1,0)  女人数  from e;

--输出结果

男人数               女人数
------------           -----------
              3                       2

select  *  from  aa;

--输出结果

a1               a2                a3
-------------   ---------------  -----------------
abc xyz     aa
bca dee     aa
abc ttt                   aa
cba                       aa
bbb                       aa
abc ggd               aa
abc dfd                aa

select a1,nvl(a2,'未输入') from aa;

--输出结果

a1               a2                a3
-------------   ---------------  -----------------
abc xyz     aa
bca dee     aa
abc ttt                   aa
cba 未输入          aa
bbb 未输入          aa
abc ggd               aa
abc dfd                aa

select * from aa where a2 is null;

--输出结果

a1               a2                a3
-------------   ---------------  -----------------
cba                       aa
bbb                       aa

 

 

 

 

***************************************************************

--游标的属性
 %FOUND
 %ISOPEN
 %NOTFOUND
 %ROWCOUNT

--游标与记录集
*****************************************************************************
declare
cursor mycur is
select * from book;
myrecord books%rowtype;  --定义记录集与books表类型大小一样
begin
open mycur;
fetch mycur into myrecord;
while mycur%fount loop
dbms_output.put_line(myrecord.books_id||','||myrecord.books_name);
fetch mycur into myrecord;
end loop;
close mycur;
end;
/
--输出结果：
0001,中国文学
0002,外国文学
0003,英语阅读
0004,建筑艺术
0005,计算机入门
0006,数值算法
0007,C语言
*****************************************************************************

--带参数的游标
*****************************************************************************
declare
cursor cur_para(id varchar2) is
select books_name from books where book_id=id;
t_name  books.books_name%type;
begin
open cur_para('0001');
loop
fetch cur_para into t_name;
exit when cur_para%notfound;
dbms_output.put_line(t_name);
end loop;
close cur_para;
end;
/

--输出结果：
中国文学

*****************************************************************************

--利用游标修改表数据
*****************************************************************************
declare
cursor cur is
select name from deptment for update;
text  varchar2(10);
begin
open cur;
fetch cur into text;
while  cur%found loop
update deptment set name=name||'_t' where current of cur;
fetch cur into text;
end loop;
close cur;
end;
/

--输出结果：
id             name
-------      -----------
01          A部门_t
02          B部门_t
03          C部门_t
04          D部门_t
05          E部门_t

*****************************************************************************

--游标在for循环中不用打开和关闭
*****************************************************************************
begin
for cur in(select name from deptment) loop
dbms_output.put_line(cur.name);
end loop;
end;
/

--输出结果
A部门_t
B部门_t
C部门_t
D部门_t
E部门_t

*****************************************************************************