Pl/Sql

In: Computers and Technology

Submitted By kokouzoo
Words 920
Pages 4
PL/SQL Chapter 3 Solutions

Review Questions
1. C
2. D
3. B
4. A
5. C
6. C
7. A, C
8. A
9. A
10. A

11. Variables are named memory areas that hold values to allow retrieval and manipulation of values within our programs

12. A SELECT statement within a PL/SQL block must include an INTO clause to indicate the variable/s that will hold the data being retrieved. The INTO clause follows the SELECT clause and precedes the FROM clause. In addition, a SELECT statement that returns no rows will raise an Oracle error.

13. The %TYPE attribute is used to declare the data type of a variable based on the data type of a database table column. The data type declaration must include the table and column name in the form: tablename.columnname%TYPE.

14. The %ROWTYPE attribute is used to declare the data type of a record variable based on the row structure of a table. The data type declaration must include the table name in the form: tablename%ROWTYPE.

15. Composite data types allow the creation of a variable that can hold multiple values with various data types as a single unit. Examples include a record and a table of records.

Advanced Review Questions
1. B
2. C
3. D
4. A
5. B

Hands-On Assignments

Assignment 3-1
VARIABLE g_basket NUMBER
BEGIN
:g_basket := 3;
END;
/
DECLARE
lv_ship_date bb_basketstatus.dtstage%TYPE; lv_shipper_txt bb_basketstatus.shipper%TYPE; lv_ship_num bb_basketstatus.shippingnum%TYPE;
BEGIN
SELECT dtstage, shipper, shippingnum INTO lv_ship_date, lv_shipper_txt, lv_ship_num FROM bb_basketstatus WHERE idbasket = :g_basket AND idstage = 5; DBMS_OUTPUT.PUT_LINE('Date Shipped: '||lv_ship_date); DBMS_OUTPUT.PUT_LINE('Shipper: '||lv_shipper_txt); DBMS_OUTPUT.PUT_LINE('Shipping #: '||lv_ship_num);
END;
/
BEGIN
:g_basket := 7;
END;
/

Assignment 3-2
VARIABLE…...

Similar Documents

Sql Performance Analyzer

...SQL Performance Analyzer in Oracle Database 11g Release 1 The concept of SQL tuning sets, along with the DBMS_SQLTUNE package to manipulate them, was introduced in Oracle 10g as part of the Automatic SQL Tuning functionality. Oracle 11g makes further use of SQL tuning sets with the SQL Performance Analyzer, which compares the performance of the statements in a tuning set before and after a database change. The database change can be as major or minor as you like, such as: •Database, operating system, or hardware upgrades. •Database, operating system, or hardware configuration changes. •Database initialization parameter changes. •Schema changes, such as adding indexes or materialized views. •Refreshing optimizer statistics. •Creating or changing SQL profiles. Unlike Database Replay, the SQL Performance Analyzer does not try and replicate the workload on the system. It just plugs through each statement gathering performance statistics. The SQL Performance Analyzer can be run manually using the DBMS_SQLPA package or using Enterprise Manager. This article gives an overview of both methods. •Setting Up the Test •Creating SQL Tuning Sets using the DBMS_SQLTUNE Package •Running the SQL Performance Analyzer using the DBMS_SQLPA Package •Creating SQL Tuning Sets using Enterprise Manager •Running the SQL Performance Analyzer using Enterprise Manager •Optimizer Upgrade Simulation •Parameter Change •Transferring SQL Tuning Sets Setting Up the Test The SQL......

Words: 1591 - Pages: 7

Oracle Sql Tutorial

...Oracle/SQL Tutorial1 Michael Gertz Database and Information Systems Group Department of Computer Science University of California, Davis gertz@cs.ucdavis.edu http://www.db.cs.ucdavis.edu This Oracle/SQL tutorial provides a detailed introduction to the SQL query language and the Oracle Relational Database Management System. Further information about Oracle and SQL can be found on the web site www.db.cs.ucdavis.edu/dbs. Comments, corrections, or additions to these notes are welcome. Many thanks to Christina Chung for comments on the previous version. Recommended Literature George Koch and Kevin Loney: Oracle8 The Complete Reference (The Single Most Comprehensive Sourcebook for Oracle Server, Includes CD with electronic version of the book), 1299 pages, McGraw-Hill/Osborne, 1997. Michael Abbey and Michael Corey: Oracle8 : A Beginner’s Guide [A Thorough Introduction for First-time Users], 767 pages, McGraw-Hill/Osborne, 1997. Steven Feuerstein, Bill Pribyl, Debby Russell: Oracle PL/SQL Programming (2nd Edition), O’Reilly & Associates, 1028 pages, 1997. C.J. Date and Hugh Darwen: A Guide to the SQL Standard (4th Edition), Addison-Wesley, 1997. Jim Melton and Alan R. Simon: Understanding the New SQL: A Complete Guide (2nd Edition, Dec 2000), The Morgan Kaufmann Series in Data Management Systems, 2000. 1 revised Version 1.01, January 2000, Michael Gertz, Copyright 2000. Contents 1. SQL – Structured Query Language 1.1. Tables 1.2. Queries (Part I) 1.3. Data......

Words: 21631 - Pages: 87

Cursors in Pl/Sql

...SQL Cursor  A SQL cursor is a private Oracle SQL working area. There are two types of SQL cursor: implicit or explicit cursor. The implicit cursor is used by Oracle server to test and parse the SQL statements and the explicit cursors are declared by the programmers. Using the implicit cursor, we can test the outcome of SQL statements in PL/SQL. For example, • SQL%ROWCOUNT, return the number of rows affected; • SQL%FOUND, a BOOLEAN attribute indicating whether the recent SQL statement matches to any row; • SQL%NOTFOUND, a BOOLEAN attribute indicating whether the recent SQL statement does not match to any row; • SQL%ISOPEN, a BOOLEAN attribute and always evaluated as FALSE immediately after the SQL statement is executed. To write the explicit cursor,  please refer to the following example. Note that a cursor definition can array a number of arguments. For example,         DECLARE CURSOR csr_ac (p_name VARCHAR2) IS SELECT empno, name, sal FROM employee WHERE name LIKE '%p_name%'; BEGIN FOR rec_ac IN csr_ac ('LE') LOOP    DBMS_OUTPUT.PUT_LINE(rec_ac.empno || ' ' ||rec_ac.name || ' '||v_sal);  END LOOP ; CLOSE csr_ac; END; / Another way of writing the above code, is to use the basic loop and the SQL%NOTFOUND cursor, as shown in the following. SQL> DECLARE 2 CURSOR csr_ac (p_name VARCHAR2) IS 3 SELECT empno, ename, sal 4 FROM emp 5 6 WHERE ename LIKE '%SMITH%'; 7 8 v_a......

Words: 2868 - Pages: 12

Sql for Beginners

...An Training Guide SQL & SQL*Plus for Beginners Training Guide SQL & SQL*Plus for Beginners www.appltop.com info@appltop.com Course Introduction Copyright © 1999 – 2004 ApplTop Solutions Limited 2 Training Guide SQL & SQL*Plus for Beginners www.appltop.com info@appltop.com Course Objectives This course is designed to give each delegate a basic understanding/awareness of the following... • Oracle SQL • Oracle SQL*Plus Copyright © 1999 – 2004 ApplTop Solutions Limited 3 Training Guide SQL & SQL*Plus for Beginners www.appltop.com info@appltop.com Course Objectives This course is designed to give each delegate a basic understanding of the following topics... • Oracle SQL. You will learn how to use the industry standard tool for working with a Relational Database. You will also learn many other things, from constructing simple queries to creating your own tables. • Oracle SQL*Plus. After the course you should have a good understanding of SQL*Plus, from starting it, to creating simple reports on it. During the course there will be several exercises to complete. Copyright © 1999 – 2004 ApplTop Solutions Limited 4 Training Guide SQL & SQL*Plus for Beginners www.appltop.com info@appltop.com Note that this guide is meant only as an introduction to SQL and SQL*Plus and therefore much of the newer, more advanced features available in Oracle databases 8i, 9i and 10g are not covered. Copyright © 1999 – 2004 ApplTop......

Words: 19188 - Pages: 77

Database - Sql

...referential integrity rules could specify that whenever you modify the value of a linked field in Table A, all records in Table B that are linked to it will also be modified accordingly. 24. What is SQL? * The language that is most commonly used in relational database to define database odjects (DDL) and to manipulate data (DML) 25. What is the difference between the data definition and data manipulation portion of SQL? * Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples: * CREATE - to create objects in the database * ALTER - alters the structure of the database * DROP - delete objects from the database * TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed * COMMENT - add comments to the data dictionary * RENAME - rename an object * Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples: * SELECT - retrieve data from the a database * INSERT - insert data into a table * UPDATE - updates existing data within a table * DELETE - deletes all records from a table, the space for the records remain * MERGE - UPSERT operation (insert or update) * CALL - call a PL/SQL or Java subprogram * EXPLAIN PLAN - explain access path to data * LOCK TABLE - control concurrency 26. What are data types? * The column specification that......

Words: 2656 - Pages: 11

Teach Yourself Sql

...Teach Yourself SQL in 21 Days, Second Edition Table of Contents: Introduction Week 1 at a Glance Day 1 Introduction to SQL Day 2 Introduction to the Query: The SELECT Statement Day 3 Expressions, Conditions, and Operators Day 4 Functions: Molding the Data You Retrieve Day 5 Clauses in SQL Day 6 Joining Tables Day 7 Subqueries: The Embedded SELECT Statement Week 1 in Review Week 2 at a Glance Day 8 Manipulating Data Day 9 Creating and Maintaining Tables Day 10 Creating Views and Indexes Day 11 Controlling Transactions Day 12 Database Security Day 13 Advanced SQL Topics Day 14 Dynamic Uses of SQL Week 2 in Review Week 3 at a Glance Day 15 Streamlining SQL Statements for Improved Performance Day 16 Using Views to Retrieve Useful Information from the Data Dictionary Day 17 Using SQL to Generate SQL Statements Day 18 PL/SQL: An Introduction Day 19 Transact-SQL: An Introduction Day 20 SQL*Plus Day 21 Common SQL Mistakes/Errors and Resolutions Week 3 in Review Appendixes A Glossary of Common SQL Statements B Source Code Listings for the C++ Program Used on Day 14 C Source Code Listings for the Delphi Program Used on Day 14 D Resources E ASCII Table F Answers to Quizzes and Excercises © Copyright, Macmillan Computer Publishing. All rights reserved. Teach Yourself SQL in 21 Days, Second Edition Acknowledgments A special thanks to the following individuals: foremost to my loving wife, Tina, for her tolerance and endless support, to Dan Wilson for his...

Words: 128515 - Pages: 515

Sql Homework

...Basic Books 3350 Group: Six People in Search of a Life BP Berkley Publishing 669X A Guide to SQL CT Course Technology 2908 Electric Light FS Farrar Straus and Giroux 0189 Magic Terror FA Fawcett Books 9931 To Kill a Mockingbird HC HarperCollins Publishers 9882 Slay Ride JP Jove Publications 8720 When Rabbit Howls JP Jove Publications 0808 The Edge JP Jove Publications 3743 Nine Stories LB Lb Books 6908 Franny and Zooey LB Lb Books 9883 The Catcher in the Rye LB Lb Books 9701 The Grapes of Wrath PE Penguin USA 7405 East of Eden PE Penguin USA 2766 Of Mice and Men PE Penguin USA 5163 Travels with Charley PE Penguin USA 6128 Jazz PL Plume 138X Beloved PL Plume 9627 Song of Solomon PL Plume 079X Second Wind PU Putnam Publishing Group 9611 Black......

Words: 408 - Pages: 2

Sql Tutorial

...SQL Tutorial SQL TUTORIAL Simply Easy Learning by tutorialspoint.com tutorialspoint.com i ABOUT THE TUTORIAL SQL Tutorial SQL is a database computer language designed for the retrieval and management of data in relational database. SQL stands for Structured Query Language. This tutorial will give you quick start with SQL. Audience This reference has been prepared for the beginners to help them understand the basic to advanced concepts related to SQL languages. Prerequisites Before you start doing practice with various types of examples given in this reference, I'm making an assumption that you are already aware about what is database, especially RDBMS and what is a computer programming language. Copyright & Disclaimer Notice All the content and graphics on this tutorial are the property of tutorialspoint.com. Any content from tutorialspoint.com or this tutorial may not be redistributed or reproduced in any way, shape, or form without the written permission of tutorialspoint.com. Failure to do so is a violation of copyright laws. This tutorial may contain inaccuracies or errors and tutorialspoint provides no guarantee regarding the accuracy of the site or its contents including this tutorial. If you discover that the tutorialspoint.com site or this tutorial content contains some errors, please contact us at webmaster@tutorialspoint.com TUTORIALS POINT Simply Easy Learning Table of Content SQL Tutorial .................................................................

Words: 39505 - Pages: 159

Assignment Week 1 Sql

...1. SQL (Structured Query Language) is a special-purpose programming language designed for managing data in a relational database management system (RDBMS). The scope of SQL includes data insert, query, update and delete, schema creation and modification, and data access control. The SQL standard has gone through a number of revisions: SQL-86, SQL-89, SQL-92, SQL: 1999, SQL: 2003, SQL: 2006, SQL: 2008, SQL: 2011. A few things I was able to find was language enhancements for temporal data definition and manipulation, time Period definitions, temporal primary keys, temporal referential integrity, Syntax for time-sliced and sequenced queries on system time tables. 2. Features for Microsoft SQL Server Express supports most of the features and functionality of SQL Server. The following list some of the major features and components that are supported. Stored Procedures, SQL Server Configuration Manager, Views, Replication (as a subscriber only), Triggers, Advanced Query Optimizer, Cursors, SMO/RMO, sqlcmd and osql utilities, Integration with Visual Studio 2005, Snapshot Isolation Levels, Service Broker (as a client only), Native XML support, including XQuery and XML Schemas, SQL CLR, Transact-SQL language support, Multiple Active Result Sets (MARS), Dedicated Administrator Connection, Import/Export Wizard. Although it does not come without its limitations like Maximum memory utilized by SQL Server Database Engine is 1GB, Maximum size of the each relational database is 10 GB, No......

Words: 869 - Pages: 4

Sql Book

... A GUIDE TO SQL Eighth Edition This page intentionally left blank A G U I D E TO S Q L Eighth Edition Philip J. Pratt Grand Valley State University Mary Z. Last University of Mary Hardin-Baylor Australia • Brazil • Japan • Korea • Mexico • Singapore • Spain • United Kingdom • United States A Guide to SQL, Eighth Edition Philip J. Pratt, Mary Z. Last Vice President, Publisher: Jack Calhoun Editor-in-Chief: Alex von Rosenberg Senior Acquisitions Editor: Charles McCormick, Jr. Product Manager: Kate Hennessy Development Editor: Jessica Evans Editorial Assistant: Bryn Lathrop Marketing Director: Brian Joyner Marketing Manager: Bryant Chrzan Marketing Communications Manager: Libby Shipp Marketing Coordinator: Suellen Ruttkay Content Project Manager: Matt Hutchinson Art Director: Stacy Jenkins Shirley, Marissa Falco Cover Designer: Joseph Sherman Cover Image: Getty Images/Taxi/Chris Bell Manufacturing Coordinator: Denise Powers © 2009 Course Technology, Cengage Learning ALL RIGHTS RESERVED. No part of this work covered by the copyright hereon may be reproduced, transmitted, stored, or used in any form or by any means graphic, electronic, or mechanical, including but not limited to photocopying, recording, scanning, digitizing, taping, Web distribution, information networks, or information storage and retrieval systems, except as permitted under Section 107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the publisher...

Words: 48772 - Pages: 196

Physik Pl

...Regeln dafür zu entwickeln. So sollten die Schülerinnen und Schüler ihre Portfolios mindestens am Ende des 2. Semesters und rechtzeitig vor Beginn der Abiturprüfungen innerhalb des 4. Semesters in dem Fach vorlegen, in dem die Grundlagen für die Präsentationsleistung geübt wurden. Sie zeigen das Ergebnis des Lernens; wenn die Aufgabe für die Präsentationsprüfung noch nicht gestellt wurde, kann an dieser Stelle noch beraten werden. Peer-Beratung Feedback des Lehrers Auswertung der Beratungen Mitschriften, Anregungen, Tipps zu Vortrag und Medium, Auseinandersetzung mit Bewertungskriterien Beratung Sammeln Präsentationsportfolio Reflexion der Bewertungsmaßstäbe, des Ablaufs der PL, der weiteren Lernschritte Dokumentation des eigenen Lernprozesses Erarbeitungsschritte eigener PL, Chronologie des Lernens, Überlegungen zur Strukturierung, Visualisierungsideen 14 Die Planung der Präsentationsprüfungen im Abitur 4. Die Planung der Präsentationsprüfungen im Abitur Für die Durchführung der Präsentationsprüfungen im Abitur gibt es noch normativen Regelungsbedarf. Das LI stellt im Herbst 2010 Beispielaufgaben für alle Fächer zur Verfügung. Da die Schülerinnen und Schüler bereits zu Beginn des 3. Semesters die Prüfungsfächer wählen, müssen Anfang des 3. Semesters schul- und fachschaftsintern organisatorische Festlegungen getroffen werden. Die folgenden Ausführungen beschränken sich allein auf diesen Aspekt. 4.2. Der Ablaufplan für die Präsentationsprüfungen......

Words: 14047 - Pages: 57

Sql Fundamentals

...SQL Fundamentals The Structured Query Language (SQL) comprises one of the fundamental building blocks of modern database architecture. SQL defines the methods used to create and manipulate relational databases on all major platforms. At first glance, the language may seem intimidating and complex but it's really not all that bad. In a series of articles over the next few weeks we'll explore the inner workings of SQL together. By the time we're through, you'll have the fundamental knowledge you need to go out there and start working with databases! This week, our first article in the SQL series provides an introduction to the basic concepts behind SQL and we'll take a brief look at some of the main commands used to create and modify databases. Throughout this article, please keep our goal in mind: we're trying to get the "big picture" of SQL -- not a mastery of the individual commands. We'll provide a few examples for illustrative purposes and explain the theory behind them, but don't feel frustrated if you can't write your own SQL commands after reading this article. We'll cover each of the major commands in detail in future weekly installments. If you'd like a reminder in your e-mail inbox each week when the next article is posted, please take a moment and subscribe to our newsletter. By the way, the correct pronunciation of SQL is a contentious issue within the database community. In their SQL standard, the American National Standards Institute declared that the......

Words: 1859 - Pages: 8

Sql Lab 4

...MET CS 669 Database Design and Implementation for Business SQL Lab 4 Instructions: Procedural......

Words: 6758 - Pages: 28

Sql Injection.

...due to a SQL injection flaw in a web application that communicate with a database. Over ten years have passed since a famous hacker coined the term “SQL injection” and it is still considered one of the major application threats. A lot has been said on this vulnerability, but not all of the aspects and implications have been uncovered, yet. This paper aim is to collate some of the existing knowledge, introduce new techniques and demonstrate how to get complete control over the database management system's underlying operating system, file system and internal network through SQL injection vulnerability in over-looked and theoretically not exploitable scenarios. This paper also discuss about the prevention from the SQL Injection, not only in ORACLE but also in PHP, C#, JAVA and other languages. INDEX ABSTRACT………………………………………………………………………………….....02 INTRODUCTION……………….…………………………….…….………………………….04 BLIND SQL INJECTION…………………………………….………………………………..05 SQL INJECTION OVERVIEW…………………………….………………………………....06 CATEGORIES OF SQL INJECTION ATTACKS…………………………………………..07 WHAT’S VULNERABLE…………………………………………………………..…………08 WHAT’S NOT VULNERABLE…………………………………………………….………….08 SQL INJECTION METHODS……………………………………….……………….……….09 SQL MANIPULATION………………………………………………………..……………….09 CODE INJECTION……………………………………………………….……………………10 FUNCTION CALL INJECTION……………………………………………………………….11 BUFFER OVERFLOWS………………………………………………………………………13 SQL INJECTION TESTING METHODOLOGY………………………………………….....14 PREVENTING SQL......

Words: 3449 - Pages: 14

Sql Plus

...Introduction to Oracle9i: SQL Instructor Guide • Volume 1 40049GC11 Production 1.1 October 2001 D33993 Authors Copyright © Oracle Corporation, 2000, 2001. All rights reserved. Nancy Greenberg Priya Nathan This documentation contains proprietary information of Oracle Corporation. It is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited. If this documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable: Technical Contributors and Reviewers Josephine Turner Martin Alvarez Anna Atkinson Don Bates Marco Berbeek Andrew Brannigan Restricted Rights Legend Use, duplication or disclosure by the Government is subject to restrictions for commercial computer software and shall be deemed to be Restricted Rights software under Federal law, as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988). This material or any portion of it may not be copied in any form or by any means without the express prior written permission of Oracle Corporation. Any other copying is a violation of copyright law and may result in civil and/or criminal penalties. Laszlo Czinkoczki Michael Gerlach Sharon Gray Rosita Hanoman Mozhe Jalali Sarah Jones Charbel......

Words: 33197 - Pages: 133