Oracle基础-PLSQL基础知识.pptx

PLSQL基础知识基础知识郑欣SQL与PLSQLSQL是操作所有关系型数据库的规则，因为我们的数据大多都 存放在ORACLE数据库中，且对于关系型数据库而言，只能用SQL规则去操作，所有需要在程序中获取数据库的数据进行操作，所以必须学习SQL。主要在程序开发中，对数据的存取进行的一系统操作。容易调试，错误提示，直截了当SQL强调结果，忽略了过程。SQL与PLSQLPLSQL是专用于ORACLE服务器，在SQL基础之上，添加了一些过程化控制语句。过程化包括有：类型定义，判断，循环，游标，异常或例外处理。ORACLE数据库服务器对SQL做了符合自身的特点的扩展。即PLSQL只能在ORACLE数据库服务器中运行因此：PLSQL=SQL+ORACLE自身的扩展，PLSQL强调过程为什么要用PLSQL因为SQL是第四代命令式语言，无法显示处理过程化的业务，所以得用一个过程化程序设计语言来弥补SQL的不足之处，SQL和PLSQL不是替代关系，是弥补关系PLSQL程序的完整组成结构如下declare 变量声明;变量声明;begin DML/TCL操作;exception 例外处理;例外处理;end;/注意：在PLSQL程序中，；号表示每条语句的结束，/表示整个PLSQL程序结束。执行一次后，可以输入“/”再执行一次。书写PLSQL的工具有（1）SQLPLUS工具（2）SQLDeveloper工具（3）第三方工具（PLSQL&其它）PLSQL与SQL执行有什么不同（1）SQL是单条执行的（2）PLSQL是整体执行的，不能单条执行，整个PLSQL结束用/，其中每条语句结束用；号写一个PLSQL程序输出hello world字符串，语法：dbms_output.put_line(需要输出的字符串);会自动换行。begin -向SQLPLUS客户端工具输出字符串 dbms_output.put_line(hello 你好);end;/写一个PLSQL程序注意：dbms_output是oracle中的一个输出对象put_line是上述对象的一个方法，用于输出一个字符串自动换行 设置显示PLSQL程序的执行结果，默认情况下，不显示PLSQL程序的执行结果，语法：set serverout on/off;set serveroutput on;变量及声明选择变量名称的规则变量必须以字母(AZ)开头其后跟可选的一个或多个字母、数字(09)或特殊字符$、#或_变量长度不超过30个字符变量名中不能有空格变量及声明声明变量或常量的通用语法变量名 数据类型 DEFAULT|:=值；如：Year_ratenumber:=1.23;Day_ratenumber default 0.08;Pi constant number(9):=3.1415926;变量及声明定义与表某个字段类型相同的类型，可以不知道字段的类型，可以根据原对象进行实时更新语法：变量名 表名.字段%TYPE；实际开发中常使用%type，而不常用%rowtype变量及声明例：输出7369号员工姓名和工资。declare -定义二个变量，分别装姓名和工资 pename emp.ename%type;psal emp.sal%type;begin -SQL语句 -select ename,sal from emp where empno=7369;-PLSQL语句，将ename的值放入pename变量中，sal的值放入psal变量中 select ename,sal into pename,psal from emp where empno=7369;-输出 dbms_output.put_line(7369号员工的姓名是|pename|,薪水是|psal);end;/变量及声明将一条记录的所有字段都保存到一个变量中的语法：变量 表名%ROWTYPE;例：定义行变量Declare emp_record emp%rowtype;BeginEnd/注意：定义成%rowtype的变量，必须要与表结构的字段完全一样。可以根据原对象进行实时更新变量及声明范例：输出7788号员工姓名和工资declare emp_record emp%rowtype;begin select*into emp_record from emp where empno=7788;dbms_output.put_line(7788号员工的姓名是|emp_record.ename|,薪水是|emp_record.sal);end;/变量作用范围及可见性变量作用范围及可见性变量的作用范围是在你所引用的程序单元（块、子程序、包）内。即从声明变量开始到该块的结束。一个变量（标识）只能在你所引用的块内是可见的。当一个变量超出了作用范围，PL/SQL引擎就释放用来存放该变量的空间（因为它可能不用了）。在子块中重新定义该变量后，它的作用仅在该块内。建议变量的命名方法：标识符标识符命名规则命名规则例子例子程序变量程序变量V_nameV_name程序常量程序常量C_NameC_company_name游标变量游标变量Name_cursorEmp_cursor异常标识异常标识E_nameE_too_many表类型表类型Name_table_typeEmp_record_type表表Name_tableEmp记录类型记录类型Name_recordEmp_recordSQL*Plus 替代变量替代变量P_nameP_sal绑定变量绑定变量G_nameG_year_sal常用运算符、表达式 类型字符说明数学运算符+-*/*加号减号乘号除号乘幂布尔运算符=相等不等于小于大于小于等于大于等于常用运算符、表达式 类型字符说明其他符号.|&“”范围运算符字符串连接远程数据库指示符字符串起始符号字符串结束符号捆绑变量指示器引用开始符号引用结束符号PL/SQL的控制结构选择条件结构循环结构顺序结构包括如下三类:条件控制语句:IF 语句循环语句:LOOP语句,EXIT语句顺序语句:GOTO语句,NULL语句 选择结构 1)IF THEN PL/SQL 和 SQL语句 END IF;2)IF THEN PL/SQL 和 SQL语句 ELSE 其它语句 END IF;选择结构 3)IF THENPL/SQL 和 SQL语句ELSIF THEN其它语句ELSIF THEN其它语句ELSE其它语句END IF;选择结构 范例：使用if-else-end if显示今天星期几，是工作日还是休息日“declare pday varchar2(10);begin select to_char(sysdate,day)into pday from dual;dbms_output.put_line(今天是|pday);if pday in(星期六,星期日)thendbms_output.put_line(休息日);elsedbms_output.put_line(工作日);end if;end;/选择结构 从键盘接收值，使用if-elsif-else-end if显示age16，age30，age60，age80“declare age number(3):=&age;begin if age 16 then dbms_output.put_line(你未成人);elsif age 30 then dbms_output.put_line(你青年人);elsif age 60 then dbms_output.put_line(你奋斗人);elsif age 80 then dbms_output.put_line(你享受人);else dbms_output.put_line(未完再继);end if;end;/NULL结构空操作或空值结构 处理方法if count60 then Null；else INSERT INTO member VALUES(1,jack)；end if；循环结构1.简单循环简单循环LOOP 要执行的语句;EXIT WHEN ;/*条件满足，退出循环语句*/END LOOP;循环结构例：使用loop循环显示1-10DECLARE int NUMBER(2):=0;BEGIN LOOP int:=int+1;DBMS_OUTPUT.PUT_LINE(int 的当前值为:|int);EXIT WHEN int=10;END LOOP;END;循环结构 2、WHILE 循环循环WHILE LOOP 要执行的语句;END LOOP;循环结构例：使用while循环显示1-10DECLARE x NUMBER;BEGIN x:=1;WHILE x10 LOOP DBMS_OUTPUT.PUT_LINE(X的当前值为:|x);x:=x+1;END LOOP;END;循环结构使用while循环，向emp表中插入999条记录declare i number(4):=1;begin while(i 1000)loop insert into emp(empno,ename)values(i,哈哈);i:=i+1;end loop;end;/循环结构 2、FOR循环循环FOR 循环计数器 IN REVERSE 下限.上限 LOOP 要执行的语句;END LOOP;每循环一次，循环变量自动加1；使用关键字REVERSE，循环变量自动减1。跟在IN REVERSE 后面的数字必须是从小到大的顺序，而且必须是整数，不能是变量或表达式。可以使用EXIT 退出循环。注意，FOR循环不能自己设置步长。循环结构例：使用for循环显示20-30declare i number(2):=20;begin for i in 20.30 loop dbms_output.put_line(i);end loop;end;/请大家使用FOR循环删除emp表中的999记录请大家使用循环插入10万条记录看一下时间。Set timing on显示执行时间循环结构范例：DECLARE int NUMBER(2):=0;BEGIN LOOP int:=int+1;DBMS_OUTPUT.PUT_LINE(int 的当前值为:|int);EXIT WHEN int=10;END LOOP;END;PLSQL游标/光标从上向下依次获取每一记录的内容。游标分类：无参游标有参游标游标的属性游标的属性%FOUND 布尔型属性，当最近一次读记录时成功返回,则值为TRUE；%NOTFOUND 布尔型属性，与%FOUND相反；%ISOPEN 布尔型属性，当游标已打开时返回 TRUE；%ROWCOUNT 数字型属性，返回已从游标中读取的记录数。游标的基本操作(1)声明游标，使用查询来定义游标的列和行 语法：CURSOR 游标名(参数名 数据类型,)IS SELECT 语句;用于存储一个查询返回的多行数据例：cursor emp_c is select empno,ename from emp;游标的基本操作(2)打开游标，使用PL/SQL命令OPEN来打开一个声明的游标语法：OPEN 游标名;例：open emp_c;游标的基本操作(3)提取数据，从游标中重复提取每条记录到数据结构中，会自动向下移动，直到数据集合被提空语法：FETCH 游标 INTO 变量;例：Declare eno emp.empno%type;ena emp.ename%type;fetch emp_c into eno,ena;游标的基本操作(4)关闭游标，使用完游标之后将其关闭语法：CLOSE 游标;例：close emp_c;游标的基本操作范例：使用无参光标cursor，查询所有员工的姓名和工资【如果需要遍历多条记录时，使用光标cursor，无记录找到使用cemp%notfound】游标的基本操作declare -定义游标 cursor cemp is select ename,sal from emp;-定义变量 vename emp.ename%type;vsal emp.sal%type;begin -打开游标，这时游标位于第一条记录之前 open cemp;-循环 loop -向下移动游标一次 fetch cemp into vename,vsal;-退出循环,当游标下移一次后，找不到记录时，则退出循环 exit when cemp%notfound;-输出结果 dbms_output.put_line(vename|-|vsal);end loop;-关闭游标 close cemp;end;/游标的基本操作范例：使用带参光标cursor，查询10号部门的员工姓名和工资declare cursor cemp(pdeptno emp.deptno%type)is select ename,sal from emp where deptno=pdeptno;pename emp.ename%type;psal emp.sal%type;begin open cemp(&deptno);loop fetch cemp into pename,psal;exit when cemp%notfound;dbms_output.put_line(pename|的薪水是|psal);end loop;close cemp;end;/游标的基本操作范例：使用无参光标cursor，真正给员工涨工资，ANALYST涨1000，MANAGER涨800，其它涨400，要求显示编号，姓名，职位，薪水declare cursor cemp is select empno,ename,job,sal from emp;pempno emp.empno%type;pename emp.ename%type;pjob emp.job%type;psal emp.sal%type;begin open cemp;loop fetch cemp into pempno,pename,pjob,psal;-循环退出条件一定要写 exit when cemp%notfound;if pjob=ANALYST then update emp set sal=sal+1000 where empno=pempno;elsif pjob=MANAGER then update emp set sal=sal+800 where empno=pempno;else update emp set sal=sal+400 where empno=pempno;end if;end loop;commit;close cemp;end;/认识例外（异常）是程序设计语言提供的一种功能，用来增加程序的健壮性和容错性。简单说：就是异常处理操作。例外分类：内置(系统)例外、自定义例外系统例外no_data_found(没有找到数据)too_many_rows(select into语句匹配多个行)zero divide (被零除)value_error(算术或转换错误)timeout_on_resource(在等待资源时超时)捕获和处理例外当出现例外（异常）后会导致错误，捕获就是在出现例外时自动跳到处理例外的程序段对例外进行处理的过程。捕获语法：DeclareBegin正常语句;Exception-捕获 处理例外;End;/系统例外范例：使用oracle系统内置例外，演示除0例外【zero_divide】declare myresult number;begin myresult:=1/0;dbms_output.put_line(myresult);exception when zero_divide then dbms_output.put_line(除数不能为0);delete from emp;end;/系统例外范例：使用oracle系统内置例外，查询100号部门的员工姓名，演示没有找到数据【no_data_found】declare pename varchar2(20);begin select ename into pename from emp where deptno=100;dbms_output.put_line(pename);exception when NO_DATA_FOUND then dbms_output.put_line(查无该部门员工);insert into emp(empno,ename)values(1111,ERROR);end;/自定义例外及处理例外1、在DECLARE语句段中定义例外语法：例外名 EXCEPTION;例：out_of exception;2、在begin节中可行语句中抛出例外语法：RAISE 例外名;例：raise out_of;3、在exception语句段中处理例外语法：WHEN 例外名 THE 处理语句;自定义例外范例：使用用户自定义例外，使用光标cursor，查询10/20/30/100号部门的员工姓名，演示没有找到数据【nohave_emp_found】自定义例外Declare cursor cemp(pdeptno number)is select ename from emp where deptno=pdeptno;Begin open cemp(10);fetch cemp into pename;if cemp%notfound then raise nohave_emp_found;else dbms_output.put_line(pename);loop fetch cemp into pename;exit when cemp%notfound;dbms_output.put_line(pename);end loop end if;close cemp;Exception when nohave_emp_found then dbms_output.put_line(查无此部门员工);End;/