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)
