# PL/SQL

## Structure

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

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

## Variable declarations <a href="#d13284e11" id="d13284e11"></a>

### %TYPE

```plsql
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;
```

```plsql
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

```plsql
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

```plsql
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

```plsql
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

```plsql
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

```plsql
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

```plsql
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

```plsql
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;
```

```plsql
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

```plsql
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

```plsql
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

```plsql
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;
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.datahungry.dev/sql/pl-sql.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.