CREATE PROCEDURE Examples
1. This database procedure, mark_emp, accepts an employee ID number and a label string as input. The employee matching that ID is labeled and an indication is returned.
CREATE PROCEDURE mark_emp
(id INTEGER NOT NULL, label VARCHAR(100)) AS
BEGIN
UPDATE employee
SET COMMENT = :label
WHERE id = :id;
IF iirowcount = 1 THEN
MESSAGE 'Employee was marked';
COMMIT;
RETURN 1;
ELSE
MESSAGE'Employee was not marked - record error';
ROLLBACK;
RETURN 0;
ENDIF;
END;
2. In this example, the database procedure, add_n_rows, accepts as input a label, a base number, and a number of rows. It inserts the specified number of rows into the table blocks, starting from the base number. If an error occurs, the procedure terminates and the current row number is returned.
CREATE PROCEDURE add_n_rows
(base INTEGER NOT NULL, n INTEGER,
label VARCHAR(100)) AS
DECLARE
limit INTEGER;
err INTEGER;
BEGIN
limit = base + n;
err = 0;
WHILE (base < limit) AND (err = 0) DO
INSERT INTO blocks VALUES (:label, :base);
IF iierrornumber > 0 THEN
err = 1;
ELSE
base = base + 1;
ENDIF;
ENDWHILE;
RETURN :base;
END;
3. The following example illustrates the use of global temporary tables as procedure parameters. The database procedure, gttproc, accepts as input a surrogate table name; gtt1 is defined as a SET OF parameter to the gttproc procedure and is used in the FROM clause of a SELECT statement in the body of the procedure.
CREATE PROCEDURE gttproc
(gtt1 SET OF (col1 INT, col2 FLOAT NOT NULL, col3 CHAR(8))) AS
BEGIN
...
INSERT INTO table1
SELECT * FROM gtt1;
...
END;
4. The following example illustrates the use of parameter modes to effect the return of the customer name and zipcode column values for a given customer number to the calling procedure.
CREATE PROCEDURE getnamezip (IN custno INT NOT NULL, OUT custname, OUT custzip) AS
BEGIN
...
SELECT c_name, c_zip INTO :custname, :custzip FROM customer WHERE c_id = :custno;
...
END;
5. The following database procedure, avgsal_by_dept, returns rows containing the department name, average salary in the department, and count of employees in the department. The columns of the result row are assigned the names dept_name, avg_sal and emp_count so that the procedure can also be referenced as a table procedure in a SELECT statement. Any unexpected error from the SELECT statement in the procedure terminates the loop:
CREATE PROCEDURE avgsal_by_dept
RESULT ROW avgsal(dept_name CHAR(15), avg_sal FLOAT, emp_count INT) AS
DECLARE
deptname CHAR(15);
avgsal FLOAT;
empcount INT;
err INT;
BEGIN
err = 0;
FOR SELECT d.dept, avg(e.salary), count(*) INTO :deptname, :avgsal, :empcount
FROM department d, employee e
WHERE e.deptid = d.deptid
GROUP BY d.deptid DO
IF iierrornumber > 0 THEN
err = 1;
ENDLOOP;
ENDIF;
RETURN ROW(:deptname, :avgsal, :empcount);
ENDFOR;
RETURN :err;
END;
Last modified date: 04/26/2024