Posts
كل كلمات امتحان الـ IELTS اللى هتحتاجها
12 years ago
Aliasing from Passing Global Variable with NOCOPY Hint
DECLARE
TYPE definition IS RECORD(word VARCHAR2(20),
meaning VARCHAR2(200));
TYPE dictionary IS VARRAY(2000) OF DEFINITION;
lexicon DICTIONARY := Dictionary();
PROCEDURE Add_entry
(word_list IN OUT NOCOPY DICTIONARY)
IS
BEGIN
Word_list(1).word := 'aardvark';
Lexicon(1).word := 'aardwolf';
END;
BEGIN
lexicon.extend;
Add_entry(lexicon);
dbms_output.Put_line(Lexicon(1).word);
END;
/
OUTPUT :-
aardwolf
PL/SQL procedure successfully completed.
Resolving PL/SQL Functions With Inheritance
CREATE OR REPLACE TYPE super_t AS OBJECT
(n NUMBER
) NOT FINAL;
/
CREATE OR REPLACE TYPE sub_t UNDER super_t
(n2 NUMBER
) NOT FINAL;
/
CREATE OR REPLACE TYPE final_t UNDER sub_t
(n3 NUMBER
);
/
CREATE OR REPLACE PACKAGE p
IS
FUNCTION func(arg SUPER_T) RETURN NUMBER;
FUNCTION func(arg SUB_T) RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY p
IS
FUNCTION Func
(arg SUPER_T)
RETURN NUMBER
IS
BEGIN
RETURN 1;
END;
FUNCTION Func
(arg SUB_T)
RETURN NUMBER
IS
BEGIN
RETURN 2;
END;
END;
/
DECLARE
v FINAL_T := Final_t(1,2,3);
BEGIN
dbms_output.Put_line(p.Func(v)); -- prints 2
END;
/
OUTPUT :-
Resolving PL/SQL Procedure Names
DECLARE
PROCEDURE Swap
(n1 NUMBER,
n2 NUMBER)
IS
num1 NUMBER;
num2 NUMBER;
FUNCTION Balance
(bal NUMBER)
RETURN NUMBER
IS
x NUMBER := 10;
PROCEDURE Swap
(d1 DATE,
d2 DATE)
IS
BEGIN
NULL;
END;
PROCEDURE Swap
(b1 BOOLEAN,
b2 BOOLEAN)
IS
BEGIN
NULL;
END;
BEGIN
dbms_output.Put_line('The following raises an error');
-- swap(num1, num2); wrong number or types of arguments in call to 'SWAP'
RETURN x;
END balance;
BEGIN
NULL;
END swap;
BEGIN
NULL;
END;
/
Overloading a Subprogram Name
DECLARE
TYPE datetabtyp IS TABLE OF DATE INDEX BY PLS_INTEGER;
TYPE numtabtyp IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
hiredate_tab DATETABTYP;
sal_tab NUMTABTYP;
PROCEDURE Initialize
(tab OUT DATETABTYP,
n INTEGER)
IS
BEGIN
FOR i IN 1.. n LOOP
Tab(i) := SYSDATE;
END LOOP;
END initialize;
PROCEDURE Initialize
(tab OUT NUMTABTYP,
n INTEGER)
IS
BEGIN
FOR i IN 1.. n LOOP
Tab(i) := 0.0;
END LOOP;
END initialize;
BEGIN
Initialize(hiredate_tab,50); -- calls first (DateTabTyp) version
Initialize(sal_tab,100); -- calls second (NumTabTyp) version
END;
/
Procedure with Default Parameter Values
DECLARE
emp_num NUMBER(6) := 120;
bonus NUMBER(6);
merit NUMBER(4);
PROCEDURE Raise_salary
(emp_id IN NUMBER,
amount IN NUMBER DEFAULT 100,
extra IN NUMBER DEFAULT 50)
IS
BEGIN
UPDATE employees
SET salary = salary + amount + extra
WHERE employee_id = emp_id;
END raise_salary;
BEGIN
Raise_salary(120); -- same as raise_salary(120, 100, 50)
Raise_salary(emp_num,extra => 25); -- same as raise_salary(120, 100, 25)
END;
/
Using the OUT Mode
DECLARE
emp_num NUMBER(6) := 120;
bonus NUMBER(6) := 50;
emp_last_name VARCHAR2(25);
PROCEDURE Raise_salary
(emp_id IN NUMBER,
amount IN NUMBER,
emp_name OUT VARCHAR2)
IS
BEGIN
UPDATE employees
SET salary = salary + amount
WHERE employee_id = emp_id;
SELECT last_name
INTO emp_name
FROM employees
WHERE employee_id = emp_id;
END raise_salary;
BEGIN
Raise_salary(emp_num,bonus,emp_last_name);
dbms_output.Put_line('Salary has been updated for: '
||emp_last_name);
END;
/
OUTPUT :-
Salary has been updated for: Weiss
PL/SQL procedure successfully completed.
Subprogram Calls Using Positional, Named, and Mixed Notation
DECLARE
emp_num NUMBER(6) := 120;
bonus NUMBER(6) := 50;
PROCEDURE Raise_salary
(emp_id NUMBER,
amount NUMBER)
IS
BEGIN
UPDATE employees
SET salary = salary + amount
WHERE employee_id = emp_id;
END raise_salary;
BEGIN
Raise_salary(emp_num,bonus); -- positional procedure call for actual parameters
Raise_salary(amount => bonus,emp_id => emp_num); -- named parameters
Raise_salary(emp_num,amount => bonus); -- mixed parameters
END;
/
Formal Parameters and Actual Parameters
DECLARE
emp_num NUMBER(6) := 120;
bonus NUMBER(6) := 100;
merit NUMBER(4) := 50;
PROCEDURE Raise_salary
(emp_id NUMBER,
amount NUMBER)
IS
BEGIN
UPDATE employees
SET salary = salary + amount
WHERE employee_id = emp_id;
END raise_salary;
BEGIN
Raise_salary(emp_num,bonus); -- procedure call specifies actual parameters
Raise_salary(emp_num,merit + bonus); -- expressions can be used as parameters
END;
/
Simple PL/SQL Procedure
DECLARE
in_string VARCHAR2(100) := 'This is my test string.';
out_string VARCHAR2(200);
PROCEDURE Double
(original IN VARCHAR2,
new_string OUT VARCHAR2)
AS
BEGIN
new_string := original
||' + '
||original;
EXCEPTION
WHEN value_error THEN
dbms_output.Put_line('Output buffer not long enough.');
END;
BEGIN
Double(in_string,out_string);
dbms_output.Put_line(in_string
||' - '
||out_string);
END;
/
OUTPUT :-
This is my test string. - This is my test string. + This is my test string.
PL/SQL procedure successfully completed.
Dynamic SQL Fetching into a Record
DECLARE
TYPE empcurtyp IS REF CURSOR;
emp_cv EMPCURTYP;
emp_rec employees%ROWTYPE;
sql_stmt VARCHAR2(200);
v_job VARCHAR2(10) := 'ST_CLERK';
BEGIN
sql_stmt := 'SELECT * FROM employees WHERE job_id = :j';
OPEN emp_cv FOR sql_stmt USING v_job;
LOOP
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
dbms_output.Put_line('Name: '
||emp_rec.last_name
||' Job Id: '
||emp_rec.job_id);
END LOOP;
CLOSE emp_cv;
END;
/
OUTPUT :-
Name: Nayer Job Id: ST_CLERK
Name: Mikkilineni Job Id: ST_CLERK
Name: Landry Job Id: ST_CLERK
Name: Markle Job Id: ST_CLERK
Name: Bissot Job Id: ST_CLERK
Name: Atkinson Job Id: ST_CLERK
Name: Marlow Job Id: ST_CLERK
Name: Olson Job Id: ST_CLERK
Name: Mallin Job Id: ST_CLERK
Name: Rogers Job Id: ST_CLERK
Name: Gee Job Id: ST_CLERK
Name: Philtanker Job Id: ST_CLERK
Name: Ladwig Job Id: ST_CLERK
Name: Stiles Job Id: ST_CLERK
Name: Seo Job Id: ST_CLERK
Name: Patel Job Id: ST_CLERK
Name: Rajs Job Id: ST_CLERK
Name: Davies Job Id: ST_CLERK
Name: Matos Job Id: ST_CLERK
Name: Vargas Job Id: ST_CLERK
PL/SQL procedure successfully completed.
Accessing %ROWCOUNT For an Explicit Cursor
DECLARE
TYPE cursor_ref IS REF CURSOR;
c1 CURSOR_REF;
TYPE emp_tab IS TABLE OF employees%ROWTYPE;
rec_tab EMP_TAB;
rows_fetched NUMBER;
BEGIN
OPEN c1 FOR 'SELECT * FROM employees';
FETCH c1 BULK COLLECT INTO rec_tab;
rows_fetched := c1%ROWCOUNT;
dbms_output.Put_line('Number of employees fetched: '
||To_char(rows_fetched));
END;
/
Output :-
Number of employees fetched: 107
PL/SQL procedure successfully completed.
Dynamic SQL Inside FORALL Statement
DECLARE
TYPE NumList IS TABLE OF NUMBER;
TYPE NameList IS TABLE OF VARCHAR2(15);
empids NumList;
enames NameList;
BEGIN
empids := NumList(101,102,103,104,105);
FORALL i IN 1..5
EXECUTE IMMEDIATE
'UPDATE employees SET salary = salary * 1.04 WHERE employee_id = :1
RETURNING last_name INTO :2'
USING empids(i) RETURNING BULK COLLECT INTO enames;
END;
/
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;
/
Declaring an Autonomous Function in a Package
CREATE OR REPLACE PACKAGE emp_actions
AS -- package specification
FUNCTION raise_salary(emp_id NUMBER,sal_raise NUMBER) RETURN NUMBER;
END emp_actions;
/
CREATE OR REPLACE PACKAGE BODY emp_actions
AS -- package body
-- code for function raise_salary
FUNCTION Raise_salary
(emp_id NUMBER,
sal_raise NUMBER)
RETURN NUMBER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
new_sal NUMBER(8,2);
BEGIN
UPDATE employees
SET salary = salary
+ sal_raise
WHERE employee_id = emp_id;
COMMIT;
SELECT salary
INTO new_sal
FROM employees
WHERE employee_id = emp_id;
RETURN new_sal;
END raise_salary;
END emp_actions;
/
