主题：PLSQL入门的一些简单练习资料

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（赋值出错）