plsql入门

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;