SQL 函数的语法问题

Question

试试：CREATE   FUNCTION check_unit_price
    (
      @p_org_id INT ,
      @p_item_no INT ,
      @p_order_number INT ,
      @p_line_number INT ,
      @p_cust_number VARCHAR(50)
    )
RETURNS DECIMAL(18, 8)
AS
    BEGIN 
   
        DECLARE @v_selling_price DECIMAL(18, 8) 
        DECLARE @v_cust VARCHAR(50)  
   
        DECLARE c1 CURSOR
        FOR
            SELECT  ola.unit_selling_price
                    * dbo.get_day_rate(oha.creation_date,
                                       oha.transactional_curr_code, 'hkd')
            FROM    oe_order_headers_all oha ,
                    oe_order_lines_all ola
            WHERE   oha.header_id = ola.header_id
                    AND oha.booked_flag = 'y'
                    AND oha.cancelled_flag = 'n'
                    AND ola.cancelled_flag = 'n'
                    AND ola.unit_selling_price > 0
                    AND ola.org_id = @p_org_id
                    AND inventory_item_id = @p_item_no
                    AND pricing_quantity_uom = 'ctn'
                    AND ola.unit_selling_price
                    * dbo.get_day_rate(oha.creation_date,
                                       oha.transactional_curr_code, 'hkd') > 0
            ORDER BY ola.unit_selling_price
                    * dbo.get_day_rate(oha.creation_date,
                                       oha.transactional_curr_code, 'hkd')
   
        DECLARE c2 CURSOR
        FOR
            SELECT  ola.unit_selling_price
                    * dbo.get_day_rate(oha.creation_date,
                                       oha.transactional_curr_code, 'hkd')
            FROM    oe_order_headers_all oha ,
                    oe_order_lines_all ola
            WHERE   oha.header_id = ola.header_id
                    AND oha.booked_flag = 'y'
                    AND oha.cancelled_flag = 'n'
                    AND ola.cancelled_flag = 'n'
                    AND ola.unit_selling_price > 0
                    AND ola.org_id = @p_org_id
                    AND order_number = @p_order_number
                    AND line_number = @p_line_number
                    AND ola.unit_selling_price
                    * dbo.get_day_rate(oha.creation_date,
                                       oha.transactional_curr_code, 'hkd') > 0
  
  
        DECLARE c_cust CURSOR
        FOR
            SELECT  segment2
            FROM    mtl_system_items_kfv
            WHERE   organization_id = @p_org_id
                    AND inventory_item_id = @p_item_no        IF ( ISNULL(@p_order_number, 0) = 0 )
            BEGIN
                OPEN c_cust
                FETCH NEXT FROM c_cust INTO @v_cust
                WHILE ( @@fetch_status <> -1 )
                    BEGIN
                        FETCH NEXT FROM c_cust INTO @v_cust
                    END 
                CLOSE c_cust
                DEALLOCATE c_cust 
            END 
        ELSE
            SET @v_cust = @p_cust_number 
        
        IF ( ( @v_cust IN ( 'h06700', 'p01911', 's14005' ) )
             OR ( ISNULL(@p_cust_number, 0) = 0 )
           )
            BEGIN
                OPEN c1
                FETCH NEXT FROM c1 INTO @v_selling_price
                WHILE ( @@fetch_status <> -1 )
                    BEGIN
                        FETCH NEXT FROM c1 INTO @v_selling_price
                    END 
                CLOSE c1
                DEALLOCATE c1 
            END 
        ELSE
            OPEN c2
        FETCH NEXT FROM c2 INTO @v_selling_price
        WHILE ( @@fetch_status <> -1 )
            BEGIN
                FETCH NEXT FROM c2 INTO @v_selling_price
            END 
        CLOSE c2
        DEALLOCATE c2        RETURN(@v_selling_price)
    END 

Answer 1

版主 谢谢 可以了， 是不是 IF 里面一样要有begin end 
else 里面是可以没有的吗？

Answer 2

如果没有begin /end包住，if只对接下来的下一句有效，后面一堆都被认为是别的地方的