Oracle |
Altibase |
Comments |
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 |
