PL/SQL Procedure SELECT Input- Error PLS-00306

Question

你好，我是PL/SQL的初学者，希望能得到一些帮助。

这里是这个过程我的目标是在执行这个过程时输入一个5位数的整数(邮政编码)它会从表中选择这些值并显示出来就像我执行了像

这样的查询一样

SELECT * FROM customers WHERE customer_zipcode = "input zipcode".

create or replace PROCEDURE LIST_CUSTOMER_ZIPCODE(
p_zipcode IN customers.customer_zipcode%TYPE,
p_disp OUT SYS_REFCURSOR)
-- User input Variable, Display Variable
IS
BEGIN
OPEN p_disp for SELECT customer_first_name, customer_zipcode FROM customers 
WHERE customer_zipcode=p_zipcode;
EXCEPTION
-- Input Sanitization
WHEN no_data_found THEN
dbms_output.put_line('-1');
END;
EXEC LIST_CUSTOMER_ZIPCODE(07080); 

当我执行这个命令时，我总是得到这个错误。

https://i.stack.imgur.com/nCI8T.png

Answer 1

如果你正在使用SQL*Plus或SQL Developer，那么你可以声明一个绑定变量，然后调用传递变量的过程，然后打印它:

SELECT * FROM customers WHERE customer_zipcode = "input zipcode".
create or replace PROCEDURE LIST_CUSTOMER_ZIPCODE(
p_zipcode IN customers.customer_zipcode%TYPE,
p_disp OUT SYS_REFCURSOR
)
IS
BEGIN
OPEN p_disp FOR
SELECT customer_first_name, customer_zipcode
FROM   customers 
WHERE  customer_zipcode = p_zipcode;
EXCEPTION
-- Input Sanitization
WHEN no_data_found THEN
dbms_output.put_line('-1');
END;
/
VARIABLE cur SYS_REFCURSOR;
EXEC LIST_CUSTOMER_ZIPCODE('07080', :cur);
PRINT cur;

然而，你的异常处理块永远不会被调用，因为游标可以在不引发异常的情况下返回零行，所以过程可以简化为:

create or replace PROCEDURE LIST_CUSTOMER_ZIPCODE(
p_zipcode IN customers.customer_zipcode%TYPE,
p_disp OUT SYS_REFCURSOR
)
IS
BEGIN
OPEN p_disp FOR
SELECT customer_first_name, customer_zipcode
FROM   customers 
WHERE  customer_zipcode = p_zipcode;
END;
/

Answer 2

你不能只是执行这样的过程，因为它需要两个参数;一个是IN，另一个是OUT(包含结果集的ref游标)。

我没有您的表格，所以我将使用Scott的示例模式来演示它，通过传递部门编号并返回在该部门工作的员工列表。

程序:

SQL> set serveroutput on
SQL> create or replace procedure p_list
2    (p_deptno    in dept.deptno%type,
3     p_disp     out sys_refcursor
4    )
5  is
6  begin
7    open p_disp for select ename, job from emp
8                    where deptno = p_deptno;
9  end;
10  /
Procedure created.

用法如下:

SQL> declare
2    l_list   sys_refcursor;
3    l_ename  emp.ename%type;
4    l_job    emp.job%type;
5  begin
6    p_list(10, l_list);       --> calling the procedure; use 2 parameters
7
8    loop
9      fetch l_list into l_ename, l_job;
10      exit when l_list%notfound;
11      dbms_output.put_line(l_ename ||' - '|| l_job);
12    end loop;
13  end;
14  /
CLARK - MANAGER
KING - PRESIDENT
MILLER - CLERK
PL/SQL procedure successfully completed.
SQL>

Answer 3

TRY EXEC LIST_CUSTOMER_ZIPCODE(:p_Zipcode);

如果你在任何字符串前加上':'，它将成为替换字符串，你可以输入你的输入。