Live ORACLE

if you are Oracle Developer ,than this Blog most likely will make you happy.

About my Blog

This Blog particularized for Oracle Developers ... you will see interesting Codes for SQL , PL/SQL as well as new ideas for Developer Suite and Client Tools that will help you in your professional life ... and I hope I reach for your satisfaction.

About Me

I'm Hany Freedom , 25 years old , I live in EL-Minia City in Egypt , I'm Moderator at ArabOUG.org the best Arabic Forum for ORACLE on the Net. if you interested to know more about me .... just Click Here.

Using ROWNUM

DROP TABLE employees_temp CASCADE CONSTRAINTS PURGE;

CREATE TABLE employees_temp
AS
SELECT *
FROM employees;



DECLARE

CURSOR c1 IS

SELECT employee_id,

salary

FROM employees_temp

WHERE salary > 2000

AND ROWNUM <= 10; -- 10 arbitrary rows

CURSOR c2 IS

SELECT *

FROM (SELECT employee_id,

salary

FROM employees_temp

WHERE salary > 2000

ORDER BY salary DESC)

WHERE ROWNUM < 5; -- first 5 rows, in sorted order



BEGIN

-- Each row gets assigned a different number

UPDATE employees_temp

SET employee_id = ROWNUM;

END;

/



Share/Save/Bookmark

Using CURRVAL and NEXTVAL


CREATE TABLE employees_temp
AS
SELECT employee_id,
first_name,
last_name
FROM employees;


CREATE TABLE employees_temp2
AS
SELECT employee_id,
first_name,
last_name
FROM employees;



DECLARE

seq_value NUMBER;

BEGIN

-- Display initial value of NEXTVAL

-- This is invalid: seq_value := employees_seq.NEXTVAL;

SELECT employees_seq.nextval

INTO seq_value

FROM dual;



dbms_output.Put_line('Initial sequence value: '

||To_char(seq_value));



-- The NEXTVAL value is the same no matter what table you select from

-- You usually use NEXTVAL to create unique numbers when inserting data.

INSERT INTO employees_temp

VALUES (employees_seq.nextval,

'Lynette',

'Smith');



-- If you need to store the same value somewhere else, you use CURRVAL

INSERT INTO employees_temp2

VALUES (employees_seq.currval,

'Morgan',

'Smith');



-- Because NEXTVAL values might be referenced by different users and

-- applications, and some NEXTVAL values might not be stored in the

-- database, there might be gaps in the sequence

-- The following uses the stored value of the CURRVAL in seq_value to specify

-- the record to delete because CURRVAL (or NEXTVAL) cannot used in a WHERE clause

-- This is invalid: WHERE employee_id = employees_seq.CURRVAL;

SELECT employees_seq.currval

INTO seq_value

FROM dual;



DELETE FROM employees_temp2

WHERE employee_id = seq_value;



-- The following udpates the employee_id with NEXTVAL for the specified record

UPDATE employees_temp

SET employee_id = employees_seq.nextval

WHERE first_name = 'Lynette'

AND last_name = 'Smith';



-- Display end value of CURRVAL

SELECT employees_seq.currval

INTO seq_value

FROM dual;



dbms_output.Put_line('Ending sequence value: '

||To_char(seq_value));

END;

/



Share/Save/Bookmark

Oracle Magazine - March/April 2009

Look Inside >>
March/April 2009

Share/Save/Bookmark

Calling the SQL COUNT Function in PL/SQL


DECLARE

job_count NUMBER;

emp_count NUMBER;

BEGIN

SELECT Count(DISTINCT job_id)

INTO job_count

FROM employees;



SELECT Count(* )

INTO emp_count

FROM employees;

END;

/



Share/Save/Bookmark

Substituting PL/SQL Variables


CREATE TABLE employees_temp
AS
SELECT first_name,
last_name
FROM employees;



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;

/



Share/Save/Bookmark

Checking SQL%ROWCOUNT After an UPDATE

DROP TABLE employees_temp CASCADE CONSTRAINTS PURGE;

CREATE TABLE employees_temp
AS
SELECT *
FROM employees;



BEGIN

UPDATE employees_temp

SET salary = salary * 1.05

WHERE salary < 5000;



dbms_output.Put_line('Updated '

||SQL%ROWCOUNT

||' salaries.');

END;

/



Output :-
Updated 49 salaries.

PL/SQL procedure successfully completed.

Share/Save/Bookmark

Data Manipulation With PL/SQL


CREATE TABLE employees_temp
AS
SELECT employee_id,
first_name,
last_name
FROM employees;



DECLARE

emp_id employees_temp.employee_id%TYPE;

emp_first_name employees_temp.first_name%TYPE;

emp_last_name employees_temp.last_name%TYPE;

BEGIN

INSERT INTO employees_temp

VALUES (299,

'Bob',

'Henry');



UPDATE employees_temp

SET first_name = 'Robert'

WHERE employee_id = 299;



DELETE FROM employees_temp

WHERE employee_id = 299

RETURNING first_name,last_name INTO emp_first_name,emp_last_name;



COMMIT;



dbms_output.Put_line(emp_first_name

||' '

||emp_last_name);

END;

/



Output :-
Robert Henry

PL/SQL procedure successfully completed.

Share/Save/Bookmark

Using BULK COLLECT With a SELECT INTO Statement

You can use the BULK COLLECT clause with a SELECT INTO or FETCH statement to retrieve a set of rows into a collection of records.


DECLARE

TYPE employeeset IS TABLE OF employees%ROWTYPE;

underpaid EMPLOYEESET; -- Holds set of rows from EMPLOYEES table.

CURSOR c1 IS

SELECT first_name,

last_name

FROM employees;

TYPE nameset IS TABLE OF c1%ROWTYPE;

some_names NAMESET; -- Holds set of partial rows from EMPLOYEES table.



BEGIN

-- With one query, we bring all the relevant data into the collection of records.

SELECT *

BULK COLLECT INTO underpaid

FROM employees

WHERE salary < 5000

ORDER BY salary DESC;



-- Now we can process the data by examining the collection, or passing it to

-- a separate procedure, instead of writing a loop to FETCH each row.

dbms_output.Put_line(underpaid.COUNT

||' people make less than 5000.');



FOR i IN underpaid.FIRST.. underpaid.LAST LOOP

dbms_output.Put_line(Underpaid(i).last_name

||' makes '

||Underpaid(i).salary);

END LOOP;



-- We can also bring in just some of the table columns.

-- Here we get the first and last names of 10 arbitrary employees.

SELECT first_name,

last_name

BULK COLLECT INTO some_names

FROM employees

WHERE ROWNUM < 11;



FOR i IN some_names.FIRST.. some_names.LAST LOOP

dbms_output.Put_line('Employee = '

||Some_names(i).first_name

||' '

||Some_names(i).last_name);

END LOOP;

END;

/



Output :-
49 people make less than 5000.
Austin makes 4800
Pataballa makes 4800
Whalen makes 4400
Sarchand makes 4200
Lorentz makes 4200
Bull makes 4100
Bell makes 4000
Everett makes 3900
Chung makes 3800
Ladwig makes 3600
Dilly makes 3600
Rajs makes 3500
Dellinger makes 3400
Mallin makes 3300
Bissot makes 3300
Stiles makes 3200
Nayer makes 3200
Taylor makes 3200
McCain makes 3200
Khoo makes 3100
Walsh makes 3100
Fleaur makes 3100
Davies makes 3100
Feeney makes 3000
Cabrio makes 3000
Gates makes 2900
Rogers makes 2900
Baida makes 2900
Jones makes 2800
Atkinson makes 2800
Geoni makes 2800
Tobias makes 2800
Seo makes 2700
Mikkilineni makes 2700
OConnell makes 2600
Himuro makes 2600
Grant makes 2600
Matos makes 2600
Perkins makes 2500
Colmenares makes 2500
Sullivan makes 2500
Vargas makes 2500
Patel makes 2500
Marlow makes 2500
Landry makes 2400
Gee makes 2400
Philtanker makes 2200
Markle makes 2200
Olson makes 2100
Employee = Ellen Abel
Employee = Sundar Ande
Employee = Mozhe Atkinson
Employee = David Austin
Employee = Hermann Baer
Employee = Shelli Baida
Employee = Amit Banda
Employee = Elizabeth Bates
Employee = Sarah Bell
Employee = David Bernstein

PL/SQL procedure successfully completed.

Share/Save/Bookmark

Using the RETURNING Clause with a Record


DECLARE

TYPE emprec IS RECORD(last_name employees.last_name%TYPE,

salary employees.salary%TYPE);

emp_info EMPREC;

emp_id NUMBER := 100;

BEGIN

UPDATE employees

SET salary = salary * 1.1

WHERE employee_id = emp_id

RETURNING last_name,salary INTO emp_info;



dbms_output.Put_line('Just gave a raise to '

||emp_info.last_name

||', who now makes '

||emp_info.salary);



ROLLBACK;

END;

/



Share/Save/Bookmark

Updating a Row Using a Record


DECLARE

dept_info departments%ROWTYPE;

BEGIN

-- department_id, department_name, and location_id are the table columns

-- The record picks up these names from the %ROWTYPE.

dept_info.department_id := 300;



dept_info.department_name := 'Personnel';



dept_info.location_id := 1700;



-- The fields of a %ROWTYPE can completely replace the table columns

-- The row will have values for the filled-in columns, and null

-- for any other columns

UPDATE departments

SET ROW = dept_info

WHERE department_id = 300;

END;

/



Share/Save/Bookmark

Inserting a PL/SQL Record Using %ROWTYPE


DECLARE

dept_info departments%ROWTYPE;

BEGIN

-- department_id, department_name, and location_id are the table columns

-- The record picks up these names from the %ROWTYPE

dept_info.department_id := 300;



dept_info.department_name := 'Personnel';



dept_info.location_id := 1700;



END;

/



Share/Save/Bookmark

Using SELECT INTO to Assign Values in a Record


DECLARE

TYPE recordtyp IS RECORD(LAST employees.last_name%TYPE,

id employees.employee_id%TYPE);

rec1 RECORDTYP;

BEGIN

SELECT last_name,

employee_id

INTO rec1

FROM employees

WHERE ROWNUM < 2;



dbms_output.Put_line('Employee #'

||rec1.id

||' = '

||rec1.LAST);

END;

/



Share/Save/Bookmark

Assigning All the Fields of a Record in One Statement


DECLARE

-- Two identical type declarations.

TYPE deptrec1 IS RECORD(dept_num NUMBER(2),

dept_name VARCHAR2(14));

TYPE deptrec2 IS RECORD(dept_num NUMBER(2),

dept_name VARCHAR2(14));

dept1_info DEPTREC1;

dept2_info DEPTREC2;

dept3_info DEPTREC2;

BEGIN

-- Not allowed; different datatypes, even though fields are the same.

-- dept1_info := dept2_info;

-- This assignment is OK because the records have the same type.

dept2_info := dept3_info;

END;

/



You can assign a %ROWTYPE record to a user-defined record if their fields match in number and order, and corresponding fields have the same datatypes:



DECLARE

TYPE recordtyp IS RECORD(LAST employees.last_name%TYPE,

id employees.employee_id%TYPE);

CURSOR c1 IS

SELECT last_name,

employee_id

FROM employees;

-- Rec1 and rec2 have different types. But because rec2 is based on a %ROWTYPE,

-- we can assign is to rec1 as long as they have the right number of fields and

-- the fields have the right datatypes.

rec1 RECORDTYP;

rec2 c1%ROWTYPE;

BEGIN

SELECT last_name,

employee_id

INTO rec2

FROM employees

WHERE ROWNUM < 2;



rec1 := rec2;



dbms_output.Put_line('Employee #'

||rec1.id

||' = '

||rec1.LAST);

END;

/


You can also use the SELECT or FETCH statement to fetch column values into a record.
The columns in the select-list must appear in the same order as the fields in your record.


Share/Save/Bookmark

Assigning Default Values to a Record


DECLARE

TYPE recordtyp IS RECORD(field1 NUMBER,

field2 VARCHAR2(32) DEFAULT 'something');

rec1 RECORDTYP;

rec2 RECORDTYP;

BEGIN

-- At first, rec1 has the values we assign.

rec1.field1 := 100;



rec1.field2 := 'something else';



-- Assigning an empty record to rec1 resets fields to their default values.

-- Field1 is NULL and field2 is 'something' due to the DEFAULT clause

rec1 := rec2;



dbms_output.Put_line('Field1 = '

||Nvl(To_char(rec1.field1),'')

||',
field2 = '


||rec1.field2);

END;

/



Share/Save/Bookmark

Declaring a Nested Record

DECLARE
TYPE TimeTyp IS RECORD ( minutes SMALLINT, hours SMALLINT );
TYPE MeetingTyp IS RECORD (
day DATE,
time_of TimeTyp, -- nested record
dept departments%ROWTYPE, -- nested record representing a table row
place VARCHAR2(20),
purpose VARCHAR2(50) );
meeting MeetingTyp;
seminar MeetingTyp;
BEGIN
-- you can assign one nested record to another if they are of the same datatype
seminar.time_of := meeting.time_of;
END;
/

Share/Save/Bookmark

Using a Record as Parameter to a Procedure


DECLARE

TYPE EMPRECTYP IS RECORD(EMP_ID NUMBER(6),

EMP_SAL NUMBER(8,2));

PROCEDURE RAISE_SALARY

(EMP_INFO EMPRECTYP)

IS

BEGIN

UPDATE EMPLOYEES

SET SALARY = SALARY + SALARY * .10

WHERE EMPLOYEE_ID = EMP_INFO.EMP_ID;

END RAISE_SALARY;

BEGIN

NULL;

END;

/


Share/Save/Bookmark

Returning a Record from a Function


DECLARE

TYPE EMPRECTYP IS RECORD(EMP_ID NUMBER(6),

SALARY NUMBER(8,2));

CURSOR DESC_SALARY RETURN EMPRECTYP IS

SELECT EMPLOYEE_ID,

SALARY

FROM EMPLOYEES

ORDER BY SALARY DESC;

EMP_REC EMPRECTYP;

FUNCTION NTH_HIGHEST_SALARY

(N INTEGER)

RETURN EMPRECTYP

IS

BEGIN

OPEN DESC_SALARY;



FOR I IN 1.. N LOOP

FETCH DESC_SALARY INTO EMP_REC;

END LOOP;



CLOSE DESC_SALARY;



RETURN EMP_REC;

END NTH_HIGHEST_SALARY;

BEGIN

NULL;

END;

/


Share/Save/Bookmark

Using %ROWTYPE to Declare a Record


DECLARE

-- Best: use %ROWTYPE instead of specifying each column.

-- Use %ROWTYPE instead of %ROWTYPE because

-- we only want some columns.

-- Declaring the cursor doesn't run the query, so no performance hit.

CURSORC1IS

SELECTDEPARTMENT_ID,

DEPARTMENT_NAME,

LOCATION_ID

FROMDEPARTMENTS;

REC1C1%ROWTYPE;

-- Use %TYPE in field declarations to avoid problems if

-- the column types change.

TYPEDEPTREC2ISRECORD(DEPT_IDDEPARTMENTS.DEPARTMENT_ID%TYPE,

DEPT_NAMEDEPARTMENTS.DEPARTMENT_NAME%TYPE,

DEPT_LOCDEPARTMENTS.LOCATION_ID%TYPE);

REC2DEPTREC2;

-- Final technique, writing out each field name and specifying the type directly,

-- is clumsy and unmaintainable for working with table data.

-- Use only for all-PL/SQL code.

TYPEDEPTREC3ISRECORD(DEPT_IDNUMBER,

DEPT_NAMEVARCHAR2(14),

DEPT_LOCVARCHAR2(13));

REC3DEPTREC3;

BEGIN

NULL;

END;

/



Share/Save/Bookmark

Declaring and Initializing Record Types


DECLARE

-- Declare a record type with 3 fields.

TYPE REC1_T IS RECORD(FIELD1 VARCHAR2(16),

FIELD2 NUMBER,

FIELD3 DATE);

-- For any fields declared NOT NULL, we must supply a default value.

TYPE REC2_T IS RECORD(ID INTEGER NOT NULL := -1,

NAME VARCHAR2(64) NOT NULL := '[anonymous]');

-- Declare record variables of the types declared

REC1 REC1_T;

REC2 REC2_T;

-- Declare a record variable that can hold a row from the EMPLOYEES table.

-- The fields of the record automatically match the names and

-- types of the columns.

-- Don't need a TYPE declaration in this case.

REC3 EMPLOYEES%ROWTYPE;

-- Or we can mix fields that are table columns with user-defined fields.

TYPE REC4_T IS RECORD(FIRST_NAME EMPLOYEES.FIRST_NAME%TYPE,

LAST_NAME EMPLOYEES.LAST_NAME%TYPE,

RATING NUMBER);

REC4 REC4_T;

BEGIN

-- Read and write fields using dot notation

REC1.FIELD1 := 'Yesterday';



REC1.FIELD2 := 65;



REC1.FIELD3 := TRUNC(SYSDATE - 1);



-- We didn't fill in the name field, so it takes the default value declared

DBMS_OUTPUT.PUT_LINE(REC2.NAME);

END;

/



Share/Save/Bookmark

Declaring and Initializing a Simple Record Type


DECLARE

TYPE DEPTRECTYP IS RECORD(DEPTID NUMBER(4) NOT NULL := 99,

DNAME DEPARTMENTS.DEPARTMENT_NAME%TYPE,

LOC DEPARTMENTS.LOCATION_ID%TYPE,

REGION REGIONS%ROWTYPE);

DEPT_REC DEPTRECTYP;

BEGIN

DEPT_REC.DNAME := 'PURCHASING';

END;

/



Share/Save/Bookmark

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;

/



Share/Save/Bookmark

How Invalid Subscripts are Handled With DELETE(n)


DECLARE

TYPE NUMLIST IS TABLE OF NUMBER;

NUMS NUMLIST := NUMLIST(10,20,30); -- initialize table



BEGIN

NUMS.DELETE(-1); -- does not raise SUBSCRIPT_OUTSIDE_LIMIT



NUMS.DELETE(3); -- delete 3rd element



DBMS_OUTPUT.PUT_LINE(NUMS.COUNT); -- prints 2



NUMS(3) := 30; -- allowed; does not raise NO_DATA_FOUND



DBMS_OUTPUT.PUT_LINE(NUMS.COUNT); -- prints 3



END;

/



Share/Save/Bookmark

Collection Exceptions


DECLARE

TYPE WORDLIST IS TABLE OF VARCHAR2(5);

WORDS WORDLIST;

ERR_MSG VARCHAR2(100);

PROCEDURE DISPLAY_ERROR

IS

BEGIN

ERR_MSG := SUBSTR(SQLERRM,1,100);



DBMS_OUTPUT.PUT_LINE('Error message = '

||ERR_MSG);

END;

BEGIN

BEGIN

WORDS(1) := 10; -- Raises COLLECTION_IS_NULL



-- A constructor has not been used yet.

-- Note: This exception applies to varrays and nested tables,

-- but not to associative arrays which do not need a constructor.

EXCEPTION

WHEN OTHERS THEN

DISPLAY_ERROR;

END;



-- After using a constructor, we can assign values to the elements.

WORDS := WORDLIST('1st','2nd','3rd'); -- 3 elements created



-- Any expression that returns a VARCHAR2(5) is valid.

WORDS(3) := WORDS(1)

||'+2';



BEGIN

WORDS(3) := 'longer than 5 characters'; -- Raises VALUE_ERROR



-- The assigned value is too long.

EXCEPTION

WHEN OTHERS THEN

DISPLAY_ERROR;

END;



BEGIN

WORDS('B') := 'dunno'; -- Raises VALUE_ERROR



-- The subscript (B) of a nested table must be an integer.

-- Note: Also, NULL is not allowed as a subscript.

EXCEPTION

WHEN OTHERS THEN

DISPLAY_ERROR;

END;



BEGIN

WORDS(0) := 'zero'; -- Raises SUBSCRIPT_OUTSIDE_LIMIT



-- Subscript 0 is outside the allowed subscript range.

EXCEPTION

WHEN OTHERS THEN

DISPLAY_ERROR;

END;



BEGIN

WORDS(4) := 'maybe'; -- Raises SUBSCRIPT_BEYOND_COUNT



-- The subscript (4) exceeds the number of elements in the table.

-- To add new elements, call the EXTEND method first.

EXCEPTION

WHEN OTHERS THEN

DISPLAY_ERROR;

END;



BEGIN

WORDS.DELETE(1);



IF WORDS(1) = 'First' THEN

NULL;

END IF; -- Raises NO_DATA_FOUND



-- The element with subcript (1) has been deleted.

EXCEPTION

WHEN OTHERS THEN

DISPLAY_ERROR;

END;

END;

/


Share/Save/Bookmark

Using the DELETE Method on a Collection


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('Bob') := 'Robert';



NICKNAMES('Buffy') := 'Esmerelda';



NICKNAMES('Chip') := 'Charles';



NICKNAMES('Dan') := 'Daniel';



NICKNAMES('Fluffy') := 'Ernestina';



NICKNAMES('Rob') := 'Robert';



-- following deletes element denoted by this key

NICKNAMES.DELETE('Chip');



-- following deletes elements with keys in this alphabetic range

NICKNAMES.DELETE('Buffy','Fluffy');

END;

/



Share/Save/Bookmark
Newer Posts Older Posts Home Page
 
http://www.dpriver.com/images/sqlpp-banner-2.png

Thanks for reading my Blog ... you Visitor Number :-