# Write a stored procedure in PL/SQL or T-SQL that will change the price of an item with a given item number. How would you use this stored procedure to change the price of item AH74 to \$26.95?

### A Guide to SQL

9th Edition
Philip J. Pratt
Publisher: Course Technology Ptr
ISBN: 9781111527273

Chapter
Section

### A Guide to SQL

9th Edition
Philip J. Pratt
Publisher: Course Technology Ptr
ISBN: 9781111527273
Chapter 8, Problem 8TD
Textbook Problem
77 views

## Write a stored procedure in PL/SQL or T-SQL that will change the price of an item with a given item number. How would you use this stored procedure to change the price of item AH74 to \$26.95?

Program Plan Intro

Stored procedures:

• A procedure is a collection of procedural and SQL statements.
• A procedure may have input parameter, output parameter and both parameters.
• It has a declared with a unique named with a unit of procedural code using the proprietary RDBMS and it is invoked by a host language library routine.

Syntax for stored procedure:

CREATE FUNCTION fun_name(argument IN data-type)RETRUN data-type[IS]

BEGIN

PL/SQL statements;

Return (value or expression);

END;

### Explanation of Solution

Query to create stored procedure:

CREATE OR REPLACE PROCEDURE CHG_ITEM_PRICE (I_ITEM_NUM IN ITEM. ItEM_NUM%TYPE,

I_PRICE IN ITEM.PRICE%TYPE) AS

BEGIN

UPDATE ITEM

SET PRICE = I_PRICE

WHERE ITEM_NUM = I_ITEM_NUM;

END;

/

Explanation:

• The above query is used to create a procedure named “CHG_ITEM_PRICE” to select the records in the “ITEM” table.
• Pass the “I_ITEM_NUM” and “I_PRICE” as the parameters.
• Update the “I_PRICE” value for the corresponding “I_ITEM_NUM” value...

### Still sussing out bartleby?

Check out a sample textbook solution.

See a sample solution

#### The Solution to Your Study Problems

Bartleby provides explanations to thousands of textbook problems written by our experts, many with advanced degrees!

Get Started

Find more solutions based on key concepts
What is logical independence?

Database Systems: Design, Implementation, & Management

What is the smallest graduation on a metric vernier caliper?

Precision Machining Technology (MindTap Course List)

For Problems 16.33 through 16.38, draw the isometric view of the following objects. Make the necessary measurem...

Engineering Fundamentals: An Introduction to Engineering (MindTap Course List)

5. Describe a data dictionary and list the types of information it contains.

Systems Analysis and Design (Shelly Cashman Series) (MindTap Course List)

If your motherboard supports ECC DDR3 memory, can you substitute non-ECC DDR3 memory?

A+ Guide to Hardware (Standalone Book) (MindTap Course List)

What are some popular mobile operating systems? (434)

Enhanced Discovering Computers 2017 (Shelly Cashman Series) (MindTap Course List)

Does all of the heat produced by an SMA weld stay in the weld? Why or why not?

Welding: Principles and Applications (MindTap Course List)