如何在oracle中创建存储过程 [英] how to create a stored procedure in oracle

问题描述

我只需要创建一个SP即可返回多个表的计数.所以我只定义了这样的过程，

-像这样创建了我的包裹

Hi,

i just need to create an SP which returns the count fo multiple tables.so i just defined the procedure like this,

--created my package like this

CREATE OR REPLACE PACKAGE COUNTTABLE AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE SAMPLECOUNT
(
    cur_COUNT OUT T_CURSOR
);
END COUNTTABLE;

--defined the procedure like this

CREATE OR REPLACE PACKAGE BODY COUNTTABLE AS
PROCEDURE SAMPLECOUNT
(
    cur_COUNT OUT T_CURSOR
)
IS
BEGIN
    OPEN cur_COUNT FOR
    SELECT  (
        SELECT COUNT(*)
        FROM   PROJECTS
        ) AS count1,
        (
        SELECT COUNT(*)
        FROM   PROJECTS1
        ) AS count2
FROM    dual
END SAMPLECOUNT;
END COUNTTABLE;

当我执行时，它抛出一个错误，说PL/SQL找不到名为"MyDBA.Counttable"的程序单元.MyDBA是我的数据库，而counttable是包.但是在SP中，如果我指定采用单个计数表而不是多个表，它的工作正常.我是这个Oracle的新手，所以任何人都可以帮助我......?

while i am executing, its throwing me an error saying PL/SQL cannot find the program unit being called "MyDBA.Counttable. MyDBA is my database and counttable is the package. but in the SP if i specify to take the count of a single table instead of multiple tables, its working fine. i am new to this Oracle.so can anyone please help me in doing this.........?

推荐答案

似乎您的呼叫错误或没有权限执行该过程.

如果要从程序包外部执行该过程，则调用应类似于:

Seems that either your call is wrong or you don''t have the priviliges to execute the procedure.

If you''re executring the procedure from outside the package, the call should be something like:

execute counttable.samplecount(the parameter for the cursor);

如果您没有该包的公共同义词，则可能还需要定义所有者架构.

You may also need to define the owner schema if you don''t have a public synonym for the package.

这篇关于如何在oracle中创建存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！