Mega Code Archive
Use dynamic SQL to check the business logic
<![CDATA[
SQL>
SQL> CREATE TABLE employees
2 ( employee_id number(10) not null,
3 last_name varchar2(50) not null,
4 email varchar2(30),
5 hire_date date,
6 job_id varchar2(30),
7 department_id number(10),
8 salary number(6),
9 manager_id number(6)
10 );
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
2 values ( 1001, 'Lawson', 'lawson@g.com', '01-JAN-2002','MGR', 30000,1 ,1004);
1 row created.
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values ( 1002, 'Wells', 'wells@g.com', '01-JAN-2002', 'DBA', 20000,2, 1005 );
1 row created.
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values( 1003, 'Bliss', 'bliss@g.com', '01-JAN-2002', 'PROG', 24000,3 ,1004);
1 row created.
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1004, 'Kate', 'YourName@a.com', SYSDATE-3650, 'MGR',25000 ,4, 1005);
1 row created.
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1005, 'Dillon', 'sdillon@a .com', SYSDATE, 'PROG', 20000, 1, 1006);
1 row created.
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
2 values( 1006, 'Beck', 'clbeck@g.com', SYSDATE, 'PROG', 20000, 2, null);
1 row created.
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1007, 'Java', 'java01@g.com', SYSDATE, 'PROG', 20000, 3, 1006);
1 row created.
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1008, 'Oracle', 'oracle1@g.com', SYSDATE, 'DBA', 20000, 4, 1006);
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE update_salary (empid_in IN INTEGER, newsal_in IN NUMBER)
2 IS
3 updcur INTEGER := DBMS_SQL.OPEN_CURSOR;
4 returnValue INTEGER;
5 BEGIN
6 DBMS_SQL.PARSE (updcur,'UPDATE employee SET salary = GREATEST (:minsal, salary) WHERE employee_id = :empid',DBMS_SQL.NATIVE);
7 DBMS_SQL.BIND_VARIABLE (updcur, 'empid', empid_in);
8 DBMS_SQL.BIND_VARIABLE (updcur, 'minsal', newsal_in);
9 returnValue := DBMS_SQL.EXECUTE (updcur);
10 DBMS_SQL.CLOSE_CURSOR (updcur);
11 END;
12 /
Procedure created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE ensure_minsal (minsal_in IN NUMBER, where_in IN VARCHAR2 := NULL)
2 IS
3 cur INTEGER := DBMS_SQL.OPEN_CURSOR;
4 rec employee%ROWTYPE;
5 returnValue INTEGER;
6 BEGIN
7 DBMS_SQL.PARSE (cur,'SELECT employee_id, salary FROM employee WHERE ' || NVL (where_in, '1=1'),DBMS_SQL.NATIVE);
8
9 DBMS_SQL.DEFINE_COLUMN (cur, 1, 1);
10 DBMS_SQL.DEFINE_COLUMN (cur, 2, 1);
11
12 returnValue := DBMS_SQL.EXECUTE (cur);
13 LOOP
14 EXIT WHEN DBMS_SQL.LAST_ROW_COUNT > 10 OR DBMS_SQL.FETCH_ROWS (cur) = 0;
15 DBMS_SQL.COLUMN_VALUE (cur, 1, rec.employee_id);
16 DBMS_SQL.COLUMN_VALUE (cur, 2, rec.salary);
17 update_salary (rec.employee_id, minsal_in);
18 DBMS_OUTPUT.PUT_LINE ('Updated salary for ' || rec.employee_id);
19 END LOOP;
20
21 DBMS_SQL.CLOSE_CURSOR (cur);
22 END;
23 /
SQL>
SQL> drop table employees;
SQL>
]]>