jdbcÖе÷ÓÃoracleº¯Êý
1¡¢´´½¨±í
create table stud(
sid int,
sname varchar2(50),
age number,
score number(4,2))
²¢²åÈëһЩÊý¾Ý£¨×Ô¼ºÊÖ¶¯²åÈëһЩ°É£©
2¡¢´´½¨º¯Êý
create or replace function fun_getScores(
v_age in stud.age%type
)
return number
is
v_score number;
begin
select sum(score) into v_score from stud where age > v_age;
return v_score;
--Òì³£´¦Àí
exception
when others then
dbms_output.put_line(sqlcode||sqlerrm);
return -1;
end;
3¡¢jdbcÖе÷ÓÃ
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Types;
public class TestFun {
public void test(){
Connection con = null;
Statement st = null;
ResultSet rs = null;
CallableStatement cst = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
//ÏÂÃæµÄtanÊÇÊý¾Ý¿âÃû£¬Ä¬ÈÏÊÇorcl£¬loveÊÇ·ÃÎÊÃÜÂ룬ĬÈÏÊÇtiger,
//1521ÊǶ˿ںţ¬Ä¬ÈÏÊÇ1521
con = DriverManager.getConnection
("jdbc:oracle:thin:@192.168.1.103:1521:tan","scott","love");
String sql = "{? = call fun_getScores(?)}";
cst = con.prepareCall(sql);
cst.registerOutParameter(1, Types.NUMERIC);
cst.setDouble(2, 2);
cst.execute();
double result = cst.getDouble(1);
System.out.println("ÄêÁ䳬¹ý2ËêѧÉúµÄ³É¼¨×ÜºÍ "+result);
}catch(Ex
Ïà¹ØÎĵµ£º
ÊÓͼ
´´½¨ÐÂ±í£ºcreate table emp2 as select * from emp;
create view empv20 as select empno,ename,job,hiredate,deptno from emp where deptno=20 with check option;
Óï·¨£ºcreate or replace view ÊÓͼÃû³Æ as ×Ó²éѯ£¨ÐÞ¸ÄÖ®ºóµÄ×Ó²éѯ£©
Ìæ»»ÊÓͼ(ÐÞ¸Ä)
create or replace view empv20 as select empno,ename, ......
1. ½âÊÍÀ䱸·ÝºÍÈȱ¸·ÝµÄ²»Í¬µãÒÔ¼°¸÷×ÔµÄÓŵã
½â´ð£ºÈȱ¸·ÝÕë¶Ô¹éµµÄ£Ê½µÄÊý¾Ý¿â£¬ÔÚÊý¾Ý¿âÈԾɴ¦ÓÚ¹¤×÷״̬ʱ½øÐб¸·Ý¡£¶øÀ䱸·ÝÖ¸ÔÚÊý¾Ý¿â¹Ø±Õºó£¬½øÐб¸·Ý£¬ÊÊÓÃÓÚËùÓÐģʽµÄ
Êý¾Ý¿â¡£Èȱ¸·ÝµÄÓŵãÔÚÓÚµ±±¸·Ýʱ£¬Êý¾Ý¿âÈԾɿÉÒÔ±»Ê¹Óò¢ÇÒ¿ÉÒÔ½«Êý¾Ý¿â»Ö¸´µ½ÈÎÒâÒ»¸öʱ¼äµã¡£À䱸·ÝµÄÓŵãÔÚÓÚËüµÄ±¸·ÝºÍ»Ö¸´
²Ù×÷Ïൠ......
1£© ½¨Á¢ÐòÁÐÃüÁî
CREATE SEQUENCE [user.]sequence_name
[increment by n]
[start with n]
[maxvalue n | nomaxvalue]
[minvalue n | nominvalue];
INCREMENT BY£º Ö¸¶¨ÐòÁкÅÖ®¼äµÄ¼ä¸ô ......
ĬÈÏÇé¿öÏ£¬ÔÚOracleµÄÖ÷»úÉÏ£¨linuxϵͳ£©µÄOracle²Ù×÷ϵͳÓû§Ï£¬ÒÔsysÓû§µÇ½Êý¾Ý¿âʱ¿ÉÒÔ²»ÓÃÊäÈëÃÜÂëÖ±½ÓµÇ¼¡£Êµ¼ÊÉÏ£¬Ö»ÒªÊÇÊôÓÚdba×飨linux£©»òosdba×飨windows£©Ï£¬¶¼¿ÉÒÔ²»ÓÃÃÜÂë½øÐеǼ¡£ÕâÊÇÈÏÖ¤·½Ê½³ÆÎªOSÈÏÖ¤¡£
ÕâÖֵǼ·½Ê½µÄ¿ØÖÆ£¬ÊÇͨ¹ý$ORACLE_HOME/network/admin/sqlnet.oraÖеģº
SQLNET.AUTH ......