学习笔记22/1/19与22/1/20

学习笔记

一、游标

（1）不带参数的游标

游标声明格式：

DECLARE
  CURSOR 游标名   
  IS
  SELECT 语句;---游标所指向的那个查询的结果集
BEGIN
  -- 打开游标 逻辑体(游标的使用/执行 放在这一块)
END;

游标自动使用格式：
eg：将 EMP 表的员工工号、姓名一起打印出来；

DECLARE 
  CURSOR C_EMP IS 
  SELECT EMPNO
        ,ENAME
  FROM EMP ;

BEGIN 
  FOR I IN C_EMP LOOP  -- 调用游标格式
     DBMS_OUTPUT.put_line(I.EMPNO||'  '||I.ENAME);
  END LOOP;
END;

（2）带参数的游标

声明格式：

DECLARE
  CURSOR 游标名 (参数1 数据类型 , 参数2 数据类型 .. .. )  
  IS
  SELECT 语句;---游标所指向的那个查询的结果集
BEGIN
  -- 打开游标 逻辑体(游标的使用/执行 放在这一块)
END;

eg：传入一个部门编号，将这个部门的员工的名字打印出来；

DECLARE 
  CURSOR C_EMP(P_DEPTNO NUMBER ) IS
  SELECT T.ENAME
  FROM EMP T 
  WHERE T.DEPTNO = P_DEPTNO ;
  
  V_DEPTNO NUMBER;
  V_JOB VARCHAR2(30);
BEGIN 
  V_DEPTNO:= &传入你要打印的那个部门编号;  

  FOR I IN C_EMP(V_DEPTNO) LOOP 
       DBMS_OUTPUT.put_line(I.ENAME);
  END LOOP;
END;

eg：按业务要求更新 EMP_0118 的薪资
传入一个部门编号，更新这个部门里的员工的薪资
1.如果员工的基本工资低于1500，就给这个员工加 1000 的基本工资
2.如果这个员工的基本工资高于1500，并且奖金为空的或者等于0，就给这个员工加100块钱的奖金

DECLARE 
  CURSOR C_EMP(P_DEPTNO NUMBER) IS
  SELECT T.EMPNO
        ,T.SAL
        ,T.COMM
  FROM EMP_0118 T 
  WHERE T.DEPTNO = P_DEPTNO;
  
  V_DEPTNO NUMBER;
BEGIN 
  V_DEPTNO := 10;
  
  FOR I IN C_EMP(V_DEPTNO) LOOP
     IF I.SAL <1500 THEN 
        UPDATE EMP_0118 T SET T.SAL = T.SAL + 1000
        WHERE T.EMPNO  = I.EMPNO;
     ELSIF I.SAL >= 1500 AND NVL(I.COMM,0) = 0 THEN 
        UPDATE EMP_0118 T SET T.COMM = 100
        WHERE T.EMPNO  = I.EMPNO;
     END IF;
  END LOOP;
  
  COMMIT;

END;

需要注意：

1. 参数的数据类型不要带长度，只需要定义类型即可 ；
2. 定义了参数，就必须要传参数进去；
3. 定义的参数是什么数据类型，传参的时候就必须传该数据类型的值。

参数和变量区别：

1. 参数声明的时候不能加数据类型的【长度】 ，而变量需要指定长度；
2. 变量 声明以后，可以不赋值 或者 赋值以后 也可以不用，但是参数
   只要声明了，必须得用。

本人对于cursor这会的一些看法，cursor这会在我看来就像一个可以存储各种类型的表的一个指针一样，指向我们所需要的表，有点函数的感觉，但又不一样，后面学了存储过程、函数和视图后到时候回头来总结一下四者的区别吧！

二、字符拼接与动态SQL

（1）字符拼接

这部分的就是掌握两个东西：
- || 连接符
- 各种转义符

首先要知道我们需要的是什么，其实就是字符串和那些变量都可以用连接符来连接，之前在学习简单的查询语句的时候也有学着使用 || 连接百分号来着，就只要知道它是连接符就好了；

然后就是说转义符的事，首先，我们知道在oracle中单引号引起来的部分是一个字符类型的数据，但是在之前刚刚开始学习单引号的时候我就想过怎么用单引号代表一个单纯的字符串，就是输出一个字符串，这会其实已经有两种方法；
1.转义符
首先我们要知道转义符是什么意思，顾名思义，就是讲该符号后面的特殊符号含有的特殊含义给它转换了，让它能作为一个普通字符输出，如果对于这方面有兴趣可以多看看编译原理；

转义符有两种

（2）动态SQL

1.用动态SQL执行简单的DDL语句：
eg：执行建表语句；

BEGIN 
  --CREATE TABLE EMP_0120 AS SELECT * FROM EMP;
  EXECUTE IMMEDIATE 'CREATE TABLE EMP_0120 AS SELECT * FROM EMP';
END;

eg：执行删除语句；

DECLARE 
 V_JOB VARCHAR2(20);
 V_SQL VARCHAR2(4000);
BEGIN 
  V_JOB := &INPUT_JOB;
  --DELETE FROM EMP_0103 T WHERE T.JOB = V_JOB;
   -----单引号是特殊字符，在字符串里表示一个单引号，需要用两个单引号转义来表示一个单引号
  --chr(39) 也是表示一个单引号
  --V_SQL := 'DELETE FROM EMP_0103 T WHERE T.JOB ='||'''' ||V_JOB ||'''';
  V_SQL := 'DELETE FROM EMP_0120 T WHERE T.JOB =' ||CHR(39)||V_JOB||CHR(39);
  
  DBMS_OUTPUT.put_line(V_SQL);
  EXECUTE IMMEDIATE V_SQL;
  --COMMIT;
END;

2.带参数的SQL语句

• 如果动态语句是SELECT语句，可以把查询的结果保存到INTO后面的变量中。
  如果动态语句中存在参数，USING为语句中的参数传值。
  动态SQL中的参数格式是 :参数名 ，参数在运行时需要使用 USING传值。

eg：根据传入的empno和deptno找出对应员工的姓名与工资；

• 程序块里直接用select语句

DECLARE 
  V_SQL VARCHAR2(4000);
  V_ENAME VARCHAR2(40);
  V_SAL NUMBER;
  V_EMPNO NUMBER;
  V_DEPTNO NUMBER;
BEGIN 
  V_EMPNO :=&INPUT_EMPNO; 
  V_DEPTNO := &INPUT_DEPTNO;
  
 SELECT T.ENAME
          ,T.SAL
  INTO V_ENAME
       ,V_SAL
  FROM EMP T 
  WHERE T.EMPNO = V_EMPNO
    AND T.DEPTNO = V_DEPTNO;
  DBMS_OUTPUT.put_line(V_ENAME ||' '|| V_SAL);
END;

• 使用带参数的动态SQL语句

DECLARE 
  V_SQL VARCHAR2(4000);
  V_ENAME VARCHAR2(40);
  V_SAL NUMBER;
  V_EMPNO NUMBER;
  V_DEPTNO NUMBER;
BEGIN 
  V_EMPNO :=&INPUT_EMPNO; 
  V_DEPTNO := &INPUT_DEPTNO;
  
    V_SQL := 'SELECT T.ENAME
                  ,T.SAL
             FROM EMP T 
           WHERE T.EMPNO = :P_EMPNO 
           AND T.DEPTNO = :P_DEPTNO'; ---- 用 :参数名称  
           
  EXECUTE IMMEDIATE  V_SQL
   INTO V_ENAME  ---动态SQL查出来的数据要给到变量
       ,V_SAL
   USING V_EMPNO,V_DEPTNO; ---参数顺序要注意
   --将变量 V_EMPNO 传递给动态 SQL 的参数P_EMPNO
   
 DBMS_OUTPUT.put_line(V_ENAME ||' '|| V_SAL);  
   
END; 

eg：传入一个工号用带参数动态SQL的方法 或者用 字符串拼接的不带参数的 将这个工号 的员工姓名和工资查出来，将姓名和工资打印在输出窗口上;

• 带参数SQL

DECLARE 
    V_SQL VARCHAR(3000);
    V_ENO NUMBER;
    V_EN VARCHAR2(20);
    V_SAL NUMBER;

BEGIN
    V_ENO := &INPUT_EMPNO;
    V_SQL := 'SELECT ENAME
                     ,SAL
              FROM EMP
              WHERE EMPNO = : P_EMPNO
              ';
              
    EXECUTE IMMEDIATE V_SQL
                 INTO V_EN
                      ,V_SAL
                 USING V_ENO;
    COMMIT;
     
    DBMS_OUTPUT.put_line(V_EN||'  '||V_SAL);

END;

• 拼接不带参数

DECLARE 
    V_SQL VARCHAR2(3000);
    V_ENO NUMBER := &INPUT_EMPNO;
    V_EN VARCHAR2(10);
    V_SAL NUMBER;  

BEGIN
    V_SQL := '
    SELECT ENAME
           ,SAL
    FROM EMP
    WHERE EMPNO = '||V_ENO;
    

    EXECUTE IMMEDIATE V_SQL
                 INTO V_EN
                      ,V_SAL;
    
    DBMS_OUTPUT.put_line(V_EN||'  '||V_SAL);

END;

三、递归

今天整太晚了，后面随缘给写补充了