PL-SQL.sql

--PL/SQL variable declaration and print value
SET SERVEROUTPUT ON
DECLARE
    C_ID CUSTOMERS.CUSTOMER_ID%TYPE;
    C_NAME CUSTOMERS.CUSTOMER_NAME%TYPE;
    C_PHONE CUSTOMERS.CUSTOMER_PHONE%TYPE;
BEGIN
    SELECT CUSTOMER_ID,CUSTOMER_NAME,CUSTOMER_PHONE INTO C_ID,C_NAME,C_PHONE FROM CUSTOMERS WHERE CUSTOMER_ID = 123;
    DBMS_OUTPUT.PUT_LINE('CUSTOMER_ID: ' || C_ID || ', CUSTOMER_NAME: ' || C_NAME || ', CUSTOMER_PHONE: ' || C_PHONE);
END;
/
--Insert and set default value
DECLARE
    C_ID CUSTOMERS.CUSTOMER_ID%TYPE := 999;
    C_NAME CUSTOMERS.CUSTOMER_NAME%TYPE := 'Fahim Wasi';
    C_EMAIL CUSTOMERS.CUSTOMER_EMAIL%TYPE := 'fahim.wasi@example.com';
    C_PHONE CUSTOMERS.CUSTOMER_PHONE%TYPE := '555-9898';
    C_ADDRESS CUSTOMERS.CUSTOMER_ADDRESS%TYPE := '999 Main st';
    
BEGIN
    INSERT INTO CUSTOMERS VALUES (C_ID,C_NAME,C_EMAIL,C_PHONE,C_ADDRESS);
END;
/
--Row type
DECLARE
    CUSTOMER_ROW CUSTOMERS%ROWTYPE;
BEGIN
    SELECT CUSTOMER_ID,CUSTOMER_NAME,CUSTOMER_EMAIL INTO CUSTOMER_ROW.CUSTOMER_ID,
    CUSTOMER_ROW.CUSTOMER_NAME,CUSTOMER_ROW.CUSTOMER_EMAIL  FROM CUSTOMERS WHERE CUSTOMER_ID = 123;
END;
/
--Cursor and row count
DECLARE 
  CURSOR CUSTOMER_CURSOR IS SELECT * FROM CUSTOMERS;
  CUSTOMER_ROW CUSTOMERS%ROWTYPE;
BEGIN
  OPEN CUSTOMER_CURSOR;
  FETCH CUSTOMER_CURSOR INTO CUSTOMER_ROW.CUSTOMER_ID,
    CUSTOMER_ROW.CUSTOMER_NAME,CUSTOMER_ROW.CUSTOMER_EMAIL,CUSTOMER_ROW.CUSTOMER_PHONE,CUSTOMER_ROW.CUSTOMER_ADDRESS;
  WHILE CUSTOMER_CURSOR%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE('CUSTOMER_ID: ' || CUSTOMER_ROW.CUSTOMER_ID || ' CUSTOMER_NAME: ' 
    || CUSTOMER_ROW.CUSTOMER_NAME || ' CUSTOMER_EMAIL: ' || CUSTOMER_ROW.CUSTOMER_EMAIL || ' CUSTOMER_PHONE : ' || CUSTOMER_ROW.CUSTOMER_PHONE 
    || ' CUSTOMER_ADDRESS: ' || CUSTOMER_ROW.CUSTOMER_ADDRESS);
    DBMS_OUTPUT.PUT_LINE('ROW COUNT: ' || CUSTOMER_CURSOR%ROWCOUNT);
    FETCH CUSTOMER_CURSOR INTO CUSTOMER_ROW.CUSTOMER_ID,
    CUSTOMER_ROW.CUSTOMER_NAME,CUSTOMER_ROW.CUSTOMER_EMAIL,CUSTOMER_ROW.CUSTOMER_PHONE,CUSTOMER_ROW.CUSTOMER_ADDRESS;
  END LOOP;
  CLOSE CUSTOMER_CURSOR;
END;
/
--LOOP
DECLARE 
    CUS_ID ORDERS.CUSTOMER_ID%TYPE;
BEGIN
    FOR X IN 1..10 LOOP
    SELECT CUSTOMER_ID INTO CUS_ID FROM ORDERS  WHERE ORDER_ID = X;
    DBMS_OUTPUT.PUT_LINE('CUSTOMER_ID: ' || CUS_ID);
    END LOOP;
END;
/
--IF /ELSEIF /ELSE
DECLARE
    I_NAME MENUITEMS.ITEM_NAME%TYPE :='Caesar Salad';
BEGIN
    IF I_NAME = 'Caesar Salad' THEN
        DBMS_OUTPUT.PUT_LINE('ITEM_CATEGORY IS Appetizer');
    ELSIF I_NAME = 'Margherita Pizza' THEN
        DBMS_OUTPUT.PUT_LINE('ITEM_CATEGORY IS Entree');
    ELSE
        DBMS_OUTPUT.PUT_LINE('NOT IN THE DATA TABLE');
    END IF;
END;
/
--Procedure
CREATE OR REPLACE PROCEDURE PROC1(O_ID IN INTEGER,I_NAME OUT VARCHAR2,O_ID2 IN OUT INTEGER) IS
T_SHOW CHAR(30) := 'From procedure: ';
BEGIN
    SELECT ITEM_NAME INTO I_NAME FROM MENUITEMS WHERE ITEM_ID=(SELECT ITEM_ID FROM ORDERDETAILS WHERE ORDER_ID = O_ID);
    O_ID2 := O_ID + 1;
      DBMS_OUTPUT.PUT_LINE(T_SHOW|| I_NAME || ' code is ' || O_ID || ' In out parameter: ' || O_ID2);
END;
/
DECLARE
    ORDER_ID_NO ORDERDETAILS.ORDER_ID%TYPE:=5;
    ITEM_NAME2 MENUITEMS.ITEM_NAME%TYPE;
    EXTRA INTEGER;
BEGIN
    PROC1(ORDER_ID_NO,ITEM_NAME2,EXTRA);
END;
/
--Function
CREATE OR REPLACE FUNCTION FUNC(CUS_ID IN VARCHAR) RETURN VARCHAR IS
NAME1 CUSTOMERS.CUSTOMER_NAME%TYPE;
BEGIN
    SELECT CUSTOMER_NAME INTO NAME1 FROM CUSTOMERS WHERE CUSTOMER_ID = CUS_ID;
    RETURN NAME1;
END;
/
DECLARE
    NAME2 VARCHAR(50);
BEGIN
    NAME2:=FUNC(123);
    DBMS_OUTPUT.PUT_LINE(NAME2);
END;
/

DROP PROCEDURE PROC1;
DROP FUNCTION FUNC;