PL SQL 2

GREATEST() - наибольшее значение из списка
LEAST() - наименьшее
LENGTH - длина строки
LOWER -  в нижний регистр
TO_CHAR, TO_DATE, TO_NUMBER, TO_TIMESTAMP

вложенные блоки:
<<my_block_1>>
DECLARE
    value NUMBER := 10;
BEGIN
    DECLARE
        value NUMBER :=20;
    BEGIN
        dbms_output.put_line(my_block_1.value);
    END;
END;

** - возведение в степень




4.
--------------------------------------------------------------
set serveroutput on
DECLARE
    val_first_name EMPLOYEES.FIRST_NAME%TYPE;
    val_last_name  EMPLOYEES.LAST_NAME%TYPE;
BEGIN
    SELECT first_name, last_name
        INTO val_first_name, val_last_name
        FROM employees
        WHERE email = 'BERNST';
    dbms_output.put_line('val_first_name, val_last_name is '
        ||val_first_name||' '||val_last_name );
END;
/
--------------------------------------------------------------
set serveroutput on
DECLARE
    fond_salary EMPLOYEES.SALARY%TYPE;
    dept_id  EMPLOYEES.DEPARTMENT_ID%TYPE NOT NULL :=50;
BEGIN
    SELECT SUM(salary) 
        INTO fond_salary
        FROM employees
        WHERE DEPARTMENT_ID = dept_id;
    dbms_output.put_line('fond_salary is  '
        ||fond_salary);
END;
/     
---------------------------------------------------------------
set serveroutput on
DECLARE
    reg_id REGIONS.REGION_ID%type := 5;
    reg_name REGIONS.REGION_NAME%type := 'Arctica';
BEGIN
    INSERT INTO REGIONS 
        (REGION_ID, region_name)
        VALUES(reg_id, reg_name);
END;
/ 
---------------------------------------------------------------
set serveroutput on
DECLARE
    sal EMPLOYEES.salary%type := 10000;
BEGIN
    UPDATE EMPLOYEES
    SET EMPLOYEES.SALARY = EMPLOYEES.SALARY * 2
    WHERE EMPLOYEES.SALARY < sal;
END;
/
----------------------------------------------------------------
set serveroutput on
DECLARE
    name EMPLOYEES.FIRST_NAME%type := 'Ki';
BEGIN
    DELETE FROM  EMPLOYEES
    WHERE FIRST_NAME = name;
END;
/ 
----------------------------------------------------------------

Курсоры
----------------------------------------------------------------
set serveroutput on
DECLARE
    sal EMPLOYEES.salary%type := 10000;
BEGIN
    UPDATE EMPLOYEES
    SET EMPLOYEES.SALARY = EMPLOYEES.SALARY * 2
    WHERE EMPLOYEES.SALARY < sal;
     dbms_output.put_line(sql%rowcount ||' rows updated ');
END;
/

output: 94 rows updated
----------------------------------------------------------------
sql%found - истина, если обработано более одной строки
sql%notfound - истина, если ни одной строки не обработано
----------------------------------------------------------------

операторы
---------------------------------------------------------------------
set serveroutput on
DECLARE
    sal employees.salary%type;
    id_ employees.employee_id%type := 117;
BEGIN
    SELECT salary INTO sal FROM EMPLOYEES WHERE employee_id = id_;
    IF sal >18000 THEN
        dbms_output.put_line(sal ||' is very good salary ');
    ELSIF sal >15000 THEN
        dbms_output.put_line(sal ||' is good salary ');
    ELSE
        dbms_output.put_line(sal ||' is not good salary ');
    END IF;
END;
/
----------------------------------------------------------------------
set serveroutput on
DECLARE
    sal employees.salary%type;
    id_ employees.employee_id%type := 127;
    out_ varchar2(25);
BEGIN
    SELECT salary INTO sal FROM EMPLOYEES WHERE employee_id = id_;
    out_ := CASE WHEN sal > 18000 THEN  ' is very good salary '
                 WHEN sal > 15000 THEN  ' is good salary '
                 ELSE                   ' is not good salary ' 
            END;
            dbms_output.put_line(sal || out_);
END;
/
--------------------------------------------------------------------------------
set serveroutput on
DECLARE 
    sal employees.salary%type;
    id_ employees.employee_id%type := 121;
BEGIN
    SELECT salary INTO sal FROM EMPLOYEES WHERE employee_id = id_;
    CASE sal
        WHEN  19200 THEN 
                          dbms_output.put_line(sal || ' is very good salary ');
        WHEN  16400 THEN 
                          dbms_output.put_line(sal || ' is good salary ');
        ELSE              dbms_output.put_line(sal || ' is not good salary ');     
    END CASE;
END;
/ 
---------------------------------------------------------------------------------
set serveroutput on
DECLARE 
    sal employees.salary%type;
    id_ employees.employee_id%type := 121;
BEGIN
    SELECT salary INTO sal FROM EMPLOYEES WHERE employee_id = id_;
    CASE
        WHEN  sal >= 19200 THEN 
                          dbms_output.put_line(sal || ' is very good salary ');
        WHEN  sal >= 16400 THEN 
                          dbms_output.put_line(sal || ' is good salary ');
        ELSE              dbms_output.put_line(sal || ' is not good salary ');     
    END CASE;
END;
/ 
-----------------------------------------------------------------------------------