Question

Write PL/SQL or T-SQL procedures to accomplish the following tasks:a. Obtain the first name and last name of the owner whose number currently is storedin I_OWNER_NUM. Place these values in the variables I_FIRST_NAME andI_LAST_NAME. Output the contents of I_OWNER_NUM, I_FIRST_NAME, andI_LAST_NAME.

Answer 1

Create or replace PROCEDURE OWNER_PROCE
(   
I_OWNER_NUM NUMBER
)
AS
   --when declaring
   -- variable's type, we can reference
--table.column%TYPE to use the
   -- type of an existing column.
   I_FIRST_NAME OWNER.firstname%TYPE;
  I_LAST_NAME OWNER.lastname%TYPE;

BEGIN
   -- SELECT and return column values into the
-- variables. If
   -- the OWNER NUMBER ID isn't found, Oracle 
--will throw and you can pick
   -- up the pieces in the EXCEPTION block 
--below.
   SELECT firstname,lastname INTO
     I_FIRST_NAME,I_LAST_NAME
     FROM OWNER
     WHERE OwnerNumber = I_OWNER_NUM ;
   -- this means the query above found one a
    --only one row, and ththen it put values --into the variables. Print out the
   -- variables.
   
   DBMS_OUTPUT.PUT_LINE('OWNER_NUM: ' || I_OWNER_NUM);
   DBMS_OUTPUT.PUT_LINE('FIRST_NAME: ' || I_FIRST_NAME);
   DBMS_OUTPUT.PUT_LINE('LAST_NAME: ' || I_LAST_NAME);
EXCEPTION
   -- If the query didn't find a row you'll end up here. In this case,
   -- there's no need for any type of fancy exception handling; just
   -- reporting that the Owner wasn't found is enough.
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Owner number ' ||I_OWNER_NEM|| ' not found.');
END;

NOTE: here, you should give the name of tables and field name. But you didnt. So I assume the table as OWNER and number of owner as OwnerNumber first name as firstname and last name as lastname.