在 PL/SQL 中使用带有“LIKE %”（例如“variable%”）的变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10150723/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use a variable with "LIKE %" (e.g. "variable%") in PL/SQL?
提问by user980411
The question is similar to using LIKEin SQL *PLUS, where a select statement contains a LIKEclause as follows:
该问题类似于在SQL *PLUS 中使用LIKE，其中 select 语句包含LIKE子句，如下所示：
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%';
same as above
同上
EDIT: I need to get somethingas a parameter, meaning, the select statement is executed within a stored procedure.
编辑：我需要获取一些东西作为参数，这意味着 select 语句是在存储过程中执行的。
EDIT 2:
编辑2：
create procedure proc1 (search VARCHAR) is
cursor c is select student_name from students where student_name like 'search%';
--I know using 'search%' retrieves student names containing 'the key search', but is there any other way to use such a variable.
--我知道使用“search%”检索包含“key search”的学生姓名，但是还有其他方法可以使用这样的变量。
do something;
end;
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.
简而言之，我需要选择包含作为参数传递的值的学生姓名；这可能不是全名，可能足以在 like 子句中使用。
回答by Gaurav Soni
As per my understanding to your issue, you are using variable searchwithin quotes. Put your variable outside the quotes, e.g.:
根据我对您的问题的理解，您search在引号中使用了变量。将您的变量放在引号之外，例如：
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;
