PLSQL经典教程PPT.ppt

Click to edit Master title style,First Level,Second Level,Third Level,Fourth Level,Fifth Level,1-,*,PL/SQL,程序设计简介,什么是,PL/SQL,PL/SQL,是,Procedure Language&Structured Query Language,（过程化,SQL,语言）的缩写,PL/SQL,是对,SQL,语言存储过程语言的扩展,PL/SQL,的优点,它最大的优点是提供了一种服务器端存储过程语言,，可以对数据进行快速高效的处理。,任何客户,/,服务器工具都能访问,PL/SQL,程序，具有很好的可重用性。,可以使用,ORACLE,数据工具管理存储在服务器中的,PL/SQL,程序的安全性。可以授权或撤销数据库其他用户访问,PL/SQL,程序的能力。,对于,SQL,，,ORACLE,必须在同一时间处理每一条,SQL,语句，在网络环境下这就意味着每一个独立的调用都必须被,oracle,服务器处理，这就占用大量的服务器时间，同时导致网络拥挤。而,PL/SQL,是以整个语句块发给服务器，这就降低了网络拥挤。,PL/SQL,块结构和组成元素,PL/SQL,程序由三个块组成，即声明部分、执行部分、,异常处理部分。,PL/SQL,块,PL/SQL,块结构和组成元素,PL/SQL,块的结构如下：,DECLARE,/*,声明部分,:,在此声明,PL/SQL,用到的变量,类型及游标，以及局部的存储过程和函数*,/,BEGIN,/*,执行部分,:,过程及,SQL,语句,即程序的主要部分*,/,EXCEPTION,/*,执行异常部分,:,错误处理 *,/,END;,其中 执行部分是必须的。,PL/SQL,块结构和组成元素,PL/SQL,块可以分成三类：,匿名块：动态构造，只能执行一次,子程序：存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们,触发器：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序,PL/SQL,中的标识符,PL/SQL,程序设计中的标识符定义与,SQL,的标识符定义的要求相同,标识符名不能超过,30,字符,第一个字符必须为字母,不能用,-,(,减号,),不分大小写,不能是,SQL,保留字,PL/SQL,中的标识符,一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果,例：,DECLARE,Ename varchar2(20):=,KING,;,BEGIN,DELETE FROM emp WHERE ename=ename;,END;,PL/SQL,中的标识符,下面是建议使用的变量命名方法,标识符,命名原则,例,程序变量,V_name,V_id,程序常量,C_constant,C_student_name,游标变量,Name_cursor,Emp_cursor,异常标识,E_name,E_too_many_rows,表类型,Name_table_type,Emp_record_type,表,Name_table,Emp,记录类型,Name_type,Emp_record,参数,P_name,P_id,练习：给,KING,的工资涨,10%,PL/SQL,中的变量类型,类型,说明,CHAR(n),定长字符串,长度可选,缺省,1,。最大可达,2000,字节,VARCHAR2(n),可变字符串,长度不能缺省,最大可达,4000,字节,BINARY_INTEGER,带符号整数,为整数计算优化性能,NUMBER(p,s),数值，,p,代表刻度，,s,代表精度,LONG,变长字符串,DATE,日期,BOOLEAN,布尔,ROWID,存放数据库行号,PL/SQL,中声明变量,在语句块的声明部分对变量声明，声明一个变量的语法是：,variable_name,constant,type not null :=value,其中：,variable_name,为变量名,type,为类型,value,为变量的初值,PL/SQL,中声明变量,例：,DECLARE,V_Description VARCHAR2(50);,V_Number NUMBER:=45;,V_Counter BINARY_INTEGER:=0;,注意：如果变量在声明时使用了,NOT NULL,选项则必须为变量指定初值。,如果变量在声明时使用了,CONSTANT,选项则必须为变量指定初值，并且该初值不能被改变。,练习：查询员工号为,7788,的员工姓名，并显示在控制台。,PL/SQL,中的复合类型,记录类型：记录类型是把逻辑相关的数据作为一个单元存储起来,定义记录类型语法如下：,TYPE record_type IS RECORD(,Field1 type1 NOT NULL :=exp1,Field2 type2 NOT NULL :=exp2,.,Fieldn typen NOT NULL :=expn );,练习,1,：创建一个图书记录类型（书号，书名），显示结果为：,书号：,1100,书名：,VB.Net,练习,2,：显示,7788,号员工的员工号，姓名，工资,PL/SQL,中的复合类型,%TYPE:,定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用,%TYPE,例：,DECLARE,-,用,%TYPE,类型定义与表相配的字段,TYPE t_Record IS RECORD(,T_no emp.empno%TYPE,T_name emp.ename%TYPE,T_sal emp.sal%TYPE);,-,声明接收数据的变量,v_emp t_Record;,PL/SQL,中的复合类型,%ROWTYPE:,返回一个记录类型,其数据类型和数据库表的数据结构相 一致。,例：,DECLARE,v_empno emp.empno%TYPE:=,rec emp%ROWTYPE;,BEGIN,SELECT*INTO rec FROM emp WHERE empno=v_empno;,DBMS_OUTPUT.PUT_LINE(,姓名,:|rec.ename|,工资,:|rec.sal|,工作时间,:|rec.hiredate);,END;,PL/SQL,表,PL/SQL,表或者称为索引表,(index-table),，是可以在,PL/SQL,程序中引用、能够模仿数组的非永久表。用户可以定义一个表类型，然后声明这种类型的变量。接下来，用户就可以将记录添加到用户的,PL/SQL,表中，并且采用与引用数组元素大体相同的方法引用他们,表包括两个基本成分：,数据处理类型为,BINARY_INTEGER,主键,标量或记录数据类型的列,PL/SQL,表,定义一个表：,TYPE type_name IS TABLE OF,column_type|variable%TYPE|table.column%TYPE NOT NULL|table%ROWTYPE,INDEX BY BINARY_INTEGER;,例：,DECLARE,TYPE dept_table_type IS TABLE OF,dept%ROWTYPE INDEX BY BINARY_INTEGER;,my_dname_table dept_table_type;,引用表元素,例：,DECLARE,type dept_table_type is table of,dept%ROWTYPE index by binary_integer;,my_dname_table dept_table_type;,int number(3);,BEGIN,int:=-10;,my_dname_table(int).dname:=searching;,dbms_output.put_line(my_dname_table(int).dname);,int:=0;,my_dname_table(int).dname:=market;,dbms_output.put_line(my_dname_table(int).dname);,int:=10;,my_dname_table(int).dname:=finance;,dbms_output.put_line(my_dname_table(int).dname);,END;,PL/SQL,表,注意：,Index-by,表中的元素不一定要按任何特定的顺序排序,用于,Index-by,表的关键字没有必要是顺序的,关键字唯一允许的类型是,BINARY_INTERGER,练习：定义数组元素为部门表的记录的数组，保存,10,号，,20,号部门的信息并显示。,PL/SQL,中的可变数组,一般格式为：,TYPE type_name IS VARRAY(maximum_size)OF element_type NOT NULL,其中：,type_name,是新可变长数组类型的类型名。,maximum_size,是一个指定可变数组中元素最大数目的整数。,element_type,是一个,PL/SQL,标量、记录或对象类型。,NOT NULL,子串表示集合中的每一个元素都必须有值。,一般格式为：,DECLARE,type numberlist is varray(10)of number(5);,type recordlist is varray(5)of dept%rowtype;,引用数组元素,例：,DECLARE,type strings is varray(5)of varchar2(10);,-Declare a varray with four element,v_list strings:=strings(scott,peter,smith,tom);,int number;,BEGIN,int:=1;dbms_output.put_line(v_list(int);,v_list(int):=urman;dbms_output.put_line(v_list(int);,int:=3;,dbms_output.put_line(v_list(int);,v_list(int):=jackson;,dbms_output.put_line(v_list(int);,v_list.extend;,v_list(5):=oracle;,dbms_output.put_line(v_list(5);,END;,PL/SQL,表和可变数组的区别,VARRAY,集合中的元素的数量是有限的，,Index_by,则是没有限制的。,Index_by,表的下标可以有间隔，,VARRAY,集合的下标之间没有间隔。,Index_by,表不能存储在数据库中，但嵌套表和,VARRAY,可以被存储在数据库中。,Index_by,表初始化是自动进行的，,VARRAY,就必须使用内建的构造函数，构造函数和集合的名字相同。,表和数组属性,属性,返回类型,描述,有效范围,Exists(n),boolean,指定索引为,n,的元素在集合中是否存在,表、可变数组,count,number,返回集合中元素的数目,表、可变数组,limit,number,返回集合中最大元素数,可变数组,First&last,Binary_integer,返回第一个（最后一个）元素的索引,表、可变数组,Next&prior,Binary_integer,返回当前元素的下一个（前一个）元素的索引,表、可变数组,extend,N/A,向集合末尾添加一个元素,可变数组,trim,N/A,从集合的最后删除一个元素,可变数组,Delete(n),N/A,删除元素下标为,n,的元素,表,表和数组属性,例：,DECLARE,type strings_table is table of varchar2(10)index by binary_integer;,strings strings_table;,int number;,BEGIN,int:=1;,strings(int):=element1;,if strings.exists(int)then,dbms_output.put_line(strings(int);,else,dbms_output.put_line(no data!);,return;,end if;,表和数组属性,例：,strings(2):=element2;,strings(3):=element3;,strings(4):=element4;,strings(5):=element5;,strings(6):=element6;,dbms_output.put_line(strings.count);,dbms_output.put_line(strings.first);,dbms_output.put_line(strings.last);,dbms_output.put_line(strings.next(2);,dbms_output.put_line(strings.prior(4);,strings.delete(1,3);,-dbms_output.put_line(strings(2);,END;,PL/SQL,运算符和表达式,关系运算符,运算符,意义,=,等于,!=,=,=,不等于,大于,=,大于等于,PL/SQL,运算符和表达式,一般运算符,运算符,意义,+,加号,-,减号,*,乘号,/,除号,:=,赋值号,|,连接符,.,范围运算符,PL/SQL,运算符和表达式,逻辑运算符,运算符,意义,IS NULL,空值,BETWEEN AND,介于两者之间,IN,在一个值列表之中,AND,逻辑与,OR,逻辑或,NOT,取返,如,IS NOT NULL,NOT IN,PL/SQL,中的变量赋值,在,PL/SQL,编程中，变量赋值是一个值得注意的地方，它的语法如下：,variable :=expression;,variable,是一个,PL/SQL,变量,expression,是一个,PL/SQL,表达式,.,BOOLEAN,型变量赋值：,布尔值只有,TRUE,FALSE,及,NULL,三个值。,空值加数字仍是空值：,NULL+=NULL,。,空值加（连接）字符，结果为字符：,NULL|=,PL/SQL,中的变量赋值,例：,DECLARE,done BOOLEAN;,/*the following statements are legal:*/,BEGIN,done:=FALSE;,WHILE NOT done LOOP,Null;,END LOOP;,END;,PL/SQL,中的,变量作用范围及可见性,PL/SQL,的变量作用范围特点是：,变量的作用范围是在所引用的程序单元（块、子程序、包）内。即从声明变量开始到该块的结束,一个变量（标识）只能在所引用的块内是可见的,当一个变量超出了作用范围，,PL/SQL,引擎就释放用来存放该变量的空间（因为它可能不用了）,在子块中重新定义该变量后，它的作用仅在该块内,PL/SQL,中的,变量作用范围及可见性,例：,DECLARE,v_Number NUMBER(3,2);,BEGIN,DECLARE,v_Character VARCHAR2(10);,BEGIN,END;,END;,v_Number,的作用域,v_Character,的作用域,PL/SQL,中的注释,在,PL/SQL,里，可以使用两种符号来写注释：,PL/SQL,允许用双,来写注释，它的作用范围是只能在一行有效。,例：,V_Sal NUMBER(12,2);-,工资变量,使用,/*/,来加一行或多行注释。,例：,/*/,/*,文件名：,department_salary.sql */,/*/,PL/SQL,简介,例：,简单数据查询例子,DECLARE,emp_id emp.empno%TYPE:=7788;,emp_name emp.ename%TYPE;,wages emp.sal%TYPE;,BEGIN,SELECT ename,NVL(sal,0)+NVL(comm,0)INTO emp_name,wages,FROM emp WHERE empno=emp_id;,DBMS_OUTPUT.PUT_LINE(emp_name|,-,|to_char(wages);,END;,PL/SQL,简介,例：,简单数据插入例子,/*,本例子仅是一个简单的插入，不是实际应用。*,/,DECLARE,v_ename VARCHAR2(20):=,Bill,;,v_sal NUMBER(7,2):=1234.56;,v_deptno NUMBER(2):=10;,v_empno NUMBER(4):=8888;,BEGIN,INSERT INTO emp(empno,ename,JOB,sal,deptno,hiredate),VALUES(v_empno,v_ename,Manager,v_sal,v_deptno,TO_DATE(,1954.06.09,yyyy.mm.dd,);,COMMIT;,END;,PL/SQL,简介,例：简单数据删除例子,/*,本例子仅是一个简单的删除例子，不是实际应用。*,/,DECLARE,v_empno number(4):=8888;,BEGIN,DELETE FROM emp WHERE empno=v_empno;,COMMIT;,END;,