Posts
كل كلمات امتحان الـ IELTS اللى هتحتاجها
14 years ago
Dynamic SQL with BULK COLLECT INTO Clause
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
TYPE NumList IS TABLE OF NUMBER;
TYPE NameList IS TABLE OF VARCHAR2(25);
emp_cv EmpCurTyp;
empids NumList;
enames NameList;
sals NumList;
BEGIN
OPEN emp_cv FOR 'SELECT employee_id, last_name FROM employees';
FETCH emp_cv BULK COLLECT INTO empids, enames;
CLOSE emp_cv;
EXECUTE IMMEDIATE 'SELECT salary FROM employees'
BULK COLLECT INTO sals;
END;
/
Dynamic SQL Procedure that Accepts Table Name and WHERE Clause
DROP TABLE employees_temp CASCADE CONSTRAINTS PURGE;
CREATE TABLE employees_temp
AS
SELECT *
FROM employees;
CREATE OR REPLACE PROCEDURE Delete_rows
(table_name IN VARCHAR2,
condition IN VARCHAR2 DEFAULT NULL)
AS
where_clause VARCHAR2(100) := ' WHERE '
||condition;
v_table VARCHAR2(30);
BEGIN
-- first make sure that the table actually exists; if not, raise an exception
SELECT object_name
INTO v_table
FROM user_objects
WHERE object_name = Upper(table_name)
AND object_type = 'TABLE';
IF condition IS NULL THEN
where_clause := NULL;
END IF;
EXECUTE IMMEDIATE 'DELETE FROM '|| v_table || where_clause;
EXCEPTION
WHEN no_data_found THEN
dbms_output.Put_line('Invalid table: '
||table_name);
END;
/
BEGIN
Delete_rows('employees_temp','employee_id = 111');
END;
/
Calling an Autonomous Function
DROP TABLE debug_output CASCADE CONSTRAINTS PURGE;
-- create the debug table
CREATE TABLE debug_output ( msg VARCHAR2(200));
-- create the package spec
DROP PACKAGE Debugging ;
CREATE OR REPLACE PACKAGE debugging
AS
FUNCTION log_msg(msg VARCHAR2) RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(log_msg,wnds,rnds);
END debugging;
/
DROP PACKAGE BODY Debugging ;
-- create the package body
CREATE OR REPLACE PACKAGE BODY debugging
AS
FUNCTION Log_msg
(msg VARCHAR2)
RETURN VARCHAR2
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- the following insert does not violate the constraint
-- WNDS because this is an autonomous routine
INSERT INTO debug_output
VALUES (msg);
COMMIT;
RETURN msg;
END;
END debugging;
/
-- call the packaged function from a query
DECLARE
my_emp_id NUMBER(6);
my_last_name VARCHAR2(25);
my_count NUMBER;
BEGIN
my_emp_id := 120;
SELECT debugging.Log_msg(last_name)
INTO my_last_name
FROM employees
WHERE employee_id = my_emp_id;
-- even if you roll back in this scope, the insert into 'debug_output' remains
-- committed because it is part of an autonomous transaction
ROLLBACK;
END;
/
Using Autonomous Triggers
DROP TABLE emp_audit CASCADE CONSTRAINTS PURGE;
CREATE TABLE emp_audit (
emp_audit_id NUMBER(6),
up_date DATE,
new_sal NUMBER(8,2),
old_sal NUMBER(8,2));
-- create an autonomous trigger that inserts into the audit table before
-- each update of salary in the employees table
CREATE OR REPLACE TRIGGER audit_sal
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO emp_audit
VALUES (:old.employee_id,
SYSDATE,
:new.salary,
:old.salary);
COMMIT;
END;
/
-- update the salary of an employee, and then commit the insert
UPDATE employees
SET salary = salary
* 1.05
WHERE employee_id = 115;
COMMIT;
-- update another salary, then roll back the update
UPDATE employees
SET salary = salary
* 1.05
WHERE employee_id = 116;
ROLLBACK;
-- show that both committed and rolled-back updates add rows to audit table
SELECT *
FROM emp_audit
WHERE emp_audit_id = 115
OR emp_audit_id = 116;
OUTPUT :-
Declaring an Autonomous Trigger
DROP TABLE emp_audit CASCADE CONSTRAINTS PURGE;
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 employees
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- bind variables are used here for values
INSERT INTO emp_audit
VALUES (:old.employee_id,
SYSDATE,
:new.salary,
:old.salary);
COMMIT;
END;
/
