我正在尝试创建一个存储过程来查找表中的客户,如果找到某些内容则返回1,如果没有返回0。我有使用异常处理0部分的想法,但我不知道如何返回我想要的数字。
CREATE OR REPLACE PROCEDURE FIND_CUSTOMER
(
CUSTOMER_ID IN NUMBER
, FOUND OUT NUMBER
) AS
BEGIN
SELECT CUST_NO
FROM CUSTOMERS
WHERE CUST_NO = customer_id;
EXCEPTION
WHEN no_data_found
THEN found := 0;
END FIND_CUSTOMER;
我在哪里把返回语句使其正常工作?提前谢谢。
刚错过INTOSELECT语句。您可以像这样重新创建过程
CREATE OR REPLACE PROCEDURE find_customer(
customer_id customer.cust_no%TYPE,
found OUT INT
) AS
BEGIN
SELECT SIGN(cust_no)
INTO found
FROM customer
WHERE cust_no = customer_id;
EXCEPTION
WHEN no_data_found THEN
found := 0;
END;
/
或者更喜欢通过使用简单的隐式游标来创建像下面这样的游标,而不需要INTO子句和异常处理,甚至只是用于out参数(如
)的初始化值(零)CREATE OR REPLACE PROCEDURE find_customer(
customer_id customer.cust_no%TYPE,
found OUT INT
) AS
BEGIN
found := 0;
FOR c IN
(
SELECT SIGN(cust_no) AS fnd
FROM customer
WHERE cust_no = customer_id
)
LOOP
found := c.fnd;
END LOOP;
END;
/
我使用SIGN()
函数来返回值(1))假定cust_no
的所有值在逻辑上都是正整数。