Oracle PL/SQL Tutorial - PL/SQL Procedures

• « Previous
• Next »

PL/SQL procedures don't return a value.

They perform their instructions and return.

You can't use procedures on the right-hand side of an assignment statement like a function.

Create a Procedure

The following code is a very simple example of a procedure.

It's based on SYS.DBMS_LOCK package's procedure sleep(seconds in number).

This procedure will stop executing for the number of seconds specified.

CREATE OR REPLACE PROCEDURE wait( ain_seconds IN number) is 
begin --  w ww .j  a  va2  s .co m
    SYS.DBMS_LOCK.sleep(ain_seconds); 
end wait; 
/ 

declare 
    v_max_line varchar2(32767); 
begin 

    wait(2);
end; 
/ 

The code above generates the following result.

Note

The syntax for creating a procedure is as follows:

CREATE [OR REPLACE] PROCEDURE <procedure_name> [( 
<parameter_name_1> [IN] [OUT] <parameter_data_type_1>, 
<parameter_name_2> [IN] [OUT] <parameter_data_type_2>,... 
<parameter_name_N> [IN] [OUT] <parameter_data_type_N> )] IS
     --the declaration section --  w ww .j  a  va2s  .c  o  m
BEGIN
     -- the executable section 
EXCEPTION
     -- the exception-handling section 
END; 
/ 

<procedure_name> is the name of the PROCEDURE; <parameter_name> is the name of a parameter being passed IN, OUT, or IN and OUT <parameter_data_type> is the PL/SQL data type of the corresponding parameter.

Note 2

The brackets around the keywords OR REPLACE denote that they are optional.

The brackets around the parameters denote that they are optional.

The block structure of a PROCEDURE is the same as an anonymous block, except for the CREATE PROCEDURE keywords and the optional parameters.

A procedure differs from a function since it does not have a RETURN parameter.

Example 2

The following code creates a warpper for SYS.DBMS_OUTPUT.put_line().

create or replace PROCEDURE pl( aiv_text in varchar2 ) is 
begin --  www  . jav a2 s  . c o m
    SYS.DBMS_OUTPUT.put_line(aiv_text); 
end pl; 
/ 

declare 
    v_max_line varchar2(32767); 
begin 
    for i in 1..10 loop 
        v_max_line := v_max_line || ' a'; 
    end loop; 
    pl('Test a line of text.'); 
    pl(to_date('20200101', 'YYYYMMDD')); 

end; 
/ 

The code above generates the following result.

• Next »
• « Previous