一.sql语句
二.pl/sql 编程方面
三.系统函数和系统包使用方面
一.sql语句 1.增加主键
alter table TABLE_NAME add constraint KEY_NAME primary key (TABLE_COLUMN);
指定表空间
alter table TABLE_NAME add constraint KEY_NAME primary key (TABLE_COLUMN) using index tablespace TABLE_SPACE_NAME;
2.增加外键
alter table TABLE_NAME add constraint FK_NAME foreign key (TABLE_COLUMN) references KEY_TABLE_NAME;
3.使主键或外键失效、生效
alter table TABLE_NAME disable(enable) constraint KEY_NAME;
4、查看各种约束
select constraint_name,table_name,constraint_type,status from user_constraints;
select constraint_name, constraint_type,search_condition, r_constraint_name from user_constraints where table_name = upper('&table_name') 二.pl/sql 编程方面
--====================================================== 1.自治事务:8i以上版本，不影响主事务。
在存储过程的is\as
后面声明PRAGMA AUTONOMOUS_TRANSACTION;
自治事务防止嵌套提交，使事务在自己的事务区内提交或回滚不会影响其他的事务。 2、包
包说明(package specification)，包头,存放关于包的内容的信息，定义包的用户可见的过程、
函数，数据类型和变量
create or replace package tt_aa as
v1 varchar2(10);
v2 varchar2(10);
v3 number;
v4 boolean;
procedure proc1(x number);
procedure proc2(y varchar2);
procedure proc3(z number);
function my_add(x number,y number) return number;
end;
包主体（package body)是可选的
create or replace package body tt_aa as
procedure proc1(x number) as
begin
v1:=to_char(x);
end;
procedure proc2(y varchar2) as
begin
v2:=y;
end;
procedure proc3(z number) as
begin
v1:=z;
end;
procedure proc4(x number,y number) return number as
begin
return x+y;
end;
end;
调用
begin
tt_aa.proc1(6);
dbms_output.put_line(to_char(tt_aa.my_add(1,3));
end; 3、动态sql(使用dbms_sql)
create or replace procedure my_execute(sql_string in varchar2) as
v_cursor number;
v_numrows interger;
begin
v_cursor:=dbms_sql.open_cursor;
dbms_sql.parse(v_cursor,sql_string,dbms_sql.v7);
v_numrows:=dbms_sql.execute(v_cursor);
dbms_sql.close_cursor(v_cursor);
end;
则可以
sql>exec my_execute('select * from tab');
sql>exec my_execute('insert into test value'||'('||'''ddd'''||')');
sql>exec my_execute('commit');
对于查询方面的可以如下方式：
比如想用游标查询一个表，但是这个表是分月的，每个月可能表名都会改变。
create or replace procedure proc_test as
v_curid integer;
v_result integer;
v_strSql varchar2(255);
v_userid okcai.userid%type;
v_username okcai.username%type;
begin
v_strSql := 'select * from okcai_'||to_char(sysdate,'yyyymm');
v_curid := dbms_sql.open_cursor;
dbms_sql.parse(v_curid,v_strSql,dbms_sql.v7);
dbms_sql.define_column(v_curid,1,v_userid);
dbms_sql.define_column(v_curid,2,v_username,10); --必须指定大小
v_result := dbms_sql.execute(v_curid);
loop
if dbms_sql.fetch_rows(v_curid) = 0 then
exit; --没有了 ，退出循环
end if;
dbms_sql.column_value(v_curid,1,v_userid);
dbms_sql.column_value(v_curid,2,v_username);
dbms_output.put_line(v_userid);
dbms_output.put_line(v_username);
end loop;
dbms_sql.close(v_curid);
end; 4、用EXECUTE IMMEDIATE
<1>. 在PL/SQL运行DDL语句
begin
execute immediate 'set role all';
end;
<2>. 给动态语句传值(USING 子句)
declare
l_depnam varchar2(20) := 'testing';
l_loc varchar2(10) := 'Dubai';
begin
execute immediate 'insert into dept values (:1, :2, :3)'
using 50, l_depnam, l_loc;
commit;
end;
<3>. 从动态语句检索值(INTO子句)
declare
l_cnt varchar2(20);
begin
execute immediate 'select count(1) from emp'
into l_cnt;
dbms_output.put_line(l_cnt);
end;
<4>. 动态调用例程.例程中用到的绑定变量参数必须指定参数类型.黓认为IN类型,其它类型必须显式指定
declare
l_routin varchar2(100) := 'gen2161.get_rowcnt';
l_tblnam varchar2(20) := 'emp';
l_cnt number;
l_status varchar2(200);
begin
execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
using in l_tblnam, out l_cnt, in out l_status; if l_status != 'OK' then
dbms_output.put_line('error');
end if;
end;
<5>. 将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量
declare
type empdtlrec is record (empno number(4),
ename varchar2(20),
deptno number(2));
empdtl empdtlrec;
begin
execute immediate 'select empno, ename, deptno ' ||
'from emp where empno = 7934'
into empdtl;
end;
<6>. 传递并检索值.INTO子句用在USING子句前
declare
l_dept pls_integer := 20;
l_nam varchar2(20);
l_loc varchar2(20);
begin
execute immediate 'select dname, loc from dept where deptno = :1'
into l_nam, l_loc
using l_dept ;
end;
<7>. 多行查询选项.对此选项用insert语句填充临时表，用临时表进行进一步的处理,也可以用REF cursors纠正此缺憾.
declare
l_sal pls_integer := 2000;
begin
execute immediate 'insert into temp(empno, ename) ' ||
' select empno, ename from emp ' ||
' where sal > :1'
using l_sal;
commit;
end;
<8>. 完成update的returning功能
update可以用returning返回修改以后的值。比如:
UPDATE employees
SET job_id =’SA_MAN’, salary = salary + 1000, department_id = 140
WHERE last_name = ’Jones’
RETURNING salary*0.25, last_name, department_id
INTO :bnd1, :bnd2, :bnd3;
用execute immediate来完成的时候，可以用
declare
l_sal pls_integer;
begin
execute immediate 'update employees SET salary = salary + 1000 where last_name=''okcai'' RETURNING INTO :1'
returning into v_sql;
commit;
end;
5、用ref cursor来完成动态游标的功能
declare
type ct is ref cursor;
cc ct;
v_notify acc_woff_notify%rowtype;
begin
open cc for 'select * from acc_woff_notify';
loop
fetch cc into v_notify;
exit when cc%notfound;
dbms_output.put_line(v_notify.done_code);
end loop;
close cc;
end;
6、重新编译
对失效的过程
sql>exec dbms_utility.compile_schema(schema);
如：
sql>exec dbms_utility.compile_schema(scott); 7.存储过程使用table类型
<1>.字符串数组
declare
type regionType is table of varchar2(3) index by binary_integer;
v_listRegion regionType;
i number(2):=0;
begin
v_listRegion(1):='571';
v_listRegion(2):='572';
v_listRegion(3):='573';
v_listRegion(4):='574';
v_listRegion(5):='575';
v_listRegion(6):='576';
v_listRegion(7):=null;
i := 1;
while i<= v_listRegion.last loop
dbms_output.put_line( v_listRegion(i) );
i := v_listRegion.next(i);
end loop;
end; <2>.rowtype数组
declare
type CmUserType is table of cm_user%rowtype index by binary_integer;
v_listUser CmUserType;
i number(5):=0;
r_user cm_user%rowtype;
begin
i := 1;
for r_user in (select * from cm_user where rownum<=5) loop
v_listUser(i):= r_user;
i := i + 1;
end loop;
i := 1;
while i<= v_listUser.last loop
dbms_output.put_line( v_listUser(i).bill_id );
i := v_listUser.next(i);
end loop;
end; <3>. record数组
declare
type recCmUserType is record (bill_id cm_user.bill_id%type,cust_name varchar2(25));
type CmUserType is table of recCmUserType index by binary_integer;
v_listUser CmUserType;
i number(5):=0;
r_user cm_user%rowtype;
begin
i := 1;
for r_user in (select * from cm_user where rownum<=5) loop
v_listUser(i).bill_id:= r_user.bill_id;
v_listUser(i).cust_name:= '客户'||i;
i := i + 1;
end loop;
i := 1;
while i<= v_listUser.last loop
dbms_output.put_line( v_listUser(i).bill_id );
dbms_output.put_line( v_listUser(i).cust_name );
i := v_listUser.next(i);
end loop;
end; 8、存储函数和过程
查看函数和过程的状态
SQL>select object_name,status from user_objects where object_type='FUNCTION';
SQL>select object_name,status from user_objects where object_type='PROCEDURE';
查看函数和过程的源代码
SQL>set long 1000
SQL>set pagesize 0
SQL>set trimspool on
SQL>select text from all_source where owner=user and name=upper('&plsql_name');
9、触发器
查看触发器 set long 50000;
set heading off;
set pagesize 2000; select
'create or replace trigger "' ||
trigger_name || '"' || chr(10)||
decode( substr( trigger_type, 1, 1 ),
'A', 'AFTER', 'B', 'BEFORE', 'I', 'INSTEAD OF' ) ||
chr(10) ||
triggering_event || chr(10) ||
'ON "' || table_owner || '"."' ||
table_name || '"' || chr(10) ||
decode( instr( trigger_type, 'EACH ROW' ), 0, null,
'FOR EACH ROW' ) || chr(10) ,
trigger_body
from user_triggers; 10. 加密ORACLE的存储过程
用wrap命令,如:
下列存储过程内容放在AA.SQL文件中
create or replace procedure testCCB(i in number) as
begin
dbms_output.put_line('输入参数是'||to_char(i));
end; SQL>wrap iname=a.sql;
PL/SQL Wrapper: Release 8.1.7.0.0 - Production on Tue Nov 27 22:26:48 2001
Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved.
Processing a.sql to a.plb
提示a.sql转换为a.plb，这就是加密了的脚本，执行a.plb即可生成加密了的存储过程
运行a.plb
SQL> @a.plb ; 11.怎么样利用游标更新数据
cursor c1 is
select * from tablename
where name is null for update [of column]
...
update tablename set column = ...
where current of c1; 但是如果这种方式打开以后做了commit，则下次fetch会报ora-01002错误 12.怎样自定义异常
pragma_exception_init(exception_name,error_number);
如果立即抛出异常
raise_application_error(error_number,error_msg,true|false);
其中number从-20000到-20999，错误信息最大2048B
异常变量
SQLCODE 错误代码
SQLERRM 错误信息 13.在pl/sql中执行DDL语句
<1>、8i以下版本dbms_sql包
<2>、8i以上版本还可以用
execute immediate sql;
dbms_utility.exec_ddl_statement('sql'); 14.用java写存储过程包
<1>
create or replace and compile
java source
named "CHelloWorld" as
public class HelloWorld
{
public static String print()
{
return System.out.println("Hello,World");
}
};
/
<2>
create or replace function my_helloWorld return varchar2
as language java
name 'HelloWorld.print() return java.lang.String';
/
<3>
select my_helloWorld from dual;
三.系统函数和系统包使用方面
1.十进制和十六进制转换
(oracle 8i以后)
select to_char(125,'XXXXX') from dual
-----------
7D
select to_char(125,'xxxxx') from dual
-----------
7d select to_number('7D','XXXXX') from dual
-----------
125 2. ORACLE产生随机函数
DBMS_RANDOM.RANDOM 3、调度程序 DBMS_JOB
broken 中止一个任务调度
change 修改任务的属性
internal 改变间隔
submit 任务发送到任务队列中去
next_date 改变任务的运行时间
remove 删除一个任务
run 立即执行一个任务
submit 提交一个任务
user_export 任务说明
what 改变任务运行的程序
查询
select * from user_job;
建立一存储过程
create or replace procedure log_proc as
begin
insert into test(aa) values(sysdate);
commit;
end;
提交一个任务
declare
job_num number;
begin
dbms_job.submit(job_num,'log_proc;',sysdate,sysdate+5/(24*60*60),false);
dbms_output.put_line('Job numer='||to_char(job_num));
end;
1> 上面程序从当前开始，间隔5秒执行一次。
2> 如果每天几点执行，可以写为(比如从2004-09-13开始执行，每天7点执行)
next_date => to_date('13-09-2004 07:00:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'trunc(sysdate)+(7+24)/24')
3> 如果是每个月几号开始执行。比如每月2号21点执行。
add_months(trunc(sysdate,'MONTH'),1) + 2-1 + 21/24
移走任务
begin
dbms_job.remove(1);
end;
中止任务
begin
dbms_job.broken(1,true);
end;
查询正在执行的job
select * from dba_jobs_running
如果运行比较慢，加
select /*+ rule */* from dba_jobs_running
4.UTL_FILE包
在PL/SQL 3.3以上的版本中，UTL_FILE包允许用户通过PL/SQL读写操作系统文件。如下： DECALRE
FILE_HANDLE UTL_FILE.FILE_TYPE;
BEGIN
FILE_HANDLE:=UTL_FILE.FOPEN('C:\','TEST.TXT','A');
UTL_FILE.PUT_LINE(FILE_HANDLE,'HELLO,IT iS A TEST TXT FILE');
UTL_FILE.FCLOSE(FILE_HANDLE);
END;
比如：怎么样在Oracle中写操作系统文件，如写日志
可以利用utl_file包，但是，在此之前，要注意设置好Utl_file_dir初始化参数
/**************************************************************************
parameter:textContext in varchar2 日志内容
desc: ·写日志,把内容记到服务器指定目录下
·必须配置Utl_file_dir初始化参数，并保证日志路径与Utl_file_dir路径一致或者是其中一个
****************************************************************************/
CREATE OR REPLACE PROCEDURE sp_Write_log(text_context VARCHAR2)
IS
file_handle utl_file.file_type;
Write_content VARCHAR2(1024);
Write_file_name VARCHAR2(50);
BEGIN
--open file
write_file_name := 'db_alert.log';
file_handle := utl_file.fopen('/u01/logs',write_file_name,'a');
write_content := to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'||'||text_context;
--write file
IF utl_file.is_open(file_handle) THEN
utl_file.put_line(file_handle,write_content);
END IF;
--close file
utl_file.fclose(file_handle);
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF utl_file.is_open(file_handle) THEN
utl_file.fclose(file_handle);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END sp_Write_log;
5.SYS_CONTEXT的详细用法
select
SYS_CONTEXT('USERENV','TERMINAL') terminal,
SYS_CONTEXT('USERENV','LANGUAGE') language,
SYS_CONTEXT('USERENV','SESSIONID') sessionid,
SYS_CONTEXT('USERENV','INSTANCE') instance,
SYS_CONTEXT('USERENV','ENTRYID') entryid,
SYS_CONTEXT('USERENV','ISDBA') isdba,
SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
SYS_CONTEXT('USERENV','NLS_DATE_formAT') nls_date_format,
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
SYS_CONTEXT('USERENV','SESSION_USER') session_user,
SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
SYS_CONTEXT('USERENV','DB_NAME') db_name,
SYS_CONTEXT('USERENV','HOST') host,
SYS_CONTEXT('USERENV','OS_USER') os_user,
SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type,
SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data
from dual 6.怎么样在过程中暂停指定时间
DBMS_LOCK包的sleep过程
如：dbms_lock.sleep(5);表示暂停5秒。
7.怎么在Oracle中发邮件
可以利用utl_smtp包发邮件，以下是一个发送简单邮件的例子程序
/****************************************************************************
parameter: Rcpter in varchar2 接收者邮箱
Mail_Content in Varchar2 邮件内容
desc: ·发送邮件到指定邮箱
·只能指定一个邮箱，如果需要发送到多个邮箱，需要另外的辅助程序
****************************************************************************/
CREATE OR REPLACE PROCEDURE sp_send_mail( rcpter IN VARCHAR2,
mail_content IN VARCHAR2)
IS
conn utl_smtp.connection;
--write title
PROCEDURE send_header(NAME IN VARCHAR2, HEADER IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(conn, NAME||': '|| HEADER||utl_tcp.CRLF);
END; BEGIN
--opne connect
conn := utl_smtp.open_connection('smtp.com');
utl_smtp.helo(conn, 'oracle');
utl_smtp.mail(conn, 'oracle info');
utl_smtp.rcpt(conn, Rcpter);
utl_smtp.open_data(conn);
--write title
send_header('From', 'Oracle Database');
send_header('To', '"Recipient" ');
send_header('Subject', 'DB Info');
--write mail content
utl_smtp.write_data(conn, utl_tcp.crlf || mail_content);
--close connect
utl_smtp.close_data(conn);
utl_smtp.quit(conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
utl_smtp.quit(conn);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
WHEN OTHERS THEN
NULL;
END sp_send_mail; 8.怎么样获取对象的DDL语句
第三方工具就不说了主要说一下9i以上版本的dbms_metadata
<1>获得单个对象的DDL语句
set heading off
set echo off
set feedback off
set pages off
set long 90000
select dbms_metadata.get_ddl('TABLE','TABLE_NAME','SCAME') from dual;
比如
select dbms_metadata.get_ddl('TABLE','CM_USER','AICBS') from dual;
<2>.如果获取整个用户的脚本，可以用如下语句
select dbms_metadata.get_ddl('TABLE',u.table_name) from user_tables u;
当然，如果是索引，则需要修改相关table到index
<3>.还有
dbms_metadata.get_xml() select c.constraint_name,c.constraint_type,cc.column_name
from user_constraints c,user_cons_columns cc
where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')
and c.owner = cc.owner and c.constraint_name = cc.constraint_name
order by cc.position; 5、删除主键或外键
alter table TABLE_NAME drop constraint KEY_NAME;
6、建外键
单字段时：create table 表名 (col1 char(8),
cno char(4) REFERENCE course);
多个字段时,在最后加上 Foreign Key (字段名) REFERENCE 表名(字段)
连带删除选项 (on delete cascade
当指定时，如果父表中的记录被删除，则依赖于父表的记录也被删除
REFERENCE 表名（） on delete cascade;
7、删除带约束的表
Drop table 表名 cascade constraints; 8：索引管理
<1>.creating function-based indexes
sql> create index summit.item_quantity on summit.item(quantity-quantity_shipped); <2>.create a B-tree index
sql> create [unique] index index_name on table_name(column,.. asc/desc) tablespace
sql> tablespace_name [pctfree integer] [initrans integer] [maxtrans integer]
sql> [logging | nologging] [nosort] storage(initial 200k next 200k pctincrease 0
sql> maxextents 50); <3>.pctfree(index)=(maximum number of rows-initial number of rows)*100/maximum number of rows <4>.creating reverse key indexes
sql> create unique index xay_id on xay(a) reverse pctfree 30 storage(initial 200k
sql> next 200k pctincrease 0 maxextents 50) tablespace indx; <5>.create bitmap index
sql> create bitmap index xay_id on xay(a) pctfree 30 storage( initial 200k next 200k
sql> pctincrease 0 maxextents 50) tablespace indx; <6>.change storage parameter of index
sql> alter index xay_id storage (next 400k maxextents 100); 7.allocating index space
sql> alter index xay_id allocate extent(size 200k datafile 'c:/oracle/index.dbf'); <8>.alter index xay_id deallocate unused; <9>、查看索引
SQL>select index_name,index_type,table_name from user_indexes order by table_name;
<10>、查看索引被索引的字段
SQL>select * from user_ind_columns where index_name=upper('&index_name'); 11、创建序列
select * from user_sequences;
create sequence SEQ_NAME start with 1000
maxvalue 1000 increment by 1;
alter sequence SEQ_NAME minvalue 50 maxvalue 100;
12、删除重复行
update a set aa=null where aa is not null;
delete from a where rowid!=
(select max(rowid) from a b where a.aa=b.aa);
13、删除同其他表相同的行
delete from a where exits
(select 'X' from b where b.no=a.no);
或
delete from a where no in (select no from b);
14、查询从多少行到多少行的记录(可以用在web开发中的分页显示) select * from ( select rownum row_id,b.* from (select a.* from sys_oper a) b )
where row_id between 15 and 20 15、对公共授予访问权
grant select on 表名 to public;
create public synonym 同义词名 for 表名;
16、填加注释
comment on table 表名 is '注释';
comment on column 表名.列名 is '注释';
17、分布式数据库,创建数据库链路
create [public] database link LINKNAME
[connect to USERNAME identified by PASSWORD]
[using 'CONNECT_STRING']
可以在服务器端，也可以在客户端建立，但必须注意，两台服务器之间
数据库必须可以互访，必须各有各自的别名数据库
18、查看数据库链路
select * from all_db_links;
select * from user_db_links;
查询 select * from TABLENAME@DBLNKNAME;
创建远程数据库同义词
create synonym for TABLENAME@DBLNKNAME;
操纵远程数据库记录
insert into TABLENAME@DBLNKNAME (a,b) values (va,vb);
update TABLENAME@DBLNKNAME set a='this';
delete from TABLENAME@DBLNKNAME;
怎样执行远程的内嵌过程
begin
otherdbpro@to_html(参数);
end; 19、数据库链路用户密码有特殊字符的时候，可以用双引号把密码引起来
create public database link dblink1 connect to db1 identified by "123*456" using 'db11' 20.oracle8中扩充了group by rollup和cube的操作。有时候省了你好多功夫的。
<1>下面的语句可以进行总计
select region_code,count(*) from aicbs.acc_woff_notify
group by rollup(region_code);
<2> 对第1个字段小计，最后合计
select region_code,write_status,count(*) from aicbs.acc_woff_notify
group by rollup(region_code,write_status);
----------------------
570 0 3
570 1 2
570 5 --此处小计了570的记录
571 0 10
571 1 2
571 12 --此处小计了571的记录
.....
100 --此处有总计
<3> 复合rollup表达式,只做总计
select region_code,write_status,count(*) from aicbs.acc_woff_notify
group by rollup(region_code,write_status);
<4> 对第1个字段小计，再对第2个字段小计，最后合计
select region_code,write_status,count(*) from aicbs.acc_woff_notify
group by cube(region_code,write_status);
----------------------
100 --此处有总计
0 60 --对write_status＝0的小计
1 39 --对write_status＝1的小计
3 1 --对write_status＝3的小计
570 5 --此处小计了570的记录
570 0 3
570 1 2
571 12 --此处小计了571的记录
571 0 10
571 1 2
....
<3> 复合cube表达式,只做总计
select region_code,write_status,count(*) from aicbs.acc_woff_notify
group by cube(region_code,write_status);
<4>下面的语句可以按照rollup不同的字段进行小计
select region_code,write_status,count(*) from aicbs.acc_woff_notify
group by region_code,rollup(write_status);
21.查询view的创建语句
sql>set long 1000
sql>select * from user_views where view_name='MY_VIEW_NAME';
or
sql>select * from all_views where view_name='MY_VIEW_NAME'; 22、去除数据库中特殊字符
<1>.字符串字段中含有"'",如果用来组合sql语句，会造成语句不准确。
比如：replace(f1,'''','')
<2>.字符串字段中含有"\t \n",如果用来在c或者c＋＋程序中输出到文件，格式无法保证。
比如：replace(f2,'\t','')
<3>.清除换行和回车
比如: replace(f2,chr(13)||chr(10),'')
23、如何在字符串里加回车或者tab键
在sqlplus中执行
sql>select 'UserId=1233111'||chr(10)||'AccId=13431'||chr(9)||'AccId2=11111' from dual; 24、树形查询
create table zj(
bm number(8),
bmmc varchar2(20),
sjbm number(8)
) insert into zj values(1,'aaa',0)
insert into zj values(11,'aaa1',1)
insert into zj values(12,'aaa2',1)
insert into zj values(111,'aaa11',11)
insert into zj values(112,'aaa12',11)
insert into zj values(113,'aaa13',11)
insert into zj values(121,'aaa21',12)
insert into zj values(122,'aaa22',12)
insert into zj values(123,'aaa23',12)
--
select bm,bmmc,sjbm,level
from zj
start with sjbm=0
connect by prior bm = sjbm
或者 select bm,bmmc,sjbm,level
from zj
start with sjbm=0
connect by sjbm = prior bm
25、快照
create snapshot SNAPSHOT_NAME
[storage (storage parameter)]
[tablespace TABLESPACE_NAME]
[refresh [fast\complete\force]
[start with START_DATE next NEXT_DATE]
as QUERY;
create snapshot snapshot_to_study as select * from TABLE_NAME@to_study;
创建角色
create role aa identified by aaa;
授权 grant create snapshot,alter snapshot to aaa;
grant aaa to emp;
create snapshot SNAPSHOT_TO_HTML refresh complete start with sysdate next
sysdate+5/(24*60*60) as select * from a@to_html;
删除 drop snapshot snap_to_html
手工刷新快照,(调用DBMS_SNAPSHOT包中的refresh过程)DBMS_SNAPSHOT.refresh(snapshot_name,refresh_type);
begin
DBMS_SNAPSHOT.REFRESH('snap_to_html','c');
end;
对所有快照进行刷新
begin
DBMS_SNAPSHOT.REFRESH_ALL;
end;
怎样执行远程的内嵌过程
begin
otherdbpro@to_html(参数);
end;
26、用户管理
create a user: database authentication
sql> create user juncky identified by oracle default tablespace users
sql> temporary tablespace temp quota 10m on data password expire
sql> [account lock|unlock] [profile profilename|default];
<1>.查看当前用户的缺省表空间
SQL>select username,default_tablespace from user_users;
<2>生成用户时指定缺省表空间
create user 用户名 identified by 口令 default tablespace 表空间名;
<3>重新指定用户的缺省表空间
alter user 用户名 default tablespace 表空间名
<4>查看当前用户的角色
SQL>select * from user_role_privs;
<5>查看当前用户的系统权限和表级权限
SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;
<6>查看用户下所有的表
SQL>select * from user_tables;
<7> alter user语句的quota子句限制用户的磁盘空间
如:alter user jf quota 10M on system;
27、查看放在ORACLE的内存区里的表
SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;
28、约束条件
create table employee
(empno number(10) primary key,
name varchar2(40) not null,
deptno number(2) default 10,
salary number(7,2) check salary<10000,
birth_date date,
soc_see_num char(9) unique,
foreign key(deptno) references dept.deptno)
tablespace users;
关键字(primary key)必须是非空,表中记录的唯一性
not null 非空约束
default 缺省值约束
check 检查约束,使列的值符合一定的标准范围
unqiue 唯一性约束
foreign key 外部键约束 29、查看创建视图的select语句
SQL>set view_name,text_length from user_views;
SQL>set long 2000; 说明：可以根据视图的text_length值设定set long 的大小
SQL>select text from user_views where view_name=upper('&view_name'); 30、查看同义词的名称
SQL>select * from user_synonyms; 31、用Sql语句实现查找一列中第N大值
select * from
(select t.*,dense_rank() over (order by sal) rank from employee)
where rank = N; 32 虚拟自段
<1>. CURRVAL 和 nextval
为表创建序列
CREATE SEQUENCE EMPSEQ ... ;
SELECT empseq.currval FROM DUAL ;
自动插入序列的数值
INSERT INTO emp
VALUES (empseq.nextval, 'LEWIS', 'CLERK',
7902, SYSDATE, 1200, NULL, 20) ; <2>. ROWNUM
按设定排序的行的序号
SELECT * FROM emp WHERE ROWNUM < 10 ; <3>. ROWID
返回行的物理地址
SELECT ROWID, ename FROM emp WHERE deptno = 20 ; 33、对CLOB字段进行全文检索
SELECT * FROM A WHERE dbms_lob.instr(a.a,'K',1,1)>0; 34. 特殊字符的插入，比如"&"
insert into a values (translate ('att','at{}','at')); 35.表管理
<1>.create a table
sql> create table table_name (column datatype,column datatype]....)
sql> tablespace tablespace_name [pctfree integer] [pctused integer]
sql> [initrans integer] [maxtrans integer]
sql> storage(initial 200k next 200k pctincrease 0 maxextents 50)
sql> [logging|nologging] [cache|nocache] <2>.copy an existing table
sql> create table table_name [logging|nologging] as subquery
<3> create table ... as 方式建表的时候，指定表参数
create table a
storage(
initial 1M /*第一次创建时分配空间*/
next 1M /*第一次分配的存储空间用完时在分配*/
)
as select * from b;
<4>.创建临时表
sql> create global temporary table xay_temp as select * from xay;
on commit preserve rows/on commit delete rows
在Oracle中，可以创建以下两种临时表:
a 会话特有的临时表:
create global temporary table () on commit preserve rows;
会话指定，当中断会话时ORACLE将截断表 b 事务特有的临时表:
create global temporary table () on commit delete rows;
事务指定,每次提交后ORACLE将截断表（删除全部行）
c 说明
临时表只在当前连接内有效
临时表不建立索引,所以如果数据量比较大或进行多次查询时,不推荐使用
数据处理比较复杂的时候时表快,反之视图快点
在仅仅查询数据的时候建议用游标: open cursor for 'sql clause';
<5>
pctfree = (average row size - initial row size) *100 /average row size
pctused = 100-pctfree- (average row size*100/available data space)
<6>.change storage and block utilization parameter
sql> alter table table_name pctfree=30 pctused=50 storage(next 500k
sql> minextents 2 maxextents 100);
<7>.manually allocating extents
sql> alter table table_name allocate extent(size 500k datafile 'c:/oracle/data.dbf');
<8>.move tablespace
sql> alter table employee move tablespace users;
<9>.deallocate of unused space
sql> alter table table_name deallocate unused [keep integer]
<10>.drop a column
sql> alter table table_name drop column comments cascade constraints checkpoint 1000;
alter table table_name drop columns continue;
<11>.mark a column as unused
sql> alter table table_name set unused column comments cascade constraints;
alter table table_name drop unused columns checkpoint 1000;
alter table orders drop columns continue checkpoint 1000
data_dictionary : dba_unused_col_tabs 37. 中文是如何排序的？ Oracle9i之前，中文是按照二进制编码进行排序的。
在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值
SCHINESE_RADICAL_M 按照部首（第一顺序）、笔划（第二顺序）排序
SCHINESE_STROKE_M 按照笔划（第一顺序）、部首（第二顺序）排序
SCHINESE_PINYIN_M 按照拼音排序 38. 数据表中的字段最大数:
表或视图中的最大列数为 1000
39. oracle中的裸设备:
裸设备就是绕过文件系统直接访问的储存空间
40. 在Oracle服务器上通过SQLPLUS查看本机IP地址 ?
select sys_context('userenv','ip_address') from dual;
如果是登陆本机数据库，只能返回127.0.0.1 41. 在ORACLE中取毫秒?
9i之前不支持,9i开始有timestamp.
9i可以用select systimestamp from dual; 42. 将N秒转换为时分秒格式？
set serverout on
declare
N number := 1000000;
ret varchar2(100);
begin
ret := trunc(n/3600) || '小时' || to_char(to_date(mod(n,3600),'sssss'),'fmmi"分 "ss"秒"') ;
dbms_output.put_line(ret);
end; 43、在某个用户下找所有的索引
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
44. not in的替代。
一般not in的效率比较低。特别是数据量大的时候，几乎不能执行。
用下面几种方式可以替换写法
比如要查询在fee_rev_info表中已经销户的用户（不在cm_user中的）(不过下面的例子不是很好，因为bill_id是cm_user的唯一索引）
select * from fee_rev_info where bill_id not in (select bill_id from cm_user)
<1> 用not exists
select * from fee_rev_info a where not exists (select 'p' from cm_user b where b.bill_id = a.bill_id)
<2> 用外连接(+)
select a.* from fee_rev_info a,cm_user b
where a.bill_id = b.bill_id (+)
and b.bill_id is null
<3> 用hash_aj
select /*+HASH_AJ*/* from fee_rev_info where bill_id not in (select bill_id from cm_user)
45.怎么样查询特殊字符，如通配符%与_
假如数据库中有表 STATIONTYPE,STATION_571 STATION_572 ...
select * from tab where tname like 'STATION_%'
会显示 STATIONTYPE,STATION_571 ... 可以用下面的语句
select * from tab where tname like 'STATION\_%' escape'\' 46.如果存在就更新，不存在就插入可以用一个语句实现吗
9i已经支持了，是Merge，但是只支持select子查询，
如果是单条数据记录，可以写作select .... from dual的子查询。
语法为：
MERGE INTO table
USING data_source
ON (condition)
WHEN MATCHED THEN update_clause
WHEN NOT MATCHED THEN insert_clause; 如
MERGE INTO cm_user_credit
USING (select * from dual) ON (user_id =1302514690 )
when MATCHED then update set credit_value = 1000
when NOT MATCHED then insert (user_id,acc_id,bill_id,plan_id,region_code,credit_value) values(1302514690,1305032158,'13857141218',10070247,'571',1000); 47.怎么实现一条记录根据条件多表插入
9i以上可以通过Insert all语句完成，仅仅是一个语句，如：
INSERT ALL
WHEN (id=1) THEN
INTO table_1 (id, name)
values(id,name)
WHEN (id=2) THEN
INTO table_2 (id, name)
values(id,name)
ELSE
INTO table_other (id, name)
values(id, name)
SELECT id,name
FROM a; 如果没有条件的话，则完成每个表的插入，如
INSERT ALL
INTO table_1 (id, name)
values(id,name)
INTO table_2 (id, name)
values(id,name)
INTO table_other (id, name)
values(id, name)
SELECT id,name
FROM a; 48.如何实现行列转换
<1>、固定列数的行列转换
如
student subject grade
---------------------------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
...
转换为
语文 数学 英语
student1 80 70 60
student2 90 80 100
...
语句如下：
select student,sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student <2>、不定列行列转换
如
c1 c2
--------------
1 我
1 是
1 谁
2 知
2 道
3 不
...
转换为
1 我是谁
2 知道
3 不 这一类型的转换必须借助于PL/SQL来完成，这里给一个例子
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
RETURN VARCHAR2
IS
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
END;
/
SQL> select distinct c1 ,get_c2(c1) cc2 from table;即可
--例子：
create table okcai_1
(
user_id varchar2(10),
user_number varchar2(10),
user_num number(8)
)
user_id user_number user_num
---------------------
1 123 2
1 456 5
1 789 6
2 11 2
2 22 3
2 33 4
2 44 5
2 55 6
2 66 7
2 77 8
3 1234 1
3 5678 2
方式一：
create or replace function get_col(
p_userId number,
p_col number
) return varchar
as
v_tmp varchar2(255);
begin
select user_number||chr(9)||user_num into v_tmp
from
(select user_number,user_num,rownum row_id
from okcai_1
where user_id = p_userId) a
where row_id = p_col;
return ltrim(v_tmp);
--return v_tmp;
end; 然后
select distinct user_id,get_col(user_id,1),get_col(user_id,2),get_col(user_id,3) .... from okcai_1 方式二：
create or replace function get_col(
p_userId number,
p_col number
) return varchar
as
v_tmp varchar2(255);
begin
select user_number||chr(9)||user_num into v_tmp
from
(select user_number,user_num,rownum row_id
from okcai_1
where user_id = p_userId) a
where row_id = p_col;
return ltrim(v_tmp);
--return v_tmp;
end;
select distinct user_id,get_col_new(user_id) from okcai_1;
49.怎么设置存储过程的调用者权限
普通存储过程都是所有者权限，如果想设置调用者权限，请参考如下语句
create or replace
procedure ...()
AUTHID CURRENT_USER
As
begin
...
end;
50.Oracle有哪些常见关键字
详细信息可以查看v$reserved_words视图 51.怎么查看数据库参数
<1> show parameter 参数名
如通过show parameter spfile可以查看9i是否使用spfile文件
其中参数名是可以匹配的。
比如show parameter cursor ,则会显示跟cursor相关的参数
<2>
select * from v$parameter
<3> 除了这部分参数，Oracle还有大量隐含参数，可以通过如下语句查看:
SELECT NAME
,VALUE
,decode(isdefault, 'TRUE','Y','N') as "Default"
,decode(ISEM,'TRUE','Y','N') as SesMod
,decode(ISYM,'IMMEDIATE', 'I',
'DEFERRED', 'D',
'FALSE', 'N') as SysMod
,decode(IMOD,'MODIFIED','U',
'SYS_MODIFIED','S','N') as Modified
,decode(IADJ,'TRUE','Y','N') as Adjusted
,description
FROM ( --GV$SYSTEM_PARAMETER
SELECT x.inst_id as instance
,x.indx+1
,ksppinm as NAME
,ksppity
,ksppstvl as VALUE
,ksppstdf as isdefault
,decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') as ISEM
,decode(bitand(ksppiflg/65536,3),
1,'IMMEDIATE',2,'DEFERRED','FALSE') as ISYM
,decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE') as IMOD
,decode(bitand(ksppstvf,2),2,'TRUE','FALSE') as IADJ
,ksppdesc as DESCRIPTION
FROM x$ksppi x
,x$ksppsv y
WHERE x.indx = y.indx
AND substr(ksppinm,1,1) = '_'
AND x.inst_id = USERENV('Instance')
)
ORDER BY NAME
52.怎样建立基于函数索引
8i以上版本，确保
Query_rewrite_enabled=true
Query_rewrite_integrity=trusted
Compatible=8.1.0以上
Create index indexname on table (function(field));
53.怎么样移动表或表分区
[A]移动表的语法
Alter table tablename move
[Tablespace new_name
Storage(initial 50M next 50M
pctincrease 0 pctfree 10 pctused 50 initrans 2) nologging]
移动分区的语法
alter table tablename move (partition partname)
[update global indexes]
之后之后必须重建索引
Alter index indexname rebuild
如果表有Lob段，那么正常的Alter不能移动Lob段到别的表空间，而仅仅是移动了表段，可以采用如下的方法移动Lob段
alter table tablename move
lob(lobsegname) store as (tablespace newts); 54.怎么样修改表的列名
[A]9i以上版本可以采用rname命令
ALTER TABLE UserName.TabName
RENAME COLUMN SourceColumn TO DestColumn
9i以下版本可以采用create table …… as select * from SourceTable的方式。
另外，8i以上可以支持删除列了
ALTER TABLE UserName.TabName
SET UNUSED (ColumnName) CASCADE CONSTRAINTS
ALTER TABLE UserName.TabName
DROP (ColumnName) CASCADE CONSTRAINTS
55.case的用法
在sql语句中
CASE test_value
WHEN expression1 THEN value1
[[WHEN expression2 THEN value2] [...]]
[ELSE default_value]
END
比如1
SELECT last_name, job_id, salary
CASE job_id
WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees 比如2
select
case
when real_charge>=20000 and real_charge<30000 then 5000
when real_charge>=30000 and real_charge<40000 then 9000
when real_charge>=40000 and real_charge<50000 then 10000
when real_charge>=50000 and real_charge<60000 then 14000
when real_charge>=60000 and real_charge<70000 then 18000
when real_charge>=70000 and real_charge<80000 then 19000
when real_charge>=80000 and real_charge<90000 then 24000
when real_charge>=90000 and real_charge<100000 then 27000
when real_charge>=100000 and real_charge<110000 then 27000
when real_charge>=110000 and real_charge<120000 then 29000
when real_charge>=120000 then 36000
else
0
end ,acc_id,user_id,real_charge from okcai_jh_charge_200505 在存储过程中
case v_strGroupClassCode
when '1' then
v_nAttrNum := v_nAttrNum + 300;
v_strAttrFlag := '1'||substr(v_strAttrFlag,2,7);
when '2' then
v_nAttrNum := v_nAttrNum + 200;
v_strAttrFlag := '2'||substr(v_strAttrFlag,2,7);
else
NULL;
end case;
注意的是存储过程和sql语句有的细微差别是用end case，而不是end。语句后面跟";" |
