Oracle笔记1

sqlplus “sys/test1234 as sysbda”
desc $controlfile
select status,name form v$controlfile;
desc v$datafile
select file#,status from v$datafile;
desc v$logfile
select member from v$logfile;
SGA ：
DB buffer， 大共享区， 共享池， Redo buffer， 固定SGA
DB高速缓存池（DB buffer）： 默认缓存池， 保持缓池， 再生缓存池
共享池： 库缓存区（共享SQL区 PL／SQL区）， 字典缓存区
块(block 8K 操作系统的整数倍) 盘区(extent) 段(segment) 表空间(tablespace) 数据文件(datafile)
我们只能指定表在那个表空间中
sqlplus/nolog
connect sys/test1234 as sysdba
startup 实例 控制文件 数据文件
startup mount 启动数据文件 但是不启动控制文件
archive log list
startup mount alter database open 非归档方式改为归档方式
startup nomount 控制文件失的时间，重新创建控制文件
shutdown immediate
shutdown
shutdown transactional
shutdown abort 强行关闭数据库
http://192.168.1.93:5560/isqlplus
alter user TESTUSER account unlock;
create user “test” identified by “test”;
grant connect to “test”;
sqlplus scott/tiger
help index
select * from dept
? set
set sqlblanklines on 支持空格行
替代变量
select * from dept where deptno=10
select * from dept where deptno=&tt
查看命令
list l
l 1 2
c /n/m
l
/
? change
del 4
l
del 2 3
l
a from dept
save d:oracletest.txt
l
@c:oracletest.txt
get c:orcletext.txt
edit
/
? col
col deptno heading “编号”
desc dept
col dname format a10 heading “部门名称”
col deptno format 999,999,999
connect sys/test1234 as sysdba
set linesize 50
ttitle center “我的标题” skip 1-
left “测试报表” right “页” -
format 999 sql.pno skip 2
ttitle off
break
? conp
break on pub
select * from books
conp count label “计数” of books_name on pub
spool d:1.txt
spool off
edit d:1.txt
视图学习
视图称为虚表
视图的作用 安全性 方便 一致性
create or replace view myview
as
select * from dept

create or replace view myview
as
select * from books where price>30
with check option
edit c:1.txt
@ c:1.txt
create or replace view myview
as
select * from books where price>30
with read only
dba_views
desc all_views
desc user_views
select text from user_views where view_name=’u_views’
oracle的同义词
select user from dual
select * from scott.dept
同义词
create synonym dept for scott.dept
select * from dept
drop synonym dept
create public synonym dept for scott.dept
select * from dept
connect tt/tt11
select * from dept
desc dba_synonyms
desc user_synonyms
序列
create sequence myseq
start with 1
increment by 1
order
nocycle;
select myseq.nextval from dual;
select myseq.currual form dual;
create table auto((a number,b varchar2(10)))
create sequence myseq
insert into auto values(myseq.nextval,”dd”)
desc dba_sequences
select sequence_name,sequence_owner from dba_sequences where sequence_owner=’TT’
select user from dual
alter sequence myseq increment by 3
select myseq.nextval from dual
sql语言基础
connect scott/tiger
DDL
create table abc(a varchar2(20),b char(20))
alter table abc add c number
alter table abc drop column c
DCL
grant select on dept to tt
revoke select on dept from tt
DML
insert into abc values(’aa’,'cc’)
delete
update
常用系统函数
字符
length ltrim,replace,rtrim,substr,trim
日期
Sysdate,current_date,next_day
转换
To_char,to_date,to_number
聚集函数
sum,avg,max,min,count
其它
user,decode,nvl
select length(’ddd’) from dual
select lengthb(’dd好d’) from dual
select trim(’ ddd ‘) from dual
select rtrim(’ ddd ‘) from dual
select ltrim(’ ddd ‘) from dual
select SUBSTR(’abacedf’,1,3) from dual
select SUBSTR(’abacedf’,length(’abacedf’)-3+1,3) from dual
SELECT current_date FROM dual
ALTER SESSION SET NLS_DATE_FORMAT=’dd-mon-yyyy hh:mi:ss’
SELECT NEXT_DAY(sysdate,’星期五’) from dual
SELECT TO_CHAR(sysdate,’yyyy-mm-dd hh:mi:ss’) from dual
SELECT TO_DATE(’12-3月-04′) from dual
SELECT TO_NUMBER(’222′) from dual
select user from dual
select sum(decode(sex,’男’,1,0)) 男人数,sum(decode(sex,’女’,1,0)) 女人数 from e;
select a1,nvl(a2,’地输入’) a2 from aa;
select * from aa where a2 is null
select * from aa where a2 is not null
分组查询
聚集函数不能在where中，如果要用则用having
select a,count(a) from aa group by a having count(a)>1
模糊查询
select * from aa where a2 like ‘a_’
select * from aa where a2 like ‘a%’
select * from aa where a2 like ‘_a’
select * from aa where a2 like ‘__a’
select * from aa where a2 like ‘%a’
select * from aa where a2 like ‘%a%’
表的连接
from a,b where a.=b.
a join b on a.=b.
from a,b where a.id=b.id(+)左连接 左边为全部显示出来，有匹配值，则写上，无则以空值填充
右连接则相反

子查询
无关子查询
select * from e where id in (select id from d);
相关子查询
select * from e where id in (select id from d where id=e.id and id=’03′);
select * from e where id not in (select id from d where id=e.id and id=’03′);
select * from e where exists (select id from d where id=e.id and id=’03′);
select * from e where not exists (select id from d where id=e.id and id=’03′);
select * from a union select * from d
select * from a intersect select * from d 返回两者教匹配的记录

insert into e(id,name) select id,name from d ;
create table ttt as (select * from e)

PL/SQL基础
declare
…
begin
…
exception
…
end

declare
x varchar2(20);
begin
x:=’this is..’;
dbms_output.put_line(’x的值为:’||x);
end;
/

set serveroutput on size 10000
l
/

save D:1.txt
@ D:1.txt

/**/块注释

declare
x varchar2(20):=’456kkk’;
–y integer:=123;
y string(10):=’123′;
begin
–x:=’this is..’;
–dbms_output.put_line(’x的值为:’||x);
dbms_output.put(’x的值为:’||x||’y的值是：’||y);
dbms_output.new_line;
end;
/

declare
a number;
b varchar2(10);
begin
a:=2;
if a=1 then
b:=’a';
elsif a=2 then
b:=’b';
else
b:=’c';
end if;
dbms_output.put_line(’B值是：’||b);
end;
/

declare
a number;
b varchar2(10);
begin
a:=10;
case
when a=1 then b:=’a';
when a=2 then b:=’b';
when a=3 then b:=’c';
when a=4 then b:=’d';
else
b:=’others’;
end case;
dbms_output.put_line(’B值是：’||b);
end;
/

declare
cursor mycur IS
select * from dept;
myrecord dept%rowtype;
begin
open mycur;
fetch mycur into myrecord;
while mycur%found loop
dbms_output.put_line(myrecord.deptno||’,'||myrecord.dname);
fetch mycur into myrecord;
end loop;
close mycur;
end;
/

declare
cursor mycur_para(id varchar2) IS
select dname from dept where deptno=id;
t_name dept.dname%type;
begin
open mycur_para(’10′);
loop
fetch mycur_para into t_name;
exit when mycur_para%notfound;
dbms_output.put_line(t_name);
end loop;
close mycur_para;
end;
/

declare
cursor mycur_para(id varchar2) IS
select dname from dept where deptno=id;
begin
dbms_output.put_line(’*******结果集为********’);
for mycur in mycur_para(’10′) loop
dbms_output.put_line(mycur.dname);
end loop;
end;
/

declare
t_name dept.dname%type;
cursor cur(id varchar2) IS
select dname from dept where deptno=id;
begin
if cur%isopen then
dbms_output.put_line(’游标己被打开’);
else
open cur(’10′);
end if;
fetch cur into t_name;
close cur;
dbms_output.put_line(t_name);
end;
/

declare
t_name varchar2(20);
cursor mycur IS
select dname from dept;
begin
open mycur;
loop
fetch mycur into t_name;
exit when mycur%notfound or mycur%notfound is null;
dbms_output.put_line(’游标mycur的rowcount是:’||mycur%rowcount);
end loop;
close mycur;
end;
/

declare
cursor IS
select dname from dept for update;
text varchar2(20);
begin
open cur;
fetch cur into text;
while cur%found loop
update dept set dname=name||’_t’ where current of cur;
fetch cur into text;
end loop;
close cur;
end ;
/

begin
for cur in(select dname from dept) loop
dbms_output.put_line(cur.dname);
end loop;
end;
/
建议不要使用游标
因为效率不是很高

存储过程
create or replace procedure myproc(id in number)
IS
name varchar2(10);
begin
select dname into name from dept where deptno=id;
dbms_output.put_line(name);
end myproc;
/

show errors procedure myproc;

declare
tid number(10);
begin
tid:=10;
myproc(tid);
end;
/

begin
myproc(10);
end;
/

execute myproc(10);

create or replace procedure myproc2(id varchar2,name out varchar2)
is
begin
select dname into name from dept where deptno=id;
end;
/

declare
tid varchar2(10);
dname varchar2(10);
begin
tid:=’10′;
myproc2(tid,tname);
end;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21507788/viewspace-1021716/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21507788/viewspace-1021716/