通用SQL和PL/SQL(Oracle)用法

--通用SQL:	
--1.CASE..WHEN --行转列
	SELECT 
		MAX(CASE WHEN deptId = 10 THEN prn ELSE 0 END) '10',
		MAX(CASE WHEN deptId = 20 THEN prn ELSE 0 END) '20',
		MAX(CASE WHEN deptId = 30 THEN prn ELSE 0 END) '30',
		SUM(prn) '总人数'
	FROM
		(SELECT deptId, COUNT(perNum) prn FROM personTable GROUP BY deptId)
	
--2.递归函数（树结构）
	--2.1.查询7369上级领导：
		SELECT area_id,area_name,LEVEL,SYS_CONNECT_BY_PATH(area_name,'-') 
		FROM emp e 
		START WITH area_id = 7369 --从area_id=7369开始
		CONNECT BY area_id = PRIOR p_area_id; --(p_area_id是area_id上级区域编号)
	--2.2.查询7566下属：
		SELECT area_id,area_name,LEVEL,SYS_CONNECT_BY_PATH(area_name,'-') 
		FROM emp e 
		START WITH area_id = 7566 
		CONNECT BY PRIOR area_id = p_area_id; --(p_area_id是area_id上级区域编号)
			
--PL/SQL:
--1.基本语法
	DECLARE --声明变量赋值
		v_date DATE := sysdate;
    BEGIN
	    dbms_output.put_line(V_DATE);
    END;
    
--2.常用变量
--dbms_output输出包
--dbms_random随机包
--dbms_job定时任务包
--%ROWTYPE声明的对象和表一样
--%FOUND取出游标成功，也就是FETCH语句成功，该属性为 true，否则为false
--%NOTFOUND取出游标失败，也就是FETCH 语句失败，则该属性是 true，否则为 false
--%ISOPEN游标是开着的，则返回 true ，否则返回false
--%TYPE声明的变量和表字段变量属性一样
	DECLARE
		v_record DICT_NATION%ROWTYPE;
	BEGIN
		SELECT * INTO v_record FROM DICT_NATION dn WHERE dn.NATION_CODE = 1;
		dbms_output.put_line(v_record.nation_code);
		dbms_output.put_line(v_record.nation_name);
	END;
		
--3.异常处理
	DECLARE
		v_status DICT_NATION.status%TYPE;
		v_dict NUMBER:=80;
	BEGIN
		SELECT status INTO v_status FROM DICT_NATION WHERE NATION_CODE < v_dict;
		IF v_status < 2 THEN
		UPDATE DICT_NATION SET status = 0 WHERE NATION_CODE = v_dict;
		COMMIT;
		END IF;
	EXCEPTION
		WHEN no_data_found THEN dbms_output.put_line('未找到该信息');
		WHEN too_many_rows THEN dbms_output.put_line('返回多条数据，请使用游标');
		WHEN OTHERS THEN dbms_output.put_line('其他异常');
	END;
		
--4.循环结构
	--4.1.LOOP循环
		DECLARE
			v_counter NUMBER:=57;
		BEGIN
			LOOP
				INSERT INTO DICT_NATION (NATION_CODE,NATION_NAME,PY_CODE,STATUS) VALUES (V_COUNTER, '汉奸', 'HJ','1');
				V_COUNTER:=V_COUNTER +1;
				EXIT WHEN V_COUNTER  > 60;
			END LOOP;
			COMMIT;
		END;
		
	--4.2.WHILE LOOP循环
		DECLARE
			v_counter NUMBER:=61;
		BEGIN
			WHILE V_COUNTER  < 65 LOOP
				INSERT INTO DICT_NATION (NATION_CODE,NATION_NAME,PY_CODE,STATUS) VALUES (V_COUNTER, '汉奸', 'HJ','1');
				V_COUNTER:=V_COUNTER +1;
			END LOOP;
			COMMIT;
		END;
		
	--4.3.FOR..IN..LOOP循环
		DECLARE
			v_counter NUMBER;
		BEGIN
			FOR v_counter IN 66..70 LOOP 
				INSERT INTO DICT_NATION (NATION_CODE,NATION_NAME,PY_CODE,STATUS) VALUES (V_COUNTER, '汉奸', 'HJ','1');
			END LOOP;
			COMMIT;
		END;
		
--5.游标
	DECLARE  
		v_code DICT_NATION.NATION_CODE%TYPE;
		v_name DICT_NATION.NATION_NAME%TYPE;
		--声明游标并赋值
		CURSOR c_nation IS SELECT MIN(NATION_CODE), NATION_NAME FROM DICT_NATION GROUP BY NATION_NAME HAVING COUNT(NATION_CODE) >1;
	BEGIN
		OPEN c_nation;--开启游标
			LOOP--循环
				FETCH c_nation INTO v_code,v_name;--遍历游标赋值给声明变量
				EXIT WHEN c_nation%notfound;--判断循环退出条件
				DELETE FROM DICT_NATION WHERE NATION_NAME = v_name AND NATION_CODE > v_code;--执行循环满足条件的语句
			END LOOP;--循环结束
		CLOSE c_nation;--关闭游标
	END;
		
--6.存储过程
	--存储过程的定义
	CREATE PROCEDURE mp(n1 IN OUT NUMBER, n2 IN OUT NUMBER)--IN OUT及作为输入也做为输出参数
	AS 
		temp NUMBER;
	BEGIN
		temp := n1;
		n1 := n2;
		n2 := temp;
	END;
	--存储过程的执行
	DECLARE 
		a1 NUMBER := 2;
		a2 NUMBER := 5;
	BEGIN
		mp(a1, a2);--执行方法
		dbms_output.put_line(a1);
		dbms_output.put_line(a2);
	END;
		
--7.分析函数	
	--7.1.分析函数聚合函数+OVER(PARTITION BY(PARTITION BY不写默认所有数据聚合) ..)
		SELECT 
			deptId, deptName, userName, sal, 
			SUM(sal) OVER () sum_sal,--总工资
			SUM(sal) OVER (PARTITION BY (deptId)) sum_dept_sal,--部门总工资
			SUM(sal) OVER (PARTITION BY (deptId) ORDER BY sal) leiji_sum_dept_sal--部门累计总工资
		FROM user_table ORDER BY deptId
		
		--分析函数和聚合函数的不同
			--1.普通聚合函数用group by分组，每个分组返回一个统计值
			--2.分析函数有partition by分组，每组每行都可返回一个统计值
			
	--7.2.分析函数之等级函数
		SELECT 
			deptId, deptName, userName,
			RANK() OVER(ORDER BY sal DESC) rank1,--排序12245
			DENSE_RANK() OVER(ORDER BY sal DESC) densk_rank1,--排序12234
			ROW_NUMBER() OVER(ORDER BY sal DESC) row_rank2--排序12345
		FROM user_table