Page History

CREATE OR REPLACE PROCEDURE PROC1(V_EMPNO INTEGER)
 AS
 BEGIN
 EXECUTE IMMEDIATE
 'DELETE FROM EMPLOYEES WHERE ENO = :1'
 USING V_EMPNO;
 END;
 /

CREATE OR REPLACE PROCEDURE PROC1(V_EMPNO INTEGER)
AS
 BEGIN
 EXECUTE IMMEDIATE
 'DELETE FROM EMPLOYEES WHERE ENO = ?'
 USING V_EMPNO;
 END;
 /

The placeholder and the question mark are compatible with each other.
The corresponding placeholder value should be increased according to the increment of binding parameter count.

CREATE OR REPLACE PROCEDURE raise_emp_salary (column_value NUMBER,
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;
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 NUMBER,
emp_column VARCHAR2(30), 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 SYSTEM_.SYS_TABLES_ TS, SYSTEM_.SYS_COLUMNS_ CS
 WHERE TS.USER_ID = CS.USER_ID AND TS.TABLE_ID = CS.TABLE_ID
AND TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = emp_column;
 sql_stmt := 'UPDATE employees SET salary = salary + ?
WHERE ' || v_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;
/

Specifying question marks and placeholders in the dynamic SQL variables respectively.

CREATE OR REPLACE PROCEDURE PROC1
AS
plsql_block VARCHAR2(500);
new_deptid NUMBER(4);
new_dname VARCHAR2(30) := 'Advertising';
new_mgrid NUMBER(6) := 200;
new_locid NUMBER(4) := 1700;
BEGIN
plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';
EXECUTE IMMEDIATE plsql_block
USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
END;
/

CREATE OR REPLACE PROCEDURE PROC1
AS 
plsql_block VARCHAR2(500);
new_deptid NUMBER(4);
new_dname VARCHAR2(30) := 'Advertising';
new_mgrid NUMBER(6) := 200;
new_locid NUMBER(4) := 1700;
BEGIN
plsql_block := 'BEGIN create_dept(:new_deptid, :new_dname, :new_mgrid, :new_locid); END;';
EXECUTE IMMEDIATE plsql_block
USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
END;
/

Specifying arbitrary(e.g> 1,2,3,4.. a,b,c,d..) parameter is impossible to use in ALTIBASE Stored Procedure.

CREATE OR REPLACE PROCEDURE fire_employee (emp_id NUMBER)
 AS
BEGIN
EXECUTE IMMEDIATE
'DELETE FROM employees WHERE eno = ' || TO_CHAR(emp_id);
END;
/

CREATE OR REPLACE PROCEDURE fire_employee (emp_id NUMBER)
AS
BEGIN
EXECUTE IMMEDIATE
'DELETE FROM employees WHERE eno = ' || TO_CHAR(emp_id);
END;
/

Delivering DML as parameter.

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
END;
/

CREATE PROCEDURE drop_table (table_name IN VARCHAR2(40)) AS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
END;
/

Delivering DDL as parameter.

CREATE OR REPLACE PROCEDURE PROC1
AS
a NUMBER := 4;
b NUMBER := 7;
plsql_block VARCHAR2(100);
BEGIN
plsql_block := 'EXEC calc_stats(:a, :a, :b, :a);';
EXECUTE IMMEDIATE plsql_block USING a, b;
END;
/

CREATE OR REPLACE PROCEDURE PROC1
AS
a NUMBER := 4;
b NUMBER := 7;
plsql_block VARCHAR2(100);
BEGIN
plsql_block := 'EXEC calc_stats(:a, :a, :b, :a);';
EXECUTE IMMEDIATE plsql_block USING a, b;
END;
/

Duplicate parameter name is not supported in ALTIBASE Stored Procedure.
You will encounter into
[ERR-31248 : Mismatched bind column count
0008 : EXECUTE IMMEDIATE PLSQL_BLOCK USING A, B;
      ^                                        ^
]

DECLARE
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM employees WHERE eno > 10';
DBMS_OUTPUT.PUT_LINE('Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT));
END;
/

CREATE OR REPLACE PROCEDURE PROC1
AS
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM employees WHERE eno > 10';
SYSTEM_.PRINTLN('Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT));
END;
/

Using Cursor Attributes with Dynamic SQL

CREATE OR REPLACE FUNCTION row_count (tab_name VARCHAR2) RETURN NUMBER
AS rows NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tab_name INTO rows;
RETURN rows;
END;
/

CREATE OR REPLACE FUNCTION row_count (tab_name VARCHAR2(40)) RETURN NUMBER
AS rows NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tab_name INTO rows;
RETURN rows;
END;
/

The conversion of function in Altibase function is almost similar to that of ORACLE PL/SQL.

CREATE OR REPLACE PROCEDURE PROC1
AS
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
emp_rec employees%ROWTYPE;
sql_stmt VARCHAR2(200);
v_job VARCHAR2(10) := 'designer';
BEGIN
sql_stmt := 'SELECT * FROM employees WHERE emp_job = :j';
OPEN emp_cv FOR sql_stmt USING v_job;
LOOP
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name: ' || emp_rec.e_lastname || ' Job Id: ' ||
emp_rec.emp_job);
END LOOP;
CLOSE emp_cv;
END;
/

CREATE OR REPLACE PROCEDURE PROC1
AS
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
emp_rec employees%ROWTYPE;
sql_stmt VARCHAR2(200);
v_job VARCHAR2(10) := 'designer';
BEGIN
sql_stmt := 'SELECT * FROM employees WHERE emp_job = :j';
OPEN emp_cv FOR sql_stmt USING v_job;
LOOP
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
SYSTEM_.PRINTLN('Name: ' || emp_rec.e_lastname || ' Job Id: ' ||emp_rec.emp_job);
END LOOP;
CLOSE emp_cv;
END;
/

Dynamic SQL Fetching into a Record