PL/SQL Introduction PDF

Database Programming with PL/SQL 1-1 Introduction to PL/SQL Copyright © 2016, Oracle and/or its affiliates. All rights reserved. Objectives This lesson covers the following objectives: Describe PL/SQL Differentiate between SQL and PL/SQL Explain the need for PL/SQL...

Database Programming with PL/SQL 1-1 Introduction to PL/SQL Copyright © 2016, Oracle and/or its affiliates. All rights reserved. Objectives This lesson covers the following objectives: Describe PL/SQL Differentiate between SQL and PL/SQL Explain the need for PL/SQL PLSQL S1L1 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 3 Introduction to PL/SQL Purpose PL/SQL is Oracle Corporation’s procedural programming language for relational databases. To describe PL/SQL, you learn its characteristics and identify the differences between PL/SQL and SQL. Identifying limitations of SQL and how PL/SQL addresses those limitations will help you to understand why PL/SQL is needed. PLSQL S1L1 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 4 Introduction to PL/SQL PL/SQL Description Procedural Language extension to SQL. A third-generation programming language (3GL). An Oracle proprietary programming language. Combines program logic and control flow with SQL. PLSQL S1L1 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 5 Introduction to PL/SQL SQL Description A nonprocedural language. – Also known as a "declarative language," allows programmer to focus on input and output rather than the program steps. A fourth-generation programming language (4GL). Primary language used to access and modify data in relational databases. Standardized by the American National Standards Institute (ANSI). Vendors such as Oracle typically include some proprietary SQL features in their database environments. PLSQL S1L1 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 6 Introduction to PL/SQL SQL Statement The SQL statement shown is simple and straightforward. However, if you need to modify a data item in a conditional manner, you come across a limitation of SQL. SELECT employee_id, job_id, hire_date FROM employees; For example, how would you write an SQL statement to update the job_id data with a new value determined by the current job_id and the hire_date? PLSQL S1L1 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 7 Introduction to PL/SQL Limitations of SQL Assume the company decides to promote all sales representatives, marketing representatives, and stock clerks employed for at least ten years to senior representatives and clerks. If the current date is 05-Feb-2015, sales representatives 174, 176, and 178 qualify for the promotion. EMPLOYEE_ID JOB_ID HIRE_DATE “NEW” JOB_ID 174 SA_REP 11-May-1996 SR_SA_REP 176 SA_REP 24-Mar-1998 SR_SA_REP 178 SA_REP 24-May-1999 SR_SA_REP 240 SA_REP 02-Oct-2005 242 SA_REP 09-Dec-2007 PLSQL S1L1 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 8 Introduction to PL/SQL Limitations of SQL If the current date is 05-FEB-2015, stock clerks 141, 142, 143, and 144 also qualify for the promotion. EMPLOYEE_ID JOB_ID HIRE_DATE “NEW” JOB_ID 141 ST_CLERK 17-Oct-1995 SR_ST_CLERK 142 ST_CLERK 29-Jan-1997 SR_ST_CLERK 143 ST_CLERK 15-Mar-1998 SR_ST_CLERK 144 ST_CLERK 09-Jul-1998 SR_ST_CLERK 244 ST_CLERK 07-Sep-2009 PLSQL S1L1 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. 9 Introduction to PL/SQL Limitations of SQL One solution to updating the job_id data is shown. How many SQL statements do you need to write for sales representatives, marketing representatives, and stock clerks? What if there are other job_ids to update? UPDATE employees SET job_id = 'SR_SA_REP' WHERE job_id = 'SA_REP' AND hire_date