Oracle PL/SQL Fetch Example DECLARE v_name customers.customer_name%TYPE; v_amount contracts.amount%TYPE; CURSOR c IS SELECT cust.customer_name, ctr.amount FROM customers cust, contracts ctr WHERE ctr.customer_id = cust.customer_id AND ctr.contract_id = 4003; BEGIN OPEN c; LOOP FETCH c INTO v_name, v_amount; EXIT WHEN c%NOTFOUND; DBMS_OUTPUT.PUT_LINE...
Oracle PL/SQL Exit Example DECLARE v_name customers.customer_name%TYPE; v_type customers.customer_type%TYPE; CURSOR c IS SELECT customer_name, customer_type FROM customers WHERE customer_id = 2147; BEGIN OPEN c; LOOP FETCH c INTO v_name, v_type; EXIT WHEN c%NOTFOUND; DBMS_OUTPUT.PUT_LINE ('Description: '||v_name||' '||v_type||''); END LOOP; CLOSE c;...
Oracle PL/SQL Exists Example Exists Example 1: SELECT * FROM customers c1 WHERE EXISTS (Select * From contracts c2 Where c1.customer_id = c2.customer_id ); Not Exists Example 2: SELECT * FROM customers c1 WHERE NOT EXISTS (Select * From contracts c2...
Oracle PL/SQL Drop Table The Drop Table statement is used to remove a table from the database. The Drop Table syntax: Drop Table table_name ; Drop Table Example: drop table offers ;...
Oracle PL/SQL Delete Example Example 1: DELETE FROM customers c WHERE c.customer_type='INDIVIDUAL' AND c.city = 'NEW YORK'; Example 2: DECLARE c customers%ROWTYPE; BEGIN SELECT * INTO c FROM customers WHERE customer_id = 123001; DELETE FROM customers WHERE customer_id = c.customer_id; DBMS_OUTPUT.PUT_LINE('The...