Oracle存储过程查询，例如不能与游标一起使用 [英] Oracle stored procedure query like not working with cursor

问题描述

我是oracle的新手，正尝试使用以下pl/sql代码创建存储过程.

I am new to oracle and trying to create a stored procedure with the following pl/sql code.

代码有什么问题?当我用"1605％"的硬代码值替换searchString时，将执行查询，但不使用变量.

What is wrong with the code?when i replace the searchString with hard code value of '1605%' then the query is executed but not with the variable.

create or replace PACKAGE BODY MAT_INFO AS

  PROCEDURE mat_details(mat_id IN varchar, mat_cur OUT SYS_REFCURSOR) AS

  searchString varchar2(700);
  BEGIN

    searchString := ''''||mat_id||'%''';
    OPEN mat_cur FOR SELECT mi.* FROM S_JAMM_MAT_INFO mi WHERE mi.MAT_ID LIKE searchString;
  END mat_details;

END MAT_INFO;

有人可以帮我在这里做什么错吗?

Can anyone help me what am i doing wrong here?

谢谢

推荐答案

您在使用不必要的引号:

You are using unnecessary quotes:

SQL> declare
  2      mat_id        varchar2(100) := 'X';
  3      searchString  varchar2(100) := ''''||mat_id||'%''';
  4      searchString2 varchar2(100) := ''||mat_id||'%';
  5      num number;
  6  begin
  7      select count(1)
  8      into num
  9      from dual
 10      where 'X' like searchString;
 11      --
 12      dbms_output.put_line('1. num= ' || num);
 13      --
 14      select count(1)
 15      into num
 16      from dual
 17      where 'X' like searchString2;
 18      --
 19      dbms_output.put_line('2. num= ' || num);
 20  end;
 21  /
1. num= 0
2. num= 1

PL/SQL procedure successfully completed.

SQL>

如果您要使用动态SQL，则双引号可能会很有用，但是在静态查询中，这意味着您要查找包含引号作为字符串一部分的模式.

Your double quoting could be useful if you would use a dynamic SQL, but in your static query it means that you are looking for a pattern containing quotes as part of the string to look for.

这篇关于Oracle存储过程查询，例如不能与游标一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！