PL/SQL基础、变量及游标

一、PL/SQL基础及变量
1、PL/SQL是一种语言，相当于把sql语句与java语言的结合运用。
2、注意写SQL语句时，它的前面最好不要有注释的语句。
3、PL/SQL由声明、执行体、异常处理三部分组成。如：
	SQL>DECLARE	/*声明*/
	 v_job varchar2(20);
	BEGIN	/*执行体*/
	 SELECT job INTO v_job
	 FROM emp
	 WHERE ename='SCOTT';
	 dbms_output.put_line('SCOTT'||' job is '||v_job);	/*输出语句，PL/SQL中是用||作为连接符*/
	EXCEPTION	/*异常处理*/
	 WHEN NO_DATA_FOUND THEN	/*数据不存在时的异常*/
	 dbms_output.put_line('没有这个员工！');
	 WHEN TOO_MANY_ROWS THEN	/*数据过多时的异常*/
	 dbms_output.put_line('有多个员工叫SCOTT！');
	END;
4、输出操作结果语句为："dbms_output.put_line('a'||'b')"等同于System.out.println("a"+"b"),只是在使用此语句之前要先执行"set serveroutput on"。
5、PL/SQL语句和SQL语句是分别进行解析和执行的，PL/SQL块被数据库内部的PL/SQL引擎提取，将SQL语句取出送给oracle的SQl引擎处理。
6、PL/SQL语法：语句可以写在多行；语句中的关键字、字段名称等，用空格分隔；每条语句必须以分号结束，END关键字后面出需要分号；在PL/SQL程序中出现的字符值和日期值
必须用单引号括起来。
7、标识符需要遵循相应的命名规定：第一个字符必须以字母开始；若使用保留字需用双引号括起来；名称最多包含30个字符；不要与数据库的表或者列名称相同。
8、注释有两种方式：/*...*/和--作为前缀。
9、varchar2(最大长度值)用来定义变长字符串，必须要指定最大长度值；char用来定义定长字符串，不指定长度时默认为1；number(总位数,小数点位数)可以表示整数和浮点数；
date类型长度固定为7个字节；timestamp类型用于定义日期和时间的，只是会显示上下午的数据；long用来定义变长字符串，long raw用来可变长的二进制数据，这两种类型不常
用，可用clob/blob替代；binary_float和binary_double这两种类型需要加后缀，用来定义浮点数；以下类型只能在PL/SQL中用，不能定义表中列的数据类型：boolean布尔类型有有效值为true/false/null，此类型的值不能输出；binary_integer用来定义整数；
10、PL/SQL中是通过:=来表示赋值，变量以v开始，常量以c开始，每行只能声明一个标识符。
11、用%TYPE定义简单变量更灵活，如：
v_name emp.ename%TYPE;
v_balance NUMBER(7,2);
v_min_balance v_balance%TYPE:=10;
其中变量v_name的数据类型与emp表的ename列相同并保持同步,emp.ename的类型改变则v_name的也会变。
12、复合数据类型有PL/SQL TABLES(表类型)、PL/SQL RECORDS(记录类型)。复合类型被创建后，可以被多次使用以便定义多个复合变量。
13、表类型：定义一组相同类型的数据，类似数组，但没有长度限制，可以动态增长(INDEX BY 简单类型)。语法:TYPE 类型名 IS TABLE OF 数据的类型 INDEX BY BINARY_INTEGER; 变量名 类型名;
14、复合变量在声明类型之前首先要先创建使用到的复合类型，再将变量声明为复合类型。
15、记录类型：由多个组件组成的类型，每个组件称为一个域，数据类型可以是简单变量、另一个记录类型或表类型。每个域可以是不同的数据类型，存放不同类型的数据。
16、记录类型变量通过“记录变量.域名”的方式来赋值或者引用。常用于存储从表中取出查询到的行数据。
17、通过%ROWTYPE声明记录类型变量，作用与%TYPE类似，主要用于定义不确定的变量类型，前缀通常是数据库的表名，语法：表名%ROWTYPE而%TYPE是字段名%TYPE。
二、PL/SQL基本语法
1、在PL/SQL中等号是比较运算符，转换函数有三种：TO_CHAR、TO_DATE、TO_NUMBER。
2、替代变量："&变量名"，如"select job into v_job from emp where empno=&no;" 输入no的值：1122，其中&no表示把输入的1122值赋给变量no。
3、绑定变量：":变量名"，如"select avg(sal) into v_sal from emp; :avg_sal:=v_sal;" 其中:avg_sal表示把v_sal的值赋给avg_sal,即让avg_sal与v_sal变量绑定。
4、PL/SQL中的select语句，如："select 指定选择的列 into 接收的变量"，查询列表后必须使用into子句，用于将查询出的字段值传递给变量。
5、当PL/SQL中的select语句的查询结果返回多行或者一行都没有返回时，会产生TOO_MANY_ROWS和NO_DATA_FOUND异常。
6、DQL: SELECT；DML: DELETE, INSERT, UPDATE；DDL: CREATE, DROP, TRUNCATE, ALTER；TCL: COMMIT, ROLLBACK, SAVEPOINT；DCL: GRANT, REVOKE。
7、通过EXECUTE IMMEDIATE可以在PL/SQL中直接执行DDL和DCL语句，只是EXECUTE IMMEDIATE后面的语句要用单引号引起来，如：
	SQL>DECLARE	
	 v_count number;
	BEGIN	
	--查询数据字典，看是否有TEMP这个表
	 SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name='TEMP';
	 --如果有TEMP表，执行DDL操作删除这个表
	 IF v_count=1 THEN EXECUTE IMMEDIATE 'DROP TABLE temp';
	END IF; 
	END;
8、条件判断语句：if……then……;elsif……then……; else……; end if;二重分支：if……then……else……;end if;多重分支：if……then……elsif……;CASE语句：case……when……then……;when……then……;else……;end case;
9、多个条件可以用连接操作符与、或、非三种(AND/OR/NOT)。对于空值的判断用IS NULL或者IS NOT NULL。
10、循环语句："LOOP 循环体 EXIT[WHEN 条件]"相当于"do……while()"。for循环： FOR index in [REVERSE] lower_bound……upper_bound LOOP……;end LOOP;其中REVERSE表示递减，不写REVERSE表示默认递增
lower_bound……upper_bound表示从最小值到最大值循环。
11、外部循环<<outer>>,内部循环<<inner>>，相当于用两层for循环时，通过{}来区别外循环与内循环的情况。
12、PL/SQL程序块作用域：相当于全局变量与局部变量的用法，如：
	SQL>set serveroutput on
	SQL>DECLARE	
	 v_weight number(3):=100;
	 v_msg varchar2(255):='outer value';
	BEGIN	
		DECLARE
			v_msg varchar2(255):='inner value';
			BEGIN
				DBMS_OUTPUT.PUT_LINE(v_weight||':'||v_msg);
			END;
		DBMS_OUTPUT.PUT_LINE(v_weight||':'||v_msg);
	END;
	输出结果为100：inner value
			  100：outer value
	只能引用当前BEGING外面的变量。
三、游标
 1、游标是指向一段私有的内存区域，用于暂时保存SQL语句影响到的数据的指针。 
 2、游标分隐式游标和显式游标，游标属性包括四种：%ROWCOUNT、%FOUND、%NOTFOUND、%ISOPEN。
 3、隐式游标以SQL为前缀，SQL%ROWCOUNT(受SQL影响的行数)、SQL%FOUND(Boolean值，是否还有数据)、SQL%NOTFOUND(Boolean值，是否已无数据)、SQL%ISOPEN(总是为FALSE)。
 4、显式游标%ISOPEN(Boolean类型，如果游标打开，则为TRUE)、%NOTFOUND(Boolean类型，如果最近的提取没有返回记录，则为TRUE)、%FOUND(Boolean类型，只要有记录能提取到就一直为TRUE，直到提取不到)、
 %ROWCOUNT(Number类型，到当前时间为止，提取的总行数)。
 5、声明游标：CURSOR 游标名 IS select语句;打开游标：OPEN 游标名;取游标中的数据：FETCH 游标名 INTO 变量;其中注意游标只有在打开时，才可以执行取(FETCH)操作，
用%ISOPEN来测试游标是否打开。 另外，INTO后面的变量要与游标中的字段个数相同，并且顺序要一一对应。
 6、通过游标属性来判断取操作是否完成，%NOTFOUND操作判断上一次的取操作是否成功取到数据，返回TRUE表示游标已为取空。通过%FOUND来控制循环取操作。
 7、游标一旦被关闭，游标的内存空间就被释放，不可以再提取数据了。
 8、用记录接收游标中的数据，如：
	SQL>DECLARE	
	--定义游标后可以不用再创建记录类型，便可直接引用游标作为记录类型
	  CURSOR  emp_cursor IS
			 SELECT ename,sal FROM emp WHERE deptno=10;
	  emp_record emp_cursor%ROWTYPE; /*声明记录类型变量时不是创建记录类型变量，而是通过游标名%ROWTYPE的方式声明，这能让记录类型变量与游标结构保持一致*/
	BEGIN	
		OPEN emp_cursor;
		Loop
			FETCH emp_cursor INTO emp_record;	/*将游标中的字段的值fetch到变量中*/
			EXIT WHEN emp_cursor%NOTFOUND;	/*当游标中没有数据时跳出循环*/
			--注意游标变量中域的获取方式为记录名.域名
			DBMS_OUTPUT.PUT_LINE(emp_record.ename||'的薪水是'||emp_record.sal);
			END LOOP;
		DBMS_OUTPUT.PUT_LINE('共取出了'||emp_cursor%ROWCOUNT||'条记录');
		CLOSE emp_cursor;
	END;
 9、带参数的游标：如果需要执行多个查询语句，而多个查询的区别在于WHERE条件中的值不同，这时就可以通过传递参数来实现。如把上个例子稍做改变：
	SQL>DECLARE	
	--定义带参数的游标
	  CURSOR  emp_cursor(v_deptno NUMBER) IS
			 SELECT ename,sal FROM emp WHERE deptno=v_deptno;	--参数变量
	  emp_record emp_cursor%ROWTYPE;
	BEGIN	
		OPEN emp_cursor(10);	/*打开游标，在此处给定参数值*/
 10、定义游标变量："TYPE 游标类型名 IS REF CURSOR RETURN 变量名%ROWTYPE"，如：
	SQL>DECLARE	
	  TYPE dept_cursor_type IS REF CURSOR RETURN dept%ROWTYPE;		/*定义游标变量类型*/
		dept_cursor dept_cursor_type;		/*定义游标变量*/
		dept_record dept%ROWTYPE;		/*定义记录变量*/
 11、UPPER('&input'):表示取输入的字符串的首字母大写。
 12、游标的标准过程需经过声明游标、打开、循环取操作、关闭游标几个步骤。但是用游标中的for循环就可以不需OPEN、FETCH、CLOSE操作。
 13、游标中的for循环："for 下标变量 in 游标名 Loop"，如：
	--遍历部门号为20的所有员工的薪水
	SQL>DECLARE	
	  CURSOR  emp_cursor(v_deptno NUMBER) IS
			 SELECT ename,sal FROM emp WHERE deptno=v_deptno;
	BEGIN	
		FOR  emp_record IN emp_cursor(20) LOOP
		DBMS_OUTPUT.PUT_LINE(emp_record.ename||'的薪水是'||emp_record.sal);
		END LOOP;
	END;
 14、为了修改或者删除当前的游标记录，必须在打开游标时锁定游标取出的数据，锁定表示只有游标可以修改这些数据，其他的数据库连接都不可以修改。
 15、锁定游标用for update，如"CURSOR 游标名 IS SELECT语句 FOR UPDATE [OF 特定的表]"。
 16、更新游标中的行："UPDATE 表名 SET 字段名=.. WHERE CURRENT OF 游标名"；删除游标中的行："DELETE FROM 表名 WHERE CURRENT OF 游标名"。