SQLPLUS, PLSQL and SQLLDR - PowerPoint

Document Sample
SQLPLUS, PLSQL and SQLLDR - PowerPoint Powered By Docstoc
					SQL*PLUS, PLSQL and
     SQLLDR
      Ali Obaidi
              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
               PL/SQL
    A 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)
                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
   Using PL/SQL From SQL*Plus
• 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 <file_name>
• Examine syntax errors by
  – show errors
               Example
SQL> declare
         i integer;
         j integer;
      begin
         i := 0;
         for j in 1..10 loop
         i := i+j;
         end loop;
         dbms_output.put_line(i);
         end;
         /
                    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
                  Example
• SQL> 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 /
• SQL> exec sumsalary
• Salary sum: 5805000
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
SQL> DECLARE
CURSOR c1(min_salary integer) IS
SELECT name FROM employee WHERE salary > min_salary;
PROCEDURE count_salaries(m_s integer) IS
emp_rec c1%ROWTYPE;
how_many integer := 0;
BEGIN
OPEN c1(m_s);
FETCH c1 INTO emp_rec;
WHILE c1%FOUND LOOP
     how_many := how_many + 1;
     FETCH c1 INTO emp_rec;
END LOOP;
dbms_output.put_line(how_many || ' employees have salary > '17 || m_s);
CLOSE c1;
END;
BEGIN
count_salaries(40000);
count_salaries(60000);
END;
/
5 employees have salary > 40000
3 employees have salary > 60000
         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
     Functions In PL/SQL
SQL> create or replace function f(i in
  integer)
      return integer IS
      begin
           return i*i;
      end;
      /
Function created.
SQL> exec dbms_output.put_line(f(13));
• 169