-
为什么学习oracle
1)oracle比mysql早出10多年
2)oracle收费(众多的数据库特性,能满足企业的多样性需求,一套完整的数据解决方案)
3)oracle性能强悍
4)事物:oracle很早就完全支持事物,而mysql在innodb存储引擎的行级锁的情况下才支持事物
5)oracle有很严格的权限使用,mysql的权限控制比较弱 -
PL/SQL
pl/sql 是一种程序语言,叫做过程化SQL语言(Procedural Language/SQL),是Oracle数据库对SQL语句的扩展 -
PL/SQL语法
基本外壳
declare
--声明变量
str1 varchar2(20):='hellow';
str2 varchar2(20):='world';
begin
--打印输出 `||` 代表+号 字符串拼接
dbms_output.put_line(str1||' '||str2);
end;
for循环和条件判断
declare
begin
for i in 1..9 loop
if mod(i,2)=0 then
dbms_output.put_line(i||' 是偶数');
elsif mod(i,2)!0 then
dbms_output.put_line(i||' 是奇数');
if end;
end loop;
end;
- pl/sql中动态sql
declare
sqlStr varchar2(100);
begin
sqlStr:='insert into user(id,name) values('||id||','||name||')';
execute immediate sqlStr;
end;
- 游标
oracle游标通过cursor关键字来定义一组oracle查询出来的数据集,简而言之:游标就是查询出来的数据集
declare
--定义游标
cursor yb is select * from user;
--定义记录变量
jj yb%rowtype;
begin
--打开游标
open yb;
--将游标中的记录存放到jj变量中
loop fetch yb into jj;
--判断是否还有记录 没有时退出
exit when yb%notfound;
dbms_output.put_line('id:' || jj.id|| ',name:' || jj.name);
end loop;
--关闭游标
close yb;
end;
通常我们使用游标来循环数据集,使用loop控制结果集来搭配使用,加上游标的属性变量%notfound来获取游标的结束,跳出loop循环
-
游标的属性
我们利用游标的属性来获取游标所处的状态,然后对应做相应的处理
1)%NOTFOUND 表示游标获取数据的时候是否有数据提取出来,没有数据返回TRUE,有数据返回false
2)%ISOPEN 用来判断游标是否打开
3)%ROWCOUNT表示游标 FETCH INTO获取了多少行记录值
4)%TYPE表示表中的一列
5)%ROWTYPE表示表中的一行 -
存储过程
存储过程(Stored Procedure)一组为了完成特定功能的SQL语句集,编译后存储在数据库中,用户通过制定存储过程的名字并给出参数来执行
--创建一个显示学生总人数的存储过程
--create procedure <名>(<参数名> in/out <参数类型>,<参数名> in/out <参数类型>... ) as
create or replace procedure pro_zhy(restr out number) as
shuLiang number;
begin
select count(*) into shuLiang from student;
restr:=shuLiang;
end;
PL/SQL调用
declare
v_restr number;
begin
pro_zhy(v_restr);
dbms_output.put_line(v_restr);
end;
java语言jdbc调用
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("oracle.jdbc.OracleDriver");
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.245.132:1521:PPMSDB","ppmsdb","ppmsdb");
//通过prepareCall方法回调执行oracle的存储过程
CallableStatement call = connection.prepareCall("{pro_zhy(?)}");
call.registerOutParameter(1, Types.INTEGER);
call.execute();
System.out.println(call.getInt(1));
call.close();
connection.close();
}
- 函数
当oracle自带的函数不能满足需求时,就可以安装语法规则封装一段pl/sql语句
函数可以看做一类特殊的存储过程 没有输出参数 必有唯一返回值
--实现根据学生编号,查找该学生是否在student中存在
create or replace function fun_zhy(sid in varchar2) return varchar2 is
v_result varchar2(100);
stu number;
begin
select count(*) into stu from student where sno=sid;
if stu>0 then
v_result:='true';
else
v_result:='false';
end if;
return(v_result);
end fun_zhy;
pl/sql调用
s varchar2(100);
begin
s:=fun_zhy('s012');
dbms_output.put_line(s);
end;
java语言jdbc调用
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//加载驱动
Class.forName("oracle.jdbc.OracleDriver");
//创建连接jdbc:oracle:thin:@192.168.219.130:1521:PPMSDB", "ppmsdb", "ppmsdb"
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.245.132:1521:PPMSDB","ppmsdb","ppmsdb");
//通过prepareCall方法回调执行oracle的自定义函数
CallableStatement call = connection.prepareCall("{?=call fun_zhy( ?) }");
call.registerOutParameter(1, Types.VARCHAR);
call.setString(2, "s012");
call.execute();
System.out.println(call.getString(1));
call.close();
connection.close();
}
-
存储过程和函数的区别
存储过程多进多出或者不出 函数多进1出
存储过程 使用的场景:增删改查
函数 使用的场景:计算变量 -
触发器
触发器(trigger)是数据库保证完成完整性的一种方法,它是于表事件相关的特殊的存储过程,它的执行不有程序调用,而是由事件来触发,比如对表( insert,delete, update)时,触发器用于加强数据的完整性约束和业务规则等
触发时间:before和after动作之前或动作之后
触发事件:
insert:数据库插入会触发此触发器;
update:数据库修改会触发此触发器;
delete:数据库删除会触发此触发器
create [or replace] tigger 触发器名 触发时间 触发事件 on 表名
begin
pl/sql语句
end
--员工薪资上涨的时候只能是原来基础上的20%--50%
CREATE OR REPLACE TRIGGER salary_trigger
BEFOREUPDATE OR DELETE OR INSERT ON salary
FOR EACH ROW
BEGIN
if :new.money>:old.money*0.5+:old.money or :new.money<:old.money*0.2+:old.money then
RAISE_APPLICATION_ERROR(-20008,'员工薪资幅度错误');
end if;
END;