Pl/SQL

学习目标

1、认识plsql

2、变量、常量

3、匿名块

4、分支语句

5、循环

6、事务

学习内容

1、plsql

sql：结构化的查询语言：

insert、update、delete、select

表链接查询：

内连接：返回多表中都匹配（==）的数据

外连接：左（所有数据）、右（所有数据）

笛卡尔乘积：

plsql:过程化的SQL语言；扩充了：变量、常量、分支语句、循环、数据结构；匿名块的形式写命令

2、变量、常量

匿名块

declare -- 声明 变量 begin -- 开始 exception -- 可以有 处理异常 end; --结束

变量:

declare -- 声明 变量 -- 变量名 数据类型 v_sal number(10,2); v_name varchar2(30); begin -- 开始 select sal into v_sal from emp where ename='333'; -- 默认的服务器输出命令 -- || 字符串拼接夫 类似于java中的+ dbms_output.put_line('二郎的工资是:'||v_sal); exception -- 可以有 处理异常 -- no_data_found ：预定义好的一个异常类型 when no_data_found then dbms_output.put_line('没有找到合适的数据'); end; --结束

常量:

-- 常量 declare --constant pi constant number(10,6):=3.1415926; r number(3):=20; area number(10,3); --面积 begin -- pi:=5.14; 常量不允许赋值 area:=pi*r*r; dbms_output.put_line('面积是:'||area); end;

3、数据类型：

数字：

number(38),number(总位数,小数的位数)

时间：

date datetime

字符串：

char:固定长度的字符类型 ：char(N) :学号、身份证号、手机号；查询效率高 varchar2：可变长度的字符类型 varchar2(N)：合理利用存储空间；名字、地址、.... 2000

大类型：

BLOB:大的二进制类型 CLOB:大的文本类型

引用类型：

列引用类型：

列名%type declare v_name emp.ename%type; --跟某列的数据类型一致 v_sal emp.sal%type; begin select ename,sal into v_name,v_sal from emp where empno=66; dbms_output.put_line('名字:'||v_name||'工资是:'||v_sal); end;

行引用类型：

表名%rowtype declare v_emp emp%rowtype; --行引用类型 begin select * into v_emp from emp where empno=66; dbms_output.put_line(v_emp.EMPNO||v_emp.ename); end;

记录类型：

-- 存储：编号、部门名称、员工的名字、工资 declare --声明结构类型 type emp_record is record( dno dept.deptno%type, dname dept.dname%type, ename emp.ename%type, sal emp.sal%type ); -- 声明结构类型的变量 只能存放一行数据 v_emp emp_record; begin select dt.deptno,dt.dname,e.ename,e.sal into v_emp from dept dt,emp e where dt.deptno=e.deptno and e.empno=66; dbms_output.put_line(v_emp.dno||v_emp.ename); end;

4、分支语句

if分支语句：

if 条件 then ... end if; if 条件 then ... else ... end if; if 条件 then elsif 条件 then elsif 条件 then elsif 条件 then else end if; declare v_sal emp.sal%type; begin select sal into v_sal from emp where empno=8000; if v_sal>=10000 then dbms_output.put_line('西藏'); elsif v_sal>=6000 and v_sal<10000 then dbms_output.put_line('黄浦江边.....'); else dbms_output.put_line('白开水就着馒头'); end if; end;

case…end结构：

select deptno, case deptno when 3 then '科技部' when 4 then '信息部' end 部门 from dept; select deptno, case when deptno=3 then '科技部' when deptno=4 then '信息部' end 部门 from dept;

5、循环

loop…end loop;

declare v_i number(10):=1; begin loop --循环 dbms_output.put_line(v_i); v_i:=v_i+1; exit when v_i>10; --退出条件 end loop; --结束循环 end;

while循环

declare v_i number(10):=1; begin while v_i<=10 loop --循环 dbms_output.put_line(v_i); v_i:=v_i+1; end loop; --结束循环 end;

for循环

begin --reverse :反转 for vi in reverse 1..10 loop dbms_output.put_line(vi); end loop; end;

6、事务

事务？：transaction

确保一系列数据操作作为整体执行的一种机制。要么都执行，要么都不执行（回滚）；

四大特性：

原子性（A)：事务作为整体执行，要么执行，要么不执行。

一致性©：事务完成前 后 ，数据要保持一致状态。

隔离性(I)：多个事务在并发执行时，事务之间是相互隔离的。

持久性(D)：数据更新到数据库中对数据库的影响是永久的。

不同的隔离级别引发的一些问题：

脏读： A事务正在操作数据，尚未提交 ，B事务读取到了A事务中正在操作的数据。

不可重复读：A事务正在读取数据，B事务修改了A事务中的部分数据，A事务再次读取的数据不一致。只需要锁住部分数据就可以。

幻读：A事务正在读取数据，B事务插入了部分数据，A事务再次读取的数据不一致。需要锁住整张表。

设置事务的隔离级别：

1、读未提交（read uncommit) : 引发：脏读、不可重复读、幻读

2、读已提交(read committed)：（oralce默认的隔离级别） 引发：不可重复读、幻读

3、重复读(repeatable read) (mysql的默认隔离级别) 引发：幻读

4、串行化(serializable)

oracle数据库中，默认写的insert,update,delete命令都属于事务的一部分，需要显示的提交或者回滚。

三个命令：

commit :提交 rollback :回滚 savepoint :设置保存点 begin update emp set sal=sal+1000 where empno=66; update emp set sal=0 where empno=88; commit; --提交 rollback; --回滚 end; begin update emp set sal=sal+2000 where empno=66; savepoint a; update emp set sal=sal+2000 where empno=88; savepoint b; --commit; --提交 rollback to a; --回滚 commit; end;

jdbc中如何控制事务：

public static void main(String[] args) { Connection connection=null; PreparedStatement statement=null; try { Class.forName("oracle.jdbc.OracleDriver"); connection= DriverManager.getConnection("","scott","tiger"); // 关掉自动提交事务 connection.setAutoCommit(false); // 第一条命令 statement=connection.prepareStatement ("insert ...."); statement.setInt(); statement.setInt(); statement.setInt(); statement.setInt(); statement.executeUpdate(); // 第二条命令 statement=connection.prepareStatement("insert ...."); statement.setInt(); statement.setInt(); statement.setInt(); statement.setInt(); statement.executeUpdate(); // 提交事务 connection.commit(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { try { // 回滚事务 connection.rollback(); } catch (SQLException ex) { ex.printStackTrace(); } e.printStackTrace(); } }

总结

1、plsql是什么？

2、变量如何声明？

declare 变量名 数据类型:=值;

3、数据类型：

%type %rowtype

4、示例熟练

tea_year 认证博客专家 大司徒 产品总监 微软MVP！ORACLE认证高级工程师！主要研究方向为大数据、人工智能、JAVA、.Net、数据库 、前端开发、产品研发，曾经服务过中铝、中烟等大型上市国企IT部门，软件企业联合创始人，喜欢软件研发管理、技术营销！