Oracle 存储过程


Oracle Content • 存储过程介绍 – PL/SQL简介 – 数据类型 – PL/SQL控制语句 – Record – 游标 • 常用函数 • 练习 ©2008 iSoftStone Holdings Ltd. All Rights Reserved. PL/SQL 简介  QL(Structure Query Language): • 是对关系型数据库(RDBMS)进行操作的标准语言.  PL/SQL(Procedural Language/SQL): 在SQL的基础上,增加了过程化处理. 比如sql*plus, developer 6i  和标准的SQL相比,PL/SQL有自己特有的一些特性 变量定义 流程结构控制 对象类型 存储过程和函数 ©2008 iSoftStone Holdings Ltd. All Rights Reserved. Example1 Declare /*定义变量*/ v_yesterday varchar2(10); Begin /*代码部分*/ v_yesterday :=‘2009-07-10’; dbms_output.put_line(v_yesterday); select trunc(sysdate)-1 into v_yesterday from dual; dbms_output.put_line(v_yesterday); End; ©2008 iSoftStone Holdings Ltd. All Rights Reserved. PL/SQL Declare 变量声明,可以声明oracle标准变量,也可以声明 自定义变量,还 可以声明异常. Begin 块代码,可以变量赋值,使用循环结构,条件结构 等. Exception 异常处理部分,可以捕捉oracle预定义的异常,也 可以捕捉自定义异常 End; ©2008 iSoftStone Holdings Ltd. All Rights Reserved. PL/SQL 无名块:example1就是一个无名块. 子程序:存储过程,函数,程序包,存储在数据 库中,可以被调用. 触发器:存储在数据库中,根据触发条件自动 触发执行. ©2008 iSoftStone Holdings Ltd. All Rights Reserved. Example2: ,, Create or replace procedure Log_Print(v_content varchar2) is n_id number; Begin Select nvl(max(id),0)+1 into n_id from test_case_runtime_log; insert into test_case_runtime_log (id,s_content,dt_input) values (n_id,v_content,sysdate); commit; End Log_Print; ©2008 iSoftStone Holdings Ltd. All Rights Reserved. • 常用的数据类型有: • Number, 如 v_passRate number(5,2); • Varchar2, 如 v_result varchar2(10); • Date, 如 v_yesterday date; • 数据类型转换: • To_char, to_number, to_date • 如:To_date(‘2009-07-08 09:01:00’, ‘yyyy-mm-dd hh24:mi:ss’) ©2008 iSoftStone Holdings Ltd. All Rights Reserved. PL/SQL控制结构  选择控制语句  循环控制语句  顺序控制语句 ©2008 iSoftStone Holdings Ltd. All Rights Reserved. 选择控制语句  If .. Then  If .. Then .. Else  If .. Then .. elsif ©2008 iSoftStone Holdings Ltd. All Rights Reserved. Example Declare n_id number; Begin select max(id) into n_id from test_case_runtime_log; if n_id>10 then dbms_output.put_line(‘the id ’|| n_id ||’ in table test_case_runtime_log is more than 10‘); else dbms_output.put_line(‘there are ‘|| n_id||’records in table test_case_runtime_log‘); end if; End; ©2008 iSoftStone Holdings Ltd. All Rights Reserved. Example if p_sal<800 then dbms_output.put_line(‘The salary of ’||p_ename||’ is less than 800‘); elsif p_sal=800 then dbms_output.put_line(‘equal to 800’); else dbms_output.put_line(‘greater than 800’) end if; ©2008 iSoftStone Holdings Ltd. All Rights Reserved. 循环控制语句  loop  while .. Loop  for .. loop ©2008 iSoftStone Holdings Ltd. All Rights Reserved. LOOP Declare I number:=0; Begin loop I=I+1; /*if I>=10 then exit; end if;*/ exit when I>=10; end loop; End; ©2008 iSoftStone Holdings Ltd. All Rights Reserved. While .. loop Declare I number; Begin I:=0; while I<10 loop I=I+1; end loop; End; ©2008 iSoftStone Holdings Ltd. All Rights Reserved. For .. Loop Begin /*for I in reverse 1..9 loop 从9 递减到1,增量或者减量只能是1*/ for I in 1..9 loop dbms_output.put_line(I); end loop; End;  环境变量I不必定义,隐式定义为整数  只能引用,不能赋值  增量或者减量是1  初始值必须小于结束值 ©2008 iSoftStone Holdings Ltd. All Rights Reserved. For .. Loop(续) Begin for r in (select 10 a,20 b from dual) loop dbms_output.put_line(r.a); dbms_output.put_line(r.b); end loop; End; ©2008 iSoftStone Holdings Ltd. All Rights Reserved. For .. Loop(续) Begin for i in 1..10 loop dbms_output.put_line(i); for j in 11..20 loop dbms_output.put_line(j); if j=15 then exit; end if; end loop; end loop; End; Exit只退出当前循环 ©2008 iSoftStone Holdings Ltd. All Rights Reserved. Goto • declare i number; Begin i:=0; if i=0 then goto i0; elsif i=1 then goto i1; else goto i3; end if; <> dbms_output.put_line('i=0'); return; <> dbms_output.put_line('i=1'); return; <> dbms_output.put_line('i>=2'); End; ©2008 iSoftStone Holdings Ltd. All Rights Reserved. Goto(续)  <<>>标号后必须有可执行的pl/sql块  不能跳转到if loop  不能从异常处理部分跳到当前块 ©2008 iSoftStone Holdings Ltd. All Rights Reserved. Record • 可以想象成数据库table的一条记录,是 由一个一个列组成的. DECLARE TYPE DeptRec IS RECORD ( dept_id dept.deptno%TYPE, dept_name VARCHAR2(14), dept_loc VARCHAR2(13)); BEGIN ... END; ©2008 iSoftStone Holdings Ltd. All Rights Reserved. Record引用 DECLARE TYPE EmpRec IS RECORD ( emp_id NUMBER(4), job_title VARCHAR2(9), salary NUMBER(7,2)); middle_sal NUMBER(7,2); FUNCTION nth_highest_sal (n INTEGER) RETURN EmpRec IS emp_info EmpRec; BEGIN ... RETURN emp_info; -- return record END; BEGIN middle_sal := nth_highest_sal(10).salary; -- call function ... END; ©2008 iSoftStone Holdings Ltd. All Rights Reserved. Record赋值  可以单独的为Record每个列赋值: emp_info.ename := UPPER(emp_info.ename);  可以把record变量赋值给相同type的record变量 (注释中的例子显示了此类型赋值的不合法语法)  可以把%rowtype变量赋值给用户定义record变量, 二者必须有完全相同的列 (见示例代码)  可以使用select into 直接对用户定义record变量 赋值 (见示例代码)  不能使用record_name := (value1, value2, value3, ...)形式赋值 ©2008 iSoftStone Holdings Ltd. All Rights Reserved. Record 续 CREATE TYPE Worker AS OBJECT (name VARCHAR2(25), dept VARCHAR2(15)); / CREATE TABLE teams (team_no NUMBER, team_member Worker); DECLARE team_rec teams%ROWTYPE; BEGIN team_rec.team_no := 5; team_rec.team_member := Worker(’Paul Ocker’, ’Accounting’); UPDATE teams SET ROW = team_rec; END; ©2008 iSoftStone Holdings Ltd. All Rights Reserved. 游标 显式游标操作  游标属性  隐式游标处理  游标变量 ©2008 iSoftStone Holdings Ltd. All Rights Reserved. • 游标,可以理解为一个指针,指向内存中处理 sql的一个区域,PL/SQL程序通过游标来控 制在此区域中被处理的sql 显式游标 由cursor .. Is 定义 对select返回多条记录进行处理 隐式游标 对insert/delete/update/select(返回单条 记录)操作, 由pl/sql自动产生和管理隐式 游标 ©2008 iSoftStone Holdings Ltd. All Rights Reserved. 显式游标操作 declare l_ename varchar2(10); l_emp scott.emp%rowtype; cursor my_emp(v_empno in number:=0) is select * from scott.emp where empno=v_empno and ename=l_ename; begin l_ename:='ISS'; open my_emp(7369); loop l_emp:=null; fetch my_emp into l_emp; exit when my_emp%notfound; dbms_output.put_line(l_emp.ename); end loop; close my_emp; end; ©2008 iSoftStone Holdings Ltd. All Rights Reserved. For .. Loop在游标中的应用 • declare cursor my_emp is select * from scott.emp where empno=7369 for update of sal; begin for r in my_emp loop update scott.emp set sal=sal+200 where current of my_emp; end loop; commit; end; ©2008 iSoftStone Holdings Ltd. All Rights Reserved. 游标的属性 %FOUND 返回boolean,当fetch得到记录,返回true,否则,返回 false,如果游标没有打开,则返回ora-1001(游标不可用 )错误,如果游标打开,但是没有使用fetch推进,则返回 null. %NOTFOUND 和%FOUND相反 %ISOPEN 返回boolean,当游标打开,返回true,否则 false. %ROWCOUNT 返回游标推进的行数.如果游标没有打开,则返回错误. ©2008 iSoftStone Holdings Ltd. All Rights Reserved. 隐式游标的使用 • declare l_emp SCOTT.EMP%ROWTYPE; begin select *INTO l_emp from scott.emp where ename=‘ISS’ --for update of sal; if sql%found then update scott.emp set sal=sal+200 where ename=‘ISS’; end if; exception when no_data_found then dbms_output.put_line(‘ISS does not exist’); when too_many_rows then dbms_output.put_line(‘More than one ISS exists’); end; ©2008 iSoftStone Holdings Ltd. All Rights Reserved. 隐式游标的使用  当执行insert/delete/update/insert into时,pl/sql自 动创建隐式游标.  隐式游标不能open/close/fetch  和显式游标有同样的属性,用来检测语句执行的结果. ©2008 iSoftStone Holdings Ltd. All Rights Reserved. Execute Immediate • Execute Immediate解析并马上执行动态的SQL语句或非 运行时创建的PL/SQL块.动态创建和执行SQL语句性能很高。 • 使用技巧 – EXECUTE IMMEDIATE将不会提交一个DML事务执行,应该显式提交如果通过 EXECUTE IMMEDIATE处理DML命令,那么在完成以前需要显式提交或者作为 EXECUTE IMMEDIATE自己的一部分。如果通过EXECUTE IMMEDIATE处理 DDL命令,它提交所有以前改变的数据 – 不支持返回多行的查询,这种交互将用临时表来存储记录或者用REF cursors – 当执行SQL语句时,不要用分号,当执行PL/SQL块时,在其尾部用分号。 – 使用using子句 给动态语句传值 – 使用into子句从动态语句检索值 ©2008 iSoftStone Holdings Ltd. All Rights Reserved. Execute Immediate Example • declare • SQL_Str varchar2(200); • temp_1 varchar2(100); • temp_2 varchar2(100); • Type Test_Temp_Data is Record (sname varchar2(100),svalue varchar2(100)); • temp_test_Info Test_Temp_Data; • begin •--execute immediate 'drop table temp_test'; • SQL_Str := 'create table temp_test as select '||chr(39)||'name0'||chr(39)||' sName, to_char(sysdate,'||chr(39)||'yyyy-mm-dd hh24:mi:ss'||chr(39)||') sValue from dual'; • execute immediate SQL_Str; • execute immediate 'insert into temp_test values ('||chr(39)||'name1'||chr(39)||','||chr(39)||'value1'||chr(39)||')'; --连 接字符串来拼SQL • temp_1 :='name2'; • temp_2 :='value2'; • execute immediate 'insert into temp_test values ('||chr(39)||temp_1||chr(39)||','||chr(39)||temp_2||chr(39)||')'; • execute immediate 'insert into temp_test values (:1,:2)' using 'name3','value3';--使用using子句 • commit; • execute immediate 'select * from temp_test where sName =:1' into temp_test_Info using 'name1'; • dbms_output.put_line(temp_test_Info.sName); • dbms_output.put_line(temp_test_Info.sValue); • execute immediate 'select * from temp_test where sName =:1' into temp_test_Info using 'name1'; • dbms_output.put_line(temp_test_Info.sName); • dbms_output.put_line(temp_test_Info.sValue); • end; ©2008 iSoftStone Holdings Ltd. All Rights Reserved. Oracle • Nvl • Trunc • decode ©2008 iSoftStone Holdings Ltd. All Rights Reserved. Nvl() • NVL(EXPR1,EXPR2) 若EXPR1是NULL,则返回EXPR2,否则返回 EXPR1. • Select nvl(max(id),0)+1 from tc_testRun; ©2008 iSoftStone Holdings Ltd. All Rights Reserved. Trunc() • TRUNC()函數分两种 • 1.TRUNC(for dates) TRUNC函数为指定元素而截去的日期值。 其具体的语法格式如下: TRUNC(date[,fmt]) 其中: date 一个日期值 fmt 日期格式,该日期将由指定的元素格式所截去。忽略它则由最近的日期 截去 下面是该函数的使用情况: TRUNC(TO_DATE(’24-Nov-1999 08:00 pm’,’dd-mon-yyyy hh:mi am’)) =’24-Nov-1999 12:00:00 am’ TRUNC(TO_DATE(’24-Nov-1999 08:37 pm’,’dd-mon-yyyy hh:mi am’,’hh’)) =’24-Nov-1999 08:00:00 am’ ©2008 iSoftStone Holdings Ltd. All Rights Reserved. Trunc() • 2.TRUNC(for number) TRUNC函数返回处理后的数值,其工作机制与ROUND函数极为类似,只 是该函数不对指定小数前或后的部分做相应舍入选择处理,而统统截去。 其具体的语法格式如下 TRUNC(number[,decimals]) 其中: number 待做截取处理的数值 decimals 指明需保留小数点后面的位数。可选项,忽略它则截去所有的小 数部分 下面是该函数的使用情况: TRUNC(89.985,2)=89.98 TRUNC(89.985)=89 TRUNC(89.985,-1)=80 注意:第二个参数可以为负数,表示为小数点左边指定位数后面的部分截 去,即均以0记。 ©2008 iSoftStone Holdings Ltd. All Rights Reserved. Decode() • decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺 省值) • 该函数的含义如下: • IF 条件=值1 THEN • RETURN(翻译值1) • ELSIF 条件=值2 THEN • RETURN(翻译值2) •...... • ELSIF 条件=值n THEN • RETURN(翻译值n) • ELSE • RETURN(缺省值) • END IF • select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较 小值 • sign()函数根据某个值是0、正数还是负数,分别返回0、1、-1 • Select decode(sign(10-20),-1,10,20) from dual; --10 ©2008 iSoftStone Holdings Ltd. All Rights Reserved. Test Report • 测试报告分为两部分,一是总览报告,展 现每次测试的通过率;二是明细报告,展 现每次测试的每个具体的测试用例的是否 通过。 ©2008 iSoftStone Holdings Ltd. All Rights Reserved. Test Report – Summary & Detail ©2008 iSoftStone Holdings Ltd. All Rights Reserved. Test Report - Table ©2008 iSoftStone Holdings Ltd. All Rights Reserved. TC_TestRun Name Data Type Primary Comment ID number X sCode varchar2(50) 测试执行版本号。编码规则:4位年+2位月+2位天 +3位流水号,如20090609001 sSoftwareRelease Varchar2(50) 本次测试的软件版本信息 sSchema varchar2(50) 本次测试运行在哪个Schema中 dtStart date 测试开始时间 dtEnd date 测试结束时间 sRunStatus Varchar2(10) 运行状态。(EXEC 运行中,DONE 完成, ERROR 错误) nStatus number 状态。1 ---有效,0 ---无效 TC_Result Name Data Type Primary Comment ID number X nTestRunID number 测试执行版本ID sTestCaseName varchar2(100) 测试用例ID sResult varchar2(10) 运行结果:PASS, FAIL sReason varchar2(500) 失败原因 dtStart date 测试开始时间 Test Report • 1 完成Package: • Procedure setTestRunStart (sSoftwareRelease in varchar2, nTestRunID out number); • 标记下本次测试开始,记录本次测试的开始时间、测试 运行编号。 • Procedure setTestRunEnd(nTestRunID number); • 标记本次测试结束。 • Procedure setTestDetailResult(nTestRunID number, sTestCaseName varchar2, sResult varchar2, sReason varchar2, startTime date); • 记录每个测试用例的具体运行情况。 • 2 考虑完成Test Report 的两个视图 ©2008 iSoftStone Holdings Ltd. All Rights Reserved. • 执行set serveroutput on ,打开服务器的输出显示,即打 开oracle自带的输出方法dbms_output. • dual表是 oracle中一个实际存在的表,任何用户均可读 取,常用在没有目标表的select语句块中, oracle保证dual 里面永远只有一条记录 . • 可以根据不同条件抛出多个自定义异常. • 存储过程参数不带取值范围,in表示传入,out表示输出. • function和存储过程区别:function必须有返回值, function里的参数可以直接被赋值,function可以单独执 行,存储过程必须通过exec执行。 • 数据隐式转换:用连接操作符||时,oracle会把非字符类 型的数据转换为字符类型 ,例如: select 123||‘temp’ from dual ;对于select语句,oracle会把字段的数据类 型隐式转换为变量的数据类型 . • 调出一个column的数据: for I in {select * from test_case_runtime_log } loop dbms_output.putline(I.id); end loop’; • If..else写完整. • 不推荐使用goto. • 2009年7月20日 • 对于Record的应用,例:Dept_obj.ID :=1,表示赋值 • Upper把小写改为大写 •%rowtype也是定义一个Record类型 • 游标也可以进行变量的传入与传出 • NO_Data_found是一个内置的Exception • 动态执行存储过程时,可以通过CHR(39)将单引号转 换成字符 • Execute immediate 'SQL_String' into temp,只有一条记 录时才可以这样用,否则要用临时表 • Execute immediate 'create table temp_test as select :1 name, :2 value 'using 'name1','value1', 其中:1,:2指占位 符,2指第二个位置 • 常用函数:ceil(),取比给定值大的最小一个整数 floor(),取比给定值小的最大一个整数 mod(),取余 round(),四舍五入 Thanks Q & A ©2008 iSoftStone Holdings Ltd. All Rights Reserved.
还剩46页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 5 金币 [ 分享pdf获得金币 ] 0 人已下载

下载pdf

pdf贡献者

lw1386589

贡献于2014-08-24

下载需要 5 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf