Question

Create a FUNCTION that will print out an address label for the TAL Distributors database using the Customer table. You can specify the customer table to output the address.

Here is an example of the output expected:

Toys Galore   

28 Laketon St.   

Fullton, CA 90085

The database name is "TAL Distributors" and images of all the tables have been attached. Please double check that function executes before answering.

Customer Table:

SQLQueryl.sgl . (L (SOM.Jonesl58 (54)) × SELECT TOP (1000) [Customer-Num] , [Customer_Name] , [Street] ,[City] [State] ,[Zip] [Balance] [Credit_Limit] ,[Rep_Num] 2 4 7 9 10 11 FROM [TAL Distributors].[dbo]. [Custpmer] 108 % l Results Messages Customer Num Customer Name Street City State Zp Balance Credit_Limit Rep_Num 1 00001 Jones, Jinm 811 Montevideo Dr Lansing MI 48823 1000.00 10000.00 1 2 00002 Richardson, Jamila 4216 W Outer Drive Detroit M 48204 500.00 50000.00 2

Answer 1

drop table customer cascade constraints;
create table customer(customer_num int primary key,
                       Customer_name varchar(25),
                       street varchar(25),
                       City varchar(10),
                       State char(2),
                       ZIp1 varchar2(25),
                       Balance number(10,2),
                       Credit_limit number(10,2),
                       Rep_num int);
                      
insert into customer values(00001,'Jones Jim','811 Montervideo Dr','Lansing','MI','48823',1000.00,10000.00,1);
insert into customer values(00002,'Richardson Jamila','4216 W Outer Drive','Detriot','MI','48204',500.00,50000.00,2);

set serveroutput on;
CREATE or replace function MYfunction6 return varchar2
as
   output varchar2(200);
   CURSOR C1 IS
   SELECT Customer_name,Street,City,State,Zip1
   FROM CUSTOMER;  
begin  
   for p in c1
   loop
       output := output || ' ( ' ||p.Customer_name|| ' ' ||p.Street|| ' '||p.City ||' '||P.state||' '||p.Zip1||')';
   END LOOP;
   return output;
end;
/

select MYfunction6 from dual;

SQL> set serveroutput on; SQL CREATE or replace function MYFunctionó return varchar2 2 as 3 output varchar2 (200); 4 CURSOR C1 IS 5 SELECT Custoner_nane,Street,City,State,Zip1 6 FROM CUSTOMER 7 begin 8 For p in c1 9loop 1 outputoutput IIp.Customer_naeIp.Street p.City II PstatelI 11 END LOOP; 12 return output; 13 end; 14 / Function created. SQL> SQL> select MYFunctionó From dual; MYFUNCTION6 Jones Jim 811 Montervideo Dr Lansing MI 48823) Richardson Jamila 4216 W Out er Drive Detriot MI 48204) SQL> I

SQL> select fron custoner; CUSTOMER NUM CUSTOMER_NAME STREET CITY BALANCE CREDIT_LIMIT REP NUM ST ZIP1 2 dihardson Jan21 uter eriwe betriot MI24 10000 59000 1 Jones Jim 2 Richardson Jamila 811 Montervideo Dr 4216 W Outer Drive 1900 500 Detriot MI 48204