oracle储存过程培训资料
- 格式：doc
- 大小：137.00 KB
- 文档页数：27
ORACLE储存过程开发指南文档编号：001版本：1.0二零零九年七月1.版本更新说明2.目录oracle存储过程开发指南 (2)1版本说明 (3)2目录...................................................................................... 错误！未定义书签。
2.1说明实例中:红色oracle代表关键字.黄色代表自定义.绿色表示例解说明.2.2oracle储存过程开发须知2.2.1需要的技巧1.SQL基础知识,包括插入、修改、删除等2.使用Oracle'sSQL*Plus工具的基本技巧3.使用Oracle'sPL/SQL编程语言的基本技巧2.2.2存储过程常用数据类型varchar2(长度)可变长字符串char(长度) 定长number()表示整数或者浮点数number(8) number(8,2)interger 整型2.2.3存储的基本结构1.存储过程包含三部分：声明，执行部分，异常.2.3创建储存过程简单命令2.3.1创建储存过程1.从Window打开SQL*Plus并且从SQL*Plus登录到你的数据库;打开skeleton.sql文件.2.在SQL>命令提示符下输入以下命令：SQL>@skeleton注释:(SQL*Plus装载skeleton.sql文件的内容到SQL*Plus缓冲区,并且执行SQL*Plus语句;SQL*Plus会通知你存储过程已经被成功地创建)3.写一个存储过程,实例:CREATE OR REPLACE PROCEDURE skeletonISBEGINDBMS_OUTPUT.PUT_LINE('HelloWorld!');END;2.3.2运行一个存储过程命令SQL>EXECUTE skeleton;注释(SQL*Plus输出一下信息确信存储过程成功执即PL/SQLproceduresuccessfullycompleted).2.3.3查看储存打印信息1.在SQL*Plus命令行提示符,键入:SQL>SET SERVEROUTPUT ON2.再次敲入SQL>EXECUTE skeleton即可.注释:查看存储过程中的打印语句信息,必须实行以上命令.2.3.4删除一个存储过程命令: SQL>DROP PROCEDURE skeleton;2.4存储过程2.4.1存储过程基本结构CREATE OR REPLACE PROCEDURE 存储过程名字(参数1 IN NUMBER,参数2 IN NUMBER) IS变量1 INTEGER :=0;变量2 DATE;BEGINEND 存储过程名字;2.4.2存储过程的基本语法2.4.2.0 LOOP循环Counter :=0;LOOPcounter :=counter +1 ;EXIT WHEN counter =5;END LOOP;2.4.2.1 IF 判断IF str1 > str2 thenresult :=1;ELSIF str2 > str1 THENresult := -1;ELSEresult := 0;END IF2.4.2.2 while循环Counter :=0;WHILE counter < 6 LOOPcounter :=counter +1;END LOOP;2.4.2.3变量定义及赋值declarerealsal emp.sal%type; realname varchar(40); realjob varchar(40); Price number(5,2); Product_id interger;realjob := ‘work’ ;Product_id := 100001 ;realname := 'Brunhilda';Price := 3.1415;this_day := TODAY;2.4.2.4 for使用1,方式一:BEGINFOR cur_result REVERSE 1..5 LOOPBEGINDBMS_OUTPUT.PUT_LINE(cur_result );END;END LOOP;END;2.方式二:ISCURSOR cur IS SELECT * FROM xxx;BEGINFOR cur_result in cur LOOPBEGINV_SUM :=cur_result.列名1+cur_result.列名2 END;END LOOP;END;2.4.2.5带参数的cursorCURSOR C_USER(C_ID NUMBER) ISSELECT NAME FROM USER WHERE TYPEID= C_ID NUMBER; OPEN C_USER(变量值);LOOPFETCH C_USER INTO V_NAME;EXIT FETCH C_USER%NOTFOUND;END LOOP;…………………….CLOSE C_USER;注释: 与存储过程和函数相似,可以将参数传递给游标并在查询中使用.2.4.2.6存储过程操作符+-*/|| 合并如：sp_str1=“ASD”||”ERT”,则：sp_str1=“ASDERT”2.4.2.7存储过程结构块BEGIN第一步处理；END;BEGIN第二步处理；END;BEGIN第三步处理；END;2.4.2.8存储过程异常错误控制exceptionwhen too_many_rows thenDBMS_OUTPUT.PUT_LINE('返回值多于1行');when others thenDBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');2.4.2.9存储过程游标FOR rec IN cur_test LOOPDBMS_OUTPUT.put_line(rec.p_number);DBMS_OUTPUT.put_line(rec.p_name);DBMS_OUTPUT.put_line(rec.p_manager);DBMS_OUTPUT.put_line(rec.p_client);END LOOP;2.4.2.10存储过程条件表达式存储过程if条件表达式比较符<\>\<=\>=\=\<>\!=AND OR NOT(NOT) BETWEEN AND(NOT) IN ( , , , ,)IS (NOT) NULL(NOT) LIKE2.4.2.11存储过程控制语句的跳出exit wheneixt注释:exit 语句可立即结束循环exit when 语句是在指定条件下结束循环,并且可以出现在循环代码中的任何位置.2.4.2.12存储过程返回值outOracle 存储过程的返回值,必须在创建一个存储过程时定义.2.4.2.13存储过程的执行Execute spdata;Execute spdata(var1);Execute spdata(var1=值1,var2=值,2);2.4.2.14隐形游标1.dml语句是指:insert、update、delete和lock table的操作.2.对dml操作会产生隐式游标.3.隐式游标只使用sql%found,sql%notfound,sql%rowcount三个属性.4.sql%found,sql%notfound是布尔值，sql%rowcount是整数值。
5. sql%found为true,sql%notfound为false,6.在执行任何dml语句之前,sql%rowcount的值都是null7. %ROWCOUNT返回当前位置为止游标读取的记录行数.2.4.2.15 %type属性1. 在pl/sql中可以将变量和常量声明为内建或用户定义的数据类型,以引用一个列名,同时继承他的数据类型和大小.注释:delcarev_a number(5):=10;v_b v_a%type:=15;v_c v_a%type;2.5开发一个储存过程2.5.1不带参数的储存过程注释:1.如果没有or replace语句，则仅仅是新建一个存储过程,如果系统存在该存储过程，则会报错。
Create or replace procedure 如果系统中没有此存储过程就新建一个，如果系统中有此存储过程则把原来删除掉，重新创建一个存储过程.2.存储过程名定义：包括存储过程名和参数列表,参数名和参数类型,参数名不能重复.3.as (is )为关键字,可以理解为pl/sql的declare关键字,用于声明变量.create or replace procedure runbyparmetersbeginselect count(*) into icount from emp where sal>isal and job=sjob;if icount=1 then....else....end if;exception //存储过程异常when too_many_rows thenDBMS_OUTPUT.PUT_LINE('返回值多于1行');when others thenDBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');end;2.5.2带参数存储过程含赋值注释:1.sal%type目的是为了保持与传参过来的数据宽度一致.2.IN /OUT 即可作输入参数，也可作输出参数。
3.变量声明块：紧跟着的as (is )关键字,用于声明变量。
4.IN 按值传递，并且它不允许在存储过程中被重新赋值。
如果存储过程的参数没有指定存参数传递类型，默认为IN.5.OUT 参数：作为输出参数，需要注意，当一个参数被指定为OUT类型时，就算在调用存储过程之前对该参数进行了赋值，在存储过程中该参数的值仍然是null.create or replace procedure runbyparmeters (isal in emp.sal%type,sname out varchar, sjob in out varchar)as icount number;beginselect count(*) into icount from emp where sal>isal and job=sjob;if icount=1 then....else....end if;exceptionwhen too_many_rows thenDBMS_OUTPUT.PUT_LINE('返回值多于1行');when others thenDBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错');end;2.5.3参数的存储过程默认值注释:1.可以通过default 关键字为存储过程的参数指定默认值。
