1 / 12

SQL*PLUS, PLSQL and SQLLDR

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

umay
Download Presentation

SQL*PLUS, PLSQL and SQLLDR

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL*PLUS, 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 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

  6. 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; /

  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 • 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

  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. 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

  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 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

More Related