小女巫 发表于 2015-1-16 22:43:30

MYSQL编程：ORACLE之经常使用FAQ V1.0 (1)

一些典型的RDBMS功能并不总是在DBaaS系统中可用。例如MySQL学习教程，WindowsAzureSQLDatabase(以前的SQLAzure)是微软的DBaaS产品，提供了一个类似于SQLServer的数据库平台。oracle第一部分、SQL&PL/SQL

怎样查询特别字符，如通配符%与_
select*fromtablewherenamelikeA\_%escape
怎样拔出单引号到数据库表中
能够用ASCII码处置，别的特别字符如&也一样，如
insertintotvalues(i||chr(39)||m);--chr(39)代表字符
大概用两个单引号暗示一个
orinsertintotvalues(Im);--两个能够暗示一个
如何设置事件分歧性
settransactionreadcommitted;默许语句级分歧性
settransactionserializable;
readonly;事件级分歧性
怎样使用游标更新数据
cursorc1is
select*fromtablename
wherenameisnullforupdate
……
updatetablenamesetcolumn=……
wherecurrentofc1;
如何自界说非常
pragma_exception_init(exception_name,error_number);
假如当即抛出非常
raise_application_error(error_number,error_msg,true|false);
个中number从-20000到-20999，毛病信息最年夜2048B
非常变量
SQLCODE毛病代码
SQLERRM毛病信息
十进制与十六进制的转换
8i以上版本：
to_char(100,XX)
to_number(4D,XX)
8i以下的进制之间的转换参考以下剧本
createorreplacefunctionto_base(p_decinnumber,p_baseinnumber)
returnvarchar2
is
l_strvarchar2(255)defaultNULL;
l_numnumberdefaultp_dec;
l_hexvarchar2(16)default0123456789ABCDEF;
begin
if(p_decisnullorp_baseisnull)then
returnnull;
endif;
if(trunc(p_dec)p_decORp_dec20;
怎样抽取反复纪录
select*fromtablet1wherewheret1.rowed!=
(selectmax(rowed)fromtablet2
wheret1.id=t2.idandt1.name=t2.name)
大概
selectcount(*),t.col_a,t.col_bfromtablet
groupbycol_a,col_b
havingcount(*)>1
假如想删除反复纪录，能够把第一个语句的select交换为delete
怎样设置自治事件
8i以上版本，不影响主事件
pragmaautonomous_transaction;
……
commit|rollback;
怎样在过程当中停息指准时间
DBMS_LOCK包的sleep历程
如：dbms_lock.sleep(5);暗示停息5秒。
怎样疾速盘算事件的工夫与日记量
能够接纳相似以下的剧本
DECLARE
start_timeNUMBER;
end_timeNUMBER;
start_redo_sizeNUMBER;
end_redo_sizeNUMBER;
BEGIN
start_time:=dbms_utility.get_time;
SELECTVALUEINTOstart_redo_sizeFROMv$mystatm,v$statnames
WHEREm.STATISTIC#=s.STATISTIC#
ANDs.NAME=redosize;
--transactionstart
INSERTINTOt1
SELECT*FROMAll_Objects;
--otherdmlstatement
COMMIT;
end_time:=dbms_utility.get_time;
SELECTVALUEINTOend_redo_sizeFROMv$mystatm,v$statnames
WHEREm.STATISTIC#=s.STATISTIC#
ANDs.NAME=redosize;
dbms_output.put_line(EscapeTime:||to_char(end_time-start_time)||centiseconds);
dbms_output.put_line(RedoSize:||to_char(end_redo_size-start_redo_size)||bytes);
END;
如何创立一时表
8i以上版本
createglobaltemporarytablename(columnlist)
oncommitpreserverows;--提交保存数据会话一时表
oncommitdeleterows;--提交删除数据事件一时表
一时表是相对会话的，其余会话看不到该会话的数据。
怎样在PL/SQL中实行DDL语句
1、8i以下版本dbms_sql包
2、8i以上版本还能够用
executeimmediatesql;
dbms_utility.exec_ddl_statement(sql);
怎样猎取IP地点
服务器(817以上)：utl_inaddr.get_host_address
客户端：sys_context(userenv,ip_address)
怎样加密存储历程
用wrap命令，如（假定你的存储历程保留为a.sql）
wrapiname=a.sql
PL/SQLWrapper:Release8.1.7.0.0-ProductiononTueNov2722:26:482001
Copyright(c)OracleCorporation1993,2000.AllRightsReserved.
Processinga.sqltoa.plb
提醒a.sql转换为a.plb，这就是加密了的剧本，实行a.plb便可天生加密了的存储历程
怎样在ORACLE中准时运转存储历程
能够使用dbms_job包来准时运转功课，如实行存储历程，一个复杂的例子，提交一个功课：
VARIABLEjobnonumber;
BEGIN
DBMS_JOB.SUBMIT(:jobno,ur_procedure;,SYSDATE,SYSDATE+1);
commit;
END;
以后，就能够用以下语句查询已提交的功课
select*fromuser_jobs;
怎样从数据库中取得毫秒
9i以上版本，有一个timestamp范例取得毫秒，如
SQL>selectto_char(systimestamp,yyyy-mm-ddhh24:mi:ssxff)time1,
to_char(current_timestamp)time2fromdual;
TIME1TIME2
---------------------------------------------------------------------------------------------
2003-10-2410:48:45.65600024-OCT-0310.48.45.656000AM+08:00
能够看到，毫秒在to_char中对应的是FF。
8i以上版本能够创立一个以下的java函数
SQL>createorreplaceandcompile
javasource
named"MyTimestamp"
as
importjava.lang.String;
importjava.sql.Timestamp;
publicclassMyTimestamp
{
publicstaticStringgetTimestamp()
{
return(newTimestamp(System.currentTimeMillis())).toString();
}
};
SQL>javacreated.
注：注重java的语法，注重巨细写
SQL>createorreplacefunctionmy_timestampreturnvarchar2
aslanguagejava
nameMyTimestamp.getTimestamp()returnjava.lang.String;
/
SQL>functioncreated.
SQL>selectmy_timestamp,to_char(sysdate,yyyy-mm-ddhh24:mi:ss)ORACLE_TIMEfromdual;
MY_TIMESTAMPORACLE_TIME
-------------------------------------------
2003-03-1719:15:59.6882003-03-1719:15:59
假如只想取得1/100秒(hsecs)，还能够使用dbms_utility.get_time
假如存在就更新，不存在就拔出能够用一个语句完成吗
9i已撑持了，是Merge，可是只撑持select子查询，
假如是单条数据纪录，能够写作select……fromdual的子查询。
语法为：
MERGEINTOtable
USINGdata_source
ON(condition)
WHENMATCHEDTHENupdate_clause
WHENNOTMATCHEDTHENinsert_clause;
如
MERGEINTOcoursec
USING(SELECTcourse_name,period,
course_hours
FROMcourse_updates)cu
ON(c.course_name=cu.course_name
ANDc.period=cu.period)
WHENMATCHEDTHEN
UPDATE
SETc.course_hours=cu.course_hours
WHENNOTMATCHEDTHEN
INSERT(c.course_name,c.period,
c.course_hours)
VALUES(cu.course_name,cu.period,
cu.course_hours);
怎样完成左联，右联与外联
在9i之前能够这么写:
左联：
selecta.id,a.name,b.addressfroma,b
wherea.id=b.id(+)
右联:
selecta.id,a.name,b.addressfroma,b
wherea.id(+)=b.id
外联
SELECTa.id,a.name,b.address
FROMa,b
WHEREa.id=b.id(+)
UNION
SELECTb.id,name,b.address
FROMb
WHERENOTEXISTS(
SELECT*FROMa
WHEREa.id=b.id);
在9i以上，已入手下手撑持SQL99尺度，以是，以上语句能够写成：
默许外部联合：
selecta.id,a.name,b.address,c.subject
from(ainnerjoinbona.id=b.id)
innerjoinconb.name=c.name
whereother_clause
左联
selecta.id,a.name,b.address
fromaleftouterjoinbona.id=b.id
whereother_clause
右联
selecta.id,a.name,b.address
fromarightouterjoinbona.id=b.id
whereother_clause
外联
selecta.id,a.name,b.address
fromafullouterjoinbona.id=b.id
whereother_clause
or
selecta.id,a.name,b.address
fromafullouterjoinbusing(id)
whereother_clause
怎样完成一笔记录依据前提多表拔出
9i以上能够经由过程Insertall语句完成，仅仅是一个语句，如：
INSERTALL
WHEN(id=1)THEN
INTOtable_1(id,name)
values(id,name)
WHEN(id=2)THEN
INTOtable_2(id,name)
values(id,name)
ELSE
INTOtable_other(id,name)
values(id,name)
SELECTid,name
FROMa;
假如没有前提的话，则完成每一个表的拔出，如
INSERTALL
INTOtable_1(id,name)
values(id,name)
INTOtable_2(id,name)
values(id,name)
INTOtable_other(id,name)
values(id,name)
SELECTid,name
FROMa;
怎样完成行列转换
1、流动列数的行列转换
如
studentsubjectgrade
---------------------------
student1语文80
student1数学70
student1英语60
student2语文90
student2数学80
student2英语100
……
转换为
语文数学英语
student1807060
student29080100
……
语句以下：
selectstudent,sum(decode(subject,语文,grade,null))"语文",
sum(decode(subject,数学,grade,null))"数学",
sum(decode(subject,英语,grade,null))"英语"
fromtable
groupbystudent
2、不定列行列转换
如
c1c2
--------------
1我
1是
1谁
2知
2道
3不
……
转换为
1我是谁
2晓得
3不
这一范例的转换必需借助于PL/SQL来完成，这里给一个例子
CREATEORREPLACEFUNCTIONget_c2(tmp_c1NUMBER)
RETURNVARCHAR2
IS
Col_c2VARCHAR2(4000);
BEGIN
FORcurIN(SELECTc2FROMtWHEREc1=tmp_c1)LOOP
Col_c2:=Col_c2||cur.c2;
ENDLOOP;
Col_c2:=rtrim(Col_c2,1);
RETURNCol_c2;
END;
/
SQL>selectdistinctc1,get_c2(c1)cc2fromtable;便可
怎样完成分组取前N笔记录
8i以上版本，使用剖析函数
如猎取每一个部门薪水前三名的员工或每一个班成就前三名的先生。
Select*from
(selectdepno,ename,sal,row_number()over(partitionbydepno
orderbysaldesc)rn
fromemp)
wherernhostlsntctlstart
大概unix/linux平台下
SQL>!
windows平台下
SQL>$
总结：HOST能够间接实行OS命令。
备注：cd命令没法准确实行。
怎样设置存储历程的挪用者权限
一般存储历程都是一切者权限，假如想设置挪用者权限，请参考以下语句
createorreplace
procedure……()
AUTHIDCURRENT_USER
As
begin
……
end;
怎样疾速取得用户下每一个表或表分区的纪录数
能够剖析该用户，然后查询user_tables字典，大概接纳以下剧本便可
SETSERVEROUTPUTONSIZE20000
DECLARE
miCountINTEGER;
BEGIN
FORc_tabIN(SELECTtable_nameFROMuser_tables)LOOP
EXECUTEIMMEDIATEselectcount(*)from"||c_tab.table_name||"intomiCount;
dbms_output.put_line(rpad(c_tab.table_name,30,.)||lpad(miCount,10,.));
--ifitispartitiontable
SELECTCOUNT(*)INTOmiCountFROMUser_Part_TablesWHEREtable_name=c_tab.table_name;
IFmiCount>0THEN
FORc_partIN(SELECTpartition_nameFROMuser_tab_partitionsWHEREtable_name=c_tab.table_name)LOOP
EXECUTEIMMEDIATEselectcount(*)from||c_tab.table_name||partition(||c_part.partition_name||)
INTOmiCount;
dbms_output.put_line(||rpad(c_part.partition_name,30,.)||lpad(miCount,10,.));
ENDLOOP;
ENDIF;
ENDLOOP;
END;
怎样在Oracle中发邮件
能够使用utl_smtp包发邮件，以下是一个发送复杂邮件的例子程序
/****************************************************************************
parameter:Rcpterinvarchar2吸收者邮箱
Mail_ContentinVarchar2邮件内容
desc:·发送邮件到指定邮箱
·只能指定一个邮箱，假如必要发送到多个邮箱，必要别的的帮助程序
****************************************************************************/
CREATEORREPLACEPROCEDUREsp_send_mail(rcpterINVARCHAR2,
mail_contentINVARCHAR2)
IS
connutl_smtp.connection;
--writetitle
PROCEDUREsend_header(NAMEINVARCHAR2,HEADERINVARCHAR2)AS
BEGIN
utl_smtp.write_data(conn,NAME||:||HEADER||utl_tcp.CRLF);
END;
BEGIN
--opneconnect
conn:=utl_smtp.open_connection(smtp.com);
utl_smtp.helo(conn,oracle);
utl_smtp.mail(conn,oracleinfo);
utl_smtp.rcpt(conn,Rcpter);
utl_smtp.open_data(conn);
--writetitle
send_header(From,OracleDatabase);
send_header(To,"Recipient");
send_header(Subject,DBInfo);
--writemailcontent
utl_smtp.write_data(conn,utl_tcp.crlf||mail_content);
--closeconnect
utl_smtp.close_data(conn);
utl_smtp.quit(conn);
EXCEPTION
WHENutl_smtp.transient_errorORutl_smtp.permanent_errorTHEN
BEGIN
utl_smtp.quit(conn);
EXCEPTION
WHENOTHERSTHEN
NULL;
END;
WHENOTHERSTHEN
NULL;
ENDsp_send_mail;
怎样在Oracle中写操纵体系文件，如写日记
能够使用utl_file包，可是，在此之前，要注重设置好Utl_file_dir初始化参数
/**************************************************************************
parameter:textContextinvarchar2日记内容
desc:·写日记,把内容记到服务器指定目次下
·必需设置Utl_file_dir初始化参数，并包管日记路径与Utl_file_dir路径分歧大概是个中一个
****************************************************************************/
CREATEORREPLACEPROCEDUREsp_Write_log(text_contextVARCHAR2)
IS
file_handleutl_file.file_type;
Write_contentVARCHAR2(1024);
Write_file_nameVARCHAR2(50);
BEGIN
--openfile
write_file_name:=db_alert.log;
file_handle:=utl_file.fopen(/u01/logs,write_file_name,a);
write_content:=to_char(SYSDATE,yyyy-mm-ddhh24:mi:ss)||||||text_context;
--writefile
IFutl_file.is_open(file_handle)THEN
utl_file.put_line(file_handle,write_content);
ENDIF;
--closefile
utl_file.fclose(file_handle);
EXCEPTION
WHENOTHERSTHEN
BEGIN
IFutl_file.is_open(file_handle)THEN
utl_file.fclose(file_handle);
ENDIF;
EXCEPTION
WHENOTHERSTHEN
NULL;
END;
ENDsp_Write_log;
事实上，在任何DBaaS中，解决方案提供商对如何管理物理资源都没有控制权，因此MySQL学习教程可能会发现由于DBaaS的局限性使得他们提供给客户的远远小于客户所期望的。

简单生活 发表于 2015-1-19 21:45:07

SQLServer的异构移植功能个人感觉最好了。（如果对比过SQLServer的链接服务器和Oracle的透明网关的朋友会发现SQLServer的sp_addlinkedserver(openquery)异构数据库系列比Oracle真是强太多了。）

透明 发表于 2015-1-26 23:47:25

总感觉自己还是不会SQL

灵魂腐蚀 发表于 2015-2-4 22:40:19

如安全管理、备份恢复、性能监控和调优等，SQL只要熟悉基本操作就可以，只要程序设计部分只要稍加了解即可（如存储过程、触发器等）。

若相依 发表于 2015-3-1 16:34:07

可以动态传入参数，省却了动态SQL的拼写。

深爱那片海 发表于 2015-3-10 21:04:47

作了些试验，发现使用CLR的存储过程或函数在达到一定的阀值的时候，系统性能会呈指数级下滑！这是非常危险的！只使用几个可能没有问题，当一旦大规模使用会造成严重的系统性能问题！

乐观 发表于 2015-3-17 10:09:26

数据库物理框架没有变动undo和redo都放在数据库得transaction中，个人感觉是个败笔。如果说我们在设计数据库的时候考虑分多个数据库，可能能在一定程度上避免I/O效率问题。

柔情似水 发表于 2015-3-17 10:09:27

学习SQL语言的话如果要学会去做网站就不是很难!但是要做数据库管理的话就有难度了!

再见西城 发表于 2015-3-24 06:34:58

连做梦都在想页面结构是怎么样的，绝非虚言
页: [1]
查看完整版本: MYSQL编程：ORACLE之经常使用FAQ V1.0 (1)