SQL*PLUS, PLSQL and SQLLDR

1
SQLPLUS, PLSQL and SQLLDR

• Ali Obaidi

2
SQL Advantages

• High level
• Builds on relational algebra and calculus
• Powerful operations
• Enables automatic optimization
• Table oriented
• Direct representation and manipulation as bags
  of rows
• Standardized
• ANSI standards
• Compatibility across vendors
• Gateway to other languages
• Embeddings in 3GL languages (e.g. C)
• Client software via ODBC, JDBC

3
PL/SQLA Procedural Extension To SQL

• Procedural, statement oriented
• Blocks, declarations, assignable variables,
  conditionals, iterations, exceptions
• Subprograms
• Procedures, functions
• Packages
• With specification separate from body
• Composite datatypes
• Rows, cursors, records (structs), tables (arrays)

4
Achievements

• Modular, Reusable Database
• Manipulation and Maintenance Code
• Stored objects
• Procedures, functions packages
• External procedures
• PL/SQL code invoked from 3GL languages
• Triggers
• Stored procedures invoked by insert, delete,
  update
• Crucial for integrity maintenance

5
Using PL/SQL From SQLPlus

• Execute the following to enable printing
• set serveroutput on
• Defining procedures
• Since procedures, functions and packages are
  objects, use create
• Do a drop first
• create or replace
• Type in source code, execute by single line
  containing /
• Can also input from source file
• start ltfile_namegt
• Examine syntax errors by
• show errors

6
Example

• SQLgt declare
• i integer
• j integer
• begin
• i 0
• for j in 1..10 loop
• i ij
• end loop
• dbms_output.put_line(i)
• end
• /

7
Cursors

• Cursors allow embedded SQL statements
• Result is a set (table) in a temporary work area
• Cursor name permits iterative manipulation of
  rows
• Two varieties of cursors
• Implicit
• Quick to write
• For simple iteration of resulting row set
• Explicit
• More detailed to write
• Permit more advanced manipulations

8
Example

• SQLgt create or replace procedure sumsalary IS
• cursor c1 is
• select from employee
• salsum integer
• begin
• salsum 0
• for emp_rec in c1 loop
• salsum salsum emp_rec.salary
• end loop
• dbms_output.put_line('Salary sum ' salsum)
• end
• 13 /
• SQLgt exec sumsalary
• Salary sum 5805000

9
Explicit Cursor Operations

• Declare
• Introduces cursor object, bound to SQL statement
• OPEN
• Constructs temporary table
• FETCH
• Accesses next row
• Tests
• FOUND, NOTFOUND, ROWCOUNT, ISOPEN
• CLOSE
• Releases temporary table and associated working
  data

10

• SQLgt DECLARE
• CURSOR c1(min_salary integer) IS
• SELECT name FROM employee WHERE salary gt
  min_salary
• PROCEDURE count_salaries(m_s integer) IS
• emp_rec c1ROWTYPE
• how_many integer 0
• BEGIN
• OPEN c1(m_s)
• FETCH c1 INTO emp_rec
• WHILE c1FOUND LOOP
• how_many how_many 1
• FETCH c1 INTO emp_rec
• END LOOP
• dbms_output.put_line(how_many ' employees
  have salary gt '17 m_s)
• CLOSE c1
• END
• BEGIN
• count_salaries(40000)
• count_salaries(60000)

11
Relational Data Model

• Defined by Edgar Codd in 1970
• Considered ingenious but impractical
• Conceptually simple
• Relational DB is perceived as a collection of
  tables
• Provides SQL, a 4GL

12
Functions In PL/SQL

• SQLgt create or replace function f(i in integer)
• return integer IS
• begin
• return ii
• end
• /
• Function created.
• SQLgt exec dbms_output.put_line(f(13))
• 169