警告：在 oracle 中创建的过程有编译错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21821030/
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
Warning: Procedure created with compilation errors in oracle
提问by Sesuraj
CREATE TABLE:
创建表：
create table customer (Name varchar2(10), Address varchar(40), Contact number);
CREATE PROCEDURE FOR INSERT:
创建插入程序：
CREATE OR REPLACE PROCEDURE SP_CUSTOMER (
p_name customer.Name%TYPE,
p_address customer.Address%TYPE,
p_contact customer.Contact%TYPE)
IS
BEGIN
INSERT INTO customer ("Name", "Address", "Contact")
VALUES (p_name, p_address, p_contact);
COMMIT;
END;
/
ERROR: IT SHOWS: Warning: Procedure created with compilation errors.
错误：它显示：警告：创建的过程有编译错误。
CREATE PROCEDURE FOR SELECT:
CREATE OR REPLACE PROCEDURE SP_SELECT_CUSTOMER (
p_name customer.Name%TYPE,
p_address customer.Address%TYPE,
p_contact customer.Contact%TYPE)
IS
BEGIN
SELECT Name, Address, Contact FROM customer;
END;
/
ERROR: IT SHOWS: Warning: Procedure created with compilation errors.
错误：它显示：警告：创建的过程有编译错误。
What is the problem.? How to solve it.?
问题是什么。？怎么解决。？
回答by Jeffrey Kemp
Whenever you compile something in SQL*Plus and you get "compilation errors", the first thing you want to do is to
SHOW ERRORS, which will tell you what actually went wrong.Your first procedure has a problem where the column names will not match the column names from your table. Your table definition has
Name,AddressandContact, but because you haven't used double-quotes, the column names will actually beNAME,ADDRESSandCONTACT- but your insert statement uses double-quotes, so it tries to insert intoNameetc. Just get rid of the double-quotes, you don't need them here.In your second procedure, you have a SELECT statement in PL/SQL, but you don't specify where to put the resulting data. You at least need an
INTOclause, or perhaps do a LOOP over the resulting records.
每当您在 SQL*Plus 中编译某些内容并遇到“编译错误”时，您要做的第一件事就是
SHOW ERRORS，它会告诉您实际出错的地方。您的第一个过程有一个问题，即列名与表中的列名不匹配。你的表定义有
Name,AddressandContact，但因为你没有使用双引号，列名实际上是NAME,ADDRESSandCONTACT- 但你的插入语句使用双引号，所以它试图插入Name等。只需摆脱双引号-quotes，这里不需要它们。在您的第二个过程中，您在 PL/SQL 中有一个 SELECT 语句，但您没有指定将结果数据放在哪里。您至少需要一个
INTO子句，或者可能对结果记录执行 LOOP。
回答by Gaurav Soni
--you need not to put the column in quotes
--您不需要将列放在引号中
CREATE OR REPLACE PROCEDURE SP_CUSTOMER (
p_name customer.Name%TYPE,
p_address customer.Address%TYPE,
p_contact customer.Contact%TYPE)
IS
BEGIN
INSERT INTO customer (Name, Address, Contact)
VALUES (p_name, p_address, p_contact);
COMMIT;
END;
/
--just use a refcursor to return the resultset
-- 只需使用 refcursor 返回结果集
CREATE OR REPLACE PROCEDURE SP_SELECT_CUSTOMER (
p_cust_details OUT SYS_REFCURSOR)
IS
BEGIN
OPEN p_cust_details for SELECT Name, Address, Contact FROM customer;
END;
/
EDITif you want to find details based on name ,then pass an IN parameter and use it as filter condition.
如果要根据名称查找详细信息，请编辑，然后传递 IN 参数并将其用作过滤条件。
CREATE OR REPLACE PROCEDURE SP_SELECT_CUSTOMER (
p_cust_name IN customer.Name%TYPE
p_cust_details OUT SYS_REFCURSOR)
IS
BEGIN
OPEN p_cust_details for SELECT Name, Address, Contact FROM customer
WHERE name=p_cust_name ;
END;
/
回答by Yogeshwaran
select * from user_errors where name='SP_SELECT_CUSTOMER'
