View + PL SQL

CREATE  OR REPLACE VIEW empInfo_vw AS
SELECT employee_id, last_name, department_name, city, state_province, country_name,region_name
FROM departments d
JOIN employees e ON d.department_id = e.department_id
JOIN locations l ON d.location_id = l.location_id
JOIN countries c ON l.country_id = c.country_id
JOIN regions r ON c.region_id = r.region_id;



SELECT last_name, department_name, country_name,region_name
FROM empInfo_vw
WHERE employee_id = 100;

DESC empInfo_vw


SELECT text FROM user_views WHERE view_name = 'EMPINFO_VW';


CREATE OR REPLACE VIEW empInfo_dept50_vw AS
SELECT employee_id,first_name,last_name,email,phone_number,hire_date,job_title,department_name
FROM employees e JOIN departments d ON d.department_id = e.department_id
JOIN jobs j ON e.job_id  = j.job_id
WHERE d.department_id = 50;

SELECT * FROM empInfo_dept50_vw;



CREATE SEQUENCE department_id_seq
START WITH 290
INCREMENT BY 10;

DROP SEQUENCE department_id_seq



INSERT INTO departments
VALUES (department_id_seq.NEXTVAL, 'IT Education', 103,1400);


SET SERVEROUTPUT ON

BEGIN
DBMS_OUTPUT.PUT_LINE('안녕');
END;


DECLARE
I_MESSAGE VARCHAR2(100);
BEGIN I_MESSAGE:= 'HELLO WORLD';
DBMS_OUTPUT.PUT_LINE(I_MESSAGE);
END;

CREATE OR REPLACE PROCEDURE hello_world
IS
I_MESSAGE VARCHAR2(100):='HELLO WORLD!';
BEGIN
DBMS_OUTPUT.PUT_LINE(I_message);
END;


BEGIN
hello_world;
END;

CREATE OR REPLACE PROCEDURE hello_world(p_message IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(p_message);
END;

DROP PROCEDURE hello_world;

BEGIN
hello_world('김동규');
END