锁定设置口令:tiger
全局数据库名:orcl
系统标符:orcl
用户名/口令:scott/tiger
按F10可用IE执行sqlplus

错误:ora-28000 the account is locked
解决:新装完Oracle10g后，用scott/tiger测试，会出现以下错误提示：
        oracle10g the account is locked
        oracle10g the password has expired
原因：默认Oracle10g的scott不能登陆。
解决：
（1）conn sys/sys as sysdba; //以DBA的身份登录
（2）alter user scott account unlock;// 然后解锁
（3）conn scott/tiger //弹出一个修改密码的对话框，修改一下密码就可以了(建议还以tiger)

在运行里面输入cmd在DOS模式下输入sqlplus，以system用户名登录，密码是刚装oracle时自己填写的密码orcl，登录进去以后。

SQL> conn sys/sys as sysdba;       （分号是必须的但是我是以system登录的所在这不应该写conn sys/sys as sysdba应该写conn system/orcl as sysdba;）
         Connected.
SQL> alter user scott account unlock;
         User altered.
SQL> commit;
         Commit complete.
SQL> conn scott/tiger//请输入新密码，并确认后OK
Password changed
Connected.

授权:
conn sys/scott as sysdba;
或者conn sys/tiger as sysdba;
grant create table,create view to scott;

语句学习:
desc dept/salgrade/emp

select * from dept;
select sal*12 from emp;
select 2*3 from dept;
select ename||sal from emp;(串连接)
select sal sal_anme from emp;(取别名)
select sal "sal dd" from emp;()
select ename||'afasdf' from emp;(连接)
select ename||'sdfs''sdf' from emp;(用双引号显示单引号)

select deptno from emp;
select distinct deptno from emp;(distinct去掉重复的)
select distinct deptno,job from emp;

select * from emp where deptno=10;
select * from emp where ename='CLARK';
 select sal,deptno from emp where deptno!=10;(!=与<>等同)
select ename,sal from emp where ename>'CBA';
select ename,sal from emp where sal between 800 and 1500;(其中包括800和1500)
select ename,sal form. emp where sal >=800 and <= 1500;

select ename,sal,comm from emp where comm is null;(正确的写法,not null)
select ename,sal,comm from emp where comm=null;(错误的写法)

select ename,sal,comm from emp where sal in (800,1500,2000)(取得等于800,或1500,或2000的值)
select ename,sal,comm from emp where ename in ('SMITH','KING','ABC')(取得满足条件的值)

select ename,sal from emp where deptno=10 and sal>1000; (and可以改成or)
select ename,sal from emp where sal not in (800,1500);(只要不是800,1500的)

%代表0到多个,_代表一个字符,\代表转义字符
select ename from emp where ename like '%ALL%';
select ename from emp where ename like '_A%';(满足第二个字符为A的)
select ename from emp where ename like '%\%%';(满足有%的)
如果不想用\做转义字符.可以这么写:
select ename from emp where ename like '%$%%' escape '$';(即以$为转义字符)

order by语句(排序)
select * from dept;
select * from dept order by deptno desc;
select empno,ename from emp where deptno<> 10 order by empno asc;(先筛选完再排序)
select ename,sal,deptno from emp order by deptno asc,ename desc;(先按deptno升序后,再按ename降序)

函数的使用:
单行函数(给多条就产生多条输出):
select lower(ename) from emp; 小写
select ename from emp where lower(ename) like '_a%';
select ename from emp where ename like '_a%' or ename like '_A%';

select substr(ename,1,3) from emp;(从第一个字符开始写,写三个字符)
select substr(ename,2,3) from emp;(从第二个字符开始写写三个字符)
select chr(65) from dual;(把65转成ASCII字符)
select ascii('A') from dual;
select round(23.652) from dual;(四舍五入)
select round(23.652,2) from dual;(四舍五入到小数点后两位)
select round(23.5443,-1) from dual;(结果20)
上面的函数有个概念即可,不记得查一下就好.下面的函数很重要,无论如何记住它们.
1.to_char转换日期,数字
select to_char(sal,'$99,999.9999') from emp;(让工资以$99,999.9999的形式显示,其实9就是一位数字)(美元)
select to_char(sal,'L99,999.9999') from emp;(人民币)
select to_char(sal,'L00000.000') from emp;(统一格式,不足的以0补齐)
select to_date(hiredate,'YYYY-MM-DD HH:MI:SS') from emp;
select to_date(sysdate,'YYYY-MM-DD HH24:MI:SS') from emp;(把当前日期转成24小时制的.默认是12进制的)
select ename,hiredate from emp where hiredate > to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS') ;
select sal from emp where sal> to_number('$1,250.00','$9,999.99');
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from emp;
select ename,sal*12+comm from emp;(这样只要表达式中有一个空值,结果就为空)
select ename,sal*12+nvl(comm,0) from emp;

多行(组)函数(给出一条或者多条,只会有一行输出)
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
select to_char(avg(sal),'999999.99') from emp;
select round(avg(sal),2) from emp;
select sum(sal) from emp;统计总额
select count(*) from emp;统计记录
select count(*) from emp where deptno=10;求出部门号为10的人数为多少.
select count(comm) from emp;(只要不为空值就算一个)
select count(distinct deptno) from emp;对非重复的条目进行统计

select deptno, avg(sal) from emp group by deptno;先按部门分组,再把各组数据平均值
select deptno,job, max(sal) from emp group by deptno,job;(按照两个字段来分组,同时满足)

select ename from emp where sal=(select max(sal) from emp);取得工资最高的所有人

select deptno,max(sal) from emp group by deptno;(select中的字段,不在group内就要在组函数内)
select avg(sal),deptno from emp group by deptno;每个部门的平均薪水

where语句只对当前的记录进行过滤,不能在group by过后过滤,此时就要用到having
select avg(sal) ,deptno from emp group by deptno having avg(sal) >2000;

记住一个重要的顺序,不能错:
select * from emp
where sal > 1000   条件限制
group by deptno    分组
having             限制
order by           排序

例子:
select avg(sal)
from emp
where sal>1200  
group by deptno
having avg(sal) >1500
order by avg(sal) desc;

子查询(在select语句里套select语句):
select ename,sal from emp where sal=(select max(sal) from emp);
select ename,sal from emp where sal>(select avg(sal) from emp);
select ename,sal ,deptno from emp where sal=(select max(sal) from emp group by deptno);(错误句,因为子查询的结果可能不只一个)

select ename,sal from emp
join (select max(sal) max_sal,deptno from emp group by deptno) t  把select max(sal) max_sal,deptno from emp group by deptno查询的结果当成一张表t
on (emp.sal=t.max_sal and emp.deptno=t.deptno);                   进行表连接

自连接,连接相同的两表并取得对应的值
select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;

交叉连接(cross join)
select ename,dname from emp cross join dept;
等值连接:
select ename,dname from emp,dept where emp.deptno=dept.deptno;    以前的写法
select ename,dname from emp join dept on (emp.deptno=dept.deptno);现在的写法
select ename,dname from emp join dept using (deptno);另一种写法,但不推荐用
不等值连接:
select ename,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);

select ename,dname,grade from
emp e join dept d on (e.deptno=d.deptno)
join salgrade s on (e.sal between s.losal and s.hisal)
where ename not like '_A%';

select e1.ename,e2.ename form. emp e1 join emp e2 on (e1.mgr=e2.empno);(自连接的新语法)

显示空行的方式(左,右外连接)
select e1.ename,e2.ename from emp e1 left join emp e2 on (e1.mgr=e2.empno);(左外连接,即便满足条件的右边没有数据也会显示出来)
select ename,dname from emp e right outer join dept d on (e.deptno=d.deptno);(右外连接)
select ename,dname form. emp e full join dept d on (e.deptno=d.deptno);(全外连接)

select empno,ename from emp;其实在显示此语句的empno和ename后面还有一列rownum,这是看不见的,像隐藏的编号列,不过rownum只能取<或者<=.
select empno,ename from emp where rownum <=5;(取得前五行)
若要看到rownum可以这么写:
select rownum r,ename from emp;
所以解决rownum不能取小于等于的限制如下:
select ename from (select rownum r,ename from emp) where r>10;

--求部门平均薪水等级:
select deptno,avg_sal,grade from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal);
--求部门中哪些人的薪水最高
select ename,sal from emp
join (select max(sal) max_sal,deptno from emp group by deptno) t
on (emp.sal=t.max_sal and emp.deptno=t.deptno);
--求部门平均的薪水等级(平均所有的等级)
select avg(grade) from
(select deptno,ename,grade from emp join salgrade s on (emp.sal between s.losal and s.hisal)) t
group by deptno;
--雇员中有哪些人是经理人
select  ename from emp where empno in (select distinct mgr from emp);
--不准用组函数,求薪水的最高值(面试题)
思路:左右两张同样的表,找左边的薪水比右边的薪水高,再找左右边对应不上的薪水值即为最高值.
第一步:
select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal);
第二步:
select  distinct sal from emp where sal not in
(select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));
--求平均薪水最高的部门的部门编号
第一步(每个部门的平均薪水):
select avg(sal),deptno from emp group by deptno;
第二步,对上面求出的求最大值
select max(avg_sal) from
(select avg(sal) avg_sal,deptno from emp group by deptno);
第三步:
select deptno,avg_sal from
(select avg(sal) avg_sal,deptno from emp group by deptno)
where avg_sal=
(select max(avg_sal) from
 (select avg(sal) avg_sal,deptno from emp group by deptno));
修改:
select deptno,avg_sal from
(select avg(sal) avg_sal,deptno from emp group by deptno)
where avg_sal=
(select max(avg(sal)) from emp group by deptno));
--求平均薪水最高的部门的部门名称
select dname from dept where deptno=
(
  select deptno,avg_sal from
    (select avg(sal) avg_sal,deptno from emp group by deptno)
  where avg_sal=
    (select max(avg_sal) from
 (select avg(sal) avg_sal,deptno from emp group by deptno))
);
--求平均薪水的等级最低的部门的部门名称
1.select deptno,avg(sal) avg_sal from emp group by deptno
2.select deptno,grade,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)
3.select min(grade) from
(select deptno,grade,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal));
4.
select danme,t1.deptno,grade,avg_sal from
(select deptno,grade,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)) t1
join dept on (t1.deptno=dept.deptno)
where t1.grade=
(select min(grade) from
(select deptno,grade,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)));
改进(利用视图View,简单理解视图就是一张表或是一个子查询):
create view v$_dept_avg_sal_info as
select deptno,grade,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)

select danme,t1.deptno,grade,avg_sal from
( v$_dept_avg_sal_info t1
join dept on (t1.deptno=dept.deptno)
where t1.grade=
(select min(grade) from
v$_dept_avg_sal_info);
--求部门经理人中平均薪水最低的部门名称

--求比普通员工的最高薪水还要高的经理人
select ename from emp
where empno in (select distinct mgr from emp where mgr is not null)
and
sal>
(select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null));
--求薪水最高的前5名雇员
select ename,sal from
(select ename,sal from emp order by sal desc) where rownum<=5;
--求薪水最高的第6到第10名雇员(重点掌握)
首先排好序:
select ename,sal from emp order by sal desc;
然后在此基础上加上rownum:
select ename,sal,rownum r from
  (select ename,sal from emp order by sal desc)
然后再在此基础上取6到10:
select ename ,sal from
   (select ename,sal,rownum r from
  (select ename,sal from emp order by sal desc))
where r>=6 and r<=10;
--比较效率
 select * from emp where deptno=10 and ename like '%A%';
 select * from emp where ename like '%A%' and deptno=10;
第一句理论上较高,因为它查询的语句较少,但是在Oracle中并一定是这样,因为Oracle会对语句进行优化,有可能自动把第二句后面的语句提前执行完再执行前面的.

DML(insert/delete/update)
Oracle支持多用户,首先为它创建不同的用户
conn sys/tiger as sysdba;
1--backup scott(备份原用户及表信息)
   exp (导出,新建一个文件夹,在该目录下执行此命令,一路回车即可)
2--create user
   create user aa identified by bb default tablespace users quota 10M on users;(创建用户名aa,密码bb,在默认的表空间users下操作,分配了10M的表空间)
   grant create session,create table,create view to aa;(分配权限)
3--import the data
   imp;(一路回车,直到问你用户名,这是问你从哪导来的,所以填scott,再一路回车)

drop user aaa;(要确定要删除的用户存在)
建议对表操作前先对表进行备份:
create table emp2 as select * from emp;
同样的,对dept,salgrade创建表dept2,salgrade2,然后直接在这新建的表上操作.
insert into dept values (50,'aa','bb');
rollback;

insert into dept2 values (33,'aa','bb');
insert into dept2 (deptno,dname) values (50,'aas');(插入特定字段)
insert into dept2 select * from dept;

update emp2 set sal =sal*2,ename=ename||'_' where deptno=10;

delete from emp2;
delete from dept2 where deptno<25;
SQL面试题:
有3个表S,C,SC
S(sno,sname)代表(学号,姓名)
C(cno,cname,cteacer)代表(课程,课名,教师)
SC(sno,cno,scgrade)代表(学号,课程成绩)
--找出没选过"liming"的所有学生姓名
select sname from s join sc on (s.sno=sc.sno) join c (c.cno=sc.cno) where c.cteacher <> 'liming';
--列出2门以上(包含2门)不及格学生姓名及平均成绩
select  sname where sno in (select sno from sc where scgrade<60 group by sno having count(*)>=2);
--既学过1号课程也有学过2号课程的所有学生的姓名
select sname from s where sno in(
select sno from sc where cno=1 and sno in (select distinct sno from sc where cno=2));

create table t (a varchar2(10));
desc t;
drop table t;

事务transaction:
rallback;
commit; (搞清楚在什么情况下是自动提交的,像create,exit退出自动提交,如果是关闭窗口则是自动回滚)

Create全集
create table stu (
  id number(6),
  name varchar2(20),
  sex number(1),
  age number(3),
  sdate date,
  grade number(2) default 1,(设置其默认值为1,当不赋值时)
  class number(4),
  email varchar2(50)
);

约束(constraint)--非空,唯一(不允许重复,但允许重复的null值),主键,外键,check
create table stu (
  id number(6),
  name varchar2(20) constraint stu_name_nn not null,
  sex number(1),
  age number(3),
  sdate date,
  grade number(2) default 1,
  class number(4),
  email varchar2(50) unique
);
组合约束(由名字和Email的组合共同约束):
create table stu (
  id number(6),
  name varchar2(20) constraint stu_name_nn not null,
  sex number(1),
  age number(3),
  sdate date,
  grade number(2) default 1,
  class number(4),
  email varchar2(50) ,
  constraint stu_name_email_uni unique(name,email)
);
主键约束(即要求非空,唯一组合):
id number(6) primary key
也可以在表最后这样写:
constraint stu_id_pk primary key(id)

3表,S,C,SC分别以sno,cno,sno+cno作为主键
多对多的设计通常是用三张表来做,其中一张作为两表的关系

外键约束(references)--加在两个字段上:
create table stu (
  id number(6),
  name varchar2(20) constraint stu_name_nn not null,
  sex number(1),
  age number(3),
  sdate date,
  grade number(2) default 1,
  class number(4) references class(id),
  email varchar2(50) ,
  constraint stu_name_email_uni unique(name,email)
);

create table class
(
  id number(4) primary key,
  name varchar2(20) not null
);
同样此约束也可以加在表末:
constraint stu_class_fk froeign key(class) references class(id),
注意:被参考字段必须是主键.一旦两者是有外键约束就无法在一表中删除数据,因它们受到约束

alter table stu add(addr varchar2(100));往表中添加字段
alter table stu drop (addr);删除表中字段
alter table stu modify (addr varchar2(150));修改字段大小,但不能小于现在存储的字段内容大小.
alter table stu drop constraint stu_class_fk;去掉约束性条件
alter table stu add constranint stu_class_fk foreign key (class) references class(id);添加约束条件

Oracle把约束之类的放在一张单独的字典表中
desc user_tables;
select table_name from user_tables;查看当前用户下的表
select view_name from user_views;查看当前用户下的视图
select constraint_name from user_constraints;查看约束名
desc user_constraints;
select contraint_name,table_name from user_constraints;查看约束及其所施加的表

desc dictionary;字典表,存入字典表的表

索引(提高读的效率)--重点掌握
desc stu;
create index idx_stu_email on stu (email);创建Email的索引
drop index idx_stu_email;
select index_name from user_indexes;

视图(子查询),以v$开头,有用才去建:
create view v$_stu as select id,name,age from stu;

序列sequence(Oracle独有的东西)--用来产生一个唯一的,不间断的序列,一般用来做主键--相当Sql中的Identity
create sequence seq;  seq相当一个对象
select seq.nextval from dual;  1
select seq.nextval from dual;  2
select seq.nextval from dual;  3
......

insert into article values (seq.nextval,'a','b');
insert into article values (seq.nextval,'a','b');
insert into article values (seq.nextval,'a','b');
...
select * from article;  查看结果

三范式:
追求无冗余数据.
第一范式:
1.要有主键 2.列不可再分,即列中再分列
第二范式--多对多表设计时应该遵循的东西:
不能存在部分依赖--如果有两个或者两个以上做主键时,不是主键的那些字段不能够部分依赖主键
解决办法:拆分成三张表,其中一张专门存入另两表的关系
第三范式--不能传递依赖:
除了主键外的任何字段必须直接依赖于主键,不能传递依赖.
解决办法:拆分成两表

思考BBS的数据库应该如何设计
首先就是应该明确需求.1.BBS中分版块,2.版块中有帖子,每个帖子都能让人回复,可以是树状结构的,3.只有注册的用户才能发帖,4.每个版块有自己的版主,它们具有管理权限.
根据上面的需求设计出自己的数据库

简单的PL/SQL(不做重点)
Oracle内部的编程语言,用来写触发器,存储过程等,是一种面向过程的语言
--最简单的语句块:
begin
  dbms_output.put_line('Hello world');
end;
/
上面的主要语句其实就像Java中的System.out.println.
事实上如果只是上面的语句,Hello world不会输出,要输出结果就要先执行下面的语句:
set serveroutput on;
然后再执行上面的PL_SQL语句就可以看到结果了.主要是因为默认的环境变量serveroutput是关闭的

--语句块的组成
declare
  v_name varchar2(20);
begin
  v_name:='myname';
  dbms_output.put_line(v_name);
end;
/
declare声明,PL/SQL中的变量都以V_开头,可以给变量赋值,Pascal的赋值格式

declare
  v_num number:=0;
begin
  v_num:=2/v_num;
  dbms_output.put_line(v_num);
exception
 when others then
   dbms_output.put_line('error');
end;
/

--变量声明的规则
1.变量名不能够使用保留字,如from,select等(所以推荐前面以v_开头)
2.第一个字符必须是字母
3.变量名最多包含30个字符
4.不要与数据库的表或者列同名
5.每一行只能声明一个变量

--常用变量类型
1.binary_integer:整数,主要用来计数而不是用来表示字段类型
2.number:数字类型
3.char:定长类型
4.varchar2:变长字符串
5.date: 日期
6.long:长字符串,最长2GB
7.boolean:布尔类型,可以取值为true,false和null值

declare
  v_temp number(1);
  v_count binary_integer:=0;
  v_sal number(7,2):=4000.00;
  v_date date:=sysdate;
  v_pi constant number(3,2) :=3.14;
  v_valid boolean:=false;
  v_name varchar2(20) not null:='myname';
begin
  dbms_output.put_line('v_temp value:'||v_temp);
end;
/
输出语句中的||代表连接符.而且Oracle中无法输出boolean的值,通过变通的方法可以这样考虑,当为true时输出一个结果,为false时输出一个结果,然后根据结果反推.

Oracle中的注释可以用--,也可以用/* */

--变量声明,使用%type属性
declare
  v_empno number(4);
  v_empno2 emp.empno%type;
  v_empno3 v_empno2%type;
begin
  dbms_output.put_line('Test');
end;
其中,v_empno2取的是emp表中empno的类型,如果该类型发生改变,其类型随之改变.

--table变量类型
declare
  type type_table_emp_empno is table of emp.empno%type index by binary_integer;
   v_empnos type_table_emp_empno;
begin
  v_empnos(0):=7369;
  v_empnos(2):=7839;
  v_empnos(-1):=9999;
  dbms_output.put_line(v_empnos(-1));
end;
table类型相当数组

--record变量类型(类似类)
declare
  type type_record_dept is record
 (
 deptno dept.deptno%type,
 dname dept.dname%type,
 loc dept.loc%type
 );
begin
  v_temp.deptno:=50;
  v_temp.dname:='aaaa';
  v_temp.loc:='dd';
  dbms_out.put_line(v_temp.deptno||''||v_temp.dname);
end;

--使用%rowtype声明record变量
declare
 v_temp dept%rowtype;
begin
 v_temp.deptno:=50;
 v_temp.dname:='aaaa';
 v_temp.loc:='sdf';
 dbms_output.put_line(v_temp.deptno||''||v_temp.dname);
end;

--SQL语句的运用
delcare
 v_ename emp.ename%type;
 v_sal emp.sal%type;
begin
 select ename,sal into v_ename,v_sal from emp where empno=7369;
 dbms_output.put_line(v_ename||''||v_sal);
end;
这里要求是有且只能返回一条记录,无论是没有返回记录还是返回多条记录都会报错.而且多条记录放到一个变量里面显然也不合适.注意:不使用游标的情况下,select语句必须加into

declare
 v_emp emp%rowtype;
begin
 select * into v_emp from emp where empno=7369;
 dbms_output.put_line(v_emp.ename);
end;
rowtype代表一行,刚好可以用来存储一条记录

declare
 v_deptno dept.deptno%type:=50;
 v_dname dept.dname%type:='aaaa';
 v_loc dept.loc%type:='adsf';
begin
 insert into dept2 values(v_deptno,v_dname,v_loc);
 commit;
end;

declare
 v_deptno dept.deptno%type:=50;
 v_count number;
begin
 update emp2 set sal =sal/2 where deptno = v_deptno;
 --select deptno into v_deptno from emp2 where empno=7369;
 --select count(*) into v_count from emp2;
 dbms_output.put_line(sql%roecount||'条记录被影响');
 commit;
end;
在增,删,改时要注意commit,让修改永久.

DDL语句
begin
  execute immediate 'create table T (nnn varchar2(20) default ''aaa'')';
end;
注意:前面要写execute immediate,单引号里面的单引要用两上单引号代表一个单引号
如果上述语句执行出错,可能是表T已经存在,用drop table T;删除即可.

--if语句
--取出7369的薪水,如果<1200,则输出'low',如果<2000则输出'middle',否则输出'high'
declare
 v_sal emp.sal%type;
begin
 select sal into v_sal from emp where empno=7369;
 if(v_sal<1200) then
  dbms_output.put_line('low');
 elseif(v_sal<2000) then
  dbms_output.put_line('middle');
 else
  dbms_output.put_line('high');
 end if;
end;

--循环
delclare
 i binary_integer :=1;
begin
  loop
 dbms_output.put_line(i);
 i:=i+1;
 exit when(i>=11);
  end loop;
end;
(相当do while)
delclare
 j binary_integer :=1;
begin
  while j<11 loop
 dbms_output.put_line(i);
 j:=j+1;
  end loop;
end;
(相当while)
begin
  for k in 1..10 loop
 dbms_output.put_line(k);
  end loop;

  for k in reverse 1..10 loop
 dbms_output.put_line(k);
  end loop;
end;
(相当for)

--错误处理
declare
 v_temp number(4);
begin
 select empno into v_temp from emp where deptno=10;
exection
 when too_many_rows then
 dbms_output.put_line('太多记录了');
 when others then
 dbms_output.put_line('error');
end;

declare
 v_temp number(4);
begin
 select empno into v_temp from emp where deptno=2222;
exection
 when no_data_found then
 dbms_output.put_line('没记录');
end;

--创建一个表专门存储错误信息
create table errorlog
(
 id number primary key,
 errcode number,
 errmsg varchar2(1024),
 errdate date
);
create sequence seq_errorlog_id start with 1 increment by 1;
declare
 v_deptno dept.deptno%type:=10;
 v_errcode number;
 v_errmsg varchar2(1024);
begin
 delete from dept where deptno=v_deptno;
 commit;
exception
 when others then
  rollback;
  v_errcode:=SQLCODE;
  v_errmsg:=SQLERRM;
  insert into errorlog values (seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate);
 commit;
end;

游标(cursor)--重点
--得到结果集中的第一条
declare
 cursor c is
  select * from emp;
 v_emp c%rowtype;
begin
 open c;
 fetch c into v_emp;
 dbms_output.put_line(v_emp.ename);
 close c;
end;
注意,执行游标只有当执行到open c;时才会执行select语句,fetch c into v_emp是取出当前游标所指向的值存入v_emp中.注意是最后要关闭游标.

--使用游标取出所有结果
declare
 cursor c is
  select * from emp;
 v_emp c%rowtype;
begin
 open c;
 loop
  fetch c into v_emp;
  exit when(c%notfound);
  dbms_output.put_line(v_emp.ename);
 end loop;
 close c;
end;
如果上面的退出判断和输出语句对换,就会最后的那条结果输出两次,所以注意顺序不要倒掉.

--while
declare
 cursor c is
  select * from emp;
 v_emp c%rowtype;
begin
 open c;
 fetch c into v_emp;
 while(c%found) loop
  
  dbms_output.put_line(v_emp.ename);
  fetch c into v_emp;
 end loop;
 close c;
end;

--for(v_emp不用声明)--推荐使用
declare
 cursor c is
  select * from emp;
begin
 for v_emp in c loop
  
  dbms_output.put_line(v_emp.ename);
  fetch c into v_emp;
 end loop;
end;

--带参数的游标
declare
 cursor c(v_deptno emp.deptno%type,v_job emp.job%type)
 is
   select ename,sal from emp where deptno=v_deptno and job =v_job;
 --v_temp c%rowtype;
begin
 for v_temp in c(30,'CLERK') loop
  dbms_output.put_line(v_emp.ename);
 end loop;
end;

--可更新的游标(非重点)
declare
 cursor c is
  select * from emp2 for update;
begin
 for v_emp in c loop
   if(v_temp.sal<2000) then  
  update emp2 set sal=sal*2 where current of c;
   elseif(v_temp.sal=5000) then
  delete from emp2 where current of c;
   end if;
 end loop;
 commit;
end;
注意Oracle中赋值是用:=,如果只是=,那表示比较

--存储过程(给常用的语句块写成存储过程方便调用)
create or replace procedure p
is
 cursor c is
  select * from emp2 for update;
begin
 for v_emp in c loop
   if(v_temp.deptno=10) then  
  update emp2 set sal=sal+10 where current of c;
   elseif(v_temp.deptno=20) then
  update emp2 set sal=sal+20 where current of c;
   else
  update emp2 set sal=sal+50 where current of c;
   end if;
 end loop;
 commit;
end;

--执行存储过程,并查看结果
exec p;
select * from emp2; 
--执行存储过程的另一种方法
begin
 p;
end;

exec p;

--带参数的存储过程
--in是传入,out是传出,由存储过程返回结果
create or replace procedure p
 (v_a in number,v_b number ,v_ret out number,v_temp in out number)
is

begin
 if(v_a>v_b) then
   v_ret:=v_a;
 else
   v_ret:=v_b;
 end if;
 v_temp:=v_temp+1;
end;
--下面是调用
declare
 v_a number:=3;
 v_b number:=4;
 v_ret number;--由存储过程返回
 v_temp number:=5;

begin
 p(v_a,v_b,v_ret,v_temp);
 dbms_output.put_line(v_ret);
 dbms_output.put_line(v_temp);
end;

--函数
create or replace function sal_tax
 (v_sal number)
 return number
is
begin
 if(v_sal<2000) then
  return 0.10;
 elseif(v_sal<2750) then
  return 0.15;
 else
  return 0.20;
 end if;
end;
--调用
select sa_tax(sal) from emp;

--触发器(概念要掌握)
--谁在什么时间做了什么操作,比如银行,是一定要记录所有的操作的,这时触发器就用上了.触发器不能直接执行,要依付在某张表上.
create table emp2_log
(
 uname varchar2(20),
 action varchar2(10),
 atime date
);
create or replace trigger trig
 after insert or delete or update on emp2 for each row
begin
 if inserting then
  insert into emp2_log values(USER,'insert',sysdate);
 elseif updating then
  insert into emp2_log values(USER,'upadte',sysdate);
 elseif deleting then
  insert into emp2_log values(USER,'delete',sysdate);
 end if;
end;
--测试一下
update emp2 set sal=sal*2 where deptno =30;
select * from emp2_log;
说明:触发器可以分为事前触发(before)和事后触发(after),for each row的意思是每影响一行都会触发,即触发多次.如果去掉for each row,则只会触发一次
--删除触发器
drop trigger trig;

如果要表中要修改一数据.结果此数据又被别的表所参考,直接修改肯定报错,此时可以考虑用触发器在修改此记录同时修改那些关联的数据
create or replace trigger trig
 after update on dept
 for each row
begin
 update emp set deptno=:NEW.deptno where deptno=:OLD.deptno;
end;
然后再执行如下语句就不会出错了:
update dept set deptno=99 where deptno=10;
所以这里应该是先执行了触发器才执行了更新过程.
select * from emp;
rollback;

--树状结构的存储与展示
drop table article;

create table article
(
 id number primary key,
 content varchar2(4000),
 pid number,
 isleaf number(1),--0代表非叶子节点,1代表叶子节点
 alevel number(2)
);

insert into article values(1,'aa',0,0,0);
insert into article values(2,'bb',1,0,1);

insert into article values(3,'cc',2,1,2);
insert into article values(4,'dd',2,0,2);
insert into article values(5,'ee',4,1,3);
insert into article values(6,'ff',1,0,1);
insert into article values(7,'gg',6,1,2);
insert into article values(8,'hh',6,1,2);
insert into article values(9,'ii',2,0,2);
insert into article values(10,'jj',9,1,3);
commit;

--结果应如下:
aa
 bb
  cc
  dd
   ee
 ff
  gg
  hh
  ii
   jj

--创建存储过程
create or replace procedure p (v_pid article.pid%type) is
  cursor c is select * from article where pid =v_pid;
begin
  for v_article in c loop
 dbms_output.put_line(v_article.content);
 if(v_article.isleaf=0) then
  p (v_article.id);
 end if;
  end loop;
end;

set serveroutput on;
exec p(0);
--产生缩进
create or replace procedure p (v_pid article.pid%type,v_level binary_integer) is
  cursor c is select * from article where pid =v_pid;
  v_preStr varchar2(1024):='';
begin
  for i in 1..v_level loop
 v_preStr:=v_preStr||'****';
  end loop;

  for v_article in c loop
 dbms_output.put_line(v_preStr||v_article.content);
 if(v_article.isleaf=0) then
  p (v_article.id,v_level+1);
 end if;
  end loop;
end;

exec p(0,0)

思考:把系统自带的emp表照上面展示出来