1> Make a simple anon block , where we would declare variables of all types. specify which are SQL types and which are PL/SQL. DECLARE sales number(10, 2); name varchar(25); address varchar2(100); a integer := 10; p1 PLS_INTEGER := 2147; f1 float := 3.0067; f2 real :=43.778899; birthDate DATE; checkout TIMESTAMP := '22-JUN-2014 07:48:53.275'; indiaTime TIMESTAMP with time zone := '22-JUN-2014 07:48:53 AM +5:30'; BEGIN dbms_output.put_line('Just declaring data types ' || ' hi from Rocky sir'); dbms_output.put_line( checkout ); dbms_output.put_line( indiaTime ); end; / -------------------------------------------------------------------- 2> About PLS_INTEGER DATA TYPE. Is SIMPLE_INTEGER better ? PLS_INTEGER is a SUB TYPE on INTEGER introduced from Oracle 10g SIMPLE_INTEGER is a SUB TYPE on PLS_INTEGER introduced from Oracle 11g From Programmer Usage INTEGER , int , PLS_INTEGER and SIMPLE_INTEGER are all same. But internally SIMPLE_INTEGER working is much faster. ( twice as efficient to PLS_INTEGER or INTEGER ) The PLS_INTEGER data type stores signed integers in the range -2,147,483,648 through 2,147,483,647, represented in 32 bits. The PLS_INTEGER data type has an advantage over the NUMBER data type and NUMBER subtypes : PLS_INTEGER values require less storage. Yes, SIMPLE_INTEGER is best of all. Introduced from oracle 11g. ------- SIMPLE_INTEGER Subtype of PLS_INTEGER. SIMPLE_INTEGER is a predefined subtype of the PLS_INTEGER data type that has the same range as PLS_INTEGER (-2,147,483,648 through 2,147,483,647) and has a NOT NULL constraint. It differs significantly from PLS_INTEGER in its overflow semantics. eg : showing SIMPLE_INTEGER is better than PLS_INTEGER The SIMPLE_INTEGER datatype is a subtype of the PLS_INTEGER datatype and can dramatically increase the speed of integer arithmetic in natively compiled code, but only shows marginal performance improvements in interpreted code. The following procedure compares the performance of the SIMPLE_INTEGER and PLS_INTEGER datatypes. CREATE OR REPLACE PROCEDURE simple_integer_test_proc AS l_start NUMBER; l_loops NUMBER := 10000000; l_pls_integer PLS_INTEGER := 0; l_pls_integer_incr PLS_INTEGER := 1; l_simple_integer SIMPLE_INTEGER := 0; l_simple_integer_incr SIMPLE_INTEGER := 1; BEGIN l_start := DBMS_UTILITY.get_time; -- finds current time in 1/100th of a second FOR i IN 1 .. l_loops LOOP l_pls_integer := l_pls_integer + l_pls_integer_incr; END LOOP; DBMS_OUTPUT.put_line('PLS_INTEGER: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs'); l_start := DBMS_UTILITY.get_time; FOR i IN 1 .. l_loops LOOP l_simple_integer := l_simple_integer + l_simple_integer_incr; END LOOP; DBMS_OUTPUT.put_line('SIMPLE_INTEGER: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs'); END simple_integer_test_proc; / When run in the default interpreted mode the performance improvement of the SIMPLE_INTEGER datatype is not spectacular. SQL> SET SERVEROUTPUT ON SQL> EXEC simple_integer_test_proc; PLS_INTEGER: 47 hsecs SIMPLE_INTEGER: 44 hsecs PL/SQL procedure successfully completed. SQL> We natively compile the procedure by altering the PLSQL_CODE_TYPE value for the session and recompiling the procedure. ALTER SESSION SET PLSQL_CODE_TYPE=NATIVE; ALTER PROCEDURE simple_integer_test_proc COMPILE; Natively compiling the procedure produces dramatic speed improvements for both datatypes, but more so for the SIMPLE_INTEGER datatype. SQL> SET SERVEROUTPUT ON SQL> EXEC simple_integer_test_proc; PLS_INTEGER: 10 hsecs SIMPLE_INTEGER: 2 hsecs PL/SQL procedure successfully completed. ------ The speed improvements are a result of two fundamental differences between the two datatypes. First, SIMPLE_INTEGER and PLS_INTEGER have the same range (-2,147,483,648 through 2,147,483,647), but SIMPLE_INTEGER wraps round when it exceeds its bounds, rather than throwing an error like PLS_INTEGER. SET SERVEROUTPUT ON DECLARE l_simple_integer SIMPLE_INTEGER := 2147483645; BEGIN FOR i IN 1 .. 4 LOOP l_simple_integer := l_simple_integer + 1; DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_simple_integer, 'S9999999999')); END LOOP; FOR i IN 1 .. 4 LOOP l_simple_integer := l_simple_integer - 1; DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_simple_integer, 'S9999999999')); END LOOP; END; / +2147483646 +2147483647 -2147483648 -2147483647 -2147483648 +2147483647 +2147483646 +2147483645 PL/SQL procedure successfully completed. ----- Second, SIMPLE_INTEGER can never have a NULL value, either when it is declared, or by assignment. DECLARE l_simple_integer SIMPLE_INTEGER; BEGIN NULL; END; / * ERROR at line 2: ORA-06550: line 2, column 20: PLS-00218: a variable declared NOT NULL must have an initialization assignment SQL> DECLARE l_simple_integer SIMPLE_INTEGER := 0; BEGIN l_simple_integer := NULL; END; / * ERROR at line 4: ORA-06550: line 4, column 23: PLS-00382: expression is of wrong type ORA-06550: line 4, column 3: PL/SQL: Statement ignored SQL> ---------------------------------------------------------- 3> Can we define text literal / string in delimiters other than ' ? Yes. run example below. begin dbms_output.put_line(q''); dbms_output.put_line(Q'! This is '' tag !'); end; / -- Rules : 1. start with q or Q. 2. precede and follow the delimiter with ' ( single quote). 3. can use any delimiter other than space , tab or return. ' ----------------------------------------------------------- 4> Are their any predefn Floating point Literals ( i.e constants ) ? yes, their are 4 predefn Floating point literal. a> binary_float_nan : A value of type BINARY_FLOAT for which the condition IS NAN is true eg : SELECT COUNT(*) FROM employees WHERE TO_BINARY_FLOAT(commission_pct) != BINARY_FLOAT_NAN; // NAN stands for Not a number // read NAN as NULL b> binary_float_infinity : Single-precision positive infinity eg : SELECT COUNT(*) FROM employees WHERE salary < BINARY_FLOAT_INFINITY; c> binary_double_nan : A value of type BINARY_DOUBLE for which the condition IS NAN is true. eg: SELECT COUNT(*) FROM employees WHERE TO_BINARY_FLOAT(commission_pct) != BINARY_FLOAT_NAN; d> binary_double_infinity :Double-precision positive infinity eg : SELECT COUNT(*) FROM employees WHERE salary < BINARY_FLOAT_INFINITY; ------------- 5> Make table employees with 3 - 4 attr. use %TYPE to make var same as column type of employees. CREATE TABLE employee ( e_id number(10) not null, e_name varchar2(50) not null, city varchar2(50) ); DECLARE em_id employee.e_id%type := 1; em_name employee.e_name%type; em_city customers.city%type; BEGIN SELECT e_name,city INTO em_name,em_city FROM employee WHERE e_id = em_id; dbms_output.put_line('Employee ' ||em_name || ' from ' || em_city ); END; ---------- 6> Make table CUSTOMERS , Insert few values. Make a procedure to fetch a CUSTOMER record. Use INTO keyword. CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Komal', 22, 'MP', 4500.00 ); DECLARE c_id customers.id%type := 1; c_name customers.name%type; c_addr customers.address%type; c_sal customers.salary%type; BEGIN SELECT name, address, salary INTO c_name, c_addr, c_sal FROM customers WHERE id = c_id; dbms_output.put_line ('Customer ' ||c_name || ' from ' || c_addr || ' earns ' || c_sal); END; ------------------- 7> Avoid clumsy IF statements such as: IF new_balance < minimum_balance THEN overdrawn := TRUE; ELSE overdrawn := FALSE; END IF; Instead, assign the value of the BOOLEAN expression directly to a BOOLEAN variable: overdrawn := new_balance < minimum_balance; A BOOLEAN variable is either TRUE, FALSE, or NULL. Do not write: IF overdrawn = TRUE THEN RAISE insufficient_funds; END IF; Instead, write: IF overdrawn THEN RAISE insufficient_funds; END IF; ------------------ 8> What is the o/p ? DECLARE grade CHAR(1); BEGIN grade := 'B'; IF grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent'); ELSIF grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good'); ELSIF grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good'); ELSIF grade = 'D' THEN DBMS_OUTPUT. PUT_LINE('Fair'); ELSIF grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor'); ELSE DBMS_OUTPUT.PUT_LINE('No such grade'); END IF; END; / -- Rewrite above pl/sql block using case statement ? DECLARE grade CHAR(1); BEGIN grade := 'B'; CASE grade WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent'); WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good'); WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good'); WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair'); WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor'); ELSE DBMS_OUTPUT.PUT_LINE('No such grade'); END CASE; END; / -- Rewritten in searched case format DECLARE grade CHAR(1); BEGIN grade := 'B'; CASE WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent'); WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good'); WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good'); WHEN grade = 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair'); WHEN grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor'); ELSE DBMS_OUTPUT.PUT_LINE('No such grade'); END CASE; END; / -- rewriting above case , once again -- using EXCEPTION Instead of ELSE Clause in CASE Statement DECLARE grade CHAR(1); BEGIN grade := 'B'; CASE WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent'); WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good'); WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good'); WHEN grade = 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair'); WHEN grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor'); END CASE; EXCEPTION WHEN CASE_NOT_FOUND THEN DBMS_OUTPUT.PUT_LINE('No such grade'); END; / --------------------------------- 8> What is the outcome of the running below block ? DROP TABLE temp; CREATE TABLE temp ( emp_no NUMBER, email_addr VARCHAR2(50) ); DECLARE emp_count NUMBER; BEGIN SELECT COUNT(employee_id) INTO emp_count FROM employees; FOR i IN 1 .. emp_count LOOP INSERT INTO temp (emp_no, email_addr) VALUES(i, 'to be added later'); END LOOP; END; / ---------------------------------------- 9> What is the o/p ? DECLARE p VARCHAR2(30); n PLS_INTEGER := 37; -- test any integer > 2 for prime BEGIN FOR j in 2..ROUND(SQRT(n)) LOOP IF n MOD j = 0 THEN -- test for prime p := ' is not a prime number'; -- not a prime number GOTO print_now; END IF; END LOOP; p := ' is a prime number'; <> DBMS_OUTPUT.PUT_LINE(TO_CHAR(n) || p); END; / ---------------------------------------- 10> -- GOTO Statement Cannot Transfer Control into IF Statement DECLARE valid BOOLEAN := TRUE; BEGIN GOTO update_row; IF valid THEN <> NULL; END IF; END; / ----------------------- 11> --- We can use all SQL functions in PL/SQL. -- SQL :Single row functions : Character or String , Numeric etc. -- Multi row fns ( also called aggregate functions ) like count,sum,.. -- This topic of SQL functions is fully covered in Oracle 12c SQL Training. declare v_a varchar2(10); v_b varchar2(10); v_c varchar2(10); v_d1 number; v_d2 number; v_d3 number; v_d4 number; begin v_a:='hello'; v_b:='all'; --string functions v_c:=CONCAT(v_a,v_b); dbms_output.put_line(v_c); dbms_output.put_line(INSTR(v_a,'e')); dbms_output.put_line(LENGTH(v_a)); dbms_output.put_line(LPAD(v_b,7,'$')); dbms_output.put_line(RPAD(v_a,7,'$')); dbms_output.put_line(TRIM(LEADING '0' FROM '000123')); dbms_output.put_line(TRIM(TRAILING '1' FROM 'Tech1')); dbms_output.put_line(TRIM(BOTH '1' FROM '123Tech111')); dbms_output.put_line(LTRIM('000123', '0')); dbms_output.put_line(LTRIM('123000', '0')); dbms_output.put_line(REPLACE('JACK and JUE','J','BL')); dbms_output.put_line(SUBSTR('This is a test', 6, 2)); dbms_output.put_line(SUBSTR('TechOnTheNet', -3, 3)); dbms_output.put_line(INITCAP('suven consultants pvt ltd')); dbms_output.put_line(LOWER('Tech on the Net')); dbms_output.put_line(UPPER('Tech on the Net')); dbms_output.put_line(TRANSLATE('1tech233', '123', '456')); --numeric functions dbms_output.put_line(MOD(15, 4)); dbms_output.put_line(POWER(3, 2)); dbms_output.put_line(ROUND(125.315)); dbms_output.put_line(CEIL(32.65)); dbms_output.put_line(FLOOR(32.65)); dbms_output.put_line(TRUNC(125.815)); dbms_output.put_line(SQRT(9)); --aggregate function or group fn SELECT SUM(salary), AVG(salary), MIN(salary), Max(salary) into v_d1 , v_d2, v_d3, v_d4 FROM employees_table; dbms_output.put_line('sum of Sal '|| v_d1); dbms_output.put_line('Avg Sal is '|| v_d2); dbms_output.put_line('Min Sal is '|| v_d3); dbms_output.put_line('Max Sal is '|| v_d4); end; / ------------- 12> -- SQL Conversion functions -- TO_DATE() , TO_NUMBER() and TO_CHAR() -- TO_NUMBER() : converts Char / date to Number SELECT TO_NUMBER('1121.23') FROM DUAL; -- will not allow seperators -- Note : format parameter only checks i/p in correct format or not ? SELECT TO_NUMBER('1,121.23', '9G999D99') FROM DUAL; SELECT TO_NUMBER('1,121.30', '9G999D99') FROM DUAL; -- G -> group seperator ( only , allowed ) -- D -> decimal point ( only . allowed ) -- will not work SELECT TO_NUMBER('1-121.23', '9G999D99') FROM DUAL; SELECT TO_NUMBER('1*121.23', '9G999D99') FROM DUAL; SELECT TO_NUMBER('1,1212.23', '9G999D99') FROM DUAL; SELECT TO_NUMBER('21,121.23', '9G999D99') FROM DUAL; SELECT TO_NUMBER('31121.23', '9G999D99') FROM DUAL; SELECT TO_NUMBER('3121.23', '9G999D99') FROM DUAL; SELECT TO_NUMBER('1,121.30000', '9G999D99') FROM DUAL; special case : SELECT TO_NUMBER('1&121.23', '9G999D99') FROM DUAL; -- enter any digit followed with . --more examples select TO_NUMBER('546', '999') from dual; select TO_NUMBER('23', '99') from dual; Select to_number('1234.64', '9999.9') from Dual; Select to_number('$99.64', 'L99D99') from Dual; -- TO_CHAR() : converts number / date to CHAR Select to_char(sysdate, 'yyyy/mm/dd') FROM dual; Select to_char(sysdate, 'Month DD, YYYY') FROM dual; select to_char(sysdate,'HH24:MI:SS') "Time Now" from dual; Select to_char(1000.25, '9,999.99') FROM dual; Select to_char(1000.25, '$9,999.00') FROM dual; Select to_char('01110' + 1) FROM dual; Select to_char(sysdate, 'Month W WW DY DAY,RR') FROM dual; -- W -> week# of the month , WW -> week# of the Year Select to_char(sysdate, 'CC , YY , MMTH , RM') FROM dual; -- CC -> century , TH -> 1st , 11th , 3rd. -- RM -> roman repr of the Month select to_char(sysdate,'HH:MI:SS') "Time Now" from dual; -- 12hr format -- To_DATE() : converts from number / char to date -- default date format is dd-MMM-YY SELECT TO_DATE('January 15, 1989, 11:00 A.M.', 'Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American') FROM DUAL; -- see the error ? SELECT TO_DATE('January 15, 1989, 11:00 P.M.', 'Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = SPANISH') FROM DUAL; -- Remember by default(in Oracle) we follow American English -- default date format is dd-MMM-YY select TO_DATE('2003/07/09', 'yyyy/mm/dd') from dual; select TO_DATE('070903', 'MMDDYY') from dual; select TO_DATE('20020315', 'yyyymmdd') from dual; -- to change the date format use TO_CHAR() SELECT to_char(add_months(to_date('01-JAN-1998', 'DD-MON-YYYY'),1 * 12),'DD-MON-YYYY') y1999 from dual; -------------------------- 13> --declaring all collections -> nested Table , VARRAY , Associative Array DECLARE TYPE nested_type IS TABLE OF VARCHAR2(30); TYPE varray_type IS VARRAY(5) OF INTEGER; TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER; TYPE assoc_array_str_type IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER; TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64); v1 nested_type; v2 varray_type; v3 assoc_array_num_type; v4 assoc_array_str_type; v5 assoc_array_str_type2; BEGIN -- an arbitrary number of strings can be inserted v1 v1 := nested_type('Shipping','Sales','Finance','Payroll'); v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers v3(99) := 10; -- Just start assigning elements v3(7) := 100; -- Subscripts will be any integer values v4(42) := 'Suven'; -- Just start assigning to elements v4(54) := 'Consultants'; -- Subscripts can be any integer values v5('Canada') := 'North America'; -- Just start assigning to elements v5('Greece') := 'Europe'; -- Subscripts will be string values -- print 1 value of each dbms_output.put_line('nested array v1 ' ||v1(2)); dbms_output.put_line(' Varray v2 ' ||v2(2)); dbms_output.put_line(' Assoc Array v3 ' ||v3(99)); dbms_output.put_line(' Assoc Array v4 ' ||v4(54)); dbms_output.put_line(' Assoc Array v5 ' ||v5('Canada')); END; / -------------------------- 14> --Can we pass Collection as a parameter ? --yes we can. --Declaring a Procedure Parameter as a Nested Table CREATE PACKAGE personnel AS TYPE staff_list IS TABLE OF employees_table.employee_id%TYPE; PROCEDURE award_bonuses (empleos_buenos IN staff_list); END personnel; / CREATE PACKAGE BODY personnel AS PROCEDURE award_bonuses (empleos_buenos staff_list) IS BEGIN FOR i IN empleos_buenos.FIRST .. empleos_buenos.LAST LOOP UPDATE employees_table SET salary = salary + 100 WHERE employees_table.employee_id = empleos_buenos(i); END LOOP; END; END; / --Invoking a Procedure with a Nested Table Parameter select employee_id,salary from employees_table; DECLARE good_employees personnel.staff_list; -- always prefix package name BEGIN good_employees := personnel.staff_list(1, 3, 7); personnel.award_bonuses (good_employees); END; / select employee_id,salary from employees_table; -------------------------- 15> -- Execute the program and observe o/p carefully ? DECLARE TYPE dnames_var IS VARRAY(20) OF VARCHAR2(30); dept_names dnames_var; BEGIN IF dept_names IS NULL THEN DBMS_OUTPUT.PUT_LINE ('Before initialization, the varray is null.'); -- While the varray is null, you cannot check its COUNT attribute. -- DBMS_OUTPUT.PUT_LINE -- ('It has ' || dept_names.COUNT || ' elements.'); ELSE DBMS_OUTPUT.PUT_LINE ('Before initialization, the varray is not null.'); END IF; dept_names := dnames_var(); -- initialize empty varray IF dept_names IS NULL THEN DBMS_OUTPUT.PUT_LINE ('After initialization, the varray is null.'); ELSE DBMS_OUTPUT.PUT_LINE ('After initialization, the varray is not null.'); DBMS_OUTPUT.PUT_LINE ('It has ' || dept_names.COUNT || ' elements.'); END IF; END; / -------------------------- 16> -- How to use SET OPERATORS with collection ? SET -> finds the unique values from the collection. MULTISET UNION -> concat both collection MULTISET UNION DISTINCT -> concat but with no duplicates MULTISET INTERSECT -> common from both sets MULTISET EXCEPT -> exclusive items from the first set -- -- Assigning Nested Tables with Set Operators DECLARE TYPE nested_typ IS TABLE OF NUMBER; nt1 nested_typ := nested_typ(1,2,3); nt2 nested_typ := nested_typ(3,2,1); nt3 nested_typ := nested_typ(2,3,1,3); nt4 nested_typ := nested_typ(1,2,4); answer nested_typ; -- The results might be in a different order than you expect. -- Do not rely on the order of elements in nested tables. PROCEDURE print_nested_table(the_nt nested_typ) IS output VARCHAR2(128); BEGIN IF the_nt IS NULL THEN DBMS_OUTPUT.PUT_LINE('Results: '); RETURN; END IF; IF the_nt.COUNT = 0 THEN DBMS_OUTPUT.PUT_LINE('Results: empty set'); RETURN; END IF; FOR i IN the_nt.FIRST .. the_nt.LAST LOOP output := output || the_nt(i) || ' '; END LOOP; DBMS_OUTPUT.PUT_LINE('Results: ' || output); END; BEGIN answer := nt1 MULTISET UNION nt4; -- (1,2,3,1,2,4) print_nested_table(answer); answer := nt1 MULTISET UNION nt3; -- (1,2,3,2,3,1,3) print_nested_table(answer); answer := nt1 MULTISET UNION DISTINCT nt3; -- (1,2,3) print_nested_table(answer); answer := nt2 MULTISET INTERSECT nt3; -- (3,2,1) print_nested_table(answer); answer := nt2 MULTISET INTERSECT DISTINCT nt3; -- (3,2,1) print_nested_table(answer); answer := SET(nt3); -- (2,3,1) print_nested_table(answer); answer := nt3 MULTISET EXCEPT nt2; -- (3) print_nested_table(answer); answer := nt3 MULTISET EXCEPT DISTINCT nt2; -- () print_nested_table(answer); END; / -------------------------- 17> --Collection Methods --Checking Whether a Collection Element EXISTS DECLARE TYPE NumList IS TABLE OF INTEGER; n NumList := NumList(1,3,5,7); BEGIN n.DELETE(2); -- Delete the second element IF n.EXISTS(1) THEN DBMS_OUTPUT.PUT_LINE('OK, element #1 exists.'); END IF; IF n.EXISTS(2) = FALSE THEN DBMS_OUTPUT.PUT_LINE('OK, element #2 was deleted.'); END IF; IF n.EXISTS(99) = FALSE THEN DBMS_OUTPUT.PUT_LINE('OK, element #99 does not exist at all.'); END IF; END; / -------------------------- 18> --Counting Collection Elements with COUNT DECLARE TYPE NumList IS TABLE OF NUMBER; n NumList := NumList(2,4,6,8); -- Collection starts with 4 elements. BEGIN DBMS_OUTPUT.PUT_LINE ('There are ' || n.COUNT || ' elements in N.'); n.EXTEND(3); -- Add 3 new elements at the end. DBMS_OUTPUT.PUT_LINE ('Now there are ' || n.COUNT || ' elements in N.'); n := NumList(86,99); -- Assign a completely new value with 2 elements. DBMS_OUTPUT.PUT_LINE ('Now there are ' || n.COUNT || ' elements in N.'); n.TRIM(2); -- Remove the last 2 elements, leaving none. DBMS_OUTPUT.PUT_LINE ('Now there are ' || n.COUNT || ' elements in N.'); END; / -------------------------- 19> --Finding the First or Last Collection Element(FIRST and LAST Methods) /* For a collection indexed by integers, FIRST and LAST return the first and last (smallest and largest) index numbers. For an associative array indexed by strings, FIRST and LAST return the lowest and highest key values. If the collection is empty, FIRST and LAST return NULL. If the collection contains only one element, FIRST and LAST return the same value. */ -- shows how to use FIRST and LAST to iterate through the elements in a collection that has consecutive subscripts. DECLARE TYPE NumList IS TABLE OF NUMBER; n NumList := NumList(1,3,5,7); counter INTEGER; BEGIN DBMS_OUTPUT.PUT_LINE('N''s first subscript is ' || n.FIRST); DBMS_OUTPUT.PUT_LINE('N''s last subscript is ' || n.LAST); -- When the subscripts are consecutive starting at 1, -- it's simple to loop through them. FOR i IN n.FIRST .. n.LAST LOOP DBMS_OUTPUT.PUT_LINE('Element #' || i || ' = ' || n(i)); END LOOP; n.DELETE(2); -- Delete second element. -- When the subscripts have gaps -- or the collection might be uninitialized, -- the loop logic is more extensive. -- Start at the first element -- and look for the next element until there are no more. IF n IS NOT NULL THEN counter := n.FIRST; WHILE counter IS NOT NULL LOOP DBMS_OUTPUT.PUT_LINE ('Element #' || counter || ' = ' || n(counter)); counter := n.NEXT(counter); END LOOP; ELSE DBMS_OUTPUT.PUT_LINE('N is null, nothing to do.'); END IF; END; / -------------------------- 20> -- use PRIOR or NEXT to traverse collections indexed by any series of subscripts. DECLARE TYPE NumList IS TABLE OF NUMBER; n NumList := NumList(1,3,5,7); counter INTEGER; BEGIN n.DELETE(2); -- Delete second element. -- When the subscripts have gaps, -- loop logic is more extensive. -- Start at first element and look for next element -- until there are no more. counter := n.FIRST; WHILE counter IS NOT NULL LOOP DBMS_OUTPUT.PUT_LINE ('Counting up: Element #' || counter || ' = ' || n(counter)); counter := n.NEXT(counter); END LOOP; -- Run the same loop in reverse order. counter := n.LAST; WHILE counter IS NOT NULL LOOP DBMS_OUTPUT.PUT_LINE ('Counting down: Element #' || counter || ' = ' || n(counter)); counter := n.PRIOR(counter); END LOOP; END; / -------------------------- 21> --Increasing the Size of a Collection (EXTEND Method) --To increase the size of a nested table or varray, use EXTEND. /* This procedure has three forms: EXTEND appends one null element to a collection. EXTEND(n) appends n null elements to a collection. EXTEND(n,i) appends n copies of the ith element to a collection. */ /* You cannot use EXTEND with index-by tables. You cannot use EXTEND to add elements to an uninitialized collection. If you impose the NOT NULL constraint on a TABLE or VARRAY type, you cannot apply the first two forms of EXTEND to collections of that type. */ /* EXTEND operates on the internal size of a collection, which includes any deleted elements. This refers to deleted elements after using DELETE(n), but not DELETE without parameters which completely removes all elements. If EXTEND encounters deleted elements, it includes them in its tally. PL/SQL keeps placeholders for deleted elements, so that you can re-create them by assigning new values. */ --Example: Using EXTEND to Increase the Size of a Collection DECLARE TYPE NumList IS TABLE OF INTEGER; n NumList := NumList(2,4,6,8); x NumList := NumList(1,3); PROCEDURE print_numlist(the_list NumList) IS output VARCHAR2(128); BEGIN FOR i IN the_list.FIRST .. the_list.LAST LOOP output := output || NVL(TO_CHAR(the_list(i)),'NULL') || ' '; END LOOP; DBMS_OUTPUT.PUT_LINE(output); END; BEGIN DBMS_OUTPUT.PUT_LINE ('At first, N has ' || n.COUNT || ' elements.'); n.EXTEND(5); -- Add 5 elements at the end. DBMS_OUTPUT.PUT_LINE ('Now N has ' || n.COUNT || ' elements.'); -- Elements 5, 6, 7, 8, and 9 are all NULL. print_numlist(n); DBMS_OUTPUT.PUT_LINE ('At first, X has ' || x.COUNT || ' elements.'); x.EXTEND(4,2); -- Add 4 elements at the end. DBMS_OUTPUT.PUT_LINE ('Now X has ' || x.COUNT || ' elements.'); -- Elements 3, 4, 5, and 6 are copies of element #2. print_numlist(x); END; / /* When it includes deleted elements, the internal size of a nested table differs from the values returned by COUNT and LAST. This refers to deleted elements after using DELETE(n), but not DELETE without parameters which completely removes all elements. For example, if you initialize a nested table with five elements, then delete elements 2 and 5, the internal size is 5, COUNT returns 3, and LAST returns 4. All deleted elements, regardless of position, are treated alike. */ -------------------------- 22> --Decreasing the Size of a Collection (TRIM Method) /* This procedure has two forms: TRIM removes one element from the end of a collection. TRIM(n) removes n elements from the end of a collection. */ Using TRIM to Decrease the Size of a Collection DECLARE TYPE NumList IS TABLE OF NUMBER; n NumList := NumList(1,2,3,5,7,11); PROCEDURE print_numlist(the_list NumList) IS output VARCHAR2(128); BEGIN IF n.COUNT = 0 THEN DBMS_OUTPUT.PUT_LINE('No elements in collection.'); ELSE FOR i IN the_list.FIRST .. the_list.LAST LOOP output := output || NVL(TO_CHAR(the_list(i)),'NULL') || ' '; END LOOP; DBMS_OUTPUT.PUT_LINE(output); END IF; END; BEGIN print_numlist(n); n.TRIM(2); -- Remove last 2 elements. print_numlist(n); n.TRIM; -- Remove last element. print_numlist(n); n.TRIM(n.COUNT); -- Remove all remaining elements. print_numlist(n); -- If too many elements are specified, -- TRIM raises the exception SUBSCRIPT_BEYOND_COUNT. BEGIN n := NumList(1,2,3); n.TRIM(100); EXCEPTION WHEN SUBSCRIPT_BEYOND_COUNT THEN DBMS_OUTPUT.PUT_LINE ('There weren''t 100 elements to be trimmed.'); END; -- When elements are removed by DELETE, -- placeholders are left behind. -- TRIM counts these placeholders -- as it removes elements from the end. n := NumList(1,2,3,4); n.DELETE(3); -- delete element 3 -- At this point, n contains elements (1,2,4). -- TRIMming the last 2 elements -- removes the 4 and the placeholder, not 4 and 2. n.TRIM(2); print_numlist(n); END; / /* In general, do not depend on the interaction between TRIM and DELETE. It is better to treat nested tables like fixed-size arrays and use only DELETE, or to treat them like stacks and use only TRIM and EXTEND. Because PL/SQL does not keep placeholders for trimmed elements, you cannot replace a trimmed element simply by assigning it a new value. */ -------------------------- 23> --Deleting Collection Elements (DELETE Method) /* This procedure has these forms: DELETE with no parameters removes all elements from a collection, setting COUNT to 0. DELETE(n) removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing. DELETE(m,n) removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is NULL, DELETE(m,n) does nothing. */ DECLARE TYPE NumList IS TABLE OF NUMBER; n NumList := NumList(10,20,30,40,50,60,70,80,90,100); TYPE NickList IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(32); nicknames NickList; BEGIN n.DELETE(2); -- deletes element 2 n.DELETE(3,6); -- deletes elements 3 through 6 n.DELETE(7,7); -- deletes element 7 n.DELETE(6,3); -- does nothing since 6 > 3 n.DELETE; -- deletes all elements nicknames('SC') := 'Suven Consultants'; nicknames('SCTPL') := 'Suven Consultants abd Tech Pvt Ltd'; nicknames('IC') := 'Intel'; nicknames('Mobile') := 'Android'; nicknames('SQL') := 'Rocky Sir'; nicknames('PL-SQL') := 'Rocky Sir'; -- following deletes element denoted by this key nicknames.DELETE('IC'); -- following deletes elements with keys in this alphabetic range nicknames.DELETE('PL-SQL','SQL'); -- deletes PL-SQL , SC , SCTPL , SQL END; / -------------------------- 24> /* Packaged collection types and local collection types are never compatible. */ -- Incompatibility Between Package and Local Collection Types CREATE PACKAGE pkg AS TYPE NumList IS TABLE OF NUMBER; PROCEDURE print_numlist (nums NumList); END pkg; / CREATE PACKAGE BODY pkg AS PROCEDURE print_numlist (nums NumList) IS BEGIN FOR i IN nums.FIRST .. nums.LAST LOOP DBMS_OUTPUT.PUT_LINE(nums(i)); END LOOP; END; END pkg; / DECLARE TYPE NumList IS TABLE OF NUMBER; n1 pkg.NumList := pkg.NumList(2,4); -- type from the package. n2 NumList := NumList(6,8); -- local type. BEGIN pkg.print_numlist(n1); -- type from pkg is legal -- The packaged procedure cannot accept -- a value of the local type (n2) --pkg.print_numlist(n2); -- Causes a compilation error. END; / drop package pkg; -------------------------- 25> What is the o/p ? -- Example of RECORD Type, CURSOR , BULK COLLECT INTO -- Make a RECORD data type DECLARE TYPE emp_name_rec is RECORD ( firstname employees_table.first_name%TYPE, lastname employees_table.last_name%TYPE, hiredate employees_table.hire_date%TYPE ); -- Array type that can hold information 10 employees TYPE EmpList_arr IS VARRAY(10) OF emp_name_rec; SeniorSalespeople EmpList_arr; -- Declare a cursor to select a subset of columns. CURSOR c1 IS SELECT first_name, last_name, hire_date FROM employees_table; Type NameSet IS TABLE OF c1%ROWTYPE; SeniorTen NameSet; EndCounter NUMBER := 10; BEGIN SeniorSalespeople := EmpList_arr(); SELECT first_name, last_name, hire_date BULK COLLECT INTO SeniorTen FROM employees_table WHERE dept_id > 100 ORDER BY hire_date; -- for this example, display a maximum of ten employees IF SeniorTen.LAST > 0 THEN IF SeniorTen.LAST < 10 THEN EndCounter := SeniorTen.LAST; END IF; FOR i in 1 .. EndCounter LOOP SeniorSalespeople.EXTEND(1); SeniorSalespeople(i) := SeniorTen(i); DBMS_OUTPUT.PUT_LINE(SeniorSalespeople(i).last_name || ', ' || SeniorSalespeople(i).first_name || ', ' || SeniorSalespeople(i).hiredate); END LOOP; END IF; END; ---------- 26> What is the O/p ? -- Example using RECORD , NESTED TABLE , CURSOR and BULK COLLECT INTO DECLARE TYPE emp_name_rec is RECORD ( firstname employees.first_name%TYPE, lastname employees.last_name%TYPE, hiredate employees.hire_date%TYPE ); -- Table type that can hold information about employees TYPE EmpList_tab IS TABLE OF emp_name_rec; SeniorSalespeople EmpList_tab; -- Declare a cursor to select a subset of columns. CURSOR c1 IS SELECT first_name, last_name, hire_date FROM employees_table; EndCounter NUMBER := 10; TYPE EmpCurTyp IS REF CURSOR; emp_cv EmpCurTyp; BEGIN OPEN emp_cv FOR SELECT first_name, last_name, hire_date FROM employees_table WHERE dept_id > 100 ORDER BY hire_date; FETCH emp_cv BULK COLLECT INTO SeniorSalespeople; CLOSE emp_cv; -- for this example, display a maximum of ten employees IF SeniorSalespeople.LAST > 0 THEN IF SeniorSalespeople.LAST < 10 THEN EndCounter := SeniorSalespeople.LAST; END IF; FOR i in 1..EndCounter LOOP DBMS_OUTPUT.PUT_LINE (SeniorSalespeople(i).lastname || ', ' || SeniorSalespeople(i).firstname || ', ' || SeniorSalespeople(i).hiredate); END LOOP; END IF; END; / ---------- 27> --Comparing Nested Tables with Set Operators DECLARE TYPE nested_typ IS TABLE OF NUMBER; nt1 nested_typ := nested_typ(1,2,3); nt2 nested_typ := nested_typ(3,2,1); nt3 nested_typ := nested_typ(2,3,1,3); nt4 nested_typ := nested_typ(1,2,4); answer BOOLEAN; howmany NUMBER; PROCEDURE testify (truth BOOLEAN DEFAULT NULL) IS BEGIN IF truth IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE (CASE truth WHEN TRUE THEN 'True' WHEN FALSE THEN 'False' END); END IF; END; BEGIN answer := nt1 IN (nt2,nt3,nt4); -- true, nt1 matches nt2 testify(answer); answer := nt1 SUBMULTISET OF nt3; -- true, all elements match testify(answer); answer := nt1 NOT SUBMULTISET OF nt4; -- also true testify(answer); howmany := CARDINALITY(nt3); -- number of elements in nt3 DBMS_OUTPUT.PUT_LINE(howmany); howmany := CARDINALITY(SET(nt3)); -- number of distinct elements DBMS_OUTPUT.PUT_LINE(howmany); answer := 4 MEMBER OF nt1; -- false, no element matches testify(answer); answer := nt3 IS A SET; -- false, nt3 has duplicates testify(answer); answer := nt3 IS NOT A SET; -- true, nt3 has duplicates testify(answer); answer := nt1 IS EMPTY; -- false, nt1 has some members testify(answer); END; / ----------- 28> -- Example : Static SQL --Substituting PL/SQL Variables drop table employees_temp; CREATE TABLE employees_temp AS SELECT first_name, last_name FROM employees_table; DECLARE x VARCHAR2(20) := 'my_first_name'; y VARCHAR2(25) := 'my_last_name'; BEGIN INSERT INTO employees_temp VALUES(x, y); UPDATE employees_temp SET last_name = x WHERE first_name = y; DELETE FROM employees_temp WHERE first_name = x; COMMIT; END; / --With this notation, you can use variables in place of values in the WHERE clause. To use variables in place of table names, column names, and so on, requires the EXECUTE IMMEDIATE statement that is explained in Using Native Dynamic SQL. --------------- 29> -- Implicit Cursor or SQL Cursor : is auto managed by PLSQL. -- Cursor attributes -- Using SQL%FOUND CREATE TABLE dept_temp AS SELECT * FROM department_table; DECLARE dept_no NUMBER(4) := 102; BEGIN DELETE FROM dept_temp WHERE department_id = dept_no; IF SQL%FOUND THEN -- delete succeeded INSERT INTO dept_temp VALUES (270, 'Personnel', 200); END IF; END; / select * from dept_temp where department_id = 270; ---------------------------- 30> -- 2. Cursor Attr %NOTFOUND --%NOTFOUND is the logical opposite of %FOUND. --%NOTFOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected no rows -- 3. %ROWCOUNT Attribute: How Many Rows Affected So Far ? -- example for implicit cursor CREATE TABLE employees_temp AS SELECT * FROM employees_table; DECLARE dept_no NUMBER(6) := 102; BEGIN DELETE FROM employees_temp WHERE dept_id = dept_no; DBMS_OUTPUT.PUT_LINE ('Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT)); END; / --If a SELECT INTO statement returns more than one row, PL/SQL raises the predefined exception TOO_MANY_ROWS and %ROWCOUNT yields 1, not the actual number of rows that satisfy the query. ------------- 31> -- Explicit Cursor attributes -- %FOUND Attribute: Has a Row Been Fetched? /* After a cursor or cursor variable is opened but before the first fetch, %FOUND returns NULL. After any fetches, it returns TRUE if the last fetch returned a row, or FALSE if the last fetch did not return a row. */ DECLARE CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11; my_ename employees.last_name%TYPE; my_salary employees.salary%TYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO my_ename, my_salary; IF c1%FOUND THEN -- fetch succeeded DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ', salary = ' || my_salary); ELSE -- fetch failed, so exit loop EXIT; END IF; END LOOP; END; / ----------- 32> -- %ISOPEN Attribute: Is the Cursor Open? -- %ISOPEN returns TRUE if its cursor or cursor variable is open; otherwise, %ISOPEN returns FALSE. -- Example:Using %ISOPEN DECLARE CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11; the_name employees.last_name%TYPE; the_salary employees.salary%TYPE; BEGIN IF c1%ISOPEN = FALSE THEN -- cursor was not already open OPEN c1; END IF; FETCH c1 INTO the_name, the_salary; CLOSE c1; END; / ----------- 33> --Using %NOTFOUND DECLARE CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11; my_ename employees.last_name%TYPE; my_salary employees.salary%TYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO my_ename, my_salary; IF c1%NOTFOUND THEN -- fetch failed, so exit loop -- Another form of this test is -- "EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;" EXIT; ELSE -- fetch succeeded DBMS_OUTPUT.PUT_LINE ('Name = ' || my_ename || ', salary = ' || my_salary); END IF; END LOOP; END; / -------------- 34> --%ROWCOUNT Attribute: How Many Rows Fetched So Far? /* When its cursor or cursor variable is opened, %ROWCOUNT is zeroed. Before the first fetch, %ROWCOUNT yields zero. Thereafter, it yields the number of rows fetched so far. The number is incremented if the last fetch returned a row. */ DECLARE CURSOR c1 IS SELECT last_name FROM employees_table WHERE ROWNUM < 5; name employees_table.last_name%TYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO name; EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL; DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT || '. ' || name); IF c1%ROWCOUNT = 3 THEN DBMS_OUTPUT.PUT_LINE('--- Fetched 3rd record ---'); END IF; END LOOP; CLOSE c1; END; / ---------------- 35> --Defining Aliases for Expression Values in a Cursor FOR Loop /* In a cursor FOR loop, PL/SQL creates a %ROWTYPE record with fields corresponding to columns in the result set. The fields have the same names as corresponding columns in the SELECT list. The select list might contain an expression, such as a column plus a constant, or two columns concatenated together. If so, use a column alias to give unique names to the appropriate columns. */ BEGIN FOR item IN ( SELECT first_name || ' ' || last_name AS full_name, salary * 10 AS dream_salary FROM employees_table WHERE ROWNUM <= 5 ) LOOP DBMS_OUTPUT.PUT_LINE (item.full_name || ' dreams of making ' || item.dream_salary); END LOOP; END; / ------------ 36> -- Can we define Subquery in a Cursor ? Yes -- 4.6 -- Using a Subquery in a Cursor DECLARE CURSOR c1 IS -- main query returns only rows -- where the salary is greater than the average SELECT employee_id, last_name FROM employees_table WHERE salary > (SELECT AVG(salary) FROM employees_table); CURSOR c2 IS -- subquery returns all the rows in descending order of salary -- main query returns just the top 5 highest-paid employees SELECT * FROM (SELECT last_name, salary FROM employees_table ORDER BY salary DESC , last_name) WHERE ROWNUM < 5; BEGIN FOR person IN c1 LOOP DBMS_OUTPUT.PUT_LINE('Above-average salary: ' || person.last_name); END LOOP; FOR person IN c2 LOOP DBMS_OUTPUT.PUT_LINE ('Highest paid: ' || person.last_name || ' $' || person.salary); END LOOP; END; / --------------- 37> -- REF CURSOR i.e using cursor variables instead of cursor. -- Adv : flexibility. We can associate a cursor var with many diffr queries --Opening Ref Cursors with Different Queries. CREATE PACKAGE emp_data AS TYPE empcurtyp IS REF CURSOR RETURN employees_table%ROWTYPE; PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, choice INT); END emp_data; / CREATE PACKAGE BODY emp_data AS PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, choice INT) IS BEGIN IF choice = 1 THEN OPEN emp_cv FOR SELECT * FROM employees_table WHERE commission_pct IS NOT NULL; ELSIF choice = 2 THEN OPEN emp_cv FOR SELECT * FROM employees_table WHERE salary > 2500; ELSIF choice = 3 THEN OPEN emp_cv FOR SELECT * FROM employees_table WHERE dept_id = 100; END IF; END; END emp_data; / declare emp_cv emp_data.empcurtyp; begin emp_data.open_emp_cv (emp_cv, 1); end; / -------------- 38> -- Another Example : Cursor Variable -- Fetching from a Cursor Variable DECLARE TYPE empcurtyp IS REF CURSOR RETURN employees_table%ROWTYPE; emp_cv empcurtyp; emp_rec employees_table%ROWTYPE; BEGIN OPEN emp_cv FOR SELECT * FROM employees_table WHERE employee_id < 10; LOOP FETCH emp_cv INTO emp_rec; -- fetch from cursor variable EXIT WHEN emp_cv%NOTFOUND; -- exit when last row is fetched -- process data record DBMS_OUTPUT.PUT_LINE ('Name = ' || emp_rec.first_name || ' ' || emp_rec.last_name); END LOOP; CLOSE emp_cv; END; / ----------------- 39> -- Fetching BULK rows using BULK COLLCT INTO clause -- Using OPEN-FOR , FETCH , BULK COLLECT INTO wrt REF CURSOR DECLARE TYPE empcurtyp IS REF CURSOR; -- weak cursor Var TYPE namelist IS TABLE OF employees.last_name%TYPE; --nested table TYPE sallist IS TABLE OF employees.salary%TYPE; emp_cv empcurtyp; -- variable of Cursor Var names namelist; sals sallist; BEGIN OPEN emp_cv FOR SELECT last_name, salary FROM employees WHERE dept_id IN (10,11); -- we will value error , if our -- cursor is empty FETCH emp_cv BULK COLLECT INTO names, sals; CLOSE emp_cv; -- loop through the names and sals collections FOR i IN names.FIRST .. names.LAST LOOP DBMS_OUTPUT.PUT_LINE ('Name = ' || names(i) || ', salary = ' || sals(i)); END LOOP; END; / ------------ 40> --Passing a REF CURSOR as a Parameter DECLARE TYPE empcurtyp IS REF CURSOR RETURN employees_table%ROWTYPE; emp empcurtyp; -- after result set is built, -- process all the rows inside a single procedure -- rather than invoking a procedure for each row PROCEDURE process_emp_cv (emp_cv IN empcurtyp) IS person employees%ROWTYPE; BEGIN DBMS_OUTPUT.PUT_LINE('-----'); DBMS_OUTPUT.PUT_LINE ('Here are the names from the result set:'); LOOP FETCH emp_cv INTO person; EXIT WHEN emp_cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Name = ' || person.first_name || ' ' || person.last_name); END LOOP; END; BEGIN -- First find 10 arbitrary employees. OPEN emp FOR SELECT * FROM employees_table WHERE ROWNUM < 11; process_emp_cv(emp); CLOSE emp; -- find employees matching a condition. OPEN emp FOR SELECT * FROM employees_table WHERE last_name LIKE 'R%'; process_emp_cv(emp); CLOSE emp; END; / -------------- 41> --Using SET TRANSACTION to Begin a Read-only Transaction --Read-only Transaction allows many users to run tx at the same time. --only 1 other update tx can be done in parallel. --Read-only Tx can have only select st. DECLARE daily_order_total NUMBER(12,2); weekly_order_total NUMBER(12,2); monthly_order_total NUMBER(12,2); BEGIN COMMIT; -- ends previous transaction SET TRANSACTION READ ONLY NAME 'Calculate Order Totals'; -- make sure of creating orders table SELECT SUM (order_total) INTO daily_order_total FROM orders WHERE order_date = SYSDATE; SELECT SUM (order_total) INTO weekly_order_total FROM orders WHERE order_date = SYSDATE - 7; SELECT SUM (order_total) INTO monthly_order_total FROM orders WHERE order_date = SYSDATE - 30; COMMIT; -- ends read-only transaction END; / -------------- 42.A> -- Importance of AUTONOMOUS_TRANSACTION -- create the debug table CREATE TABLE debug_output (msg VARCHAR2(200)); -- create the package spec CREATE PACKAGE debugging AS FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2; PRAGMA RESTRICT_REFERENCES(log_msg, WNDS, RNDS); END debugging; / -- create the package body CREATE 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; / -- invoke the packaged function from a query DECLARE my_emp_id NUMBER(6); my_last_name VARCHAR2(25); my_count NUMBER; BEGIN my_emp_id := 1; SELECT debugging.log_msg(last_name) INTO my_last_name FROM employees_table 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; / -- chk the records in table debug_output select * from debug_output; ------------- -- from here on , go to plsql_oracle_11g_home practice_file_2.sql -- -------------