PLSQL

set serveroutput on

DECLARE
	v_weight NUMBER(3) := 600;
	v_message VARCHAR2(255) := 'Product 10012';
	v_new_locn VARCHAR2(255) := 'America';
BEGIN
	DECLARE
		v_weight NUMBER(3) := 1;
		v_message VARCHAR2(255) := 'Product 11001';
		v_new_locn VARCHAR2(50) := 'Europe';
	BEGIN
		v_weight := v_weight + 1;
		v_message := 'Western ' || v_new_locn;
		DBMS_OUTPUT.PUT_LINE('variable x posicion 1 ' || v_weight);
		DBMS_OUTPUT.PUT_LINE('variable x posicion 1 ' || v_new_locn);
	END;

	v_weight := v_weight + 1;
	v_message := v_message || ' is in stock';
	v_new_locn := 'Western ' || v_new_locn;

	DBMS_OUTPUT.PUT_LINE('variable x posicion 2 ' || v_weight);
	DBMS_OUTPUT.PUT_LINE('variable x posicion 2 ' || v_message);
	DBMS_OUTPUT.PUT_LINE('variable x posicion 2 ' || v_new_locn);
END;
/

DECLARE
	v_customer varchar2(50) := 'Womansport';
	v_credit_rating varchar2(50) := 'EXCELLENT';
	v_name varchar2(50) := 'HELLO';
BEGIN
	DECLARE
		v_customer number(7) := 201;
		v_name varchar2(25) := 'Unisport';
	BEGIN
		DBMS_OUTPUT.PUT_LINE('INNER BLOCK v_customer is ' || v_customer);
		DBMS_OUTPUT.PUT_LINE('INNER BLOCK v_name is ' || v_name);
		DBMS_OUTPUT.PUT_LINE('INNER BLOCK v_credit_rating is ' || v_credit_rating);
	END;

	DBMS_OUTPUT.PUT_LINE('OUTER BLOCK v_customer is ' || v_customer);
	DBMS_OUTPUT.PUT_LINE('OUTER BLOCK v_name is ' || v_name);
	DBMS_OUTPUT.PUT_LINE('OUTER BLOCK v_credit_rating is ' || v_credit_rating);

	DBMS_OUTPUT.PUT_LINE('OUTER BLOCK  is ' || );

END;
/

DECLARE
	v_result number := &p_num1 / &p_num2;
BEGIN
	v_result := v_result + &p_num2;
	DBMS_OUTPUT.PUT_LINE('RESULT: ' || v_result);
END;
/

DECLARE
	v_deptono number;
	v_location_id number;
BEGIN
	select department_id, location_id
	into v_deptono, v_location_id
	from departments
	where department_name like '%S%';
	DBMS_OUTPUT.PUT_LINE('v_deptono ' || v_deptono);
	DBMS_OUTPUT.PUT_LINE('v_location_id ' || v_location_id);
END;
/

DECLARE
	v_sum_sal number;
	v_deptono number not null := 60;
BEGIN
	select sum(salary)
	into v_sum_sal
	from employees
	where department_id = v_deptono;
	DBMS_OUTPUT.PUT_LINE('The sum salary is ' || TO_CHAR(v_sum_sal));
END;
/

select sum(salary)
	into v_sum_sal
	from employees
	where department_id = v_deptono;

DECLARE
	v_hire_date employees.hire_date%TYPE;
	v_sysdate v_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 = v_employee_id;
END;
/

create table emp_copy as select * from employees e where e.department_id=80;

merge into emp_copy c
	using employees e
	on (e.employee_id = c.employee_id)
	when matched then
		update set
			c.salary = e.salary
	when not matched then
		insert values(e.employee_id, e.first_name, e.last_name, e.email, e.phone_number, e.hire_date, e.job_id, e.salary, e.commission_pct, e.manager_id, e.department_id);

select salary, department_id, from emp_copy where department_id = 80;

VARIABLE rows_deleted VARCHAR2(30)
SET SERVEROUTPUT ON
DECLARE
	v_employee_id emp_copy.employee_id%TYPE := 176;
BEGIN
	DELETE FROM emp_copy
	WHERE employee_id = v_employee_id;
	UPDATE emp_copy
	SET salary = 10000;
	:rows_deleted := (SQL%ROWCOUNT || ' row deleted.');
END;
/
PRINT rows_deleted

SET SERVEROUTPUT ON
DECLARE
	v_grade CHAR(1) := UPPER('&p_grade');
	v_appraisal VARCHAR2(20);
BEGIN
	v_appraisal :=
		CASE v_grade
			WHEN 'A' THEN 'Excellent'
			WHEN 'B' THEN 'Very Good'
			WHEN 'C' THEN 'Good'
			ELSE 'No such grade'
		END;
	DBMS_OUTPUT.PUT_LINE('Grade: ' || v_grade || ' Appraisal ' || v_appraisal);
END;
/

DECLARE
	v_var1 BOOLEAN := FALSE;
	v_var2 BOOLEAN := NULL;
BEGIN
	IF v_var1 AND v_var2 THEN
		DBMS_OUTPUT.PUT_LINE('v_var1' || ' AND ' || 'v_var2');
	ELSIF v_var1 OR v_var2 THEN
		DBMS_OUTPUT.PUT_LINE('v_var1' || ' OR ' || 'v_var2');
	ELSIF (v_var1 OR v_var2) = NULL THEN
		DBMS_OUTPUT.PUT_LINE('v_var1' || ' OR' || 'v_var2');
	ELSIF (v_var1 AND v_var2) = FALSE THEN
		DBMS_OUTPUT.PUT_LINE('v_var1' || ' AND ' || 'v_var2');
	END IF;
END;
/

DECLARE
	v_country_id locations.country_id%TYPE := 'CA';
	v_location_id locations.location_id%TYPE;
	v_counter NUMBER(2) := 1;
	v_city locations.city%TYPE := 'Montreal';
BEGIN
	SELECT MAX(location_id) INTO v_location_id FROM locations
	WHERE country_id = v_country_id;
	LOOP
		INSERT INTO locations(location_id, city, country_id)
		VALUES ((v_location_id + v_counter), v_city, v_country_id);
		v_counter := v_counter + 1;
		EXIT WHEN v_counter > 3;
	END LOOP;
END;
/

DECLARE
	v_country_id locations.country_id%TYPE := 'CA';
	v_location_id locations.location_id%TYPE;
	v_counter NUMBER(2) := 1;
	v_city locations.city%TYPE := 'Montreal';
BEGIN
	SELECT MAX(location_id) INTO v_location_id FROM locations
	WHERE country_id = v_country_id;
	WHILE v_counter <= 3 LOOP
		INSERT INTO locations(location_id, city, country_id)
		VALUES ((v_location_id + v_counter), v_city, v_country_id);
		v_counter := v_counter + 1;
	END LOOP;
END;
/

DECLARE
	v_country_id locations.country_id%TYPE := 'CA';
	v_location_id locations.location_id%TYPE;
	v_city locations.city%TYPE := 'Montreal';
BEGIN
	SELECT MAX(location_id) INTO v_location_id FROM locations
	WHERE country_id = v_country_id;
	FOR i IN 1..3 LOOP
		INSERT INTO locations(location_id, city, country_id)
		VALUES ((v_location_id + i), v_city, v_country_id);
	END LOOP;
END;
/