当前位置：文江博客话题详情

入门 Oracle 存储过程的良好初学者资源

发布于 2024-07-24 07:13:49 字数 1281 浏览 7 评论 0 原文

我正在寻找适合初学者的有关 Oracle 存储过程的优质资源。我尝试了 Dev Shed 文章和来自 Oracle 文档站点，但它们不能满足我的需求。单一形式的 Oracle 文档站点具有 Java 示例的开销。我尝试了 Dev Shed，但是当我尝试他们的示例时，我不断收到无效的 SQL 错误。这是我尝试运行的基本错误：

CREATE OR REPLACE PROCEDURE skeleton
IS
BEGIN
 NULL;
END;

EXECUTE skeleton;

我是否犯了菜鸟错误，或者文章中的语法是否已过时？我正在使用 Oracle 10g & PL/SQL 开发人员。

谢谢！

解决方案：

根据@curtisk的建议，我尝试了一个稍微复杂一些的示例：

CREATE OR REPLACE PROCEDURE p_getdate
IS
BEGIN
 dbms_output.put_line(TO_CHAR
    (SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
END;

当我尝试像这样执行它时：

EXECUTE p_getdate

我收到ORA-00900：无效的SQL语句错误。我寻找为什么会出现这种情况& 偶然发现此线程。我决定尝试一下，所以我尝试了以下方法：

CALL p_getdate;

但这也不起作用。我又读了一些帖子& 怀疑地尝试过：

CALL p_getdate();

瞧，我正确地调用了该过程。我不知道为什么它有效，但它有效的事实让我能够继续我的项目。如果有人可以向我解释这是为什么，我很想了解更多信息。

再次感谢@curtisk 的帮助！

原文

I am looking for good resources on Oracle Stored Procedure geared towards beginners. I tried the Dev Shed Article and one from Oracle Documentation site but they did not meet my needs. The one form Oracle Documentation site had the overhead of Java example. I tried the Dev Shed one but I keep getting invalid SQL error when I try their example. Here is the basic one that I tried to run:

CREATE OR REPLACE PROCEDURE skeleton
IS
BEGIN
 NULL;
END;

EXECUTE skeleton;

Am I making a rookie mistake or is the syntax in the article out of date? I am working with Oracle 10g & PL/SQL Developer.

Thanks!

SOLUTION:

As per @curtisk's suggestion, I tried a slightly more involved example:

CREATE OR REPLACE PROCEDURE p_getdate
IS
BEGIN
 dbms_output.put_line(TO_CHAR
    (SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
END;

When I tried to execute it like so:

EXECUTE p_getdate

I got ORA-00900: Invalid SQL statement error. I looked for why this would be the case & stumbled across this thread. I decided to give call a try so I tried the following:

CALL p_getdate;

but that did not work as well. I read the thread some more & skeptically tried:

CALL p_getdate();

and voila, I had the procedure being called properly. I do not have an answer as to why this works but the fact that it works allows me to move forward with my project. If there is anyone who can explain to me why this is, I would love to learn more on this.

Thanks once again to @curtisk for the help!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

淡写薰衣草的香 2024-07-31 07:13:49

我不会使用该示例作为起点...我会让它做某事，这里有一个尝试

CREATE OR REPLACE PROCEDURE p_getdate
IS
BEGIN
 dbms_output.put_line(TO_CHAR
    (SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
END;

运行它，它应该成功编译，然后在提示符下...

SQL> SET SERVEROUT ON;
SQL> execute p_getdate;
 06-11-2009 12:29:54

您应该查看输出中的当前日期/时间。恕我直言，您使用的示例太简单了。下一步是创建一个处理声明和参数的过程。

就初学者资源而言，有大量并不是特别推荐任何一个，如果没有的话，总是有“问汤姆”..应该有一些那里的问答可能会有所帮助

I wouldn't use that example as a starting point...I'd make it do something here's one to try

CREATE OR REPLACE PROCEDURE p_getdate
IS
BEGIN
 dbms_output.put_line(TO_CHAR
    (SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
END;

Run that, it should compile successfully, then at the prompt....

SQL> SET SERVEROUT ON;
SQL> execute p_getdate;
 06-11-2009 12:29:54

You should see the current date/time in the output. The example you used was too simple IMHO. Next step is to create a procedure that deals with declarations and parameters.

As far as beginner resources, there's a ton out there can't really recommend any one in particular, and if nothing there's always "Ask Tom"..should be some Q/A there that may help