oracle常用命令集合(随记)

SQL常用关键字(#:表名 ...:字段)
sqlplus sys/bj2008 as sysdba <-
alter user scott account unlock <-

----运算符
||
!=
<>
%
escape
is not null
is null
and
or
between
in

----数型
number
char
varchar2
varchar
date
clog
blog
bfile

----函数
lower()
upper()
substr(,,,)
chr()
ascii()
round()
to_char()
to_date()
to_number()
nvl()
avg()
max()
min()
sum()
count()

dual
describe /desc

select
distinct
from
where
group by
having
order by
desc
asc

cross
join on
right outer join
left outer join
full join
rownum

insert into
values
update set
delete from
truncate
rollback
transaction
savepoint

create
table ()
view v$_ as ()
index idx_ on #(...)
sequence seq_ start with 1 increment by 100
seq_.nextval
seq_.currval
constaint
primary key
references
foreign
unique
check
default
not null
alter table # add
drop
modify

----流程控制
if..then..end if
if..the..elsif..end if
loop..exit when..end loop
while..loop .. end loop
for..in 1..100 loop .. end loop;
for..in reverse 1..100 loop .. end loop;

----事务控制
set auto on/off
rollback
savepoint _name
rollback to _name

----存储过程
create/replace procedure 过程名 is
    声明语句段;
begin
    执行语句段;
exception
    异常处理语句段;
end;
commit;

exec ..;
begin
p;
end;
/

参数:
in 读入，主程序向过程传入值
out 传出，过程向主程序传出值
inout 双向，既有读入也有传出

----pl/sql
declare
begin
exception
when others then
end
:=
number()
binary_integer
date
boolean
char()
varchar2()
long
表名.字段名%type
表名%rowtype
table
type 类型名 is table of 表名.字段名%type index by binary_integer;
type 类型名 is record (,,);
sql%rowcount ||'条记录'
execute immediate '创一个新表
cursor .. is ..
open
fetch .. into ..
close ..
%notfound
%isopen
%found
%rowcount
for update
update .. set .. where current of

 

 

 

---- sql*plus常用命令集
grant
revoke
clear screen
append text / a text
change old/new /c /old/new
c text
clear buffer /cl buff
del n
input /i
input text /i text
list /l
list n /l n /n
list * /l *
list last /l last
list m n /l m n

save *.txt
edit *.txt /ed
get *.txt
start
@ *.txt
set linesize n / set lin n
set pagesize n/set pages n
columu ... format 显示的格式

help
show