Oracle存储过程入门

 

学习的时候要静下心去学习，不要心浮气躁。其中找了一些网上的资料和自己的理解，再做以实践，由于存储过程内容是比较多的，我这里只能做个入门，希望大家能自己深入学习。

 

--  文中这个符号，等于注释的意思

 

1.基本结构
　　CREATE OR REPLACE PROCEDURE 存储过程名字
　　(
　　参数1 IN 数据类型,
　　参数2 IN 数据类型
　　) IS
　　变量1 数据类型;
　　变量2 数据类型;
　　BEGIN

         ********* --存储过程的业务，也就是你想要做的事
　　END 存储过程名字

 

      上面的大写字母是保留字，数据类型有很多，说几个基本的，以后靠大家自己去学习，比如：NUMBER,VARCHAR,VARCHAR2,这里的等于符号是 :=    这里定义变量的方式比较独特采用的是

      变量  数据类型;

      eg

      t_name VARCHAR2(100);

      t_age    NUMBER;

 

     如何给变量赋值

     t_name := 'aaaaaaa';

     t_age := 10;

 

 

　　2.SELECT INTO STATEMENT

　　将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
　　例子：
　   BEGIN
　　   select col1,col2 into 变量1,变量2 FROM 表名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

      CREATE OR REPLACE PROCEDURE TEST(name in number)
　   IS
　　CURSOR cur IS select * FROM xxx; --这里是定义一个游标，把查询结果放入游标中，

      --游标就象指针，大家可以这么去理解

     
　   BEGIN
　      FOR cur_result in cur LOOP
　         BEGIN
　　         V_SUM :=cur_result.列名;
　         END;
　      END LOOP;
　　END;

 

      看看下面的例子

 

      CREATE OR REPLACE PROCEDURE TEST(name in number)
      is  
      V_SUM varchar2(200);
      cursor cur is select * from LAD_USER;
      BEGIN
         for V_RESULT in cur LOOP
             BEGIN
                   V_SUM := V_RESULT.User_Name;
             END;
         end LOOP;
         DBMS_OUTPUT.put_line(V_SUM); --输出命令
      END TEST;

 

　　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单步调试

 

      9.运行

      我第一次学习的时候很苯，连存储过程都没创建就去执行调用命令，请大家别和我一样。

 

      有2种方式去执行

      1.首先dos环境连接到oracle数据库

 

       sqlplus 用户名/密码@数据库地址，通过这个命令可以连接到数据库。

 

       比如：sqlplus TEST_ZJ/111111@192.168.10.10_ORCL     

 

      

       这样接连接上数据库了，下面把要创建的存储过程复制到一个txt文本文件，然后把文本文件修改为TEST.sql 这样类型的文件。

      这里我直接把EST.sql存在了E:/根目录

 

     连接上以后，接着执行命令  @TEST

                                            /

   

    执行完这2个命令，存储过程就被创建了。

 

    调用存储过程  call TEST(1); 

 

    这样dos的创建和调用就结束了，我感觉还是挺麻烦的，不过这是基础，还是了解的好。

 

 

    另一种办法，就是用工具了，就是第8点说的办法，下一个PLSQL Developer ，推荐，非常好用，连接上后，直接创建一个存储过程，通过执行，才能把存储过程写进数据库，再用Test WinDow进行测试，命令直接写call TEST(1) 然后点左上角的执行，就可以看到了结果了

 

  

 

 

 

 

 

 

 

 

 

 

 

1.命令格式
  存储过程是一个PL/SQL程序块，接受零个或多个参数作为输入(INPUT)或输出(OUTPUT)、或既作输入又作输出(INOUT)，与函数不同，存储过程没有返回值，存储过程不能由SQL语句直接使用，只能通过EXECUT命令或PL/SQL程序块内部调用，定义存储过程的语法如下：

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->PROCEDURE  Name [ (Parameter[,Parameter,] )]
IS |AS
   [ Local Declarations]
BEGIN
   Execute  statements;
   [ EXCEPTION Exception Handlers]
END  [ Name ] ;

2.调用
  存储过程可以直接用EXECUT命令调用或PL/SQL程序块内部调用。用EXECUT命令调用存储过程的格式如下：

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->SQL > EXCUTE  Proc_Name(par1, par2…);

  存储过程也可以被另外的PL/SQL块调用，调用的语句是：

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->DECLARE  par1, par2;
BEGIN
  Proc_Name(par1, par2…);
END ;

3.释放
  当某个存储过程不再需要时，应将其从内存中删除，以释放它占用的内存资源。释放过程的语句格式如下：

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->SQL > DROP   PROCEDURE Proc_Name;

4.实例：
  编写存储过程，显示所指定雇员名所在的部门名和位置。

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->CREATE  OR   REPLACE   PROCEDURE DeptMesg(pename emp.ename % TYPE,
                                     pdname OUT dept.dname % TYPE,
                                     ploc   OUT dept.loc % TYPE)  AS
BEGIN
   SELECT  dname, loc
     INTO  pdname, ploc
     FROM  emp, dept
    WHERE  emp.deptno =  dept.deptno
      AND  emp.ename =  pename;
END ;

  调用：

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->VARIABLE vdname  VARCHAR2 ( 14 );
VARIABLE vloc  VARCHAR2( 13 );
EXECUTE  DeptMesg(' SMITH ' , :vdname￡? :vloc);
PRINT  vdname vloc;

 

 

看完后，我自己学习写的demo

 

 

CREATE OR REPLACE PROCEDURE DeptMesg(peid OUT LAD_USER.USER_ID%TYPE,pename OUT LAD_USER.USER_NAME%TYPE) IS
BEGIN
  SELECT USER_ID, USER_NAME INTO peid,pename
    FROM LAD_USER
   WHERE USER_ID = 1;
END DeptMesg;

 

 

测试代码

 

-- Created on 2009-7-22 by ADMINISTRATOR
declare
  -- Local variables here
  id integer;
  names varchar2(100);
begin
  -- Test statements here 
  Dbms_Output.put_line('sssssssssssssss');
  DeptMesg(peid => id, pename => names);
  Dbms_Output.put_line('id:'||id);
  Dbms_Output.put_line('names:'||names);
end;

 

 

希望大家能看明白

http://blog.csdn.net/cocojiji5/article/details/4364253