使用带有“LIKE %"的变量(例如“变量%")在 PL/SQL 中? [英] Use a variable with "LIKE %" (e.g. "variable%") in PL/SQL?

问题描述

问题类似于在SQL *PLUS中使用LIKE，其中一个select语句包含一个LIKE子句，如下所示:

The question is similar to using LIKE in SQL *PLUS, where a select statement contains a LIKE clause as follows:

select * from sometable where somecolumn LIKE 'something%';

如何在游标中使用相同的内容?我尝试使用以下方法:

How could one use the same within a cursor? I tried using the following:

 cursor c is select * from sometable where somecolumn like 'something%'; 

同上

我需要获取 something 作为参数，这意味着 select 语句在存储过程中执行.

I need to get something as a parameter, meaning, the select statement is executed within a stored procedure.

编辑 2:

create procedure proc1 (search VARCHAR) is

cursor c is select student_name from students where student_name like 'search%';

--我知道使用search%"可以检索包含key search"的学生姓名，但还有其他方法可以使用这样的变量.

--I know using 'search%' retrieves student names containing 'the key search', but is there any other way to use such a variable.

do something;

end;

简而言之，我需要选择包含作为参数传递的值的学生姓名；这可能不是全名，可能足以在 like 子句中使用.

In short, I need to select student names containing a value that is passed as a parameter; this may not be the whole name, and may suffice enough to be used within a like clause.

推荐答案

根据我对您的问题的理解，您在引号内使用了变量 search.将您的变量放在引号之外，例如:

As per my understanding to your issue, you are using variable search within quotes. Put your variable outside the quotes, e.g.:

 create or replace procedure PROC1(search VARCHAR2) 
 IS
  cursor test_cur(search IN VARCHAR2)
   IS
    SELECT student_name 
    FROM student
    WHERE student_name LIKE search||'%'; --you're putting you variable within quotes

 v_temp_var student.student_name%TYPE;

BEGIN

 OPEN test_cur(search);
  LOOP
   FETCH test_cur INTO v_temp_var;
    EXIT WHEN test_cur%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(v_temp_var);  
  END LOOP;

 CLOSE test_cur;

END test;

这篇关于使用带有“LIKE %"的变量(例如“变量%")在 PL/SQL 中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！