Forum Stats

  • 3,781,578 Users
  • 2,254,530 Discussions
  • 7,879,760 Comments

Discussions

procedure declarartion in PL/SQl

2659647
2659647 Member Posts: 1
edited Apr 23, 2014 7:47AM in SQL & PL/SQL

Hi

Could anyone tell if below syntax to create procedure correct:

create procedure test_proc() as begin end; its not working but as i understand the concept of procedure is: its used to perform certain action. SO why its necessary to give parameter in declaration as

create procedure test_proc(var1 number) as begin end; works but without paramter it doesnt work.

Tagged:

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,615 Red Diamond

    You don't have to give a parameter, you just leave out the brackets. (and ensure you include a statement in the actual procedure rather than just begin end;

  • Moazzam
    Moazzam Member Posts: 1,356
    edited Apr 23, 2014 7:42AM

    As bluShadow said, parameters are not mandatory. However, empty brackets are not allowed. Also there must be at least one statement inside procedure because procedure is supposed to do something e.g.

    CREATE PROCEDURE test_proc AS
    BEGIN
    NULL;
    END;
    /
    
    
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,009 Red Diamond
    f3569039-eb54-4333-aeeb-ab40925c1ef0 wrote:
    
    SO why its necessary to give parameter in declaration as
    create procedure test_proc(var1 number) as begin end; works but without paramter it doesnt work.
     

    As in most cases - RTFM. Below is CREATE PROCEDURE syntax diagram:

    CREATE [ OR REPLACE ] PROCEDURE [ schema. ] procedure_name

    [ ( parameter_declaration [, parameter_declaration ]... ) ]

      [ invoker_rights_clause ]

      { IS | AS }

      { [ declare_section ] body | call_spec | EXTERNAL} ;

    As you can see, you either specify comma separated parameter list enclosed in parenthesis or do not specify it at all when procedure has no parameters.

    SY.

This discussion has been closed.