exam1)
SELECT 쿼리를 프로시저로 작성
CREATE OR REPLACE PROCEDURE pEMP
IS
vname employees.last_name%TYPE;
BEGIN
SELECT last_name INTO vname FROM employees WHERE employee_id=100;
DBMS_OUTPUT.PUT_LINE(vname);
END;
/
SET SERVEROUTPUT ON;
EXEC pEMP;
exam2)
VARIABLE X VARCHAR2(25)
BEGIN
:X := 'Abel';
END;
/
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY
FROM EMPLOYEES
WHERE LAST_NAME = :X;
EMPLOYEE_ID,FIRST_NAME , SALARY
-----------,--------------------,----------
174,Ellen , 11000
exam2)
--INSERT 쿼리를 프로시저로 작성
create table emp7 (id,ln,fn,sal)
as
select employee_id,last_name,first_name,salary
from employees
where 1=2;
CREATE OR REPLACE PROCEDURE pEMP7
(
vid IN employees.employee_id%TYPE
,vln IN employees.last_name%TYPE
,vfn IN employees.first_name%TYPE
,vsal IN employees.salary%TYPE)
IS
BEGIN
INSERT INTO emp7 (id, ln, fn, sal)
VALUES (vid, vln, vfn, vsal);
COMMIT;
END;
/
EXEC pEMP7(100, 'King', 'Steven', '5000');
select * from emp7;
----------------------------------------
exam3)
DROP TABLE employees7;
DROP TABLE emp_audit;
DROP TRIGGER audit_sal;
CREATE TABLE employees7 as SELECT * FROM employees;
CREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE,
new_sal NUMBER(8,2), old_sal NUMBER(8,2) );
CREATE OR REPLACE TRIGGER audit_sal
AFTER UPDATE OF salary ON employees7 FOR EACH ROW
BEGIN
-- bind variables are used here for values
INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE,
:new.salary, :old.salary );
END;
/
UPDATE employees7
SET salary=4000
WHERE employee_id=100;
SELECT * FROM emp_audit;
------------------------------------------