Interacting With The Oracle Server

1. Interacting With The Oracle Server

2. Objectives After completing this lesson, you should be able to do the following: • Write a successful SELECT statement in PL/SQL • Declare the datatype and size of a PL/SQL variable dynamically. • Write DML statement in PL/SQL. • Control transactions in PL/SQL. • Determine the outcome of SQL DML statements.

3. SQL Statements in PL/SQL • Extract a row of data from the database by using the SELECT command. Only a single set of values can be returned. • Make changes to rows in the database by using DML commands. • Control a transaction with the COMMIT, ROLLBACK, or SAVEPOINT command. • Determine DML outcome with implicit cursors.

4. SELECT statement in PL/SQL • Retrieve data from the database with SELECT. • Syntax SELECT select_list INTO { variable_name[, variable_name]…. | record_name} FROM table WHERE condition;

5. SELECT Statement in PL/SQL • The INTO clause is required • Example: DECLARE v_dept_id NUMBER(2); v_loc_id VARCHAR(15); BEGIN SELECT department id, location id INTO v_dept_id, v_loc_id FROM departments WHERE department_name =‘SALES’; …. END;

6. Manipulating Data Using PL/SQL • Make changes to database tables by using DML commands: • INSERT • UPDATE • DELETE

7. Inserting Data The column list is optional if values will be inserted for all columns and the value list conforms to the order of columns in the table. The basic syntax: INSERT INTO table_name(column_list) VALUES (value_list); Ensure that each value conforms to the corresponding column’s datatype.

8. Inserting Data BEGIN INSERT INTO employees VALUES (207, ‘Jun’, ‘Aziz’, ‘JAZIZ’, ‘590.423.3256’, SYSDATE, ‘IT_PROG’, 6000, NULL, 103, 60); END; / Here we are inserting a record for a new programmer in the IT department.

9. Updating Data The basic syntax: UPDATE table_name SET column_name = new_value WHERE update_condition The WHERE clause is optional if all of the records in the table are to be updated.

10. Updating Data BEGIN UPDATE employees SET salary = salary * 1.1 WHERE employee_id = 207; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ‘ row(s) updated’); END; / Omitting the condition will increase the salary for all employees.

11. Deleting Data The basic syntax: DELETE FROM table_name WHERE delete_condition The WHERE clause is optional if all of the records in the table are to be deleted.

12. Deleting Data BEGIN DELETE FROM employees WHERE employee_id = 207; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ‘ row(s) updated’); END; / Omitting the condition will delete all employee records.

13. Example: Increasing the Salary of All Employees by a User-supplied Percentage SELECT employee_id, salary FROM employees SET SERVEROUTPUT ON ACCEPT p_percent NUMBER PROMPT ‘Enter the percentage of salary increase: ’ BEGIN UPDATE employees SET salary = salary + (salary * &p_percent / 100); DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' employee salaries updated'); END; / SET SERVEROUTPUT OFF UNDEFINE p_percent SELECT employee_id, salary FROM employees

14. Extras • Issue the ROLLBACK command in iSQL Plus if you want to undo an insertion, update or deletion

15. Exercise • Retrieve the records of employees that are receiving minimum salaries. • Increase the minimum salary for all jobs by 10%. • Update the salaries of the employees retrieved earlier. • Insert a job history record for a clerk who will be promoted to the post Administration Assistant for his/her department. • Update his/her employee record. • Delete location records that do not have any departments.