PRODUCT TABLE CREATE TABLE ProductTable( ProductID INTEGER NOT NULL primary key,
ID: 3784749 • Letter: P
Question
PRODUCT TABLE
CREATE TABLE ProductTable(
ProductID INTEGER NOT NULL primary key,
ProductName VARCHAR(50) NOT NULL,
ListPrice NUMBER(10,2),
Category INTEGER NOT NULL
);
/
INSERT INTO ProductTable VALUES(299,'Chest',99.99,10);
INSERT INTO ProductTable VALUES(300,'Wave Cruiser',49.99,11);
INSERT INTO ProductTable VALUES(301,'Megaland Play Tent',59.99,11);
INSERT INTO ProductTable VALUES(302,'Wind-Up Water Swimmers',2.00,11);
INSERT INTO ProductTable VALUES(303,'Garmin Pocket or Vehicle GPS Navigator',609.99,12);
CREATE OR REPLACE PROCEDURE sqltest AS
V_Category NUMBER;
BEGIN
-- comments
SELECT Category INTO v_Category FROM ProductTable
WHERE ProductId = 300;
dbms_output.put_line('Product category for product #300 is ' || V_Category);
END;
/
set serveroutput on;
exec sqltest;
CONCATENATE PROCEDURE
create or replace procedure concatenate_strings (
str1 varchar2,
str2 varchar2
) is
result varchar(50);
begin
result := str1 || '_' || str2;
dbms_output.put_line('The result is ' || result);
dbms_output.put_line(SYSDATE);
end;
/
SET SERVEROUTPUT ON;
EXECUTE concatenate_strings('big','dog');
Write a function f_concatenate_strings that will do the same as concatenate_strings procedure from #1, but can be called from a select statement. Examples of output:
SELECT f_concatenate_strings('big','dog') FROM DUAL;
Will return big_dog 27-JAN-16
SELECT f_concatenate_strings(ProductName, to_char(ListPrice, '$999.99')) from ProductTable WHERE ProductID=302;
First, the table ProductTable, then product with ID 302 will be located. Product name of that product will be concatenated with the price and current date.
Create a procedure that accepts product ID as a parameter and returns the price from ProductTable table. Add exception handling to catch if product ID is not in the table.
The table ProductTable already exists (see sample code in this module).
Please make sure you run it to see if it works.
Explanation / Answer
[1]
CREATE OR REPLACE FUNCTION f_concatenate_strings (x IN varchar2, y IN varchar2)
RETURN VARCHAR2 AS
BEGIN
result := str1 || '_' || str2||' '||SYSDATE;
return result;
END;
/
[2]
CREATE OR REPLACE PROCEDURE p_product_id(product_id IN number, price OUT number)
AS
BEGIN
SELECT price INTO price
FROM ProductTable
WHERE ProductId = product_id;
EXCEPTION NO_DATA_FOUND
dbms_output.put_line('Product id '||product_id||' not in table' );
END;
/
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.