Oracle语法
PL/SQL和T-SQL都是扩展SQL的一种方言。
PL/SQL的支持者有oracle、MySql等。
T-SQL的支持者有Sql Server、Sybase等。
SQL Monitor(oracle语句追踪工具)
GRANT SELECT, DELETE, INSERT, UPDATE ON ESCMOWNER.FSR_GEN_PPO_LOG TO ESCMUSER;
CREATE PUBLIC SYNONYM FSR_GEN_PPO_LOG FOR ESCMOWNER.FSR_GEN_PPO_LOG;
语法区别
PL/SQL用";"分割每一條語句，T-SQL用回車分割一條語句。
如ORACLE中sql语句可以用分号结尾，且不区分大小写。
SQL SERVER中sql语句不能用分号结尾，且对表等区分大小写。
执行查询时
PLSQL执行查询的时候，FROM子句是必须的，而SQL SERVER不一定
如ORACLE中: select 25 from dual
而SQL SERVER中: select 25
字符串连接
Oracle用|| 符号作为连接符，而SQL Server的连接符是加号：+ 。
Oracle查询如下所示：Select ‘Name’ || ‘Last Name’ From tableName
SQL Server查询如下：Select ‘Name’ + ‘Last Name
日期
Oracle取得日期和采用如下方式：SYSDATE
SQL Server则是这样的：GETDATE()
Oracle日期：Select sysdate from dual
SQL Server：Select getdate()
select * from product_component_version --查询Oracle数据库版本
实例（instance），他指的就是Oracle 的进程和内存。提到数据库（database）时，则是说保存数据的物理文件。可以从多个实例访问一个数据库，但是一个实例一次只能访问一个数据库。
————————————————
版权声明：本文为CSDN博主「事后诸葛亮」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/zq9017197/article/details/5840714
https://github.com/xyp-hf/Oracle/blob/master/
https://xyp-hf.github.io/2017/10/27/第一节：Vue-js框架是什么，为什么选择它？/
SQL （Struct Query Language 结构化查询语言） 的分类 select 语句 （查询语句 DQL）
DDL （data define language 数据定义语言）
create table 建表
drop table 删表
alter table 改表
DML （data manapulation language 数据操作语言）
insert 插入
delect 删除
alter table 该表
TCL （transation control language 事物控制语句）
commit 提交
rollback 回滚
savepoiont 保存点
DCL （data control language 数据控制语句）
grant 授权
revoke 回收权限
使用sqlplus 登录oracle
sqlplus system/密码@别人的ip地址
sqlplus escmuser/FS7GM222@192.168.7.222:1521/epps
https://www.cnblogs.com/zhou__zhou/archive/2010/03/19/sqlplus.html
select userenv('lang') from dual;
select table_name from user_tables;
SELECT * FROM DBA_TABLES WHERE TABLE_NAME='TMP_TEST'
select text from user_source where name='GETMIN'
select * from dual;
SELECT SYSDATE FROM dual;
to_char(start_date,'yyyy-mm-dd hh24:mi:ss')
to_date('2008-08-08 20:08:08','yyyy-mm-dd hh24:mi:ss')
id 自增--序列.nextval
rowid
rownum
临时表，缓存表
https://www.cnblogs.com/kerrycode/p/3285936.html
package
存储过程
视图--create or replace view 视图名 as select语句;
事务
赋权，同义词
1.Oracle查询语句-from
如何从表中查询一个字段对应的内容 select 字段名 from 表名;
如何查询多个字段对应的内容 select 字段名1,字段名2,字段名3 from 表名;
查询 s_emp 表中所有的字段对应的内容 select * from s_emp;
sql 中的数学运算 + - * /
字符串的连接 Oracle 中字符串的拼接 使用 || ,如字符串 || 字符串 || 字符串 select first_name || last_name from s_emp;
NULL 值的处理 nvl(par1, par2) 当par1 为NULL 时会返回par2的值，否则返回 par1 本身
数据的排重 （重复的数据值留一份） distinct
2.Oracle查询语句-where字句
where字句的作用 限制表中数据的返回,符合where条件的数据被选中,不符合where条件的数据被过滤掉。
where的语法格式 select 字段名 from 表名 where 条件;
两个极限条件 1 = 1 恒等 永真 1 ！= 1 恒假 永假
数字类型的表单
字符类型的表达
常见的条件运算符 等于 = 不等于 != ^= <>
4.sql提供的条件运算符
表达闭区间的运算符 [a,b] where 字段名 between a and b;
表达一个字段的值 出现在某个列表内 where 字段名 in 列表;
空值判断运算符 除了 is null 运算符 其他运算符 对 NULL值判断无效
模糊查询运算符 where 字段 like '统配符' 统配串 中最关键 是 两个 统配符: - 代表任意一个字符 % 代表 0-n 个字符串
特例：
user_table 中有一个字段 table_name 代表表名
select table_name from user_tables;
找出所有的以 S_ 开头的表名
select table_name from user_tables where table_name like 'S_%'; (X)
要把 _ 处理成普通字符 不让他代表任意一个字符才行
所以应该这样写
select table_name from user_tables where table_name like 'S\_%' escape '\';
逻辑运算符 （多条件连接）
与 where a and b; 其实就是把java中的 && 换成了 and
------------------------------------------------------------------------------------------
如：显示 s_emp 表中 每个员工的 id first_name salary 要求salary在[1400,2500]中
select id,first_name,salary from s_emp where salary>=1400 and salary<=2500;
或 的逻辑运算符就是 把java中的 || 换成 or
------------------------------------------------------------------------------------------
如：查询s_emp 表的员工 在 31部门 或者在 41部门 或者在 50 最终显示 id first_name dept_id
select id,first_name,dept_id from s_emp where dept_id=31 or dept_id=41 or dept_id=50;
非 ! not
------------------------------------------------------------------------------------------
对立面
= != <> ^=
> <=
< >=
between a and b not between a and b
in not in ( where id not in (1,3,5) id!=1 and id!=3 and id!=5)
like not like
is null is not null
除了is null 之外，其他运算符 都要主要null值的问题
5.Oracle的排序
6.Oracle单行函数
单行函数：
针对sql语句影响的每一行 都做处理 并且针对每一行都会返回一个结果
sql语句影响多少行就返回多少个结果
组函数:
针对sql语句影响的所有行 只返回一个结果
无论sql影响多少行 只返回一个结果
单行函数:
select first_name,upper(first_name) name from s_emp where id=1;
select first_name,upper(first_name) name from s_emp where id=1;
select first_name,upper(first_name) name from s_emp where id=1;
组函数：
select count(first_name) cname from s_emp where id=1;
select count(first_name) canme from s_emp where id>1;
select count(first_name) cname from s_emp where id<1;
Oracle数据库中 处理字符串的单行函数
upper(par1) 变大写 如： select upper('hello') from dual;
lower(par2) 变小写 如： select lower('HEllo') from dual;
initcap(par1) 每个单词首字母变大写 如： select initcap('one world one dream') from dual;
length(par1) 求长度 如：select length('hello') from dual;
concat(par1,par2) 连接两个字符串 如: select concat('a','b') from dual;
nvl(par1,par2) nvl 可以处理任何类型 要求par1 和 par2 类型保持一致
substr(par1,par2,par3)
par1 要处理的字符串或者字段
par2 截取的位置 编号从1 开始 可是负数 -1 代表最后一个字符
par3 截取的长度
select substr('hello',0,2) from dual;
select substr('hello',1,2) from dual;
select substr('hello',2,2) from dual;
replace(par1,par2,par3)
par1 要处理的字符串
par2 被替换的内容
par3 替换成什么
如：select replace('one' world one dream','one','two') from dual;
处理数字的函数
round(par1,par2) 四舍五入
par1 要处理的数字
par2 处理的精确 默认是0 可以省略
保留小数点后一位 看第二位小数
select round(9.58,1) from dual;
对小数点前一位进行四舍五入
select round(9.58,-1) from dual;
------------------------------------------------------------------------------------------
trunc(par1,par2) 截取函数
par1 要处理的数字
par2 处理的精度 默认是 0，可以省略
select trunc(9.58) from dual;
select trunc(12.88) from dual;
保留小数点后一位 不看第二位
select trunc(9.58,1) from dual;
对小数点前一位 进行截取
select trunc(9.58,-1) from dual;
格式显示函数
to_char(par1,par2)
par1 要处理的数据，还可以处理日期
par2 是显示格式 以字符串形式出现 可以省略 如果省略代表把某个类型变成 字符串类型
FM 格式的开头 是format格式的缩写 可以省略
￥ 美元符号
L 本地货币符号 ￥
9 小数点前面 代表 0-9 的任意数字
小数点后面 代表 1-9 的任意数字
0 小数点前面代表 强制显示前导零 如 12345 => 012,234
小数点后面代表 0-9 的任意数字
, 千分位分隔符
. 小数点
select salary,to_char(salary,'FM$099,999.99') from s_emp;
select salary,to_char(salary,'FM$099,999.00') from s_emp;
select to_char(12345.86,'$099,999.99') from dual;
select to_char(12345.86,'$099,999.00') from dual;
如果使用 L 则看的是 环境变量 NLS_LANG
select to_char(12345.86,'L099,999.00') from dual;
函数嵌套
7.Oracle多表查询
8.Oracle的外连接
外连接的特点
外连接的结果集 等于 内连接的结果集 加上 匹配不上的记录一个也不能少
(+) (+)的意思是 (+)所在的字段 对面的表的数据全部被匹配出来。
找出所有的普通员工?
select distinct m.id,m.first_name from s_emp e,s_emp m where e.manager_id(+) = m.id and e.manager_id is null;
内连接 符合连接条件的数据被选中 不符合的被过滤掉
等值连接 员工和对应的部门 部门和对应的地区
非等值连接 工资 和工资级别
自连接 谁是领导
外连接 外连接的结果集 等于 内连接的结果集 加上匹配不上的记录
(+) (+)所在的字段的表的对面的表的数据全部被匹配出来 本质上使用null 记录
自连接 谁是普通员工
等值连接 找出新增的部门
非等值连接 找出超出工资统计范围的员工
9.表连接的SQL99-标准
sql99 的内连接标准
语法格式：
from a表 join b 表 on 表的连接条件 where 过滤条件;
from a表 inner join b 表 on 表的连接条件 where 过滤条件;
sql99 外连接标准
语法格式：
a 表 left outer join b表 on 连接条件 where 过滤条件;
a 表 right outer join b表 on 连接条件 where 过滤条件;
a 表 full outer join b表 on 连接条件 where 过滤条件;
全外连接
全外连接 只是一个逻辑概念 全外连接的结果集 等于 左外连接的结果集 加上右外连接的结果集
然后排除重复的记录。
全外连接 在Oracle 中 是怎么实现的?
全外连接 不是通过两端 加 (+) 实现的，而是通过 union 实现的
union 是可以合并两个结果集 然后排重
union all 是 合并两个结果集 不排重
select id from s_emp union select id from s_emp;
select id from s_emp union all select id from s_emp;
10.Oracle的组函数
组函数 是 对一组数据处理之后 只返回一个结果
常见的组函数
count(par1) 统计数据的个数
max(par1) 统计最大值
min(par1) 统计最小值
avg(par1) 统计平均值
sum(par1) 统计和
11.Oracle的分组
分组的概念
分组 就按照一定的标准 把数据分成若干部分
语法格式
select 字段
from 表名
where 条件
group by 分组标准 having 组函数的过滤条件
order by 排序标准 排序方式;
sql语句的执行顺序
from where group by having select order by
12.Oracle的子查询
13.Oracle的建表、删表、改表
建表
create table 表名(
字段名1 类型,
字段名2 类型,
字段名3 类型
);
------------------------------------------------------------------------------------------
类型: number 数字类型
varchar2(n) 变长字符串类型
char(n) 定长字符串
date 日期类型
删除表 drop table 表名;
修改表
删除某个字段
alter table 表名 drop column 字段名
如：
alter table student100 drop column sdate;
增加一个字段
alter table 表名 add 字段名 类型;
如：
alter table student100 add sdate date;
14.DML-data-manipulation-language
插入语句 insert into
1.1.1 全字段插入 插入数据的顺序和 desc 表名之后的顺序相同
insert into 表名 values(值1,值2，值3);
commit;
删除语法
delete from 表名 where 条件;
commit;
修改数据
update 表名 set 字段名=值 where 条件;
update 表名 set 字段名=值,字段名2=值2 where 条件;
commit;
15.TCL-（transaction-control-language）
事务的概念：事务又叫做交易 开发中希望把多个sql操作 看成一个逻辑整体，这些sql 要求同时成功或者同时失败。
可以完成一个事务的语句：commit; 提交 确认 rollback; 回滚 撤销
事务四大特性 (了解)
原子性 事务中的语句是一个逻辑整体不可分割
一致性 同时成功 同时失败 状态要保持一致
隔离性 一个事务中的操作 在没有提交之前 数据的变化 对另外一个事务而言不可见。
持久性 状态的持久
保存点
引入它就是为了打破事务的原子性
insert into student100 values(1,'test1','111',sysdate);
savepoint a;
insert into student100 values(2,'test2','222',sysdate);
savepoint b;
insert into student100 values(3,'test3','3333',sysdate);
rollback to b;
commit;
16.Oracle中的日期类型
默认的日期类型 dd-MON-yy 表现默认是两年 实际上存储了四位
如何按照指定格式 显示日期
to_char(par1,par2) par1是要处理的日期
par2 日期格式 日期格式
yyyy 四位年 mm 月 dd 日
hh 12小时制 hh24 24小时制 mi 分钟 ss 秒
day 星期几 MON 英文月的缩写 month 英文月的全写
------------------------------------------------------------------------------------------
select id,first_name,to_char(start_date,'yyyy-mm-dd hh24:mi:ss') start_date from s_emp order by start_date;
如何插入一个日期
之前学过 null 和 sysdate sysdate 代表系统时间
如何插入未来的时间 或者 过去的时间点
2008-08-08 20:08:10
insert into student100(id,sdate) values(3,'08-AUG-08');
默认只能放入 年 月 日,时分秒信息是 0
如果需要时分秒信息 则使用 to_date 就可以了
to_date(par1,par2) par1 是一个日期字符串 par2 是日期的格式
insert into student100(id,sdate) values(4,to_date('2008-08-08 20:08:08','yyyy-mm-dd hh24:mi:ss'));
select id,to_char(sdate,'yyyy-mm-dd hh24:mi:ss') from student100;
2012-12-22 23:59:59
日期的调整
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
调整一天 默认按照天为单位
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
to_char(sysdate+1,'yyyy-mm-dd hh24:mi:ss') from dual;
调整一个小时的一半 默认按照天为单位
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
to_char(sysdate+1/(24*2),'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
to_char(sysdate-1/(24*2),'yyyy-mm-dd hh24:mi:ss') from dual;
特殊的调整
按照月为单位 调整
add_months(par1,par2) par1 要调整的日期 par2 调整的月数 可以是负数
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
to_char(add_months(sysdate,1),'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
to_char(add_months(sysdate,-1),'yyyy-mm-dd hh24:mi:ss') from dual;
对日期进行 截取 默认以天为单位截取
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
to_char(trunc(sysdate),'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
to_char(round(sysdate),'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
to_char(trunc(sysdate,'hh'),'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
to_char(trunc(sysdate,'mm'),'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
to_char(trunc(sysdate,'yy'),'yyyy-mm-dd hh24:mi:ss') from dual;
给你一个时间 获取到这个时间对应的月的最后的一天的最后一秒对应的时间点
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
to_char(add_months(trunc(sysdate,'mm'),1)-1/(24*60*60),
'yyyy-mm-dd hh24:mi:ss') from dual;
17.Oracle的约束
约束--约束是 对数据库表中字段加的限制，有了约束之后 可以让我们的数据更加准确 和 完整
约束种类
主键约束 primary key pk
如果给表的某个字段加了主键约束 则这个字段的值 不能重复,并且字段的值不能为 null.
一张表只能有一个主键。
唯一性约束 unique uk
如果给表的字段加了唯一性约束 则 这个字段的值不能重复。
注意 唯一性约束 无法区分两个 null 值
非空约束 not null nn
如果给表的字段增加了 非空约束 则这个字段的值不能是 null 值
检查约束 check ck
如果给表的字段增加了 检查约束 则这个字段必须符合检查条件 符合检查条件的数据
才能进入数据库。
外键约束 references foreign key fk
约束的实现
列级约束:在定义表的某一列时 直接在这一列的后面加约束限制 叫列级约束。
表级约束:在定义完表的所有列之后 再选择某些列加约束限制。
主键的列级约束实现
建立一张 订单表 myorder
id number pk
oname varchar2(30)
create table myorder(
id number primary key,
name varchar2(30)
);
insert into myorder values(1,'test1');
insert into myorder values(1,'test1')
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C007619) violated
如果不给约束起名字 则系统会自动给约束进行一个命名 保证约束的名字不重复
但是这个名字 不方便记忆 并且 不方便排错 以及以后的约束管理。
如何给约束命名
constraint 约束的名字 约束对应的英文
约束的名字构成: 表名_字段名_约束简写
检查约束 需要把检查约束的条件写入()
在上面的表的基础上 加一个字段 omoney number 要求omoney 大于35
------------------------------------------------------------------------------------------
drop table order100;
create table order100(
id number constraint order100_id_pk primary key,
ono varchar2(30) constraint order100_ono_uk unique,
odate date constraint order100_odate_nn not null,
omoney number constraint order100_omoney_ck check(omoney > 35)
);
主键的表级约束实现
create table order200(
id number,
ono varchar2(30),
odate date,
omoney number , constraint order200_id_pk primary key(id)
);
表级约束最大的优势就在于可以做联合约束
create table order300(
id number,
ono varchar2(30),
odate date,
omoney number , constraint order300_idono_pk primary key(id,ono)
);
1 'a'
1 'b'
建立一张 订单表 叫 order1001
id number pk
ono varchar2(30) uk
odate date nn 列级约束 (因为不支持表级)
omoney number ck 要求大于35
除了 odate之外 其它的字段需要使用 表级约束
create table order1001(
id number,
ono varchar2(30),
odate date not null,
omoney number,
constraint order1001_id_pk primary key(id),
constraint order1001_ono_uk unique(ono),
constraint order1001_omoney_ck check(omoney>35)
);
18.Oracle的外键
概念
涉及到两张表 一张叫 父表(主表) 另一张叫子表(从表)
子表:定义了外键的表 一定是子表
子表中外键字段的取值 要受限于父表中某个字段的取值,要么取父表字段对应的值 要么取null值。
语法 考虑外键
建表
先建父表 后建子表 除非先不考虑主外键关系(先建表 后加外键)
create table parent100(
id number constraint parent100_id_pk primary key,
name varchar2(30)
);
create table child100(
id number constraint child100_id_pk primary key,
name varchar2(30),
fid number constraint child100_fid_fk references parent100(id)
);
------------------------------------------------------------------------------------------
1.2.2 插入数据
先插入父表对应的数据 后插入子表对应的数据 除非子表的外键值 使用 null 值
insert into parent100 values(1,'p1');
insert into parent100 values(2,'p2');
commit;
insert into child100 values(1,'c1',1);
commit;
------------------------------------------------------------------------------------------
1.2.3 删除数据
先删除子表中对应的数据 再删除父表中的数据 除非你使用级联
delete from parent100 where id=1;
delete from parent100 where id=2;
------------------------------------------------------------------------------------------
1.2.4 删除表
先删子表 后删父表 除非使用 cascade constraints
drop table parent100；
------------------------------------------------------------------------------------------
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
cascade constraints 表示先解除主外键关系 这样可以删表时不考虑顺序
drop table parent100 cascade constraints;
级联
on delete cascade 级联删除
on delete set null 级联置空
把上面的操作 加在外键上
先建表 后加约束 (外键)
create table emp100(
id number constraint emp100_id_pk primary key,
name varchar2(30),
salary number,
dept_id number
);
alter table emp100 add constraint emp100_dept_id_fk foreign key(dept_id) references dept100(id);
19.Oracle数据库中的其它对象
序列 sequence
1.1 作用
用来产生主键值的 保证产生不重复的数字
------------------------------------------------------------------------------------------
1.2 如何创建序列
create sequence 序列名;
序列名 表名_字段名_seq;
------------------------------------------------------------------------------------------
1.3 如何使用
序列名.nextval 偶尔使用 序列名.currval
举例:
create table testseq(
id number constraint testseq_id_pk primary key,
name varchar2(30)
);
create sequence testseq_id_seq;
insert into testseq values(testseq_id_seq.nextval,'test'||testseq_id_seq.currval);
------------------------------------------------------------------------------------------
1.4 删除序列
drop sequence 序列名;
索引 index (了解)
2.1 作用
加速查询的
3亿
不使用索引 500s
使用索引 0.01s
------------------------------------------------------------------------------------------
2.2 原理
索引 底层使用树状结构 通过消耗大量的空间 和 时间 来达到加速查询的目的。
------------------------------------------------------------------------------------------
2.3 如何建立
create index 索引名 on 表名(字段名);
注意不要在 主键 和 唯一性字段上 加索引 因为系统已经自动建立过索引了。
create table emp222 as select id,first_name name,salary from s_emp;
set timing on;
create index emp222_name_index on emp222(name);
------------------------------------------------------------------------------------------
2.4 删除索引
drop index 索引名;
drop index emp222_name_index;
视图 view
3.1 本质
视图的本质 就是一条sql 语句的select语句。
相对于 视图对应的数据而言 视图本身的空间可以忽略不计。
------------------------------------------------------------------------------------------
3.2 语法
create or replace view 视图名 as select语句;
------------------------------------------------------------------------------------------
3.3 举例
create or replace view myview as select id,first_name name,salary from s_emp;
------------------------------------------------------------------------------------------
3.4 作用
可以对同一份物理数据 作出不同表现 可以用来做权限的管理
可以用来简化子查询
20.Oracle数据库分页技术
分页
rownum 行编号 这个字段叫伪列
------------------------------------------------------------------------------------------
select rownum,first_name,salary from s_emp;
一页显示 11 条数据 显示第一页数据
select rownum,first_name,salary from s_emp where rownum < 12;
一页显示 11 条数据 显示第二页数据
select rownum,first_name,salary from s_emp where rownum < 23 and rownum > 11;
通过子查询 把rownum 变成一个普通的字段
select r,first_name,salary
from (select rownum r,first_name,salary from s_emp
where rownum < 23 ) where r > 11;
一页显示 11 条数据 显示第三页数据
select r,first_name,salary
from (select rownum r,first_name,salary from s_emp where rownum < 3*11+1 )
where r > (3-1)*11;
按照某个字段排序之后 显示 第几页数据
按照salary 排序 显示 first_name salary
select first_name,salary from s_emp order by salary;
按照salary 排序 一页显示11 条数据 显示 第二页数据
select * from(select rownum r,first_name,salary from
(select first_name,salary from s_emp order by salary) where rownum < 2*11+1
)where r > (2-1)*11 ;
最内层:负责排序
中间层:负责编号 和 起别名 以及部分数据的过滤
最外层:负责使用别名进行过滤
按照salary 排序 一页显示11 条数据 显示 第三页数据
select * from(select rownum r,t.* from
(select first_name,salary from s_emp order by salary) t where rownum < 3*11+1
)where r > (3-1)*11 ;
21.PLSQL-过程化
PLSQL(procedure language / struct query language)
PLSQL 过程化sql,就是可以像写java程序一样，去完成数据库的操作
jdbc 使用java程序访问数据库
plsql 简介
plsql 是在标准sql的基础上 增加了 过程化处理语言,它是在客户端操作服务端的一门语言
PLSQL的特点
结构化 模块化编程
良好的可扩展性
良好的可维护性
提高了程序的性能
不便于向异构数据库移植
plsql扩展了sql那些内容
变量和数据类型
控制语句 --- 分支语句 循环语句 goto
过程和函数
对象和方法
plsql的程序结构
declare
/* 申明区
用来定义变量 和 类型的 /
begin
/ 执行区
用来执行plsql语句 和 sql语句的
*/
exception
-- 异常处理区
-- 当程序执行出错自动进入这个区处理
end;
/
第一个plsql程序
begin
dbms_output.put_line('hello plsql!');
end;
/
执行 plsql程序
命令行下的 sqlplus
图形界面工具 plsqldeveloper sqldeveloper
无论使用 哪种开发工具 必须设置 set serveroutput on;
标识符
给变量 类型 过程 函数 游标等命名的
变量
9.1 语法
declare
变量名 类型;
begin
类型:
基本类型 number varchar2(n) char(n) date boolean binary_integer
复合类型 record table cursor
参考类型 ref
大数据类型 BLOB CLOB FILE 一般使用，使用字符串存储路径即可
------------------------------------------------------------------------------------------
9.2 举例
定义两个变量 分别是 数字类型 和 字符串类型
declare
var_id number;
var_name varchar2(30);
begin
dbms_output.put_line(var_id||':'var_name);
end;
/
变量没有赋值 则值一定是null
------------------------------------------------------------------------------------------
9.3 变量的赋值
declare
var_id number:=123;
var_name varchar2(30):='zhangsan';
begin
dbms_output.put_line(var_id||':'||var_name);
end;
/
------------------------------------------------------------------------------------------
9.4 定义两个变量 分别 和 s_emp 表中的id 和first_name对应的类型保持一致
然后分别给两个变量赋值成 1 testcarmen 最后打印这两个变量饿值
declare
id number(7);
first_name varchar2(25);
begin
id:=1;
first_name:='testcarmen';
dbms_output.put_line(id||':'||first_name);
end;
/
------------------------------------------------------------------------------------------
9.5 可以使用 表名.字段名 % type 来获取表中字段对应的类型
declare
var_id s_emp.id%type;
first_name s_emp.first_name%type;
begin
var_id:=2;
/* 使用sql 语句 给变量 first_name 赋值 */
select first_name into first_name from s_emp where id=var_id;
end;
/
------------------------------------------------------------------------------------------
9.6 变量的修饰
constant 代表修饰成常量
not null 修饰成非空
declare
var_id constant s_emp.id%type:=1;
first_name s_emp.first_name%type not null:='abc';
begin
-- var_id:=1;
dbms_output.put_line(var_id||':'||first_name);
end;
/
22.PLSQL 变量
变量
1.1 使用plsql 定义两个变量 分别和 s_emp 表中的 id 和 first_name 的类型保持一致,并且给 和 id 类型相同 的变量赋值成 10，
使用 sql 语句给 first_name类型相同的那个变量赋值 并 输出这个变量的值。
declare
/* 获取变量名 表名.字段名%type; */
var_id s_emp.id%type;
var_name s_emp.first_name%type;
begin
var_id:=10;
select first_name into var_name from s_emp where id=var_id;
dbms_output.put_line(var_name);
end;
/
set serveroutput on;
1.2 如果想把 id = 10 的员工的 first_name salary dept_id 这三个数据查询出来
declare
/* 获取变量名 表名.字段名%type */
var_name s_emp.first_name%type;
var_salary s_emp.salary%type;
var_dept_id s_emp.dept_id%type;
begin
select first_name,salary,dept_id into var_name,var_salary,var_dept_id from s_emp where id=10;
dbms_output.put_line(var_name||':'||var_salary||':'||var_dept_id);
end;
/
1.3使用 recod 类型,可以组织多个变量
recode 类型的语法 ----- 定义一个recode 类型
declare
type 类型名 is record(
字段名 类型,
字段名 类型,
字段名 类型
);
变量名 类型名;
begin
declare
type emptype is record(
var_name s_emp.first_name%type,
var_salary s_emp.salary%type,
var_dept_id s_emp.dept_id%type
);
/* 使用上面的emptype 定义一个变量 */
var_emp smptype;
begin
select first_name,salary,dept_id into var_emp
from s_emp where id=10;
dbms_output.put_line(var_emp.var_name||':'||var_emp.var_salary||':'||var_emp.var_dept_id);
end;
/
把s_dept 表中的所有字段 包装成一个record 类型,然后使用sql 把 id = 50 的信息存入这个变量中 打印这个变量的信息
declare
TYPE depttype IS RECORD(
id s_dept.id%type,
name s_dept.name%type,
region_id s_dept.region_id%type
);
var_dept depttype;
begin
select * into var_dept from s_dept where id=50;
dbms_output.put_line(var_dept.id||':'||var_dept.name);
end;
/
1.4 如何把 s_epm 表中所有的字段 包装成一个record类型 然后 把id=25 所有的员工信息放入这个类型对应的变量中,打印 其中的 id first_name salary
表名%rowtype 实际上获取的是 字段名 和 字段顺序 完全和表头相同的一个记录类型
declare
var_emp s_emp%rowtype;
begin
select * into var_emp from s_emp where id=25;
dbms_output.put_line(var_emp.id||':'||var_emp.first_name||':'||var_emp.salary);
end;
/
1.5 table类型
类似于java数组 或者Map<Integer,Object>
1.5.1 语法
TYPE 类型名 is table of 元素类型 index by binary_integer;
变量名 类型名;
变量名(下标) 下标任意
变量名(下标):=值;
1.5.2 定一一个装 数字的 table类型 使用这个类型定义一个变量 用来存放 9 5 2 7 0 这个五个数字，最后显示下标为 1 的数据。
declare
type numstyle is table of number index by binary_integer;
var_nums numstype;
begin
var_nums(-1):=9;
var_nums(0):=5;
var_nums(1):=2;
var_nums(2):=7;
var_nums(3):=0;
dbms_output.put_line(var_nums(1));
end;
/
1.5.3 下标连续时 如何遍历 table 类型的变量
declare
type numstype is table of number index by binary_integer;
var_nums numstype;
var_index binary_integer:=-1;
begin
var_nums(-1):=9;
var_nums(0):=5;
var_nums(1):=2;
var_nums(2):=7;
var_nums(3):=0;
dbms_output.put_line(var_nums(var_index));
var_index:=var_index+1;
dbms_output.put_line(var_nums(var_index));
var_index:=var_index+1;
dbms_output.put_line(var_nums(var_index));
var_index:=var_index+1;
dbms_output.put_line(var_nums(var_index));
var_index:=var_index+1;
dbms_output.put_line(var_nums(var_index));
end;
/
1.5.4 下标不连续时 如何遍历?
迭代器的核心思想
可以根据上一个元素的信息 获取到 下一个元素相关的信息
变量名.first() 获取最小的下标
变量名.next(下标) 根据一个元素的下标,可以获取下一个元素的下标
变量名.last() 获取最大的下标
变量名.count() 获取元素个数
declare
type numstyle is table of number index by binary_integer;
var_nums numstyle;
var_index binary_interger:=-1;
begin
var_nums(-1):=9;
var_nums(0):=5;
var_nums(11):=2;
var_nums(2):=7;
var_nums(3):=0;
var_index:=var_nums.first();
dbms_output.put_line(var_nums(var_index));
var_index:=var_nums.next(var_index);
dbms_output.put_line(var_nums(var_index));
var_index:=var_nums.next(var_index);
dbms_output.put_line(var_nums(var_index));
var_index:=var_nums.next(var_index);
dbms_output.put_line(var_nums(var_index));
var_index:=var_nums.next(var_index);
dbms_output.put_line(var_nums(var_index));
dbms_output.put_line(var_nums.count());
end;
/
1.6、变量的作用域 和 可见性
全局不能访问 局部 局部可以访问全局
declare
var_m number:=1;
begin
var_n number:=100;
end;
/
如果需要全局访问局部可以使用标记
<<abc>>
declare
var_m number:=1;
begin
declare
var_m number:=100;
begin
dbms_output.put_line(var_m);
dbms_output.put_line(abc.var_m);
end;
end;
/
23.PLSQL 控制语句
控制语句
1.1 分支语句
1.1.1 语法
------------------------------------------------------------------------------------------
if a > b then
end if;
------------------------------------------------------------------------------------------
if a > b then
else
end if;
------------------------------------------------------------------------------------------
if a > b then
elsif a > c then
elsif a > c then
elsif a > d then
end if;
------------------------------------------------------------------------------------------
if a > b then
elsif a > c then
else
end if;
------------------------------------------------------------------------------------------
1.1.2 练习
定义两个整数变量并赋值,然后打印两个变量的最大值
declare
var_x number:=20;
var_y number:=30;
begin
if var_x < var_y then
dbms_output.put_line(var_y);
else
dbms_output.put_line(var_x);
end if;
end;
/
1.1.3 练习
定义三个整数变量，并赋值，然后打印三个变量的最大值
declare
var_x number:=20;
var_y number:=30;
var_z number:=40;
begin
if var_x < var_y then
if var_y < var_z then
else
end if;
else
if var_x < var_z then
else
end if;
end if;
end;
/
------------------------------------------------------------------------------------------
declare
var_x number:=20;
var_y number:=30;
var_z number:=40;
var_max number;
begin
var_max:=var_z;
if var_max < var_x then
var_max:=var_x;
end if;
if var_max < var_y then
var_max:=var_y;
end if;
dbms_output.put_line(var_max);
end;
/
1.1.4 null 值的判断
declare
var_x number;
var_y number;
begin
if var_x < var_y then
dbms_output.put_line('var_x < var_y');
elsif var_x > var_y then
dbms_output.put_line('var_x > var_y');
elsif var_x = var_y then
dbms_output.put_line('var_x = var_y');
elsif var_x is null and var_y is null then
dbms_output.put_line('var_x is null and var_y is null');
else
dbms_output.put_line('other');
end if;
end;
/
------------------------------------------------------------------------------------------
declare
var_x number;
var_y number;
begin
if var_x < var_y then
dbms_output.put_line('var_x < var_y');
end if;
if var_x > var_y then
dbms_output.put_line('var_x > var_y');
end if;
if var_x = var_y then
dbms_output.put_line('var_x = var_y');
end if;
if var_x is null and var_y is null then
dbms_output.put_line('var_x is null and var_y is null');
end if;
end;
/
1.2 循环语句
1.2.1 简单循环
loop
---循环的代码
end loop;
1.2.2 如何结束一个循环 循环中出现下面的语句之一可结束循环
exit when 结束循环的条件;
------------------------------------------------------------------------------------------
if 结束循环的条件 then
exit;
end if;
1.2.3 使用简单循环 把一个变量的值 从1 输出到 10
declare
var_i number:=1;
begin
loop
dbms_output.put_line(var_i);
exit when var_i=10;
var_i:=var_i+1;
end loop;
end;
/
------------------------------------------------------------------------------------------
declare
var_i number:=1;
begin
loop
dbms_output.put_line(var_i);
if var_i=10 then
dbms_output.put_line('game over');
exit;
end if;
var_i:=var_i+1;
end loop;
end;
/
1.2.4 while 循环
while 循环条件 loop
--- 循环语句
end loop;
------------------------------------------------------------------------------------------
把一个变量的值 从 1 输出 到 10
declare
var_i number:=1;
begin
while var_i <= 10 loop
dbms_output.put_line(var_i);
var_i:=var_i+1;
end loop;
end;
/
------------------------------------------------------------------------------------------
把一个变量的值 从 10 输出 到 1
declare
var_i number:=10;
begin
while var_i > 0 loop
dbms_output.put_line(var_i);
var_i:=var_i-1;
end loop;
end;
1.2.5 for 循环
for 变量 in 区间 loop
end loop;
闭区间的表达是 a..b
注意： 不用对变量 进行加操作 这个变量其实只能读 不能写
------------------------------------------------------------------------------------------
举例：
begin
for var_i in 1..10 loop
dbms_output.put_line(var_i);
end loop;
end/
------------------------------------------------------------------------------------------
反向输出 在in 后面加上reverse即可
begin
for var_i in reverse 1..10 loop
dbms_output.put_line(var_i);
end loop;
end;
/
------------------------------------------------------------------------------------------
for循环中自定义的变量只能读不嫩写
begin
for var_i in reverse 1..10 loop
dbms_output.put_line(var_i);
-- var_i:=100;
exit when var_i=5;
end loop;
end;
/
1.2.6 循环的嵌套
declare
var_x number;
var_y number;
begin
var_x:=1;
while var_x < 4 loop
var_y:=y;
while var_y < 4 loop
dbms_output.put_line(var_y);
var_y:=var_y+1;
end loop;
var_x:=var_x+1;
end loop;
end;
/
因为 exit 退出的是当前循环 就是内层循环 所以 输出三次 1 2
------------------------------------------------------------------------------------------
declare
var_x number;
var_y number;
begin
var_x := 1;
while var_x < 4 loop
var_y:=1;
while var_y < 4 loop
dbms_output.put_line(var_y);
if var_y = 2 then
exit;
end if;
var_y:=var_y+1;
end loop;
var_x:=var_x+1;
end loop;
end;
/
------------------------------------------------------------------------------------------
declare
var_x number;
var_y number;
begin
var_x := 1;
while var_x < 4 loop
var_y:=1;
while var_y < 4 loop
dbms_output.put_line(var_y);
if var_y = 2 then
var_x:=4;
exit;
end if;
var_y:=var_y+1;
end loop;
dbms_output.put_line('sha che .....');
var_x:=var_x+1;
end loop;
end;
/
------------------------------------------------------------------------------------------
declare
var_x number;
var_y number;
begin
var_x := 1;
<<outerloop>>
while var_x < 4 loop
var_y:=1;
while var_y < 4 loop
dbms_output.put_line(var_y);
if var_y = 2 then
exit outerloop;
end if;
var_y:=var_y+1;
end loop;
dbms_output.put_line('sha che .....');
var_x:=var_x+1;
end loop;
end;
/
1.3 控制语句 ------ goto 语句 (了解)
1.3.1 语法
goto 标签名;
------------------------------------------------------------------------------------------
1.3.2 练习
declare
var_x number;
begin
var_x:=-1;
<<myloop>>
if var_x < 11 then
dbms_output.put_line(var_x);
var_x:var_x+1;
goto myloop;
end if;
end;
/
1.3.3 练习 使用goto结束外层循环
begin
<<outerloop>>
for var_x in 1..3 loop
for var_y in 1..3 loop
dbms_output.put_line(var_y);
if var_y = 2 then
exit outerloop;
end if;
end loop;
end loop;
end;
/
------------------------------------------------------------------------------------------
使用goto 结合标签 结束多重循环 必须放在代码块上，如果下面没有代码块补上null
begin
for var_x in 1..3 loop
for var_y in 1..3 loop
dbms_output.put_line(var_y);
if var_y = 2 then
goto outerloop;
end if;
end loop;
end loop;
<<outerloop>>
dbms_output.put_line('game over');
end;
/
------------------------------------------------------------------------------------------
begin
for var_x in 1..3 loop
for var_y in 1..3 loop
dbms_output.put_line(var_y);
if var_y = 2 then
goto outerloop;
end if;
end loop;
end loop;
<<outerloop>>
null;
end;
/
24.PLSQL 游标
游标 cursor
1.1 作用
用来存放多条数据的一个结果集
1.2 游标的使用步骤
使用步骤
定义游标
declare
cursor 游标名 is select语句；
begin
打开游标
open 游标名;
提取数据 操作数据
fetch 游标名 into 变量
关闭游标
close 游标名；
1.3 举例
查询 s_emp表中 id first_name salary 放入一个游标中 然后提取游标中前两条数据并进行打印。
declare
cursor empcursor is select id,first_name,salary from s_emp;
type emptype is record(
id s_emp.id%type,
first_name s_emp.first_name%type,
salary s_emp.salary%type
);
var_emp emptype;
begin
oper empcursor;
fetch empcursor into var_emp;
dbms_output.put_line(var_emp.id||':'||var_emp.first_name||':'||var_emp.salary);
fetch empcursor into var_emp;
dbms_output.put_line(var_emp.id||':'||var_emp.first_name||':'||var_emp.salary);
close empcursor;
end;
/
------------------------------------------------------------------------------------------
declare
cursor empcursor is select id,first_name,salary from s_emp;
var_emp empcursor%rowtype;
begin
open empcursor;
fetch empcursor into var_emp;
dbms_output.put_line(var_emp.id||':'||var_emp.first_name||':'||var_emp.salary);
fetch empcursor into var_emp;
dbms_output.put_line(var_emp.id||':'||var_emp.first_name||':'||var_emp.salary);
close empcursor;
end;
/
1.4 如何提取游标中所有的数据
游标的属性
游标名%found 当提取游标数据时 如果提取到了新数据 则这个属性返回true 如果没有提取到
新数据 则这个属性返回false。但这个属性使用有两个前提 第一游标必须处于打开状态 否则返回
非法游标 第二 游标必须fetch 否则这个属性返回 null值。
游标名%notfound 当提取游标数据时 如果提取到了新数据 则这个属性返回false 如果没有提取到
新数据 则这个属性返回true。但这个属性使用有两个前提 第一游标必须处于打开状态 否则返回
非法游标 第二 游标必须fetch 否则这个属性返回 null值。
简单循环 结合notfound属性
declare
cursor empcursor is select id,first_name,salary from s_emp;
var_emp empcursor%rowtype;
begin
open empcursor;
loop
fetch empcurror into var_emp;
/* 提取不到新数据时 结束循环 */
exit when epmcursor%notfound;
dbms_output.put_line(var_emp.id||':'||var_emp.first_name||var_emp.salary);
end loop;
close empcursor;
end;
/
------------------------------------------------------------------------------------------
把上面的游标遍历 改成 使用 while 循环 结合 found 属性遍历
declare
cursor empcursor is select id,first_name,salary from s_emp;
var_emp empcursor%rowtype;
begin
open empcursor;
fetch empcursor into var_emp;
while empcursor%found loop
dbms_output.put_line(var_emp.id||':'||var_emp.first_name||':'||var_emp.salary);
fetch empcursor into var_emp;
end loop;
close empcursor;
end;
/
------------------------------------------------------------------------------------------
for 询函 遍历游标
for循环又叫智能循环 它可以自动定义变量 自动打开游标 自动提取数据 自动关闭游标
declare
cursor empcursor is select id,first_name,salary from s_emp;
begin
for var_emp in empcursor loop
dbms_output.put_line(var_emp.id||':'||var_emp.first_name||':'||var_emp.salary);
end loop;
end;
/
1.5 游标的其它属性
游标名 % isopen 判断游标是否处于打开状态 打开的游标返回 true 关闭的游标返回false.
注意打开的游标不能再打开 关闭的游标不能再关闭。
游标名 % rowcount 游标指针偏移量 (了解)
游标必须打开 否则报错 游标指针默认在第一条数据之前
1.6 带参游标
定义游标时 可以给游标 设计参数
游标的参数 可以在游标对应的查询语句中使用
只要在打开游标时 传入实参即可
注意:plsql 中 参数不能加长度修饰 但是可以使用%type
declare
cursor empcursor(var_id s_emp.id%type) is select id,first_name,salary
from s_emp where id>var_id;
var_emp empcursor%rowtype;
begin
open empcursor(20);
loop
fetch empcursor into var_emp;
/* 提取不到新数据时 结束循环 */
exit when empcursor%notfound;
dbms_output.put_line(var_emp.id||':'||var_emp.first_name||':'||var_emp.salary);
end loop;
close empcursor;
end;
/
------------------------------------------------------------------------------------------
for 循环能否 遍历带参游标
declare
cursor empcursor(var_id s_emp.id%type) is select id,first_name,salary
from s_emp where id>var_id;
begin
for var_emp in empcursor(15) loop
dbms_output.put_line(var_emp.id||':'||var_emp.first_name);
end loop;
end;
/
1.7 把每个员工的 名字 和 对应部门编号 以及 部门的名字 放入一个游标中
然后提取这个游标中的所有数据 打印
declare
cursor empdeptcursor is select first_name,dept_id,name from
s_emp,s_dept where dept_id=s_dept.id;
begin
for var_t in empdeptcursor loop
dbms_output.put_line(var_t.first_name||':'||var_t.dept_id||':'||var_t.name);
end loop;
end;
/
------------------------------------------------------------------------------------------
把每个员工的 员工编号 名字 和 对应部门编号 以及 部门的名字 放入一个游标中 然后提取这个游标中的所有数据 打印
declare
cursor empdeptcursor is select s_emp.id eid, first_name,dept_id,name,s_dept.id did from s_emp,s_dept where dept_id=s_dept.id;
begin
for var_t in empdeptcursor loop
dbms_output.put_line(var_t.eid||':'||var_t.first_name||':'||var_t.dept_id
||':'||var_t.name);
end loop;
end;
/
25.PLSQL中如何使用sql?
1、PLSQL中如何使用sql?
DML(insert delete update) TCL(commit rollback savepoint) 可以直接在plsql中使用
------------------------------------------------------------------------------------------
select 语句要么和 into 结合使用(查询的数据只有一个结果)
如果select语句返回多行数据 则需要和游标结合使用
------------------------------------------------------------------------------------------
DDL(create drop alter) 不能直接在plsql中使用 需要使用动态sql
------------------------------------------------------------------------------------------
begin
create table dsql(id number primary key);
end;
/
2、动态sql
2.1 概念
在程序执行的过程中 sql语句可以根据条件发生改变。
2.2 如何实现?
只要把sql语句变换成字符串即可, 并使用 execute immediate 执行字符串
------------------------------------------------------------------------------------------
declare sqlstr varchar2(200):='create table dsql(id number primary key)';
begin
sqlstr:=replace(sqlstr,')','');
sqlstr:=sqlstr || ' ,name varchar2(30))';
dbms_output.put_line(sqlstr);
execute immediate sqlstr;
end;
/
3、其实DML select语句 等都可以使用 动态sql
3.1 参考游标的概念
select 语句的动态sql ------ 参考游标
参考游标的核心思想 就是 游标对应的sql语句 是一个动态拼接的字符串。
------------------------------------------------------------------------------------------
3.2 参考游标的使用步骤
定义一个参考游标类型
type 参考游标类型名 is ref cursor;
使用参考游标类型 定义游标变量;
游标名 参考游标类型名;
把游标变量 和 拼接的字符串 关联起来；
------------------------------------------------------------------------------------------
3.3 举例
declare
sqlstr varchar2(300);
type myrefcursor is ref cursor;
empcursor myrefcursor;
type emptype is record(
id s_emp.id%type,
name s_emp.first_name%type,
salary emp.salary%type
);
var_emp emptype;
begin
sqlstr:='select id,first_name,salary from s_emp';
sqlstr:=sqlstr || 'where salary > 1500';
sqlstr:=sqlstr || 'and id > 5';
open empcursor for sqlstr;
loop
fetch empcursor into var_emp;
exit when empcursor%notfound;
dbms_output.put_line(var_emp.id||':'||var_emp.name||':'||var_emp.salary);
end loop;
close empcursor;
end;
/
------------------------------------------------------------------------------------------
3.4 参考游标中的占位符 :bn 开头的叫占位符 使用using 可以传入实参
declare
sqlstr varchar2(300);
type myrefcursor is ref cursor;
empcursor myrefcursor;
type emptype is record(
id s_emp.id%type;
name s_emp.first_name%type;
salary s_emp.salary%type;
);
var_emp emptype;
begin
sqlstr:='select id,first_name,salary from s_emp ';
sqlstr:=sqlstr || 'where salary>:b0 ';
sqlstr:=sqlstr||' and id > :b1 ';
open empcursor for sqlstr using 1000,3;
loop
fetch empcursor into var_emp;
exit when empcursor%notfound;
dbms_output.put_line(var_emp.id||':'||var_emp.name||':'||var_emp.salary);
end loop;
close empcursor;
end;
/
26.PLSQL中的异常机制
概念
在程序执行过程 出现的错误
举例
declare
var_name s_emp.first_name%type;
begin
select first_name into var_name from s_emp where id=44;
dbms_output.put_line(var_name);
exception
when others then
dbms_output.put_line('have exception');
end;
/
预定义了 一些常见的操作异常
declare
var_name s_emp.first_name%type;
begin
select first_name into var_name from s_emp where id=4;
dbms_output.put_line(var_name);
select first_name into var_name from s_emp where id>4;
dbms_output.put_line(var_name);
exception
when NO_DATA_FOUND then
dbms_output.put_line('no emp found');
when TOO_MANY_ROWS then
dbms_output.put_line('too many emp');
when others then
dbms_output.put_line('have exception');
end;
/
当写plsql程序 时要分析程序可能会出现哪些异常 然后根据系统预定义的异常
进行相应的处理 程序的最后一般使用 others 作为最后的异常捕获处理。
用户自定义异常
1.定义异常
根据系统中 可能出现问题 使用异常描述出来
2.抛出异常
用户根据 条件 抛出 满足什么样的条件 就抛出什么样的异常
3.捕获异常
4.处理异常
针对不同异常 作出相应的处理
举例
declare
empexception exception;
deptexception exception;
begin
if 1=2 then
raise empexception;
end if;
if 1=1 then
raise deptexception;
end if;
dbms_output.put_line('app continue');
exception
when empexception then
dbms_output.put_line('emp exception');
when deptexception then
dbms_output.put_line('dept exception');
end;
/
27.PLSQL中过程
1、概念
对完成某一个功能的 一组 sql 语句和 plsql语句的一个逻辑整体。
这个逻辑整体有一个名字 对应 数据库中的逻辑。
2、复用
3、定义两个整数变量 选出 两个变量的最大值
declare
var_x number:=20;
var_y number:=30;
begin
if var_x < var_y then
dbms_output.put_line(var_y);
else
dbms_output.put_line(var_x);
end if;
end;
/
------------------------------------------------------------------------------------------
create or replace procedure getmax(var_x number,var_y number)
is
begin
if var_x < var_y then
dbms_output.put_line(var_y);
else
dbms_output.put_line(var_x);
end if;
end;
4、调用
call getmax(1,10);
------------------------------------------------------------------------------------------
execute getmax(200,300);
------------------------------------------------------------------------------------------
通过plsql程序来调用
begin
getmax(101,999);
end;
/
5、写一个存储过程 可以完成两个参数最小值的选取 并使用匿名块调用
create or replace procedure getmin(var_x number:=1,var_y number:=2)
is
begin
if var_x < var_y then
dbms_output.put_line(var_x);
else
dbms_output.put_line(var_y);
end if;
end;
/
call getmin();
execute getmin;
execute getmin();
------------------------------------------------------------------------------------------
当不需要给参数传值时 call 必须使用() execute 可以省略()
如果参数有默认值 则可以不给参数传值 不传值则使用参数的默认值 传值则会替代掉默认值
execute getmin(8899,9988);
查看公司文档 搜索默认值 也可以测试得出默认值 最后还可以查看存储过程源代码
select text from user_source where name='GETMIN';
6、调用别人的存储过程 一般会先查看存储过程
desc 过程名;
desc getmin;
SQL> desc getmin;
------------------------------------------------------------------------------------------
PROCEDURE getmin
Argument Name Type In/Out Default?
------------------------------ ------------ ----------- ------ --------
VAR_X NUMBER IN DEFAULT
VAR_Y NUMBER IN DEFAULT
查看一个存储过程 可以获取
参数的名字
参数的类型
参数的模式
------------------------------------------------------------------------------------------
in 默认的 代表参数只负责给存储过程传入值
out 代表只给存储过程传出值
in out 代表参数即给存储过程传入值 又给存储过程传出值
参数的默认值
public static void getnum(int x){
x=10001;
}
public static void getString(String x){
x=x+"abc"
}
public static void getStringBuf(StringBuffer y){
y.append("cba");
}
static main(String[] args){
String x = new String ("nba");
getString(x);
x
StringBuffer y = new StringBuffer("nba");
getStringBuf(y);
y
}
7、参数的模式
in 只负责给存储过程 传入参数值
create or replace procedure getnum(var_x number )
is
begin
dbms_output.put_line(var_x);
var_x:=10001;
end;
/
通过show errors 可以看到 var_x 只能读不能写 证明它只负责传入 不负责传出
out 参数 只负责给存储过程 传出参数值
设计一个存储过程 有三个整数参数 打印前两个参数的最大值 并且把前两个参数的和存入第三个参数中
create or replace procedure getmax_and_sum(var_x number,var_y number,
var_z out number)
is
begin
if var_x < var_y then
dbms_output.put_line(var_y);
else
dbms_output.put_line(var_x);
end if;
var_z:=var_x+var_y;
end;
/
注意:out 修饰的参数 必须是变量
declare
var_z number:=0;
begin
getmax_and_sum(1,100,var_z);
dbms_output.put_line(var_z);
end;
/
in out 参数 即负责给存储过程传入参数值 又负责传出值
设计一个存储过程 有两个整数参数 打印两个参数的最大值 并且把两个参数的和存入第二个参数中
create or replace procedure getmax_and_sum(var_x number,var_y in out number)
is
begin
if var_x < var_y then
dbms_output.put_line(var_y);
else
dbms_output.put_line(var_x);
end if;
var_y:=var_x+var_y;
end;
/
按照参数的位置赋值
declare
var_t number:=99;
begin
getmax_and_sum(100,var_t);
dbms_output.put_line(var_t);
end;
/
按照参数的名字赋值
declare
var_t number:=99;
begin
getmax_and_sum(var_y=>var_t,var_x=>100);
dbms_output.put_line(var_t);
end;
/
8、设计一个存储过程 第一个参数代表从1 加到的数 第二个参数 用来存储前n项的和 调用这个存储过程 看计算是否正确
create or replace procedure getNumN(var_n number,var_sum out number)
is
begin
var_sum:=(1+var_n)*var_n/2;
end;
/
-----------------------------------------------------------------------------------------
declare
var_s number:=0;
begin
getNumN(100,var_s);
dbms_output.put_line(var_s);
end;
/
28.PLSQL 函数
1、函数 和 过程的区别
第一 关键字不同 函数是Function 过程是 procedure
第二 函数有返回值类型 和 返回值 过程没有
第三 调用方式不同 过程可以直接在plsql中调用 函数必须组成表达式才能调用
2、函数的语法
create or replace function 函数名(参数列表) return 具体的type
is
begin
if 条件 then
return 值;
end if;
return 值;
end;
/
3、设计一个plsql 函数 有两个整数参数 返回两个参数的最大值
create or replace function getmax(var_x number,var_y number) return number
is
begin
if var_x < var_y then
return var_y;
end if;
return var_x;
end;
/
4、如何调用
select getmax(1,100) from dual;
begin
dbms_output.put_line(getmax(200,999));
if 100<getmax(1,999) then
dbms_output.put_line('call function');
end if;
end;
5、要求设计一个plsql 的函数 有两个整数参数 返回两个参数的最大值 并且 把两个参数的和存入第二个参数中。 调用函数 验证功能。
create or replace function getmax_and_sum(
var_x number,var_y in out number)return number
is
begin
if var_x < var_y then
var_y:=var_x+var_y;
return var_y-var_x;
else
var_y:=var_x+var_y;
return var_x;
end if;
end;
/
------------------------------------------------------------------------------------------
create or replace function getmax_and_sum(
var_x number,var_y in out number)return number
is
var_t number;
begin
var_t:=var_y;
var_y:=var_x+var_y;
if var_x < var_t then
return var_t;
else
return var_x;
end if;
end;
/
------------------------------------------------------------------------------------------
declare
var_y number:=1;
var_x number:=99;
var_res number:=0;
begin
var_res:=getmax_and_sum(var_x,var_y);
dbms_output.put_line(var_res);
dbms_output.put_line(var_y);
end;
/
29.包 package
1、系统提供的包
dbms_output 输出包
dbms_output.put_line('hello');
dbms_random 随机数包
select dbms_random.value(1,100) from dual;
select trunc(dbms_random.value(1,100)) from dual;
dbms_job 定时任务包
可以定时调用存储过程
dbms_lob 操作大数据类型的包 比如 blob 和 clob 类型
2、用户可以自定义包
无论是系统的包 还是自定义的包 使用包中的数据记得在数据前加 包名点即可
mypack ----- getmax
mypack.getmax(1,100)
30、PLSQL 触发器
1、DML 级别的触发器
当进行dml(insert delete update)操作时,这些操作可以被数据库管理系统捕获到，捕获到这些行为之后 可以根据操作 进行相应的处理。
2、语法
create or replace trigger 触发器的名字 before|after dml(insert |delete | update)
on 表名 | for each row
declare
begin
end;
/
3、 举例
create table emp300 as select id,first_name name,salary from s_emp;
col name for a20;
为 emp300 这张表建立一个更新操作之后的 触发器
create or replace trigger emp300_after_update after update on emp300
declare
begin
dbms_output.put_line('warning you update emp300');
end;
/
update emp300 set salary=salary+100 where id=1;
update emp300 set salary=salary+100 where id<1;
update emp300 set salary=salary+100 where id>1;
无论sql语句影响多少行数据 上面的触发器 都会被触发一次 这种触发器 叫 语句级触发器。
4、开发中 比较有意义的是 针对每一行 都触发 这样才能获取到数据的具体变化
行级触发器 —- 针对sql语句影响的每一行都触发 for each row
create or replace trigger emp300_after_update after update on emp300 for each row
declare
begin
dbms_output.put_line(:old.id||':'||:old.salary);
dbms_output.put_line('warning you update emp300');
dbms_output.put_line(:new.id||':'||:new.salary);
end;
/
update emp300 set salary=salary+100 where id=1;
update emp300 set salary=salary+100 where id<1;
update emp300 set salary=salary+100 where id>1;
update :old :new
delete :old
insert :new
触发器中不能使用 事务控制语句
create or replace procedure getNumN(var_n number,var_sum out number)
is
begin
var_sum:=0;
for var_i in 1..var_n loop
var_sum:=var_sum+var_i;
end loop;
end;
/
------------------------------------------------------------------------------------------
declare
var_s number:=0;
begin
getNumN(100,var_s);
dbms_output.put_line('var_s='||var_s);
end;
/
constant not null
declare
var_x constant number not null:=0;
begin
