文章目录
一、PL/SQL基础
## 1 用户自定义类型
1-1 对象类型
create [or replace] type 类型名 as object(
字段名 字段类型,
...
);
create [or replace] type schoolType as object(
name varchar2(20),
city varchar2(20),
street varchar2(20)
);
实例： create table t_student(
name varchar2(20),
school schoolType --用户自定义类型
);
-- 插入数据
insert into t_student values('lisi',schoolType('zzx','sh','tz'));
-- 如何查询
select
s.name,
s.school.name,
s.school.city,
s.school.street
from t_student s;
-- 创建一张和schoolType类型一致的表
create table t_student2 of schoolType;
### 1-2 自定义可变数组类型
-- schoolType数组
create type school_list as varray(10) of schoolType;
实例：
create table t_student3(
name varchar2(20),
schools school_list --schoolType数组
);
-- 如何插入数据
insert into t_student3 values('aaa',school_list(
schoolType('a','b','c'),
schoolType('d','e','f'),
)
);
-- 如何查询
select
e.*
from
t_student3 t,table(t.schools) e;
### 1-3 嵌套表 类型
人 人看过的小说
create type novs as Object(
name varchar(20),
author varchar(20)
);
create type per_novs as table of novs;
create table t_person(
name varchar(20),
books per_novs
)tablespace users
nested table books store as t_book_list;
-- t_person 主表
-- t_book_list 嵌套表
-- 嵌套表和主表是分开存放的
-- 嵌套表如何插入数据
insert into t_person values('wangwu',per_novs(
novs('java从入门到放弃','aaa'),
novs('Oracle从入门到放弃','bbb')
)
);
select * from table(select t.nooks from t_person t);
2 PL/SQL基础语法
oracle对sql的扩充一种模块化的程序语言 面向过程
优点：
1 和sql紧密结合
2 效率高
3 可扩展
4 可访问oracle预定义的包
5 支持面向对象技术
pl/sql 是以块为基础单位
块就是一段程序
declare
定义变量 变量名/类型/初始值
begin
sql语句/表达式
exception
异常
end
-- 在PL/SQL中 || 此运算符是连接
-- 动态获取表数据
declare
v_id scott.emp.empno%type;
v_name scott.emp.ename%type;
begin
select
empno,ename
into
v_id,v_name
from
emp
where
rownum = 1;
dbms_output.put_line('v_id:'|| v_id || 'v_name:' || v_name);
end;
## 3 流程控制语句
-- 分支语句 if
if 条件1 then
xx;
xx;
[elsif 条件2
xx;
...
else
xx;
...]
end if;
查询emp表中empno为7499的员工工资
如果工资大于10k 直接输出 工资及格
否则 将表中的工资数据 提高1.5倍
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=7499;
if v_sal>10000 then
dbms_output.put_line('ok')；
else
update emp set sal=v_sal * 1.5 where empno=7499;
end if;
end;
case when 条件1 then xx
when 条件2 then xx
else xx;
end case;
自带break效果
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=7499;
case when v_sal>10000 then
dbms_output.put_line('ok')；
when v_sal>8000 then
dbms_output.put_line('no')；
else
dbms_output.put_line('workhard')；
end if;
end;
-- 基本循环
loop
xxx;
end loop;
declare
v_i number(10);
bgin
for v_i in [reverse(反向输出)] 1..10 loop
dbms_output.putline('v_i的值是: ' || v_i);
end loop;
end;
while 条件 loop
xx;
...
end loop;
declare
v_i number(10) :=1;
begin
while v_i <10 loop
dbms_output.putline('v_i的值是: ' || v_i);
v_i:=v_i+1;
end loop;
end;
查询empno为7782的员工工资
将工资上调50%直到 >=10000
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=7782;
while v_sal <= 10000 loop
update emp set sal=v_sal*1.5 where empno=7782;
end loop;
end;
两个关键字
JAVA： break continue
PL/SQL：exit continue
loop
exit; -- 无条件退出
end loop;
loop
exit when 条件; -- 当满足条件时退出
end loop;
loop
continue; --无条件结束本次循环
end loop;
loop
continue when 条件; --当满足条件时结束本次循环
end loop;
二、Oracle异常
declare
xxx;
begin
xxx;
exception
end;
declare
v_name emp.ename%type;
begin
dbms_output.put_line(1/0);
exception
when zero_divide/no_data_found/too_many_rows then
dbms_output.put_line('出错了');
end;
-- 自定义异常
名字+编号
declare
e_name Exception;
begin
end;
-- 先定义一个异常
-- 为定义的异常初始化一个编号
-- 编号范围 -20000 —— -20999
-- 如果不在编号范围内 会报错
declare
name Exception;
prama exception_init(e_name,code)
begin
end;
-- 异常的触发方式
1 oracle 自动触发
2 raise exception name
2-1 如果是第一种方式定义的异常 只有异常名
2-2 如果是第二种
名字+异常
错误信息
3 raise application error (code,'errMsg');
不但有名字 和 编号
还有错误信息
declare
userNotFound exception;
pragma Exception_init(userNotFound,-209000);
v_count number(11);
begin
select count(*) into v_count from emp where ename='aa';
if v_count < 1 then
raise userNotFound;
end if;
exception
when userNotFound then
dbms_output.put_line('没有符合的员工,code= ' || SQLCODE);
end;
declare
userNotFound exception;
pragma EXCEPTION_INIT(userNotFound,-209000);
v_count number(11);
begin
select count(*) into v_count from emp where ename='aa';
if v_count < 1 then
raise_application_error(-209000,'内有符合条件的员工');
end if;
exception
when userNotFound then
dbms_output.put_line(SQLERRM);
end;
二、Oracle触发器
1、触发器的概念
触发器是存储在服务器中的程序单元，当数据库中某些事件发生时（比如insert\update\delete），
数据库自动启动触发器，执行触发器中的相应操作。
(1) 触发器是一种特殊的存储过程，具备事务的功能；
(2) 触发器不能被直接调用，而是由事件来触发；–例如：增删改
(3) 触发器常用于加强数据的完整性约束和业务规则等。
2、触发器事件
Insert,update,delete,create(创建对象时),alter,drop
logon/logoff(用户的登录或注销时执行触发器)
startup/shutdown(数据库打开或关闭时执行触发器)。
3、触发器时间
before 在指定的事件发生之前执行触发器
after 在指定的事件发生之后执行触发器
## 4、触发级别
行级触发
语句触发
行级触发器：一条条的删，删一条就触发一个行级触发器。
语句级触发器：执行一条触发一次。
5、触发器语句
create [or replace] tigger 触发器名 触发时间 触发事件
on 表名
[for each row]
begin
pl/sql语句
end
实例： 下面的触发器在更新表tb_emp之前触发，目的是不允许在周末修改表：
create or replace trigger auth_secure before insert or update or DELETE
on tb_emp
begin
IF(to_char(sysdate,'DY')='星期日') THEN
RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表tb_emp');
END IF;
END;
--通过触发器实现序列号自增
--复习Oracle创建序列
create sequence my_seq
increment by 1
start with 1
nomaxvalue nocycle
cache 20;
--未定义MAXVALUE(序列递增时)/MINVALUE(序列递减时) 默认是MAXVALUE
--NOMAXVALUE 即使设置NOMAXVALUE，仍有最大值限制。
--CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环
--未定义CYCLE时，默认是NOCYCLE。
create or replace tigger my_tgr
before insert on tab_user
for each row --对表的每一行触发器执行一次
declare
next_id number;
begin
select my_seq.nextval INTO next_id from dual;
:NEW.ID := next_id; --:NEW表示新插入的那条记录
end;
三、Oracle游标
游标用来处理从数据库中检索的多行记录（使用SELECT语句）。利用游标，程序可以逐个地处理和遍历一次检索返回的整个记录集。
在Oracle中 游标只能下行 不能回退或定位
游标分为两类
1 单行(隐式)游标 不需要申明变量 可以直接使用
又叫SQL游标
在执行dml操作时（insert update delete select…into…）返回单条记录
隐式游标由Oracle自动打开 关闭
四个属性
%found
boolean
判断dml语是否改变了行或select…into…是否返回了数据
不能直接打印输出只能作为判断的条件使用
%notfount
!%found
%isopen
boolean
返回游标是否打开
%rowcount
受影响的行数
declare
begin
delete from emp where empno = 7934;
if sql%found then
dbms_output.put_line('成功删除'||sql%rowcount||'行数据');
else
dbms_output.put_line('没有符合的结果');
end if;
end;
四、Oracle事务
五、Oracle锁
六、Oracle包
七、Oracle存储过程
1、什么是存储过程
所谓存储过程(Stored Procedure)，就是一组用于完成特定数据库功能的SQL语句集，该SQL语句集经过
编译后存储在数据库系统中。在使用时候，用户通过指定已经定义的存储过程名字并给出相应的存储过程参数
来调用并执行它，从而完成一个或一系列的数据库操作。
2、 存储过程的创建
Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。
2128
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
