Procedures
A procedure is a group of PL/SQL statements that you can call by name. A call specification (sometimes called call spec) declares a Java method or a third-generation language (3GL) routine so that it can be called from SQL and PL/SQL. The call spec tells Oracle which Java method to invoke when a call is made. It also tells the database what type conversions to make for the arguments and return value.
To create a new procedure:
- select the Database | New Object... main menu item;
- select Procedure in the Create New Object dialog;
- define procedure properties using the appropriate tabs of Procedure Editor.
Hint: To create a new procedure, you can also right-click the Procedures node or any object within this node in the DB Explorer tree and select the New Procedure... item from the context menu.
To create a new procedure with the same properties as one of existing procedures has:
- select the Database | Duplicate Object... main menu item;
- follow the instructions of Duplicate Object Wizard.
Alternatively, you can right-click a procedure in the DB Explorer tree and select the Duplicate Procedure <procedure_name>... context menu item.
Duplicate Object Wizard allows you to select the database to create a new procedure in, and to edit the result SQL statement for creating the procedure.
To edit an existing procedure:
- select the procedure for editing in the DB Explorer tree (type the first letters of the procedure name for quick search);
- right-click the object and select the Edit Procedure <procedure_name> context menu item, or simply double-click the procedure;
- edit procedure definition using the appropriate tabs of Procedure Editor.
To execute a procedure:
- select the procedure to execute in the DB Explorer tree (type the first letters of the procedure name for quick search);
- right-click the object and select the Edit Procedure <procedure_name>... context menu item, or simply double-click the procedure;
- execute the procedure using the Execute Procedure Navigation bar item of Procedure Editor.
To drop a procedure:
- select the procedure to drop in the DB Explorer tree;
- right-click the object and select the Drop Procedure <procedure_name>... context menu item;
- confirm dropping in the dialog window.
Note: If more convenient, you can also use the following shortcuts:
Ctrl+N to create a new procedure;
Ctrl+O to edit the selected procedure;
F9 to execute the procedure;
Shift+Del to drop the object from the database.