Database Systems: Design, Implementation, & Management
13th Edition
ISBN: 9781337627900
Author: Carlos Coronel, Steven Morris
Publisher: Cengage Learning
expand_more
expand_more
format_list_bulleted
Concept explainers
Question
Chapter 8, Problem 28P
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;
Retrun (value or expression);
END;
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
Task 8: Create the UPDATE_INVOICE procedure to change the date of the invoice whose number is stored in I_INVOICE_NUM to the date currently found in I_INVOICE_DATE.
Expected Results
INVOICE_NUM
INVOICE_DATE
CUST_ID
00001
2021-11-21
294
Task 8: Create the UPDATE_INVOICE procedure to change the date of the invoice whose number is stored in I_INVOICE_NUM to the date currently found in I_INVOICE_DATE.
1) Add the following record to the OWNER table:
INSERT INTO OWNER VALUES('SA100', 'Sam', 'Afyouni', '100 Hello St', 'Anytown', 'MA', '55555');
2) Create the UPD_OWNER_LAST_NAMEprocedure to change the last name of the owner whose number is stored in I_OWNER_NUM (provided as a parameter) to the value currently found in I_LAST_NAME.
Chapter 8 Solutions
Database Systems: Design, Implementation, & Management
Ch. 8 - Prob. 1RQCh. 8 - Explain why it might be more appropriate to...Ch. 8 - What is the difference between a column constraint...Ch. 8 - What are referential constraint actions?Ch. 8 - What is the purpose of a CHECK constraint?Ch. 8 - Explain when an ALTER TABLE command might be...Ch. 8 - What is the difference between an INSERT command...Ch. 8 - What is the difference between using a subquery...Ch. 8 - What is the difference between a view and a...Ch. 8 - Prob. 10RQ
Ch. 8 - Prob. 11RQCh. 8 - Prob. 12RQCh. 8 - Write the SQL code that will create only the table...Ch. 8 - Having created the table structure in Problem 1,...Ch. 8 - Prob. 3PCh. 8 - Write the SQL code that will save the changes made...Ch. 8 - Write the SQL code to change the job code to 501...Ch. 8 - Write the SQL code to delete the row for William...Ch. 8 - Write the SQL code to create a copy of EMP_1,...Ch. 8 - Using the EMP_2 table, write the SQL code that...Ch. 8 - Using the EMP_2 table, write the SQL code to...Ch. 8 - Prob. 10PCh. 8 - Prob. 11PCh. 8 - Prob. 12PCh. 8 - Prob. 13PCh. 8 - Prob. 14PCh. 8 - Prob. 15PCh. 8 - Create the CUSTOMER table structure illustrated in...Ch. 8 - Create the INVOICE table structure illustrated in...Ch. 8 - Prob. 18PCh. 8 - Prob. 19PCh. 8 - Create an Oracle sequence named CUST_NUM_SEQ to...Ch. 8 - Create an Oracle sequence named INV_NUM_SEQ to...Ch. 8 - Prob. 22PCh. 8 - Modify the CUSTOMER table to include the customers...Ch. 8 - Prob. 24PCh. 8 - Prob. 25PCh. 8 - Create a trigger named trg_updatecustbalance to...Ch. 8 - Prob. 27PCh. 8 - Prob. 28PCh. 8 - Write a trigger to update the customer balance...Ch. 8 - Prob. 30PCh. 8 - Create a trigger named trg_line_total to write the...Ch. 8 - Create a trigger named trg_line_prod that...Ch. 8 - Create a stored procedure named prc_inv_amounts to...Ch. 8 - Create a procedure named prc_cus_balance_update...Ch. 8 - Modify the MODEL table to add the attribute and...Ch. 8 - Prob. 36PCh. 8 - Modify the CHARTER table to add the attributes...Ch. 8 - Write the sequence of commands required to update...Ch. 8 - Write the sequence of commands required to update...Ch. 8 - Write the command required to update the...Ch. 8 - Write the command required to update the...Ch. 8 - Write the command required to update the...Ch. 8 - Prob. 43PCh. 8 - Create a trigger named trg_char_hours that...Ch. 8 - Create a trigger named trg_pic_hours that...Ch. 8 - Create a trigger named trg_cust_balance that...Ch. 8 - Write the SQL code to create the table structures...Ch. 8 - The following tables provide a very small portion...Ch. 8 - For Questions 49-63, use the tables that were...Ch. 8 - Prob. 50PCh. 8 - Write a single SQL command to increase all price...Ch. 8 - Alter the DETAILRENTAL table to include a derived...Ch. 8 - Update the DETAILRENTAL table to set the values in...Ch. 8 - Alter the VIDEO table to include an attribute...Ch. 8 - Update the VID_STATUS attribute of the VIDEO table...Ch. 8 - Alter the PRICE table to include an attribute...Ch. 8 - Prob. 57PCh. 8 - Prob. 60P
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Similar questions
- Create a Procedure to delete a existing invoice row by accepting INV_NUMBER,CUS_CODE, INV_DATE. Whenever you execute the procedure by passing value, it hasto delete a particular tuple from the INVOICE table.arrow_forwardcode must be in SQL SSMS format /*29. Write a trigger to update the CUST_BALANCE ON CUSTOMER TABLE when an invoice is deleted (INV_NUM) FROM INVOICE TABLE. Namethe trigger trg_updatecustbalance2.*/ 30. Write a procedure to delete an invoice (INV_NUM) FROM INVOICE TABLE giving the invoice number as a parameter.Name the procedure prc_inv_delete. Test the procedure by deleting invoices 8005and 8006. TABLE STRUCTURE & CURRENT INV TABLE ARE ATTACHEDarrow_forwardI need help with an UPDATE statement that will change the CUST_EMAIL value to "unknown" for records in the CUSTOMERS table that have null values for the cust_email fieldarrow_forward
- The COUNT(*) function in the SELECT clause is used to return:a. the number of records in the specified tablesb. the number of orders placed by each customerc. the number of NULL values in the specified tablesd. the number of customers who have placed an orderarrow_forwardWrite a procedure named prc_inv_add to add a new invoice record to the INV_MYSQL table. Use the following values in the new record: INV_NUM CUST_NUM INV_DATE INV_AMOUNT 9006 2000 2018-04-30 301.72 (You should execute the procedure and verify that the new invoice was added to ensure your code is correct.)arrow_forwardCreate a Procedure to delete a existing invoice row by accepting INV_NUMBER, CUS_CODE, Whenever you execute the procedure by passing value, it has to delete a particular tuple from the INVOICE table. CREATE TABLE INVOICE (INV_NUMBER integer PRIMARY KEY,CUS_CODE integer,INV_DATE date,FOREIGN KEY (CUS_CODE) REFERENCES CUSTOMER);INSERT INTO INVOICE VALUES('1001','10014','1/16/2016');INSERT INTO INVOICE VALUES('1002','10011','1/16/2016');INSERT INTO INVOICE VALUES('1003','10012','1/16/2016');INSERT INTO INVOICE VALUES('1004','10011','1/17/2016');INSERT INTO INVOICE VALUES('1005','10018','1/17/2016');INSERT INTO INVOICE VALUES('1006','10014','1/17/2016');INSERT INTO INVOICE VALUES('1007','10015','1/17/2016');INSERT INTO INVOICE VALUES('1008','10011','1/17/2016');arrow_forward
- Task 9: Create the DELETE_INVOICE procedure to delete the invoice whose number is stored in I_INVOICE_NUM.arrow_forwardCreate a view named INVOICE_TOTAL. It consists of the invoice number and invoice total for each invoice currently on file. (The invoice total is the sum of the number of units ordered multiplied by the quoted price on each invoice line for each invoice.) Sort the rows by invoice number. Use TOTAL_AMOUNT as the name for the invoice total.arrow_forwardAlter the given table by adding new column of "Job" in it and assign the sequence values to the column AS WELL. EMPLOYEE ID LAST NAME 149 Zotkey 174 Abel 176 Taylor ANNSAL HIRE_DATE 126000 29-JAN-00 132000 11-MAY-96 103200 24-MAR-98 NOTE: ASSUME 100 RECORDS IN THE TABLE. YOU NEED TO CREATE PROCEDURE FOR IT. USE ANY LOOP TO FILL THE ENTERIES ALONG WITH DML OPERATION.arrow_forward
- Task 7: Create a procedure to add a row to the INVOICES table. CALL ADD_INVOICE('00001', '2021-11-20', '294') SELECT * FROM INVOICES WHERE INVOICE_NUM = '00001'arrow_forwardProblem 30 Write a procedure to delete an invoice, giving the invoice number as a parameter. Name the procedure prc_inv_delete. Test the procedure by deleting invoices 9000 and 9001arrow_forwardcode must be in SQL SSMS format /*29. Write a trigger to update the CUST_BALANCE ON CUSTOMER TABLE when an invoice is deleted (INV_NUM) FROM INVOICE TABLE. Namethe trigger trg_updatecustbalance2.*/ TABLE STRUCTURE IS ATTACHEDarrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill Education
Starting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSON
Digital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON
C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSON
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Programmable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education
Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education