Oracle学习-------PL/SQL数据块，游标，存储过程，分析函数

1. PL/SQL数据块

语法格式：

----基础语法格式
DECLARE 
  ---变量声明部分
  BEGIN
    /*程序
    执行
    部分*/
  EXCEPTION
      /*异常处理部分*/
  END;

1.1 数据类型

1.2 变量的声明与赋值

  DECLARE
      ---定义语法： 变量名 变量类型  [:= 赋初值]
      v_name VARCHAR2(10);    ---定义不赋值
      v_age NUMBER := 1;  ---定义并且赋值
      v_birth DATE;
      ----定义一条记录
      TYPE v_subject IS RECORD(
           v_sub_name VARCHAR2(10),
           v_sub_id NUMBER
      );
      ---动态定义语法1: 变量名  某个表的某个字段%type
      v_sex STUDENT.STU_SEX%TYPE; ---动态进行定义类型即 v_sex变量类型会随着STUDENT表的STU_SEX字段的类型保持一致
      ----动态定义语法2： 变量名 某个表%rowtype
      v_student STUDENT%ROWTYPE;  ---v_student 类型为一条记录，每隔字段的类型和student表的类型保持一致
  BEGIN
    
  END;
  
  ---示例
  DECLARE
      v_name VARCHAR2(50) := 'zhang'||'san';
      v_age NUMBER := 12;
      v_sex CHAR;
  BEGIN
    v_sex := 'F';
    DBMS_OUTPUT.put_line('name:'||v_name||' age:'||v_age||'sex:'||v_sex);
  END; 

1.3 逻辑语法基础

1.3.1 if

 IF 条件 THEN
   语句；
 END IF;
 
 ---示例
 DECLARE
     v_num NUMBER := 10;
 BEGIN
   IF v_num = 10 THEN
     DBMS_OUTPUT.put_line('num='||v_num);
   END IF;
 END;

1.3.2 if…else…

IF 条件 THEN
  语句；
ELSE 
  语句；
END IF;

---示例
DECLARE
    v_num NUMBER := 11;
BEGIN
  IF v_num < 10 THEN
    DBMS_OUTPUT.put_line('V_NUM < 10');
  ELSE
    DBMS_OUTPUT.put_line('V_NUM > 10');
  END IF;
END;

1.3.3 if …elsif …

IF 条件 THEN
   逻辑语句；
ELSIF  条件 THEN
  逻辑语句；
ELSIF 条件 THEN
  逻辑语句；
END IF;


--- 示例
DECLARE 
    v_num NUMBER := 15;
BEGIN
  IF v_num < 14 THEN
    DBMS_OUTPUT.put_line('V_NUM < 14');
  ELSIF v_num >14 AND v_num < 20 THEN
    DBMS_OUTPUT.put_line('v_num >14 AND v_num < 20');
  ELSIF v_num>20 THEN
    DBMS_OUTPUT.put_line('v_num >20');
  END IF;
END;

1.4 循环结构

1.4.1 loop

-----1.loop
LOOP
  循环语句；
END LOOP;

---示例  求和1-10
DECLARE
    v_num NUMBER :=1;
    v_result NUMBER :=0;
BEGIN
  LOOP
    EXIT WHEN v_num > 10;    ---满足添加退出循环
    v_result := v_result + v_num;
    v_num := v_num +1;
  END LOOP;
  DBMS_OUTPUT.put_line('result:'|| v_result);
END;

1.4.2 while loop

---2. while loop
WHILE 条件 LOOP
  循环语句；
END LOOP;

---示例 求和1-10
DECLARE
    v_num NUMBER :=1;
    v_result NUMBER :=0;
BEGIN
  WHILE v_num<11 LOOP
    v_result := v_result + v_num;
    v_num := v_num +1;
  END LOOP;
  DBMS_OUTPUT.put_line('result:'|| v_result);
END;

1.4.3 for

---3.  for
FOR 变量 IN 起始位置 .. 结束位置 LOOP
  循环语句；
END LOOP;

----示例 求和1-10
DECLARE 
       V_RESULT NUMBER :=0;
BEGIN
  FOR FLAG IN 1..10 LOOP
    V_RESULT:=V_RESULT+FLAG;
  END LOOP;
  DBMS_OUTPUT.put_line('result:'|| v_result);
END;

2. 游标

---创建游标
CURSOR 游标名称 IS select查询;
---说明
----1：游标必须在PL/SQL块的声明部分进行定义；
----2：游标定义时可以引用PL/SQL变量，但变量必须在游标定义之前定义；
----3：定义游标时并没有生成数据，只是将定义信息保存到数据字典中；
----4：游标定义后，可以使用cursor_name%ROWTYPE定义游标类型变量。
---打开游标
OPEN 游标名;
---获取游标的一条数据
FETCH 游标名 INTO 变量名;
---获取游标的累计数据
游标名%ROWCOUNT  返回游标的累计数据量
---判断游标是否还有下一条
游标名%NOTFOUND  判断是否还有下一条数据，没有下一条数据返回为true
----关闭游标
CLOSE 游标；


---示例 取出学生表中所有学生的姓名和年龄
DECLARE
    CURSOR C_STU IS SELECT * FROM STUDENT;    ---定义游标
    V_STU C_STU%ROWTYPE;                      ---游标数据接受参数
BEGIN
  
  OPEN C_STU;                                 ---打开游标，游标的所有操作必须先打开游标
  DBMS_OUTPUT.put_line(C_STU%ROWCOUNT);       ----查看游标的当前指针
  LOOP                                        ---循环获取游标数据
    FETCH C_STU INTO V_STU;                   ---获取游标的一条数据
    DBMS_OUTPUT.put_line(V_STU.STU_NAME||V_STU.STU_AGE);
    EXIT WHEN C_STU%NOTFOUND;                 ---判断是否还有下一条数据
  END LOOP;
   DBMS_OUTPUT.put_line(C_STU%ROWCOUNT);
  CLOSE C_STU;                                ----关闭游标
END;

3. 存储过程

----存储过程
CREATE [OR REPLACE] PROCEDURE 存储过程名(参数 参数类别 参数类型，.......)
AS 
       /**
       变量声明部分
       **/
BEGIN
  /**逻辑语句**/
  EXCEPTION
    /** 异常处理部分**/
END;
=========================================
----1.传入参数 IN
---示例
CREATE PROCEDURE PRE_IN(STUNAME IN VARCHAR2)
AS
       V_AGE NUMBER;
BEGIN 
  SELECT STU_AGE INTO V_AGE FROM STUDENT WHERE STU_NAME = STUNAME;
  DBMS_OUTPUT.put_line('AGE:'||V_AGE);
  
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.put_line('没有查询到数据');
END;
=========================================
----存储过程调用方式
-----1.在命令窗口
execute PRE_IN('zhangsan');
-----2. plsql 调用
DECLARE

BEGIN
  PRE_IN('ZHANGSAN');
END;
=========================================
-----返回值OUT
---示例
CREATE PROCEDURE PRE_IN_OUT(STUNAME IN VARCHAR2,            ---传入的参数
                        RE_AGE OUT NUMBER)              ---返回值
AS
      
BEGIN 
  SELECT STU_AGE INTO RE_AGE FROM STUDENT WHERE STU_NAME = STUNAME;
  DBMS_OUTPUT.put_line('AGE:'||RE_AGE);
  
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.put_line('没有查询到数据');
END;
=========================================
---调用
DECLARE
      v_age NUMBER;
BEGIN
  PRE_IN_OUT('ZHANGSAN',v_age);   ---注意函数调用的方式
  DBMS_OUTPUT.put_line(v_age);
END;
 =========================================
 ----即做参数又作为返回值
 ---示例，交换a b的值
CREATE PROCEDURE PRE_INOUT(A IN OUT NUMBER,B IN OUT NUMBER)
AS
       
BEGIN
  A := A+B;
  B := A-B;
  A := A-B;
END;

---调用
DECLARE
  A NUMBER:=1;
  B NUMBER:=2;
BEGIN
  PRE_INOUT(A,B);
  DBMS_OUTPUT.put_line('A:'||A);
  DBMS_OUTPUT.put_line('B:'||B);
END;           

4. 分析函数

---分析函数
SELECT 
  STUDENT.STU_NAME,
  STUDENT.STU_SEX,
  STUDENT.STU_AGE,
  AVG(STU_AGE)OVER(PARTITION BY STU_SEX)  --对stu_sex进行分组，求出每组的平均年龄，加在每条数据后
FROM
  STUDENT

---分析函数
SELECT 
  STUDENT.STU_NAME,
  STUDENT.STU_SEX,
  STUDENT.STU_AGE,
  AVG(STU_AGE)OVER()  ---over函数没有参数时，表示对整个结果集进行操作
FROM
  STUDENT;

SELECT 
  STUDENT.STU_NAME,
  STUDENT.STU_SEX,
  STUDENT.STU_AGE,
  SUM(STU_AGE)OVER(PARTITION BY STU_SEX ORDER BY STU_AGE) ---如果存在order by 操作，就求每一组内的累计数据
FROM
  STUDENT;