AntDB-Oracle Compatibility Developer’s Manual P4–19

INSERT

The INSERT command in standard SQL can also be used in SPL programs.

The same expressions that appear in the standard SQL INSERT command can also be used in SPL. Thus, SPL variables and parameters can be used to provide values for insert operations.

In the following example a procedure performs the operation of inserting the value passed in the calling program into the emp data table as a new employee record.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_insert (
    p_empno IN NUMBER(4),
    p_ename IN VARCHAR2(10),
    p_job IN VARCHAR2(9),
    p_mgr IN NUMBER(4),
    p_hiredate IN DATE,
    p_sal IN NUMBER(7,2),
    p_comm IN NUMBER(7,2),
    p_deptno IN NUMBER(2)
)
IS
BEGIN
    INSERT INTO emp VALUES (
        p_empno,
        p_ename,
        p_job,
        p_mgr,
        p_hiredate,
        p_sal,
        p_comm,
        p_deptno);

    DBMS_OUTPUT.PUT_LINE('Added employee...');
    DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
    DBMS_OUTPUT.PUT_LINE('Name : ' || p_ename);
    DBMS_OUTPUT.PUT_LINE('Job : ' || p_job);
    DBMS_OUTPUT.PUT_LINE('Manager : ' || p_mgr);
    DBMS_OUTPUT.PUT_LINE('Hire Date : ' || p_hiredate);
    DBMS_OUTPUT.PUT_LINE('Salary : ' || p_sal);
    DBMS_OUTPUT.PUT_LINE('Commission : ' || p_comm);
    DBMS_OUTPUT.PUT_LINE('Dept # : ' || p_deptno);
    DBMS_OUTPUT.PUT_LINE('----------------------');

END;
/
\set PLSQL_MODE off

If the procedure encounters an exception during execution, all modifications to the database will be automatically rolled back. The exception section with the WHEN OTHERS clause in this example catches all exceptions. Two variables are displayed as output, SQLCODE is a numeric value that identifies an exception encountered, and SQLERRM is a text message that explains the exception error that occurred.

The following is the output produced by the stored procedure execution.

postgres=# select emp_insert(9503,'PETERSON','ANALYST',7902,'31-MAR-05',5000,NULL,40);
NOTICE:  Added employee...
NOTICE:  Employee # : 9503
NOTICE:  Name : PETERSON
NOTICE:  Job : ANALYST
NOTICE:  Manager : 7902
NOTICE:  Hire Date : 2005-03-31 00:00:00
NOTICE:  Salary : 5000
NOTICE:  Commission :
NOTICE:  Dept # : 40
NOTICE:  ----------------------
 EMP_INSERT
------------

(1 row)

postgres=# select * from emp WHERE empno = 9503;
 EMPNO |  ENAME   |   JOB   | MGR  |      HIREDATE       | SAL  | COMM | DEPTNO
-------+----------+---------+------+---------------------+------+------+--------
  9503 | PETERSON | ANALYST | 7902 | 2005-03-31 00:00:00 | 5000 |      |     40
(1 row)

Note: INSERT commands can be included in a FORALL statement. The FORALL statement allows an INSERT command to insert multiple new rows using the values provided by one or more collections.

UPDATE

The UPDATE command in standard SQL can also be used in SPL programs.

Expressions in the SPL language can also be used where expressions appear in the UPDATE command in standard SQL. Therefore, SPL variables and parameters can be used to provide values for update operations.

Prepare test data.

CREATE TABLE emp (
 empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (7389,'SMITH','CLERK',7902,'17-DEC-80',800,20);

Create a stored procedure.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_comp_update (
    p_empno IN NUMBER,
    p_sal IN NUMBER(7,2),
    p_comm IN NUMBER(7,2)
)
IS
BEGIN
    UPDATE emp SET sal = p_sal, comm = p_comm WHERE empno = p_empno;

    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Updated Employee # : ' || p_empno);
        DBMS_OUTPUT.PUT_LINE('New Salary : ' || p_sal);
        DBMS_OUTPUT.PUT_LINE('New Commission : ' || p_comm);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
    END IF;
END;
/
\set PLSQL_MODE off

The SQL%FOUND conditional expression returns "true" if a row is successfully updated, otherwise it returns "false".

The following procedure performs the update operation in the employee table.

postgres=# select emp_comp_update(7369, 6540, 1200);
NOTICE:  Updated Employee # : 7369
NOTICE:  New Salary : 6540
NOTICE:  New Commission : 1200
 EMP_COMP_UPDATE
-----------------

(1 row)

postgres=# select * from emp where empno=7369;
 EMPNO | ENAME |  JOB  | MGR  |      HIREDATE       | SAL  | COMM | DEPTNO
-------+-------+-------+------+---------------------+------+------+--------
  7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 6540 | 1200 |     20
(1 row)

Note: You can include the UPDATE command in a FORALL statement. The FORALL statement allows a single UPDATE command to update multiple rows with the values provided by one or more collections.