oracle存储过程超详细使用手册
Oracle 存储过程总结1、创建存储过程create or replace procedure test(var_name_1 in type,var_name_2 out type) as--声明变量(变量名 变量类型)begin--存储过程的执行体end test;打印出输入的时间信息E.g:create or replace procedure test(workDate in Date) isbegindbms_output.putline(The input date is:||to_date(workDate, yyyy-mm-dd));end test;2、变量赋值变量名 := 值;E.g：create or replace procedure test(workDate in Date) isx number(4,2);beginx := 1;end test;3、判断语句:if 比较式 then begin end; end if;E.gcreate or replace procedure test(x in number) isbeginif x >0 thenbeginx := 0 - x;end;end if;if x = 0 thenbeginx: = 1;end;end if;end test;4、 For 循环For ... in ... LOOP--执行语句end LOOP;(1)循环遍历游标create or replace procedure test() asCursor cursor is select name from student;name varchar(20);beginfor name in cursor LOOPbegindbms_output.putline(name);end;end LOOP;end test;(2)循环遍历数组create or replace procedure test(varArray in myPackage.TestArray) as--(输入参数varArray 是自定义的数组类型，定义方式见标题6)i number;begini := 1; --存储过程数组是起始位置是从1开始的，与java、 C、 C++等语言不同。因为在Oracle 中本是没有数组的概念的，数组其实就是一张--表(Table),每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历for i in 1..varArray.count LOOPdbms_output.putline(The No. || i ||record in varArray is: ||varArray(i));end LOOP;end test;5、 While 循环while 条件语句 LOOPbeginend;end LOOP;E.gcreate or replace procedure test(i in number) asbeginwhile i < 10 LOOPbegini:= i + 1;end;end LOOP;end test;6、数组首先明确一个概念： Oracle中本是没有数组的概念的， 数组其实就是一张表(Table),每个数组元素就是表中的一个记录。使用数组时，用户可以使用Oracle已经定义好的数组类型，或可根据自己的需要定义数组类型。(1)使用Oracle自带的数组类型x array; --使用时需要需要进行初始化e.g:create or replace procedure test(y out array) isx array;beginx := new array();y := x;end test;(2)自定义的数组类型 (自定义数据类型时，建议通过创建Package的方式实现，以便于管理)E.g (自定义使用参见标题4.2) create or replace package myPackage is-- Public type declarations type info is record( name varchar(20), y number);type TestArray is table of info index by binary_integer; --此处声明了一个TestArray 的类型数据，其实其为一张存储Info 数据类型的Table而已，及TestArray 就是一张表，有两个字段，一个是name， 一个是y。 需要注意的是此处使用了Index by binary_integer 编制该Table 的索引项，也可以不写，直接写成： type TestArray istable of info，如果不写的话使用数组时就需要进行初始化： varArray myPackage.TestArray; varArray := new myPackage.TestArray();end TestArray;7.游标的使用 Oracle 中Cursor是非常有用的， 用于遍历临时表中的查询结果。其相关方法和属性也很多，现仅就常用的用法做一二介绍：(1)Cursor型游标(不能用于参数传递)create or replace procedure test() iscusor_1 Cursor is select std_name from student where ...; --Cursor 的使用方式1 cursor_2 Cursor;beginselect class_name into cursor_2 from class where ...; --Cursor的使用方式2可使用For x in cursor LOOP .... end LOOP; 来实现对 Cursor的遍历end test;(2)SYS_REFCURSOR型游标， 该游标是Oracle以预先定义的游标， 可作出参数进行传递create or replace procedure test(rsCursor out SYS_REFCURSOR) iscursor SYS_REFCURSOR; name varhcar(20);beginOPEN cursor FOR select name from student where ... --SYS_REFCURSOR只能通过OPEN方法来打开和赋值LOOPfetch cursor into name --SYS_REFCURSOR只能通过 fetch into来打开和遍历 exit when cursor%NOTFOUND; --SYS_REFCURSOR中可使用三个状态属性：---%NOTFOUND(未找到记录信息) %FOUND(找到记录信息)---%ROWCOUNT(然后当前游标所指向的行位置)dbms_output.putline(name);end LOOP;rsCursor := cursor;end test;下面写一个简单的例子来对以上所说的存储过程的用法做一个应用：现假设存在两张表，一张是学生成绩表(studnet)，字段为： stdId,math,article,language,music,sport,total,average,step一张是学生课外成绩表(out_school),字段为:stdId,parctice,comment通过存储过程自动计算出每位学生的总成绩和平均成绩， 同时， 如果学生在课外课程中获得的评价为 A，就在总成绩上加20分。create or replace procedure autocomputer(step in number) isrsCursor SYS_REFCURSOR;commentArray myPackage.myArray;math number;article number;language number;music number;sport number;total number;average number;stdId varchar(30);record myPackage.stdInfo;i number;begini := 1;get_comment(commentArray); --调用名为get_comment()的存储过程获取学生课外评分信息OPEN rsCursor for select stdId,math,article,language,music,sport fromstudent t where t.step = step;LOOPfetch rsCursor into stdId,math,article,language,music,sport; exit when rsCursor%NOTFOUND;total := math + article + language + music + sport;for i in 1..commentArray.count LOOPrecord := commentArray(i);if stdId = record.stdId thenbeginif record.comment = 'A' thenbegintotal := total + 20;go to next; --使用go to跳出for循环end;end if;end;end if;end LOOP;<
oracle 存储过程的基本语法1.基本结构CREATE OR REPLACE PROCEDURE 存储过程名字(参数1 IN NUMBER,参数2 IN NUMBER) IS变量1 INTEGER :=0;变量2 DATE;BEGINEND 存储过程名字2.SELECT INTO STATEMENT将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)例子：BEGINSELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;EXCEPTIONWHEN NO_DATA_FOUND THENxxxx;END;...3.IF 判断IF V_TEST=1 THENBEGINdo somethingEND;END IF;4.while 循环WHILE V_TEST=1 LOOPBEGINXXXXEND;END LOOP;5.变量赋值V_TEST := 123;6.用for in 使用cursor...ISCURSOR cur IS SELECT * FROM xxx;BEGINFOR cur_result in cur LOOPBEGINV_SUM :=cur_result.列名1+cur_result.列名2END;END LOOP;END;7.带参数的cursorCURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERETYPEID=C_ID;OPEN C_USER(变量值);LOOPFETCH C_USER INTO V_NAME;EXIT FETCH C_USER%NOTFOUND;do somethingEND LOOP;CLOSE C_USER;8.用pl/sql developer debug连接数据库后建立一个 Test WINDOW在窗口输入调用 SP的代码,F9开始debug,CTRL+N单步调试
关于oracle存储过程的若干问题备忘1.在oracle中，数据表别名不能加 as，如：select a.appname from appinfo a;-- 正确select a.appname from appinfo as a;-- 错误也许，是怕和 oracle中的存储过程中的关键字 as冲突的问题吧2.在存储过程中， select某一字段时， 后面必须紧跟 into， 如果select整个记录， 利用游标的话就另当别论了。select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 有into，正确编译select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 没有into，编译报错，提示： CompilationError: PLS-00428: an INTO clause is expected in this SELECT statement3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no datafound"异常。可以在该语法之前，先利用select count(*) from 查看数据库中是否存在该记录，如果存在，再利用select...into...4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;-- 正确运行select af.keynode into kn from APPFOUNDATION af where af.appid=appid and af.foundationid=foundationid;-- 运行阶段报错，提示ORA-01422:exact fetch returns more than requested number of rows5.在存储过程中，关于出现null的问题假设有一个表A，定义如下：create table A(id varchar2(50) primary key not null,vcount number(8) not null,bid varchar2(50) not null -- 外键);如果在存储过程中，使用如下语句：select sum(vcount) into fcount from A where bid='xxxxxx';如果A表中不存在bid="xxxxxx"的记录，则fcount=null(即使fcount定义时设置了默认值，如： fcountnumber(8):=0依然无效， fcount还是会变成null)，这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：if fcount is null thenfcount:=0;end if;这样就一切ok了。6.Hibernate调用oracle存储过程this.pnumberManager.getHibernateTemplate().execute(new HibernateCallback() {public Object doInHibernate(Session session)throws HibernateException, SQLException {CallableStatement cs = session.connection().prepareCall("{call modifyapppnumber_remain(?)}");cs.setString(1, foundationid);cs.execute();return null;}});
oracle 存储过程语法总结及练习-----------------------------------------------1.存储过程之ifclear;create or replace procedure mydel(in_a in integer)asbeginif in_a<100 thendbms_output.put_line('小于 100.');elsif in_a<200 thendbms_output.put_line('大于 100 小于200.');elsedbms_output.put_line('大于 200.');end if;end;/set serveroutput on;beginmydel(1102);end;/-----------------------------------------------2.存储过程之 case1clear;create or replace procedure mydel(in_a in integer)asbegincase in_awhen 1 thendbms_output.put_line('小于 100.');when 2 thendbms_output.put_line('大于 100 小于200.');elsedbms_output.put_line('大于 200.');end case;end;/set serveroutput on;beginmydel(2);end;/--------------------------------------------------1.存储过程之 loop1clear;create or replace procedure mydel(in_a in integer)asa integer;begina:=0;loopdbms_output.put_line(a);a:=a+1;exit whena>301;end loop;end;/set serveroutput on;beginmydel(2);end;/----------------------------------------------------1.存储过程之 loop2clear;create or replace procedure mydel(in_a in integer)asa integer;begina:=0;while a<300 loopdbms_output.put_line(a);a:=a+1;end loop;end;/set serveroutput on;beginmydel(2);end;----------------------------------------------------1.存储过程之 loop3clear;create or replace procedure mydel(in_a in integer)asa integer;beginfor a in 0..300loopdbms_output.put_line(a);end loop;end;/set serveroutput on;beginmydel(2);end;/clear;select ename,cc:=(casewhen comm=null then sal*12;else (sal+comm)*12;end case from emp order by salpersal;----------------------------------------------------clear;create or replace procedure getstudentcomments(i_studentid in int,o_comments out varchar)asexams_sat int;avg_mark int;tmp_comments varchar(100);beginselect count(examid) into exams_sat from studentexamwhere studentid=i_studentid;if exams_sat=0 thentmp_comments:='n/a-this student did not attend the exam!';elseselect avg(mark) into avg_mark from studentexamwhere studentid=i_studentid;casewhen avg_mark<50 then tmp_comments:='very bad';when avg_mark<60 then tmp_comments:='bad';when avg_mark<70 then tmp_comments:='good';end case;end if;o_comments:=tmp_comments;end;/set serveroutput on;declarepp studentexam.comments%type;begingetstudentcomments(8,pp);dbms_output.put_line(pp);end;/--------------------------------------------------------delete from emp where empno<6000;clear;create or replace procedure insertdata(in_num in integer)asmyNum int default 0;emp_no emp.empno%type:=1000;beginwhile myNum ORACLE 查询练习emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm 佣金/deptno部门编号)dept部门表(deptno部门编号/dname 部门名称/loc 地点)工资 ＝ 薪金 ＋ 佣金1．列出至少有一个员工的所有部门。2．列出薪金比“ SMITH” 多的所有员工。3．列出所有员工的姓名及其直接上级的姓名。4．列出受雇日期早于其直接上级的所有员工。5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门6．列出所有“ CLERK” （办事员）的姓名及其部门名称。7．列出最低薪金大于 1500的各种工作。8．列出在部门“ SALES” （销售部）工作的员工的姓名，假定不知道销售部的部门编号。9．列出薪金高于公司平均薪金的所有员工。10．列出与“ SCOTT” 从事相同工作的所有员工。11．列出薪金等于部门 30 中员工的薪金的所有员工的姓名和薪金。12．列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金。13．列出在每个部门工作的员工数量、平均工资和平均服务期限。14．列出所有员工的姓名、部门名称和工资。15．列出所有部门的详细信息和部门人数。16．列出各种工作的最低工资。17．列出各个部门的 MANAGER（经理）的最低薪金。18．列出所有员工的年工资,按年薪从低到高排序。--------1----------select dname from deptwhere deptnoin(select deptnofrom emp);--------2----------select * fromemp where sal>(select sal from emp whereename='SMITH');--------3----------select a.ename,(select ename from emp b where b.empno=a.mgr) as bossname from emp a;--------4----------select a.ename from emp a wherea.hiredate<(select hiredate fromemp b whereb.empno=a.mgr);--------5----------select a.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.comm,b.deptnofrom depta left join emp b ona.deptno=b.deptno;--------6----------selecta.ename,b.dnamefromempajoindeptbona.deptno=b.deptnoanda.job='CLERK';--------7----------selectdistinctjobasHighSalJobfromempgroupbyjobhavingmin(sal)>1500;--------8----------selectenamefromempwheredeptno=(selectdeptnofromdeptwheredname='SALES');--------9----------selectenamefromempwheresal>(selectavg(sal)fromemp);--------10---------selectenamefromempwherejob=(selectjobfromempwhereename='SCOTT');--------11---------selecta.ename,a.salfromempawherea.salin(selectb.salfromempbwhereb.deptno=30)anda.deptno<>30;--------12---------selectename,salfromempwheresal>(selectmax(sal)fromempwheredeptno=30);--------13---------select(selectb.dnamefromdeptbwherea.deptno=b.deptno)asdeptname,count(deptno)asdeptcount, avg(sal)asdeptavgsalfromempagroupbydeptno;--------14---------selecta.ename,(selectb.dnamefromdeptbwhereb.deptno=a.deptno)asdeptname,salfromempa;--------15---------selecta.deptno,a.dname,a.loc,(selectcount(deptno)fromempbwhereb.deptno=a.deptnogroupbyb.deptno)asdeptcountfromdepta;--------16---------selectjob,avg(sal)fromempgroupbyjob;--------17---------selectdeptno,min(sal)fromempwherejob='MANAGER'groupbydeptno;--------18---------selectename,(sal+nvl(comm,0))*12assalpersalfromemporderbysalpersal; ORACLE 子句查询,分组等A.同表子查询作为条件a. 给出人口多于 Russia(俄国)的国家名称 SELECT name FROM bbcWHERE population>(SELECT population FROM bbcWHERE name='Russia')b.给出'India'(印度), 'Iran'(伊朗)所在地区的所有国家的所有信息 SELECT * FROM bbcWHERE region IN(SELECT region FROM bbcWHERE name IN ('India','Iran'))c.给出人均 GDP 超过'United Kingdom'(英国)的欧洲国家. SELECT name FROM bbcWHERE region='Europe' AND gdp/population >(SELECT gdp/population FROM bbcWHERE name='United Kingdom')d.这个查询实际上等同于以下这个:select e1.ename from emp e1,(select empno from emp where ename = 'KING') e2 where e1.mgr = e2.empno;你可以用 EXISTS写同样的查询，你只要把外部查询一栏移到一个像下面这样的子查询环境中就可以了：select ename from emp ewhere exists (select 0 from emp where e.mgr = empno and ename = 'KING');当你在一个 WHERE 子句中写 EXISTS时，又等于向最优化传达了这样一条信息，即你想让外部查询先运行，使用每一个值来从内部查询（假定： EXISTS＝由外而内）中得到一个值。B.异表子查询作为条件a.select * from studentExam where studentid=( select studentid from student where name='吴丽丽');b.select * from studentexam where studentid in (select studentid from student) order by studentid;c.select * from student where studentid in (select studentid from studentexam where mark>80);3.select studentexam.mark,studentexam.studentid as seid, student.studentid,student.name from studentexam,student where student.studentid=studentexam.studentid;过滤分组:顺序为先分组,再过滤,最后进行统计(实际值).select studentid,count(*) as highpasses from studentexamwhere mark>70group by studentid;假使我们不想通过数据表中的实际值,而是通过聚合函数的结果来过过滤查询的结果.select studentid,avg(mark) as averagemarkfrom studentexamwhere avg(mark)<50 oravg(mark)>70group by studentid;(此句错误,where 句子是不能用聚合函数作条件的)此时要用 having.select studentid,avg(mark) from studentexam group by studentid having avg(mark)>70 or avg(mark)<50;select studentid,avg(mark) from studentexam where studentid in(1,7,9,5)group bystudentid having avg(mark)>70;(先分组,再过滤,再 having聚合,最后再统计).select studentid ,avg(mark) as averagemarkfrom studentexamwhere examid in(5,8,11)group by studentidhaving avg(mark)<50 or avg(mark)>70;返回限定行数查询:select name from student where rownum<=10;oracle 中使用 rownum 关键字指定,但该关键字必须在 where 子句中与一个比较运算符一起指定,而不能与order by 一起配合便用,因为rownum 维护的是原始行号.如果需要用groupby\order by 就用子句查询作表使用的方法:select studentid,averagemark from(select studentid,avg(mark) as averagemarkfromstudentexamgroup by studentid order by averagemark desc)where rownum<=10; oracle 存储过程语法:Oracle存储过程入门学习基本语法1.基本结构createOR REPLACEPROCEDURE 存储过程名字( 参数1IN NUMBER, 参数2IN NUMBER)IS 变量1INTEGER:=0; 变量2DATE;BEGINEND 存储过程名字2.selectINTOSTATEMENT 将select查询的结果存入到变量中， 可以同时将多个列存储多个变量中， 必须有一条记录， 否则抛出 异常(如果没有记录抛出NO_DATA_FOUND) 例子:BEGINselectcol1,col2into 变量1,变量2 FROMtypestructwherexxx;EXCEPTIONWHENNO_DATA_FOUNDTHENxxxx;END;...3.IF 判断IFV_TEST=1THENBEGINdosomethingEND;ENDIF;4.while 循环WHILEV_TEST=1 LOOPBEGINXXXXEND;END LOOP;5.变量赋值V_TEST:=123;6.用for in 使用cursor...ISCURSOR curIS select*FROM xxx;BEGINFOR cur_resultincurLOOPBEGINV_SUM :=cur_result.列名1+cur_result.列名2END;ENDLOOP;END;7.带参数的cursorCURSOR C_USER(C_IDNUMBER) IS selectNAME FROM USERwhereTYPEID=C_ID;OPEN C_USER(变量值);LOOPFETCHC_USER INTOV_NAME;EXITFETCH C_USER%NOTFOUND;dosomethingEND LOOP;CLOSE C_USER;8.用pl/sqldeveloperdebug 连接数据库后建立一个Test WINDOW 在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试 oracle语法:Oracle 触发器语法及实例基础知识（一） 一Oracle触发器语法 触发器是特定事件出现的时候， 自动执行的代码块 类似于存储过程， 触发器和存储过程的区别 在于:存储过程是由用户或应用程序显式调用的,而触发器是不能被直接调用的 功能:1、 允许/限制对表的修改2、 自动生成派生列， 比如自增字段3、 强制数据一致性4、 提供审计和日志记录5、 防止无效的事务处理6、 启用复杂的业务逻辑 触发器触发时间有两种:after 和before1、触发器的语法:CREATE[OR REPLACE]TIGGER 触发器名 触发时间 触发事件ON 表名[FOR EACHROW]BEGINpl/sql 语句END 其中: 触发器名:触发器对象的名称 由于触发器是数据库自动执行的， 因此该名称只是一个名称， 没有实质的用途 触发时间:指明触发器何时执行， 该值可取:before---表示在数据库动作的前触发器执行;after---表示在数据库动作的后出发器执行 触发事件:指明哪些数据库动作会触发此触发器:insert:数据库插入会触发此触发器;update:数据库修改会触发此触发器;delete:数据库删除会触发此触发器 表 名:数据库触发器所在的表foreach row:对表的每一行触发器执行一次 如果没有这一选项， 则只对整个表执行一次2、举例: 下面的触发器在更新表auths的前触发， 目的是不允许在周末修改表:createtriggerauth_securebefore insertorupdate ordelete//对整表更新前触发onauthsbeginif(to_char(sysdate,'DY')='SUN'RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表auths');endif;end 例子:CREATE OR REPLACE TRIGGER CRM.T_SUB_USERINFO_AUR_NAME AFTER UPDATE OFSTAFF_NAMEON CRM.T_SUB_USERINFOREFERENCING OLDAS OLD NEWAS NEWFOR EACHROWdeclarebeginif:NEW.STAFF_NAME!=:OLD.STAFF_NAME thenbegin 客户投诉update T_COMPLAINT_MANAGE set SERVE_NAME=:NEW.STAFF_NAME whereSERVE_SEED=:OLD.SEED; 客户关怀updateT_CUSTOMER_CARE setEXECUTOR_NAME=:NEW.STAFF_NAMEwhereEXECUTOR_SEED=:OLD.SEED; 客户服务updateT_CUSTOMER_SERVICE setEXECUTOR_NAME=:NEW.STAFF_NAMEwhereEXECUTOR_SEED=:OLD.SEED;end;endif;endT_sub_userinfo_aur_name;/2 Oracle触发器详解 开始:createtriggerbiufer_employees_department_idbeforeinsertorupdateofdepartment_idonemployeesreferencingoldasold_valuenewasnew_valueforeach rowwhen(new_value.department_id<>80)begin:new_value.commission_pct :=0;end;/1、触发器的组成部分:1、 触发器名称2、 触发语句3、 触发器限制4、 触发操作1.1、触发器名称createtrigger biufer_employees_department_id 命名习惯:biufer(beforeinsertupdatefor eachrow)employees表名department_id列名1.2、触发语句 比如: 表或视图上的DML语句DDL语句 数据库关闭或启动,startupshutdown等等beforeinsert orupdateofdepartment_idonemployeesreferencingoldasold_valuenewasnew_valueforeach row 介绍说明:1、 无论是否规定了department_id， 对employees表进行insert的时候2、 对employees表的department_id列进行update的时候1.3、触发器限制when(new_value.department_id<>80) 限制不是必须的 此例表示如果列department_id不等于80的时候， 触发器就会执行 其中的new_value是代表更新的后的值1.4、触发操作 是触发器的主体begin:new_value.commission_pct :=0;end; 主体很简单， 就是将更新后的commission_pct列置为0 触发:insertinto employees(employee_id,last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct)values( 12345,’Chen’,’Donny’, sysdate,12, ‘donny@hotmail.com’,60,10000,.25);selectcommission_pctfromemployeeswhere employee_id=12345;2、触发器的类型有: 触发器类型:1、 语句触发器2、 行触发器3、 INSTEAD OF触发4、 系统条件触发器5、 用户事件触发器2.1、语句级触发器.(语句级触发器对每个DML语句执行一次) 是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器 能够和 INSERT、UPDATE、 DELETE或者组合上进行关联 但是无论使用什么样的组合， 各个语句触发器都只会针对 指定语句激活一次 比如， 无论update多少行， 也只会调用一次update语句触发器 例子:createor replacetrigger tri_testafterinsertor updateor delete_disibledevent=> 测试,插入几条记录insertinto test values(0,'ff');insertinto test values(0,'ff');insertinto test values(0,'tt'); 例子 2: 创建一个触发器,无论用户插入新记录,还是修改emp表的job列,都将用户指定的job列的值转换成大 写.create orreplacetriggertrig_jobbeforeinsert orupdateof jobonempforeach rowbeginifinserting then:new.job:=upper(:new.job);else: new.job:=upper(:new.job);endif;end;2.3、 insteadof触发器.(此触发器是在视图上而不是在表上定义的触发器,它是用来替换所使用实际语句的触发器.) 语法如下:createor replacetriggertrig_testinsteadofinsertor update_disibledevent=>2.5、数据库级触发器. 可以创建在数据库事件上的触发器,包括关闭,启动,服务器错误,登录等.这些事件都是例子范围的,不和 特定的表或视图关联. 例子:createor replacetrigger trig_nameafterstartup_disibledevent=>4、 测试updateemployees_copyset salary=salary*1.1;select*fromemployess_log;5、 确定是哪个语句起作用? 即是INSERT/UPDATE/DELETE中的哪一个触发了触发器? 可以在触发器中使用INSERTING/ UPDATING/ DELETING条件谓词， 作判断:beginifinserting then-----elsifupdatingthen-----elsifdeletingthen------endif;end;ifupdating(‘COL1’) orupdating(‘COL2’) then------endif;2.8、 [试验]1、 修改日志表alter tableemployees_logadd(actionvarchar2(20));2、 修改触发器， 以便记录语句类型thenl_action:=’Delete’;elseraise_application_error(-20001,’Youshouldneverever getthiserror.’);Insertintoemployees_log(Who,action,when)Values(user,l_action,sysdate);End;Createor replacetrigger biud_employee_copyBeforeinsertorupdate ordeleteOn employees_copyDeclareL_actionemployees_log.action%type;Beginifinserting thenl_action:=’Insert’;elsifupdatingthenl_action:=’Update’;elsifdeleting/3、 测试insert intoemployees_copy( employee_id,last_name, email, hire_date,job_id)values(12345,’Chen’,’Donny@hotmail’,sysdate,12);select*fromemployees_log oracle 基本语法备忘2008 年 11 月 13 日 星期四 09:581.基本结构CREATE OR REPLACE PROCEDURE 存储过程名字( 参数 1IN NUMBER, 参数 2IN NUMBER) IS 变量1 INTEGER :=0; 变量2 DATE;BEGIN END 存储过程名字 2.SELECT INTO STATEMENT 将 select 查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条 记录，否则抛出异常(如果没有记录抛出 NO_DATA_FOUND) 例子： BEGIN SELECT col1,col2 into 变量 1,变量2 FROM typestruct where xxx; EXCEPTION WHEN NO_DATA_FOUND THEN xxxx; END; ... 3.IF 判断 IF V_TEST=1 THEN BEGIN do something END; END IF; 4.while 循环 WHILE V_TEST=1 LOOP BEGIN XXXX END; END LOOP; 5.变量赋值 V_TEST := 123; 6.用 for in 使用 cursor ... IS CURSOR cur IS SELECT * FROM xxx; BEGIN FOR cur_result in cur LOOP BEGIN V_SUM :=cur_result.列名1+cur_result.列名2 END; END LOOP; END; 7.带参数的 cursor CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID; OPEN C_USER(变量值); LOOP FETCH C_USER INTO V_NAME; EXIT FETCH C_USER%NOTFOUND; do something END LOOP; CLOSE C_USER; 8.用 pl/sql developer debug 连接数据库后建立一个Test WINDOW 在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试 关于 oracle 存储过程的若干问题备忘1.在 oracle中，数据表别名不能加 as，如：selecta.appname from appinfoa;-- 正确selecta.appname from appinfoas a;-- 错误 也许，是怕和oracle 中的存储过程中的关键字 as 冲突的问题吧2.在存储过程中， select 某一字段时，后面必须紧跟 into，如果 select 整个记录，利用游标的话就另当别论了。select af.keynode into kn fromAPPFOUNDATION af where af.appid=aid andaf.foundationid=fid;-- 有 into，正确编译select af.keynode fromAPPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 没有 into，编译报错，提示：CompilationError: PLS-00428: an INTOclause is expected in this SELECTstatement3.在利用 select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。 可以在该语法之前，先利用 select count(*) from 查看数据库中是否存在该记录，如果存在，再利用 select...into...4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错selectkeynode into kn fromAPPFOUNDATION whereappid=aidand foundationid=fid;-- 正确运行select af.keynode into kn fromAPPFOUNDATION af where af.appid=appid and af.foundationid=foundationid;-- 运行阶段报错，提示ORA-01422:exact fetch returnsmore thanrequested number of rows5.在存储过程中，关于出现 null 的问题 假设有一个表A，定义如下：createtableA(id varchar2(50) primary key not null,vcount number(8) notnull,bidvarchar2(50) not null -- 外键); 如果在存储过程中，使用如下语句：selectsum(vcount) into fcount fromAwhere bid='xxxxxx'; 如果 A 表中不存在 bid="xxxxxx"的记录， 则 fcount=null(即使 fcount 定义时设置了默认值， 如： fcount number(8):=0 依然 无效， fcount还是会变成 null)，这样以后使用 fcount 时就可能有问题，所以在这里最好先判断一下：iffcount is null thenfcount:=0;endif; 这样就一切ok了。1、创建存储过程 create or replace procedure test(var_name_1 in type,var_name_2 out type) as --声明变量(变量名 变量类型) begin --存储过程的执行体 end test; 打印出输入的时间信息 E.g: create or replace procedure test(workDate in Date) is begin dbms_output.putline('The input date is:'||to_date(workDate,'yyyy-mm-dd')); end test;2、变量赋值 变量名 := 值; E.g： create or replace procedure test(workDate in Date) is x number(4,2); begin x := 1; end test;3、判断语句: if 比较式 then begin end; end if; E.g create or replace procedure test(x in number) is begin if x >0 then begin x := 0 - x; end; end if; if x = 0 then begin x: = 1; end; end if; end test;4、 For 循环 For ... in ... LOOP --执行语句 end LOOP;(1)循环遍历游标 create or replace procedure test() as Cursor cursor is select name from student; name varchar(20); begin for name in cursor LOOP begin dbms_output.putline(name); end; end LOOP; end test;(2)循环遍历数组 create or replace procedure test(varArray in myPackage.TestArray) as --(输入参数varArray 是自定义的数组类型，定义方式见标题 6) i number; begin i := 1; --存储过程数组是起始位置是从1开始的，与java、 C、 C++等语言不同。因为在Oracle中本是 没有数组的概念的，数组其实就是一张 --表(Table),每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍 历 for i in 1..varArray.count LOOP dbms_output.putline('The No.'|| i || 'record in varArray is:'||varArray(i)); end LOOP; end test;5、 While 循环 while 条件语句 LOOP begin end; end LOOP; E.g create or replace procedure test(i in number) as begin while i < 10 LOOP begin i:= i + 1; end; end LOOP; end test;6、数组 首先明确一个概念： Oracle中本是没有数组的概念的，数组其实就是一张表(Table),每个数组元素就是 表中的一个记录。 使用数组时，用户可以使用Oracle已经定义好的数组类型，或可根据自己的需要定义数组类型。(1)使用 Oracle自带的数组类型 x array; --使用时需要需要进行初始化 e.g: create or replace procedure test(y out array) is x array; begin x := new array(); y := x; end test;(2)自定义的数组类型 (自定义数据类型时，建议通过创建Package 的方式实现，以便于管理) E.g (自定义使用参见标题 4.2) create or replace package myPackage is -- Public type declarations type info is record( name varchar(20), y number); type TestArray is table of info index by binary_integer; --此处声明了一个TestArray的类型数 据，其实其为一张存储Info数据类型的Table而已，及TestArray 就是一张表，有两个字段，一个是 name，一个是y。需要注意的是此处使用了Index by binary_integer 编制该Table的索引项，也可以 不写，直接写成： type TestArray is table of info，如果不写的话使用数组时就需要进行初始化： varArray myPackage.TestArray; varArray := new myPackage.TestArray(); end TestArray;7.游标的使用 Oracle中Cursor 是非常有用的，用于遍历临时表中的查询结果。其相关方法和属性也很 多，现仅就常用的用法做一二介绍：(1)Cursor 型游标(不能用于参数传递) create or replace procedure test() is cusor_1 Cursor is select std_name from student where ...; --Cursor的使用方式1 cursor_2 Cursor; begin select class_name into cursor_2 from class where ...; --Cursor的使用方式2 可使用 For x in cursor LOOP .... end LOOP; 来实现对Cursor 的遍历 end test;(2)SYS_REFCURSOR型游标，该游标是Oracle以预先定义的游标，可作出参数进行传递 create or replace procedure test(rsCursor out SYS_REFCURSOR) is cursor SYS_REFCURSOR; name varhcar(20); begin OPEN cursor FOR select name from student where ... --SYS_REFCURSOR 只能通过OPEN方法来打开和 赋值 LOOP fetch cursor into name --SYS_REFCURSOR只能通过fetch into来打开和遍历 exit when cursor%NOTFOUND; --SYS_REFCURSOR 中可使用三个状态属 性： ---%NOT FOUND(未找到记录信息) %FOUND(找到记录信 息) ---%ROW COUNT(然后当前游标所指向的行位置) dbms_output.putline(name); end LOOP; rsCursor := cursor; end test;下面写一个简单的例子来对以上所说的存储过程的用法做一个应用： 现假设存在两张表，一张是学生成绩表(studnet)，字段为： stdId,math,article,language,music,sport,total,average,step 一张是学生课外成绩表(out_school),字段为:stdId,parctice,comment 通过存储过程自动计算出每位学生的总成绩和平均成绩，同时，如果学生在课外课程中获得的评价为A， 就在总成绩上加20分。 create or replace procedure autocomputer(step in number) is rsCursor SYS_REFCURSOR; commentArray myPackage.myArray; math number; article number; language number; music number; sport number; total number; average number; stdId varchar(30); record myPackage.stdInfo; i number; begin i := 1; get_comment(commentArray); --调用名为get_comment()的存储过程获取学生课外评分信息 OPEN rsCursor for select stdId,math,article,language,music,sport from student t where t.step = step; LOOP fetch rsCursor into stdId,math,article,language,music,sport; exit when rsCursor%NOTFOUND; total := math + article + language + music + sport; for i in 1..commentArray.count LOOP record := commentArray(i); if stdId = record.stdId then begin if record.comment = 'A' then begin total := total + 20; go to next; --使用 go to跳出for循环 end; end if; end; end if; end LOOP; < update student t set t.total=total and t.average = average where t.stdId = stdId; end LOOP; end; end autocomputer;--取得学生评论信息的存储过程 create or replace procedure get_comment(commentArray out myPackage.myArray) is rs SYS_REFCURSOR； record myPackage.stdInfo; stdId varchar(30); comment varchar(1); i number; begin open rs for select stdId,comment from out_school i := 1; LOOP fetch rs into stdId,comment; exit when rs%NOTFOUND; record.stdId := stdId; record.comment := comment; recommentArray(i) := record; i:=i + 1; end LOOP; end get_comment;--定义数组类型myArray create or replace package myPackage is begin type stdInfo is record(stdId varchar(30),comment varchar(1)); type myArray is table of stdInfo index by binary_integer; end myPackage;