SQL-PLSQL教程

汉得信息技术有限公司®SQLandPl/SQL培训文档作者：周胜杰2006-10-25汉得信息技术有限公司®第一章PL/SQL简介第二章PL/SQL数据类型第三章PL/SQL控制结构第四章PL/SQLCollectionsandrecords第五章游标练习第一天汉得信息技术有限公司®SQL(StructureQueryLanguage):是对关系型数据库(RDBMS)进行操作的标准语言.PL/SQL(ProceduralLanguage/SQL):在SQL的基础上,增加了过程化处理.比如sql*plus,developer6i和标准的SQL相比,PL/SQL有自己特有的一些特性变量定义流程结构控制对象类型存储过程和函数第一章PL/SQL简介汉得信息技术有限公司®cd$ORACLE_HOME/sqlplus/demodemobld.sql,这个脚本创建几个相关联的表,并插入示例数据.这个脚本可以在用户帐号下执行.demodrop.sql删除demo表和数据安装scott/tigger用户模式汉得信息技术有限公司®Declare/*定义变量*/l_enamevarchar2(10);Begin/*代码部分*/l_ename:=‘scott’;selectenameintol_enamefromscott.empwhereempno=‘7369’;dbms_output.put_line(l_ename);End;Example1汉得信息技术有限公司®Declare变量声明,可以声明oracle标准变量,也可以声明自定义变量,还可以声明异常.Begin块代码,可以变量赋值,使用循环结构,条件结构等.Exception异常处理部分,可以捕捉oracle预定义的异常,也可以捕捉自定义异常End;标准的PL/SQL块结构汉得信息技术有限公司®无名块:example1就是一个无名块.子程序:存储过程,函数,程序包,存储在数据库中,可以被表用.触发器:存储在数据库中,根绝触发条件自动触发执行.PL/SQL块类型汉得信息技术有限公司®Createorreplaceprocedureproc1/*变量定义*/Asbegindeletefromscott.empwhereempno=1234;commit;Endproc1;Example2:子程序,函数,程序包汉得信息技术有限公司®PL/SQL代码的执行PL/SQL程序块是通过一个引擎来执行的,这个引擎位于oracle数据库,也可能在一些客户端开发工具中存在,比如form/report开发工具.如果客户端由PL/SQL引擎,则过程性语句由客户端执行,sql语句提交服务器执行.汉得信息技术有限公司®预定义数据类型用户定义子数据类型数据类型转换第二章PL/SQL数据类型汉得信息技术有限公司®标量数据类型组合数据类型引用数据类型LOB数据类型预定义数据类型汉得信息技术有限公司®Binary_integer用来存储带符号的整数,取值范围是-2147483647到2147483647Number可以存储定点数和浮点数,取值范围是1.0E-130到9.99E125Pls_integer用来存储带符号的整数,取值范围和binary_integer相同,但是该类型操作速度比Number和binary_integer快.数值数据类型汉得信息技术有限公司®Char存放固定长度的字符数据,char(max_length),max_length以字节为单位,取值从1到32767.如果数据库列为char类型,则最大为2000个字节Varchar2存放变长字符数据,最大长度是32767.如果数据库列为varchar2类型,则最大为4000字节.超出后会报错Long存储变长字符数据,最大长度为32760字节,数据库列为long型时最大长度是2G.所以数据库列大于32760时,是不能赋值给long型变量的.字符数据类型汉得信息技术有限公司®Nchar存储固定长度的NLS字符数据nvarchar2存储变长NLS字符数据字符数据类型(续)汉得信息技术有限公司®BFILE:存储二进制对象,对象存储在操作系统,BFILE列存储了指向操作系统对象的路径.BLOB:存储二进制对象到数据库,BLOB列存储了指向数据库中二进制对象的指针.CLOB:存储大字符数据对象到数据库,CLOB列存储了指向字符数据对象的指针.NCLOB:存储NLS大字符数据对象到数据库,NCLOB存储了指向NLS字符数据对象的指针.LOB类型的最大尺寸为4G,oracle提供了dbms_lob对lob数据进行操作.LOB数据类型汉得信息技术有限公司®存储true,false等逻辑值,只允许逻辑操作,可以为null布尔数据类型汉得信息技术有限公司®Date:秒为最小单位Timestamp:定义了比date更精确的时间.日期数据类型汉得信息技术有限公司®用户定义子数据类型和其对应基本类型有相同的操作,但是对基本类型的取值做了限定.语法:subtypesubtype_nameisbase_type[(constraint)][notnull]举例:Subtypebirthdayisdatenotnull;Typenamelististableofvarchar2(10);Subtypesub_namelistisnamelist;用户定义子数据类型汉得信息技术有限公司®不同数据类型之间可以进行数据转换,有些转换是隐式的,比如select‘1’+1fromdual,结果为2,这表明自动的把字符‘1’转换为数字,然后参加运算.有些转换需要用户显式进行,oracle提供了一些函数进行数据类型转换,比如to_date,to_char等.数据类型转换汉得信息技术有限公司®对于无名块,如果有变量声明,则必须写在declare后面,begin之前对于子程序,变量声明必须在createorreplace之后begin之前声明的变量作用范围为当前块或者当前块内嵌套的子块.变量的声明,赋值汉得信息技术有限公司®选择控制语句循环控制语句顺序控制语句第三章PL/SQL控制结构汉得信息技术有限公司®If..ThenIf..Then..ElseIf..Then..elsif选择控制语句汉得信息技术有限公司®Declarep_salnumber;p_enamevarchar2(10);Beginselectsal,enameintop_sal,p_enamefromscott.empwhereempno=7369;ifp_sal=800thendbms_output.put_line(‘Thesalaryof’||p_ename||’islessthan800‘);endif;End;Example3汉得信息技术有限公司®ifp_sal=800thendbms_output.put_line(‘Thesalaryof’||p_ename||’islessthan800‘);elsedbms_output.put_line(‘greaterthan800’);endif;End;example4汉得信息技术有限公司®ifp_sal800thendbms_output.put_line(‘Thesalaryof’||p_ename||’islessthan800‘);elsifp_sal=800thendbms_output.put_line(‘equalto800’);elsedbms_output.put_line(‘greaterthan800’)endif;Example5汉得信息技术有限公司®loopwhile..Loopfor..loop循环控制语句汉得信息技术有限公司®DeclareInumber:=0;BeginloopI=I+1;/*ifI=10thenexit;endif;*/exitwhenI=10;endloop;End;LOOP汉得信息技术有限公司®DeclareInumber;BeginI:=0;whileI10loopI=I+1;endloop;End;While..loop汉得信息技术有限公司®Begin/*forIinreverse1..9loop从9递减到1,增量或者减量只能是1*/forIin1..9loopdbms_output.put_line(I);endloop;End;环境变量I不必定义,隐式定义为整数只能引用,不能赋值增量或者减量是1初始值必须小于结束值For..Loop汉得信息技术有限公司®Beginforrin(select10a,20bfromdual)loopdbms_output.put_line(r.a);dbms_output.put_line(r.b);endloop;End;For..Loop(续)汉得信息技术有限公司®Beginforiin1..10loopdbms_output.put_line(i);forjin11..20loopdbms_output.put_line(j);ifj=15thenexit;endif;endloop;endloop;End;Exit只退出当前循环For..Loop(续)汉得信息技术有限公司®declareinumber;Begini:=0;ifi=0thengotoi0;elsifi=1thengotoi1;elsegotoi3;endif;i0dbms_output.put_line('i=0');return;i1dbms_output.put_line('i=1');return;i3dbms_output.put_line('i=2');End;Goto汉得信息技术有限公司®标号后必须有可执行的pl/sql块不能跳转到ifloop不能从异常处理部分跳到当前块Goto(续)汉得信息技术有限公司®什么是collection定义collectiontype声明PL/SQLcollection变量Collection的初始化和引用Collection的赋值Collection的使用Collection的方法Collection的异常什么是record声明record为record赋值Record的使用第四章PL/SQLCollectionsandRecords汉得信息技术有限公司®Collection是按某种顺序排列的一组元素，所有的元素有相同的数据类型,每个元素有唯一一个下标标识其在这一组元素中的位置。PL/SQL提供了三种类型的collection:Index-bytable,又叫associativearray(关联数组），使用键值访问nestedtable:可以作为数据库列直接存储到数据库varray：定义的时候需要指定数组的大小，可以作为数据库列直接存储到数据库什么是Collection汉得信息技术有限公司®TYPEtype_nameISTABLEOFelement_type[NOTNULL];element_type可以是PL/SQL任意的数据类型，除了refcursor比如：createtypet_myempistableofscott.emp%rowtype;Createtypet1istableofvarchar2(10);定义nestedtable汉得信息技术有限公司®TYPEtype_nameIS{VARRAY|VARYINGARRAY}(size_limit)OFelement_type[NOTNULL];定义varrays汉得信息技术有限公司®TYPEtype_nameISTABLEOFelement_type[NOTNULL]INDEXBY[BINARY_INTEGER|PLS_INTEGER|VARCHAR2(size_limit)];定义associativearrays汉得信息技术有限公司®创建数据库表时，可以定义某列的类型为