lab1plsql

--1
SET serveroutput ON;

DECLARE
--v_x NUMBER(4,2) := &x;
--v_y NUMBER(4,3) := &y;

--v_xx VARCHAR(20) := '&xx';
--v_yy VARCHAR(10) := '&yy';

v_max NUMBER := &m;
v_start NUMBER := 0;

BEGIN

  --dbms_output.put_line(v_x + v_y);
  --dbms_output.put_line(v_xx || v_yy);
  --LOOP
  --  dbms_output.put(v_start);
  --  dbms_output.put(' ');
  --  EXIT WHEN v_start = v_max;
  --  v_start := v_start + 1;
  --END LOOP;

  --WHILE v_start < v_max LOOP
  --  dbms_output.put(v_start);
  --  dbms_output.put(' ');
  --  v_start := v_start + 1;
  --END LOOP;

  FOR i IN v_start .. v_max LOOP
    IF i MOD 2 = 0 THEN
      DBMS_OUTPUT.put(i);
      DBMS_OUTPUT.put(' ');
    END IF;
  END LOOP;

  DBMS_OUTPUT.put_line(' ');

END;

/

SET serveroutput ON

DECLARE
v_ok NUMBER := 0;
v_max NUMBER := &MAX;
v_start NUMBER := 2;

BEGIN

  FOR i IN v_start .. v_max LOOP
    v_ok := 0;
    FOR j IN 2 .. SQRT(i) LOOP
      IF (MOD(i,j) = 0) THEN v_ok := 1;
      END IF;
    END LOOP;
    IF(v_ok = 0) THEN DBMS_OUTPUT.put_line(i);
    END IF;
  END LOOP;

END;

/

SET serveroutput ON;

DECLARE
v_salary NUMBER;
v_exists NUMBER;

BEGIN

  SELECT COUNT(salary) INTO v_exists FROM employees WHERE employee_id = 25;
  IF (v_exists > 0) THEN
    SELECT salary INTO v_salary FROM employees WHERE employee_id = 25;
    DBMS_OUTPUT.put_line(v_salary);
  ELSIF (v_exists = 0) THEN
    DBMS_OUTPUT.put_line('Nu exista acest angajat!');
  END IF;

END;

/

CREATE OR REPLACE FUNCTION get_sal_t(id NUMBER) RETURN NUMBER IS
v_salary NUMBER;
v_exists NUMBER;
BEGIN

  SELECT COUNT(salary) INTO v_exists FROM employees WHERE employee_id = id;
  IF (v_exists > 0) THEN
    SELECT salary INTO v_salary FROM employees WHERE employee_id = id;
    RETURN v_salary;
  ELSIF (v_exists = 0) THEN
    RETURN -1;
  END IF;

END;

/

SELECT get_sal_t(120) FROM dual;

/

SELECT get_sal_t(employee_id) FROM employees;

/

CREATE VIEW emp_view AS (
  SELECT last_name, salary, hire_date FROM employees
);

DROP VIEW emp_view;

CREATE VIEW emp_view AS (
  SELECT last_name a, salary b, hire_date c FROM employees
);

/

SET serveroutput ON;

DECLARE
v_emp emp_view%ROWTYPE;

BEGIN

  SELECT last_name, salary, hire_date INTO v_emp FROM employees WHERE department_id = 90 AND employee_id = 102;
  DBMS_OUTPUT.put_line('Employee: ' || v_emp.a);
  DBMS_OUTPUT.put_line('Salary:' || v_emp.b);
  DBMS_OUTPUT.put_line('Hire date: ' || v_emp.c);

END;

/

VARIABLE v_sql NUMBER;
--UPDATE employees SET salary = salary + 5 WHERE employee_id = 190;
--RETURNING salary INTO :v_sql;

--rollback;
/
BEGIN

   SELECT salary INTO :v_sql
   FROM employees
   WHERE employee_id = 190;

END;
/
print v_sql

/