常用sql

//ORCLE控制台以SYSDBA这个角色登陆SQLPLUS
sqlplus / as sysdba
//用sysdba进入的时候,就可以改密码了
alter  user  username account  unlock  identifed  by  password
//给用户授予权限:包含所有权限名称
GRANT
CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE,
ALTER ANY TABLE, ALTER ANY PROCEDURE,
DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,
SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE
TO username;


//查看表空间路径
select * from DBA_DATA_FILES t
//查看所有用户:
SELECT * FROM DBA_USERS;
SELECT * FROM ALL_USERS;
SELECT * FROM USER_USERS;
//查看用户系统权限:
SELECT * FROM DBA_SYS_PRIVS;
SELECT * FROM USER_SYS_PRIVS;
//查看用户对象或角色权限:
SELECT * FROM DBA_TAB_PRIVS;
SELECT * FROM ALL_TAB_PRIVS;
SELECT * FROM USER_TAB_PRIVS;
//查看所有角色:
SELECT * FROM DBA_ROLES;
//查看用户或角色所拥有的角色:
SELECT * FROM DBA_ROLE_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;


//复制表结构
CREATE TABLE MSG_TABLE_201310 as SELECT * FROM MSG_TABLE_201309 WHERE 1=2


imp userid=用户名/密码@orcl file=d:\nc60.dmp full=y
//DBA身份导出
exp 用户名/密码@orcl file=F:\090528tstapp.dmp full=y
//遇到导出错误1455 转换列溢出整数数据类型
exp 用户名/密码@orcl file=F:\090528tstapp.dmp indexes=n statistics=none
//能源库数据导出
exp energyzhj/energyzhj@energy file=F:\20130906.dmp


IMP energyzhj/energyzhj@ORCL log=C:\plsimp.log file=f:\0619.dmp fromuser=energyzhj touser=energyzhj ignore=yes tablespaces=TELEPLATFORM


exp energyzhj/energyzhj@172.18.50.107 file=f:\090909.dmp 
exp teleplatform/teleplatform@orcl file=f:\teleplatform.dmp 


drop user user01 cascade


CREATE USER energyzhj IDENTIFIED BY energyzhj   
PROFILE DEFAULT    DEFAULT TABLESPACE TELEPLATFORM
TEMPORARY TABLESPACE TELEPLATFORM_TEMP  
ACCOUNT UNLOCK;

drop tablespace zfmi including contents and datafiles ; 


create tablespace TELEPLATFORM 
logging 
datafile 'D:\oracle\oradata\zfmi\zfmi.dbf' 
size 100m 

create temporary tablespace zfmi 
tempfile 'D:\oracle\oradata\zfmi\zfmi.dbf' 
size 100m 
grant connect,resource,dba to wmlt;


 alter user user01 identified by user10;

imp energyzhj/energyzhj file=dbuser.dmp log=dbuser.log fromuser=dbuser touser=dbuser2 buffer=2048000 commit=y ignore=y feedback=10000


根据网上提供的解决方法找到脚本E:\Program Files (x86)\oracle\product\10.2.0\db_1\RDBMS\ADMIN/catqueue.sql


//查询重点计量点
select t1.*,t2.mpid,t2.mpname,t2.rtuid,t2.mpsn,t3.rtuname
from AP4_KEY_USER t1,amp_zb t2,artu_zb t3
where t1.sn=t2.mpsn
and t1.jzqid=t2.rtuid
and t2.rtuid=t3.rtuid


//生成sql树查询
select A.SYSCST_ID,A.SYSCST_FID, lpad('     ', level * 5, '--') || A.SYSCST_NAME FNAME
    from (select B.SYSCST_ID, B.SYSCST_NAME, B.SYSCST_FID from sysconst_t B where 1 = 1) A
    connect by A.SYSCST_FID = prior A.SYSCST_ID
    start with A.SYSCST_FID = 10006;


//递归查询包含此节点及此节点下的所有集
select A.SYSCST_ID
    from sysconst_t A
    connect by A.SYSCST_FID = prior A.SYSCST_ID
    start with A.SYSCST_ID = 10017;


//外关联空表查询
sql="select mp.mpid,mp.mpname,mp.rtuid " +
" from amp_zb mp,app_amp_zb_ext mpext " +
" where mp.mpid = mpext.mpid(+) " +
" and mpext.mpid is null ";


//查询层次树计量点信息
SELECT RPAD( ' ', 5*(LEVEL-1), '--' ) || a.mpname mpname,
CONNECT_BY_ROOT mpname ROOT,
CONNECT_BY_ISLEAF ISLEAF,
LEVEL 
--SYS_CONNECT_BY_PATH(a.mpname, '/') PATH
FROM app_amp_zb_ext e,amp_zb a
where a.mpid = e.mpid and mpname not like '%虚%'
START WITH mpid_preid  is null
CONNECT BY PRIOR e.mpid = e.mpid_preid;


//更改计量点电量倍率
update amp_ptct t set t.factor=200 where t.mpid='1160005921'




//通过表名生成grid的model、column结构
select t.*, t.rowid from user_col_comments t where t.table_name = 'MSN_ACCOUNT';


select * from user_tab_cols t where t.table_name = 'MSN_ACCOUNT';


select t.column_name,t.comments, t1.data_type
from user_col_comments t ,user_tab_cols t1
where t.table_name = 'MSN_ACCOUNT'
and t1.table_name = t.table_name
and t1.column_name = t.column_name;


//不带参数的游标使用
create or replace procedure testCursor is
 
 cur SYS_REFCURSOR;
      rec     student%ROWTYPE;
  BEGIN
      
      OPEN cur FOR SELECT * FROM  student;
      LOOP
          FETCH cur INTO rec;
          EXIT WHEN cur%NOTFOUND;
          dbms_output.put_line(rec.math);
      END LOOP;


end testCursor;


//带参数的游标使用
create or replace procedure paramCursor(id in number) is
  rec student%ROWTYPE;
  CURSOR C_USER(C_ID NUMBER) IS SELECT * FROM student WHERE stdid=C_ID;
  BEGIN
  OPEN C_USER(id);
  LOOP
    FETCH C_USER INTO rec;
    EXIT WHEN   C_USER%NOTFOUND;
    dbms_output.put_line(rec.math);
  END LOOP;
  CLOSE C_USER;


end paramCursor;


//if使用实例
create or replace procedure test1(
in_a in integer)
as
begin
if in_a<100 then
dbms_output.put_line('小于100.');
elsif in_a<200 then
dbms_output.put_line('大于100小于200.');
else
dbms_output.put_line('大于200.');
end if;
end test1;


//case when使用实例
create or replace procedure test2(in_a in integer)
as
begin
case in_a
when 1 then
dbms_output.put_line('小于100.');
when 2 then
dbms_output.put_line('大于100小于200.');
else
dbms_output.put_line('大于200.');
end case;
end test2;


//loop使用实例1
create or replace trigger trig_job 
　　before insert or update of job 
　　on emp 
　　for each row 
　　begin 
　　if inserting then 
　　:new.job:=upper(:new.job); 
　　else 
　　: new.job:=upper(:new.job); 
　　end if; 
　　end; 


//loop使用实例2
create or replace procedure loop2(
in_a in integer)
as
a integer;
begin
a:=0;
while a<300 loop
dbms_output.put_line(a);
a:=a+1;
end loop;
end loop2;


//loop使用实例3
create or replace procedure loop3(
in_a in integer)
as
a integer;
begin
for a in 0..300
loop
dbms_output.put_line(a);
end loop;
end loop3;


//触发器实例
create or replace trigger trig_stdid
  before insert or update or delete on student
 for each row
  begin
   if inserting then
   insert into out_school(STDID) values(5);
   elsif updating then
   insert into out_school(STDID) values(6);
   elsif deleting then
   insert into out_school(STDID) values(8);
  end if;
  end trig_stdid;




//定义函数
create or replace function bool_to_char(Pbool in boolean)
return varchar2 is
  str varchar2(5);--capture string to return
begin
  if(Pbool) then -- test boolean value for true
     str:='true';
  elsif(not Pbool) then --false
     str:='false';
  else --must be null
     str:='null';
  end if;  -- test boolean value
  return(str);
  end bool_to_char;




//函数测试单元
set serveroutput on
begin
  dbms_output.enable;
  dbms_output.put_line(bool_to_char(true));
  dbms_output.put_line(bool_to_char(false));
  dbms_output.put_line(bool_to_char(null));
end;




//无返回值的存储过程
CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS
BEGIN 
  INSERT INTO test.B_ID (I_ID,I_NAME) S (PARA1, PARA2);
END TESTA;


//JAVA代码
package test;


import java.sql.*;
import java.sql.ResultSet;


public class TestProcedureOne {
public TestProcedureOne() {
}
public static void main(String[] args ){
    String driver = "oracle.jdbc.driver.OracleDriver";
    String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    Statement stmt = null;
    ResultSet rs = null;
    Connection conn = null;
    CallableStatement cstmt = null;


    try {
      Class.forName(driver);
      conn = DriverManager.getConnection(strUrl, "test", "test");
      System.out.println(conn);
      CallableStatement proc = null;
      proc = conn.prepareCall("{ call TESTA(?,?) }");
      proc.setString(1, "100");
      proc.setString(2, "TestOne");
      proc.execute();
    }
    catch (SQLException ex2) {
      ex2.printStackTrace();
    }
    catch (Exception ex2) {
      ex2.printStackTrace();
    }
    finally{
      try {
        if(rs != null){
          rs.close();
          if(stmt!=null){
            stmt.close();
          }
          if(conn!=null){
            conn.close();
          }
        }
      }
      catch (SQLException ex1) {
      }
    }
}
}
--------------------------------------------------------------------------------------------


//有返回值的存储过程（非列表）
CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS
BEGIN 
  SELECT I_NAME INTO PARA2 FROM B_ID  WHERE I_ID= PARA1; 
END TESTB;


//JAVA代码
package test;
import java.sql.*;
import java.sql.ResultSet;




public class TestProcedureTWO {
public TestProcedureTWO() {
}
public static void main(String[] args ){
    String driver = "oracle.jdbc.driver.OracleDriver";
    String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    Statement stmt = null;
    ResultSet rs = null;
    Connection conn = null;
    try {
      Class.forName(driver);
      conn = DriverManager.getConnection(strUrl, "test", "test");
      CallableStatement proc = null;
      proc = conn.prepareCall("{ call TESTB(?,?) }");
      proc.setString(1, "100");
      proc.registerOutParameter(2, Types.VARCHAR);
      proc.execute();
      String testPrint = proc.getString(2);
      System.out.println("=testPrint=is="+testPrint);
    }
    catch (SQLException ex2) {
      ex2.printStackTrace();
    }
    catch (Exception ex2) {
      ex2.printStackTrace();
    }
    finally{
      try {
        if(rs != null){
          rs.close();
          if(stmt!=null){
            stmt.close();
          }
          if(conn!=null){
            conn.close();
          }
        }
      }
      catch (SQLException ex1) {
      }
    }
}
}


--------------------------------------------------------------------------------------------
//返回列表的存储过程
由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，
但由于是集合，所以不能用一般的参数，必须要用pagkage了.所以要分两部分，
1， 建一个程序包。如下：


CREATE OR REPLACE PACKAGE TESTPACKAGE  AS
TYPE Test_CURSOR IS REF CURSOR;
end TESTPACKAGE;


2，建立存储过程，存储过程为：
CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS 
BEGIN
    OPEN p_CURSOR FOR SELECT * FROM B_ID;
END TESTC;
可以看到，它是把游标（可以理解为一个指针），作为一个out 参数来返回值的。




//在java里调用时就用下面的代码：
package test;


import java.sql.*;
import java.io.OutputStream;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;


import oracle.jdbc.driver.*;




public class TestProcedureTHREE {
public TestProcedureTHREE() {
}
public static void main(String[] args ){
    String driver = "oracle.jdbc.driver.OracleDriver";
    String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    Statement stmt = null;
    ResultSet rs = null;
    Connection conn = null;


    try {
      Class.forName(driver);
      conn = DriverManager.getConnection(strUrl, "test", "test");


      CallableStatement proc = null;
      proc = conn.prepareCall("{ call testc(?) }");
      proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
      proc.execute();
      rs = (ResultSet)proc.getObject(1);


      while(rs.next())
      {
          System.out.println("<tr><td>" + rs.getString(1) + "</td><td>"+rs.getString(2)+"</td></tr>");
      }
    }
    catch (SQLException ex2) {
      ex2.printStackTrace();
    }
    catch (Exception ex2) {
      ex2.printStackTrace();
    }
    finally{
      try {
        if(rs != null){
          rs.close();
          if(stmt!=null){
            stmt.close();
          }
          if(conn!=null){
            conn.close();
          }
        }
      }
      catch (SQLException ex1) {
      }
    }
}
}
-------------------------------------------------------------------------------------------------


//在包中定义数组类型myArray
create or replace package myPackage is
type stdInfo is record(stdId varchar(30),coment varchar(10));
type myArray is table of stdInfo index by binary_integer;
end myPackage;


//取得学生评论信息的存储过程
create or replace procedure get_coment(comentArray out myPackage.myArray) is
 rs SYS_REFCURSOR;
record myPackage.stdInfo;
stdId varchar(30);
coment varchar(1);
i number;
begin
open rs for select stdId,coment from out_school;
 i := 1;
LOOP
 fetch rs into stdId,coment; exit when rs%NOTFOUND;
record.stdId := stdId;
 record.coment := coment;
comentArray(i) := record;
i:=i + 1;
end LOOP;
end get_coment;


//通过存储过程自动计算出每位学生的总成绩和平均成绩，同时，如果学生在课外课程中获得的评价为A，就在总成绩上加20分。
create or replace procedure autocomputer(step in number) is
rsCursor SYS_REFCURSOR;
commentArray myPackage.myArray;
math number;
article number;
language number;
music number;
sport number;
total number;
average number;
stdId varchar(30);
record myPackage.stdInfo;
i number;
begin
i := 1;
get_comment(commentArray); --调用名为get_comment()的存储过程获取学生课外评分信息
OPEN rsCursor for select stdId,math,article,language,music,sport from student t where t.step = step;
LOOP
fetch rsCursor into stdId,math,article,language,music,sport; exit when rsCursor%NOTFOUND;
total := math + article + language + music + sport;
for i in 1..commentArray.count LOOP 
<<l_Begin_Loop>>
 record := commentArray(i);    
if stdId = record.stdId then  
 begin     
 if record.coment ='A' then     
  begin         
 total := total + 20;   
   goto l_Begin_Loop; --使用goto跳出for循环       
  end;    
end if;  
end;  
end if;
end LOOP;
<<continue>>  average := total / 5;
 update student t set t.total=total , t.average = average where t.stdId = stdId;
end LOOP;
end autocomputer;


解决oracle锁表问题:
1.下面的语句用来查询哪些对象被锁：


　　select object_name,machine,s.sid,s.serial#


　　from v$locked_object l,dba_objects o ,v$session s


　　where l.object_id　=　o.object_id and l.session_id=s.sid;


　　2.下面的语句用来杀死一个进程：


　　alter system kill session '24,111'; (其中24,111分别是上面查询出的sid,serial#)


　　【注】以上两步，可以通过Oracle的管理控制台来执行。 用户一定要具有相应权限。


　　3.如果利用上面的命令杀死一个进程后，进程状态被置为"killed"，但是锁定的资源很长时间没有被释放，那么可以在os一级再杀死相应的进程（线程），首先执行下面的语句获得进程（线程）号：


　　select spid, osuser, s.program


　　from v$session s,v$process p


　　where s.paddr=p.addr and s.sid=24 （24是上面的sid）


　　4.在OS上杀死这个进程（线程）：


　　1)在unix上，用root身份执行命令:


　　#kill -9 12345（即第3步查询出的spid）


　　2)在windows（unix也适用）用orakill杀死线程，orakill是oracle提供的一个可执行命令，语法为：


　　orakill sid thread


　　其中：


　　sid：表示要杀死的进程属于的实例名


　　thread：是要杀掉的线程号，即第3步查询出的spid。


　　例：c:>orakill orcl 12345