Oracle数据库总结语法三
1、pl/sql的if判断
//输入<18的数字，输出未成年
//输入>18和<40的数字，输出中年人
//输入>40的数字，输出老年人
declare
i number(3) :=$ii;
begin
if i<18 then
dbms_output.put_line("未成年");
elsif i<40 then
dbms_output.put_line("中年人");
else
dbms_output.put_line("老年人");
end if;
end;
2、pl/sql中的loop循环
//三种方式输出1-10
2.1、--while循环
declare
i number(2) :=1;
begin
while i<11 loop;
dbms_output.put_line(i);
i :=i+1;
end loop;
end;
2.2、exit循环(退出循环)常用
declare
i number(2) :=1;
begin
loop
exit while i>10;
dbms_output.put_line(i);
i :=i+1;
end loop;
end;
2.3、for循环
declare
begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
end;
3、游标（可以存放多个对象，多行记录）
//输出表中的所有信息
declare
cursor cl is select * from 表名；
enprow 表名%rowtype;
begin
open cl;
loop
fetch cl into enprow;
exit when cl%notfound;
dbms_output.put_line(enprow.ename);
end loop;
close cl;
end;
//给指定员工张工资
declare
cursor c2(eno emp.deptno%type) is select empno from emp where deptno=eno;
en emp.empno%type; --参数
begin
open c2;
loop
fetch c2 into en;
exit when c2%notfound;
update emp set sal=sal+100 where empno=en;
commit;
end loop;
close c2;
4、存储过程
--概念：提前已经编译好一段pl/sql语言，放在数据库端（可以直接调用，一般都是固定的业务）
参数 默认为：in
//给指定员工涨100元
create or replace procedure p1(eno emp.empno%type)
is
begin
update 表名 set sal=sal+100 where empno=eno; --sal工资字段名
commit;
end;
5、存储函数
//通过存储实现计算指定员工年薪
--存储过程和存储函数的参数都不能带长度
--存储函数的返回值类型不能带长度
----存储函数在调用的时候，返回值需要接收
create or replace function 存储名 (变量名 emp.empno%type) return number
is
s number (10);
begin
select sal*12+nvl(comm,0) into s from 表名 where 字段名=变量名;
return s;
end;
//测试
f_yearsal:存储名称
6、out类型参数使用
//用存储过程算年薪
create or replace procedure 存储名（变量名 emp.empno%type，输出类型参数 out number）
is
s number(10);
c emp.comm%type;
begin
select sal*12,nvl(comm,0) into s,c from 表名 where 字段名=变量名;
输出类型参数 :=s+c;
end;
6.1、in和out区别
凡是涉及到into查询的语句或 :=赋值操作的参数，都必须使用out来修饰
7、存储过程和存储函数的区别
--语法区别
1.关键字不一样
2.存储函数比存储过程多两个return
--本质区别
1.存储函数有返回值，存储过程没有返回值
--存储函数想实现有返回值，必须使用out类型的参数
--即便有out类型的参数，本质也不是有了真的返回值
--而是在存储过程内部给out类型参数赋值，执行完毕，直接拿输出类型参数的值
7.1、自定义函数
//查询员工姓名，和所在部门名称
select e.ename,d.dnaem
from
emp e,depr d
where e.depeno=d.deptno;
7.2、使用存储过程实现提供一个部门编号，输出以后个部门名称
create or replace function 存储名(dno dept.deptno%type) return dept,dname
is
dna dept.dname%type;
begin
select dname into dna from dept where deptno=dno;
return dna;
end;
7.3、使用存储函数来实现查询员工姓名和所在部门名称
select e.ename,存储名(e,deptno) from emp e;
8、触发器
--就是制定规则，在满足增删改操作时，自动触发，不用调用
--语句级触发器，不包含for each row的触发器
--行级触发器：包含for each row的触发器
---加for each row是为了使用：old或者：new对象或者一行记录
8.1、语句级触发器
//插入一条记录，输出一个新员工入职
create or replace trigger t1
after
insert
on 表名
declare
begin
dbms_output.put_line("一个新员工入职");
end;
--触发t1
insert into 表名 values(id,"值")；
commit；
8.2、行级触发器
--不能给员工降薪
//raise_application_errof(-2001-20999(不能重复),"错误信息");
create or replace trigger t2
before
update
on 表名
for each row
declare
begin
if :old.sal>:new.sal then
raise_application_errof(-2001,"不能给员工降薪");
end if;
end;
--触发t2
update 表名 set sal=sal-1 where 字段名=值；
commit；
8.3、触发器实现主键自增【行级触发器】
--分析：在做插入之前，拿到即将插入的数据，给该数据的主键列赋值
---以person表为例
create or replace trigger auid(触发器名称)
before
insert
on 表名
for each row
declare
begin
select s_person.nextval into :new.pid from dual;
end;
//使用auid实现id自增
insert into 表名（字段名） values （'值'）；
commit；
insert into 表名 values （1，'值'）；
commit；
