Oracle Database ：玩转Oracle学习笔记之（15）：PLSQL函数包

Ⅰ、介绍：在编写pl/sql程序时候，可以定义变量和常量，在pl/sql中包括有：
标量类型（scalar）
复合类型（composite）
参照类型（reference）
lob（large object）；

㈠、标量：（scalar）常用的类型：

在编写pl/sql的时候，如果要使用变量，需要在定义部分定义变量；

pl/sql中定义变量和常量的语法如下：

identifier [constant] datatype [ not null ]  [:=|default expt]

identifier :名称；
constant :指定常量，需要指定他的初始值，而且他的值是不能够改变的；
datatype：数据类型；
not null ；指定变量不能为null；

:=  给变量或是常量指定初始值；

default：用于指定初始值；

expr：指定初始值的pl/sql表达式，可以是文本、其他变量、函数等；

①定义一个变长字符串：v_ename varchar2（20）；
②定义一个数：v_sal number（6,3）；
③定义一个数并给定初始值：v_sal number（5,3）:=5.4

④定义一个日期类型的数据：v_hitedate date；
⑤定义一个bool变量，不能为null，初始值为false；
v_valid boolean not null default false；


㈡、标量：使用标量

在定义好变量后就可以使用这些边玲了，这里需要说明的是pl/sql块为变量赋值不同于其他编程语言，需要使用:=符号；

SQL> --下面以输入员工号，显示员工姓名等信息；
SQL> declare
  2  c_tax_rate number(3,2):=0.03;
  3  --用户名；
  4  v_name varchar2(5);
  5  v_sal number(6,2);
  6  v_tax_sal number(6,2);
  7  begin
  8  --执行
  9
 10  select ename ,sal into v_name , v_sal from emp where empno=&no;
 11  --计算所得税‘
 12  v_tax_sal:=v_sal*c_tax_rate;
 13  --输出；
 14  dbms_output.put_line('Name is : ' || v_name || ' sal is '||v_sal||' tax is  '||v_tax_sal);
 15  end;
 16  /
Enter value for no: 7788
old  10: select ename ,sal into v_name , v_sal from emp where empno=&no;
new  10: select ename ,sal into v_name , v_sal from emp where empno=7788;
Name is : SCOTT sal is 3000 tax is  90

PL/SQL procedure successfully completed.

㈢、标量：使用%type类型；
对于上面的pl/sql块有一个问题：
如果员工的姓名操过了5个字符的话救护出错，为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样他会按照数据库列来确定你定义的变量类型和长度；

标识符名称 表明.列名%type；

SQL> --下面以输入员工号，显示员工姓名等信息；
SQL> declare
  2  c_tax_rate number(3,2):=0.03;
  3  --用户名；
  4  v_name emp.ename%type;
  5  v_sal  emp.sal%type;
  6  v_tax_sal number(6,2);
  7  begin
  8  --执行
  9
 10  select ename ,sal into v_name , v_sal from emp where empno=&no;
 11  --计算所得税‘
 12  v_tax_sal:=v_sal*c_tax_rate;
 13  --输出；
 14  dbms_output.put_line('Name is : ' || v_name || ' sal is '||v_sal||' tax is  '||v_tax_sal);
 15  end;
 16  /
Enter value for no: 7788
old  10: select ename ,sal into v_name , v_sal from emp where empno=&no;
new  10: select ename ,sal into v_name , v_sal from emp where empno=7788;
Name is : SCOTT sal is 3000 tax is  90

PL/SQL procedure successfully completed.

SQL>

Ⅱ、复合变量（composite）；
㈠、介绍：用于存放多个值的变量。主要包括下面几种：
①pl/sql记录；
②pl/sql表；
③嵌套表；
④varray；

㈡、pl/sql记录：类似于高级语言中的结构体，需要注意的是，当引用pl/sql记录成员时候，必须加记录变量作为前缀（记录变量.记录成员）如下；

SQL> --pl/sql记录实例；
SQL> declare
  2  --定义复合（pl/sql记录）类型；emp_record_type
  3  type emp_record_type is record(
  4  name emp.ename%type,
  5  salary emp.sal%type,
  6  tittle emp.job%type);
  7  --复合类型定义结束；
  8
  9  --定义一个复合类型的变量sp_record，其类型是emp_record_type；
 10  sp_record emp_record_type;
 11
 12  begin
 13  select ename,sal,job into sp_record from emp where empno=7788;
 14  dbms_output.put_line('Name : '||sp_record.name);
 15  end;
 16  /
Name : SCOTT

PL/SQL procedure successfully completed.

㈢、pl/sql表：相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下表不能为负数，而pl/sql是可以为负数的，并且表元素的下标没有限制，实例如下:

SQL> --pl/sql表实例；
SQL>
SQL> declare
  2  --定义了一个pl/sql表类型：sp_table_type ，
  3  --该类型是用于存放emp.ename%type类型的数组；
  4  --index by binary_integer：代表下标是按照整数来排序的；
  5  type sp_table_type is table of emp.ename%type index by binary_integer;
  6
  7  --定义了一个sp_table_type类型的变量：sp_table；
  8  sp_table sp_table_type;
  9
 10  begin
 11  select ename into sp_table(0) from emp where empno=7788;
 12  dbms_output.put_line('Name : '||sp_table(0));
 13  end;
 14  /
Name : SCOTT

PL/SQL procedure successfully completed.

说明：
sp_table_type：pl/sql表类型；
emp.ename%type 指定了表的元素类型和长度；
sp_table：为pl/sql表变量；


注意：如果上面的块将where子句去掉，会怎么样呢？
这样就会出错，这是就应该使用参照变量；

当然，复合变量还有很多，比如
①复合变量：嵌套表（nested table）；
②复合变量：变长数组（varray）；



Ⅲ、参照变量：
㈠介绍：
参照变量是指用于存放数值指针的变量，通过使用参照变量，我们可以使得应用程序共享相同的对象，从而降低占用的空间，在编写pl/sql程序的时候，可以使用游标变量（ref cursor）和对象类型变量（ref obj_type)两种参照变量；


㈡参照变量：ref cursor游标变量；
使用游标时候，当定义游标时候，不需要指定相应的select语句，但是当使用游标（open）的时候就需要指定select语句，这样，一个游标就和一个select语句结合起来了：

实例：

①、请用pl/sql编写一个块：可以输入部门号，并显示该部门所有员工姓名和工资；

--请用pl/sql编写一个块：可以输入部门号，并显示该部门所有员工姓名和工资；
declare
	--定义游标类型：sp_emp_cursor；
	type sp_emp_cursor is ref cursor;
	--定义一个游标变量；
	test_cursor sp_emp_cursor;

	--定义变量：
	v_ename emp.ename%type;
    v_sal emp.sal%type;
begin
	--执行
	--把test_cursor和一个select结合；
	--相当于将test_cursor指向有select查询返回的结果集；
	open test_cursor for select ename , sal from emp where deptno=&no;

	--这时候我们可以循环取出结果集中的结果了；

	--开始循环体；
	loop

	--取值；
		fetch test_cursor into v_ename ,v_sal;
	--判断是否test_cursor为空了，如果不判断就会出现死循环的效果；
		exit when test_cursor%notfound;
		dbms_output.put_line('Name is '||v_ename ||' Sal is '||v_sal);
	--结束循环体；
	end loop;
end;

②、在①的基础上，如果某个员工的工资低于200，就加上100；