SYSDATE; -- current date and time BEGIN -- this section is mandatory, must contain at least one executable statement SELECT street_number INTO number1 FROM address WHERE name = 'INU'; EXCEPTION -- this section is optional WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error Code is ' || TO_CHAR(sqlcode)); DBMS_OUTPUT.PUT_LINE('Error Message is ' || sqlerrm); END; The symbol := functions as an assignment operator to store a value in a variable. Blocks can be nested – i.e., because a block is an executable statement, it can appear in another block wherever an executable statement is allowed. A block can be submitted to an interactive tool (such as SQL*Plus) or embedded within an Oracle Precompiler or OCI program. The interactive tool or program runs the block once. The block is not stored in the database, and for that reason, it is called an anonymous block (even if it has a label). Function The purpose of a PL/SQL function is generally used to compute and return a single value. This returned value may be a single scalar value (such as a number, date or character string) or a single collection (such as a nested table or array). User-defined functions supplement the built-in functions provided by Oracle Corporation. The PL/SQL function has the form: CREATE OR REPLACE FUNCTION <function_name> [(input/output variable declarations)] RETURN return_type [AUTHID <CURRENT_USER | DEFINER>] <IS|AS> -- heading part amount number; -- declaration block BEGIN -- executable part <PL/SQL block with return statement> RETURN <return_value>; [Exception none] RETURN <return_value>; END; Pipe-lined table functions return collections and take the form: CREATE OR REPLACE FUNCTION <function_name> [(input/output variable declarations)] RETURN return_type [AUTHID <CURRENT_USER | DEFINER>] [<AGGREGATE | PIPELINED>] <IS|USING> [declaration block] BEGIN <PL/SQL block with return statement> PIPE ROW <return type>; RETURN; [Exception exception block] PIPE ROW <return type>; RETURN; END; A function should only use the default IN type of parameter. The only out value from the function should be the value it returns. Procedure Procedures resemble functions in that they are named program units that can be invoked repeatedly. The primary difference is that functions can be used in a SQL statement whereas procedures cannot. Another difference is that the procedure can return multiple values whereas a function should only return a single value. The procedure begins with a mandatory heading part to hold the procedure name and optionally the procedure parameter list. Next come the declarative, executable and exception-handling parts, as in the PL/SQL