玩转Oracle-PLSQL定义并使用变量

Ⅰ、介绍：在编写pl/sql程序时候，可以定义变量和常量，在pl/sql中包括有：标量类型（scalar）复合类型（composite）参照类型（reference）lob（largeobject）；㈠、标量：（scalar）常用的类型：在编写pl/sql的时候，如果要使用变量，需要在定义部分定义变量；pl/sql中定义变量和常量的语法如下：identifier[constant]datatype[notnull][:=|defaultexpt]identifier:名称；constant:指定常量，需要指定他的初始值，而且他的值是不能够改变的；datatype：数据类型；notnull；指定变量不能为null；:=给变量或是常量指定初始值；default：用于指定初始值；expr：指定初始值的pl/sql表达式，可以是文本、其他变量、函数等；①定义一个变长字符串：v_enamevarchar2（20）；②定义一个数：v_salnumber（6,3）；③定义一个数并给定初始值：v_salnumber（5,3）:=5.4④定义一个日期类型的数据：v_hitedatedate；⑤定义一个bool变量，不能为null，初始值为false；v_validbooleannotnulldefaultfalse；㈡、标量：使用标量在定义好变量后就可以使用这些边玲了，这里需要说明的是pl/sql块为变量赋值不同于其他编程语言，需要使用:=符号；SQL--下面以输入员工号，显示员工姓名等信息；SQLdeclare2c_tax_ratenumber(3,2):=0.03;3--用户名；4v_namevarchar2(5);5v_salnumber(6,2);6v_tax_salnumber(6,2);7begin8--执行910selectename,salintov_name,v_salfromempwhereempno=&no;11--计算所得税‘12v_tax_sal:=v_sal*c_tax_rate;13--输出；14dbms_output.put_line('Nameis:'||v_name||'salis'||v_sal||'taxis'||v_tax_sal);15end;16/Entervalueforno:7788old10:selectename,salintov_name,v_salfromempwhereempno=&no;new10:selectename,salintov_name,v_salfromempwhereempno=7788;Nameis:SCOTTsalis3000taxis90PL/SQLproceduresuccessfullycompleted.㈢、标量：使用%type类型；对于上面的pl/sql块有一个问题：如果员工的姓名操过了5个字符的话救护出错，为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样他会按照数据库列来确定你定义的变量类型和长度；标识符名称表明.列名%type；SQL--下面以输入员工号，显示员工姓名等信息；SQLdeclare2c_tax_ratenumber(3,2):=0.03;3--用户名；4v_nameemp.ename%type;5v_salemp.sal%type;6v_tax_salnumber(6,2);7begin8--执行910selectename,salintov_name,v_salfromempwhereempno=&no;11--计算所得税‘12v_tax_sal:=v_sal*c_tax_rate;13--输出；14dbms_output.put_line('Nameis:'||v_name||'salis'||v_sal||'taxis'||v_tax_sal);15end;16/Entervalueforno:7788old10:selectename,salintov_name,v_salfromempwhereempno=&no;new10:selectename,salintov_name,v_salfromempwhereempno=7788;Nameis:SCOTTsalis3000taxis90PL/SQLproceduresuccessfullycompleted.SQLⅡ、复合变量（composite）；㈠、介绍：用于存放多个值的变量。主要包括下面几种：①pl/sql记录；②pl/sql表；③嵌套表；④varray；㈡、pl/sql记录：类似于高级语言中的结构体，需要注意的是，当引用pl/sql记录成员时候，必须加记录变量作为前缀（记录变量.记录成员）如下；SQL--pl/sql记录实例；SQLdeclare2--定义复合（pl/sql记录）类型；emp_record_type3typeemp_record_typeisrecord(4nameemp.ename%type,5salaryemp.sal%type,6tittleemp.job%type);7--复合类型定义结束；89--定义一个复合类型的变量sp_record，其类型是emp_record_type；10sp_recordemp_record_type;1112begin13selectename,sal,jobintosp_recordfromempwhereempno=7788;14dbms_output.put_line('Name:'||sp_record.name);15end;16/Name:SCOTTPL/SQLproceduresuccessfullycompleted.㈢、pl/sql表：相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下表不能为负数，而pl/sql是可以为负数的，并且表元素的下标没有限制，实例如下:SQL--pl/sql表实例；SQLSQLdeclare2--定义了一个pl/sql表类型：sp_table_type，3--该类型是用于存放emp.ename%type类型的数组；4--indexbybinary_integer：代表下标是按照整数来排序的；5typesp_table_typeistableofemp.ename%typeindexbybinary_integer;67--定义了一个sp_table_type类型的变量：sp_table；8sp_tablesp_table_type;910begin11selectenameintosp_table(0)fromempwhereempno=7788;12dbms_output.put_line('Name:'||sp_table(0));13end;14/Name:SCOTTPL/SQLproceduresuccessfullycompleted.说明：sp_table_type：pl/sql表类型；emp.ename%type指定了表的元素类型和长度；sp_table：为pl/sql表变量；注意：如果上面的块将where子句去掉，会怎么样呢？这样就会出错，这是就应该使用参照变量；当然，复合变量还有很多，比如①复合变量：嵌套表（nestedtable）；②复合变量：变长数组（varray）；Ⅲ、参照变量：㈠介绍：参照变量是指用于存放数值指针的变量，通过使用参照变量，我们可以使得应用程序共享相同的对象，从而降低占用的空间，在编写pl/sql程序的时候，可以使用游标变量（refcursor）和对象类型变量（refobj_type)两种参照变量；㈡参照变量：refcursor游标变量；使用游标时候，当定义游标时候，不需要指定相应的select语句，但是当使用游标（open）的时候就需要指定select语句，这样，一个游标就和一个select语句结合起来了：实例：①、请用pl/sql编写一个块：可以输入部门号，并显示该部门所有员工姓名和工资；--请用pl/sql编写一个块：可以输入部门号，并显示该部门所有员工姓名和工资；declare--定义游标类型：sp_emp_cursor；typesp_emp_cursorisrefcursor;--定义一个游标变量；test_cursorsp_emp_cursor;--定义变量：v_enameemp.ename%type;v_salemp.sal%type;begin--执行--把test_cursor和一个select结合；--相当于将test_cursor指向有select查询返回的结果集；opentest_cursorforselectename,salfromempwheredeptno=&no;--这时候我们可以循环取出结果集中的结果了；--开始循环体；loop--取值；fetchtest_cursorintov_ename,v_sal;--判断是否test_cursor为空了，如果不判断就会出现死循环的效果；exitwhentest_cursor%notfound;dbms_output.put_line('Nameis'||v_ename||'Salis'||v_sal);--结束循环体；endloop;end;②、在①的基础上，如果某个员工的工资低于200，就加上100；