`
nikoloss
  • 浏览: 33429 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

oracle过程参数可能为空或多个值的查询(一)

阅读更多
   今天在网上逛笔记本,挑眼花了,好在我的要求比较明确,i3或者i5的处理器,独立显卡,4G以上内存……
品牌:√ 不限
价格:√ 4000-5000
屏幕尺寸:√ 15英寸
等等

    我在勾选了我的需求之后,符合条件的产品就被搜索出来了。然而这些条件并不是必须的,你可以勾选其中几个,也可以不勾选,不勾选的话就是完全查询了。
    于是我在想在oracle存储过程里要怎么解决这个问题呢。
    最容易让人想到的是一个参数一个参数的判断,然后去拼接sql,我非常不喜欢拼接sql这种方式,太繁琐而且容易出错并且效率还很低。不在万不得已的情况下,不要使用拼接sql。那么我们怎么简单高效而优雅的解决这个问题呢。
    巧用COALESCE函数来解决它!
    为了测试,我们简单的创建一张产品表,为了方便就建了一张TEST表吧,四个varchar2字段(CPU_,SCREEN,PRICE,ERA)
PS:由于CPU是oracle的保留字段,所以注意我用的是CPU_。
-----------------------------------------------------
TEST|CPU_ |SCREEN|PRICE| ERA
-----------------------------------------------------
          | i3       | 13         | 3999  | 2011
-----------------------------------------------------
          | i3       | 14         | 4399  | 2012
-----------------------------------------------------
          | i5       | 15         | 5200  | 2011
-----------------------------------------------------
          | i5       | 13         | 5499  | 2012
-----------------------------------------------------
          | i7       | 15         | 6999  | 2012
-----------------------------------------------------
创建包,过程
CREATE OR REPLACE PACKAGE PKG_DEMO IS
--定义游标
  TYPE P_CURSOR IS REF CURSOR;
--过程申明
  PROCEDURE TEST_SELECT(V_VAR1 IN VARCHAR2,
                        V_VAR2 IN VARCHAR2,
                        V_VAR3 IN VARCHAR2,
                        V_VAR4 IN VARCHAR2,
                        CR OUT P_CURSOR);
END PKG_DEMO;
------------------------------
CREATE OR REPLACE PACKAGE BODY PKG_DEMO IS
--创建包体
 PROCEDURE TEST_SELECT(V_VAR1 IN VARCHAR2,
                       V_VAR2 IN VARCHAR2,
                       V_VAR3 IN VARCHAR2,
                       V_VAR4 IN VARCHAR2,
                       CR OUT P_CURSOR)
     AS
     BEGIN
--这样在参数为空的时候查询条件就成了CPU_=CPU_ AND SCREEN=SCREEN......
       OPEN CR FOR 'SELECT * FROM TEST WHERE CPU_=COALESCE(:X,CPU_)
                      AND SCREEN=COALESCE(:X,SCREEN) 
                      AND PRICE=COALESCE(:X,PRICE) 
                      AND ERA=COALESCE(:X,ERA)' 
                      USING 
                      V_VAR1,
                      V_VAR2,
                      V_VAR3,
                      V_VAR4;
     END;
END PKG_DEMO;

嗯,这么一来,是不是就把任务简化了许多?
测试一下它吧。
DECLARE
RC SYS_REFCURSOR;
REC TEST%ROWTYPE;
BEGIN
  PKG_DEMO.TEST_SELECT('i5','15',NULL,'',RC);--在oracle中''和null是一样的。
  LOOP FETCH RC INTO REC;
  EXIT WHEN RC%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('CPU='||REC.CPU_||
                         ' SCREEN='||REC.SCREEN||
                         ' PRICE='||REC.PRICE||
                         ' ERA='||REC.ERA);
  END LOOP;
END;

这样我们就把i5处理器和15寸屏幕的产品给搜索出来了

CPU=i5 SCREEN=15 PRICE=5200 ERA=2011

总结:很多网上的人也遭遇过此类问题,但是他们通过IF X IS NULL THEN 来拼接sql,不仅要一个一个的判断,而且这种写法效率很低,代码的可读性也非常差,本来一句话的代码,可能要写上好几十行。而且执行效率非常低下。而隐式的绑定变量能够让oracle在share_pool中把这句话缓存起来。下次使用就非常的快了。这是至关重要的,同样查询1000条语句,动态拼接sql所使用的时间要比绑定变量所使用的时间就有可能慢100个数量级。在一个笔记本门户网站上,很多时候,远不止1000个人并发检索自己心仪的笔记本。
好了,可能你也注意到了,有时候一个条件可能被勾选多个值,比如CPU 我即勾选了i3又勾选了i5,这种情况下我们要怎么处理呢?下一篇中我们就来实现它。



分享到:
评论

相关推荐

    oracle 存储过程的基本语法

    - **`参数列表`**:参数列表可以为空,也可以包含多个参数。参数可以定义为输入参数(IN)或输出参数(OUT)等。 - **`局部变量声明`**:在存储过程中定义局部变量,用于存储中间结果或临时数据。 #### 三、SELECT INTO...

    oracle某个字段多行记录转化为一行

    这种方法在处理大量数据时非常有用,特别是在需要将多个值汇总到一个字段中的场景下。 ### 标题理解:“oracle某个字段多行记录转化为一行” 此标题明确指出了解决的问题:在Oracle数据库中,如何将特定字段的多行...

    oracle11g导出空表、少表的解决方案

    当这个参数的值为 TRUE 时,Oracle 会在表中有数据时才创建 segment,以节省空间。而当这个参数的值为 FALSE 时,Oracle 会在创建表时就分配 segment。 allocate extent 语句 allocate extent 语句用于分配 ...

    oracle存储过程基础教程

    2. **OUT参数**:从存储过程传出数据,初始值为空,过程内部赋值后返回。 3. **IN OUT参数**:既可读又可写,过程内部可以改变其值。 五、存储过程的返回值 1. **RETURN语句**:通过RETURN关键字,可以指定一个...

    oracle存储过程详解-开发技术

    4. **代码重用性**:存储过程可以被多个应用程序共享,避免了重复编写相同的代码。 #### 三、Oracle存储过程的基本语法 ##### 1. 创建存储过程 ```sql CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数1 IN ...

    oracle存储过程

    例如,提供的示例中展示了一个名为`pro_name`的存储过程,它接受多个参数,并构建动态SQL语句进行查询。在这个过程中,首先声明了一个变量`@S1`用于存储最终的SQL查询字符串。通过添加初始的`1=1`条件,后续可以根据...

    Oracle9i的init.ora参数中文说明

    如果为此参数指定了一个值, 则该值将覆盖 make 文件中的默认值。 值范围: C 编译程序的完整路径。 默认值: 无 remote_dependencies_mode: 说明: 用于指定数据库为远程 PL/SQL 存储的过程处理被依赖对象的方式。如果...

    Oracle提高查询效率的方法

    6. 参数设置:在表中定义字段或者存储过程、函数中定义参数时,将参数的大小设置为合适即可,勿设置太大。 7. Between 和 IN 的选择:Between 在某些时候比 IN 速度更快,Between 能够更快地根据索引找到范围。 8....

    判断记录是否存在,取某个特定字段值,取多个字段值

    本文将深入讲解如何在Delphi中判断记录是否存在以及如何获取特定或多个字段的值,这些技巧是开发常用函数的一部分。 首先,我们来看`GetFieldsValue`这个函数。它的主要任务是根据提供的SQL查询语句来获取一个记录...

    oracle基础--表、视图、过程、函数、游标、异常

    视图是虚拟表,它基于一个或多个表的查询结果。视图可以简化复杂查询,隐藏底层表的结构,提供安全访问控制,并且可以作为更新数据的接口。创建视图时,可以使用SELECT语句定义其结构,用户通过操作视图来间接操作...

    Oracle经典教程1——走进Oracle

    每个表空间包含一个或多个数据文件。 #### 本章总结 本章介绍了Oracle数据库中的一些关键对象及其管理方法。了解这些对象对于有效地设计和管理数据库至关重要。 ### PL/SQL程序设计 #### PL/SQL简介 PL/SQL...

    oracle非数字合计,将字段中含有汉子的数据自动转换为 数字0

    根据提供的文件信息,本文将详细介绍如何实现“oracle非数字合计”,即将字段中含有汉字或其他非数字字符的数据自动转换为数字0的过程。 ### 一、问题背景 在实际工作中,可能会遇到这样的情况:某个字段中本应...

    Oracle存储过程

    存储过程是由一个或多个SQL和PL/SQL语句组成的代码块,它们被存储在数据库中并可以由用户或者应用程序调用。存储过程有以下优点: 1. 代码复用:存储过程可以被多次调用,减少了代码重复,提高开发效率。 2. 安全性...

    oracle存储过程学习经典入门.doc

    Oracle 存储过程是数据库管理系统Oracle中的一个重要特性,它允许开发者编写一组SQL语句和PL/SQL代码,形成一个可重复使用的程序单元。存储过程可以提高数据库应用的性能,减少网络流量,并且提供更好的安全性。以下...

    sqlserver中创建类似oracle序列的存储过程

    - 在实际应用中,可能还需要考虑并发问题,因为多个用户同时调用该存储过程可能会导致序列值重复或跳过。 - 可以考虑使用事务和锁定机制来确保数据的一致性和准确性。 - 另外,为了提高性能,可以考虑使用表变量或...

    oracle9i初始化参数中文说明

    - **说明**:此参数决定多个监听器地址是单独指定还是使用一个 `ADDRESS_LIST` 字符串来指定。自 8.1.3 版本起,该参数已被废弃。 - **值范围**:`TRUE` 或 `FALSE` - **默认值**:`FALSE` #### mts_servers 参数 ...

    oracle常见报错

    如果尝试获取全表DML锁定但`DML_LOCKS`参数的值为0,则会触发此错误。确保`DML_LOCKS`参数值大于0。 #### ORA-00063: 超出LOG_FILES的最大数 当重做日志文件的数量超过了`LOG_FILES`参数定义的最大值时,会引发此...

    oracle实验报告

    2、 定义一个为修改职工表(emp)中某职工工资的存储过程子程序,职工名作为形参,若该职工名在职工表中查找不到,就在屏幕上提示“查无此人”然后结束子程序的执行;否则若工种为MANAGER的,则工资加$1000;工种为...

Global site tag (gtag.js) - Google Analytics