Create simple procedure in PL/SQL Oracle

Subprograms

Subprograms are named PL/SQL blocks that can be called with a set of parameters.
There are two types of blocks in PL/SQL:
•    Procedures
•    Functions

Structure Of Oracle Procedure(Anonymous block)

DECLARE         (optional)
        /* Variable Block                     */
    BEGIN             (mandatory)
        /* Executable Statements / Queries     */
    EXCEPTION    (optional)
        /* Exception Action                 */
END;                (mandatory)
/

Syntax of Oracle Procedure

CREATE [OR REPLACE] PROCEDURE procedure_name
 [(parameter1 [mode1] datatype1,
  parameter2 [mode2] datatype2,
  . . .)]
IS|AS
PL/SQL Block;

Structure Of Oracle Procedure(Named block)

CREATE [OR REPLACE] PROCEDURE procedure_name          (mandatory)
        /* Variable Block                     */
    BEGIN             (mandatory)
        /* Executable Statements / Queries     */
    EXCEPTION    (optional)
        /* Exception Action                 */
END;                (mandatory)
/

Modes:

•    IN: procedure must be called with a value for the parameter. Value cannot be changed
•    OUT: procedure must be called with a variable for the parameter. Changes to the parameter are seen by the user (i.e., call by reference)
•    IN OUT: value can be sent, and changes to the parameter are seen by the user

Default Mode is:

IN

Consider Table

Table Name: Example

NAME             VARCHAR2(10)
NUM              NUMBER(3)

                                                                                                                                                                               

Table Data

Select * from example;
NAME       NUM                    
---------- ----------------------
NAMEONE    1                      
NAMETWO    2                      
NAMETHREE  3                      
NAMEFOUR   4                      
NAMEFIVE   5                      
NAMESIX    6                      
NAMESEVEN  7                      
test -100  100                    
test -200  200                    
test -300  300                    
test -500  500        

11 rows selected

Simple Procedure For Get Name From Example Table

Create or replace procedure p_getname
(v_num IN example.num%TYPE,
v_name OUT example.name%TYPE)
/*    v_num     - Input Parameter            */
/*    v_name     - Output Parameter            */
IS
BEGIN
    select name
    into v_name
    from example
    where v_num = num;
END;
/

Calling the Procedure

set serveroutput on;
declare
    getname  example.name%TYPE;
begin
    p_getname(1,getname);
    dbms_output.put_line('-----------');
    dbms_output.put_line(getname);
end;
/

Sample Output:

anonymous block completed
-----------
NAMEONE