Use dynamic SQL to check the business logic : DBMS_SQL « System Packages « Oracle PL/SQL Tutorial






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>
SQL>








31.26.DBMS_SQL
31.26.1.dbms_sql.number_table
31.26.2.Close a cursor
31.26.3.Assign date with DBMS_SQL package
31.26.4.Create Pl/SQL block dynamically and then execute it by calling 'DBMS_SQL.EXECUTE'
31.26.5.DBMS_SQL.PARSE
31.26.6.An example of using DBMS_SQL.OPEN_CURSOR
31.26.7.Package for running dynamic sql based on DBMS_SQL package
31.26.8.Wrap dbms_sql package
31.26.9.Dump Column long with DBMS_SQL.DEFINE_COLUMN_LONG
31.26.10.DBMS_SQL.BIND_VARIABLE and DBMS_SQL.EXECUTE
31.26.11.DBMS_SQL.EXECUTE an update statement
31.26.12.Use DBMS_SQL to update a table and get the number of rows updated
31.26.13.DBMS_SQL.VARCHAR2_TABLE and DBMS_SQL.NUMBER_TABLE
31.26.14.Use dynamic SQL to check the business logic
31.26.15.Use DBMS_SQL package to parse math expression
31.26.16.Use a character string of arithmetic logic, selecting against the DUAL table to return a number value.
31.26.17.DBMS_SQL.LAST_ERROR_POSITION
31.26.18.Dump query with dbms_sql
31.26.19.Use dbms_sql.describe_columns
31.26.20.Print table with dynamic query