dating in cyprus sites - Updating the record using cursor

CREATE TABLE employee 2 (employee_id NUMBER(7), 3 last_name VARCHAR2(25), 4 first_name VARCHAR2(25), 5 userid VARCHAR2(8), 6 start_date DATE, 7 comments VARCHAR2(255), 8 manager_id NUMBER(7), 9 title VARCHAR2(25), 10 department_id NUMBER(7), 11 salary NUMBER(11, 2), 12 commission_pct NUMBER(4, 2) 13 ); Table created.

SQL DECLARE 2 CURSOR emp Cursor IS 3 SELECT employee_id, department_id, NVL(salary,0) salary, ROWID 4 FROM employee; 5 lv_record_num PLS_INTEGER DEFAULT 0; 6 BEGIN 7 FOR emp Record IN emp Cursor LOOP 8 lv_record_num := lv_record_num + 1; 9 IF emp Record.department_id = 10 OR 10 emp Record.department_id = 41 THEN 11 IF emp Record.salary 2000 THEN 24 emp Record.salary := emp Record.salary * 1.05; 25 ELSE 26 emp Record.salary := emp Record.salary * 1.10; 27 END IF; 28 END IF; 29 UPDATE employee 30 SET salary = emp Record.salary 31 WHERE rowid = emp Record. PUT_LINE(); 43 ROLLBACK; 44 END; 45 / Employee: 1 Department: 50 New Salary: ,625.00 Employee: 2 Department: 41 New Salary: ,522.50 Employee: 3 Department: 31 New Salary: ,540.00 Employee: 4 Department: 10 New Salary: ,522.50 Employee: 5 Department: 50 New Salary: ,705.00 Employee: 6 Department: 41 New Salary: ,260.00 Employee: 7 Department: 41 New Salary: ,312.50 Employee: 8 Department: 41 New Salary: ,155.00 Employee: 9 Department: 41 New Salary: ,365.00 Employee: 10 Department: 41 New Salary: ,372.35 Update Process Complete.

updating the record using cursor-20

PL/SQL also conforms to the current ANSI/ISO SQL standard.

In addition to static SQL discussed in this chapter, PL/SQL also supports dynamic SQL, which enables you to execute SQL data definition, data control, and session control statements dynamically.

See Chapter 7, "Performing SQL Operations with Native Dynamic SQL".

CREATE TABLE employees_temp AS SELECT employee_id, first_name, last_name FROM employees; DECLARE emp_id employees_temp.employee_id%TYPE; emp_first_name employees_temp.first_name%TYPE; emp_last_name employees_temp.last_name%TYPE; BEGIN INSERT INTO employees_temp VALUES(299, 'Bob', 'Henry'); UPDATE employees_temp SET first_name = 'Robert' WHERE employee_id = 299; DELETE FROM employees_temp WHERE employee_id = 299 RETURNING first_name, last_name INTO emp_first_name, emp_last_name; COMMIT; DBMS_OUTPUT.

PUT_LINE( emp_first_name || ' ' || emp_last_name); END; / CREATE TABLE employees_temp AS SELECT first_name, last_name FROM employees; DECLARE x VARCHAR2(20) := 'my_first_name'; y VARCHAR2(25) := 'my_last_name'; BEGIN INSERT INTO employees_temp VALUES(x, y); UPDATE employees_temp SET last_name = x WHERE first_name = y; DELETE FROM employees_temp WHERE first_name = x; COMMIT; END; / statement that is explained in "Using the EXECUTE IMMEDIATE Statement in PL/SQL".

For information on the use of PL/SQL records with SQL to update and insert data, see "Inserting PL/SQL Records into the Database" and "Updating the Database with PL/SQL Record Values".

For additional information on assigning values to PL/SQL variables, see "Assigning a SQL Query Result to a PL/SQL Variable".

Oracle is transaction oriented; that is, Oracle uses transactions to ensure data integrity.

A transaction is a series of SQL data manipulation statements that does a logical unit of work.

For example, two statements might credit one bank account and debit another.

It is important not to allow one operation to succeed while the other fails.

Tags: , ,