CODEWITHSUNDEEP
oracle-databaseplsql
alex_fields1
alex_fields1

Reputation: 71

PL/SQL alias/variable to existing SQL query

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!

Upvotes: 0

Views: 174

Answers (1)

Steven Feuerstein
Steven Feuerstein

Reputation: 1974

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;

Upvotes: 2

Related Questions