DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005

    how to concatenate in PL/SQL


    I need to create a function to concatenate first name, blank, last name from employee table...
    It should return the concatenated value...
    and also i have to check for exceptions...

    have to then call it using sleect command

  2. #2
    Join Date
    May 2002
    select name||address|city||whatever
    from your_table.

    Use the double pipe ("||") to concatenate (as one way). Another is to append strings to strings, but since you're using a select, the "||" method will suit your needs.

  3. #3
    Join Date
    Nov 2005
    thanks..I kind of got that working..

    i have another Q for u..
    I have to write a PROCEDURE called DISCOUNT_ORDERS which gives X percent discount for all Y orders whose shipping date is more than Y days from the order date. The procedure takes X% and Ydays as input paramters...I am asked to display only orders who get a discount....It should display order id, order date, ship date, and discount amount and update total of all orders...
    I tried to create a function first and then thought of calling the function frlom preocdure...doesnt look like it works....can u help here?

    > SQL> Create or replace function DISCOUNT(p_diff IN number)
    > 2 RETURN number IS
    > 3 p_disamt number(2,2);
    > 4 p_newamt number(5,2);
    > 5 max_day :=15;
    > 6 min_day :=5;
    > 7 max_dis :=10;
    > 8 min_dis :=5;
    > 9 BEGIN
    > 10 SELECT total INTO p_total from sales_order;
    > 11 If(p_diff=min_day)
    > 12 THEN
    > 13 p_disamt :=(p_total * (min_dis/100));
    > 14 p_newamt :=(p_total - p_disamt);
    > 15 ELSIF(p_diff>=max_day)
    > 16 THEN p_disamt :=(p_total *(max_dis/100));
    > 17 p_newamt :=(p_total - p_disamt);
    > 18 ELS p_disamt :=0;
    > 19 END IF;
    > 20 /

    CREATE TABLE sales_order (
    order_id NUMBER(4) NOT NULL,
    order_date DATE,
    customer_id NUMBER(6),
    ship_date DATE,
    total NUMBER(8,2));

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.