Procedures

PL/SQL procedures behave very much like procedures in other programming languages. Here is an example of a PL/SQL procedure addtuple1 that, given an integer i, inserts the tuple (i, 'xxx') into the following example relation: 

SQL> CREATE TABLE  T2 (
  2    a  INTEGER,
  3    b  CHAR(10) );
Table created.

SQL> CREATE PROCEDURE  addtuple1( i IN NUMBER ) AS
  2  BEGIN
  3    INSERT INTO  T2  VALUES( i, 'xxx' );
  4  END  addtuple1;
  5  /
Procedure created.

SQL> BEGIN  addtuple1(99);  END;
  2  /
PL/SQL procedure successfully completed.


A procedure is introduced by the keywords CREATE PROCEDURE followed by the procedure name and its parameters. An option is to follow CREATE by OR REPLACE. The advantage of doing so is that should you have already made the definition, you will not get an error. On the other hand, should the previous definition be a different procedure of the same name, you will not be warned, and the old procedure will be lost. There can be any number of parameters, each followed by a mode and a type. The possible modes are IN (read-only), OUT (write-only), and INOUT (read and write).
Note: Unlike the type specifier in a PL/SQL variable declaration, the type specifier in a parameter declaration must be unconstrained. For example, CHAR(10) and VARCHAR(20) are illegal; CHAR or VARCHAR should be used instead. The actual length of a parameter depends on the corresponding argument that is passed in when the procedure is invoked.



      Young Son: “Is it true, Dad, that in some parts of Africa    
      a man doesn’t know his wife until he marries her?”    
      Dad: “That happens in every country, son.”