oracle - 简单的 Oracle 存储过程

Question

我有这个 SQL 查询:

select title
from DEPARTMENT;

我尝试编写一个存储过程:

create PROCEDURE select_some
(whats VARCHAR2 ,c_select_some OUT SYS_REFCURSOR) 
AS 
BEGIN 
OPEN c_select_some FOR
SELECT whats
FROM department;
END select_some;
/

但是当我使用“title”参数执行它时，我得到了 8 行带有“title”而不是实际内容的行。怎么了？

执行:

var whats varchar2(20)
variable whats = 'Title'
variable mycursor refcursor;

exec select_some (:whats, mycursor);

Answer 1

为此，您需要使用动态 SQL。

类似这样的事情

create or replace procedure select_from_department(
  col_name in varchar2,
  c_res      out sys_refcursor
) 
is
  l_sql varchar2(300);
begin
  l_sql := 'select ' || dbms_assert.simple_sql_name(col_name) || ' from departments';
  open c_res for l_sql ;
end;

DEMO