1、使用
1、1启动
打开SQL*Plus,键入用户名:system,密码:system,主机字符串:MyOracle
也可以打开命令提示符
sqlplus system/system@MyOracle
SQL*plus命令不同于SQL命令,不能访问数据库,可以缩写,命令不能被存放到SQL缓冲区。
1、2连接命令
conn:连上数据库,如conn system/system@MyOracle
disc:断开数据库
passw:修改密码,如passw system,如果要修改其他用户的密码,当前身份为DBA(SYS,SYSTEM)才行
exit:退出
1、3编辑命令
l:显示缓冲区保存的SQL语句,l1表示显示第一行,显示结果中带星号的为当前行,并将最后一行设为当前行
a:在缓冲区的当前行尾部添加内容
c:修改缓冲区的内容,如c/and/or,表示将缓冲区的and换成or(注意,只能换一个,如要换其他,需要继续执行)
del:删除缓冲区当前行的内容,也可以指定起始终止行号,如del 3 5
i:在缓冲区当前行后添加一行新的内容,如要在首行前增加内容,为“0 文本”
n(数值):定位当前行
ed:启动记事本编辑缓冲区内容
run(/):运行缓冲区内容,并且显示缓冲区内容
1、4文件操纵命令
save:保存缓冲区内容到SQL脚本文件,如save c:\a.sql create,如要替换用replace,如要追加,用append
get:转载脚本文件到缓冲区,如get c:\a.sql
start(@):运行脚本文件
@"C:\Documents and Settings\Administrator\桌面\22860精通JBuilder2005\resource\chapter13\createData.sql";
@@:基本同上,但是能在脚本中嵌套调用其他脚本文件
ed:也能编辑脚本文件
spool:建立假脱机文件,存放屏幕输出的内容,如spool c:\b.lst,关闭可以用spool off
1、5格式命令
col:控制列的显示格式
ttitle:添加标题
btitle:添加页脚注
break:不显示重复行,并将结果分为几个部分显示
1、6交互式命令
&:引用替代变量时需要添加,如select * from stu where number=&d;,此时如没有事先定义会提示输入值,否则直接使用
注意:如替代变量为数值型,可以直接使用,如为字符或者日期型,则需加单引号,如number=&a and name='&b'
&&:基本同上,但是此时定义的可以在当前环境中有效,而上述只能在当前语句中有效
define:用于定义字符型的替代变量,只在当前环境中使用,使用时如加单引号表明区分大小写和空格
如define title=MyTitle;
使用为where job='&title';
直接在define后跟变量名用于查看变量是否定义
accept:用于定义字符型,数值型和日期型的替代变量,比define更加灵活
accept title prompt 'Please input:';
隐藏用户的输入:
accept title hide;
undefine:清除替代变量的定义
prompt:输出提示信息
pause:暂停脚本执行
variable:定义绑定变量,赋值时使用execute,打印时使用print,同时在使用时变量前要加冒号
如:
var no number;
exec :no=7788;
使用为
where empno=:no
desc:describe,能用于多种地方,显示它的详细信息
1、7其他环境变量
可以控制运行环境,如果希望每次都做自动改变,可以修改glogin.sql脚本文件
2、数据类型
1)字符型
CHAR(n):n为1到2000字节
VARCHAR2(n):变长变量,n为1到4000字节
LONG:变长字符列,最大2GB,不可以索引,类型较老,逐渐被BLOB、CLOB和NCLOB等取代(一个表只能有一个LONG类型字段)
2)数字型
NUMBER(m,n):变长数值列,m为所有有效数字的位数,n为小数位数
如number(5,2)表示最大值为99999,再大就截取多余位数,小数会四舍五入
3)日期型
DATE:实际为7个字节,默认格式为DD-MM-YY
4)大对象数据型
主要包括BLOB、CLOB和NCLOB,主要用于保存大规模的非文本信息,最大为4GB
Bfile类型,用于在数据库外保存大型二进制信息,最大为4GB,9i数据库只能读取不能写入信息
5)其他数据类型
RAW(n):变长二进制数据类型,n为1到2000字节
LONG ROW:变长二进制数据,最大为2GB
3、Oracle体系结构
1)服务器结构
包含如下内容:
init.ora:文件可以配置运行时环境
该文件包含内容很多,主要分为
地址项:描述工作所需的文件所在地
限制项:控制Oracle行为的一个数字参数,具体分为资源限制器(Resource Limiter)和内存分配(Memory Allocation)
特征项:往往为枚举类型值和布尔型
改变参数:并非所有的参数都可以改变,要想了解可以
select issys_modifiable,name from v$parameter;
其中issys_modifiable为immediate时可以动态修改,deferred表示重新连接后起作用,false表示不可以修改
共享内存(Shared Merory):也称为SGA(System Global Area)全局系统区,为Oracle启动时获取的内存总数,里面有
数据库文件(Database File):分为数据文件(dbf)、控制文件(ctl)和重做日志文件(log)数据缓存和库缓存(SQL语句结果的缓存)
其中的控制文件,可以查看它的结构如下:desc v$controlfile;
其中的日志文件,可以查看文件如下:select * from v$logfile;它会显示多个文件,如果一个写满,自动转向下一个
对于文件结构,可以使用desc v$logfile;
支持的进程(Support Process):
数据库复写器(dbw0):如果缓冲区标记为脏(Dirty),负责将数据缓冲区信息写入硬盘,也是唯一一个把数据写入数据库的进程,也称为通信控制器(Director of Communication),共可以建立10个该进程,所以本来该进程叫dbwr。
进程监控器(pmon):负责用户会话异常结束或者用户没有清除会话的时候,执行清除操作,也称为特工(Secret Agent)
系统监视器(smon):频繁检查系统,确保数据库文件的一致性,并在需要时,可以执行恢复功能,也称为审计员(Comptroller)
日志复写器(lgwr):日志缓冲管理,可以称为银行出纳员(Bank Teller)
校验点(ckpt):保证数据文件的同步,可称为调度员(Dispatcher)
恢复器(reco):处理分布式Oracle环境下的失败事务
归档器(arc0):启动介质恢复后,自动将重做日志的备份存储到指定的地方
网络访问(Network Access)
跟踪文件(Trace File):包含错误信息和重要行为的时间戳信息
运行时间库(Runtime Library):一些组件和动态链接库文件
4、简单使用
1)表
创建表
create table stu(
sid integer primary key,
name varchar(8) not null,
sex char(1) check(sex in('M','F')));
insert into stu values(1,'Tom','M');
insert into stu values(2,'Alice','F');
insert into stu values(3,'eric','M');
从已有表建立新表
create table stu1
as
select * from stu;
2)视图
创建视图
create view stuMale
as
select * from stu where sex='M';
实例化视图——复制数据保存在视图中,效果有:如性能大于直接访问表,可以重写查询
create materialized view Mstu
as
select * from stu where sex='M';
3)索引
索引中含有唯一索引和非唯一索引,其中还有主键索引(主键索引一经建立,就难以删除)
create unique index index1 on stu(sid);
alter table stu add constraint pk primary key (sid);
使用索引应该注意,当查询结果返回行数少于表记录数的5%,索引有效;否则,使用索引不是最快的方式(95/5规则)
特别是对于一种枚举值很少、任何查询返回的结果都会很大的(大于表记录数的5%)字段,使用位图索引效果更好
create bitmap index bindex on stu(sex);
4)触发器
drop trigger t11;
create trigger t11
before
update or insert or delete
on stu
for each row
begin
if INSERTING then
insert into log (log_id,log_table,log_dml,log_key_id,log_username,log_date) values(log_id_seq.nextval,'LOG','INSERT',:new.sid,user,sysdate);
end if;
exception
when others then
raise_application_error(-20000,'Error!!!!' || SQLERRM);
end t1;
/
注意:SQL*Plus可以使用;结束一行,也可以在单独的行使用/结束此行
5)同义词
create synonym ss for stu;
select * from ss;
6)存储过程和函数(还有包)
有问题的代码
create or replace procedure list_stus
begin
declare
cursor getStu is select name,sex from stu;
begin
for c_rec in getStu
loop
dbms_output.put_line('Student Name:' || c_rec.name || ' | Student Sex:' || c_rec.sex);
end loop;
end;
end;
/
查看错误情况:
show errors;
改进的代码:
create or replace procedure list_stus
is
begin
declare
cursor getStu is select name,sex from stu;
begin
for c_rec in getStu
loop
dbms_output.put_line('Student Name:' || c_rec.name || ' | Student Sex:' || c_rec.sex);
end loop;
end;
end;
/
执行存储过程:
execute list_stus;
带参数的存储过程:
create or replace procedure list_stus
(sexvalue in varchar2,stucount out number)
is
begin
declare
cursor getStu is select name,sex from stu where sex=sexvalue;
begin
for c_rec in getStu
loop
dbms_output.put_line('Student Name:' || c_rec.name || ' | Student Sex:' || c_rec.sex);
end loop;
select count(*) into stucount from stu where sex=sexvalue;
end;
end;
/
执行带参数的存储过程:
variable sc number;
describe list_stus;
execute list_stus('M',:sc);
print;
也可以在其他程序中使用以前的存储过程
declare
sexvalue varchar2(2);
sc number;
begin
sexvalue:='M';
list_stus(sexvalue,sc);
dbms_output.put_line(sc);
end;
/
函数的使用(与存储过程的主要区别在于可以使用在语句中)
create or replace function list_stus_f
return number
is
begin
declare
stucount number;
cursor getStu is select name,sex from stu;
begin
for c_rec in getStu
loop
dbms_output.put_line('Student Name:' || c_rec.name || ' | Student Sex:' || c_rec.sex);
end loop;
select count(*) into stucount from stu;
return stucount;
end;
end;
/
使用函数:
desc list_stus_f;
select list_stus_f() from dual;
带参数的函数:
create or replace function list_stus_f
(sexvalue in varchar2)
return number
is
begin
declare
stucount number;
cursor getStu is select name,sex from stu where sex=sexvalue;
begin
for c_rec in getStu
loop
dbms_output.put_line('Student Name:' || c_rec.name || ' | Student Sex:' || c_rec.sex);
end loop;
select count(*) into stucount from stu where sex=sexvalue;
return stucount;
end;
end;
/
带参数函数的使用:
select list_stus_f('M') from dual;
select list_stus_f('F') from dual;
7)关于格式化输出
column sid format 09999 heading 'COL A' truncate
column name format a8 heading 'COL B' wrap
column sex format a2 heading 'COL C' wrap
select * from stu;
column sid format 09999 heading 'COL A' truncate
column name format a2 heading 'COL B' wrap
column sex format a2 heading 'COL C' wrap
select * from stu;
set pagesize 3
set linesize 20
column sid format 09999 heading 'COL A' truncate
column name format a8 heading 'COL B' wrap
column sex format a2 heading 'COL C' wrap
select * from stu;
set pagesize 4
set linesize 20
spool c:\data.txt
column sid format 09999 heading 'COL A' truncate
column name format a8 heading 'COL B' wrap
column sex format a2 heading 'COL C' wrap
select * from stu;
spool off
show all;
set pagesize 4
set linesize 20
column sid format 09999 heading 'COL A' truncate
column name format a8 heading 'COL B' wrap
column sex format a2 heading 'COL C' wrap
btitle '------Data------'
ttitle '.com'
select * from stu;
set pagesize 10
set linesize 80
repheader 'Data Report'
repfooter '----End----'
column sid format 09999 heading 'COL A' truncate
column name format a8 heading 'COL B' wrap
column sex format a6 heading 'COL_C' wrap
btitle '------Data------'
ttitle '.com'
select * from stu;
break on sex;
select * from stu order by 3;
break on sex skip 2;
select * from stu order by 3;
8)PL/SQL结构
注意:注释使用/* */和--
一般使用
declare
a number:=0;
b date:=sysdate;
c varchar2(5);
d boolean:=true;
begin
a:=a+1;
c:='Eric';
d:=false;
exception
when others then
raise_application_error(-20010,'err code : ' || sqlcode || 'desc : ' || sqlerrm);
end;
/
分支判断
set serveroutput on size 1000000;
declare
a number:=2;
begin
if a>=0 then
a:=-a;
else
a:=a;
end if;
dbms_output.put_line(a);
exception
when others then
raise_application_error(-20010,'err code : ' || sqlcode || 'desc : ' || sqlerrm);
end;
/
多重分支判断
set serveroutput on size 1000000;
declare
a number:=2;
begin
if a>0 then
a:=-a;
elsif a=0 then
a:=0;
else
a:=a;
end if;
dbms_output.put_line(a);
exception
when others then
raise_application_error(-20010,'err code : ' || sqlcode || 'desc : ' || sqlerrm);
end;
/
多重分支判断case
set serveroutput on size 1000000;
declare
a varchar2(20):='Tom';
begin
a:=case a
when 'Tom' then 'TOM'
when 'Jack' then 'Jack'
else 'NULL'
end;
dbms_output.put_line(a);
end;
/
循环
set serveroutput on size 1000000;
declare
a number:=1;
begin
loop
if a mod 2=1 then
dbms_output.put_line(a);
end if;
if a>=100 then
exit;
end if;
a:=a+1;
end loop;
end;
/
while循环
set serveroutput on size 1000000;
declare
a number:=1;
begin
while a<=100 loop
if a mod 2=1 then
dbms_output.put_line(a);
end if;
a:=a+1;
end loop;
end;
/
for循环
set serveroutput on size 1000000;
declare
i number;
begin
for i in 1..100
loop
if i mod 2=1 then
dbms_output.put_line(i);
end if;
end loop;
end;
/
倒序for循环
set serveroutput on size 1000000;
declare
i number;
begin
for i in reverse 1..100
loop
if i mod 2=1 then
dbms_output.put_line(i);
end if;
end loop;
end;
/
结合SQL语句的for循环(隐式游标Implicit Cursor)
set serveroutput on size 1000000;
declare
i number;
c number;
begin
select count(*) into c from stu;
for i in 1..c
loop
dbms_output.put_line('Student' || i);
end loop;
end;
/
结合SQL语句的for循环(显式游标Explicit Cursor)
set serveroutput on size 1000000;
declare
i number;
c number;
cursor getcount is select count(*) from stu;
begin
open getcount;
fetch getcount into c;
for i in 1..c
loop
dbms_output.put_line('Student' || i);
end loop;
end;
/
游标的for循环(注意c_rec变量无需声明)
set serveroutput on;
declare
cursor getStu is select name,sex from stu;
begin
for c_rec in getStu
loop
dbms_output.put_line('Student Name:' || c_rec.name || ' | Student Sex:' || c_rec.sex);
end loop;
end;
/
9)异常处理
自己引发错误
begin
raise_application_error(-20010,'Error!!!');
end;
注意,错误码必须为-20000到-20999之间
异常捕获
set serveroutput on;
declare
c number;
begin
select count(*) into c from stu;
exception
when no_data_found then
raise_application_error(-20010,'Error1!!!');
when others then
raise_application_error(-20011,'Error2!!!');
end;
/
注意:常见的异常还有too_many_rows(本应该返回一行,却返回多行),dup_val_on_index(主键冲突),value_error(赋值出错)
[此贴子已经被作者于2010-12-11 20:14:01编辑过]