Overview
Approximate structure of ALTIBASE Stored Procedure is almost same with the PL/SQL of ORACLE.
It consists of parameter declaration, local variables declaration, execution section and exception handlers.
The description above is same with ALTIBASE HDB and ORACLE.
Notices
- LOB type value is unavailable as a parameter value
- To execute an creation of procedure, "/" is required after "END;" on iSQL.
Example
ORACLE | ALTIBASE HDB | Comments |
|---|
CREATE OR REPLACE PROCEDURE raise_emp_salary (column_value VARCHAR2,
emp_column VARCHAR2, amount NUMBER) IS
v_column VARCHAR2(30);
sql_stmt VARCHAR2(200);
BEGIN
-- determine if a valid column name has been given as input
SELECT COLUMN_NAME INTO v_column FROM USER_TAB_COLS
WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = emp_column;
DBMS_OUTPUT.put_line('COLUMN_NAME: ' || V_COLUMN);
sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE '
|| v_column || ' = :2';
EXECUTE IMMEDIATE sql_stmt USING amount, column_value;
IF SQL%ROWCOUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE('Salaries have been updated for: ' || emp_column || ' = ' || column_value);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('Invalid Column: ' || emp_column);
END raise_emp_salary;
/ | CREATE OR REPLACE PROCEDURE raise_emp_salary
(column_value VARCHAR2(40),
emp_column VARCHAR2(40), amount NUMBER) IS
v_column VARCHAR2(40);
sql_stmt VARCHAR2(200);
BEGIN
-- determine if a valid column name has been given as input
SELECT B.COLUMN_NAME INTO v_column
FROM SYSTEM_.SYS_TABLES_ A, SYSTEM_.SYS_COLUMNS_ B
WHERE A.USER_ID = B.USER_ID AND A.TABLE_ID = B.TABLE_ID
AND A.TABLE_NAME = 'EMPLOYEES' AND B.COLUMN_NAME = emp_column;
SYSTEM_.PRINTLN('COLUMN_NAME: ' || V_COLUMN);
sql_stmt := 'UPDATE employees SET salary = salary + :column_value WHERE ' || v_column || ' = :emp_column';
EXECUTE IMMEDIATE sql_stmt USING amount, column_value;
IF SQL%ROWCOUNT > 0 THEN
SYSTEM_.PRINTLN('Salaries have been updated for: ' || emp_column || ' = ' || column_value);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
SYSTEM_.PRINTLN ('Invalid Column: ' || emp_column);
END raise_emp_salary;
/ | As parameters of procedure in ORACLE, The length of char data type should be omitted.
The transformation of DBMS_OUTPUT.put_line statement is equal to the left.
PLACE HOLDER(e.g> :1, :2, :3) is not supported in ALTIBASE HDB.
So it should be modified to the corresponding variable_name with a colon mark.(e.g> :column_value, :emp_column) |