PL/SQL

Structure

SET SERVEOUTPUT;
DECLARE
	m_name VARCHAR2(60);
BEGIN
	m_name := 'DataHungry';

	DBMS_OUTPUT.PUT_LINE('My name is' || m_name);
END;
/

Variable declarations

%TYPE

SET SERVEROUTPUT ON;
DECLARE
    V_HIRE_DATE   EMPLOYEES.HIRE_DATE%TYPE;
    V_SYSDATE     EMPLOYEES.HIRE_DATE%TYPE;
    V_EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE := 176;
BEGIN
    SELECT
        HIRE_DATE,
        SYSDATE
    INTO
        V_HIRE_DATE,
        V_SYSDATE
    FROM
        EMPLOYEES
    WHERE
        EMPLOYEE_ID = 100;

    DBMS_OUTPUT.PUT_LINE('Hire date is : ' || V_HIRE_DATE);
    DBMS_OUTPUT.PUT_LINE('Todate is : ' || V_SYSDATE);
END;

DECLARE
    V_MAX_DEPTNO NUMBER;
    V_DEPT_ID    NUMBER;
    V_DEPT_NAME  DEPARTMENTS.DEPARTMENT_NAME%TYPE := 'Education';
BEGIN
    SELECT
        MAX(DEPARTMENT_ID)
    INTO V_MAX_DEPTNO
    FROM
        DEPARTMENTS;

    DBMS_OUTPUT.PUT_LINE('The maximum department_id is: ' || V_MAX_DEPTNO);
    V_DEPT_ID := 10 + V_MAX_DEPTNO;
    
    INSERT INTO DEPARTMENTS (
        DEPARTMENT_ID,
        DEPARTMENT_NAME
    ) VALUES (
        V_DEPT_ID,
        V_DEPT_NAME
    );

    DBMS_OUTPUT.PUT_LINE('Insert department_id: ' || V_DEPT_ID);
END;

%ROWTYPE

DECLARE
    V_COUNTRY_ID     VARCHAR2(20) := 'CA';
    V_COUNTRY_RECORD COUNTRIES%ROWTYPE;
BEGIN
    SELECT
        *
    INTO V_COUNTRY_RECORD
    FROM
        COUNTRIES
    WHERE
        COUNTRY_ID = UPPER(V_COUNTRY_ID);

    DBMS_OUTPUT.PUT_LINE('Country ID: '
                         || V_COUNTRY_RECORD.COUNTRY_ID
                         || ' Country Name: '
                         || V_COUNTRY_RECORD.COUNTRY_NAME
                         || ' Region: '
                         || V_COUNTRY_RECORD.REGION_ID);

END;

User input

DECLARE
    V_YOUR_NAME  VARCHAR2(20) := &YOUR_NAME;
    V_START_DATE DATE := TO_DATE('20-Apr-2000');
    V_CUR_DATE   DATE := TO_DATE('17-SEP-22');
    V_WORKMONTH  NUMBER;
BEGIN
    V_WORKMONTH := FLOOR((V_CUR_DATE - V_START_DATE) / 30);
    DBMS_OUTPUT.PUT_LINE(V_YOUR_NAME
                         || ' work in this company for'
                         || V_WORKMONTH
                         || ' MONTHS ');
END;

Cursor

SQL%ROWCOUNT

DECLARE
    V_MIN NUMBER := 10000;
    V_MAX NUMBER := 20000;
    V_NUM NUMBER;
BEGIN
    SELECT
        COUNT(1)
    INTO V_NUM
    FROM
        EMPLOYEES
    WHERE
        SALARY BETWEEN V_MIN AND V_MAX;

    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || 'detected');
END;

SQL%FOUND / NOTFOUND

DECLARE
    TOTAL_ROWS NUMBER(2);
BEGIN
    UPDATE EMPLOYEES
    SET
        SALARY = 15000
    WHERE
        FIRST_NAME = 'Shelley';

    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('no customers selected');
    ELSIF SQL%FOUND THEN
        TOTAL_ROWS := SQL%ROWCOUNT;
        DBMS_OUTPUT.PUT_LINE('total_rows' || ' employees selected');
    END IF;
END;

CURSOR IS

DECLARE
    V_DEPTNO NUMBER := 10;
    CURSOR C_EMP_CURSOR IS
    SELECT
        LAST_NAME,
        SALARY,
        MANAGER_ID
    FROM
        EMPLOYEES
    WHERE
        DEPARTMENT_ID = V_DEPTNO;

BEGIN
    FOR EMP_RECORD IN C_EMP_CURSOR LOOP
        IF (EMP_RECORD.SALARY < 5000) AND ( EMP_RECORD.MANAGER_ID = 101 OR EMP_RECORD.MANAGER_ID = 124 ) THEN
            DBMS_OUTPUT.PUT_LINE(EMP_RECORD.LAST_NAME || ' Due for a raise');
        ELSE
            DBMS_OUTPUT.PUT_LINE(EMP_RECORD.LAST_NAME || ' Not Due for a raise');
        END IF;
    END LOOP;
END;

FETCH INTO

DECLARE
    V_NUM NUMBER(3) := 5;
    V_SAL EMPLOYEES.SALARY%TYPE;
    CURSOR C_EMP_CURSOR IS
    SELECT
        SALARY
    FROM
        EMPLOYEES
    ORDER BY
        SALARY DESC;

BEGIN
    OPEN C_EMP_CURSOR;
    FETCH C_EMP_CURSOR INTO V_SAL;
    WHILE
        C_EMP_CURSOR%ROWCOUNT <= V_NUM
        AND C_EMP_CURSOR%FOUND
    LOOP
        INSERT INTO TOP_SALARIES ( SALARY ) VALUES ( V_SAL );

        FETCH C_EMP_CURSOR INTO V_SAL;
    END LOOP;

    CLOSE C_EMP_CURSOR;
END;
/

Exception

DECLARE
    V_NUM     NUMBER := 5;
    V_DIVIDER NUMBER := 0;
    V_RES     NUMBER;
    EX_INVALID_ZERO EXCEPTION;
    EX_INVALID_NEG EXCEPTION;
BEGIN
    IF V_DIVIDER = 0 THEN
        RAISE EX_INVALID_ZERO;
    ELSIF V_NUM < 0 THEN
        RAISE EX_INVALID_NEG;
    ELSE
        V_RES := V_NUM / V_DIVIDER;
        DBMS_OUTPUT.PUT_LINE('result is: ' || V_RES);
    END IF;
EXCEPTION
    WHEN EX_INVALID_NEG THEN
        DBMS_OUTPUT.PUT_LINE('Number could not be negative');
    WHEN EX_INVALID_ZERO THEN
        DBMS_OUTPUT.PUT_LINE('Divider could not be zero.');
END;

DECLARE
    V_ENAME   EMPLOYEES.LAST_NAME%TYPE;
    V_EMP_SAL EMPLOYEES.SALARY%TYPE := 6000;
BEGIN
    SELECT
        LAST_NAME
    INTO V_ENAME
    FROM
        EMPLOYEES
    WHERE
        SALARY = V_EMP_SAL;

    INSERT INTO MESSAGES ( RESULTS ) VALUES ( V_ENAME || ' - ' || V_EMP_SAL );

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        INSERT INTO MESSAGES ( RESULTS ) VALUES ( 'No emploee with a salary of ' || TO_CHAR(V_EMP_SAL) );

    WHEN TOO_MANY_ROWS THEN
        INSERT INTO MESSAGES ( RESULTS ) VALUES ( 'More than one employee with a salary of ' || TO_CHAR(V_EMP_SAL) );

END;

IF ELSIF ELSE

BEGIN
    FOR I IN 1..10 LOOP
        IF I = 6 OR I = 8 THEN
            NULL;
        ELSE
            INSERT INTO MESSAGE ( RESULTS ) VALUES ( I );

        END IF;
    END LOOP;

    COMMIT;
END;

Loop

For

DECLARE
    TYPE DEPT_TABLE_TYPE IS
        TABLE OF DEPARTMENTS.DEPARTMENT_NAME%TYPE INDEX BY PLS_INTEGER;
    MY_DEPT_TABLE DEPT_TABLE_TYPE;
    F_LOOP_COUNT  NUMBER(2) := 10;
    V_DEPTNO      NUMBER(4) := 0;
BEGIN
    FOR I IN 1..F_LOOP_COUNT LOOP
        V_DEPTNO := V_DEPTNO + 10;
        SELECT
            DEPARTMENT_NAME
        INTO
            MY_DEPT_TABLE(I)
        FROM
            DEPARTMENTS
        WHERE
            DEPARTMENT_ID = V_DEPTNO;

    END LOOP;

    FOR I IN 1..F_LOOP_COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(I
                             || '-'
                             || MY_DEPT_TABLE(I));
    END LOOP;

END;

While

DECLARE
    V_COUNTRY_ID LOCATIONS.COUNTRY_ID%TYPE := 'CA';
    V_LOC_ID     LOCATIONS.LOCATION_ID%TYPE;
    V_NEW_CITY   LOCATIONS.CITY%TYPE := 'Montreal';
    V_COUNTER    NUMBER := 1;
BEGIN
    SELECT
        MAX(LOCATION_ID)
    INTO V_LOC_ID
    FROM
        LOCATIONS
    WHERE
        COUNTRY_ID = V_COUNTRY_ID;

    WHILE V_COUNTER <= 3 LOOP
        INSERT INTO LOCATIONS (
            LOCATION_ID,
            CITY,
            COUNTRY_ID
        ) VALUES (
            V_LOC_ID + V_COUNTER,
            V_NEW_CITY,
            V_COUNTRY_ID
        );

        V_COUNTER := V_COUNTER + 1;
        DBMS_OUTPUT.PUT_LINE(V_COUNTER - 1 || ' rows added.');
    END LOOP;

END;