3oracle_plsql基础1392.pptx

《3oracle_plsql基础1392.pptx》由会员分享，可在线阅读，更多相关《3oracle_plsql基础1392.pptx（95页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Oracle课程课程PL/SQL基础基础2/27教学提示教学提示熟悉PL/SQL的程序结构，掌握控制结构和TAPE与%ROWTYPE的使用学会游标的操作与定义及过程函数的运用3/27教学目标教学目标能运用PL/SQL语句编写简单的程序熟练使用控制结构语句知道游标、过程函数、触发器的作用能够定义过程游标和建立触发器通过上机熟练掌握PL/SQL4/27PL/SQL概述概述PL/SQL（Procedureal Language/SQL,过程化过程化SQL语言）语言）在标准在标准SQL语言下发展起来的语言下发展起来的PL/SQL语言将变量、控制结构、过程和函语言将变量、控制结构、过程和函数等结构化程序

2、设计的要素引入到数等结构化程序设计的要素引入到SQL语语言中言中它的特点是它的特点是:具有模块化的结构、使用过程具有模块化的结构、使用过程化语言控制结构、能够进行错误处理化语言控制结构、能够进行错误处理是一个块结构，划分为：变量定义、逻辑是一个块结构，划分为：变量定义、逻辑处理、异常处理三块。处理、异常处理三块。5/27声明及变量声明及变量1.由由DECLARE关键字开始关键字开始2.BEGIN程序体结束程序体结束3.强类型语言强类型语言4.Declare 5.v_id integer;6.v_name varchar(20);7.begin6/27声明及变量声明及变量Varriable_na

3、me CONSTANT databyte NOT NULL:=|DEFAULT expression如果有了如果有了NOT NULL约束条件，则初始化必约束条件，则初始化必须赋值须赋值直接赋值直接赋值通过通过SQL SELECT INTO或或FETCH INTO给变量给变量赋值赋值7/27表达式表达式declareresult number;beginresult:=10+3*4+40;dbms_output.put_line(result:|result);end;执行结果为：Result:62PL/SQL过程已成功完成。Dbms_output.put_line函数是输出后换行,put是输出

4、不换行.8/27PL/SQL程序块结构程序块结构声明部分声明部分(declarative section)执行部分执行部分(executable section)异常处理部分异常处理部分(exception section)Declare 声明部分声明部分Begin 执行部分执行部分Exception 异常执行部分异常执行部分End;9/27PL/SQL程序块结构程序块结构注释注释:单行注释单行注释(-),多行注释多行注释(/*/)标识符标识符 标识符由一个字母开始,后面选折性地跟随任意多的字母,数字,货币符号($),下划线(_),#等符号组成.不允许使用空格,斜线(/),短横线(-),&,%

5、.最大长度为30个字符.字符集字符集大写字母-和小写字母a-b 数字0-9 符号(,),+,-,*,/,=,!,;,:,.,”,#,$,_,|,?,制表符,空格符,回车符等非显示的间空符号.其中一些字符用于编程，另外一些用做算术运算符或关系运算符10/27PL/SQL运行环境运行环境SQL*PLUSTOADPL/SQL Developer11/27PL/SQL程序块示例程序块示例declare v_checkout timestamp(6);begin v_checkout:=sysdate;dbms_output.put_line(v_checkout);end;declare v_life

6、time interval year(4)to month;aa date;begin v_lifetime:=interval 0001-3 year to month;select sysdate+v_lifetime into aa from dual;dbms_output.put_line(aa);end;12/27PL/SQL数据类型数据类型建表数据类型建表兼容类型建表数据类型建表兼容类型Boolean+%type+%rowtype+%record+table13/27%typedeclare v_ename emp.ename%type;v_sal emp.sal%type;c_

7、tax_rate constant number(3,2):=0.03;v_sal_tax v_sal%type;begin select ename,sal into v_ename,v_sal from emp where empno=&eno;v_sal_tax:=v_sal*c_tax_rate;dbms_output.put_line(雇员名字:|v_ename);dbms_output.put_line(雇员薪水:|v_sal);dbms_output.put_line(雇员所得税:|v_sal_tax);end;14/27%rowtypedeclare v_emp emp%row

8、type;begin select*into v_emp from emp where empno=&eno;dbms_output.put_line(雇员名字:|v_emp.ename);dbms_output.put_line(雇员薪水:|v_emp.sal);dbms_output.put_line(雇员工种|v_emp.job);end;15/27record-类似于structure-定义的语法 type record_type is record(v1 data_type1,v2 data_type2,vn data_typen);declare type emp_record_t

9、ype is record(name emp.ename%type,salary emp.sal%type,job emp.job%type);emp_record emp_record_type;begin select ename,sal,job into emp_record from emp where empno=&eno;dbms_output.put_line(雇员名:|emp_record.name);dbms_output.put_line(工资:|emp_record.salary);dbms_output.put_line(岗位:|emp_record.job);end;

10、16/27table-类似于数组-定义的语法 type table_type is table of data_type index by binary_integer;declare type emp_table_type is table of emp.ename%type index by binary_integer;emp_table emp_table_type;begin select ename into emp_table(-1)from emp where empno=&eno;emp_table(0):=jack;emp_table(1):=lucy;dbms_outpu

11、t.put_line(emp_table(-1)雇员名:|emp_table(-1);dbms_output.put_line(emp_table(0):|emp_table(0);dbms_output.put_line(emp_table(1)|emp_table(1);end;17/27table-这个就是纯粹意义上的数组了。DECLARE TYPE array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;firstnumber array;secondnumber array;BEGIN firstnumber(0):=123456;first

12、number(1):=888888;secondnumber(0):=222222;secondnumber(1):=666666;FOR i IN 0.firstnumber.count-1 LOOP DBMS_OUTPUT.PUT_LINE(i=|i|,firstnumber0=|firstnumber(i)|,secondnumber=|secondnumber(i);END LOOP;END;18/27流程控制流程控制条件控制条件控制循环控制循环控制顺序控制顺序控制19/27条件控制条件控制语法：IF condition1 THENstatements1ELSIF condition2

13、 THENstatements2ELSEstatements3END IF;20/27条件控制条件控制declare v_sal number(7,2);begin select sal into v_sal from emp where ename=SCOTT;dbms_output.put_line(scott salary:|v_sal);if v_sal100 then dbms_output.put_line(scott 工资低于1000);else if 1000=v_sal and v_sal2000 then dbms_output.put_line(scott1在1000到2

14、000之间);else dbms_output.put_line(scott1工资高于2000);end if;end if;end;21/27条件控制条件控制CASE 结构CASE WHEN 条件表达式1 THEN 语句段1 WHEN 条件表达式2 THEN 语句段2 ELSE 语句段NEND CASE;22/27条件控制条件控制declare v_sal number(7,2);begin select sal into v_sal from emp where ename=SCOTT;dbms_output.put_line(scott工资:|v_sal);case when v_sal

15、1000 then dbms_output.put_line(scott工资低于1000);when 1000=v_sal and v_sal2000 then dbms_output.put_line(scott工资在1000到2000之间);else dbms_output.put_line(scott工资高于2000);end case;end;23/27条件控制条件控制declare v_grade char:=a;begin case v_grade when a then dbms_output.put_line(excellent);when b then dbms_output

16、.put_line(very good);when c then dbms_output.put_line(good);else dbms_output.put_line(no such grade);end case;end;24/27循环控制控制1.简单循环须显示结束循环须显示结束循环2.while 循环直到条件不满足时结束循环直到条件不满足时结束循环3.for 循环循环预先确定的次数循环预先确定的次数25/27循环控制控制简单循环 LOOP statementEND LOOP 循环标签循环标签;跳出循环跳出循环:EXIT 循环标签循环标签EXIT 循环标签循环标签 WHEN 条件表达式条

17、件表达式26/27循环控制控制declare v_i number:=1;v_s number:=0;begin loop exit when v_i100;v_s:=v_s+v_i;v_i:=v_i+1;end loop;dbms_output.put_line(v_s);end;27/27循环控制控制while 循环 WHILE 条件表达式 LOOP 语句段 END LOOP 循环标签;28/27循环控制控制declare v_i number:=1;v_s number:=0;begin while v_i=100 loop v_s:=v_s+v_i;v_i:=v_i+1;end loo

18、p;dbms_output.put_line(v_s);end;29/27循环控制控制for 循环循环 FOR 循环变量 IN REVERSE 初始表达式.终值表达式 LOOP 语句段 END LOOP 循环标签;30/27循环控制控制declare v_s number:=0;begin for v_i in 1.100 loop v_s:=v_s+v_i;end loop;dbms_output.put_line(v_s);end;31/27循环控制控制循环嵌套循环嵌套WHILE 条件1 LOOP 语句段11 WHILE 条件2 LOOP 语句段2 END LOOP;语句段12 END L

19、OOP;32/27循环控制控制循环嵌套循环嵌套 LOOP 语句段11 WHILE 条件1 LOOP 语句段2 END LOOP;语句段12 EXIT WHEN 条件2 END LOOP;33/27循环控制控制-计算计算100-110之间的素数之间的素数-对于一个整数对于一个整数m而言，把而言，把m分别用分别用2到到(m-1)的整数的整数除，如果能被其中任何除，如果能被其中任何一个数整除，则表示一个数整除，则表示m不是素数不是素数,素数肯定是奇素数肯定是奇数数declare v_m number:=101;v_i number;v_n number:=0;begin while v_mv_m-1

20、;end loop;if v_i0 then v_n:=v_n+1;dbms_output.put_line(v_n|v_m);end if;v_m:=v_m+2;end loop;end;34/27循环控制控制循环嵌套循环嵌套 FOR 计数器 IN 上界下界 LOOP 语句段11 LOOP 语句段2 EXIT WHEN 条件1 END LOOP;语句段12 END LOOP;35/27循环控制控制-用嵌套用嵌套for循环改写循环改写declare label number:=0;begin for v_s in 101.110 loop for v_i in 2.v_s-1 loop if

21、mod(v_s,v_i)=0 then label:=0;exit;end if;label:=label+1;end loop;if label0 then dbms_output.put_line(v_s);end if;end loop;end;36/27顺序控制顺序控制语法:GOTO 标签NULL 语句 语法:NULL (表示什么也不执行)37/27顺序控制例子顺序控制例子declare v_i number:=0;v_s number:=0;begin v_i:=v_i+1;if v_i=100 then v_s:=v_s+v_i;goto label_1;end if;dbms_o

22、utput.put_line(v_s);end;38/27顺序控制例子顺序控制例子declare v_empno emp.empno%type;v_ename emp.ename%type;v_sal emp.sal%type;begin v_empno:=&emp_no;select ename,sal into v_ename,v_sal from emp where empno=v_empno;if v_sal=3000 then update emp set comm=v_sal*01 where empno=v_empno;dbms_output.put_line(v_ename|补

23、助|v_sal*0.1);else null;end if;end;39/27异常处理异常处理Exception when 异常错误名称1 or 异常错误名称2 then 语句段1 when 异常错误名称3 or 异常错误名称4 then 语句段2 when others then 语句段3End;40/27异常处理例子异常处理例子declare v_ename emp.ename%type;begin select ename into v_ename from emp where empno=&emp_no;dbms_output.put_line(雇员名|v_ename);excepti

24、on when no_data_found then dbms_output.put_line(雇员号不存在,请重新输入|v_ename);end;41/27预定义异常预定义异常异常错误名称错误代码描述access_into_null-6530当开发对象类型应用时，在引用对象属性之前，必须首先初始化对象。如果试图给一个没有初始化的对象属性赋值，就会引发该异常错误cast_not_found-6592在CASE语句中没有包含必需的WHen子句，并且没有包含ELSE子句cursor_alread_open-6511试图打开一个已经打开的游标。一个游标在被重复打开之前必须关闭。一个游标FOR循环会自

25、动打开所涉及的游标，所以在游标循环中不能打开游标。dup_val_on_index-1向有唯一性索引约束的列插入重复值invalid_cursor-1001试图执行一个无效的游标操作42/27预定义异常预定义异常异常错误名称错误代码描述invalid_number-1722将字符串转换为数字时失败。在过程性语句中，将引发VALUE_ERROR错误login_denied-1017用无效的用户名或口令登录ORACLEno_data_found+100SELECT INTO 语句没有返回任何行，或者程序引用一个嵌套表中已经被删除的元素，或索引表中一个没有被初始化的元素not_logged_on-0

26、1012在没有登录ORACLE数据的情况下，访问数据库program_error-6501ORACLE内在错误，通常是由PL/SQL本身造成的，这种情况下应该通知ORACLE公司的技术部门43/27预定义异常预定义异常异常错误名称错误代码描述storage_error-6500PL/SQL程序在运行时内存不够或者内存有问题timeout_on_resource-51ORACLE在等待资源时发生超时现象too_many_rows-1422SELECT INTO 语句返回多行value_error-6502发生了一个算法、转换、截断或者大小约束错误。如果在一个SQL语句中发生这些错误，则会引发IN

27、VALID_ERROR错误zero_divide-1476发生被0除的错误44/27预定义异常的例子预定义异常的例子declare v_sal emp.sal%type;begin select sal into v_sal from emp where empno=&emp_no;case when v_sal1000 then update emp set sal=v_sal+100 where empno=&emp_no;when v_sal=2500 then dbms_output.put_line(该雇员工资:|v_sal);raise e_sal_error;end if;exc

28、eption when e_sal_error then dbms_output.put_line(该雇员工资高于2500了);end;51/27自定义错误代码及其消息文本语法：Raise_application_error(error_code,message,ture|false其中:Error_code 是自定义的错误代码（-2000-20999）之间Messagebox是自定义错误消息True 将自定义错误消息添加到错误消息栈中52/27自定义错误代码例子create or replace procedure query_comm_if_null(v_no in emp.empno%t

29、ype)isv_comm m%type;begin select comm into v_comm from emp where empno=v_no;if v_comm is null or v_comm=0 then raise_application_error(-20001,该雇员无补助);end if;exception when no_data_found then dbms_output.put_line(没有该雇员:|v_no);end query_comm_if_null;53/27自定义错误代码例子declare e_comm_null exception;pragma e

30、xception_init(e_comm_null,-20001);begin dbms_output.put_line(雇员编号:5678);begin query_comm_if_null(5678);exception when e_comm_null then dbms_output.put_line(该雇员无补助);end block_1;dbms_output.put_line(雇员编号:7788);begin query_comm_if_null(7788);exception when e_comm_null then dbms_output.put_line(该雇员无补助);

31、end block_2;end;54/27过程过程语法语法:Create or replace procedure proc_name(para_name in|out|in out type ,.)is|as声明部分声明部分begin执行部分执行部分;EXCEPTION 异常处理部分异常处理部分end proc_name;55/27定义过程定义过程create or replace procedure pro_name(v_no in emp.empno%type,v_name out emp.ename%type,v_sal out emp.sal%type)ise_sal_error e

32、xception;begin select ename,sal into v_name,v_sal from emp where empno=v_no;if v_sal2500 then dbms_output.put_line(soijdiofj|v_sal);raise e_sal_error;end if;exception when no_data_found then dbms_output.put_line(djjf|v_no);when e_sal_error then dbms_output.put_line(dfdfj);end pro_name;56/27执行过程执行过程-

33、在pl/sql中declare v_a1 emp.ename%type;v_a2 emp.sal%type;begin query_emp(v_no=5678,v_name=v_a1,v_sal=v_a2);end;57/27存储过程的查看与删除Select text from user_source where name=QUERY_EMP;-删除存储过程Drop procedure query_emp;58/27函数函数用于计算和返回特定的数据.可以将经常需要进行的计算写成函数.函数的调用是表达式的一部分,而过程的调用是一条PL/SQL语句.语法：create or replace fun

34、ction fun_name(para_name in|out|in out)type,.)return typeis|as 声明部分beginstatement;end fun_name;59/27定义函数create or replace function get_salary_by_deptno(v_dept_no in emp.deptno%type,v_emp_cnt out number)return numberis v_sum number(10,2);begin select sum(sal),count(*)into v_sum,v_emp_cnt from emp whe

35、re deptno=v_dept_no;return v_sum;end get_salary_by_deptno;60/27执行函数-pl/sql中调用函数declare v_a1 emp.deptno%type;v_a2 number;v_sum number(10,2);begin v_sum:=get_salary_by_deptno(v_emp_cnt=v_a1,v_dept_no=10);if v_a1=0 then dbms_output.put_line(该部门无人);else dbms_output.put_line(该部门工资总和:|v_sum|人数|v_a2);end i

36、f;end;61/27函数的查看与删除-函数的查看select text from user_source where name=GET_SALARY_BY_DEPTNO-删除drop function get_salary_by_deptno;62/27游标游标处理多行结果集的查询，可以使用游标。步骤为：声明打开取行关闭游标declare tempsal emp.sal%type;-1cursor tempcursor is select*from emp where saltempsal;cursorrecord tempcursor%rowtype;begin tempsal:=800;

37、-2 open tempcursor;-3 fetch tempcursor into cursorrecord;dbms_output.put_line(to_char(cursorrecord.deptno);dbms_output.put_line(to_char(cursorrecord.empno);-4 close tempcursor;end;63/27游标的属性游标的属性游标提供一些属性帮助编写PL/SQL程序，其使用方法为：游标名【属性】。例如：Mycursor%isopen64/27%isopen属性属性测试游标是否打开，如果没有打开游标就使用fetch语句，将提示错误信息

38、。刚刚的例子修改为：declare tempsal scott.emp.sal%type;cursor tempcursor is select*from scott.emp where saltempsal;cursorrecord tempcursor%rowtype;begin tempsal:=800;open tempcursor;if tempcursor%isopen then fetch tempcursor into cursorrecord;dbms_output.put_line(to_char(cursorrecord.deptno);else dbms_output.

39、put_line(游标没有打开!);end if;close tempcursor;end;65/27%found属性属性测试前一个fetch语句是否有值修改如下：declare tempsal scott.emp.sal%type;cursor tempcursor is select*from scott.emp where saltempsal;cursorrecord tempcursor%rowtype;begin tempsal:=800;open tempcursor;if tempcursor%isopen then fetch tempcursor into cursorre

40、cord;if tempcursor%found then dbms_output.put_line(to_char(cursorrecord.deptno);else dbms_output.put_line(没有取到值);end if;else dbms_output.put_line(游标没有打开!);end if;close tempcursor;end;66/27notfound属性属性%notfound是%found的反逻辑。67/27ROWCOUNT属性属性返回游标的行数修改如下：Declare tempsal scott.emp.sal%type;cursor tempcurs

41、or is select*from scott.emp where saltempsal;cursorrecord tempcursor%rowtype;rowcounts number;begin tempsal:=800;open tempcursor;fetch tempcursor into cursorrecord;if tempcursor%found then dbms_output.put_line(to_char(cursorrecord.deptno);dbms_output.put_line(to_char(tempcursor%rowcount);else dbms_o

42、utput.put_line(没有数据);end if;close tempcursor;end;68/27游标与while循环例子declare cursor tempcursor(tempsal emp.sal%type)is select*from emp where saltempsal;cursorrecord tempcursor%rowtype;begin open tempcursor(800);fetch tempcursor into cursorrecord;while tempcursor%found loop dbms_output.put_line(to_char(

43、cursorrecord.deptno|cursorrecord.ename);fetch tempcursor into cursorrecord;end loop;close tempcursor;end;69/27游标与FOR循环FOR var_name IN cursor_name LOOPEND LOOP;70/27游标与FOR循环例子declare cursor tempcursor(tempsal emp.sal%type)is select*from emp where saltempsal;begin for cursorrecord in tempcursor(800)lo

44、op dbms_output.put_line(to_char(cursorrecord.deptno|cursorrecord.ename);end loop;end;71/27使用游标更新或删除数据通过游标，还可以更新或者删除当前游标行的数据，必须使用for update选项。其语法格式为：Select column_list from table_list for update使用for update打开游标之后，就可以在在update和delete语句中使用where current of 子句，修改或删除游标结果集中当前行所对应的数据库表中的数据行了。其语法格式为：Where cur

45、rent of cursor_name72/27使用游标更新或删除数据例子declare v_emp_rec emp%rowtype;cursor c1 is select*from emp for update;begin open c1;loop fetch c1 into v_emp_rec;exit when c1%notfound;if v_emp_rec.deptno=30 then update emp set comm=800-修改数据 where current of c1;end if;end loop;commit;-提交已经修改的数据 close c1;end;73/2

46、7程序包说明语法：CREATE OR REPLACE PACKAGE package_name IS|AS PRAGMA SERIALLY_REUSABLE;公有数据类型定义 公有变量声明 公有常量声明 公有异常错误声明 公有游标,函数,过程声明END package_name;74/27程序包说明的例子create or replace package my_pkgispragma serially_reusable;v_deptrec dept%rowtype;v_sqlcode number;v_sqlerrm varchar2(2048);function add_dept(v_dep

47、tno number,v_deptname varchar2,v_deptloc varchar2)return number;function remove_dept(v_deptno number)return number;procedure query_dept(v_deptno number);procedure read_dept;end my_pkg;75/27程序包体语法：CREATE OR REPLACE PACKAGE BODY package_name IS|AS PRAGMA SERIALLY_REUSABLE;公有数据类型定义 公有变量声明 公有常量声明 公有异常错误

48、声明 公有游标,函数,过程声明BEGIN 执行部分(初始化部分)END package_name;例子在备注中76/27调用程序包-调用包的公有变量beginmy_pkg.v_sqlerrm:=message;DBMS_OUTPUT.PUT_LINE(my_pkg.v_sqlerrm);end;-调用包的公有函数Declare v1 NUMBER;beginv1:=my_pkg.add_dept(50,dept1,loc1);Dbms_output.put_line(v1);end;-调用包的公有过程beginmy_pkg.read_dept;end;77/27查看包源代码和删除包Select

49、 text from user_source where name=MY_PKG;-删除包体Drop package body package_name;-删除包说明Drop package package_name;78/27触发器触发器执行执行INSERT、UPDATE、DELETE时被激活时被激活实现对以上语句的监督实现对以上语句的监督可以在可以在SQL语句运行之前和之后被激活语句运行之前和之后被激活分为行级触发器和语句级触发器分为行级触发器和语句级触发器行级触发器：对每一行运行时都会被激活一次行级触发器：对每一行运行时都会被激活一次语句级触发器：对于所有行运行时被激活一次语句级触发器：

50、对于所有行运行时被激活一次79/27语句级触发器CREATE OR REPLACE TRIGGER trig_nameBEFORE|AFTER INSERT|DELETE|UPDATE or INSERT|DELETE|UPDATE.ON tab_namePL/SQL_block|CALL procedure_name;80/27语句级触发器例子create or replace trigger my_trigerbefore insert or update or deleteon empbegin if(to_char(sysdate,day)in(星期六,星期日)or(to_char(s