Using SQL Statements Within A PL/SQL Block

5
Using SQL Statements within a PL/SQL Block
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Objectives
After completing this lesson, you should be able to do the

following:
• Determine the SQL statements that can be directly
included in a PL/SQL executable block
• Manipulate data with DML statements in PL/SQL
• Use transaction control statements in PL/SQL
• Make use of the INTO clause to hold the values returned
by a SQL statement
• Differentiate between implicit cursors and explicit cursors
• Use SQL cursor attributes
5-2 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Agenda
• Retrieving data with PL/SQL

• Manipulating data with PL/SQL
• Introducing SQL cursors
SQL Statements in PL/SQL
• Retrieve a row from the database by using the SELECT

command.
• Make changes to rows in the database by using DML
commands.
• Control a transaction with the COMMIT, ROLLBACK, or
SAVEPOINT command.
SELECT Statements in PL/SQL
Retrieve data from the database with a SELECT statement.

Syntax:
SELECT select_list
INTO {variable_name[, variable_name]...
| record_name}
FROM table
[WHERE condition];
SELECT Statements in PL/SQL
• The INTO clause is required.

• Queries must return only one row.
DECLARE
v_fname VARCHAR2(25);
BEGIN
SELECT first_name INTO v_fname
FROM employees WHERE employee_id=200;
DBMS_OUTPUT.PUT_LINE(' First Name is : '||v_fname);
END;
/
Retrieving Data in PL/SQL: Example
Retrieve hire_date and salary for the specified employee.

DECLARE
v_emp_hiredate employees.hire_date%TYPE;
v_emp_salary employees.salary%TYPE;
BEGIN
SELECT hire_date, salary
INTO v_emp_hiredate, v_emp_salary
FROM employees
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE ('Hire date is :'|| v_emp_hiredate);
DBMS_OUTPUT.PUT_LINE ('Salary is :'|| v_emp_salary);
END;
/
Retrieving Data in PL/SQL
Return the sum of salaries for all the employees in the specified
department.
Example:
DECLARE
v_sum_sal NUMBER(10,2);
v_deptno NUMBER NOT NULL := 60;
BEGIN
SELECT SUM(salary) -- group function
INTO v_sum_sal FROM employees
WHERE department_id = v_deptno;
DBMS_OUTPUT.PUT_LINE ('The sum of salary is ' || v_sum_sal);
END;
5 - 10 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Naming Ambiguities
DECLARE
hire_date employees.hire_date%TYPE;
sysdate hire_date%TYPE;
employee_id employees.employee_id%TYPE := 176;
BEGIN
SELECT hire_date, sysdate
INTO hire_date, sysdate
FROM employees
WHERE employee_id = employee_id;
END;
/

Naming Conventions
• Use a naming convention to avoid ambiguity in the WHERE

clause.
• Avoid using database column names as identifiers.
• Syntax errors can arise because PL/SQL checks the
database first for a column in the table.
• The names of local variables and formal parameters take
precedence over the names of database tables.
• The names of database table columns take precedence
over the names of local variables.
• The names of variables take precedence over the function
names.

Agenda


Using PL/SQL to Manipulate Data
Make changes to database tables by using DML commands:

• INSERT
• UPDATE
• DELETE DELETE
• MERGE
INSERT
MERGE
UPDATE

Inserting Data: Example
Add new employee information to the EMPLOYEES table.
BEGIN
INSERT INTO employees
(employee_id, first_name, last_name, email,
hire_date, job_id, salary)
VALUES(employees_seq.NEXTVAL, 'Ruth', 'Cores',
'RCORES',CURRENT_DATE, 'AD_ASST', 4000);
END;
/

Updating Data: Example
Increase the salary of all employees who are stock clerks.

DECLARE
sal_increase employees.salary%TYPE := 800;
BEGIN
UPDATE employees
SET salary = salary + sal_increase
WHERE job_id = 'ST_CLERK';
END;
/
...

Deleting Data: Example
Delete rows that belong to department 10 from the employees

table.
DECLARE
deptno employees.department_id%TYPE := 10;
BEGIN
DELETE FROM employees
WHERE department_id = deptno;
END;
/

Merging Rows
Insert or update rows in the copy_emp table to match the

employees table.
BEGIN
MERGE INTO copy_emp c
USING employees e
ON (e.employee_id = c.empno)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
c.email = e.email,
. . .
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name, e.last_name,
. . .,e.department_id);
END;
/

Agenda


SQL Cursor
• A cursor is a pointer to the private memory area allocated

by the Oracle Server. It is used to handle the result set of a
SELECT statement.
• There are two types of cursors: implicit and explicit.
– Implicit: Created and managed internally by the Oracle
Server to process SQL statements
– Explicit: Declared explicitly by the programmer
Implicit cursor Explicit cursor

SQL Cursor Attributes for Implicit Cursors
Using SQL cursor attributes, you can test the outcome of your
SQL statements.
SQL%FOUND Boolean attribute that evaluates to TRUE if the

most recent SQL statement affected at least one
row
SQL%NOTFOUND Boolean attribute that evaluates to TRUE if
the most recent SQL statement did not affect
even one row
SQL%ROWCOUNT An integer value that represents the number of
rows affected by the most recent SQL statement

SQL Cursor Attributes for Implicit Cursors
Delete rows that have the specified employee ID from the

employees table. Print the number of rows deleted.
Example:
DECLARE
v_rows_deleted VARCHAR2(30);
v_empno employees.employee_id%TYPE := 176;
BEGIN
DELETE FROM employees
WHERE employee_id = v_empno;
v_rows_deleted := (SQL%ROWCOUNT ||
' row deleted.');
DBMS_OUTPUT.PUT_LINE (v_rows_deleted);
END;

Quiz
When using the SELECT statement in PL/SQL, the INTO clause

is required and queries can return one or more rows.
a. True
b. False

Summary
In this lesson, you should have learned how to:

• Embed DML statements, transaction control statements,
and DDL statements in PL/SQL
• Use the INTO clause, which is mandatory for all SELECT
statements in PL/SQL
• Differentiate between implicit cursors and explicit cursors
• Use SQL cursor attributes to determine the outcome of
SQL statements

Practice 5: Overview
This practice covers the following topics:

• Selecting data from a table
• Inserting data into a table
• Updating data in a table
• Deleting a record from a table

Using SQL Statements Within A PL/SQL Block

Uploaded by

Copyright:

Available Formats

Using SQL Statements Within A PL/SQL Block

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Using SQL Statements Within A PL/SQL Block

Uploaded by

Copyright:

Available Formats

5

Using SQL Statements within a PL/SQL Block

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

After completing this lesson, you should be able to do the

• Retrieving data with PL/SQL

• Retrieve a row from the database by using the SELECT

Retrieve data from the database with a SELECT statement.

• The INTO clause is required.

Retrieve hire_date and salary for the specified employee.

5 - 10 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

5 - 11 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

• Use a naming convention to avoid ambiguity in the WHERE

5 - 12 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

• Retrieving data with PL/SQL

5 - 13 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Make changes to database tables by using DML commands:

5 - 14 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Add new employee information to the EMPLOYEES table.

5 - 15 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Increase the salary of all employees who are stock clerks.

5 - 16 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Delete rows that belong to department 10 from the employees

5 - 17 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Insert or update rows in the copy_emp table to match the

5 - 18 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

• Retrieving data with PL/SQL

5 - 20 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

• A cursor is a pointer to the private memory area allocated

Implicit cursor Explicit cursor

5 - 21 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

SQL%FOUND Boolean attribute that evaluates to TRUE if the

5 - 23 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Delete rows that have the specified employee ID from the

5 - 24 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

When using the SELECT statement in PL/SQL, the INTO clause

5 - 25 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

In this lesson, you should have learned how to:

5 - 26 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

This practice covers the following topics:

5 - 27 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

You might also like