SQLPLUS PLSQL and SQLLDR Ali Obaidi SQL Advantages

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 3 GL 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 3 GL 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 c 1 is select * from employee; salsum integer; begin salsum : = 0; for emp_rec in c 1 loop salsum : = salsum + emp_rec. salary; end loop; dbms_output. put_line('Salary sum: ' || salsum); end; • 13 / • SQL> exec sumsalary

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 c 1(min_salary integer) IS SELECT name FROM employee WHERE salary > min_salary; PROCEDURE count_salaries(m_s integer) IS emp_rec c 1%ROWTYPE; how_many integer : = 0; BEGIN OPEN c 1(m_s); FETCH c 1 INTO emp_rec; WHILE c 1%FOUND LOOP how_many : = how_many + 1; FETCH c 1 INTO emp_rec; END LOOP; dbms_output. put_line(how_many || ' employees have salary > '17 || m_s); CLOSE c 1; 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 4 GL

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