Locks

Locks Locks are used to ensure data integrity while allowing maximum concurrent access to the data by unlimited users 1. 2.

Types of locks According to the way locks are applied Implicit locking Explicit locking Implicit locking This will be done automatically by DBA based on the DDL or DML processed. Readers of data do not wait for other readers or writers. Writers of data do not wait for other readers Writers of data have to wait for other writers if they attempt to update the same rows at the same time. Explicit locking A lock can be put by an operator or trigger is called explicit locking Oracle provides two levels of automatic locking Table level & row level

1. 2.

There are two ways of locking the table or rows Select ………for update (row level locking) Lock table statement (table level locking) 1. Select … for update Can not be used with distinct and group by clause Can not be used with set operators and group functions Row level locking. 2

Lock table statement Table level locking Lock table table_name in share/share update/exclusive mode; Share lock Locks the table allowing other users to only query but not insert, update or delete rows in table Multiple users can place share locks on the same table at the same time Share update lock Locks the only rows that are effected. Allowing other users to query, insert, update, delete or even lock the other rows in table Exclusive lock Same as share lock but only one user can place this lock mode on the table at a time. Where as share lock can be placed, by many users on same table If the rows or table , are already locked by another user, then Oracle will wait for the lock to be released by a commit or rollback statements. How ever we can use NOWAIT option to cause Oracle to terminate the statement. A rollback to save point releases all locks acquired, up to the named save point are released

1

Functions Functions are a very powerful feature of SQL and can be used to do the following:

    

Perform calculations on data Modify individual data items Manipulate output for groups of rows Format dates and numbers for display Convert column datatypes

SQL functions may accept arguments and always return a value. Types of functions • • • • • •

Character Number Date Conversion Function that accept any data type Group or aggregate Character functions Chr(n) It returns the character having the binary equivalent to n in the database

character set. Chr(67) returns ‘C’ ASCII(‘C’) It returns the numeric value in the database character set. ASCII(‘C’) returns 67 Lower(Column_name/value) Upper(Column_name/value) Initcap(Column_name/value) Concat(char1,char2) Lpad(column_name, n,’ string’) Pads the column or literal value from the left to a total width of n character positions. The leading space are filled with string Select lpad(ename,20,’*’) ***************smith If ename is 10 characters long then 10 * s will be padded at left of the ename Rpad (column_name, n, ’string’) Pads the column or literal value from the right to a total width of n character positions. The tailing space are filled with string Select rpad(ename,20,’*’) smith*************** If ename is 10 characters long then 10 * s will be padded at right of the ename Substr(column-name/value,pos,number of characters) If number of characters are omitted, it returns up to end of the string Instr(Column_name/value,’string’) Gives the character Position of the first accurrence of the string in the column or value. Select dname ,instr(dname,’A’) from dept; Dname instr(dname,’A’) Accounting 1 Instr(col/value,’string’,pos,n) Gives the character Position of the first accurrence of the string in the column or value starting at the pos.

2

Select dname ,instr(dname,’C’,1,2) from dept; Dname instr(dname,’c’,1,2) Accounting 3 LTRIM Ltrim(Column_name/val)—trims leading spaces Ltrim(Column_name/val,’char’) – trims leading specified character in the value RTRIM Rtrim(Column_name/val)—trims tailing spaces Rtrim(Column_name/val,’char’) – trims tailing specified character in the value Soundex Returns a character string sound a like Select ename from emp where Soundex(ename) = ‘FRED’ Ename Ford Length Returns length of the string in characters. Translate Translate(col/val,’fromchar’,’tochar’) Used for character substitution If tochar is omitted, from char will be removed from the column/value Replace replace(col/val,’fromstring’,’tostring’) Used for string substitution If tostring is omitted, fromstring will be removed from the column/value Replace is superset of translate Number functions Round(col/val,n) N is the number of positions after decimal to be rounded. If n is omitted then no decimal points. Round(45.932) = 46 Round(45.932,1) = 45.9 Round(42.932,-1) = 40 Round(42.932,-2) = 42.93 Trunc(col/val,n) N is the number of positions after decimal to be rounded. If n is omitted then no decimal points. trunc(45.932) = 45 trunc(45.932,1) = 45.90 trunc(42.932,-1) = 40 trunc(42.932,2) = 42.93 Ceil Ceil(col/val) Finds the smallest integer greater than or equal to the column/value Ceil(45.980) = 46 Floor floor(col/val) Finds the largest integer less than or equal to the column/value Floor(46.980) = 46 SQRT Sign Power(col/val,n) Abs(col/val) Finds the absolute value

3

Abs(-35) = 35 Mod(value1,value2) Date Functions Date + number = adds number of days to date and gives a date Date – number= subtracts number of days from date and gives a date Date – Date = gives number of days Months_between(date1,date2) – returns the number of months between the two dates Add_months(date1,n) – returns the date after adding n months to the date1 Next_day(date1,day of week/day number) – gives date of the next day after that day of week or after that many days Nextday(sysdate,3) Nextday(sysdate,’Monday’) Last_day(date1) – return the date of the last day in the month mentioned in date1 New_time(date1, Z1, Z2 ) – returns the date and time in time zone Z1 and Z2 can be the following values EST, EDT – eastern standard or daylight time CST, CDT – central standard or daylight time MST, MDT – mountain standard or daylight time PST, PDT – pacific standard or daylight time GMT – greenwich mean time Sysdate – return the current date of the server Conversion Functions To_char(date/number,’format’) To_number(char contains number) To_date(‘char’,’format’) Functions that accept any datatype as argument Decode(col/expression,search1,result1,search2,result2 ……default) Decode(job,’clerk’,’is a clerk’,’manager’,’is a manager’… ‘supervisor’) NVL function If a column in a row is lack of data in it, then the data will be said to be null A null value take up one byte of internal storage overhead If a column has null value we can see it any more in SQL result. We can handle this using NVL function. Select ename,sal* 12 + nvl(comm,0) annsal from emp If Comm is null it returns 0 as a value. Greatest(col/val1,col/val2) Least(col/val1,col/val2) Group /Aggregate functions AVG - Select avg(sal) from emp; CountMaxMinStddivSum-

4