Premium Essay

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

Premium Essay

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

Free Essay

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

Premium Essay

Deep'Z Studio.

...INTRODUCTION SQL is divided into the following  Data Definition Language (DDL)  Data Manipulation Language (DML)  Data Retrieval Language (DRL)  Transaction Control Language (TCL)  Data Control Language (DCL) DDL -- create, alter, drop, truncate, rename DML -- insert, update, delete DRL -- select TCL -- commit, rollback, savepoint DCL -- grant, revoke CREATE TABLE SYNTAX Create table (col1 datatype1, col2 datatype2 …coln datatypen); Ex: SQL> create table student (no number (2), name varchar (10), marks number (3)); INSERT This will be used to insert the records into table. We have two methods to insert.   a) By value method By address method USING VALUE METHOD Syntax: insert into (table_name) values (value1, value2, value3 …. Valuen); © Copy rights are reserved. 2 Ex: SQL> insert into student values (1, ’sudha’, 100); SQL> insert into student values (2, ’saketh’, 200); To insert a new record again you have to type entire insert command, if there are lot of records this will be difficult. This will be avoided by using address method. b) USING ADDRESS METHOD Syntax: insert into (table_name) values (&col1, &col2, &col3 …. &coln); This will prompt you for the values but for every insert you have to use forward slash. Ex: SQL> insert into student values (&no, '&name', &marks); Enter value for no: 1 Enter value for name: Jagan Enter value for marks:......

Words: 42387 - Pages: 170

Premium Essay

Cscc 640

...executes a dynamic SQL statement or an anonymous PL/SQL block. ____ 2. The %TYPE feature allows you to anchor variable declarations to a column in a table. ____ 3. COMMIT and ROLLBACK end the active autonomous transaction but do not exit the autonomous routine. ____ 4. The only required sections of a PL/SQL block are BEGIN and END. ____ 5. The EXCEPTION handling section is an optional section in a PL/SQL block. ____ 6. The keyword DEFAULT can be used in place of the := symbol to assign initial values to the variables within the declaration statement. ____ 7. Declaring a table of records variable in a package specification allows it to persist for a user session. ____ 8. Implicit cursors are declared automatically for all INSERT, UPDATE, and DELETE statements issued within a PL/SQL block. ____ 9. You can process multiple rows of data from a database by creating explicit cursors. ____ 10. With an implicit cursor, Oracle closes the SQL cursor automatically after executing its associated SQL statement. As a result, %ISOPEN always yields FALSE. ____ 11. The basic loop uses the LOOP and END LOOP markers to begin and end the loop code. ____ 12. Dynamic SQL lets you write schema-management procedures that can be centralized in one schema, and can be called from other schemas and operate on the objects in those schemas. ____ 13. In PL/SQL, if a SELECT ... INTO statement returns no rows, Oracle does not return an error. ____ 14. In PL/SQL, a......

Words: 1344 - Pages: 6

Free Essay

Computers

...Oracle9i: Program with PL/SQL Instructor Guide • Volume 2 40054GC11 Production 1.1 October 2001 D34008 Authors Nagavalli Pataballa Priya Nathan Copyright © Oracle Corporation, 1999, 2000, 2001. All rights reserved. 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: 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. If this documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with “Restricted Rights,” as defined in FAR 52.227-14, Rights in Data-General, including Alternate III (June 1987). The information in this...

Words: 41259 - Pages: 166

Premium Essay

Airline

...Project Report On Session (2004-2005) For the partial fulfillment of requirement of degree of Bachelor of Science in Information Technology (B.Sc – IT). Submitted By: Submitted To: Ms. Sandeep Bassi Lyallpur Khalsa college, Jalandhar City. (Affiliated To Guru Nanak Dev University) Tables of Contents ❖ Acknowledgement 4 ❖ Bonafide Certificate 5 ❖ Introduction 6 o Purpose o Design Constraints o Scope Of Project o Problem Definition ❖ Software Process 10 o Objective o Model Of Project ❖ Software Requirement Specification 12 o System Analysis o Functional Requirements o Performance Requirement o Hardware & Software Requirements ❖ Software Interface 23 ❖ Project Scheduling 46 o Pert Chart o Gantt Chart ❖ System Design 49 ...

Words: 9647 - Pages: 39

Premium Essay

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

Premium Essay

Paper

...Introduction to Oracle9i: SQL Instructor Guide • Volume 1 40049GC11 Production 1.1 October 2001 D33993 Authors Nancy Greenberg Priya Nathan Copyright © Oracle Corporation, 2000, 2001. All rights reserved. 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: 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. If this documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with “Restricted Rights,” as defined in FAR 52.227-14, Rights in Data-General, including Alternate III (June 1987). The information in this......

Words: 33172 - Pages: 133

Premium Essay

Hostel Management

...1.INTRODUCTION 1.1 PURPOSE: The purpose of this document is to describe the requirements for an automation system for a college grievance cell to register the complaints online from valid users and maintain all related information. It also describes the interface, platform and other constraints. 1.2 SCOPE: This document is the only one that describes the requirements of the system. It is meant for the use by the developers and will be the basis for validating the final delivered system. Any change made to the requirements in the future will have to go through a formal approval process. The developer is responsible for asking clarification, where necessary, and will not make any alteration without the permission of the client. 1.3 DEFINITIONS AND ABBREVIATIONS: 1. OGC- Online Grievance Cell 2. Grievance-A grievance is the subject of a complaint filed by a student, which is to be resolved by procedures provided in the college. It is any issue causing problem to the students, which need to be relevant. Ex. Issues related to ragging, food, faculty etc. Grievance is sometimes referred to as complaint. 3. Student- Any student of SDMCET belonging to any of the semesters from 1st to 8th of any branch. 4. Authority- The Dean academic for academic and Dean Student welfare for non academic grievances, who are responsible for resolving the grievances of students. 5. Database Administrator- One who manages and maintains the database, i.e adding/ deleting of......

Words: 5166 - Pages: 21

Premium Essay

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

Premium Essay

Csc 352 / 452: Database Programming Assignment #3

...g., DOC, DOCX, RTF, PDF, JPG, or ZIP) will be rejected. In D2L, only the most recent submission is kept. • If you modified the DEPARTMENT and EMPLOYEE tables created in Assignment #1, you need to delete and re-populate them. • Do not try to use complicated queries/cursors (e.g., joins) to get the results. You can use multiple PL/SQL statements to get the results easily. • You cannot use hard-coded values (e.g., IF department_name = 'MARKETING' THEN ……) in your programs. • You are not allowed to create temporary tables, views, functions, or procedures. • The EXCEPTION section is NOT allowed in your programs. • Please review your assignment file before submitting it to make sure you have the correct one. It is your responsibility to ensure that you upload the correct assignment file. 1) (CSC 352 – 25 Points | CSC 452 – 15 Points) The table popular_names consists of some popular given names for male and female babies born during the years 1915-2014. Rank 1 is the most popular, rank 2 is the next most popular, and so forth. 2) (CSC 352 – 35 Points | CSC 452 – 20 Points) Based on the tables created in Assignment #1, write a PL/SQL anonymous block to perform the following tasks: • Display the number of employees in the company. • Display the maximum/minimum/average salary for the company. • Display 50% of the maximum salary for the company. • For each department whose average salary is greater than 50% of the maximum salary for the company, display the...

Words: 334 - Pages: 2

Premium Essay

Saverr Rupee

...Knowledge of Installation, Configuration and maintenance of Oracle 10g and 11g Databases. * Configuring Physical Standby database and its maintenance.Database Upgradation from 10g to 11g. * Analyzing database performance using sql trace and TKPROF. Good in Export and Import for taking logical backup & refresh activities. * Configuration of Flash Recovery Area, Flashback Database and Block Change Tracking for Target Databases. * Creation and maintenance of database links and materialized views. * Index creation and reorganization. * Moderate understanding MSSQL & SYBASE. Mainly User-management, backup & restore activities. * Using AWR, ADDM, ASH report and other Oracle utilities for Proactive Oracle Tuning. * Good in Database Cloning (Cold/Hot ) * Good in User Management and User security. * Database backup and restoration activities. * Good in Business Communication. * Good in Business Writing, Coordination. * Ability to exhibit good team spirit. * Ability to complete assigned work on time. Technical Skills Language | SQL, PL/SQL,Shell Scripting. | Database | Oracle 10g,11g, MSSQL,SYBASE. | Operating System | Linux(RHEL 4,RHEL 5). | Oracle Tools | SQL*PLUS,RMAN,tkprof. | Work Experience Organization: Cambridge Technology India Pvt. Ltd. Duration: Dec 2009 to Till Date . Role: Oracle DBA. Project Details: Client Profile | Societe Generale is a France......

Words: 532 - Pages: 3

Premium Essay

Db Iv - Oracle 11g V2 - Final Examination -Chapters 10 and 11 Student Version-Completed

...Multiple Choice Identify the choice that best completes the statement or answers the question. __A__ 1. All of the following would cause a trigger to fire, except ____. a. | BEFORE | c. | DELETE | b. | INSERT | d. | UPDATE | __A__ 2. Which of the following indicates that the trigger is fired only once, regardless of the number of rows affected by the DML statement? a. | Statement level | c. | DML level | b. | Event level | d. | Row level | __B__ 3. Which of the following statements is correct? a. | If multiple triggers exist on a table, the order in which the triggers will be fired can be set. | b. | A BEFORE statement level trigger will fire before a BEFORE row level trigger. | c. | If you have two statement level triggers, the order in which they are fired is dependent on the order in which they were written. | d. | Only one trigger can be constructed per table. | __D__ 4. Row level options are only applicable for ____ events. a. | CREATE | c. | DECLARE | b. | INSERT | d. | UPDATE and DELETE | __A__ 5. The default timing of a trigger is ____. a. | statement level | c. | row level | b. | system level | d. | header level | __A__ 6. Which of the following events will cause the trigger to fire? AFTER UPDATE OF orderplaced ON bb_basket a. | INSERT | c. | DELETE | b. | UPDATE | d. | AFTER | __D__ 7. CURSOR basketitem_curIS SELECT idproduct, quantity, option1 FROM bb_basketitem WHERE idbasket =......

Words: 1769 - Pages: 8

Premium Essay

Questions to Ask an Oracle Developer

...and Implementation)? Under your current role, do you work in all these phases yourself or are you usually involved with one particular phase? General Oracle Database and PL/SQL Questions: • Do you have any experience with Autonomous Transactions in Oracle database? The purpose is to complete (commit/rollback) a transaction in a called procedure irrespective of the transaction state in the calling procedure. • Have you ever encountered a situation with Mutating Tables and what did you do to work around it? When a table is in state of transition it is said to be mutating. eg: If a row has been deleted then the table is said to be mutating and no operations can be done on the table except select. • What’s your experience with Oracle Forms and Reports. Where would you implement bulk of business rules so as to make your coding more modular in Oracle Forms? PLL’s (PL/SQL Libraries). • What is referential integrity? Rules governing the relationships between primary keys and foreign keys of tables within a relational database that determine data consistency. Referential integrity requires that the value of every foreign key in every table be matched by the value of a primary key in another table. • What is a Transaction in Oracle? A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. • What is a Cursor in Oracle? A cursor is a handle (name or a pointer) for the memory associated with a specific...

Words: 1951 - Pages: 8

Premium Essay

Sql Lab 4

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

Words: 6758 - Pages: 28