PLSQL
什么是PL/SQL?
PL/SQL(Procedure Language/SQL)
PLSQL是Oracle对sql语言的过程化扩展
指在SQL命令语言中增加了过程处理语句(如分支、循环等),使SQL语言具有过程处理能力。(具有编程的能力)
**为什么要学习plsql?
1.将sql逻辑写在db层,效率更高—-数据库处理数据更专业,还不需要网络数据交换。
2.为存储过程、函数等打下基础,前提是学会plsql**
--面向过程的语言
--declare --声明部分:没有变量,则declare可以省略
--你不需要变量声明,则不需要写任何东西
BEGIN--程序体的开始:编写语句逻辑
--在控制台输出一句话:dbms_output相当于system.out类,内置程序包,put_line:相当于println()方法
dbms_output.put_line('Hello World');
--dbms_output.put('Hello World');
end;--程序体的结束
概念:程序包:dbms_output相当于java中的类(system.out),它是oracle自带的,内置.
调用程序包:dbms_output.put_line(‘Hello World!’)相当于java的方法
plsql 程序结构
PL/SQL可以分为三个部分:声明部分、可执行部分、异常处理部分。
[delare]
声明部分(变量、游标、例外)
begin
逻辑执行部分(DML语句、赋值、循环、条件等)
[exception]
异常处理部分(when 预定义异常错误 then)
end;
/
最简单的PL/SQL:
Begin
Null;
End;
/
注意:在SQLPLUS中,PLSQL执行时,要在最后加上一个 “/”
plsql 变量
声明部分可以定义变量,定义变量的语法:
变量名 [CONSTANT] 数据类型;
普通数据类型(char, varchar2, date, number, boolean, long):
varl char(15);
merried boolean := true;
psal number (7,2)
特殊变量类型(引用型变量、记录型变量):
my_name emp.ename%type my_name 与emp的ename的类型一样。
emp_rec emp%rowtype
**在ORACLE中有两种赋值方式:
1,直接赋值语句 :=
2, 使用select …into … 赋值:(语法;select 值 into 变量)**
打印几个变量的值,几个变量的值分别采用两种不同的赋值方法:
--打印两个变量的值,两个变量的值分别采用两种不同的赋值方法:
DECLARE--声明变量
--姓名
v_name VARCHAR(20) :='Bo';--声明的时候直接赋值
--薪资
v_sal NUMBER;
--工作地点
v_local VARCHAR(200);
BEGIN --开始程序逻辑
--程序运行时赋值
--方法一:--直接赋值
v_sal :=9999;
--方法二:语句赋值
SELECT '上海' INTO v_local FROM dual;
--输出打印
dbms_output.put_line('姓名:'||v_name||',薪资:'||v_sal||',工作地点:'||v_local);
END;--程序结束
plsql中有两种不同的赋值方法:
一种是: 直接用:=来赋值
另一种:select 值 into 变量 from 表名。
**引用类型的好处:
1,使用普通变量定义方式,需要知道表中列类型,而使用引用类型,不需要考虑列的类型
普通变量值过小报错:**
2,使用引用类型,当列中的数据类型发生改变,不需要修改变量的类型。而使用普通方式,当列的类型改变时,需要修改变量的类型
使用%TYPE是非常好的编程风格,因为它使得PL/SQL更加灵活,更加适应于对数据库定义的更新**。
记录型变量
记录型变量,代表一行,可以理解为数组,里面元素是每一字段值。
%rowtype 引用一条(行)记录的类型 例:v_emp emp%rowtype;
含义:v_emp 变量代表emp表中的一行数据的类型,它可以存储emp表中的任意一行数据。
记录型变量分量的引用方式:
v_emp.ename :="admin"
查询并打印7839号(老大)员工的姓名和薪水
--查询并打印7839号(老大)员工的姓名和薪水
DECLARE
--记录型变量
v_emp emp%ROWTYPE;--该变量可以存储emp表中的一行记录
BEGIN
--赋值
--默认情况下,必须是全字段赋值
SELECT * INTO v_emp FROM emp WHERE empno=7839;
--打印
dbms_output.put_line('7839号员工的姓名是:'||v_emp.ename||',薪资'||v_emp.sal);
END;
如果有一个表,有100个字段,那么你程序如果要使用这100字段话,如果你使用引用型变量一个个声明,会特别麻烦,那么你可以考虑记录型变量
if语句
判断emp表中记录是否超过20条,,10-20之间,10以下打印一句
--判断emp表中记录是否超过20条,,10-20之间,10以下打印一句
DECLARE
--用来存储数量
v_count NUMBER;
BEGIN
--查询数量赋值
SELECT COUNT(1) INTO v_count FROM emp ;
--判断
IF v_count>20 THEN
dbms_output.put_line('记录数超过20条:'||v_count);
ELSIF v_count BETWEEN 10 AND 20 THEN
dbms_output.put_line('记录数在10到20条之间:'||v_count);
ELSE
dbms_output.put_line('记录数不足10条:'||v_count);
END IF;
END;
循环
在ORACLE中有三种循环:
Loop 循环 EXIT WHEN...条件 end loop;
While()…loop 条件判断循环
For 变量 in 起始..终止 Loop
这里我建议只记忆一种写法:
记住loop的写法
【示例】
打印数字1-10
--打印数字1-10
DECLARE
--声明一个变量
v_num NUMBER :=1;
BEGIN
--循环并打印
LOOP
EXIT WHEN v_num>10; --退出循环条件
dbms_output.put_line(v_num);
--递增
--v_num++;--不支持
v_num :=v_num+1;
END LOOP;
END;
游标
什么是游标
**游标(Cursor),也称之为光标,从字面意思理解就是游动的光标。
游标是映射在结果集中一行数据上的位置实体。
游标是从表中检索出结果集,并从中每次指向一条记录进行交互的机制。**
游标的主要作用:用于临时存储一个查询返回的多行数据(结果集),通过遍历游标,可以逐行访问处理该结果集的数据。
(显示)游标的使用方式:声明—>打开—>读取—>关闭
语法
游标声明:
CURSOR 游标名 [ (参数名 数据类型[,参数名 数据类型]...)]
IS SELECT 语句;
【示例】
无参游标:
cursor c_emp is select ename from emp;
有参游标:
cursor c_emp(v_deptno emp.deptno%TYPE) is select ename from emp where deptno=v_deptno;
游标的打开:
Open 游标名(参数列表)
【示例】
open c_emp;-- 打开游标执行查询
游标的取值:
fetch 游标名 into 变量列表|记录型变量
【示例】
fetch c_emp into v_ename;--取一行游标的值到变量中,注意:v_ename必须与emp表中的ename列类型一致。(v_ename emp.ename%type;)
游标的关闭:
close 游标名
【示例】
close c_emp;--关闭游标释放资源
解释游标获取数据的基本原理:
游标刚open的时候,指针结果集的第一条记录之前。
游标与结果集的区别是什么?游标是有位置的。
fetch会向前游动,并获取游标的位置的内容。
游标的属性
游标的属性 返回值类型 说明
%ROWCOUNT 整型 获得FETCH语句返回的数据行数
%FOUND 布尔型 最近的FETCH语句返回一行数据则为真,否则为假
%NOTFOUND 布尔型 与%FOUND属性返回值相反,代表游标结束
%ISOPEN 布尔型 游标已经打开时值为真,否则为假
创建和使用
【示例】
使用游标查询emp表中所有员工的姓名和工资,并将其依次打印出来。
【引用型变量获取游标的值】:
--使用游标查询emp表中所有员工的姓名和工资,并将其依次打印出来。
DECLARE
--声明一个游标
CURSOR C_EMP IS
SELECT ENAME, SAL FROM EMP;
--引用型变量
V_ENAME EMP.ENAME%TYPE; --姓名
V_SAL EMP.SAL%TYPE; --工资
BEGIN
--打开游标,执行查询
OPEN C_EMP;
--使用游标,循环取值
LOOP
--获取游标的值放入变量的时候,必须要into前后要对应(数量和类型)
FETCH C_EMP INTO V_ENAME, V_SAL;
EXIT WHEN C_EMP%NOTFOUND;
--输出打印
DBMS_OUTPUT.PUT_LINE('员工的姓名:' || V_ENAME || ',员工的工资' || V_SAL);
END LOOP;
CLOSE c_emp ;--关闭游标,释放资源
END;
【使用记录型变量存值】:
--使用游标查询emp表中所有员工的姓名和工资,并将其依次打印出来。
DECLARE
--声明一个游标
CURSOR C_EMP IS SELECT * FROM EMP;
--记录型变量
v_emp emp%ROWTYPE;
BEGIN
--打开游标,执行查询
OPEN C_EMP;
--使用游标,循环取值
LOOP
--获取游标的值放入变量的时候,必须要into前后要对应(数量和类型)
FETCH C_EMP INTO v_emp;
EXIT WHEN C_EMP%NOTFOUND;
--输出打印
DBMS_OUTPUT.PUT_LINE('员工的姓名:' || v_emp.ename || ',员工的工资' || v_emp.sal);
END LOOP;
CLOSE c_emp ;--关闭游标,释放资源
END;
带参数的游标
【示例】
使用游标查询并打印某部门的员工的姓名和薪资,部门编号为运行时手动输入。
-- Created on 2015/5/5 by BOBO
---查询10号部门的员工的姓名和薪资
declare
--定义游标--带参数的游标:需要定一个形式参数
CURSOR c_emp(v_deptno emp.deptno%TYPE) IS SELECT ename,sal FROM emp WHERE deptno=v_deptno ;
--声明变量
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
--用
--打开游标
OPEN c_emp(10);
--循环fetch
LOOP
--取出数据
FETCH c_emp INTO v_ename,v_sal;
--退出条件
EXIT WHEN c_emp%NOTFOUND;
--打印--写任何的逻辑
dbms_output.put_line('姓名:'||v_ename||',薪资:'||v_sal);
END LOOP;
--关闭
CLOSE c_emp;
end;
--使用游标查询并打印某部门的员工的姓名和薪资,部门编号为运行时手动输入。
DECLARE
--声明一个带参数的游标
CURSOR C_EMP(v_deptno emp.deptno%TYPE) IS SELECT * FROM EMP WHERE deptno=v_deptno;
--记录型变量
v_emp emp%ROWTYPE;
BEGIN
--打开游标,执行查询
--打开游标的时候需要传入参数
OPEN C_EMP(20);
--使用游标,循环取值
LOOP
--获取游标的值放入变量的时候,必须要into前后要对应(数量和类型)
FETCH C_EMP INTO v_emp;
EXIT WHEN C_EMP%NOTFOUND;
--输出打印
DBMS_OUTPUT.PUT_LINE('员工的姓名:' || v_emp.ename || ',员工的工资' || v_emp.sal);
END LOOP;
CLOSE c_emp ;--关闭游标,释放资源
END;