--   第一章节
BEGIN
	dbms_output.put_line('abc');
END;

SELECT * FROM dept;

------------------------------------
DECLARE
	a NUMBER := 20;
	b NUMBER DEFAULT 30;
	c NUMBER;
	PI CONSTANT NUMBER := 3.1415926;
BEGIN
	--a := &请输入第一个值;
	--b := &请输入第二个值;
	c := a + b;
	--PI := 333;
	dbms_output.put_line(c);
END;

-------------------------------------

DECLARE
	c INT;
BEGIN
	SELECT COUNT(*) INTO c FROM emp;
	dbms_output.put_line('总员工数:' || c);
END;	

SELECT * FROM dept;

-----------------------------------------
DECLARE
	dn dept.dname%TYPE; --dn的类型和dept表的dname列的类型一致
	r dept%ROWTYPE;
BEGIN
	dn := 'aaaa';
	dbms_output.put_line(dn);
	
	SELECT * INTO r FROM dept WHERE deptno = 10;
	dbms_output.put_line(r.deptno || r.dname || r.loc);
END;

-----------记录类型--------------

DECLARE
	TYPE dept_rec IS RECORD(
		dname VARCHAR2(20),
		loc VARCHAR2(200)
	);
	
	dr dept_rec;
BEGIN
	dr.dname := 'xxxxx';
	dr.loc := 'xxxxx';
	
	dbms_output.put_line(dr.dname || dr.loc);
	
	SELECT dname, loc INTO dr FROM dept WHERE deptno=20;
	dbms_output.put_line(dr.dname || dr.loc);
END;


--------------数组(更像java中的集合)---------

DECLARE
	TYPE  intArray IS TABLE OF INT; --创建整型数组,索引类型是整数
	ia intArray := intArray(11, 21 , 31 ,14 ,15);
	
	TYPE intArray2 IS TABLE OF INT INDEX BY VARCHAR2(20); --索引类型可以自定义
	ia2 intArray2;
BEGIN
	dbms_output.put_line(ia(1));
	ia.EXTEND(1); --扩展空间,即添加新元素
	ia(6) := 100;
	
	FOR i IN 1..ia.COUNT
	LOOP
		dbms_output.put_line(ia(i));
	END LOOP;
	
	dbms_output.put_line(ia(ia.FIRST));
	
	ia2('aa') := 22;
	--dbms_output.put_line(ia2.FIRST);
END;
	
-----------------------IF结构---------------
--如果平均工资>5000,则输出"白领", 如果[2000,5000],是蓝领,<2000是无领

DECLARE 
	avgSal NUMBER(10, 2);
BEGIN
	SELECT AVG(sal) INTO avgSal FROM emp;
	dbms_output.put_line('平均工资:' || avgSal);
	IF avgSal > 5000 THEN
		dbms_output.put_line('白领');
	ELSIF avgSal BETWEEN 2000 AND 5000 THEN
		dbms_output.put_line('蓝领');
	ELSE
		dbms_output.put_line('无领');
	END IF;
END;


-----------------------CASE结构---------------
--如果平均工资>5000,则输出"白领", 如果[2000,5000],是蓝领,<2000是无领

DECLARE 
	avgSal NUMBER(10, 2);
	r VARCHAR2(20);
BEGIN
	SELECT AVG(sal) INTO avgSal FROM emp;
	dbms_output.put_line('平均工资:' || avgSal);
	
	r := CASE 
			WHEN avgSal > 5000 THEN '白领'
			WHEN avgSal BETWEEN 2000 AND 5000 THEN '蓝领'
			WHEN avgSal < 2000 THEN '无领'
			ELSE 'xxx'
		END;
		
	dbms_output.put_line(r);
END;

SELECT ename, sal, CASE 
	WHEN sal > 5000 THEN '白领'
	WHEN sal BETWEEN 2000 AND 5000 THEN '蓝领'
	ELSE '无领'
	END CASE FROM emp;
	
-----------------------for循环1---------------

DECLARE
	s NUMBER := 0;
	i INTEGER DEFAULT 1;
BEGIN
	LOOP
		s := s + i;
		i := i + 1;
		
		IF i > 100 THEN
			EXIT;
		END IF;
	END LOOP;
	
	dbms_output.put_line(s);
END;


-----------------------for循环2---------------

DECLARE
	s NUMBER := 0;
	i INTEGER DEFAULT 1;
BEGIN
	LOOP
		s := s + i;
		i := i + 1;
		
		EXIT WHEN i > 100;
	END LOOP;
	
	dbms_output.put_line(s);
END;

-----------------------for循环3---------------

DECLARE
	s NUMBER := 0;
	i INTEGER DEFAULT 1;
BEGIN
	WHILE i <= 100
	LOOP
		s := s + i;
		i := i + 1;
	END LOOP;
	
	dbms_output.put_line(s);
END;

-----------------------for循环4---------------

DECLARE
	s NUMBER := 0;
BEGIN
	FOR i IN 1..100
	LOOP
		s := s + i;
	END LOOP;
	
	dbms_output.put_line(s);
END;

--------------------for循环访问结果集-----------

BEGIN
	FOR v IN (SELECT * FROM dept)
	LOOP
		dbms_output.put_line(v.deptno || v.dname || v.loc);
	END LOOP;
END;

------------------九九乘法表---------------


BEGIN
	FOR r IN 1..9
	LOOP
		FOR c IN 1..r
		LOOP
			dbms_output.put(c || '*' || r || '=' || (c * r) || ' ');
		END LOOP;
		dbms_output.put_line('');
	END LOOP;
END;



----------------空语句-----------

BEGIN
	IF 3 = 3 THEN
		NULL;
	END IF;
END;

-------------动态sQL---------------
DECLARE 
	sql_str VARCHAR2(2000);
	TYPE dept_type IS RECORD(
		did NUMBER,
		dname VARCHAR2(20),
		dloc VARCHAR2(200)
	);
	
	dt dept_type;
	
	dno NUMBER;

BEGIN
	dno := &请输入部门编号;
	sql_str :=