1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
| PROCEDURE add_suspect (lastName employees.last_name%TYPE, employeeId employees.employee_id%TYPE) IS
nb_table NUMBER(10);
BEGIN
SELECT 1 INTO nb_table FROM user_tables WHERE table_name = 'SUSPECTS';
EXECUTE IMMEDIATE 'INSERT INTO suspects SELECT employee_id, last_name, first_name,
hire_date, salary, job_id FROM employees WHERE last_name = :1 AND employee_id = :2'
USING lastName, employeeId;
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXECUTE IMMEDIATE 'CREATE TABLE suspects(employee_id employees.last_name%TYPE, last_name VARCHAR2(25),
first_name VARCHAR2(25), hire_date DATE, salary NUMBER(8,2), job_id VARCHAR(10))';
EXECUTE IMMEDIATE 'INSERT INTO suspects SELECT employee_id, last_name, first_name,
hire_date, salary, job_id FROM employees WHERE last_name = :1 AND employee_id = :2'
USING lastName, employeeId;
END add_suspect; |
Partager