120 likes | 394 Views
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
E N D
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/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)
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