PLSQL编程基础.ppt

第第7章章 PL/SQL PL/SQL编程基础编程基础前面所学习的前面所学习的SQL是操作关系性数据库的一种通用语言，是操作关系性数据库的一种通用语言，但是但是SQL本身是一种非过程化的语言，即不用指明执行的具体本身是一种非过程化的语言，即不用指明执行的具体方法和途径，而是简单地调用相应语句直接取得结果即可。所方法和途径，而是简单地调用相应语句直接取得结果即可。所以以SQL本身并不适合在复杂的业务流程下使用，为了解决这一本身并不适合在复杂的业务流程下使用，为了解决这一问题，问题，Oracle提供了提供了PL/SQL编程，这是一种过程化编程语言，编程，这是一种过程化编程语言，与与JAVA语言一样关注于处理细节，可以实现较为复杂的业务语言一样关注于处理细节，可以实现较为复杂的业务逻辑。逻辑。7.1 PL/SQL PL/SQL简介简介PL/SQL语法结构语法结构DECLARE -声明部分，例如：定义变量、常量、游标声明部分，例如：定义变量、常量、游标BEGIN -程序编写、程序编写、SQL语句语句EXECEPTION -处理异常处理异常END;/声明部分（声明部分（DECLARE）：包含变量定义、用户定义的）：包含变量定义、用户定义的PL/SQL类型、游标、引用类型、游标、引用 的函数或的函数或过程。过程。执行部分（执行部分（BEGIN）：包含变量赋值、对象初始化、条件结构、迭代结构、嵌套的）：包含变量赋值、对象初始化、条件结构、迭代结构、嵌套的PL/SQL块，块，或是对局部或者存储或是对局部或者存储PL/SQL命名块的调用。命名块的调用。异常部分（异常部分（exception）：包含错误处理语句，该语句可以像执行部分一样使用所有项。）：包含错误处理语句，该语句可以像执行部分一样使用所有项。结束部分（结束部分（END）:程序执行到程序执行到END表示结束，分号用户结束块，正斜杠（表示结束，分号用户结束块，正斜杠（/）执行块程序）执行块程序既没有变量，也没有流程控制既没有变量，也没有流程控制（分支、循环）。（分支、循环）。PL/SQL与与SQL区别：区别：SQLPL/SQLPL/SQL是结构化和过程化的结是结构化和过程化的结合体。合体。每条每条SQL语句逐一发送给数据库。语句逐一发送给数据库。一次性将多条一次性将多条SQL语言一起发送语言一起发送给数据库给数据库7.1 PL/SQL PL/SQL简介简介最简单的最简单的PL/SQL块可以不做任何事情，但是在块可以不做任何事情，但是在PL/SQL编程过程中要求，执行块至编程过程中要求，执行块至少要有一条语句，即使这条语句只是编写一个少要有一条语句，即使这条语句只是编写一个NULL也行。也行。范例：编写不做任何工作的范例：编写不做任何工作的PL/SQL块。块。BEGIN NULL;END;/范例：编写一个简单的范例：编写一个简单的PL/SQL程序程序DECLAREv_num number;BEGIN v_num:=30;DBMS_output.put_line(v_num变量的内容是：变量的内容是：|v_num);END;7.1 PL/SQL PL/SQL简介简介范例：编写范例：编写PL/SQL块，输入一个雇员编号，而后取得指定的雇员姓名。块，输入一个雇员编号，而后取得指定的雇员姓名。Declare v_eno number;v_ename varchar2(10);BeginV_eno:=&empno;-由键盘输入雇员编号由键盘输入雇员编号Select ename into v_ename from emp where empno=v_eno;DBMS_OUTPUT.put_line(编号为编号为:|v_eno|雇员的名字为：雇员的名字为：|v_ename);End;7.1 PL/SQL PL/SQL简介简介7.2 变量的声明与赋值变量的声明与赋值变量声明并使用变量：变量声明并使用变量：n变量名称可以有字母、数字、变量名称可以有字母、数字、_、$、#等组成。等组成。n所有变量名称要求以字母开头，不能是所有变量名称要求以字母开头，不能是Oracle中的保留字中的保留字n变量的长度最多只能为变量的长度最多只能为30个字符个字符n变量不区分大小写变量不区分大小写7.2 变量的声明与赋值变量的声明与赋值声明变量语法：声明变量语法：变量名称变量名称 CONSTANT 类型类型 not NULL:=value使用使用%TYPE声明变量类型：声明变量类型：在编写在编写PL/SQL程序时，如果希望某一个变量与指定数据表中某一列的类型程序时，如果希望某一个变量与指定数据表中某一列的类型一样，则可以采用一样，则可以采用“变量定义变量定义”表名称表名称.表名称表名称%TYPE的格式的格式7.2 变量的声明与赋值变量的声明与赋值Declare v_eno emp.empno%type;v_ename emp.ename%type;Begin DBMS_output.put_line(请输入雇员编号：请输入雇员编号：);v_eno:=&empno;select ename into v_ename from emp where empno=v_eno;DBMS_output.put_line(编号为：编号为：|v_eno|雇员的名字为：雇员的名字为：|v_ename);end;7.2 变量的声明与赋值变量的声明与赋值使用使用%ROWTYPE声明变量类型声明变量类型PL/SQL提供了一种提供了一种%ROWTYPE，使用此标记可以定义表中一行记录的类型。，使用此标记可以定义表中一行记录的类型。当用户使用了当用户使用了“selectinto”将表中的一行记录设置为将表中的一行记录设置为ROWTYPE类型的变量中时，就类型的变量中时，就可以利用可以利用“rowtype 变量变量.表字段表字段”的方式取得表中每行对应的列数据。的方式取得表中每行对应的列数据。7.2 变量的声明与赋值变量的声明与赋值Declare v_deptrow dept%rowtype;-装载一行装载一行dept记录记录 Begin select*into v_deptrow from dept where deptno=10;DBMS_output.put_line(部门编号：部门编号：|v_deptrow.deptno|名称：名称：|v_deptrow.dname|位置：位置：|v_deptrow.loc);End;7.3 标量类型标量类型数值型数值型标量类型标量类型字符型字符型日期型日期型大对象大对象布尔布尔7.3 标量类型标量类型No.No.分分类类数据类型数据类型描述描述1数值型Number(数据总长度,小数位长度)Number是一种表示数字的数据类型。可声明保存数据类型的整数位和小数位的精度，在数据库中是以十进制格式存储，在计算时，系统将其变成二进制数据运算，占32字节。BINARY_INTEGER不存储在数据库中，只能在PL/SQL中使用的带符号整数，范围为-231-231，如果发生溢出，则自动变为number型数据。23PLS_INTEFER有符号的整数，范围为-231-231，可以直接进行数学运算，溢出则触发异常，与Number相比，其占空间小，性能好4BINARY_FLOAT单精度32位浮点类型，占5个字节5BINARY_DOUBLE双精度浮点数类型，占9个字节7.3 标量类型标量类型No.No.分分类类数据类型数据类型描述描述12字符型RAW保存固定长度的二进制数据，最多可以存放2000个字节的数据LONG RAW存储二进制数据（图片、音乐等），最多可以存储2G大小数据1314ROWID数据表中每行记录的唯一物理地址标示，只支持物理行ID，不支持逻辑行ID15UROWID支持物理行ID和逻辑行ID7.3 标量类型标量类型No.No.分分类类数据类型数据类型描述描述16日期型DATADATA是一个7字节的列，可以保存时期和时间，不包含毫秒TIMESTAMPDATA子类型，包含日期和时间，时间部分包含毫秒，有TIMESTAMP WITH TIME ZONE和TIMESTAMP WITH LOCAL TIME ZONE1718INTERVALDATA的子类型，用于管理时间间隔，有INTERVAL DAY TO SECOND和INTERVAL YEAR TO MONTH两种子类型7.3 标量类型标量类型No.No.分分类类数据类型数据类型描述描述19大对象CLOBCLOB数据类型代表字符型大对象，最大存储4GB的字符串数据NCLOB存储UNICODE编码的大文本数据，最多可以存储4GB的非结构化的二进制数据2021BLOBBLOB数据类型列可以包含最大4GB的非结构化二进制数据22BFILE包含存储在外部文件系统上文件的索引，最大不超过4GB23布尔BOOLEAN布尔类型，可以设置的内容为TRUE、FALSE、NULL7.4 程序结构程序结构 PL/SQL程序与其他编程语言一样，也拥有自己的程序与其他编程语言一样，也拥有自己的3中程序结构，即顺序结构、分支结构、中程序结构，即顺序结构、分支结构、循环结构。循环结构。开始开始语句语句1语句语句2语句语句n结束结束顺序结构顺序结构判断条件判断条件语句语句2语句语句n结束结束语句语句1分支结构分支结构语句语句1语句语句2判断条件判断条件循环结构循环结构7.4.1 分支结构分支结构IF语句语句If语句IFelse语句IFelseifelse语句If判断条件 Then 满足条件执行语句；End if;If判断条件 then 满足条件执行语句；Else 不满足条件执行语句；End IF;If判断条件1 then 满足条件1执行语句；Elseif 判断条件2 then Else 所有条件不满足执行语句；END if;7.4.1 分支结构分支结构范例：查询范例：查询emp表的工资。输入员工编号，根据编号查表的工资。输入员工编号，根据编号查询工资。如果工资高于询工资。如果工资高于3000元，则显示高工资；如果工元，则显示高工资；如果工资大于资大于2000元则显示中等工资；如果小于元则显示中等工资；如果小于2000元，则显元，则显示低工资。示低工资。DECLAREv_empSalemp.sal%TYPE;-定义变量与定义变量与emp.sal字段类型相同字段类型相同v_empNameemp.ename%TYPE;-定义变量与定义变量与emp.ename字段类型相同字段类型相同v_enoemp.empno%TYPE;-定义变量与定义变量与emp.empno字段类型相同字段类型相同BEGINv_eno:=&inputEmpno;-用户输入要查找的雇员编号用户输入要查找的雇员编号-根据输入的雇员编号查找雇员姓名及工资根据输入的雇员编号查找雇员姓名及工资SELECT ename,sal INTO v_empName,v_empSal FROM emp WHERE empno=v_eno;IF v_empSal 3000 THEN-判断判断DBMS_OUTPUT.put_line(v_empName|的工资属于高工资！的工资属于高工资！);ELSIF v_empSal 2000 THEN-判断判断DBMS_OUTPUT.put_line(v_empName|的工资属于中等工资！的工资属于中等工资！);ELSEDBMS_OUTPUT.put_line(v_empName|的工资属于低工资！的工资属于低工资！);END IF;END;7.4.1 分支结构分支结构范范例例：用用户户输输入入一一个个雇雇员员编编号号，根根据据它它所所在在的的部部门门给给上涨工资，规则：上涨工资，规则：10部门上涨部门上涨10%，20上涨上涨20%，30上涨上涨30%；但是要求最高不能超但是要求最高不能超过过5000，超，超过过5000就停留在就停留在5000。7.4.1 分支结构分支结构7.4.1 分支结构分支结构DECLAREv_empSalemp.sal%TYPE;-定义变量与定义变量与emp.sal字段类型相同字段类型相同v_dnoemp.deptno%TYPE;-定义变量与定义变量与emp.deptno字段类型相同字段类型相同v_enoemp.empno%TYPE;-定义变量与定义变量与emp.empno字段类型相同字段类型相同BEGINv_eno:=&inputEmpno;-用户输入要查找的雇员编号用户输入要查找的雇员编号SELECT deptno,sal INTO v_dno,v_empSal FROM emp WHERE empno=v_eno;IF v_dno=10 THENIF v_empSal*1.1 5000 THENUPDATE emp SET sal=5000 WHERE empno=v_eno;ELSEUPDATE emp SET sal=sal*1.1 WHERE empno=v_eno;END IF;ELSIF v_dno=20 THENIF v_empSal*1.2 5000 THENUPDATE emp SET sal=5000 WHERE empno=v_eno;ELSEUPDATE emp SET sal=sal*1.2 WHERE empno=v_eno;END IF;ELSIF v_dno=30 THENIF v_empSal*1.3 5000 THENUPDATE emp SET sal=5000 WHERE empno=v_eno;ELSEUPDATE emp SET sal=sal*1.3 WHERE empno=v_eno;END IF;ELSEnull;END IF;END;7.4.1 分支结构分支结构CASE语句语句Case语句是一种多条件的判断语句，其功能与语句是一种多条件的判断语句，其功能与IFelseifelse类似类似Case语法：语法：Case变量变量 when 值值|表达式表达式 THEN 执行语句块；执行语句块；when 值值|表达式表达式 THEN 执行语句块；执行语句块；Else 条件都不满足时执行语块；条件都不满足时执行语块；End case7.4.1 分支结构分支结构范例：使用范例：使用CASE进进行多条件判断行多条件判断DECLAREv_salaryemp.sal%TYPE;v_enoemp.empno%TYPE;BEGINv_eno:=&inputEmpno;SELECT sal INTO v_salary FROM emp WHERE empno=v_eno;CASEWHEN v_salary=3000 THENDBMS_OUTPUT.put_line(雇员：雇员：|v_eno|的收入为高工资。的收入为高工资。);WHEN v_salary=2000 AND v_salary=3;v_i:=v_i+1;END LOOP;END;7.4.2 循环结构循环结构范例：使用范例：使用WHILELOOP循环循环DECLAREv_i NUMBER:=1;-定义一个变量，用于循环定义一个变量，用于循环BEGINWHILE(v_i 10 AND v_data 0 THEN-部门存在部门存在RAISE_APPLICATION_ERROR(-20888,此部门编号已存在，请重新输入！此部门编号已存在，请重新输入！);ELSE-部门不存在部门不存在INSERT INTO dept(deptno,dname,loc)VALUES(v_dno,v_dna,v_dloc);DBMS_OUTPUT.put_line(新部门增加成功！新部门增加成功！);COMMIT;END IF;EXCEPTIONWHEN others THENDBMS_OUTPUT.put_line(SQLERRM);ROLLBACK;END;范例：使用范例：使用PL/SQL增加部门信息增加部门信息