Orcale语法

最新推荐文章于 2023-08-13 20:44:07 发布

iteye_6967

最新推荐文章于 2023-08-13 20:44:07 发布

阅读量205

点赞数

分类专栏： Orcale 文章标签： Oracle Hibernate

Orcale 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

一 Orcale的大字段用clob,图像用blob,clob字段在Hibernate的映射文件中用String就可以

二 Orcale备份数据库表：

create table test_bak as select * from test

三 Orcale配置客户端：

D:\oracle\product\10.1.0\Db_1\NETWORK\ADMIN\tnsnames.ora 添加

CSMW =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.93)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = CSMW)
    )
)

四 VARCHAR2(10) 可以存10个字符，5个汉字

五

1）复制表结构及其数据：

create table table_name_new as select * from table_name_old

2）只复制表结构：

create table table_name_new as select * from table_name_old where 1=2;

六查找表中某列的重复记录

select *
          from User u
         where u.user_name in (select u.user_name
                                 from User u
                                group by u.user_name   having count(*) > 1)

七存储过程：

create or replace procedure test(age in number,sex in varchar,a out number) is

v_count number;

v_errorcode number;
v_errormsg varchar2(1024);

begin
v_count :=1;
insert into USERS values(v_count,'zz',age,to_char(sex));

a := v_count;
dbms_output.put_line('aaaa');
commit;

end;

存储过程填给两个参数赋值：

SELECT field_name,data_type into v_fieldName,v_dataType
FROM tapp_field WHERE table_id = p_tableID and field_id = p_fieldID;

八游标

动态游标：动态游标虽然可以使游标变的很灵活，但是这使得在使用函数的时候才能确定是用哪个SQL语句，导致性能会差很多，再数据量小的时候再考虑使用。

create or replace function QBLRESULTINFO(Id in varchar2,inputName in varchar2) return varchar2 is
  Result varchar2(100); --这里必须是varchar2型，因为hi_antigen字段是varchar2型，字符串相加的话
                        --类型要相同
  v_sql varchar2(200);
  v_str varchar2(200);
  TYPE   c_table   IS   REF   CURSOR;  --因为这里要使用动态的字段名称，所以这里要声明为动态游标
  getResult c_table;
begin

  if inputName='hi_result' then    --不同的字段名称，对应的SQL语句也不同，这样写避免了每个字段写一个函数
  v_sql := 'select decode('||inputName||',''1'',''阳性'',''2'',''阴性'')  from lws_q_bl_result_info  i where i.id_parent = '''||Id||'''';
  elsif inputName='hi_antigen' then
  v_sql := 'select (select d.names
          from lws_dd_dict_qbl d
         where d.code = '||inputName||' and pid = 17) from lws_q_bl_result_info  i where i.id_parent = '''||Id||'''';
  end if;



  open getResult for v_sql ;
  loop
  fetch getResult into v_str;
  exit when getResult%notfound;   --必须放在Result := Result ||','|| v_str; 这句之前，否则会多执行一次该句，
                                  --因为loop是直到见到exit才退出循环的
     if(v_str is not null) then
        Result := Result ||','|| v_str;  --结果应为“阳性，阴性”这样
     end if;


  end loop;
  close getResult;


/* for z in (v_str) loop             --这里无法使用隐式游标，因为SQL语句是动态的

             if(z.hi_antigen is not null) then

              Result := Result ||','|| z.hi_antigen ;
             end if;
          end loop; */

  if(substr(Result,1,1)=',') then         --去掉字符串开头的逗号
  Result := substr(Result,2,length(Result));
  end if;

  return(Result);
end;

显示游标：

create or replace function QBLRESULTINFO_SRH_YANG(Id in varchar2) return varchar2 is
  Result varchar2(100); --这里必须是varchar2型，因为hi_antigen字段是varchar2型，字符串相加的话
                        --类型要相同
  v_str varchar2(200);
  
 cursor getResult is select (select d.names    --声明游标
          from lws_dd_dict d
         where d.code = srh_yang and pid = 23) from lws_q_bl_result_info  i where i.id_parent = Id; 
begin

open getResult;
  loop
  fetch getResult into v_str;
  exit when getResult%notfound;   --必须放在Result := Result ||','|| v_str; 这句之前，否则会多执行一次该句，
                                  --因为loop是直到见到exit才退出循环的
     if(v_str is not null) then
        Result := Result ||','|| v_str;  --结果应为“阳性，阴性”这样
     end if;


  end loop;
  close getResult;


  if(substr(Result,1,1)=',') then         --去掉字符串开头的逗号
  Result := substr(Result,2,length(Result));
  end if;

  return(Result);
end;

隐式游标：

create or replace function ANTIGEN(Id in varchar2) return varchar2 is
  Result varchar2(100); --这里必须是varchar2型，因为hi_antigen字段是varchar2型，字符串相加的话
                        --类型要相同
begin
   
  
 for z in (select decode(hi_antigen,'1','阳性','2','阴性') hi_antigen  from lws_q_bl_result_info  i where i.id_parent = Id) loop   

             if(z.hi_antigen is not null) then
           
              Result := Result ||','|| z.hi_antigen ;
             end if;  
          end loop;   
   
  if(substr(Result,1,1)=',') then         --去掉字符串开头的逗号
  Result := substr(Result,2,length(Result));
  end if;
  
  return(Result);
end ANTIGEN;

ps: 静态游标（显示游标和隐式游标）要比动态游标效率高。动态游标只有在以下情况下使用。

1）把结果集返回给客户端；
2）在多个子例程之间共享游标；
3）没有其他有效的方法来达到你的目标时，则使用ref游标，正如必须用动态SQL时那样；

普通cursor与REF cursor还有一些大家应该都熟悉的区别，我再浪费点唾沫。

1）PL/SQL静态光标不能返回到客户端，只有PL/SQL才能利用它。ref光标能够被返回到客户端，这就是从Oracle的存储过程返回结果集的方式。

2）PL/SQL静态光标可以是全局的，而ref光标则不是。也就是说，不能在包说明或包体中的过程或函数之外定义ref光标。只能在定义ref光标的过程中处理它，或返回到客户端应用程序。

3）ref光标可以从子例程传递到子例程，而光标则不能。为了共享静态光标，必须在包说明或包体中把它定义为全局光标。因为使用全局变量通常不是一种很好的编码习惯，因此可以用ref光标来共享PL/SQL中的光标，无需混合使用全局变量。

九存储过程返回结果集

存储过程返回结果集的话要使用REF CURSUR 游标返回

先创建一个包：

CREATE OR REPLACE PACKAGE pkg_test
AS
      TYPE   myrctype   IS   REF   CURSOR;

      PROCEDURE   get   (p_rc   OUT   myrctype);
END   pkg_test;

再创建包体内容：

CREATE OR REPLACE PACKAGE   BODY   pkg_test
AS
      PROCEDURE   get   (p_rc   OUT   myrctype)   --建立存储过程get
      IS
            sqlstr       VARCHAR2   (500);
      BEGIN
           -- 进行所需的增删改查操作，最后通过游标返回结果集
                  OPEN   p_rc   FOR
                        select * from sari_j_caseinfo t;

      END   get;
END   pkg_test;

然后通过JAVA调用该存储过程：

public void testPro() {
		// TODO Auto-generated method stub
		 System.out.println("调用get存储过程！");
			
			
			Session s = dao.openSession();
		
			try {
		
				CallableStatement cs = s
							.connection()
							.prepareCall(
									"{call pkg_test.get(?)}");
					

	                cs.registerOutParameter(1, OracleTypes.CURSOR);
					cs.execute();
					ResultSet r = (ResultSet)cs.getObject(1);
					cs.close();
					
					List list = new ArrayList(); //取到的结果集转换成LIST
					while(r.next()){
						SariJCaseinfo sariJCaseinfo = new SariJCaseinfo(); 
						sariJCaseinfo.setCaseId(r.getString("case_id"));
						list.add(sariJCaseinfo);
					}
					
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					s.close();
					System.out.println("OK...");
				
				}
            
			
	}

ps:

1)identifier 'PKG_TEST.GET' must be declared 的错误可能是用一个用户在数据库中建立了一个存储过程，又用另一个用户去调用导致的。

org.apache.commons.dbcp.DelegatingCallableStatement with address: "oracle.jdbc.driver.OracleCallableStatement@1dc4ead" is closed.

错误是因为之前的 ResultSet r = (ResultSet)cs.getObject(1); 放在了cs.close(); 导致的。

十批量更新ID，ID号每一行+1

两种方法：

1）建立一个序列：

create sequence SEQ_ZSF_ID
minvalue 1
maxvalue 99999
start with 21
increment by 1
cache 20
order;

然后直接
update table set 字段 = SEQ_ZSF_ID.nextval

2）

create or replace procedure TTT is
v_str number;
begin
    v_str := 1;
    for z in (select * from test_zjj) loop
    
    update test_zjj set id = v_str where id = z.id;
    
    v_str:=v_str+1;
    end loop;

end TTT;

十一 orcale中取得唯一序列的方法是 select sys_guid() from dual

十二如果数据库配置的查询中是select * from A where a like '?__' 形式，而?可能被当成字符串来解析，这时可以这样写：select * from A where a like ?||'__' , 或者写个函数 select * from A where a like functionName(?)

十三触发器

create or replace trigger trigger_eruption_b_detect
  after insert or update on lab_eruption_b_detect 
  for each row
declare
  -- local variables here
  -- PRAGMA AUTONOMOUS_TRANSACTION;   --①
   V_MSG      VARCHAR2(200);
   V_CODE     VARCHAR2(20);  --记录发生错误的操作

begin       
      IF INSERTING THEN  --当执行的操作是插入
      V_CODE := 'INSERT';
insert into pathogeny_center(id_pathogeny_center, pathogeny_type, pathogeny_name, detect_way,
       detect_result, sociotomy_result, type_result, first_detect_date, detect_unit, sample_id, sample_type,
       sample_date, disease, patient_id, symptom, birthday, vis_date, disease_date, area, code_hospital,
       code_hospital_new, code_address, case_type, age, age_unit, sex, patient_job, is_outbreak, orgcode_add, 
       date_add, age_group)
(select lab_seq.nextval,'02' pathogeny_type ,'030201' pathogeny_name,'01' detect_way,d.i_tab_separate,
       '' sociotomy_result,'' type_result,d.first_detect_date,d.detect_unit,s.sample_id,s.sample_type,
       s.sample_date,s.disease,p.patient_id,p.syndrome,p.birthday,p.vis_date,p.disease_date,'' area,p.code_hospital,
       p.code_hospital_new,p.code_address,p.case_type,p.age,p.age_unit,p.sex,p.patient_job,'' is_outbreak,p.orgcode_add,
       p.date_add,'' age_group
from lab_eruption_b_detect d,lab_sampleinfo s,lab_patient p 
where d.id_sampleinfo = s.id_sampleinfo and s.id_patient = p.id_patient  and s.id_sampleinfo = :NEW.id_sampleinfo);
commit;    
      ELSIF UPDATING THEN  --当执行的操作是修改
      V_CODE := 'MODIFY';  
      update.....

      END IF;

       --记录错误日志
  EXCEPTION
  WHEN OTHERS THEN
    V_MSG  := SUBSTR(SQLERRM, 1, 200);
    V_CODE := V_CODE;
    INSERT INTO RPT_QUERY_ERR_LOG
      (OPDATE, FUNCNAME, ERRCODE, ERRMSG)
    VALUES
      (SYSDATE, 'trigger_eruption_b_detect', V_CODE, V_MSG);

end trigger_eruption_b_detect;

ps:

1. mysql只能对每一行触发一次，而oracle可以对每一行整个表触发一次。

2.mysql只能是表的行激活触发器，但oracle可以做到对列触发。

1：在写trigger的时候,经常会遇到这种情况
当在程序块中需要对trigger本表进行修改或查询的时候,系统会提示错误: ORA-04091: table is mutating, trigger/function may not see it，关于这个错误,其实是由于对本表的操作造成的.ORACLE DB里默认在写TRIGGER的时候把本表锁死,不允许对其进行操作,也就是说这个错误是不能通过系统的手段解决的,只能改用一些其它的SQL来绕开它.
刚接触TRIGGER的时候会经常犯这样的错误,其中有大部分是可以通过:new 来解决的。但是还会有一定要对本表进行修改或查询操作的情况,不能避免.这里的解决办法则是加上①语句。（如上面的SQL语句其实可以把trigger本表lab_eruption_b_detect表去掉，用：NEW的形式，:NEW表示在执行完操作后（插入，修改）后该条字段在表中的值，如:new.first_detect_date,相对的是:old,表示在修改（删除）前的值。

iteye_6967

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Orcale语法

一 Orcale的大字段用clob,图像用blob,clob字段在Hibernate的映射文件中用String就可以二 Orcale备份数据库表： create table test_bak as select * from test 三 Orcale配置客户端： D:\oracle\product\10.1.0\Db_1\NETWORK\ADMIN\tnsnam...
复制链接

扫一扫

专栏目录