SQL-PLSQL_Quickstart

Slide 1: 

Introduction to SQL and PL/SQL

Objective : 

Objective Provide an introduction to SQL and PL/SQL that will help developers better understand how to work with Oracle and the many different objects in Oracle

Agenda : 

Agenda SQL Sequences Constraints Triggers Views Indexes Introduction to PL/SQL PL/SQL elements Exceptions Conditional control Loops Procedures Functions Packages Cursor

SQL…. The Language for Databases : 

SQL…. The Language for Databases SQL stands for Structured Query Language. SQL is used to communicate with the database. According to ANSI, SQL is the standard language for relational database management systems.

SQL Commands : 

SQL Commands DDL (data definition language) (create, alter, drop) DCL (data control language) (grant, revoke) TCL (transaction control language) (commit, rollback) DML (data manipulation language) (select, insert, update, delete)

SQL Operators : 

SQL Operators Comparison operators (=,!=,>,<,>=,<=, IN, NOT IN, IS NULL, IS NOT NULL, LIKE, BETWEEN x AND y Logical Operators (AND, OR, NOT) Set operators (A=1,2,3,4 B=2,3,5,6) A UNION B => 1,2,3,4,5,6 A INTERSECT B => 2,3 A MINUS B => 1,4 B MINUS A => 5,6

SQL Functions : 

SQL Functions Character functions SUBSTR(string, position, substring_length) e.g. SUBSTR(‘entrac’,2,3) is ‘ntr’ UPPER(string) e.g UPPER(‘entrac’) is ‘ENTRAC’ Date functions SYSDATE ADD_MONTHS(date, n) MONTHS_BETWEEN(date1, date2)

SQL Queries : 

SQL Queries Equijoin or Inner Join SELECT employee.emp_name, department.dept_code, department.dept_name FROM employee, department WHERE department.dept_code = employee.dept_code; Cartesian Join SELECT count(*) FROM employee, department;

SQL queries (continued) : 

SQL queries (continued) Outer Join To list all departments and the employees in that department even if any department is empty: SELECT employee.emp_name, department.dept_code, department.dept_name FROM employee, department WHERE department.dept_code = employee.dept_code(+) ; Self-Join A join that relates to itself. This is useful if the join is to be between one row in a table and another row in the same table. Example: select all employees that have the same birth date SELECT e1.emp_name, e2.emp_name, e1.birth_date FROM employee e1, employee e2 WHERE e1.birth_date = e2.birth_date AND e1.emp_name <> e2.emp_name;

Group Functions : 

Group Functions A group function returns a single result row for a group of queried rows. Some of the group functions are: COUNT(value) Count of rows for a column SUM(value) Sum of all values for group of rows AVG(value) Average of value for group of rows MAX(value) Maximum of all values for group of rows MIN(value) Minimum of all values for group of rows Example: Select the highest salary paid in each department SELECT MAX(salary), dept_id FROM employee GROUP BY dept_id;

Group Functions (continued) : 

Group Functions (continued) Just like you can use a where clause to filter your result set based on values in the tables being queried, you can also filter your result set based on values obtained in your Group functions Use the HAVING function Example: Select the highest salary paid in each department if the highest salary is greater than a minimum threshold of $10,000 SELECT MAX(salary), dept_id FROM employee GROUP BY dept_id HAVING MAX(salary)>10000;

Sub Queries : 

Sub Queries Subqueries are nested SELECT statements. Subqueries can be formed, in one of the following ways: Using IN or NOT IN predicate Specifying the equality(=) or inequality(<>) predicate Specifying a predicate using a comparative operator (<, >, <=, >=) Using EXISTS or NOT EXISTS predicate e.g. SELECT COUNT(emp_id) FROM employee, department WHERE employee.dept_code IN ( SELECT dept_code FROM department WHERE dept_name like ‘%legal%’); e.g. Obtain all the rows that exist in one table, but not the other SELECT * FROM ent_admin.ent_user a WHERE NOT EXISTS (SELECT 1 FROM aderke.ent_user b WHERE a.user_id=b.user_id AND a.user_name=b.user_name)

Correlated Subqueries : 

Correlated Subqueries Correlated subqueries are a type of query that involves using data from the original query in the subquery These can be very powerful but can be slower Example: In a constants table, several versions of the same constant are stored. We want to select the most recent constant for all constants in the table. SELECT constant_id, constant_value FROM constant_value a WHERE effective_date = (SELECT MAX (effective_date) FROM constant_value b WHERE effective_date <= @PCreateDate AND a.constant_id=b.constant_id)

Inserting data : 

Inserting data There are 2 different approaches to take when inserting data into a table: Insert the data one row at a time Insert the data from another table Insert the data one row at a time INSERT INTO ent_user (user_id, user_name) VALUES (1,’Arda’); INSERT INTO ent_user (user_id, user_name) VALUES (2,’Ernie’); Insert the data from another table For example, if the ent_user table is populated in the ent_admin schema but not in my schema, I could do the following: INSERT INTO aderke.ent_user (user_id,user_name)SELECT user_id, user_name from ent_admin.ent_user;

Updating Data : 

Updating Data If you would like to update data that has already been updated into a table, you can use the UPDATE statement: UPDATE ent_user SET user_name = ‘Arda Derkevorkian’, email_addr=‘aderke@sapient.com’ WHERE user_id = 1;

Sequences : 

Sequences A sequence is an object that maintains a sequential list of numbers. A sequence is created through CREATE SEQUENCE command. CREATE SEQUENCE seq_name [INCREMENT BY N1] [START WITH N2] [MAXVALUE N3] [MINVALUE N4] NOCYCLE (.nextval, .currval) e.g. INSERT INTO employee (emp_id, emp_name) values (seq_name.nextval, ‘gogo’);

Constraints : 

Constraints Constraints can be used to establish some rules/relationships in the database Uniqueness Value in the column must exist in parent table Primary Key Every table should have a primary key which is used uniquely identify a row Can be one column or a combination of several columns Foreign Key Foreign keys establish the relationships between tables and verify the validity of data based on the relationship Unique Constraint A record can have one or more fields that can uniquely identify the record without being the primary key This often happens if surrogate keys are used for primary keys and a Unique constraint is used to ensure that a record is based on the identified fields

What is a Trigger? : 

What is a Trigger? A database trigger is a stored subprogram associated with a table Can have Oracle automatically fire the database trigger before or after an INSERT, UPDATE, or DELETE statement affects the table You can use all the SQL data manipulation statements and any procedural statement in the executable part of a database trigger Be careful using triggers!! Data manipulation can be difficult to trace if many triggers are in place However, one of the many uses for database triggers is to audit data modifications

Sample Trigger : 

Sample Trigger CREATE OR REPLACE TRIGGER ent_interface_error_i_trg BEFORE INSERT ON ent_interface_error REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW DECLARE sequence_value NUMBER; BEGIN IF INSERTING THEN SELECT ent_interface_error_seq.NEXTVAL INTO sequence_value FROM DUAL; :NEW.error_id := sequence_value; END IF; END; /

What is a view? : 

What is a view? A logical table based on another table or view Contains no data of its own but is like a window through which data can be viewed/manipulated View are used to: Restrict database access To present different views of the same data To allows users to make simple queries to retrieve the results from complicated queries

Sample View : 

Sample View CREATE OR REPLACE VIEW ent_employee_vw AS SELECT e.employee_name as EmployeeName, d.department_name as DepartmentName FROM employee e, department d WHERE e.dept_id = d.dept_id AND d.dept_id <> ‘999’ ---this is a dummy department;

What is an index? : 

What is an index? Is a schema object that is independent of the table it indexes Is used by Oracle Server to speed up the retrieval of rows by using a pointer Can reduce disk I/O by using rapid path access method to locate the data quickly Is used & maintained automatically by Oracle

Sample Index : 

Sample Index CREATE INDEX employee_idx on employee (employee_id, dept_id);

How are indexes created? : 

How are indexes created? Automatically A unique index is automatically created when you define a primary key or unique constraint in a table definition Manually Users can create nonunique indexes on columns to speed up access time to the rows

Create an index when… : 

Create an index when… The column is used frequently in WHERE clauses or joins The column contains a wide range of values The column contains a large number of null values Two or more columns are frequently used together in a WHERE clause or join (Composite Index) The table is large and most queries are expected to retrieve less than 2-4 % of rows

Do NOT create an index when… : 

Do NOT create an index when… The table is small The columns are not often used as a condition in queries Most queries return > 2-4% of rows The table is updated frequently NOTE: More indexes DOESN’T MEAN infinite increase in speed! Each manipulation of the data (insert, update, delete) committed on a table will require updates to indexes which can become expensive

Tricks for using indexes : 

Tricks for using indexes Must have a where clause to use indexes! Functions and field name modifications in a where clause wil suppress the use of an index Comparing values of different data types will cause Oracle to internally convert the datatype of a field and could suppress the use of indexes For subqueries, use EXISTS clause instead of IN to utilize indexes If the search is for NULL/NOT NULL, no index will be used != condition leads Oracle not to use index

Introduction to PL/SQL : 

Introduction to PL/SQL PL/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. All PL/SQL programs are made up of blocks, which can be nested within each other. Allows data manipulation and query statements to be included in one block Allows user to declare identifiers (variables, constants, cursors, exceptions) Allows user to have conditional, looping, and branching logic Allows user to add exception handling routines

Advantages of PL/SQL : 

Advantages of PL/SQL Can store code in compiled form within the database Operates within the server environment, reducing client-server and network traffic Allows programmer to fully specify programming logic

PL/SQL Coding Standards : 

PL/SQL Coding Standards The goal of using SQL coding standards is for all developers to write identical SQL code, with respect to blank spaces, type case, naming conventions, and comments. The benefits of using standards include: Parsing is reduced and time is saved when the SQL is executed many times Improves readability of the code, which will improve code reviews EnTRAC has PL/SQL coding standards. Find them in cvs at: $\EGD\EnTRAC-Implementation\Assets\Standards\SQL and PL-SQL Stds.doc

Program Constructs : 

Program Constructs Can use PL/SQL to write: Stored procedures Named block of code that can accept parameters Stored in compiled form Functions Similar to stored procedure but can return a value Triggers Code that is fired automatically based on an event (insert, update, delete) Anonymous blocks Unnamed block of code that is compiled at run time

PL/SQL Block : 

PL/SQL Block DECLARE declaration of variables BEGIN executable statements EXCEPTION exception handler END end of program

PL/SQL Elements : 

PL/SQL Elements PL/SQL character set includes: Upper and lower case letters A-Z and a-z Digits 0-9 White spaces: tabs, spaces and carriage returns Mathematical symbols: + - / < > =

PL/SQL Elements (cont.) : 

PL/SQL Elements (cont.) Identifiers are used to name PL/SQL objects that include variables, cursors, subprograms etc. The maximum length for an identifier is upto 30 characters. Some examples are: Depositor_id, dep_age, Dep1 Reserved words are keywords and have special meaning viz. BEGIN, END. Comments -- This is a single line comment. /* this is a Multi line Comment. */

PL/SQL Elements (cont.) : 

PL/SQL Elements (cont.)

A Sample Program : 

DECLARE vempno emp.empno%TYPE := 7369; vcomm emp.comm%TYPE; ncomm NUMBER(6); BEGIN SELECT comm INTO vcomm FROM emp WHERE empno = vempno; IF (vcomm IS NULL) THEN ncomm := 100; ELSE ncomm := vcomm+50; END IF; UPDATE emp SET comm = ncomm WHERE empno = vempno; END; / A Sample Program

Exceptions : 

DECLARE vempno emp.empno%TYPE := 7369; vcomm emp.comm%TYPE; ncomm NUMBER(6); null_exep Exception; BEGIN SELECT comm INTO vcomm FROM emp WHERE empno = vempno; IF (vcomm IS NULL) THEN ncomm := 100; raise null_exep; ELSE ncomm := vcomm+50; END IF; UPDATE emp SET comm = ncomm WHERE empno = vempno; EXCEPTION when null_exep then dbms_output.put_line('Error comm. is null'); END; / Exceptions

Exceptions (cont.) : 

DECLARE vitno itemmast.itno%type := '&no'; vqoh itemmast.qoh%type; vrol itemmast.rol%type; rol_qoh Exception; qoh_rol Exception; diff number; BEGIN SELECT qoh,rol INTO vqoh,vrol FROM itemmast where itno = vitno; SELECT abs(vqoh-vrol) INTO diff FROM itemmast where itno = vitno; IF vqoh < vrol THEN raise rol_qoh; ELSE raise qoh_rol; END IF; EXCEPTION when rol_qoh THEN --Quantity less than rol dbms_output.put_line('QOH is less than ROL by '||diff); when qoh_rol THEN dbms_output.put_line(‘You still have'||diff||' in stock'); when no_data_found THEN dbms_output.put_line('No such item number exists. Please re-enter'); END; Exceptions (cont.)

Conditional Control : 

Conditional Control IF Statement IF <condition> THEN <actions> [ELSIF <condition> THEN] <actions> [ELSE] <actions> END IF;

What is a cursor? : 

What is a cursor? A cursor is a private SQL work area in memory in which the command is parsed & executed 2 types of cursors: Implicit & Explicit Implicit: When the executable part of a block issues a SQL statement, PL/SQL creates an implicit cursor PL/SQL manages this cursor automatically Explicit: Programmer declares, names, and manages a cursor Use to individually process each row returned by a multiple-row SELECT statement You can pass parameters to explicit cursors so that the query defining the cursors may contain variables Commonly used cursor attributes %FOUND %NOTFOUND %ISOPEN

Managing Explicit Cursors : 

Managing Explicit Cursors A programmer must: DECLARE the cursor: a cursor is defined by its query OPEN the cursor FETCH the data into variables or records CLOSE the cursor The OPEN, FETCH, and CLOSE can be done implicitly using a cursor FOR Loop FOR emp_record IN emp_cursor LOOP … END LOOP; *Don’t need to declare the record outside of the loop

Cursors: An Example : 

Cursors: An Example DECLARE vempno emp.empno%TYPE; vcomm emp.comm%TYPE; ncomm number(6); CURSOR emp_cur IS SELECT empno, comm FROM emp; BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO vempno, vcomm; EXIT WHEN emp_cur%NOTFOUND; ncomm :=100; UPDATE emp SET comm = ncomm WHERE empno = vempno; END LOOP; COMMIT; CLOSE emp_cur; END;

Cursor FOR Loop : 

Cursor FOR Loop Using a simple loop to process all records in cursor DECLARE CURSOR emp_cur IS ... ; emp_rec emp_cur%ROWTYPE; BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO emp_rec; EXIT WHEN emp_cur%NOTFOUND; give_raise (emp_rec.empno, 10000); END LOOP; CLOSE emp_cur; END; Using a cursor FOR loop to do the same: DECLARE CURSOR emp_cur IS ... ; emp_rec emp_cur%ROWTYPE; BEGIN FOR emp_rec IN emp_cur LOOP give_raise (emp_rec.empno, 10000); END LOOP; END; Note : Reduce the code size and redundant code

Subprogram in PL/SQL : 

Subprogram in PL/SQL Subprogram are named PL/SQL blocks that can be stored in the database and can be invoked anytime. Subprogram can take parameters. PL/SQL has two types of subprograms Procedures Functions Advantages of subprograms Extensibility Modularity Reusability and Maintainability Abstraction (to use a subprogram, the user must know what they do, not how they work)

Procedures : 

Procedures CREATE OR REPLACE PROCEDURE <proc_name>(arg1 [mode] datatype,….) IS local declaration BEGIN statements [EXCEPTION exception handler] END [procedure name];

Procedures: An Example : 

Procedures: An Example CREATE OR REPLACE PROCEDURE updt_sal (asal IN VARCHAR2, bsal IN VARCHAR2, csal IN VARCHAR2) IS A NUMBER; B NUMBER; C NUMBER; BEGIN A := 10; B := 11; C := 12; UPDATE emp SET sal = asal+(asal*A/100) WHERE job = 'CLERK'; UPDATE emp SET sal = bsal+(bsal*B/100) WHERE job = 'MANAGER'; UPDATE emp SET sal = csal+(csal*C/100) WHERE job = 'ANALYST'; END; /

Functions : 

Functions A function is a subprogram that computes a value. Functions and procedures are structured alike, except that functions have a RETURN clause. An example: CREATE or REPLACE FUNCTION QOH(vitno NUMBER) RETURN NUMBER IS vqoh itemmast.qoh%TYPE; BEGIN SELECT qoh INTO vqoh FROM itemmast WHERE itno = vitno; RETURN vqoh; END; /

A Cursor as a Function Argument : 

A Cursor as a Function Argument A Cursor as a Function Argument CREATE FUNCTION emp_fn (emp_cur SYS_REFCURSOR, mgr_hiredate DATE) RETURN NUMBER IS emp_hiredate DATE; before number :=0; after number:=0; BEGIN LOOP FETCH emp_cur INTO emp_hiredate; EXIT WHEN emp_cur%NOTFOUND; IF emp_hiredate > mgr_hiredate THEN after := after+1; ELSE before := before+1; END IF; END LOOP; CLOSE emp_cur; IF before > after THEN RETURN 1; ELSE RETURN 0; END IF; END; SELECT e1.last_name FROM employees e1 WHERE emp_fn(CURSOR (SELECT e2.hire_date FROM employees e2 WHERE e1.emp_id = e2.mgr_id), e1.hire_date) = 1;

What is a package? : 

What is a package? A package is a database object that groups logically related PL/SQL objects. Packages encapsulate related procedures, functions, associated cursors and variables together as a logical unit in the database. A package can contain global variables/constants/ exceptions that apply to all procedures in the package Advantages of packages include: Modularity Easier application design Information hiding Added functionality

What makes up a package? : 

What makes up a package? A package has 2 components: Specification & Body Package specification: Acts like the API for the body, containing only the definition of each procedure/function held in the package Must be compiled before the body Package body: Contains the actual code of the procedures & functions To refer to any procedures in a package, you must call them through the package body

Packages - Syntax : 

Packages - Syntax Package specification/header syntax: CREATE [or replace] PACKAGE <package_name> IS declaration of global variables and cursors, procedures, functions END; Package body syntax: CREATE [or REPLACE] PACKAGE BODY <package_name> IS|AS private type object declaration BEGIN declaration of objects or subprogram body END;

Package Specification: An Example : 

Package Specification: An Example CREATE OR REPLACE PACKAGE pack1 AS PROCEDURE updt_sal (asal IN VARCHAR2, bsal IN VARCHAR2, csal IN VARCHAR2); FUNCTION QOH(vitno NUMBER) RETURN NUMBER; END;

Package Body: An Example : 

Package Body: An Example CREATE OR REPLACE PACKAGE BODY pack1 AS PROCEDURE updt_sal (asal IN VARCHAR2, bsal IN VARCHAR2, csal IN VARCHAR2) IS A NUMBER; B NUMBER; C NUMBER; BEGIN A := 10; B := 11; C := 12; UPDATE emp SET sal = asal+(asal*A/100) WHERE job = 'CLERK'; UPDATE emp SET sal = bsal+(bsal*B/100) WHERE job = 'MANAGER'; UPDATE emp SET sal = csal+(csal*C/100) WHERE job = 'ANALYST'; END; FUNCTION QOH(vitno number) RETURN number IS Vqoh itemmast.qoh%TYPE; BEGIN SELECT qoh INTO vqoh FROM itemmast WHERE itno = vitno; RETURN vqoh; END; END;

Constants Package : 

Constants Package CREATE OR REPLACE PACKAGE constant_config IS max_blend_mode CONSTANT CHAR(3) := 'MAX'; min_blend_mode CONSTANT CHAR(3) := 'MIN'; avg_blend_mode CONSTANT CHAR(3) := 'AVG'; transaction_accepted CONSTANT NUMERIC(5)=100; transaction_rejected CONSTANT NUMERIC(5)=101; booking_accepted CONSTANT NUMERIC(5)=200; booking_rejected CONSTANT NUMERIC(5)=201; min_batch_count CONSTANT NUMBER := 1; max_batch_count CONSTANT NUMBER := 100; system_date CONSTANT DATE := SYSDATE; END constant_config;

Named Constants : 

Named Constants Lets not use explicit literals: IF batch_count BETWEEN 1 and 100 THEN accept_transaction_batch; END IF; ********************************* IF trn_status = 100 THEN manual_book_trade(txn_cur, 100) END IF; Let's use named constants: IF batch_count BETWEEN min_batch_count AND max_batch_count THEN accept_transaction_batch; END IF; ************************************ IF trn_status = tran_accepted THEN manual_book_trade (txn_cur,booking_accepted) END IF;

Use Anchored Declaration : 

Use Anchored Declaration Every time you declare using an explicit data-type, you hard-code that data structure Use %TYPE attribute to anchor your variable to an existing variable or database element %TYPE for scalar structures Examples of anchored declarations ename employee.ename%TYPE; total_sales sales_amt%TYPE; Examples of hard-coded declarations v_ename VARCHAR2(30); total_sales NUMBER (10,2);

Debugging : 

Debugging Showing compilation errors SHOW ERRORS { FUNCTION | PACKAGE | PROCEDURE } [ <schema> . ] <name> This will show the current compilation errors for the object specified The default is to just show the errors for the last object compiled Writing out to the console using DBMS_OUTPUT SET serveroutput on; DBMS_OUTPUT.enable; DBMS_OUTPUT.put_line(‘I am writing to the console.’); Writing out to a log file using UTL_FILE fileHandle = UTL_FILE.FOPEN(‘\usr\files’,’log.txt’,w,1000); UTL_FILE.PUT_LINE(fileHandle,‘Writing a line to the file’); UTL_FILE.FCLOSE(fileHandle);

Further Reading : 

Further Reading Oracle9i Application Developer's Guide - Fundamentals Release 2 (9.2)http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/toc.htm Oracle9i SQL Reference Release 2 (9.2)http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/toc.htm PL/SQL User's Guide and Reference Release 2 (9.2)http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/toc.htm Oracle9i Supplied PL/SQL Packages and Types Reference Release 2 (9.2)http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/toc.htm SQL*Plus User's Guide and Reference Release 9.2http://download-west.oracle.com/docs/cd/B10501_01/server.920/a90842/toc.htm Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2)http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/toc.htm You’ll need to get a user id but it’s free

Questions? : 

Questions?