3oracle_plsql基础

《3oracle_plsql基础》由会员分享，可在线阅读，更多相关《3oracle_plsql基础（95页珍藏版）》请在装配图网上搜索。

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

2、中言中它的特点是它的特点是:具有模块化的结构、使用过程具有模块化的结构、使用过程化语言控制结构、能够进行错误处理化语言控制结构、能够进行错误处理是一个块结构，划分为：变量定义、逻辑是一个块结构，划分为：变量定义、逻辑处理、异常处理三块。处理、异常处理三块。5/271.由由DECLARE关键字开始关键字开始2.BEGIN程序体结束程序体结束3.强类型语言强类型语言4.Declare 5.v_id integer;6.v_name varchar(20);7.begin6/27Varriable_name CONSTANT databyte NOT NULL:=|DEFAULT expressio

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

4、le section)异常处理部分异常处理部分(exception section)Declare 声明部分声明部分 Begin 执行部分执行部分 Exception 异常执行部分异常执行部分 End;9/27 注释注释:单行注释单行注释(-),多行注释多行注释(/*/)标识符标识符 标识符由一个字母开始,后面选折性地跟随任意多的字母,数字,货币符号($),下划线(_),#等符号组成.不允许使用空格,斜线(/),短横线(-),&,%.最大长度为30个字符.字符集字符集大写字母-和小写字母a-b 数字0-9 符号(,),+,-,*,/,=,!,;,:,.,”,#,$,_,|,?,制表符,空格符,

5、回车符等非显示的间空符号.其中一些字符用于编程，另外一些用做算术运算符或关系运算符10/27SQL*PLUSTOADPL/SQL Developer11/27declare v_checkout timestamp(6);begin v_checkout:=sysdate;dbms_output.put_line(v_checkout);end;declare v_lifetime interval year(4)to month;aa date;begin v_lifetime:=interval 0001-3 year to month;select sysdate+v_lifetime

6、into aa from dual;dbms_output.put_line(aa);end;12/27建表数据类型建表兼容类型建表数据类型建表兼容类型Boolean+%type+%rowtype+%record+table13/27declare v_ename emp.ename%type;v_sal emp.sal%type;c_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_

7、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/27declare v_emp emp%rowtype;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_o

8、utput.put_line(雇员工种|v_emp.job);end;15/27-类似于structure-定义的语法 type record_type is record(v1 data_type1,v2 data_type2,vn data_typen);declare type emp_record_type 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 fro

9、m 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;16/27-类似于数组-定义的语法 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 b

10、inary_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_output.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/27-这个

11、就是纯粹意义上的数组了。DECLARE TYPE array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;firstnumber array;secondnumber array;BEGIN firstnumber(0):=123456;firstnumber(1):=888888;secondnumber(0):=222222;secondnumber(1):=666666;FOR i IN 0.firstnumber.count-1 LOOP DBMS_OUTPUT.PUT_LINE(i=|i|,firstnumber0=|firstnumber(

12、i)|,secondnumber=|secondnumber(i);END LOOP;END;18/27 条件控制条件控制 循环控制循环控制 顺序控制顺序控制19/27 语法：IF condition1 THENstatements1ELSIF condition2 THENstatements2ELSEstatements3END IF;20/27declare v_sal number(7,2);begin select sal into v_sal from emp where ename=SCOTT;dbms_output.put_line(scott salary:|v_sal);i

13、f v_sal100 then dbms_output.put_line(scott 工资低于1000);else if 1000=v_sal and v_sal2000 then dbms_output.put_line(scott1在1000到2000之间);else dbms_output.put_line(scott1工资高于2000);end if;end if;end;21/27CASE 结构CASE WHEN 条件表达式1 THEN 语句段1 WHEN 条件表达式2 THEN 语句段2 ELSE 语句段NEND CASE;22/27declare v_sal number(7,2

14、);begin select sal into v_sal from emp where ename=SCOTT;dbms_output.put_line(scott工资:|v_sal);case when v_sal1000 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/27declare v

15、_grade char:=a;begin case v_grade when a then dbms_output.put_line(excellent);when b then dbms_output.put_line(very good);when c then dbms_output.put_line(good);else dbms_output.put_line(no such grade);end case;end;24/271.简单循环 须显示结束循环须显示结束循环2.while 循环 直到条件不满足时结束循环直到条件不满足时结束循环3.for 循环 循环预先确定的次数循环预先确定

16、的次数25/27简单循环 LOOP statementEND LOOP 循环标签循环标签;跳出循环跳出循环:EXIT 循环标签循环标签EXIT 循环标签循环标签 WHEN 条件表达式条件表达式26/27declare 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/27while 循环 WHILE 条件表达式 LOOP 语句段 END LOOP 循环标签;28/27declare v_i number

17、:=1;v_s number:=0;begin while v_i=100 loop v_s:=v_s+v_i;v_i:=v_i+1;end loop;dbms_output.put_line(v_s);end;29/27for 循环循环 FOR 循环变量 IN REVERSE 初始表达式.终值表达式 LOOP 语句段 END LOOP 循环标签;30/27declare 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

18、 条件1 LOOP 语句段11 WHILE 条件2 LOOP 语句段2 END LOOP;语句段12 END LOOP;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

19、_i number;v_n number:=0;begin while v_mv_m-1;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

20、loop for v_i in 2.v_s-1 loop if 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/27declare 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;e

21、nd if;dbms_output.put_line(v_s);end;38/27declare 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|补

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

23、d 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循环会自动打开所涉及的游标，所以在游标循环中不能打开游标。dup_v

24、al_on_index-1向有唯一性索引约束的列插入重复值invalid_cursor-1001试图执行一个无效的游标操作42/27异常错误名称错误代码描述invalid_number-1722将字符串转换为数字时失败。在过程性语句中，将引发VALUE_ERROR错误login_denied-1017用无效的用户名或口令登录ORACLEno_data_found+100SELECT INTO 语句没有返回任何行，或者程序引用一个嵌套表中已经被删除的元素，或索引表中一个没有被初始化的元素not_logged_on-01012在没有登录ORACLE数据的情况下，访问数据库program_error

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

26、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;exception when e_sal_error then dbms_output.put_line(该雇员工资高于2500了);en

27、d;51/27语法：Raise_application_error(error_code,message,ture|false其中:Error_code 是自定义的错误代码（-2000-20999）之间Messagebox是自定义错误消息True 将自定义错误消息添加到错误消息栈中52/27create or replace procedure query_comm_if_null(v_no in emp.empno%type)isv_comm m%type;begin select comm into v_comm from emp where empno=v_no;if v_comm is

28、 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/27declare e_comm_null exception;pragma exception_init(e_comm_null,-20001);begin dbms_output.put_line(雇员编号:5678);begin query_comm_if_null(

29、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(该雇员无补助);end block_2;end;54/27语法语法:Create or replace procedure proc_name(para_name in|out|in out type ,.)i

30、s|as声明部分声明部分begin执行部分执行部分;EXCEPTION 异常处理部分异常处理部分end proc_name;55/27create 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 exception;begin select ename,sal into v_name,v_sal from emp where empno=v_no;if v_sal2500 then dbms_output.put

31、_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-在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/27Sele

32、ct text from user_source where name=QUERY_EMP;-删除存储过程Drop procedure query_emp;58/27函数用于计算和返回特定的数据.可以将经常需要进行的计算写成函数.函数的调用是表达式的一部分,而过程的调用是一条PL/SQL语句.语法：create or replace function fun_name(para_name in|out|in out)type,.)return typeis|as 声明部分beginstatement;end fun_name;59/27create or replace function ge

33、t_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 where 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

34、);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 if;end;61/27-函数的查看select text from user_source where name=GET_SALARY_BY_DEPTNO-删除drop function get_salary_by_deptno;62/27处理多行结果集的查询，可以使用游标

35、。步骤为：声明打开取行关闭游标declare tempsal emp.sal%type;-1cursor tempcursor is select*from emp where saltempsal;cursorrecord tempcursor%rowtype;begin tempsal:=800;-2 open tempcursor;-3 fetch tempcursor into cursorrecord;dbms_output.put_line(to_char(cursorrecord.deptno);dbms_output.put_line(to_char(cursorrecord.

36、empno);-4 close tempcursor;end;63/27游标提供一些属性帮助编写PL/SQL程序，其使用方法为：游标名【属性】。例如：Mycursor%isopen64/27测试游标是否打开，如果没有打开游标就使用fetch语句，将提示错误信息。刚刚的例子修改为：declare tempsal scott.emp.sal%type;cursor tempcursor is select*from scott.emp where saltempsal;cursorrecord tempcursor%rowtype;begin tempsal:=800;open tempcurso

37、r;if tempcursor%isopen then fetch tempcursor into cursorrecord;dbms_output.put_line(to_char(cursorrecord.deptno);else dbms_output.put_line(游标没有打开!);end if;close tempcursor;end;65/27测试前一个fetch语句是否有值修改如下：declare tempsal scott.emp.sal%type;cursor tempcursor is select*from scott.emp where saltempsal;cur

38、sorrecord tempcursor%rowtype;begin tempsal:=800;open tempcursor;if tempcursor%isopen then fetch tempcursor into cursorrecord;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;e

39、nd;66/27%notfound是%found的反逻辑。67/27返回游标的行数修改如下：Declare tempsal scott.emp.sal%type;cursor tempcursor 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

40、_line(to_char(cursorrecord.deptno);dbms_output.put_line(to_char(tempcursor%rowcount);else dbms_output.put_line(没有数据);end if;close tempcursor;end;68/27declare cursor tempcursor(tempsal emp.sal%type)is select*from emp where saltempsal;cursorrecord tempcursor%rowtype;begin open tempcursor(800);fetch te

41、mpcursor into cursorrecord;while tempcursor%found loop dbms_output.put_line(to_char(cursorrecord.deptno|cursorrecord.ename);fetch tempcursor into cursorrecord;end loop;close tempcursor;end;69/27FOR var_name IN cursor_name LOOPEND LOOP;70/27declare cursor tempcursor(tempsal emp.sal%type)is select*fro

42、m emp where saltempsal;begin for cursorrecord in tempcursor(800)loop 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 curr

43、ent of 子句，修改或删除游标结果集中当前行所对应的数据库表中的数据行了。其语法格式为：Where current of cursor_name72/27declare 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;en

44、d loop;commit;-提交已经修改的数据 close c1;end;73/27语法：CREATE OR REPLACE PACKAGE package_name IS|AS PRAGMA SERIALLY_REUSABLE;公有数据类型定义 公有变量声明 公有常量声明 公有异常错误声明 公有游标,函数,过程声明END package_name;74/27create or replace package my_pkgispragma serially_reusable;v_deptrec dept%rowtype;v_sqlcode number;v_sqlerrm varchar2(

45、2048);function add_dept(v_deptno 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;公有数据

46、类型定义 公有变量声明 公有常量声明 公有异常错误声明 公有游标,函数,过程声明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;7

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

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

49、4)not between 8 and 18)then raise_application_error(-20001,不是上班时间,不能修改emp表);end if;end;81/27begin delete from emp;exceptionwhen others then dbms_output.put_line(sqlcode);dbms_output.put_line(sqlerrm);end;82/27update emp set comm=800 where deptno=10;结果:update emp set comm=800 where deptno=10*ERROR at

50、 line 1:ORA-20001:不是上班时间,不能修改emp表 ORA-06512:at SCOTT.MY_TRIGER,line 4 ORA-04088:error during execution of trigger SCOTT.MY_TRIGER83/27语法：CREATE OR REPLACE TRIGGER trig_nameBEFORE|AFTER INSERT|DELETE|UPDATE OF c1,c2,or INSERT|DELETE|UPDATE OF c1,c2,ON tab_nameFOR EACH ROW WHEN trig_conditionPL/SQL_bl

51、ock|CALL procedure_name;84/27create or replace trigger tr_emp_sal_commbefore update of sal,comm or delete on empfor each row when(old.job=SALESMAN)begin case when updating(sal)then if:new.sal:old.sal then raise_application_error(-20001,销售人员工资只能涨不能降);end if;when updating(comm)then if:m:m then raise_a

52、pplication_error(-20002,销售人员补助只能涨不能降);end if;when deleting then raise_application_error(-20003,不能删除emp表的销售人员记录);end case;end;85/27begin update emp set comm=800 where job=SALESMAN;exception when others then dbms_output.put_line(sqlcode);dbms_output.put_line(sqlerrm);end;86/27select*from user_triggers

53、;select*from user_source;87/27Alter trigger tr_emp_sal_comm disable;88/27Alter trigger tr_emp_sal_comm enable;89/27Alter table dept disable all triggers;Alter table emp enable all triggers;90/27当修改表结构后，会使该表上的触发器变为当修改表结构后，会使该表上的触发器变为invalid状态，这时要重新编译。状态，这时要重新编译。Alter trigger trigger_name compile;91/27Drop trigger tr_emp_time;92/27登陆登陆Enterprise Manager，选择，选择“管理管理”，再选择，再选择“过程过程”93/27在在“方案方案”中输入中输入“scott”，单击，单击“开始开始”94/27点击点击“创建创建”，可以创建存储过程，可以创建存储过程95/27