Stored procedures

A stored procedure is a subprogram that does not directly return a value. If the parameter type is OUT, the stored procedure can return a value to the caller.

Structure of a stored procedure

The structure of a stored procedure in PL/SQL is as follows:

PROCEDURE sp_name ([proc_parameter[,...]])[characteristic ...]
    BEGIN    -- Start of execution
      SQL statement; [ SQL statement; ]...
    END;     -- End of execution

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

Creating stored procedures

You can create a stored procedure by using the CREATE PROCEDURE statement. The following rules apply:

• The DECLARE module must be placed in the BEGIN ... END; module. You can define other statements only after all declarations are completed.

• You can define DETERMINISTIC / LANGUAGE SQL and other sp_create_chistic information to enhance the usage of stored procedures.

• Stored procedures cannot be overloaded.

Example 1: Create a stored procedure without IN and OUT parameters.

DELIMITER //

CREATE PROCEDURE proc_name()
   BEGIN
     DECLARE var_name VARCHAR(20) DEFAULT 'ZhangSan';  
     SET var_name = 'LiSi';
     SELECT var_name;
   END //
Query OK, 0 rows affected

DELIMITER ;

CALL proc_name();

The return result is as follows:

+----------+
| var_name | 
+----------+
| LiSi     |
+----------+
1 row in set 

Example 2: Create a stored procedure with IN and OUT parameters.

//Create an example table emp.
CREATE TABLE emp(
   empno          NUMBER(4,0),
   empname        VARCHAR(10),
   job            VARCHAR(10),
   deptno         NUMBER(2,0),
   salary         NUMERIC
);
Query OK, 0 rows affected

INSERT INTO emp VALUES (200,'Jennifer','AD_ASST',1,15000),(202,'Pat','MK_REP',2,12000),
          (119,'Karen','PU_CLERK', 4,10000),(118,'Guy','PU_CLERK', 4,10000),
         (201,'Michael','MK_MAN',3,9000);
Query OK, 5 rows affected
Records: 5  Duplicates: 0  Warnings: 0

DELIMITER //

CREATE PROCEDURE my_proc(IN emp_no INT,OUT emp_count INT)
   BEGIN
     SELECT COUNT(*) INTO emp_count FROM emp WHERE empno=emp_no;
   END //
Query OK, 0 rows affected

DELIMITER ;

//Initialize parameters.
SET @emp_no='200',@emp_count=0;
Query OK, 0 rows affected

//Call the stored procedure my_proc.
CALL my_proc(@emp_no,@emp_count);

The return result is as follows:

+-----------+
| emp_count |
+-----------+
|         1 |
+-----------+
1 row in set

Continue to view the result after emp_count is assigned a value:

SELECT @emp_count;

The return result is as follows:

+------------+
| @emp_count |
+------------+
| 1          |
+------------+
1 row in set

Calling stored procedures

You can call a stored procedure by using the CALL statement. However, you cannot call a stored procedure as part of an SQL expression.

//Call the stored procedure my_proc.
CALL my_proc(@emp_no,@emp_count);
Query OK, 0 rows affected

//View the result after emp_count is assigned a value.

SELECT @emp_count;

The return result is as follows:

+------------+
| @emp_count |
+------------+
| 1          |
+------------+
1 row in set