创建 Oracle 存储过程并从 .sql 文件执行它
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17973109/
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
Creating an Oracle Stored Procedure and Executing it from .sql file
提问by javakid1993
I have two .sql files both are Oracle stored procedures which both take in input parameters. I would like to first connect to a remote oracle database using sqlplus in command line and want to first use both files to create their respective stored procedures so I see them under procedures for that connection in Oracle SQL Developer.
我有两个 .sql 文件都是 Oracle 存储过程，它们都接受输入参数。我想首先在命令行中使用 sqlplus 连接到远程 oracle 数据库，并希望首先使用这两个文件来创建它们各自的存储过程，因此我在 Oracle SQL Developer 中该连接的过程下看到它们。
After this I have two more .sql files which look like this and are designed to take input parameters and execute the stored procedures. This is one of the files that is meant to execute the stored procedure "REPORT".
在此之后，我还有两个 .sql 文件，它们看起来像这样，旨在获取输入参数并执行存储过程。这是用于执行存储过程“REPORT”的文件之一。
DECLARE
NAME VARCHAR2(200);
VERSION VARCHAR2(200);
STARTDATE DATE;
ENDDATE DATE;
BEGIN
NAME := '&1';
VERSION := '&2';
STARTDATE := '&3';
ENDDATE := '&4';
exec REPORT(NAME, VERSION, STARTDATE, ENDDATE);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20101,SQLERRM);
END;
/
In command prompt I first try to create the stored procedure in the database by: C:\Users\Desktop>sqlplus username/password @report_setup.sql
在命令提示符中，我首先尝试通过以下方式在数据库中创建存储过程：C:\Users\Desktop>sqlplus username/password @report_setup.sql
When I try this the output get is just empty lines that are numbered and beginning at the number that is 1 greater then the last line of my .sql file. My report_setup.sql file is 81 lines long and the output of the sqlplus command is blank numbered lines beginning at 83.
当我尝试这个时，输出只是空行，这些行被编号并从比我的 .sql 文件的最后一行大 1 的数字开始。我的 report_setup.sql 文件有 81 行，sqlplus 命令的输出是从 83 开始的空白行。
Please let me know how I can create and execute these stored procedures properly through sqlplus.
请让我知道如何通过 sqlplus 正确创建和执行这些存储过程。
Thanks in advance,
提前致谢，
回答by Njal
I think you have to remove the 'exec'-word, and it's crucial to have the slash at the bottom at the very start of the line, with no spaces in front of it:
我认为您必须删除“exec”字，并且在行首的底部使用斜杠至关重要，并且前面没有空格：
DECLARE
NAME VARCHAR2(200);
VERSION VARCHAR2(200);
STARTDATE DATE;
ENDDATE DATE;
BEGIN
NAME := '&1';
VERSION := '&2';
STARTDATE := '&3';
ENDDATE := '&4';
REPORT(NAME, VERSION, STARTDATE, ENDDATE);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20101,SQLERRM);
END;
/
回答by Alex Poole
It would have been more useful to show the report_setup.sqlthan the script that calls the procedure it creates... but from the symptoms you describe, the report_setup.sqldoesn't have a /at the end of the procedure declaration.
显示report_setup.sql比调用它创建的过程的脚本更有用......但是从你描述的症状来看，在过程声明的末尾report_setup.sql没有 a/。
It presumably has something like:
它大概有这样的东西：
CREATE OR REPLACE PROCEDURE REPORT(NAME VARCHAR2, VERSION VARCHAR2,
STARTDATE DATE, ENDDATE DATE) AS
...
BEGIN
...
END REPORT;
It needs to have
它需要有
...
BEGIN
...
END REPORT;
/
Since you're running it from the command line with @it should also have an EXITat the end; but without the /that will be treated as part of the procedure, which is never compiled.
由于您是从命令行运行它，@它EXIT的末尾也应该有一个；但如果没有/，它将被视为程序的一部分，它永远不会被编译。
You can suppress the line number display, incidentally, by calling SQL*Plus with the -sflag - though at the moment they are useful since they show roughly what the problem is.
顺便说一句，您可以通过调用带有-s标志的SQL*Plus 来抑制行号显示- 尽管目前它们很有用，因为它们大致显示了问题所在。
回答by user10951635
I had a similar issue. The problem was the encoding used, sqlplusexpects UTF-8standard enconding, wherever different encoding cause weird behavior.
我有一个类似的问题。问题是使用的编码，sqlplus期望UTF-8标准编码，无论不同的编码导致奇怪的行为。
