PL/SQL alias/variable to existing SQL query

Question

I am very new to PL/SQL and would like to be able to take advantage of the procedural options of oracle SQL.

I have a query that I would like to transform into a PL/SQL query that takes the SKU you want and inputs whenever you have the word SKU in your query.

Example:

SELECT A.*, CT.CUSTOMER_ID, CT.ORDERS
FROM CUSTOMER_TABLE CT
RIGHT JOIN
(
SELECT OT.COLUMN_ID, OT.SKU, OT.ORDERS
FROM ORDERS_TABLE OT
WHERE OT.SKU = 123
)A
ON CT.ORDERS = OT.ORDERS
AND CT.SKU IS > 0

This is strictly an example so I know that what I am asking is pointless, however, if I had a lengthier query that contains more than 2 or 3 "SKU" inputs then I would like to have a variable/parameter for it.

My ideal code would be:

DECLARE
SKUS INTEGER := 123;

BEGIN
 SELECT A.*, CT.CUSTOMER_ID
    FROM CUSTOMER_TABLE CT
    RIGHT JOIN
    (
    SELECT OT.COLUMN_ID, OT.SKU, OT.ORDERS
    FROM ORDERS_TABLE OT
    WHERE @SKUS
    )A
    ON CT.ORDERS = OT.ORDERS
    AND @SKUS IS > 0
END;
/

I am not sure if that is proper syntax but I hope the idea makes sense of what I am asking.

Thank you!

Steven Feuerstein · Accepted Answer · 2018-04-25 00:42:21Z

2

PL/SQL handles such situations very easily. Keep your SQL statement exactly the same and replace literals with your variable, or even a cursor parameter. I moved your SELECT into an explicit cursor to show how you can easily declare a record based on the cursor as well:

DECLARE
   c_sku   CONSTANT INTEGER := 123;

   CURSOR my_cur (sku_in IN INTEGER)
   IS
      SELECT a.*, ct.customer_id, ct.orders
        FROM customer_table ct
             RIGHT JOIN (SELECT ot.column_id, ot.sku, ot.orders
                           FROM orders_table ot
                          WHERE ot.sku = sku_in) a
                ON ct.orders = ot.orders AND sku_in > 0;

   l_info           my_cur%ROWTYPE;
BEGIN
   OPEN my_cur (c_sku);

   FETCH my_cur INTO l_info;

   CLOSE my_cur;
END;

answered Apr 25, 2018 at 0:42

Steven Feuerstein

2,0041 gold badge12 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PL/SQL alias/variable to existing SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related