Oracle Study Notes 2

To_date ('1970-11-11 ', 'yyyy-mm-dd') // specify a date in the format
Insert into kkk (myid, myname, mydept) select empno, ename, deptno from emp where deptno = 10; // row migration
Update emp set (job, sal, comm) (select job, sal, comm from emp where ename = 'Smith ') where ename = 'Scott'; // update Multiple Fields
Savepoint a1; // The number of storage points is not limited.
Rollback to a1; // roll back to a1 storage point
Commit; // The save point will disappear after the commit operation. If the exit operation is performed, the commit operation is automatically performed, and the data will not take effect before the commit operation,

Conn. setAutoCommit (false); // sets the transaction, which is not committed at the moment.
Sm.exe cuteUpdate ("update emp set sal = sal-100 where ename = 'Scott '");
Sm.exe cuteUpdate ("update emp set sal = sal + 100 where ename = 'Smith '");
Conn. commit (); // submit the transaction
Conn. rollback (); // if an accident occurs, roll back

Set transaction read only // read-only transaction

// Function
Lower () select lower (ename), sal form emp; // lower case
Upper () select upper (ename), sal form emp; // uppercase
Select * from emp where length (ename) = 5; // The name is 5
Select substr (ename, 1, 3) form emp; // The first three characters starting with the name

Select upper (substr (ename, 1, 1) from emp;
Select lowwer (substr (ename, 2, length (ename)-1) from emp;
Select upper (substr (ename,) | lowwer (substr (ename, 2, length (ename)-1) from emp; // upper-case letters, lower-case letters, concatenated string

Select replace (ename, 'A', 'iama ') from emp; // use IAMA to replace A, In the ename Field
Mathematical functions: floor, trunc, mod, round, ceil, cos, cosh, exp, ln, log, sin, sinh, sqrt, tan,
Round (sal, 1) // rounded to the last decimal point. By default, it is rounded to an integer. If it is a negative number, the entire number is truncated.
Tranc (sal, 1) // The first place after the decimal point. The default value is an integer.
Tranc (sal,-1) // extract digits
Floor (sal) // round down
Ceil (sal) // rounded up
Dual // dummy meta table, which is used for testing by oracle
Mod (m, n) // modulo

1. Access the database using java and connect to the jdbc_odbc Bridge
1. Load the driver
Class. forName ("sun. jdbc. odbc. JdbcOdbcDriver ");
2. Obtain the connection
Connection con = DriverManager. getConnection ("jdbc: odbc: testsp", "scott", "tigger"); // testsp local data source management tool
3,
Statement st = con. createStatement ();
ResultSet rs1_st.exe cuteQuery ();//??
While (rs. next ()){}
4. Close Resources
2. Import the jar package of oracle for jdbc connection
Class. forName ("oracle. jdbc. driver. OracleDriver ");
Connection con = DriverManager. getConnection ("jdbc: oracle: thin: @ 127.0.0.1: 1521: myora1", "scott", "m123 ");

Pagination:
Int pageNow = Integer. parseInt (String) request. getParameter ("pageNow); // obtain the page number requested by the current customer
Int pageCount = 0, pageSize = 3, rowCount = 0;
ResultSet rs1_st.exe cuteQuery ("select * from emp ;");
While (rs. next ()){
RowCount = Integer. parseInt (rs. getInt (1 ));
}
If (rowCount % pageCount = 0 ){
PageCount = rowCount/pageCount;
} Else {
PageCount = rowCount/pageCount + 1; // obtain the total number of pages
}
Rs1_st.exe cuteQuery ("select * from (select a1. *, rownum rn from (select * from emp) a1 where rn <=" + pageSize * pageNow + ") where rn> = "+ (pageNow-1) * pageSize-1 +"/""); // query pages

For (int I = 0; I <pageCount; I ++ ){
Out. println ("<a href = myTest. jsp? PageNow = "+ I +"> ["+ I +"] </a> "); // print the page number and page number.
}

Out. println ("<tr> ");
Out. println ("</tr> ");

While (rs. next ()){
Out. println ("<tr> ");
Out. println ("<td> ");
Out. println (rs. getString (1); // name
Out. println ("</td> ");

Out. println ("<td> ");
Ount. println (rs. getString (6); // salary
Out. println ("</td> ");
Out. println ("</tr> ");
}