Manipulating Data Using PL/SQL Database Management System

Manipulating Data Using PL/SQL Database Management System

Manipulating Data UsingPL/SQL Database Management System : You manipulate data in the database by using the DML commands. You can issue the DML commands INSERT, UPDATE, DELETE and MERGE without restriction in PL/SOL. Row locks (and table locks) are released by including COMMIT or ROLLBACK Statements in the PL/SOL code.

  • The INSERT Statement adds new rows of data to the table.
  • The UPDATE statement modifies existing rows in the table.
  • The DELETE statement removes unwanted rows from the table.

Inserting Data

Add new emp information to the EMP table.

Example:

BEGIN        

INSERT INTO emp

(emp_id, first name, last_name, email,

hire_date, job_id, sal)

VALUES

(emp_seq.NEXTVAL, ‘Ruth’, ‘Cores’, ‘RCORES’,

sysdate, AD_ASST,4000);

END;

Inserting Data

in the example on the slide, an INSERT statement is used within a PL/SQL block to inserta record into the EMP table. While using the INSERT command in a PL/SOL block, you can:

  • Use SOL functions, such as USER and SYSDATE
  • Generate primary key values by using database sequences
  • Derive values in the PL/SOL block
  • Add Column default values

Note: There is no possibility for ambiguity with identifiers and column names in the INSERT statement. Any identifier in the INSERT clause must be a database column name.

Updating Data              

increase the sal of all emp who are stock clerks.

Example:

DECLARE

Var_sal increase emp.sal%TYPE := 800;

BEGIN

UPDATE emp

SET sal = sal + var_sal_increase

WHERE job_id=’ST_CLERK’;

END;

/

Updating Data

There may be ambiguity in the SET clause of the UPDATE statement because although the identifier on the left of the assignment operator is always a database Column, the identifier on the right can be either a database Column or a PL/SQL variable.

Remember that the WHERE clause is used to determine which rows are affected. If no rows are modified, no error occurs, unlike the SELECT statement in PL/SOL.

Note: PL/SOL variable assignments always use :=, and SOL column assignments always use =.

Recall that if Column names and identifier names are identical in the WHERE clause, the Oracle Server looks to the database first for the name.

Deleting Data

Delete rows that belong to department 10 from the EMP table.

Example:

DECLARE         

Var_deptno emp.department_id%TYPE := 10;

BEGIN

DELETE FROM emp

WHERE department_id = var deptno;

END;

/

Deleting Data

The DELETE statement removes unwanted rows from a table. Without the use of a WHERE clause, the entire Contents of a table can be removed, provided there are no integrity constraints.

SQL Cursor

Whenever you issue a SQL statement, the Oracle server opens an area of memory in which the Command is parsed and executed. This area is called a cursor.

When the executable part of a block issues a SQL statement, PL/SQL creates an implicit cursor, which PL/SQL manages automatically. The programmer explicitly declares and names an explicit cursor. There are four attributes available in PL/SQL that can be applied to cursors. Note: More information about explicit cursors is covered in a subsequent lesson.