热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

Oracle之存储过程

PLSQL块:PLSQL块都用于解决特定问题,PLSQL块可以有参数,也可以相互调用。PLSQL块可以作为模式对象在Oracle数据库中保存,根据用途可以

PL/SQL块:

     PL/SQL块都用于解决特定问题,PL/SQL块可以有参数,也可以相互调用

     PL/SQL块可以作为模式对象在Oracle数据库中保存,根据用途可以分为两类:

         1、存储过程:用于特定目的,执行一系列操作的PL/SQL块

         2、自定义函数:通过计算返回调用者一个值的PL/SQL块


存储过程:


    存储过程是由流控制和SQL语句组成的,经编译和优化后存储在数据库服务器中,使用时只需调用即可。

    

    存储过程类似于高级程序设计语言中模块的概念,它将一些内部联系的命令组成一个个存储过程,通过参数在存储过程之间传递数据是模块化设计思想的主要内容。


    SQL语句执行的时候要先编译,然后执行。存储过程就是编译好了的一些SQL语句。可以直接执行,所以执行效率较高。


存储过程的优点: 

     1、存储过程大大增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。

     2、可保证数据的安全性和完整性:

                  a:通过存储过程可以使没有使用权限的用户间接地存取数据,从而保证数据的完全。

                   b : 通过存储过程可以使相关的动作在一起发生,从而维护数据库的完整性。

     3、在运行存储过程前,数据库已对其进行了语法和句法分析,并给出了优化执行方案,这种已经编译好的存储过程极大地改善SQL语句的性能。

     4、使用存储过程可以降低网络的通信量。

     5、使体现应用需要的运算程序放入数据库服务器中,以便集中控制。


存储过程定义:

 存储过程的创建:

    1、创建存储过程的语法结构,完整的存储过程结构如下:

            create [or replace]  procedure  存储过程名称

             as             声明语句     

             begin       执行语句段;

                            exception

                            异常处理语句段

             end;

    2、存储过程示例:

        create  or  replace  procedure  scott.tempprocedure   as

            tempdate  scott.emp.hiredate%type;

        begin   select  hiredate  into tempdate

                       from  scott.emp

                       where   scott.emp.SAL =  1500;

                       dbms_output.put_line('存储过程生成的变量值:'||tempdate);

        end;

 

存储过程的修改:

     存储过程的修改有两个方法:

        方法一:如果以前已经写了存储过程的全部或部分,现在需要修改或者继续完成,可以用SLQPlus  Worksheet打开以前保存的文件,方法是在SLQPlus  Worksheet的菜单栏选择“文件”-->“ 打开”命令,将创建存储过程的SQL文件调出进行修改,修改完毕后保存并重新执行创建存储过程即可。

         方法二:1、在企业管理器里选中要修改的存储过程,用鼠标右键点击,在出现的快捷菜单里选择“查看/编辑详细资料”菜单项;2、在弹出“编辑过程”窗口中的文本域中修改存储过程之后点击窗口中的“应用”按钮即可完成对存储过程的修改。


存储过程的执行:

    创建存储过程的最终目的是为了执行,所以创建存储过程后,需要调用它,才会让它执行。要执行创建的存储过程,必须通过主程序来调用存储过程,如果我们要调用前面写好的存储过程,可以在SQLPlus Worksheet中执行下列PL/SQL程序:

    


带参数的存储过程:

    创建带参数的存储过程的语法如下:

        create  [or replace]  procedure   过程名

            参数1  [in|out|in out]  数据类型

             [,参数2  [in|out|in out]  数据类型] ...

               {is|as}  pl/sql  语句

    1、参数类型

          在PL/SQL存储过程中,可以有3种类型的参数,其功能简要概述如下:

            in参数:读入参数,主程序向存储过程传递参数。

            out参数:读出参数,存储过程向主程序传递参数值。    

            in  out参数:双向参数,存储过程与主程序双向交流数据。

        定义带参数的存储过程:

                create  or  replace  procedure  scott.tempprocedure1(

                            tempdeptno   in   scott.dept.deptno%type,

                            tempdname    out   scott.dept.dname%type,

                            temploc           in out   scott.dept.loc%type)

                as        loc1  scott.dept.loc%type;

                            dname1  scott.dept.dname%type;

                begin   select  loc  into loc1 

                             from  scott.dept

                             where deptno=tempdeptno;

                            select dname into  dname1

                             from  scott.dept

                             where   deptno = tempdeptno;

                             temploc:='地址:'||loc1;

                             tempdname:='姓名'||dname1;

                   end;

该存储过程的功能是:从数据表scott.dept中寻找deptno字段等于一个变量(即调用参数,这里我们命名为tempdeptno的dname和loc字段,并将这两个字段的值赋给新的变量


带参数的存储过程的调用:

       在主程序中的实际参数和过程中的形式参数的传递有很多中方法,这里推荐采取——对应的办法,按对应的位置传递参数。要求实际参数和形式参数在数据类型和位置排列上做到完全一致。

         set  serveroutput  on

         declare  myno  scott.dept.deptno%type;

                       mydname  scott.dept.dname%type;

                       myloc   scott.dept.loc%type;

           begin   myno:=10;

                       mydname:='';

                       myloc:='';

                       scott.tempprocedure1(myno,myname,myloc);

                         dbms_output.put_line(myno);

                         dbms_output.put_line(myname);

                         dbms_output.put_line(myloc);

        end;



自定义函数:

         创建自定义函数语法如下:

             create  or  replace  function  函数名(参数名    参数类型,......)

                  return  返回值类型

              is

                  begin

                    ......

                  end   函数名;

例:

         create  or replace  function 

                get_sal(empname  in  varchar2)  return

                number  is

                    Result   number;

                    begin

                    select sal  into  Result  from  emp where

                  ename  = empname;

                    return(Result);

                   end  get_sal;

        执行:SQL>var sal  number

                  SQL>exec:sal:=get_sal('scott');


使用包:

        包可以看做是过程和函数的集合,对过程和函数进行更好的封装。

       包的作用:包可以将任何出现在块声明的语句(过程,函数,游标,类型,变量)放于包中,相当于一个容器。将声明语句放入包中的好处是:用户可以从其他PL/SQL块中对其进行引用,因此包为PL/SQL提供了全程变量。

        包的包括包头和包体两部分,需要分别定义。

    定义包头:

        create  or  replace  package   包名

                    is      

                        过程名/函数名;        --申明了该包中的过程及函数

        end  包名;

    定义包体:

          create  or  replace  package  body  包名

                        is

                            过程/函数定义实现;

                end   包名;

    例:

            创建包头:

                create  or  replace package  fitpackage

                        is

                            procedure  myproc1(num in number);

                            procedure  myproc2;

                            function  myfunc(stuno  varchar2)  return  number;

                      end     fitpackage;

            创建包体:包名以及包内的函数,过程名以及参数列表都要和包头定义中的内容一致

                create  or   replace  package  body   fitpackage

                    is

                        procedure  myproc1(num in number)

                            as 

                            ......

                            end    myproc1;

                        procedure  myproc2

                            as

                            ......

                            end  myproc2;

                        function  myfunc(stuno  varchar2)

                            return   number

                            as

                             ......

                            end   myfunc;

                        end fitpackage;


JAVA程序调用存储过程:

    JDBC通过CallableStatement接口提供对此类功能的支持。可以通过Connection对象的prepareCall()方法来,该方法的参数是一个String,是一种SQL转义语法:

    例如:

    

过程的参数:

    in参数的占位符为?,使用CallableStatement接口的setXXX()方法来为参数设置值。

   out参数的占位符为?,使用的时候必须先使用CallableStatement接口的registerOutParameter()方法注册此参数,最后通过getXXX()方法来取值。

    对于存储过程的返回值,除索引号为1外,其他与out参数用法一样。

    

带有输出参数:

    创建存储过程:

    create or replace procedure P_myPro_ConnOracle(empId in int,empName out String)

      as 

        begin

            select e.ename

            into empName

            from emp  e

            where  e.empno = empId;

            dbms_output.put_line('empId='||empId);

            dbms_output.put_line('empName='||empName);

        end;

    JDBC操作:

        //获得CallableStatement对象

            CallableStatement ctmt = conn.prepareCall("{call proc_forvalue(?,?)}");

       //设置输入参数

        ctmt.setString(1,type);

       //注册输出参数及数据类型

        ctmt.registerOutParameter(2,java.sql.Types.INTEGER);

       //调用存储过程,并查询输出参数的值

        ctmt.execute();

        result = ctmt.getInt(2);


使用游标返回结果集:

    创建存储过程:

        create or replace  package my_package

        as

        type my_cursor is ref cursor;

        end my_package;


        create or replace procedure my_test(test_result

                out  my_pakcage.my_cursor)

        as

        begin

        open  test_result  for

        select * from  dept;

        end;


使用游标返回结果集:

    JDBC操作:

           //获得CallableStatement对象

            CallableStatement proc= conn.prepareCall("{call my_test(?)}");

       //设置输入参数

        proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);

       //调用存储过程,并获得第一个产生的结果集

        proc.execute();

        rs = (ResultSet)proc.getObject(1);//1指的是参数1

                while(rs.next()){

                System.out.println(rs.getString(1)+","+rs.getString(2));

        }



推荐阅读
  • 本文介绍了Web学习历程记录中关于Tomcat的基本概念和配置。首先解释了Web静态Web资源和动态Web资源的概念,以及C/S架构和B/S架构的区别。然后介绍了常见的Web服务器,包括Weblogic、WebSphere和Tomcat。接着详细讲解了Tomcat的虚拟主机、web应用和虚拟路径映射的概念和配置过程。最后简要介绍了http协议的作用。本文内容详实,适合初学者了解Tomcat的基础知识。 ... [详细]
  • 本文介绍了Hyperledger Fabric外部链码构建与运行的相关知识,包括在Hyperledger Fabric 2.0版本之前链码构建和运行的困难性,外部构建模式的实现原理以及外部构建和运行API的使用方法。通过本文的介绍,读者可以了解到如何利用外部构建和运行的方式来实现链码的构建和运行,并且不再受限于特定的语言和部署环境。 ... [详细]
  • Centos7.6安装Gitlab教程及注意事项
    本文介绍了在Centos7.6系统下安装Gitlab的详细教程,并提供了一些注意事项。教程包括查看系统版本、安装必要的软件包、配置防火墙等步骤。同时,还强调了使用阿里云服务器时的特殊配置需求,以及建议至少4GB的可用RAM来运行GitLab。 ... [详细]
  • 本文介绍了解决Netty拆包粘包问题的一种方法——使用特殊结束符。在通讯过程中,客户端和服务器协商定义一个特殊的分隔符号,只要没有发送分隔符号,就代表一条数据没有结束。文章还提供了服务端的示例代码。 ... [详细]
  • 搭建Windows Server 2012 R2 IIS8.5+PHP(FastCGI)+MySQL环境的详细步骤
    本文详细介绍了搭建Windows Server 2012 R2 IIS8.5+PHP(FastCGI)+MySQL环境的步骤,包括环境说明、相关软件下载的地址以及所需的插件下载地址。 ... [详细]
  • 这是原文链接:sendingformdata许多情况下,我们使用表单发送数据到服务器。服务器处理数据并返回响应给用户。这看起来很简单,但是 ... [详细]
  • 如何使用Java获取服务器硬件信息和磁盘负载率
    本文介绍了使用Java编程语言获取服务器硬件信息和磁盘负载率的方法。首先在远程服务器上搭建一个支持服务端语言的HTTP服务,并获取服务器的磁盘信息,并将结果输出。然后在本地使用JS编写一个AJAX脚本,远程请求服务端的程序,得到结果并展示给用户。其中还介绍了如何提取硬盘序列号的方法。 ... [详细]
  • 阿,里,云,物,联网,net,core,客户端,czgl,aliiotclient, ... [详细]
  • 图解redis的持久化存储机制RDB和AOF的原理和优缺点
    本文通过图解的方式介绍了redis的持久化存储机制RDB和AOF的原理和优缺点。RDB是将redis内存中的数据保存为快照文件,恢复速度较快但不支持拉链式快照。AOF是将操作日志保存到磁盘,实时存储数据但恢复速度较慢。文章详细分析了两种机制的优缺点,帮助读者更好地理解redis的持久化存储策略。 ... [详细]
  • http:my.oschina.netleejun2005blog136820刚看到群里又有同学在说HTTP协议下的Get请求参数长度是有大小限制的,最大不能超过XX ... [详细]
  • 计算机存储系统的层次结构及其优势
    本文介绍了计算机存储系统的层次结构,包括高速缓存、主存储器和辅助存储器三个层次。通过分层存储数据可以提高程序的执行效率。计算机存储系统的层次结构将各种不同存储容量、存取速度和价格的存储器有机组合成整体,形成可寻址存储空间比主存储器空间大得多的存储整体。由于辅助存储器容量大、价格低,使得整体存储系统的平均价格降低。同时,高速缓存的存取速度可以和CPU的工作速度相匹配,进一步提高程序执行效率。 ... [详细]
  • 本文介绍了在使用Python中的aiohttp模块模拟服务器时出现的连接失败问题,并提供了相应的解决方法。文章中详细说明了出错的代码以及相关的软件版本和环境信息,同时也提到了相关的警告信息和函数的替代方案。通过阅读本文,读者可以了解到如何解决Python连接服务器失败的问题,并对aiohttp模块有更深入的了解。 ... [详细]
  • 利用Visual Basic开发SAP接口程序初探的方法与原理
    本文介绍了利用Visual Basic开发SAP接口程序的方法与原理,以及SAP R/3系统的特点和二次开发平台ABAP的使用。通过程序接口自动读取SAP R/3的数据表或视图,在外部进行处理和利用水晶报表等工具生成符合中国人习惯的报表样式。具体介绍了RFC调用的原理和模型,并强调本文主要不讨论SAP R/3函数的开发,而是针对使用SAP的公司的非ABAP开发人员提供了初步的接口程序开发指导。 ... [详细]
  • Java在运行已编译完成的类时,是通过java虚拟机来装载和执行的,java虚拟机通过操作系统命令JAVA_HOMEbinjava–option来启 ... [详细]
  • 单点登录原理及实现方案详解
    本文详细介绍了单点登录的原理及实现方案,其中包括共享Session的方式,以及基于Redis的Session共享方案。同时,还分享了作者在应用环境中所遇到的问题和经验,希望对读者有所帮助。 ... [详细]
author-avatar
mobiledu2502909493
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有