PL SQL practice (4)

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

  1.ROW_NUMBER returns the continuous position values are equal, regardless of

1 select deptno,ename,sal, row_number () over (partition by deptno 2 order by sal desc) rn from emp; 3 4 10 KING 5000 1 5 10 CLARK 2450 2 6 10 MILLER 1300 3 7 20 SCOTT 5000 1 8 20 FORD 3000 2 9 20 JONES 2975 3 10 20 ADAMS 1100 4 11 20 SMITH 800 5 12 30 BLAKE 2850 1 13 30 ALLEN 1600 2 14 30 TURNER 1500 3 15 30 WARD 1250 4 16 30 MARTIN 1250 5 17 30 JAMES 950 6

  2.DENSE_RANK has the same value for the same number of qualifying, is continuous

1 select deptno,ename,sal, DENSE_RANK () over (partition by deptno 2 order by sal desc) rn from emp; 3 4 10 KING 5000 1 5 10 CLARK 2450 2 6 10 MILLER 1300 3 7 20 SCOTT 5000 1 8 20 FORD 3000 2 9 20 JONES 2975 3 10 20 ADAMS 1100 4 11 20 SMITH 800 5 12 30 BLAKE 2850 1 13 30 ALLEN 1600 2 14 30 TURNER 1500 3 15 30 WARD 1250 4 16 30 MARTIN 1250 4 17 30 JAMES 950 5


  3.RANK has the same value for the same number of qualifying, then jump

1 select deptno,ename,sal, RANK () over (partition by deptno 2 order by sal desc) rn from emp; 3 4 10 KING 5000 1 5 10 CLARK 2450 2 6 10 MILLER 1300 3 7 20 SCOTT 5000 1 8 20 FORD 3000 2 9 20 JONES 2975 3 10 20 ADAMS 1100 4 11 20 SMITH 800 5 12 30 BLAKE 2850 1 13 30 ALLEN 1600 2 14 30 TURNER 1500 3 15 30 WARD 1250 4 16 30 MARTIN 1250 4 17 30 JAMES 950 6

  4 commonly used predefined exception


NO_DATA_FOUND: Execute the query without data, refer to an end the beginning of the table, through the UTL_FILE packet call to end of file
TOO_MANY_ROWS: Using the SELECT INTO statement, but the returned records more than 1
DUP_VAL_ON_INDEX: insert or update statement, conflict with a unique index

ZERO_DIVIDE:0 is a divisor
INVALID_NUMBER: strings are converted to digital, but failed to convert

VALUE_ERROR: in the implementation of conversion, truncation, illegal conversion of data into a text.
CURSOR_ALREADY_OPEN: opened an open cursor

  5 declare user-defined exception

Declare
my_error EXCEPTION;


Trigger user-defined exception
RAISE my_error

  6 custom program exception


1 RAISE_APPLICATION_ERRORUse 2 3 4 5 DECLARE 6 rate itemfile.itemrate%TYPE; 7 rate_exception EXCEPTION; 8 BEGIN 9 SELECT NVL(itemrate,0) INTO rate FROM itemfile 10 WHERE itemcode = 'i207'; 11 IF rate = 0 THEN 12 RAISE rate_exception; 13 ELSE 14 DBMS_OUTPUT.PUT_LINE('The rate for: ' || rate); 15 END IF; 16 EXCEPTION 17 WHEN rate_exception THEN 18 RAISE_APPLICATION_ERROR(-20001, 'Conditions not specified'); 19 END;

  7 the cursor

  7.1 implicit cursor:

Used in the process of SELECT... INTO... Query, one can extract a row of data from the database, for this form of queries and DML operations, the system will use an implicit cursor

But if you want to extract multiple rows of data, let programmers define an explicit cursors, and with the cursor on the statement to be processed. Explicit cursor corresponds to a return results for the rows and columns of the SELECT statement.


As mentioned before, DML operation and a single SELECT statement uses implicit cursor, they are:
* The insert operation: INSERT.
* The update operation: UPDATE.
* The delete operation: DELETE.
* A single query: SELECT ... INTO ...

When the system uses an implicit cursor, can understand the operation state and the results through attribute implicit cursor, and process control procedures. Implicit cursor can use the name SQL to access

  1. SQL%ROWCOUNT    The data rows integer represents the DML statement executed successfully number
  2. TRUE stands for the insert, delete, update or single query operation success SQL%FOUND Boolean value
  3. SQL%NOTFOUND    The SQL%FOUND property returns a Boolean and values
  4. True SQL%ISOPEN Boolean DML implementation process, after the end of false

  7.2 display the cursor

}Declare cursor: CURSOR ..IS select..
}Open the cursor: OPEN
}The result set control: FETCH..INTO..
}Close cursor: CLOSE
Note: use the for loop to traverse the cursor is simple to use form cursor.
   At the same time, also can use bind variables declared cursor can be reused, improve the swimming performance,
  7.3 the ref cursor
Ref cursor for dynamic SQL statement design
The statement strongly typed ref cursor
TYPE my_curtype IS REF CURSOR
RETURN stud_det%ROWTYPE;
order_cur my_curtype;
1 declare 2 type cur_type is ref cursor; 3 cur cur_type; 4 rec scott.emp%rowtype; 5 str varchar2(50); 6 letter char:= 'A'; 7 begin 8 loop 9 str:= 'select ename from emp where ename like ''%'||letter||'%'''; 10 open cur for str; 11 dbms_output.put_line('Contain letters'||letter||'Name: '); 12 loop 13 fetch cur into rec.ename; 14 exit when cur%notfound; 15 dbms_output.put_line(rec.ename); 16 end loop; 17 exit when letter='Z'; 18 letter:=chr(ascii(letter)+1); 19 end loop; 20 end; 21 22 23 Contain lettersAName: 24 ALLEN 25 WARD 26 MARTIN 27 BLAKE 28 CLARK 29 ADAMS 30 JAMES 31 Contain lettersBName: 32 BLAKE 33 Contain lettersCName: 34 CLARK 35 SCOTT

  8 simple to use lock

  8.1 when modifying data before you can use the select....for update plus a row exclusive, ensure that other users cannot be modified, but not block read.

Row exclusive: insert, delete, update and select for update.

Until commit and rollback releases the row level lock,

  SELECT … FOR UPDATE syntax:
 SELECT … FOR UPDATE [OF columns]
[WAIT n | NOWAIT] [skip locked]
Description: the OF clause is used to specify the column to be updated, the lock specific column line, multi table linked to the use of.
 The WAIT clause to specify the number of seconds to wait for other users to release the lock, prevent wait indefinitely
 Skip locked is not an error, the resources to be locked up for,
  Note the 9.oracle transaction.:
}In Oracle, execute the DDL statement (such as Create Table, Create View), automatically sends a Commit command before it is executed, and then sends a Commit or Rollback command, that is to say, DDL performs like the following code:
}
}Commit;
DDL_Statement;
If (Error) then
Rollback;
Else
Commit;
End if;
  Note 10 function:
Q limits defined function:
The Q function can accept IN parameters, and can not accept IN OUT or OUT parameters
The Q parameter cannot be PL/SQL type
The return type of Q function must be the type of database
Two ways of Q access function:
Q uses the PL/SQL block
Q using the SQL statement
  The 11 trigger
Common triggers for row level triggers, the use of before or after associated DML statement,
For the DDL statement level trigger audit: such as deleting, inserting, updating
For the database level triggers audit: create or replace trigger trg_session before logoff on database
For some can not modify the view operation triggers, the view of the operation into table operation: instead of
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Quentina at December 03, 2013 - 11:34 AM