PL/SQL 笔记

游标的类型有两种：隐式游标和显式游标
 
说明：当进行表的数据查询操作, 一般用游标实现,
      查询结果为单行的可以用隐式游标select ... into ...,
	  查询结果为多行的可以用显式游标cursor ...;
   
    ----------------
	1.需要对表结果进行逐行打印（必需一行行的打印）
	2.需要进行数据的分批提交（如：DML语言操作)
	3.需要对表结果进行逐行计算（如：计算每张表对应的行数，需要先获取每一张表名，在分别计算行数）


1.隐式游标（单行值写入变量可以用select ... into ...）
	对变量赋值还可以使用SELECT…INTO 语句从数据库中查询数据对变量进行赋值。
	但是查询的结果只能是一行记录,不能是零行或者多行记录。


	例题：打印出emp中员工编号为7369的姓名和工资。 
	DECLARE
	  V_ENAME VARCHAR2(10);
	  V_SAL NUMBER(7,2);
	BEGIN
	  SELECT ENAME,SAL
		INTO V_ENAME,V_SAL          -- 隐士游标赋值（来源于表的查询结果,查询结果只能是单行）
		FROM EMP WHERE EMPNO=7369;
	  DBMS_OUTPUT.PUT_LINE('员工名称：'||V_ENAME||' 员工工资：'||V_SAL);    -- 不能直接打印表,只能是单行字段拼接后的字符串形式打印
	END;
	
	注意：使用select…into语句对变量赋值,要求查询的结果必须是一行,不能是多行或者没有记录。
	      不能直接打印表，可以通过将表查询的结果赋值给变量，然后打印
	
		   
				   

2.引用数据类型
	%TYPE：   引用表中的某列的数据类型或某个变量的数据类型（单值变量，只能存储单值）。
	%ROWTYPE：引用表中的一行（所有字段）作为数据类型（表变量,也只能一行一行的存储）。
	
	例题：打印出emp中员工编号为7369的姓名和工资。
	-- %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=7369;
	  dbms_output.put_line('姓名:' || v_name ||' 工资: ' ||v_sal);
	END;


	-- %ROWTYPE 引用表的所有字段类型
	DECLARE
	  v_emp emp%ROWTYPE;         -- 引用整个表的字段类型（表变量）
	BEGIN
	  SELECT *
		INTO v_emp               -- 插入整行数据（所有字段数据）
		FROM emp 
	   WHERE empno=7369;
	  dbms_output.put_line('姓名:' || v_emp.ename ||' 工资: ' ||v_emp.sal);  -- 不能直接打印整个表,只能是单个值的拼接
	END;


	DECLARE
	  v_emp emp%ROWTYPE;           -- 引用整个表的字段类型（表变量）
	BEGIN
	  SELECT ename, sal
		INTO v_emp.ename,v_emp.sal -- 只插入两个字段数据
		FROM emp 
	   WHERE empno=7369;
	  dbms_output.put_line('姓名:' || v_emp.ename ||' 工资: ' ||v_emp.sal);  -- 不能直接打印整个表,只能是单个值的拼接
	END;

	

    练习：打印员工7499的工作职位和部门名称
	
	
	



3.显示游标（多行值写入变量用显示游标）
	游标的类型有两种：隐式游标和显示游标。
	PL/SQL会为所有的SQL数据操作声明一个隐式的游标,包括只返回一条记录的查询操作。
	显示游标四个步骤：
	1.声明
	2.打开游标
	3.逐行获取数据
	4.关闭游标
		  
	语法结构：声明游标
	CURSOR 游标名[(参数1 数据类型[,参数2 数据类型...])]
	IS SELECT 语句;  --游标的声明
  
	语法结构：执行游标
	OPEN 游标名[(实际参数1[,实际参数2...])];  --打开游标
	FETCH 游标名 INTO 变量名1[,变量名2...];
	或
	FETCH 游标名 INTO 记录变量;  --提取数据
	CLOSE 游标名;  --关闭游标（千万别忘了！）
  
	游标属性：%FOUND、%NOTFOUND
	%FOUND:
	用于判断游标是否从结果集中提取数据。如果提取到数据,则返回值为TRUE,否则返回值为FALSE。
	%NOTFOUND:
	该属性与%FOUND相反,如果提取到数据则返回值为FALSE；如果没有,则返回值为TRUN。

  
	例题1：查询10号部门所有员工的姓名和工资并打印（PL/SQL）
	-- 显式游标
	DECLARE
	  -- 显式游标记录结果集
	  CURSOR C_EMP IS                   -- 先声明游标（放在declare中）
	  SELECT ENAME,SAL
		FROM EMP
	   WHERE DEPTNO=10;
	  -- 变量可以被赋值打印
	  V_EMP C_EMP%ROWTYPE;              -- 声明变量为游标行类型
	BEGIN
	  -- DBMS_OUTPUT.PUT_LINE(C_EMP);   -- 没法直接打印结果集(表的结果集)
	  OPEN C_EMP;                       -- 打开游标
	  LOOP                              -- 游标为结果集所以要有循环
		FETCH C_EMP INTO V_EMP;
		EXIT WHEN C_EMP%NOTFOUND;       -- 退出循环条件
		-- DBMS_OUTPUT.PUT_LINE(C_EMP); -- 没法直接打印结果集(单行的结果集)
		DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME||' -- '||V_EMP.SAL);   -- 只能从变量中取字段
		-- EXIT WHEN C_EMP%NOTFOUND;        -- 放在最后打印结果会多出最后一行
	  END LOOP;
	  -- 关闭游标
	  CLOSE C_EMP;
	END;
	
	
	--FOR循环
	DECLARE
	  CURSOR C_EMP IS
	  SELECT ENAME,SAL
		FROM EMP
	   WHERE DEPTNO=10; 
	BEGIN
	  FOR V_EMP IN C_EMP LOOP
		DBMS_OUTPUT.PUT_LINE('姓名: ' || V_EMP.ENAME || '工资: ' || V_EMP.SAL);
	  END LOOP;
	END;
	

	
	
	-- 游标参数
	例题2：打印某个部门的员工姓名和工资
	DECLARE
	  CURSOR C_EMP(P_DEPTNO EMP.DEPTNO%TYPE) IS   -- 游标参数
	  SELECT ENAME,SAL
		FROM EMP 
	   WHERE DEPTNO=P_DEPTNO;
	BEGIN
	  FOR V_EMP IN C_EMP(&部门号) LOOP    -- 键盘输入变量值（键盘输入值时,字符型要加单引号）/打开游标的时候传入
	  DBMS_OUTPUT.PUT_LINE('姓名: ' || V_EMP.ENAME || '工资: ' || V_EMP.SAL);
	  END LOOP;
	END;
	
	


	
	

1.程序块中的%ROWCOUNT
	1.1 SQL%ROWCOUNT 用于记录修改的条数,必须放在一个增删改等修改类语句后面执行,select语句用于查询的话无法使用,
	    当你执行多条修改语句时,SQL%ROWCOUNT 之前执行的最后一条语句修改数为准。

		BEGIN
		  DELETE FROM emp;
		    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);   -- 记录删除数据的条数
		  INSERT INTO emp(empno) VALUES (7777);
		    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);   -- 记录插入数据的条数
		  UPDATE emp SET comm=1000 WHERE empno=7369;
		    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);   -- 记录更新数据的条数
		END;


	1.2 游标%ROWCOUNT  用于记录游标遍历到第几行。
		
		-- 2000万行数据更新(全部更新完再提交)  -- 长期占用表资源，影响系统
		UPDATE T_2000W SET SAL=SAL+100;
		COMMIT;
		
		-- 2000万行数据分批提交, 每1w行提交一次！
		DECLARE
		  CURSOR C_2000W IS
		    SELECT * FROM T_2000W;
		BEGIN
		  FOR V IN C_2000W LOOP
		    UPDATE T_2000W SET SAL=SAL+100 WHERE ID=V.ID;
			IF MOD(C_2000W%ROWCOUNT,10000)=0 THEN 
			  COMMIT;
			END IF;
		  END LOOP;
		  COMMIT;
		END;
		
	    



1.PL/SQL 中DDL语言

	-- DDL不能直接在PL/SQL块中使用
	BEGIN
	   -- CREATE TABLE EMP_01 AS SELECT * FROM EMP;   
	   -- TRUNCATE TABLE EMP_01;  
	   -- ALTER TABLE EMP_01 ADD CT DATE;
	   DROP TABLE EMP_01;
	END;
	
	
	
	
2.动态SQL	  
	在PL/SQL程序开发中,可以使用DML语句和事务控制语句,但是还有很多语句（比如DDL语句）不能直接在PL/SQL中执行。
	这些语句可以使用动态SQL来实现。
  
	语法格式：动态SQL
	EXECUTE IMMEDIATE 动态语句字符串
	[INTO 变量列表]
	[USING 参数列表]
	
	------------
	BEGIN
	  EXECUTE IMMEDIATE 'CREATE TABLE YYY AS SELECT * FROM EMP';    -- 字符串语句最后不要加分号;
	END;
	或者
	DECLARE
	  V_SQL VARCHAR2(100) :='CREATE TABLE YYY AS SELECT * FROM EMP'  
	BEGIN
	  EXECUTE IMMEDIATE V_SQL;
	END;
	


    例题：查看某个员工的工资并打印。
    -- 隐式游标
    DECLARE
      v_sal emp.sal%TYPE;
    BEGIN
      SELECT sal INTO v_sal FROM emp WHERE empno=&输入员工编号;
      DBMS_OUTPUT.PUT_LINE(v_sal);
    EXCEPTION WHEN OTHERS
      THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
    
    
    -- 动态SQL(含参数和赋值)
    DECLARE
      v_sal emp.sal%TYPE;
    BEGIN
      EXECUTE IMMEDIATE 'SELECT sal FROM emp WHERE empno =: 接收参数' -- =: 接收参数传入
  	  INTO v_sal
  	  USING &输入部门编号;
      DBMS_OUTPUT.PUT_LINE(v_sal);
    EXCEPTION WHEN OTHERS
      THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
	
	
	
	-- 关于 DBMS_OUTPUT.PUT 和 DBMS_OUTPUT.PUT_LINE
	BEGIN
	  DBMS_OUTPUT.PUT('0710');    -- 不换行（后面必须有DBMS_OUTPUT.PUT_LINE,才能打印）
	  DBMS_OUTPUT.PUT_LINE('BI');
	  DBMS_OUTPUT.PUT_LINE('HAHAHA');
	END;



	-- 双重循环
	BEGIN
	  FOR X IN 1..3 LOOP
		FOR Y IN 4..6 LOOP
			DBMS_OUTPUT.PUT_LINE(X||'*'||Y||'='||X*Y); 
		END LOOP;
	  END LOOP;
	END;  
		  


	
	练习1：打印当前用户下面所有的表名称及每张表其对应的总行数
	       EMP   ...... 15
		   DEPT  ......  4
	       ............
		   
		DECLARE
          -- 获取所有的表名称
        CURSOR c_tbname IS
            SELECT table_name FROM user_tables; 
          -- 变量存储表行数值
          V_NUM NUMBER(10);
          -- 存储动态SQL字符串
          V_SQL VARCHAR2(1000);
        BEGIN
          -- 遍历表名称，分别统计每张表的行数
          FOR V IN c_tbname LOOP
            V_SQL := 'SELECT COUNT(1) FROM ' || V.table_name;
            -- DBMS_OUTPUT.PUT_LINE(V_SQL);
            EXECUTE IMMEDIATE V_SQL
            INTO V_NUM;
            DBMS_OUTPUT.PUT_LINE(V.table_name||'-------'||V_NUM);
          END LOOP;
        EXCEPTION WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(SQLERRM);
        END;

	      
	注意：1.SELECT table_name FROM user_tables;   -- 查询当前用户下所有的表名称
	      2.from 后面跟变量, 会把变量当作表名,必须用动态sql 拼接语句
	      3.动态sql中的赋值用 execute immediate 动态sql语句 into 变量;
		  4.在执行动态sql前,可以BMS_OUTPUT.PUT_LINE(动态sql语句); 查看语句是否正确
		  
		  
		  
		  
		  
	练习2.编写PLSQL块,统计每个部门名称及其员工人数和平均工资（将统计结果插入到建立的目标表中,目标表在PLSQL块外建立）
		目标表：4个字段 (dname,emp_num,avg_sal,etl_date)-- etl_date为计算数据的时间,用sysdate就行
		注意：数据量特别大的时候,通过游标插入数据时候,实现每插入1万条数据提交一次;
			  数据量不大的时候,可以直接一次性插入数据到目标表,最后提交。 
		-- 全量数据（清空所有数据支持重跑）
		DELETE FROM 表;  
		-- 增量数据（清空当天数据支持重跑）
		DELETE FROM 表 WHERE TRUNC(etl_date)=TRUNC(SYSDATE);

	
	
SQL  -->  PL/SQL 块  --> 过程体（存储过程/函数/触发器） --> 包

事物的特性：一致性、原子性、隔离性、持久性
https://www.cnblogs.com/dwxt/p/8807981.html

存储过程作用？
https://blog.csdn.net/weixin_39805338/article/details/80798706



创建存储过程
	为什么需要创建存储过程？
	(1)封装代码,执行复杂的逻辑
	(2)先编译后执行,第一次编译成功后,之后调用,其效率要比单独运行sql语句要高
	(3)存储过程是一个完整的事物,整体的代码要么全部成功要么全部失败
	存储过程在工作中的实际应用？
	(1)处理多张表关联的逻辑,并将最终的结果集数据写入到目标表
	(2)同一种数据库,可以做跨库数据同步,增量(MERGE INTO ...)或全量
	语法格式：创建存储过程
	CREATE [OR REPLACE] PROCEDURE 过程名[(参数1 [IN|OUT|IN OUT] 数据类型,参数2 [IN|OUT|IN OUT] 数据类型……)]
	IS|AS
	PL/SQL过程体; -- 直接从BEGIN开始
	
	三种参数：传入(in或省去),传出(out),传入或传出(in out)
	注意: 1.数据类型不要带字符长度也不用括号 varchar2/number/date
		  2.如果存储过程不带参数,过程名后不用括号,IS|AS 后可以直接跟声明内容,不要declare
		  3.为in的参数,不能在过程中被赋值,只能作为调用传入值
		  4.省去参数类型则默认为IN传入
		  5.IN OUT 参数必需在声明时候赋初值,调用中用变量名
		  
	语法格式：调用存储过程
	
	注意：存储过程有多少个参数，调用中就要对应多少个参数（默认参数除外）
	BEGIN
	  过程名[(参数)];
	END;
	
	
	
	-- 1.不带参数		
	例题1：每天全量同步emp表的数据到emp_cp（emp_cp要有etl_tm 同步时间）。
    -- 封装成存储过程，以后每天定时调用
    CREATE OR REPLACE PROCEDURE SP_D_EMP_CP
    IS
		-- 有变量/常量/游标此处声明
		/*
		源表：    emp
		目标表：  emp_cp
		创建时间：20201201
		版本号：v01
		*/
    BEGIN
     -- 清空emp_cp的数据，支持重跑
     EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP_CP';
     -- 全量插入数据到emp_cp
     INSERT INTO EMP_CP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,ETL_TM)
     SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,SYSDATE AS ETL_TM
       FROM EMP;
     DBMS_OUTPUT.PUT_LINE('插入：'||SQL%ROWCOUNT);
     -- 数据提交
     COMMIT;
    EXCEPTION WHEN OTHERS
     THEN DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
    END SP_D_EMP_CP;
   
   
   -- 调用存储过程
   BEGIN
     SP_D_EMP_CP;
   END;
   
   -- 查询
   SELECT * FROM EMP_CP;
   
	
   -- 调用
   -- （1）sql window 的程序块执行
   BEGIN
	 SP_D_EMP_CP; -- SP_D_EMP_CP();
   END;

   -- （2）右键存储过程,test
	
	

    -- 2.只带传入参数
    例题2：每天增量同步emp表的数据到emp_cp（emp_cp要有etl_tm 同步时间）。
    CREATE OR REPLACE PROCEDURE SP_D_EMP_CP_ADD(P_ETL_DT IN VARCHAR2) IS
      V_ETL_DT DATE;
    BEGIN
      V_ETL_DT := TO_DATE(P_ETL_DT, 'YYYY-MM-DD');
      -- 删除增量时间数据,支持重跑
      DELETE FROM EMP_CP WHERE HIREDATE = V_ETL_DT;
      -- 增量插入数据
      INSERT INTO EMP_CP
        (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, ETL_TM)
        SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, V_ETL_DT AS ETL_TM
          FROM EMP
         WHERE HIREDATE = V_ETL_DT; -- 限定入职日期等于传入日期
      -- 提交
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
        ROLLBACK;
    END SP_D_EMP_CP_ADD;
	
	
    -- 调用
    BEGIN
      SP_D_EMP_CP_ADD('19810220');
    END;
    或者
    BEGIN
      SP_D_EMP_CP_ADD('&输入日期字符串');
    END;
  	或者 
  	BEGIN
  	  SP_D_EMP_CP_ADD(P_ETL_DT => '19810220');  -- 参数定向调用（多个参数可以改变顺序）
  	END;
     -- 右键存储过程,test
	
   

	-- 3.带传入和传出参数
	例题3：传入一个员工号,传出该员工的姓名和工资。
    CREATE OR REPLACE PROCEDURE sp_in_out_empno(p_empno IN emp.empno%TYPE,
                                                p_ename OUT emp.ename%TYPE,
                                                p_sal   OUT emp.sal%TYPE)
    IS 
    BEGIN
      SELECT ename,sal 
        INTO p_ename,p_sal  -- out 变量可以被赋值
        FROM emp 
       WHERE empno=p_empno; -- in 变量只能传入值
      EXCEPTION
        	WHEN OTHERS THEN
    	  DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
    
    -- 调用
    DECLARE
      v_ename emp.ename%TYPE;
      v_sal   emp.sal%TYPE;
    BEGIN
      sp_in_out_empno(&输入员工号,v_ename,v_sal);
      dbms_output.put_line('员工姓名：'||v_ename||' 员工工资：'||v_sal);
    END sp_in_out_empno;
  
  

	-- 4.参数中含IN OUT 类型
    例题4：传入一个员工号,传出该员工的姓名和工资。
    CREATE OR REPLACE PROCEDURE sp_inout_empno(p_empno_sal IN OUT NUMBER,
   											   p_ename OUT emp.ename%TYPE)
    IS 
    BEGIN
   	SELECT ename,sal 
   	  INTO p_ename,p_empno_sal -- 作为out 参数被赋值
   	  FROM emp 
   	 WHERE empno=p_empno_sal;  -- 第一次作为in 参数传值
   	EXCEPTION
   		WHEN OTHERS THEN
   	  DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END sp_inout_empno;
    
    -- 调用
    DECLARE
   	v_empno_sal NUMBER(10):=7369;  -- in out 类型必须声明给初值
   	v_ename emp.ename%TYPE;
    BEGIN
   	sp_inout_empno(v_empno_sal,v_ename);
   	dbms_output.put_line('员工姓名：'||v_ename||' 员工工资：'||v_empno_sal);
    END;
  
	
	
	
	-- 5.注意参数给默认值情况
	CREATE OR REPLACE PROCEDURE SP_EMP5(T_DEPTNO EMP.DEPTNO%TYPE DEFAULT 10,
										T_JOB    EMP.JOB%TYPE)

	 IS
	  T_EMPNO EMP.EMPNO%TYPE;
	  T_SAL   EMP.SAL%TYPE;
	BEGIN
	  SELECT EMPNO, SAL
		INTO T_EMPNO, T_SAL
		FROM EMP
	   WHERE DEPTNO = T_DEPTNO
		 AND JOB = T_JOB;
	  DBMS_OUTPUT.PUT_LINE('员工编号: ' || T_EMPNO || ' 工资: ' || T_SAL);
      EXCEPTION
          WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM);
	END;
	
	
	调用1
    BEGIN
	  SP_EMP5(20,'MANAGER');
	END;
	调用2
	BEGIN
	  SP_EMP5(T_JOB=>'MANAGER');
	END;
	调用3
	BEGIN
	  SP_EMP5(T_JOB=>'MANAGER',T_DEPTNO=>30);
	END;
	  
	  
	  
	
	注意：存储过程中的out参数,可以在过程体中被赋值, 外部调用该存储过程后获得out变量的值;
	      存储过程中的变量, 可以在过程体中被赋值, 外部调用该存储过程并不能得到其内部变量的值.
	  
	  
	  
	练习：1.编写存储过程,传入部门号和工作类型,返回员工编号和对应工资。
	      2.编写存储过程,传入一个日期字符串,返回小于等于该日期的入职员工数和程序运行代码(成功则返回'0',否则返回SQLERRM错误代码)