Oracle 学习笔记，，，经验总结整理的。。。

最新推荐文章于 2022-07-14 08:28:22 发布

fushaoping2008

最新推荐文章于 2022-07-14 08:28:22 发布

阅读量3.8k

点赞数

cmd进入：
1. D:\oracle\ora92\bin>
2. sqlplus /nolog
3. conn sys/sys as sysdba

sqlplus进入：
username：sys
password：sys
HostString： test as sysdba
重复

用户管理
创建用户，设定权限

create user fusp identified by fusp default tablespace users temporary tablespace temp;
grant dba to fusp; –dba,recource,connect –赋权限
alter system set resource_limit=true scope=both; –期限，密码等
disconnect；
conn fusp/fusp；
show user;

创建profile
create profile px_test limit
sessions_per_user 3
idle_time 10; –单位：分钟

创建用户
create user dd identified by dd
default tablespace users
temporary tablespace temp
profile px_test;

创建的dba用户，都会存放到dba_users表中
修改用户的默认表空间：
alter user fusp default tablespace users;

查询：
select username,DEFAULT_TABLESPACE from dba_users;

启动：
SQL> startup
ORACLE instance started.

    Total System Global Area  143727516 bytes
    Fixed Size                   453532 bytes
    Variable Size             109051904 bytes
    Database Buffers           33554432 bytes
    Redo Buffers                 667648 bytes
    Database mounted.
    Database opened.

看数据库归档模式
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination D:\oracle\ora92\RDBMS
Oldest online log sequence 15
Current log sequence 17

查看归档相关初始化参数
show parameter archive

创建spfile
1. startup pfile=D:\oracle\admin\test\pfile\init.ora
2. create pfile from spfile;（根据提示将相应的文件拷贝）

更改存档路径
1. startup; –启动oracle
2. alter system set log_archive_start = true scope = spfile; –修改数据库
3. show parameter archive –查看归档参数参数
4. alter system set log_archive_dest_1=’location=D:\oracle\admin\test\temp’ scope =spfile; –更改log_archive_dest_1的存档路径
5. shutdown –关闭
6. startup; –启动
7. show parameter archive –再次查看一下归档参数

把归档日志文件切换:
1. alter system switch logfile;

创建表空间:
create tablespace exam datafile ‘D:\app\tablespace\example1.dbf’ size 10M;

修改表空间：
alter tablespace exam add datafile ‘D:\oracle\admin\test\temp\example2.dbf’ size 5M;

查看表空间大小：
select tablespace_name,sum(bytes/1024/1024)
from dba_data_files group by tablespace_name;

查询动态表空间
select * from v$tablespace;

区管理方式
Select tablespace_name,extent_management from dba_tablespaces;

表空间类型
Select tablespace_name,status,contents from dba_tablespaces;

数据文件自动扩展
Select file_name, AUTOEXTENSIBLE,status,bytes/1024/1024 from dba_data_files where tablespace_name= ‘USERS’;

查看日志
select * from v $logfile; select * from v$ log;

添加group
alter database add logfile ‘d:\temp\aa.log’ size 5M;
alter database add logfile group 5 ‘d:\temp\aa1.log’ size 5M;

添加成员member
alter database add logfile member ‘d:\temp\aa2.log’ to group 4;

控制文件位置：
1.查看初始化参数：init.ora
2 打开文件找到control_files：*

生成本地Tablespace
create tablespace example02 datafile ‘c:\’ ????????????????????????????

修改表空间状态 (读和写在线与下线)
变：
alter tablespace exam read only;
alter tablespace exam read write;
查：
select tablespace_name,status,contents
from dba_tablespaces;

变：
alter tablespace exam offline;
alter tablespace exam online;
查：
select name,status from v$datafile;

创建临时表空间：
create temporary tablespace temptablespace tempfile ‘d:\temp\temptablespace.dbf’ size 5M
extent management local uniform size 100k;

付少苹
查询表数据文件实际大小
select file_name,bytes/1024/1024 from dba_data_files ;

查询表空间空闲
select tablespace_name,sum(bytes/1024/1024)
from dba_free_space group by tablespace_name;

查询临时表空间实际大小
select tablespace_name,file_name,bytes/1024/1024 from dba_temp_files;

设置行长
set linesize 100;

设置格式
col tablespace_name format a50;

查看表空间大小：
select tablespace_name,sum(bytes/1024/1024)
from dba_data_files
where tablespace_name=’EXAM’
group by tablespace_name;

修改表空间大小
alter database datafile ‘D:\oracle\admin\test\temp\example2.DBF’ resize 20M;

自动变更表空间大小
alter database datafile ‘D:\oracle\admin\test\temp\example2.DBF’ autoextend on next 1M maxsize 20M;

删除表空间：
drop tablespace example02;
including contents and datafiles;(连带数据文件一起删除)

查询表空间tablesapce：
select tablespace_name,status from dba_tablespaces;
select * from v$tablespace;

查询块segment:
select segment_name,tablespace_name from dba_segments where segment name=’DEPT’;

查询extent：
select segment_name,extent_id from dba_extents where segment_name=’DEPT’;

DBA基础操作：

创建表：
create table employee
(
empno char(5),
ename varchar2(10),
job varchar2(20),
deptno char(2)
)
–storage(initial 200k next 200k minextents 2,maxextents 100)
tablespace exam;

变更表：
alter table employee storage(next 300k maxextents 150);

alter table employee allocate extent(size 600k);

alter table employee deallocate unused;

添加column：
alter table employee add column(sal number(5));

删除里column:
1.alter table employee drop column job;

2.alter table employee set unused column job; –需要先将其置为不用，然后再进行删除，提高性能
alter table employee drop unused columns;

创建临时表：
1. create global temporary table temp1 –事务提交时，数据被删除
(a number, b number)
on commit delete rows;

create global temporary table temp2 –session结束时将其删除，与事务无关
(a number, b number)
on commit preserve rows;

删除表数据：
Truncate table employee; –不可恢复；

删除表
drop table employee;

删除表连带相关约束
drop table dept cascade constraints;

查看该用户下，所有的表：
select table_name from user_tables;

***********************************************************************8

导出数据库：
在cmd化境下：
exp scott/tiger file=d:/test1.dmp log=d:/test1.log tables=dept –tables
exp scott/tiger file=d:/test1.dmp log=d:/test1.log owner=scott –user
exp scott/tiger file=d:/test1.dmp log=d:/test1.log tablespaces=users –tablespaces
exp scott/tiger file=d:/test1.dmp log=d:/test1.log full=y –all

导入数据库：
imp userid=scott/tiger file=d:/test1.dmp log=d:/test1.log tables=dept row=y
imp userid=scott/tiger file=d:/test1.dmp log=d:/test1.log fromuser=scott touser=scott
imp userid=scott/tiger file=d:/test1.dmp log=d:/test1.log tablespaces=users
imp userid=scott/tiger file=d:/test1.dmp log=d:/test1.log full=y

查询Server端字符集
select userenv(‘language’) from dual;

oracle解决temp01.dbf文件过大的方法2009-09-09 09:25第一步删除原来的文件
SQL>alter database tempfile ‘d:\oracle\oradata\ynrmdb\temp01.dbf’ drop ;
Database altered.
第二步添加新的临时文件
SQL>alter tablespace temp add tempfile
2’d:\oracle\oradata\ynrmdb\temp01.dbf’
3 size 2048M reuse autoextend on next 100M;
Tablespace altered.
SQL> select d.file_name, d.file_id, d.tablespace_name, d.bytes from dba_temp_files d;
第三步查看当前临时文件
FILE_NAME FILE_ID TABLESPACE_NAME BYTES

D:\ORACLE\ORADATA\YNRMDB\TEMP01.DBF 1 TEMP 2147483648

=============================================================================================================
Oracle数据导入导出imp/exp就相当于oracle数据还原与备份。exp命令可以把数据从远程数据库服务器导出到本地的dmp文件，imp命令可以把dmp文件从本地导入到远处的数据库服务器中。利用这个功能可以构建两个相同的数据库，一个用来测试，一个用来正式使用。

执行环境：可以在SQLPLUS.EXE或者DOS（命令行）中执行，
DOS中可以执行时由于在oracle 8i 中安装目录\ora81\BIN被设置为全局路径，
该目录下有EXP.EXE与IMP.EXE文件被用来执行导入导出。
oracle用java编写，SQLPLUS.EXE、EXP.EXE、IMP.EXE这两个文件有可能是被包装后的类文件。
SQLPLUS.EXE调用EXP.EXE、IMP.EXE所包裹的类，完成导入导出功能。

下面介绍的是导入导出的实例。
数据导出：
1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
exp system/manager@TEST file=d:\daochu.dmp full=y

2 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)

3 将数据库中的表inner_notify、notify_staff_relat导出
exp aichannel/aichannel@TESTDB2 file= d:\data\newsmgnt.dmp tables=(inner_notify,notify_staff_relat)

4 将数据库中的表table1中的字段filed1以”00”打头的数据导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\” where filed1 like ‘00%’\”

上面是常用的导出，对于压缩，既用winzip把dmp文件可以很好的压缩。
也可以在上面命令后面加上 compress=y 来实现。

数据的导入
1 将D:\daochu.dmp 中的数据导入 TEST数据库中。
imp system/manager@TEST file=d:\daochu.dmp
imp aichannel/aichannel@HUST full=y file=file= d:\data\newsmgnt.dmp ignore=y
上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。
在后面加上 ignore=y 就可以了。

2 将d:\daochu.dmp中的表table1 导入
imp system/manager@TEST file=d:\daochu.dmp tables=(table1)

基本上上面的导入导出够用了。不少情况要先是将表彻底删除，然后导入。

注意：
操作者要有足够的权限，权限不够它会提示。
数据库时可以连上的。可以用tnsping TEST 来获得数据库TEST能否连上。

附录一：
给用户增加导入数据权限的操作
第一,启动sql*puls
第二，以system/manager登陆
第三，create user 用户名 IDENTIFIED BY 密码（如果已经创建过用户，这步可以省略）
第四，GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,
DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,
DBA,CONNECT,RESOURCE,CREATE SESSION TO 用户名字
第五, 运行-cmd-进入dmp文件所在的目录,
imp userid=system/manager full=y file=*.dmp
或者 imp userid=system/manager full=y file=filename.dmp

执行示例:
F:\Work\Oracle_Data\backup>imp userid=test/test full=y file=inner_notify.dmp

屏幕显示
Import: Release 8.1.7.0.0 - Production on 星期四 2月 16 16:50:05 2006
(c) Copyright 2000 Oracle Corporation. All rights reserved.

连接到: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

经由常规路径导出由EXPORT:V08.01.07创建的文件
已经完成ZHS16GBK字符集和ZHS16GBK NCHAR 字符集中的导入
导出服务器使用UTF8 NCHAR 字符集 (可能的ncharset转换)
. 正在将AICHANNEL的对象导入到 AICHANNEL
. . 正在导入表 “INNER_NOTIFY” 4行被导入
准备启用约束条件…
成功终止导入，但出现警告。

附录二：
Oracle 不允许直接改变表的拥有者, 利用Export/Import可以达到这一目的.
先建立import9.par,
然后，使用时命令如下：imp parfile=/filepath/import9.par
例 import9.par 内容如下：
FROMUSER=TGPMS
TOUSER=TGPMS2 （注：把表的拥有者由FROMUSER改为TOUSER，FROMUSER和TOUSER的用户可以不同）
ROWS=Y
INDEXES=Y
GRANTS=Y
CONSTRAINTS=Y
BUFFER=409600
file==/backup/ctgpc_20030623.dmp
log==/backup/import_20030623.log

========================================================================================================

*****************************************************************************8

–example6.1.1>cursor ‘s row set is allowed null;
DECLARE
CURSOR query_member
IS
SELECT name FROM MEMBER WHERE member_id=300;
BEGIN
OPEN query_member;
END;

*****************************************************************************8

–example6.1.2>it’s will be fetch,even if null;
DECLARE
CURSOR query_employee
IS
SELECT NAME FROM MEMBER WHERE member_id=300;
v_name MEMBER.NAME%TYPE;
BEGIN
OPEN query_employee;
FETCH query_employee INTO v_name ;
dbms_output.put_line(v_name);
END;

*****************************************************************************8

–example6.1.3>you should be familiar with the loop syntax .
DECLARE
CURSOR query_member IS SELECT * FROM MEMBER;
member_row MEMBER%ROWTYPE;
BEGIN
OPEN query_member;
LOOP
FETCH query_member INTO member_row ;
EXIT WHEN query_member%NOTFOUND OR query_member%ROWCOUNT > 2;
dbms_output.put_line(‘name is ‘||member_row.NAME);
END LOOP;
CLOSE query_member;
END;

*****************************************************************************8

–example6.2.1>a general syntax ,with conditions.
DECLARE
CURSOR query_employee IS SELECT first_name||last_name
FROM employees GROUP BY first_name||last_name ;
v_name VARCHAR2(20) ;
BEGIN
IF NOT query_employee%ISOPEN THEN
OPEN query_employee;
END IF ;

LOOP
FETCH query_employee INTO v_name ;
EXIT WHEN query_employee%NOTFOUND ;
dbms_output.put_line(‘name is ‘||v_name);
END LOOP;
CLOSE query_employee;
END;

*****************************************************************************8

–example6.2.2>explicit cursor attribute - %rowcount
DECLARE
CURSOR query_employee IS SELECT first_name||last_name FROM employees WHERE
employee_ID=5;
v_name VARCHAR2(20) ;
BEGIN
IF NOT query_employee%ISOPEN THEN
OPEN query_employee;
END IF ;
DBMS_OUTPUT.put_line(query_employee%Rowcount);
CLOSE query_employee;
END;

*****************************************************************************8

–example 6.3> for ..loop syntax
DECLARE
CURSOR member_cur IS SELECT NAME ,sal FROM MEMBER;
BEGIN
FOR member_rec IN member_cur LOOP
dbms_output.put(‘name =’|| member_rec.name|| ’ ‘);
dbms_output.put_line(‘sal = ‘||member_rec.sal);
END LOOP;
END;

*****************************************************************************8

–example 7.1>Cursors with parameters
DECLARE
CURSOR query_dept(dno NUMBER) IS
SELECT NAME,sal FROM MEMBER
WHERE dept_id=dno;
BEGIN
FOR r1 IN query_dept(10) LOOP
dbms_output.put_line(r1.NAME);
END LOOP;
END;

*****************************************************************************8

–example 8.1>using a no_data_found exception
DECLARE
v_name MEMBER.NAME%TYPE;
BEGIN
SELECT NAME INTO v_name FROM MEMBER
WHERE member_id = 100000;
Dbms_Output.put_line(v_name);
EXCEPTION
WHEN no_data_found THEN
Dbms_Output.put_line(‘the user doesn”t exist’);
END;

*****************************************************************************8

–example 8.2>using a too_many_rows exception
DECLARE
v_name MEMBER.Name%TYPE;
BEGIN
SELECT NAME INTO v_name FROM MEMBER WHERE dept_id=20;
EXCEPTION

WHEN too_many_rows THEN
dbms_output.put_line(‘too many rows’);
WHEN OTHERS THEN
dbms_output.put_line(‘others’);
END;

*****************************************************************************8

–example 8.3>confusion
BEGIN
DELETE FROM employees WHERE employee_id=&ID;
IF SQL%ROWCOUNT > 0 THEN
Dbms_Output.put_line(‘yes’);
ELSE
Dbms_Output.put_line(‘no’);
END IF;
END;

*****************************************************************************8

–example 8.4>using a invalid_cursor exception
DECLARE
v_name employees.first_name%TYPE;
CURSOR emp_cursor IS
SELECT first_name FROM employees WHERE employee_id=&ID;
BEGIN
FETCH emp_cursor INTO v_name;
CLOSE emp_cursor;
EXCEPTION
WHEN invalid_cursor THEN
dbms_output.put_line(‘请检查游标’);
END;

*****************************************************************************8

–example8.5>using a DUP_VAL_ON_INDEX exception
BEGIN
INSERT INTO employees (employee_id) VALUES(&ID);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
dbms_output.put_line(‘违反PK 约束’);
END;

*****************************************************************************8

–example8.6>no-pre exception
DECLARE
myexception EXCEPTION;
PRAGMA EXCEPTION_INIT (myexception,-2292);
BEGIN
DELETE FROM dept WHERE dept_id=20;
EXCEPTION
WHEN myexception THEN
dbms_output.put_line(‘该行已被其他表参照’);
WHEN OTHERS THEN
dbms_output.put_line(‘…..’);
END;

*****************************************************************************8

–example 8.7>user-defined exception
–[09:00-18:00] access
DECLARE
myexception EXCEPTION;
BEGIN
IF to_char(SYSDATE,’HH24:MI’) NOT BETWEEN ‘09:00’
AND ‘17:00’ THEN
RAISE myexception;
END IF;
dbms_output.put_line(‘001’);

EXCEPTION
WHEN myexception THEN
dbms_output.put_line(‘bbbb’);
END;

*****************************************************************************8

–example8.8>raise_application_error

BEGIN
IF to_char(SYSDATE,’HH24:MI’) NOT BETWEEN ‘09:00’
AND ‘17:00’ THEN
raise_application_error(‘-20000’,’不允许登陆’);
END IF;
dbms_output.put_line(‘001’);
/*EXCEPTION
WHEN OTHERS THEN
raise_application_error(‘-20030’,’不允许登陆’); */
END;

2.5*7/8=2.5

*****************************************************************************8
–example9.1>no parameter
CREATE OR REPLACE PROCEDURE procedure_no
IS
BEGIN
DBMS_OUTPUT.put_line(‘这是一个没有参数的例子’);
END ;

执行procedure的两种方法：
1：
BEGIN
procedure_no;
END;

2：在comm 上面
SQL> set serveroutput on
SQL> exec procedure_no;

create or replace procedure pp
is
begin
dbms_output.put_line(‘ddddd’);
end;

*****************************************************************************8

–example9.2>in parameter ,where is error .
CREATE OR REPLACE PROCEDURE PROCEDURE_PARAMETER_IN(P_DEPTNO IN
NUMBER) IS
V_MAX MEMBER.SAL%TYPE;
BEGIN
SELECT MAX(SAL) INTO V_MAX FROM MEMBER WHERE DEPT_ID = P_DEPTNO;
IF v_max IS NULL THEN
DBMS_OUTPUT.PUT_LINE(‘该部门目前没有员工’);
ELSE
DBMS_OUTPUT.PUT_LINE(P_DEPTNO || ’ 部门的最大薪资是’ || V_MAX);
END IF;
END;

执行
begin
PROCEDURE_PARAMETER_IN(11);
end;
*****************************************************************************8

–example9.3>out parameter
CREATE OR REPLACE PROCEDURE PROCEDURE_PARAMETER_OUT(P_ENO IN NUMBER,
P_ENAME OUT VARCHAR2)
IS
BEGIN
SELECT NAME INTO P_ENAME FROM MEMBER WHERE MEMBER_ID = P_ENO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘没有这个员工’);
END;
/*
define call env variable
exec procedure_name(in parameter, :define call env variable);
print define call env variable
*/
方法：
1: 在plsql:command环境下:
var name varchar2(20)
exec procedure_parameter_out(100,:name)
(补充：可以打印参数:print name)
2:
在sql*plus环境下：
SQL> set serveroutput on
SQL> declare v_name member.name%type;
2 begin
3 procedure_parameter_out(100,v_name);
4 dbms_output.put_line(v_name);
5 end;
6 /
3: 在plsql的sqlwindow下
declare v_name member.name%type;
begin
procedure_parameter_out(100,v_name);
dbms_output.put_line(v_name);
end;

*****************************************************************************8

–example9.5>in&out parameter
CREATE OR REPLACE PROCEDURE inout(p_input in OUT NUMBER)
IS
BEGIN
p_input :=p_input*p_input;
END;
/*
define call env variable & value
exec procedure_name(define call env variable);
print define call env variable
*/

调用方法：
方法1：plsql的command窗口下：
SQL> set serveroutput on
SQL> var num number
SQL> exec :num:=3

  PL/SQL procedure successfully completed
  num
  ---------
  3
  SQL> exec inout(:num)
  PL/SQL procedure successfully completed
  num
  ---------
  9

方法2：sql plus环境下：
SQL> declare
2 v_num number;
3 begin
4 v_num :=3;
5 inout(v_num);
6 dbms_output.put_line(v_num);
7 end;
8 /
9
PL/SQL 过程已成功完成。

*****************************************************************************8

–example9.6>parameter assign value
CREATE OR REPLACE PROCEDURE emp_info(p_dpid employees.department_id%TYPE,
p_j obid employees.j ob_id%TYPE DEFAULT 1)
IS
v_lastname employees.last_name%TYPE;
BEGIN
SELECT last_name INTO v_lastname FROM employees
WHERE department_id=p_dpid AND j ob_id=p_j obid;
dbms_output.put_line(‘last_name is ‘||v_lastname);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line(‘没有你要找的信息’);
END;
/*
exec procedure_name(Actual Parameters,Actual Parameters)
exec procedure_name(Formal parameters=>values,Formal parameters=>values)
exec procedure_name(Actual Parameters,Formal parameters=>values)
*/
CREATE OR REPLACE PROCEDURE test01 (
num1 NUMBER DEFAULT 1,
num2 NUMBER DEFAULT 2,
num3 NUMBER DEFAULT 3)
IS
BEGIN
dbms_output.put_line(‘num1 = ‘||num1);
dbms_output.put_line(‘num2 = ‘||num2);
dbms_output.put_line(‘num3 = ‘||num3);
END;
–run
EXEC test01(3,4,5);
EXEC test01(num2=>66,num3=>100,num1=>23);
EXEC test01(num2 => 33);
EXEC test01(11,num2=>44);
EXEC test01(num1=>30);
EXEC test01(1,num2=>44,2); 改正：EXEC test01(1,num2=>44,2);
exec test01(num3=>44,11,2);
BEGIN
FOR i IN 1..9 LOOP
FOR j IN 1..i LOOP
dbms_output.put(i||’*’||j ||’ = ‘||i*j || ’ ‘) ;
END LOOP;
dbms_output.put_line(”);
END LOOP;
END;

*****************************************************************************8

–example9.7>declaring subprograms
CREATE OR REPLACE PROCEDURE subproc (p_eid MEMBER.MEMBER_ID%TYPE)
IS
v_lastname MEMBER.Name%TYPE;
PROCEDURE log_select
IS
BEGIN
INSERT INTO Log_Table VALUES(USER,SYSDATE);
COMMIT;
END log_select;
BEGIN
SELECT NAME INTO v_lastname FROM MEMBER WHERE member_id=p_eid;
dbms_output.put_line(‘last_name’||v_lastname);
log_select;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line(‘没有你要找的信息’);
END;

*****************************************************************************8

CREATE OR REPLACE PROCEDURE add_member (p_eid MEMBER.MEMBER_ID%TYPE)
IS
v_lastname MEMBER.Name%TYPE;
PROCEDURE log_select
IS
BEGIN
INSERT INTO Log_Table VALUES(USER,SYSDATE);
COMMIT;
END log_select;
BEGIN
SELECT NAME INTO v_lastname FROM MEMBER WHERE member_id=p_eid;
dbms_output.put_line(‘last_name’||v_lastname);
log_select;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line(‘没有你要找的信息’);
END;

–9.8>handled exceptions
–9.8.1 raised a unhandle exception
CREATE OR REPLACE PROCEDURE TEST03 IS
BEGIN
INSERT INTO TEST VALUES (10, ‘test03’);
INSERT INTO TEST VALU

ES (11, ‘test03’);
TEST04;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Create a exception ‘);
END;

CREATE OR REPLACE PROCEDURE TEST04 IS
BEGIN
INSERT INTO TEST VALUES (21, ‘test04’);
INSERT INTO TEST VALUES (22, ‘test04’);
RAISE_APPLICATION_ERROR(-20020, ‘raise a exception’);
END;

*****************************************************************************8

–9.8.2
/*the whole can be commit or rollback
called procedure’s exception is handled by itself */
CREATE OR REPLACE PROCEDURE test03
IS
BEGIN
INSERT INTO test VALUES(10,’test03’);
INSERT INTO test VALUES(11,’test03’);
test04;
COMMIT;
INSERT INTO test VALUES(13,’001’);
END;

CREATE OR REPLACE PROCEDURE test04
IS
BEGIN
INSERT INTO test VALUES(21,’test04’);
INSERT INTO test VALUES(22,’test04’);
raise_application_error(-20020,’001’);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘test04’);
END;

*****************************************************************************8

–9.8.3
/* commit part */
CREATE OR REPLACE PROCEDURE test05(p_id NUMBER,p_name VARCHAR2)
IS
BEGIN
IF (p_id >20) THEN
INSERT INTO test VALUES(p_id,p_name);
COMMIT;
END IF;
test06(p_id);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
dbms_output.put_line(‘have handled’);
END;

CREATE OR REPLACE PROCEDURE test06(p_id NUMBER)
IS
BEGIN
IF (p_id >30) THEN
INSERT INTO test VALUES(p_id,USER);
COMMIT;
ELSE
raise_application_error(‘-20011’,’001’);
END IF;
END;

*****************************************************************************8

CREATE OR REPLACE FUNCTION f1 RETURN NUMBER
IS
BEGIN
RETURN 1;
END;

如何调用：
方法1：
select f1 from dual;

方法2：
plsql command环境下：
SQL> set serveroutput on
SQL> var num number
SQL> exec :num:=f1

*****************************************************************************8

–10.1>
CREATE OR REPLACE FUNCTION GET_SAL(P_ID IN NUMBER) RETURN NUMBER IS
V_SALARY MEMBER.SAL%TYPE;
BEGIN
SELECT SAL INTO V_SALARY FROM MEMBER WHERE MEMBER_ID = P_ID;
RETURN V_SALARY;
END;
–select name,get_sal(member_id) from member;
–select get_sal(100) from dual;
/*
define call env variable
exec procedure_name(in parameter, :define call env variable);
print define call env variable
*/

调用： select name,get_sal(member_id) from member;

*****************************************************************************8

DECLARE
v_name MEMBER.Name%TYPE;
BEGIN
SELECT NAME INTO v_name FROM MEMBER WHERE member_id=120;
EXCEPTION
WHEN no_data_found THEN
IF SQL%NOTFOUND THEN
dbms_output.put_line(‘a’);
ELSE
dbms_output.put_line(‘b’);
END IF;
END;

*****************************************************************************8

–10.2>
CREATE OR REPLACE FUNCTION fun01
(p_id IN NUMBER)
RETURN NUMBER IS

V_num NUMBER(5);

BEGIN
v_num :=p_id;
RETURN v_num;

END;

*****************************************************************************8
–10.3>parameter rowtype note:rowtype equals cursor sometime.
CREATE OR REPLACE FUNCTION get_fun–function can have no parameters
(p_row MEMBER%ROWTYPE) RETURN MEMBER%ROWTYPE
IS
BEGIN
RETURN p_row;
END;

VARCHAR2 (p_row.member_id||’ ‘||p_row.NAME||’ ‘||p_row.sal)
CREATE OR REPLACE PROCEDURE display_pro
IS
CURSOR m_cur IS SELECT * FROM MEMBER;
BEGIN
FOR m_rec IN m_cur LOOP
dbms_output.put_line(get_fun(m_rec));
END LOOP;
END;

*****************************************************************************8

–revoke
BEGIN
display_pro;
END;

*****************************************************************************8

–10.4>at least a return statement,but return the first value
–a
CREATE OR REPLACE FUNCTION return_more_fun RETURN VARCHAR2
IS
BEGIN
RETURN ‘a’;
RETURN ‘b’;
RETURN ‘c’;
RETURN ‘d’;

END;
–b
CREATE OR REPLACE FUNCTION tran_FUN(P_NUM NUMBER) RETURN VARCHAR2 IS
BEGIN
IF P_NUM BETWEEN 90 AND 100 THEN
RETURN P_num ||’ 的成绩是A’;
ELSE
IF P_NUM BETWEEN 80 AND 89 THEN
RETURN P_num ||’ 的成绩是B’;
ELSE
IF P_NUM BETWEEN 70 AND 79 THEN
RETURN P_num ||’ 的成绩是C’;
ELSE
IF P_NUM BETWEEN 60 AND 69 THEN
RETURN P_num ||’ 的成绩是D’;
ELSE
RETURN P_num ||’ 的成绩是E’;
END IF;
END IF;
END IF;
END IF;
END;

*****************************************************************************8

–revoke
BEGIN
dbms_output.put_line(tran_fun(91));
dbms_output.put_line(tran_fun(85));
dbms_output.put_line(tran_fun(70));
dbms_output.put_line(tran_fun(61));
dbms_output.put_line(tran_fun(45));
END;

*****************************************************************************8

–c
CREATE OR REPLACE FUNCTION f RETURN BOOLEAN
IS
BEGIN
dbms_output.put_line(‘true’);
RETURN TRUE;
dbms_output.put_line(‘001001’);
END;

BEGIN
IF f THEN
dbms_output.put_line(‘true’);
ELSE
dbms_output.put_line(‘false’);
END IF;
END;

*****************************************************************************8

–dsame table
CREATE OR REPLACE FUNCTION change_sal(p_id NUMBER) RETURN NUMBER
IS
v_sal MEMBER.Sal%TYPE;
BEGIN
UPDATE MEMBER SET sal=sal*1.1WHERE member_id=p_id;
SELECT sal INTO v_sal FROM MEMBER WHERE member_id=p_id;
RETURN v_sal;
END;
UPDATE MEMBER SET sal=change_sal(102) WHERE member_id=102
–dif
CREATE OR REPLACE FUNCTION change_sal(p_id NUMBER) RETURN NUMBER
IS
BEGIN
UPDATE dept SET dept_name=’lg’ WHERE dept_id=p_id;
RETURN 102;
END;

SELECT NAME ,change_sal(dept_id) FROM MEMBER;

*****************************************************************************8

–…
CREATE OR REPLACE FUNCTION get_count(p_dept NUMBER) RETURN NUMBER
IS
v_count NUMBER;
BEGIN

SELECT COUNT(*) INTO v_count FROM MEMBER WHERE dept_id=p_dept
;
RETURN v_count;
END;
–SELECT dept_id ,get_count(dept_id) FROM MEMBER

DECLARE
CURSOR M_CUR IS
SELECT DEPT_ID AS “aa” , COUNT(DEPT_ID) AS “bb” FROM MEMBER GROUP BY
DEPT_ID;
TYPE T_TYPE IS TABLE OF M_CUR%ROWTYPE INDEX BY BINARY_INTEGER;
V_COUNT NUMBER := 1;
T_TYPE_TEM T_TYPE;
CREATE OR REPLACE FUNCTION TT(D_DEPTID NUMBER) RETURN T_TYPE
IS
BEGIN
RETURN T_TYPE_TEM;
END;
BEGIN
FOR 001 IN t_type_tem LOOP
dbms_output.put_line(001.aa);
END;
END;

/* restrictions 1 and 2 sample */
create or replace function f01(v_deptid in number)
return number is
v number;
begin
update dept set loc_id=102 where dept_id=v_deptid;
v:=v_deptid;
return v;
end;
select * from member;

select * from member where member.dept_id=f01(10);

update member set member.name=’fushaoping’ where member.dept_id=f01(10);

*****************************************************************************8

–restrictions 3 sample
CREATE OR REPLACE FUNCTION get_deptid(p_id NUMBER) RETURN NUMBER
IS
v NUMBER;
BEGIN
SELECT dept_id INTO v FROM MEMBER WHERE member_id=p_id;
RETURN v;
END;

–revoke
UPDATE MEMBER SET bonus=100 WHERE dept_id=get_deptId(100);

*****************************************************************************8

–restrictions 4 sample
CREATE OR REPLACE FUNCTION commit_fun RETURN NUMBER
IS
BEGIN
COMMIT;
RETURN 100;
END;

begin
dbms_output.put_line(commit_fun);
end;

UPDATE MEMBER SET bonus=100 WHERE member_id=commit_fun;

*****************************************************************************8

–restrictions 5.1 sample
CREATE OR REPLACE FUNCTION res5 RETURN NUMBER
IS
v_num NUMBER;
PROCEDURE p1
IS
BEGIN
dbms_output.put_line(‘ok’);
END;

FUNCTION temp1 RETURN NUMBER
IS
BEGIN
RETURN 100;
END;
BEGIN
v_num := temp1;
p1;
RETURN v_num;
END;

*****************************************************************************8

–restrictions 5.2 sample
CREATE OR REPLACE FUNCTION res52(p_id NUMBER) RETURN NUMBER
IS
v_sal MEMBER.Sal%TYPE;
FUNCTION CHANGE_SAL(P_ID NUMBER) RETURN NUMBER IS
V_SAL MEMBER.SAL%TYPE;
BEGIN
UPDATE MEMBER SET SAL = SAL * 1.1 WHERE MEMBER_ID = P_ID;
SELECT SAL INTO V_SAL FROM MEMBER WHERE MEMBER_ID = P_ID;
RETURN V_SAL;
END;
BEGIN
v_sal :=change_sal(p_id);
RETURN v_sal;
END;

*****************************************************************************8
授予权限，与回收
grant select on member to devf2;
revoke select on member from devf2;

–select member_id,name,sal,res52(sal) from member;

SELECT * FROM User_Objects;

CREATE OR REPLACE PACKAGE member_admin
IS
FUNCTION get_sal(P_ID NUMBER) RETURN NUMBER;
PROCEDURE print_sal(P_ID NUMBER);
PROCEDURE print_max_sal;
g_max_sal MEMBER.sal%TYPE;
END;

CREATE OR REPLACE PACKAGE BODY member_admin
IS
v_temp MEMBER.sal%TYPE;

PROCEDURE print_max_sal
IS
BEGIN
dbms_output.put_line(‘g_max_sal = ‘||g_max_sal);
END;

FUNCTION get_sal(p_id NUMBER) RETURN NUMBER
IS
BEGIN
SELECT sal INTO v_temp FROM MEMBER WHERE member_id=p_id;
RETURN v_temp;
END;

PROCEDURE print_sal(P_ID NUMBER)
IS
BEGIN
dbms_output.put_line(‘该员工的薪资是’||get_sal(p_id));
END;

*****************************************************************************8

BEGIN
SELECT MAX (sal) INTO g_max_sal FROM MEMBER;

END;

*****************************************************************************8
*****************************************************************************8
*****************************************************************************8

–12>a package example
CREATE OR REPLACE PACKAGE my_package IS
—public
g_num NUMBER :=20;
PROCEDURE show_g_num ;
PROCEDURE update_num ;
function fun01(num number) return number;
END;

CREATE OR REPLACE PACKAGE BODY my_package
IS
v_count number default 1;

PROCEDURE show_g_num
IS
BEGIN
dbms_output.put_line(g_num);
v_count:=v_count+1;
g_num:=g_num+2;
END;

PROCEDURE update_num
is
begin
dbms_output.put_line(v_count);
dbms_output.put_line(g_num);
end;

function fun01(num number)
return number
is
begin
return num*10;
end;

END;
*****************************************************************************8
*****************************************************************************8
包packeage的调用：
方法1：plsql command 窗口定义
SQL> begin
2 my_package.g_num :=my_package.g_num*my_package.g_num;
3 dbms_output.put_line(my_package.g_num);
4 end;
5 /

方法2：plsql中sqlwindow的输出
调用procedure
begin
my_package.show_g_num;
end;
调用方法：
begin
dbms_output.put_line(my_package.fun01(20));
end;

*****************************************************************88888888

create or replace package pack01 is
procedure pr1;
end;

create or replace package body pack01 is
procedure pr2; –提前声明
procedure pr3; –提前声明

procedure pr1
is
begin
dbms_output.put_line(‘pr1’);
pr2;
end;

procedure pr2
is
begin
dbms_output.put_line(‘pr2’);
pr3;
end;

procedure pr3
is
begin
dbms_output.put_line(‘pr3’);
pr2;
end;
end;
*****************************************************************88888888
*****************************************************************88888888
自己实例：
create or replace package pack_cur
is
cursor c1 is select member.member_id from member;
procedure proc1_3rows;
procedure proc4_6rows;
end;

create or replace package body pack_cur
is
v_empno member.member_id%type;
procedure proc1_3rows is
begin
open c1;
loop
fetch c1 into v_empno;
dbms_output.put_line(‘Id: ‘||(v_empno));
exit when c1%rowcount >=3;
end loop;
end proc1_3rows;

procedure proc4_6rows is
begin
loop
fetch c1 into v_empno;
dbms_output.put_line(‘Id: ‘||(v_empno));
exit when c1%rowcount >=6;
end loop;
close c1;
end proc4_6rows;
end pack_cur;

begin
pack_cur.proc4_6rows;
end;

*****************************************************************88888888

CREATE OR REPLACE PROCEDURE a
IS
BEGIN
dbms_output.put_line(‘a’);
END;

CREATE OR REPLACE PROCEDURE a(p NUMBER)
IS
BEGIN
dbms_output.put_line(p);
END;

SELECT * FROM User_Source;
SELECT * FROM User_Errors;

*****************************************************************************8

–<10.overloading
CREATE OR REPLACE PACKAGE pack1 IS
FUNCTION proc(p1 VARCHAR2) RETURN VARCHAR2;
FUNCTION proc(p1NUMBER) RETURN NUMBER;
END;

CREATE OR REPLACE PACKAGE BODY pack1 IS
FUNCTION proc (p1 VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN ‘varchar2’;
END;

FUNCTION proc (p1NUMBER) RETURN NUMBER
IS
BEGIN
RETURN 100;
END;
END;

*****************************************************************************8

–13> package function
CREATE OR REPLACE PACKAGE pack2 IS
FUNCTION compuMax(p_id NUMBER) RETURN NUMBER;
FUNCTION compuMax(p_id VARCHAR2) RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY pack2 IS
FUNCTION compuMax(p_id NUMBER) RETURN NUMBER
IS
BEGIN
RETURN p_id;
END;

FUNCTION compuMax(p_id VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN p_id;
END;
END;

*****************************************************************************8

–13>
CREATE OR REPLACE PACKAGE pack5 IS
FUNCTION f01 RETURN VARCHAR2;
FUNCTION f02 RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY pack5 IS
FUNCTION f02 RETURN VARCHAR2 ;
FUNCTION f01 RETURN VARCHAR2
IS
BEGIN
dbms_output.put_line(‘—–f01—’);
dbms_output.put_line(f02);
RETURN ‘f01 working’;
END;

FUNCTION f02 RETURN VARCHAR2
IS
BEGIN
dbms_output.put_line(‘—–f02—’);
RETURN ‘f02 working’;
END;
END;

SELECT * FROM MEMBER;
SELECT * FROM dept;

*****************************************************************************8

–14>Restrictions
–14.1>
CREATE OR REPLACE PACKAGE pack3 IS
FUNCTION compuMax(p_id NUMBER) RETURN NUMBER;
END;

CREATE OR REPLACE PACKAGE BODY pack3 IS
FUNCTION compuMax(p_id NUMBER) RETURN NUMBER
IS
v_max MEMBER.SAL%TYPE;
BEGIN
SELECT MAX(sal) INTO v_max FROM MEMBER WHERE DEPT_ID=p_id;
COMMIT;
RETURN v_max;
END;
END;
SELECT NAME ,sal FROM MEMBER WHERE sal =pack3.compuMax(20);

*****************************************************************************8

–14.2>
/*
a query statement may not execute a dml statement.
eg:
1>select member_id,sal from member where sal > pack4.change_m(10);
*/
CREATE OR REPLACE PACKAGE pack4 IS
FUNCTION change_m(m_id NUMBER) RETURN NUMBER;
END;

CREATE OR REPLACE PACKAGE BODY pack4 IS
FUNCTION change_m(m_id NUMBER) RETURN NUMBER
IS
v_sal MEMBER.SAL%TYPE;
BEGIN
UPDATE MEMBER SET sal=sal*1.1 WHERE member_id=m_id;
SELECT sal INTO v_sal FROM MEMBER WHERE member_id=m_id;
RETURN v_sal;
END;
END;

*****************************************************************************8

–14.3>
/*
update member set sal = (select pack4.change_m from
*/
CREATE OR REPLACE PACKAGE pack6 IS
FUNCTION f000 RETURN VARCHAR2;
PROCEDURE f000 (p VARCHAR2) ;
END;

CREATE OR REPLACE PACKAGE BODY pack6 IS
FUNCTION f000 RETURN VARCHAR2
IS
BEGIN
RETURN ‘f000’;
END;

PROCEDURE f000 (p VARCHAR2) ;
IS
BEGIN
dbms_output.put_line(p);
END;
END;

*****************************************************************************8
*****************************************************************************8

–16.1 >trigger :a simple example[statement]
CREATE OR REPLACE TRIGGER trig01_a
BEFORE INSERT OR UPDATE OR DELETE ON test
BEGIN
dbms_output.put_line(‘before-statement-trigger is working ! ‘);
END;

CREATE OR REPLACE TRIGGER trig01_b
AFTER INSERT OR UPDATE OR DELETE ON test
BEGIN
dbms_output.put_line(‘after-statement-trigger is working ! ‘);
END;

select * from test;
insert into test values(40,’fffff’);
delete from test;

书上例子：
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
IF (TO_CHAR (SYSDATE,’DY’) IN (‘SAT’,’SUN’)) OR
(TO_CHAR (SYSDATE, ‘HH24’) NOT BETWEEN ‘08’ AND ‘18’)
THEN
IF DELETING THEN
RAISE_APPLICATION_ERROR (-20502,’You may delete from
EMPLOYEES table only during business hours.’);
ELSIF INSERTING THEN
RAISE_APPLICATION_ERROR (-20500,’You may insert into
EMPLOYEES table only during business hours.’);
ELSIF UPDATING THEN
RAISE_APPLICATION_ERROR (-20503,’You may update
SALARY only during business hours.’);
ELSE
RAISE_APPLICATION_ERROR (-20504,’You may update
EMPLOYEES table only during normal hours.’);
END IF;
END IF;
END;

*****************************************************************************8

–16.2 >trigger :a simple example[row]
CREATE OR REPLACE TRIGGER trig02
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
dbms_output.put_line(‘row-trigger is working ! ‘);
–COMMIT;
END;

*****************************************************************************8

–16.3>trigger :auto rollback ,decide by timing and handle position
CREATE OR REPLACE TRIGGER trig03
AFTER INSERT OR UPDATE OR DELETE ON dept
BEGIN
INSERT INTO test VALUES(11,’11’);
raise_application_error(-20020,’—-exception—’);
/*
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘it”s handled’);
*/
END;

BEGIN
INSERT INTO dept (dept_id) VALUES (15);
dbms_output.put_line(‘finished’);
COMMIT;
END;

*****************************************************************************8

–16.4>eg:of columns on tables
CREATE OR REPLACE TRIGGER trig04
BEFORE UPDATE OF sal ON MEMBER –只针对update有效
BEGIN
dbms_output.put_line(‘—–trig04 is working—-‘);
END;

*****************************************************************************8

–16.5> before or after
CREATE OR REPLACE TRIGGER trig05
BEFORE INSERT ON test
FOR EACH ROW
DECLARE
v_max NUMBER;
BEGIN
SELECT COUNT(*) INTO v_max FROM test;
dbms_output.put_line(v_max);
END;

INSERT INTO test VALUES (1,’001’);

*****************************************************************************8

–16.6>create view
CREATE OR REPLACE VIEW dept_emp AS
SELECT d.dept_id,dept_name,member_id,NAME
FROM MEMBER m ,dept d
WHERE m.dept_id = d.dept_id;

select * from dept_emp;
insert into dept_emp values(60,’ss’,120,’test’);

*********************************************************************************888

CREATE OR REPLACE TRIGGER tr_instead_of_dept_emp
INSTEAD OF INSERT ON dept_emp
–FOR EACH ROW
BEGIN
INSERT INTO dept (dept_id,dept_name) VALUES (:NEW.dept_id,:NEW.dept_name);
INSERT INTO MEMBER (member_id,NAME) VALUES(:NEW.member_id,:NEW.NAME);
END;

SELECT * FROM User_Triggers;

*****************************************************************************8

–17.1>schema trigger
CREATE OR REPLACE TRIGGER logon_trig
AFTER logon ON SCHEMA
BEGIN
INSERT INTO e VALUES (USER, SYSDATE, ‘Logging on’);
END;

CREATE OR REPLACE TRIGGER logoff_trig
BEFORE logoff ON SCHEMA
BEGIN
INSERT INTO log_trig_table VALUES (USER, SYSDATE, ‘Logging off’);
END;

*****************************************************************************8

–17.2>call example
CREATE OR REPLACE TRIGGER call_test
BEFORE INSERT OR UPDATE OR DELETE ON log_trig_table
CALL call_test_temp

CREATE OR REPLACE PROCEDURE call_test_temp IS
v_temp VARCHAR2(20) DEFAULT ‘lg cns china’;
BEGIN
dbms_output.put_line(v_temp);
END;

*****************************************************************************8

–17.3>mutate table
CREATE OR REPLACE TRIGGER check_salary
BEFORE INSERT OR UPDATE OF sal ON MEMBER
FOR EACH ROW
DECLARE
v_minsalary MEMBER.sal%TYPE;
v_maxsalary MEMBER.sal%TYPE;
BEGIN
SELECT MIN(sal),
MAX(sal)
INTO v_minsalary,
v_maxsalary
FROM MEMBER
WHERE jikwi = :NEW.jikwi;
IF :NEW.sal < v_minsalary
OR :NEW.sal > v_maxsalary THEN
raise_application_error(-20505,’Out of range’);
END IF;
END;

–shoule be
CREATE OR REPLACE PROCEDURE add_member
(
p_id MEMBER.member_id%TYPE,
p_name MEMBER.NAME%TYPE,
p_sal MEMBER.sal%TYPE
) IS
v_min MEMBER.sal%TYPE;
v_max MEMBER.sal%TYPE;
BEGIN
SELECT MAX(sal),
MIN(sal)
INTO v_max,
v_min
FROM MEMBER;
IF p_sal NOT BETWEEN v_min AND v_max THEN
raise_application_error(-20505,’Out of range’);
ELSE
INSERT INTO MEMBER (member_id, NAME, sal) VALUES (p_id, p_name, p_sal);
END IF;
END;

DECLARE
TABLE

*****************************************************************************8
/DEPT_EMP’,’DEPT两个表之间的参照关系：/

select * from user_dependencies
where referenced_name in (‘DEPT_EMP’,’DEPT’);
;

–18.1>alter member add (001 varchar2(20));
CREATE OR REPLACE FUNCTION get_max RETURN NUMBER
IS
v_max MEMBER.Sal%TYPE;
BEGIN
SELECT MAX(sal) INTO v_max FROM MEMBER;
RETURN v_max;
END;

CREATE OR REPLACE PROCEDURE get_max_pro
IS
BEGIN
dbms_output.put_line(get_max);
END;

SELECT * FROM User_Triggers,User_Objects
WHERE User_Triggers.Trigger_Name = User_Objects.Object_Name
AND User_Objects.Object_Type=’TRIGGER’;

SELECT * FROM user_objects WHERE status=’VALID’;
SELECT * FROM user_objects WHERE status=’INVALID’;
SELECT * FROM User_Dependencies WHERE referenced_owner=’A1’;

SELECT *
FROM USER_OBJECTS
WHERE OBJECT_NAME IN (‘MEMBER’,’GET_MAX’,’GET_MAX_PRO’);
select name,type,referenced_name,referenced_type
from user_dependencies
where referenced_name in(‘MEMBER’,’get_max’);

SELECT * FROM tab WHERE tname NOT LIKE ‘%$%’;

*****************************************************************************8

–18.2>create dblink;
CREATE DATABASE LINK mylink
CONNECT TO nanhai IDENTIFIED BY nanhai
USING ‘orcl’;tnsnames OR sid;
SELECT * FROM MEMBER@mylink;
SELECT SYSDATE FROM dual;

SELECT obj ect_name ,status
FROM User_Objects
WHERE object_name IN (‘MEMBER’,’DEPT’,’DEPT_EMP’);

*****************************************************************************8

–18.3>package depend..
CREATE OR REPLACE PACKAGE p18 IS
PROCEDURE p1 ;
PROCEDURE p2;
END;

CREATE OR REPLACE PACKAGE BODY p18 IS
PROCEDURE p1
IS
BEGIN
dbms_output.put_line(‘…….’);
END;
PROCEDURE p2
IS
BEGIN
NULL;
END;
END;

BEGIN
p18.p1;
END;

SELECT obj ect_name,status,TIMESTAMP FROM User_Objects
WHERE object_name=’P18’;

CREATE OR REPLACE PROCEDURE pr01
IS
BEGIN
pr02;
END;

CREATE OR REPLACE PROCEDURE pr02
IS
BEGIN
NULL;
NULL;
END;
PR01 VALID 2008-01-11:16:07:36
PR02 VALID 2008-01-11:16:06:07
1 PR01 VALID 2008-01-11:16:08:52
2 PR02 VALID 2008-01-11:16:08:16
SELECT obj ect_name,status,TIMESTAMP FROM User_Objects
WHERE object_name=’PR02’ OR obj ect_name=’PR01’;

BEGIN
pr01;
END;

*****************************************************************************8

–

DECLARE
v_sal NUMBER ;
v_id NUMBER :=102;
BEGIN
EXECUTE IMMEDIATE ‘select sal from member where member_id=:2’
INTO v_sal
USING v_id;
dbms_output.put_line(v_sal);
END;
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO dy VALUES(i);
END LOOP;
COMMIT;
END;

*****************************************************************************8

DECLARE
v_num NUMBER :=1;
BEGIN
LOOP
EXECUTE IMMEDIATE ‘INSERT INTO dy VALUES(:001)’
USING v_num;
v_num:=v_num+1;
EXIT WHEN v_num >100000;
END LOOP;
END;

*****************************************************************************8

　create or replace procedure pro_temp(v_col1 varchar2,v_col2 varchar2) is
　　v_num number;
　　begin
　　select count(*) into v_num from user_tables where table_name=’T_TEMP’;　　
　　–create temporary table
　　if v_num<1 then
　　execute immediate CREATE GLOBAL TEMPORARY TABLE T_TEMP (
　　COL1 VARCHAR2(10),
　　COL2 VARCHAR2(10)
　　) ON COMMIT delete ROWS;
　　end if;　
　　–insert data
　　execute immediate ‘insert into t_temp values(‘v_col1’,’v_col2’);　　
　　execute immediate select col1 from t_temp’ into v_num;
　　dbms_output.put_line(v_num);
　　 execute immediate delete from t_temp;
　　commit;
　　execute immediate ‘drop table t_temp;
　　end pro_temp;　

sql 查询记录为0 （没有记录）则返回结果用0 代替
case count(*) when 0 then 0 else max(f1)

*****************************************************************************8

oracle 查询最大字段
怎么查询表中某一字段最大值所在行的数据
SQL> select * from 001;

    ID     SEQ_ID NAME

     1          1 A
     1          2 A
     1          3 A
     2          1 B
     2          2 B
     3          1 C
     3          2 C
     3          3 C
     3          4 C

9 rows selected

SQL>
SQL> SELECT *
2 FROM 001 A
3 WHERE A.SEQ_ID = (SELECT MAX(B.SEQ_ID) FROM 001 B WHERE B.ID = A.ID)
4 /

    ID     SEQ_ID NAME

     1          3 A
     2          2 B
     3          4 C

TOP N中的TOP1，（依需要选用rank，dense_rank，row_number）
SELECT ID, SEQ_ID, NAME
FROM (SELECT ID,
SEQ_ID,
NAME,
RANK() OVER(PARTITION BY ID ORDER BY SEQ_ID DESC NULLS LAST) DRN
FROM TA)
WHERE DRN = 1
SQL> SELECT ID, SEQ_ID, NAME
2 FROM (SELECT ID,
3 SEQ_ID,
4 NAME,
5 RANK() OVER(PARTITION BY ID ORDER BY SEQ_ID DESC NULLS LAST) DRN
6 FROM 001)
7 WHERE DRN =1
8 /

    ID     SEQ_ID NAME

     1          3 A
     2          2 B
     3          4 C

某字段值最大的那条记录的另一个字段的值
查询最大记录字段
eg1： SELECT c_a FROM tb_a WHERE c_b = (SELECT MAX(c_b) FROM tb_a)

eg2：
SELECT a.c_a
FROM tb_a a
WHERE NOT EXISTS (SELECT 1 FROM tb_a WHERE c_b > a.c_b)

*****************************************************************************8

trigger示例
create or replace trigger tri_wwm
after update of event_Seq on TB_high_LEVEL_EVENT referencing new as new old as old for each row
begin
if :old.Lower_Report_Operate_Flag=’Y’ then

insert into TB_LOW_LEVEL_EVENT
  (EVENT_SEQ,
   UPPER_EVENT_DEAL_STATUS,
   UPPER_DEALER_NAME,
   UPPER_ONDUTY_DEAL_PROCESS,
   UPPER_ONDUTY_TIME_COUNT,
   UPPER_ACCEPTOR_NAME,
   CREATE_DATE)
values
  (:new.EVENT_SEQ, '1', '1', '1', 1, '1', sysdate);

end if;

end;

*****************************************************************************8

查询所有表，以及各个字段的sql

SELECT t.table_name, c.column_name
FROM user_all_tables t, user_tab_columns c
WHERE t.table_name = c.table_name

将行信息转换成列信息（行列互换sql）
SELECT id, ltrim(MAX(sys_connect_by_path(mc, ‘;’)), ‘;’) add_mc
FROM (SELECT id,
mc,
row_number() over(PARTITION BY id ORDER BY id) rn_by_id,
row_number() over(ORDER BY id) + id rn
FROM test)
START WITH rn_by_id = 1
CONNECT BY rn - 1 = PRIOR rn
GROUP BY id
ORDER BY id;

zhouwf0726 | 27 七月, 2006 12:01

–该测试脚本可以直接运行

–现在想把数据库中数据按照固定字段分组相加，这里总结了三种方法。

–创建测试表、添加测试数据

create table test(id varchar2(10),mc varchar2(50));
insert into test values(‘1’,’11111’);
insert into test values(‘1’,’22222’);
insert into test values(‘2’,’11111’);
insert into test values(‘2’,’22222’);
insert into test values(‘3’,’11111’);
insert into test values(‘3’,’22222’);
insert into test values(‘3’,’33333’);
commit;

–方法一：

SELECT t.table_name, c.column_name
FROM user_all_tables t, user_tab_columns c
WHERE t.table_name = c.table_name

;

set serveroutput on size 1000000
declare
union_mc varchar2(200);
begin
for cur_a in(select distinct id from test) loop
for cur_b in(select mc from test where id=cur_a.id) loop
union_mc:=union_mc||cur_b.mc;
end loop;
dbms_output.put_line(cur_a.id||chr(9)||union_mc);
union_mc := ”;
end loop;
end;
/

–方法二：

CREATE OR REPLACE function link(v_id varchar2)
return varchar2
is
union_mc varchar2(200);
begin
for cur in (select mc from test where id=v_id) loop
union_mc := union_mc||cur.mc;
end loop;
union_mc := rtrim(union_mc,1);
return union_mc;
end;
/

select id,link(id) from test group by id;

–方法三：

/从Oracle 9i开始，开发者可以创建用户自定义的合计函数，除了PL/SQL外，还可以使用任何Oralce所支持的语言（如C＋＋或者Java）来创建合计函数。TYPE头定义必须包含ODCIAggregateInitialize、ODCIAggregateIterate、ODCIAggregateMerge和ODCIAggregateTerminate这四个接口函数。/

/Initialize函数对数据组各个需要处理的字段各运行一次。自然的，我需要为每一个值准备一个新的清单，所以需要初始化持久变量list，这里初始化值为null。/

/Iterate函数处理返回的行，所以实际上是由它来创建返回的值的清单。先测试list是否为空，如果为空，就把list直接设置为所引入的value值；如果list变量非空，则给list添加一个逗号后再插入value值，list的最大允许字符数32767。/

/Terminate函数在数据组的每个行的感兴趣字段数据被处理后执行。在这个函数中我只需简单的返回清单变量即可。/

/Merge函数，用来返回成功标记的。/

/创建自己的合计函数扩展了Oracle统计和文本处理能力。/

create or replace type t_cat as object
(
union_mc VARCHAR2(200),
static function ODCIAggregateInitialize(sctx IN OUT t_cat) return number,
member function ODCIAggregateIterate(self IN OUT t_cat,value IN varchar2) return number,
member function ODCIAggregateTerminate(self IN t_cat,returnValue OUT varchar2, flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT t_cat,ctx2 IN t_cat) return number
);

create or replace type body t_cat is
static function ODCIAggregateInitialize(sctx IN OUT t_cat )
return number is
begin
sctx := t_cat(”);
return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT t_cat, value IN varchar2)
return number is
begin
self.union_mc := self.union_mc || value;
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN t_cat, returnValue OUT varchar2, flags IN number) return number is
begin
returnValue := self.union_mc;
return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT t_cat , ctx2 IN t_cat ) return number is
begin
return ODCIConst.Success;
end;
end;
/

/如果你的Oracle服务器没有配置成支持并行处理的方式，可以去掉参数PARALLEL_ENABLE/

create or replace function catstr(v_mc varchar2) return varchar2 PARALLEL_ENABLE AGGREGATE USING t_cat;
/

select id,catstr(mc) from test group by id;

http://zhouwf0726.itpub.net/post/9689/161638

查询所有表的表名，列名
查询所有表结构，表，字段，字段类型（含备注说明）

SELECT com.table_name,
upper(tc.comments),
com.COLUMN_NAME,
col.DATA_TYPE,
col.DATA_LENGTH,
p.POSITION,
col.NULLABLE,
com.COMMENTS
FROM ALL_COL_COMMENTS com,
ALL_TAB_COLUMNS col,
ALL_CONS_COLUMNS p,
all_tab_comments tc
WHERE com.table_name = col.table_name
AND com.column_name= col.column_name
–AND com.table_name = ‘ATT_MON_ATT’
AND com.owner = col.owner
AND com.table_name = p.table_name(+)
AND com.column_name= p.column_name(+)
AND com.owner = p.owner(+)
AND p.position(+) IS NOT NULL
AND col.owner =’LGESPM’ –此处为用户
and col.OWNER = tc.owner
and col.TABLE_NAME= tc.table_name
and col.TABLE_NAME like ‘SPMS_SHOP_CHECK%’
ORDER BY com.table_name,col.column_id

查询当前用户所有表及字段和备注注释
SELECT t.table_name,
c.column_name,
p.comments,
c.DATA_TYPE
FROM user_all_tables t,
user_tab_columns c,
user_col_comments p
WHERE t.table_name = c.table_name
AND t.table_name = p.table_name
AND c.COLUMN_NAME = p.column_name
ORDER BY t.table_name ASC,c.COLUMN_ID asc

*****************************************************************************8
oracle 树查询语句
详细出处参考：http://www.jb51.net/article/19182.htm

格式：
SELECT column
FROM table_name
START WITH column=value
CONNECT BY PRIOR 父主键=子外键

select lpad(’ ‘,4*(level-1))||name name,job,id,super from emp
start with super is null
connect by prior id=super

例子：
原始数据：select no,q from a_example2
NO NAME

001 a01
001 a02
001 a03
001 a04
001 a05
002 b01
003 c01
003 c02
004 d01
005 e01
005 e02
005 e03
005 e04
005 e05
需要实现得到结果是：
001 a01;a02;a03;a04;a05
002 b01
003 c01;c02
004 d01
005 e01;e02;e03;e04;e05

思路：
1、ORACLE8.1之后有个connect by 子句，取出整棵树数据。
create table a_example1
(
no char(3) not null,
name varchar2(10) not null,
parent char(3)
)
insert into a_example1
values(‘001’,’老王’,null)
insert into a_example1
values(‘101’,’老李’,null)
insert into a_example1
values(‘002’,’大王1’,’001’)
insert into a_example1
values(‘102’,’大李1’,’101’)
insert into a_example1
values(‘003’,’大王2’,’001’)
insert into a_example1
values(‘103’,’大李2’,’101’)
insert into a_example1
values(‘003’,’小王1’,’002’)
insert into a_example1
values(‘103’,’小李1’,’102’)
ＮＯ　　ＮＡＭＥ　ＰＡＲＥＮＴ
001 老王
101 老李
002 大王1 001
102 大李1 101
003 大王2 001
103 大李2 101
003 小王1 002
103 小李1 102
//按照家族树取数据
select *　from a_example1
select level,sys_connect_by_path(name,’/’) path
from a_example1
start with /name = ‘老王’ and/ parent is null
connect by parent = prior no
结果：
1 /老王
2 /老王/大王1
3 /老王/大王1/小王1
2 /老王/大王2
1 /老李
2 /老李/大李1
3 /老李/大李1/小李1
2 /老李/大李2
按照上面思路，我们只要将原始数据做成如下结构：
NO NAME
001 a01
001 a01/a02
001 a01/a02/a03
001 a01/a02/a03/a04
001 a01/a02/a03/a04/a05
002 b01
003 c01
003 c01/c02
004 d01
005 e01
005 e01/e02
005 e01/e02/e03
005 e01/e02/e03/e04
005 e01/e02/e03/e04/e05
最后按NO分组，取最大的一个值即为所需的结果。
NO NAME
001 a01/a02/a03/a04/a05
002 b01
003 c01/c02
004 d01
005 e01/e02/e03/e04/e05
SQL语句：
select no,max(sys_connect_by_path(name,’;’)) result from
(select no,name,rn,lead(rn) over(partition by no order by rn) rn1
from (select no,name,row_number() over(order by no,name desc) rn from a_example2)
)
start with rn1 is null connect by rn1 = prior rn
group by no
语句分析：
1、SELECT no, NAME, row_number() over(ORDER BY no, NAME DESC) rn
FROM a_example2

按照NO升序排序，同时按照NAME降序排序，产生伪列，目的是要形成树结构
ＮＯ　　ＮＡＭＥ　ＲＮ
001 a03 1
001 a02 2
001 a01 3
002 b01 4
003 c02 5
003 c01 6
004 d01 7
005 e05 8
005 e04 9
005 e03 10
005 e02 11
005 e01 12
2、SELECT no, NAME, rn, lead(rn) over(PARTITION BY no ORDER BY rn) rn1
FROM (SELECT no, NAME, row_number() over(ORDER BY no, NAME DESC) rn
FROM a_example2)
生成家族谱，即子节点与父节点有对应关系，对应关系通过rn和 rn1。其中lead为上一条记录的RN值
ＮＯ　　ＮＡＭＥ　ＲＮ　　ＲＮ１　 001 a03 1 2 –
说明：针对ＮＯ＝００１来说，其下一条记录的ＲＮ＝２ 001 a02 2 3　–说明：针对ＮＯ＝００１来说，其下一条记录的ＲＮ＝３ 001 a01 3 　–说明：针对ＮＯ＝００１来说，其下一条记录的RN IS NULL
002 b01 4 003 c02 5 6 003 c01 6 004 d01 7 005 e05 8 9 005 e04 9 10 005 e03 10 11 005 e02 11 12 005 e01 12
3、SELECT no, sys_connect_by_path(NAME, ‘;’) RESULT
FROM (SELECT no, NAME, rn, lead(rn) over(PARTITION BY no ORDER BY rn) rn1
FROM (SELECT no, NAME, row_number() over(ORDER BY no, NAME DESC) rn
FROM a_example2))
START WITH rn1 IS NULL
CONNECT BY rn1 = PRIOR rn

正式生成树
ＮＯ　　　ＲＥＳＵＬＴ
001 ;a01
001 ;a01;a02
001 ;a01;a02;a03
002 ;b01
005 ;e01
005 ;e01;e02
005 ;e01;e02;e03
005 ;e01;e02;e03;e04
005 ;e01;e02;e03;e04;e05
003 ;c01
003 ;c01;c02
004 ;d01
将上面结果按照NO分组，取result最大值即可，所以将上述语句改为
select no,max(sys_connect_by_path(name,’;’)) result from
(select no,name,rn,lead(rn) over(partition by no order by rn) rn1
from (select no,name,row_number() over(order by no,name desc) rn from a_example2)
)
start with rn1 is null connect by rn1 = prior rn
group by no
得到所需结果。
详细出处参考：http://www.jb51.net/article/19182.htm

*****************************************************************************8
Oracle 分析函数的使用

Oracle 分析函数使用介绍
分析函数是oracle816引入的一个全新的概念,为我们分析数据提供了一种简单高效的处理方式.在分析函数出现以前,我们必须使用自联查询,子查询或者内联视图,甚至复杂的存储过程实现的语句,现在只要一条简单的sql语句就可以实现了,而且在执行效率方面也有相当大的提高.下面我将针对分析函数做一些具体的说明.

今天我主要给大家介绍一下以下几个函数的使用方法
1. 自动汇总函数rollup,cube,
2. rank 函数, rank,dense_rank,row_number
3. lag,lead函数
4. sum,avg,的移动增加,移动平均数
5. ratio_to_report报表处理函数
6. first,last取基数的分析函数

基础数据
06:34:23 SQL> select * from t;

BILL_MONTH AREA_CODE NET_TYPE LOCAL_FARE

200405 5761 G 7393344.04
200405 5761 J 5667089.85
200405 5762 G 6315075.96
200405 5762 J 6328716.15
200405 5763 G 8861742.59
200405 5763 J 7788036.32
200405 5764 G 6028670.45
200405 5764 J 6459121.49
200405 5765 G 13156065.77
200405 5765 J 11901671.70
200406 5761 G 7614587.96
200406 5761 J 5704343.05
200406 5762 G 6556992.60
200406 5762 J 6238068.05
200406 5763 G 9130055.46
200406 5763 J 7990460.25
200406 5764 G 6387706.01
200406 5764 J 6907481.66
200406 5765 G 13562968.81
200406 5765 J 12495492.50
200407 5761 G 7987050.65
200407 5761 J 5723215.28
200407 5762 G 6833096.68
200407 5762 J 6391201.44
200407 5763 G 9410815.91
200407 5763 J 8076677.41
200407 5764 G 6456433.23
200407 5764 J 6987660.53
200407 5765 G 14000101.20
200407 5765 J 12301780.20
200408 5761 G 8085170.84
200408 5761 J 6050611.37
200408 5762 G 6854584.22
200408 5762 J 6521884.50
200408 5763 G 9468707.65
200408 5763 J 8460049.43
200408 5764 G 6587559.23

BILL_MONTH AREA_CODE NET_TYPE LOCAL_FARE

200408 5764 J 7342135.86
200408 5765 G 14450586.63
200408 5765 J 12680052.38

40 rows selected.

Elapsed: 00:00:00.00

使用rollup函数的介绍

Quote:
下面是直接使用普通sql语句求出各地区的汇总数据的例子
06:41:36 SQL> set autot on
06:43:36 SQL> select area_code,sum(local_fare) local_fare
06:43:50 2 from t
06:43:51 3 group by area_code
06:43:57 4 union all
06:44:00 5 select ‘合计’ area_code,sum(local_fare) local_fare
06:44:06 6 from t
06:44:08 7 /

AREA_CODE LOCAL_FARE

5761 54225413.04
5762 52039619.60
5763 69186545.02
5764 53156768.46
5765 104548719.19
合计 333157065.31

6 rows selected.

Elapsed: 00:00:00.03

下面是使用分析函数rollup得出的汇总数据的例子
06:44:09 SQL> select nvl(area_code,’合计’) area_code,sum(local_fare) local_fare
06:45:26 2 from t
06:45:30 3 group by rollup(nvl(area_code,’合计’))
06:45:50 4 /

AREA_CODE LOCAL_FARE

5761 54225413.04
5762 52039619.60
5763 69186545.02
5764 53156768.46
5765 104548719.19
333157065.31

6 rows selected.

Elapsed: 00:00:00.00

从上面的例子我们不难看出使用rollup函数,系统的sql语句更加简单,耗用的资源更少,从6个consistent gets降到4个consistent gets,如果基表很大的话,结果就可想而知了.

使用cube函数的介绍

Quote:
为了介绍cube函数我们再来看看另外一个使用rollup的例子
06:53:00 SQL> select area_code,bill_month,sum(local_fare) local_fare
06:53:37 2 from t
06:53:38 3 group by rollup(area_code,bill_month)
06:53:49 4 /

AREA_CODE BILL_MONTH LOCAL_FARE

5761 200405 13060433.89
5761 200406 13318931.01
5761 200407 13710265.93
5761 200408 14135782.21
5761 54225413.04
5762 200405 12643792.11
5762 200406 12795060.65
5762 200407 13224298.12
5762 200408 13376468.72
5762 52039619.60
5763 200405 16649778.91
5763 200406 17120515.71
5763 200407 17487493.32
5763 200408 17928757.08
5763 69186545.02
5764 200405 12487791.94
5764 200406 13295187.67
5764 200407 13444093.76
5764 200408 13929695.09
5764 53156768.46
5765 200405 25057737.47
5765 200406 26058461.31
5765 200407 26301881.40
5765 200408 27130639.01
5765 104548719.19
333157065.31

26 rows selected.

Elapsed: 00:00:00.00

系统只是根据rollup的第一个参数area_code对结果集的数据做了汇总处理,而没有对bill_month做汇总分析处理,cube函数就是为了这个而设计的.
下面,让我们看看使用cube函数的结果

06:58:02 SQL> select area_code,bill_month,sum(local_fare) local_fare
06:58:30 2 from t
06:58:32 3 group by cube(area_code,bill_month)
06:58:42 4 order by area_code,bill_month nulls last
06:58:57 5 /

AREA_CODE BILL_MONTH LOCAL_FARE

5761 200405 13060.43
5761 200406 13318.93
5761 200407 13710.27
5761 200408 14135.78
5761 54225.41
5762 200405 12643.79
5762 200406 12795.06
5762 200407 13224.30
5762 200408 13376.47
5762 52039.62
5763 200405 16649.78
5763 200406 17120.52
5763 200407 17487.49
5763 200408 17928.76
5763 69186.54
5764 200405 12487.79
5764 200406 13295.19
5764 200407 13444.09
5764 200408 13929.69
5764 53156.77
5765 200405 25057.74
5765 200406 26058.46
5765 200407 26301.88
5765 200408 27130.64
5765 104548.72
200405 79899.53
200406 82588.15
200407 84168.03
200408 86501.34
333157.05

30 rows selected.

Elapsed: 00:00:00.01

可以看到,在cube函数的输出结果比使用rollup多出了几行统计数据.这就是cube函数根据bill_month做的汇总统计结果

1 rollup 和 cube函数的再深入

Quote:
从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,
我们如何来区分到底是根据那个字段做的汇总呢,
这时候,oracle的grouping函数就粉墨登场了.
如果当前的汇总记录是利用该字段得出的,grouping函数就会返回1,否则返回0

1 select decode(grouping(area_code),1,’all area’,to_char(area_code)) area_code,
2 decode(grouping(bill_month),1,’all month’,bill_month) bill_month,
3 sum(local_fare) local_fare
4 from t
5 group by cube(area_code,bill_month)
6* order by area_code,bill_month nulls last
07:07:29 SQL> /

AREA_CODE BILL_MONTH LOCAL_FARE

5761 200405 13060.43
5761 200406 13318.93
5761 200407 13710.27
5761 200408 14135.78
5761 all month 54225.41
5762 200405 12643.79
5762 200406 12795.06
5762 200407 13224.30
5762 200408 13376.47
5762 all month 52039.62
5763 200405 16649.78
5763 200406 17120.52
5763 200407 17487.49
5763 200408 17928.76
5763 all month 69186.54
5764 200405 12487.79
5764 200406 13295.19
5764 200407 13444.09
5764 200408 13929.69
5764 all month 53156.77
5765 200405 25057.74
5765 200406 26058.46
5765 200407 26301.88
5765 200408 27130.64
5765 all month 104548.72
all area 200405 79899.53
all area 200406 82588.15
all area 200407 84168.03
all area 200408 86501.34
all area all month 333157.05

30 rows selected.

Elapsed: 00:00:00.01
07:07:31 SQL>

可以看到,所有的空值现在都根据grouping函数做出了很好的区分,这样利用rollup,cube和grouping函数,我们做数据统计的时候就可以轻松很多了.

rank函数的介绍

介绍完rollup和cube函数的使用,下面我们来看看rank系列函数的使用方法.

问题2.我想查出这几个月份中各个地区的总话费的排名.

Quote:
为了将rank,dense_rank,row_number函数的差别显示出来,我们对已有的基础数据做一些修改,将5763的数据改成与5761的数据相同.
1 update t t1 set local_fare = (
2 select local_fare from t t2
3 where t1.bill_month = t2.bill_month
4 and t1.net_type = t2.net_type
5 and t2.area_code = ‘5761’
6* ) where area_code = ‘5763’
07:19:18 SQL> /

8 rows updated.

Elapsed: 00:00:00.01

我们先使用rank函数来计算各个地区的话费排名.
07:34:19 SQL> select area_code,sum(local_fare) local_fare,
07:35:25 2 rank() over (order by sum(local_fare) desc) fare_rank
07:35:44 3 from t
07:35:45 4 group by area_codee
07:35:50 5
07:35:52 SQL> select area_code,sum(local_fare) local_fare,
07:36:02 2 rank() over (order by sum(local_fare) desc) fare_rank
07:36:20 3 from t
07:36:21 4 group by area_code
07:36:25 5 /

AREA_CODE LOCAL_FARE FARE_RANK

5765 104548.72 1
5761 54225.41 2
5763 54225.41 2
5764 53156.77 4
5762 52039.62 5

Elapsed: 00:00:00.01

我们可以看到红色标注的地方出现了,跳位,排名3没有出现
下面我们再看看dense_rank查询的结果.

07:36:26 SQL> select area_code,sum(local_fare) local_fare,
07:39:16 2 dense_rank() over (order by sum(local_fare) desc ) fare_rank
07:39:39 3 from t
07:39:42 4 group by area_code
07:39:46 5 /

AREA_CODE LOCAL_FARE FARE_RANK

5765 104548.72 1
5761 54225.41 2
5763 54225.41 2
5764 53156.77 3 这是这里出现了第三名
5762 52039.62 4

Elapsed: 00:00:00.00

在这个例子中,出现了一个第三名,这就是rank和dense_rank的差别,
rank如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,而dense_rank则不会,
差别更大的是,row_number哪怕是两个数据完全相同,排名也会不一样,这个特性在我们想找出对应没个条件的唯一记录的时候又很大用处

1 select area_code,sum(local_fare) local_fare,
2 row_number() over (order by sum(local_fare) desc ) fare_rank
3 from t
4* group by area_code
07:44:50 SQL> /

AREA_CODE LOCAL_FARE FARE_RANK

5765 104548.72 1
5761 54225.41 2
5763 54225.41 3
5764 53156.77 4
5762 52039.62 5

在row_nubmer函数中,我们发现,哪怕sum(local_fare)完全相同,我们还是得到了不一样排名,我们可以利用这个特性剔除数据库中的重复记录.

这个帖子中的几个例子是为了说明这三个函数的基本用法的. 下个帖子我们将详细介绍他们的一些用法.

a. 取出数据库中最后入网的n个用户
select user_id,tele_num,user_name,user_status,create_date
from (
select user_id,tele_num,user_name,user_status,create_date,
rank() over (order by create_date desc) add_rank
from user_info
)
where add_rank <= :n;

b.根据object_name删除数据库中的重复记录
create table t as select obj#,name from sys.obj$;
再insert into t1 select * from t1 数次.
delete from t1 where rowid in (
select row_id from (
select rowid row_id,row_number() over (partition by obj# order by rowid ) rn
) where rn <> 1
);

c. 取出各地区的话费收入在各个月份排名.
SQL> select bill_month,area_code,sum(local_fare) local_fare,
2 rank() over (partition by bill_month order by sum(local_fare) desc) area_rank
3 from t
4 group by bill_month,area_code
5 /

BILL_MONTH AREA_CODE LOCAL_FARE AREA_RANK

200405 5765 25057.74 1
200405 5761 13060.43 2
200405 5763 13060.43 2
200405 5762 12643.79 4
200405 5764 12487.79 5
200406 5765 26058.46 1
200406 5761 13318.93 2
200406 5763 13318.93 2
200406 5764 13295.19 4
200406 5762 12795.06 5
200407 5765 26301.88 1
200407 5761 13710.27 2
200407 5763 13710.27 2
200407 5764 13444.09 4
200407 5762 13224.30 5
200408 5765 27130.64 1
200408 5761 14135.78 2
200408 5763 14135.78 2
200408 5764 13929.69 4
200408 5762 13376.47 5

20 rows selected.
SQL>

lag和lead函数介绍

取出每个月的上个月和下个月的话费总额
1 select area_code,bill_month, local_fare cur_local_fare,
2 lag(local_fare,2,0) over (partition by area_code order by bill_month ) pre_local_fare,
3 lag(local_fare,1,0) over (partition by area_code order by bill_month ) last_local_fare,
4 lead(local_fare,1,0) over (partition by area_code order by bill_month ) next_local_fare,
5 lead(local_fare,2,0) over (partition by area_code order by bill_month ) post_local_fare
6 from (
7 select area_code,bill_month,sum(local_fare) local_fare
8 from t
9 group by area_code,bill_month
10* )
SQL> /
AREA_CODE BILL_MONTH CUR_LOCAL_FARE PRE_LOCAL_FARE LAST_LOCAL_FARE NEXT_LOCAL_FARE POST_LOCAL_FARE

5761 200405 13060.433 0 0 13318.93 13710.265
5761 200406 13318.93 0 13060.433 13710.265 14135.781
5761 200407 13710.265 13060.433 13318.93 14135.781 0
5761 200408 14135.781 13318.93 13710.265 0 0
5762 200405 12643.791 0 0 12795.06 13224.297
5762 200406 12795.06 0 12643.791 13224.297 13376.468
5762 200407 13224.297 12643.791 12795.06 13376.468 0
5762 200408 13376.468 12795.06 13224.297 0 0
5763 200405 13060.433 0 0 13318.93 13710.265
5763 200406 13318.93 0 13060.433 13710.265 14135.781
5763 200407 13710.265 13060.433 13318.93 14135.781 0
5763 200408 14135.781 13318.93 13710.265 0 0
5764 200405 12487.791 0 0 13295.187 13444.093
5764 200406 13295.187 0 12487.791 13444.093 13929.694
5764 200407 13444.093 12487.791 13295.187 13929.694 0
5764 200408 13929.694 13295.187 13444.093 0 0
5765 200405 25057.736 0 0 26058.46 26301.881
5765 200406 26058.46 0 25057.736 26301.881 27130.638
5765 200407 26301.881 25057.736 26058.46 27130.638 0
5765 200408 27130.638 26058.46 26301.881 0 0
20 rows selected.

利用lag和lead函数,我们可以在同一行中显示前n行的数据,也可以显示后n行的数据.

sum,avg,max,min移动计算数据介绍

计算出各个连续3个月的通话费用的平均数
1 select area_code,bill_month, local_fare,
2 sum(local_fare)
3 over ( partition by area_code
4 order by to_number(bill_month)
5 range between 1 preceding and 1 following ) “3month_sum”,
6 avg(local_fare)
7 over ( partition by area_code
8 order by to_number(bill_month)
9 range between 1 preceding and 1 following ) “3month_avg”,
10 max(local_fare)
11 over ( partition by area_code
12 order by to_number(bill_month)
13 range between 1 preceding and 1 following ) “3month_max”,
14 min(local_fare)
15 over ( partition by area_code
16 order by to_number(bill_month)
17 range between 1 preceding and 1 following ) “3month_min”
18 from (
19 select area_code,bill_month,sum(local_fare) local_fare
20 from t
21 group by area_code,bill_month
22* )
SQL> /

AREA_CODE BILL_MONTH LOCAL_FARE 3month_sum 3month_avg 3month_max 3month_min

5761 200405 13060.433 26379.363 13189.6815 13318.93 13060.433
5761 200406 13318.930 40089.628 13363.2093 13710.265 13060.433
5761 200407 13710.265 41164.976 13721.6587 14135.781 13318.93
40089.628 = 13060.433 + 13318.930 + 13710.265
13363.2093 = (13060.433 + 13318.930 + 13710.265) / 3
13710.265 = max(13060.433 + 13318.930 + 13710.265)
13060.433 = min(13060.433 + 13318.930 + 13710.265)
5761 200408 14135.781 27846.046 13923.023 14135.781 13710.265
5762 200405 12643.791 25438.851 12719.4255 12795.06 12643.791
5762 200406 12795.060 38663.148 12887.716 13224.297 12643.791
5762 200407 13224.297 39395.825 13131.9417 13376.468 12795.06
5762 200408 13376.468 26600.765 13300.3825 13376.468 13224.297
5763 200405 13060.433 26379.363 13189.6815 13318.93 13060.433
5763 200406 13318.930 40089.628 13363.2093 13710.265 13060.433
5763 200407 13710.265 41164.976 13721.6587 14135.781 13318.93
5763 200408 14135.781 27846.046 13923.023 14135.781 13710.265
5764 200405 12487.791 25782.978 12891.489 13295.187 12487.791
5764 200406 13295.187 39227.071 13075.6903 13444.093 12487.791
5764 200407 13444.093 40668.974 13556.3247 13929.694 13295.187
5764 200408 13929.694 27373.787 13686.8935 13929.694 13444.093
5765 200405 25057.736 51116.196 25558.098 26058.46 25057.736
5765 200406 26058.460 77418.077 25806.0257 26301.881 25057.736
5765 200407 26301.881 79490.979 26496.993 27130.638 26058.46
5765 200408 27130.638 53432.519 26716.2595 27130.638 26301.881

20 rows selected.

ratio_to_report函数的介绍

Quote:
1 select bill_month,area_code,sum(local_fare) local_fare,
2 ratio_to_report(sum(local_fare)) over
3 ( partition by bill_month ) area_pct
4 from t
5* group by bill_month,area_code
SQL> break on bill_month skip 1
SQL> compute sum of local_fare on bill_month
SQL> compute sum of area_pct on bill_month
SQL> /

BILL_MONTH AREA_CODE LOCAL_FARE AREA_PCT

200405 5761 13060.433 .171149279
5762 12643.791 .165689431
5763 13060.433 .171149279
5764 12487.791 .163645143
5765 25057.736 .328366866
**** —————- ———-
sum 76310.184 1

200406 5761 13318.930 .169050772
5762 12795.060 .162401542
5763 13318.930 .169050772
5764 13295.187 .168749414
5765 26058.460 .330747499
**** —————- ———-
sum 78786.567 1

200407 5761 13710.265 .170545197
5762 13224.297 .164500127
5763 13710.265 .170545197
5764 13444.093 .167234221
5765 26301.881 .327175257
**** —————- ———-
sum 80390.801 1

200408 5761 14135.781 .170911147
5762 13376.468 .161730539
5763 14135.781 .170911147
5764 13929.694 .168419416
5765 27130.638 .328027751
**** —————- ———-
sum 82708.362 1

20 rows selected.

6 first,last函数使用介绍

Quote:
取出每月通话费最高和最低的两个用户.
1 select bill_month,area_code,sum(local_fare) local_fare,
2 first_value(area_code)
3 over (order by sum(local_fare) desc
4 rows unbounded preceding) firstval,
5 first_value(area_code)
6 over (order by sum(local_fare) asc
7 rows unbounded preceding) lastval
8 from t
9 group by bill_month,area_code
10* order by bill_month
SQL> /

BILL_MONTH AREA_CODE LOCAL_FARE FIRSTVAL LASTVAL

200405 5764 12487.791 5765 5764
200405 5762 12643.791 5765 5764
200405 5761 13060.433 5765 5764
200405 5765 25057.736 5765 5764
200405 5763 13060.433 5765 5764
200406 5762 12795.060 5765 5764
200406 5763 13318.930 5765 5764
200406 5764 13295.187 5765 5764
200406 5765 26058.460 5765 5764
200406 5761 13318.930 5765 5764
200407 5762 13224.297 5765 5764
200407 5765 26301.881 5765 5764
200407 5761 13710.265 5765 5764
200407 5763 13710.265 5765 5764
200407 5764 13444.093 5765 5764
200408 5762 13376.468 5765 5764
200408 5764 13929.694 5765 5764
200408 5761 14135.781 5765 5764
200408 5765 27130.638 5765 5764
200408 5763 14135.781 5765 5764

20 rows selected.

————=-=—————======—=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

OLAP函数语法的总结over(…)

ORACLE OLAP 函数
最近这个东东用得特别多，总结了一下。

语法: FUNCTION_NAME(,,…)
OVER
()

OLAP函数语法四个部分:
1、function本身用于对窗口中的数据进行操作；
2、partitioning clause 用于将结果集分区；
3、order by clause 用于对分区中的数据进行排序；
4、windowing clause 用于定义function在其上操作的行的集合，即function所影响的范围。

一、order by对窗口的影响
不含order by的：
SQL> select deptno,sal,sum(sal) over()
2 from emp;
不含order by时，默认的窗口是从结果集的第一行直到末尾。
含order by的：
SQL> select deptno,sal,
2 sum(sal) over(order by deptno) as sumsal
3 from emp;
当含有order by时，默认的窗口是从第一行直到当前分组的最后一行。

二、用于排列的函数
SQL> select empno, deptno, sal,
2 rank() over
3 (partition by deptno order by sal desc nulls last) as rank,
4 dense_rank() over
5 (partition by deptno order by sal desc nulls last) as dense_rank,
6 row_number() over
7 (partition by deptno order by sal desc nulls last) as row_number
8 from emp;

三、用于合计的函数

SQL> select deptno,sal,
2 sum(sal) over (partition by deptno) as sumsal,
3 avg(sal) over (partition by deptno) as avgsal,
4 count(*) over (partition by deptno) as count,
5 max(sal) over (partition by deptno) as maxsal
6 from emp;

四、开窗语句

1、rows窗口: “rows 5 preceding”

适用于任何类型而且可以order by多列。

SQL> select deptno,ename,sal,
2 sum(sal) over (order by deptno rows 2 preceding) sumsal
3 from emp;

rows 2 preceding:将当前行和它前面的两行划为一个窗口，因此sum函数就作用在这三行上面
SQL> select deptno,ename,sal,
2 sum(sal) over
3 (partition by deptno order by ename rows 2 preceding) sumsal
4 from emp
5 order by deptno,ename;
加了partiton by分区后之后，rows 2 preceding(窗口)只在当前分区内生效，不会影响分区之外的行。
SQL> select ename,sal,hiredate,
2 first_value(ename)over
3 (order by hiredate asc rows 5 preceding) first_ename,
4 first_value(hiredate) over
5 (order by hiredate asc rows 5 preceding) first_hiredate
6 from emp
7 order by hiredate asc;
order by hiredate asc rows 5 preceding: order by之后，取当前行的前5行+当前行作为窗口(共6行)。
2、”range unbounded preceding”

range unbounded preceding会把当前行之前的所有行都包含进来，但当partition by时:

SQL> select deptno,ename,sal,
2 sum(sal) over
3 (partition by deptno order by deptno range unbounded preceding) sumsal
4 from emp;
SQL> select deptno,ename,sal,
2 sum(sal) over
3 (order by deptno range unbounded preceding) sumsal
4 from emp;
这SQL句子和下面这SQL是等价的:

select deptno,ename,sal,
sum(sal) over (order by deptno) sumsal
from emp;

因为order by的默认窗口总是从结果集的第一行开始到它分组的最后一行。
而partiton by的默认窗口总是从分区的第一行开始。
3、range窗口: “range 100 preceding”

这个子句只适用于number和date，而且只能order by一列。
如果over()里asc排列，意思是[number-100,number]这样一个闭区间是它的窗口。
如果over()里desc排列，意思是[number,number+100]这样一个闭区间是它的窗口。
4、窗口总结

1、unbounded preceding:从当前分区的第一行开始，到当前行结束。
2、current row:从当前行开始，也结束于当前行。
3、[numeric expression] preceding:对于rows来说从当前行之前的第[numeric expression]行开始，到当前行结束。对range来说从小于数值
表达式的值开始，到当前行结束。
4、[numeric expression] following:与[numeric expression] preceding相反。

[ Last edited by DQ on 2005-3-4 at 14:15 ]

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
ORACLE创建、修改计划任务(2010-08-13 18:09)转载标签：杂谈分类：技术
begin
sys.dbms_job.submit(job => :job,
what => ‘calculate_tag_score_smsbill;’,–分号必须加
next_date => to_date(‘13-08-2010 11:26:13’, ‘dd-mm-yyyy hh24:mi:ss’), –开始执行时间
interval => ‘sysdate+1/8640’); –字符型，执行间隔，这里是10秒一次
commit;
end;

begin
sys.dbms_job.change(job => 81,
what => ‘stat_user_tag;’,
next_date => to_date(‘12-08-2010 09:59:17’, ‘dd-mm-yyyy hh24:mi:ss’),
interval => ‘sysdate+1’);
commit;
end;

*****************************************************************************8

*****************************************************************************8
oracle JOB 的创建列子
创建
variable job_feeid_create NUMBER;
begin
sys.dbms_job.submit(:job_feeid_create,’wlk_check_fee;’,sysdate,’TRUNC(SYSDATE+1)’);
commit;
end;
在命令控制台中这样就可以建产一个作业了。sysdate表示当前作业将来执行的时间，sysdate+1表示调用频率。
描述 INTERVAL参数值
每天午夜12点 ‘TRUNC(SYSDATE + 1)’
每天早上8点30分 ‘TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)’
每星期二中午12点 ‘NEXT_DAY(TRUNC(SYSDATE ), ”TUESDAY” ) + 12/24’
每个月第一天的午夜12点 ‘TRUNC(LAST_DAY(SYSDATE ) + 1)’
每个季度最后一天的晚上11点 ‘TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), ‘Q’ ) -1/24’
每星期六和日早上6点10分 ‘TRUNC(LEAST(NEXT_DAY(SYSDATE, ”SATURDAY”), NEXT_DAY(SYSDATE, “SUNDAY”))) + （6×60+10）/（24×60）’

运行

begin
dbms_job.run(:job_feeid_create);
end;

查询作业记录
select * from user_jobs

具体讲解：
1．确保Oracle的工作模式允许启动任务队列管理器
Oracle定时执行“Job Queue”的后台程序是SNP进程，而要启动SNP进程，首先要确保整个系统的模式是可以启动SNP进程的，这需要以 DBA的身份去执行如下命令：
svrmgrl>; alter system enable restricted session;
或sql>; alter system disenable restricted session;
利用如上命令更改系统的会话方式为disenable restricted，为SNP的启动创造条件。

  2．确保Oracle的系统已经配置了任务队列管理器的启动参数 
        SNP的启动参数位于Oracle的初始化文件中，该文件放在＄ORACLE_HOME/dbs路径下，如果Oracle的SID是myora8的话，则初始化文件就是   initmyora8.ora，在文件中对SNP启动参数的描述部分如下： 
        job_queue_process=n 
        job_queue_interval=N 
        第一行定义SNP进程的启动个数为n。系统缺省值为0，正常定义范围为0～36，根据任务的多少，可以配置不同的数值。 
        第二行定义系统每隔N秒唤醒该进程一次。系统缺省值为60秒，正常范围为1～3600秒。事实上，该进程执行完当前任务后，就进入睡眠状 态，睡眠一段时间后，由系统的总控负责将其唤醒。 
        如果该文件中没有上面两行，请按照如上配置添加。配置完成后，需要重新启动数据库，使其生效。注意：如果任务要求执行的间隔很短的 话，N的配置也要相应地小一点。 
   3．将任务加入到数据库的任务队列中 
          用Oracle的dbms_job包中的存储过程，将任务加入到任务队列中：

dbms_job.submit( job out binary_integer,
what　　　　　　　in　　　archar2,
next_date　　　　in　　　date，
interval　　　　　in　　　varchar2,
no_parse　　　　　in　　　boolean)

其中：
●job：输出变量，是此任务在任务队列中的编号；
●what：执行的任务的名称及其输入参数；
●next_date：任务执行的时间；
●interval：任务执行的时间间隔。
下面详细讨论一下dbms_job.submit中的参数interval。严格地讲，interval是指上一次执行结束到下一次开始执行的时间间隔，当interval设置为null 时，该job执行结束后，就被从队列中删除。假如我们需要该job周期性地执行，则要用‘sysdate＋m’表示。
将任务加入到任务队列之前，要确定执行任务的数据库用户，若用户是scott, 则需要确保该用户拥有执行包dbms_job的权限；若没有，需要以 DBA的身份将权利授予scott用户：
svrmgrl>; grant execute on dbms_job to scott;

 4．将要执行的任务写成存储过程或其他的数据库可执行的pl/sql程序段 

        例如，我们已经建立了一个存储过程，其名称为my_job，在sql/plus中以scott用户身份登录，执行如下命令：

sql>; variable n number;
sql>; begin
dbms_job.submit(:n‘my_job;’,sysdate,
‘sysdate＋1/360’);
commit;
end;
/
系统提示执行成功。
Sql>; print :n;
系统打印此任务的编号，例如结果为300。
如上，我们创建了一个每隔4分钟执行一次的任务号为300的任务。可以通过Oracle提供的数据字典user_jobs察看该任务的执行情况：

sql>; select job,next_date,next_sec,failures,broken from user_jobs;
执行结果如下：
job next_date next_sec failures broken
300 2000/10/10 11:45:15 0 N

     这表示任务号为300的任务，下一次将在2000/10/10 11:45:15执行，此任务的执行失败记录为0次。注意：当执行job出现错误时，Oracle将其记录在日志里，失败次数每次自动加1。当执行失败次数达到16时，Oracle就将该job标志为broken。此后，Oracle不再继续执行它，直到用户调用过程dbms_job.broken，重新设置为not broken，或强制调用dbms_job.run来重新执行它。 
     除了以上我们讨论的submit存储过程之外，Oracle还提供了其他许多存储过程来操作任务。例如：dbms_job.change 、 dbms_job.what、dbms_job.interval可以用来修改提交的任务。要想删除该任务，只需运行dbms_job.remove(n)即可，其中n为任务号。

下方为实例2：

创建oracle job
oracle job简介
主要的使用情景
定时在后台执行相关操作:如每天晚上0点将一张表的数据保存到另一张表中,2：定时备份数据库等

熟化说万事开头难，这里我只简单记述一个创建一个简单的job
步骤如下：
1、创建一张表g_test
create table G_TEST
(
ID NUMBER(12),
C_DATE DATE
)
2、创建一个sequence
create sequence G_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 141
increment by 1
cache 20;

3、创建一个存储过程
create or replace procedure prc_g_test is
begin
insert into g_test values(g_seq.nextval,sysdate);
end prc_g_test;

4、创建job,
使用Submit()过程，工作被正常地计划好。
这个过程有五个参数：job、what、next_date、interval与no_parse。

PROCEDURE Submit ( job OUT binary_ineger,
What IN varchar2,
next_date IN date,
interval IN varchar2,
no_parse IN booean:=FALSE)

job参数是由Submit()过程返回的binary_ineger。这个值用来唯一标识一个工作。
what参数是将被执行的PL/SQL代码块。
next_date参数指识何时将运行这个工作。
interval参数何时这个工作将被重执行。
no_parse参数指示此工作在提交时或执行时是否应进行语法分析——TRUE
指示此PL/SQL代码在它第一次执行时应进行语法分析，
而FALSE指示本PL/SQL代码应立即进行语法分析。

在command window窗口中执行下面脚本
variable job1 number;
begin
sys.dbms_job.submit(job => :job,
what => ‘prc_g_test;’,
next_date => to_date(‘22-10-2008 10:06:41’, ‘dd-mm-yyyy hh24:mi:ss’),
interval => ‘sysdate+1/1440’);–每天1440分钟，即一分钟运行test过程一次
commit;
end;
/

在plSQL中我的做法是：

declare
job number;
begin
sys.dbms_job.submit(job,’prc_g_test;’,sysdate,’sysdate+1/1440’);
end;

删除job：
exec dbms_job.remove(jobno);

5、查看创建的job
查看相关job信息
1、相关视图
dba_jobs
all_jobs
user_jobs
dba_jobs_running 包含正在运行job相关信息。
如：
select * from dba_jobs

6、运行JOB
说明：Run()过程用来立即执行一个指定的工作。这个过程只接收一个参数：
SQL> begin
2 dbms_job.run(:job);
3 end;
4 /

在plSQL中我的做法是：
begin
dbms_job.run(3017);

end;

7、删除JOB
SQL> begin
2 dbms_job.remove(:job);–:job可以用dba_jobs.job的值代替如：1198
3 end;
4 /

说明：在sqlplus命令行直接定义变量要variable！！！！

*****************************************************************************8
使用dbms_output.putline();
使用set serveroutput on

*****************************************************************************8
ORACLE默认创建2个用户 SYS，密码：change_on_install
SYSTEM，密码：manager
一般登陆时可以输入：D:>sqlplus “/as sysdba”
或：D:>sqlplus user/password

在以SYSDBA身份登陆时可以修改其他用户的密码，比如：
SQL> alter user user01 identified by user10;
用户已更改。
这个是把USER01用户密码修改为USER10

*****************************************************************************8
oracle中查看用户权限
1.查看所有用户：
select * from dba_users;
select * from all_users;
select * from user_users;

2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
select * from dba_sys_privs;
select * from user_sys_privs;

3.查看角色(只能查看登陆用户拥有的角色)所包含的权限

sql>select * from role_sys_privs;

4.查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;

5.查看所有角色：
select * from dba_roles;

6.查看用户或角色所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;

7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)

select * from V$PWFILE_USERS

注意：

1、以下语句可以查看Oracle提供的系统权限

select name from sys.system_privilege_map

2、查看一个用户的所有系统权限(包含角色的系统权限)

Sql代码
select privilege from dba_sys_privs where grantee=’IPSBR’
union
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee=’IPSBR’ );

select privilege from dba_sys_privs where grantee=’IPSBR’
union
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee=’IPSBR’ );

session

*****************************************************************************8
Oracle 数据库用户(user)的创建、删除以及授予权限

———–Oracle 数据库用户创建学习

–创建用户，如同在网吧开机子一样，保护自身系统的安全性的同时，有简化了自己的操作（系统用户有很多繁杂的表）
–默认用户
/*
用户名密码
sys 安装时确认
system manager
scott tiger
注：Oracle10g 后的版本sys和system都是安装时设定的，而scott是默认锁定的
*/
create user user_name identified by password;
–详解:
/*
创建一个用户名称为user_name，密码为password的用户；
注：密码必须由字母开头
如果以数字或其他符号开头会出现错误：ORA-00988: 口令缺失或无效
如果以下划线“_”开头会出现错误：ORA-00911: 无效字符

drop user user_name [cascade];
–详解
/*
drop user user_name;
仅仅删除用户
drop user user_name cascade;
删除用户的同时，删除此用户名下所有的
使用cascade参数可以删除该用户的全部objects。
1、如果用户的schema中有表，则在删除表的时候自动删除与该表相关的主键和外键。
2、如果用户的schema中有表，则在删除表的时候自动删除与该表相关的索引。
3、删除用户时，下列在其他用户中的objects不会被删除，只会被置为无效
4、其他用户建立的基于被删除用户的物化视图不会被删除，只是不能在刷新了。
5、用户模式下的所有触发器全部被删除
6、被删除用户建立的其他用户不会被删除
*/

–赋予用户权限（c初始用户没有权限，连进入该用户都不可能）
grant connect,resource,dba to user_name;
–一般授予以上权限就可以了
–1、三个常用的系统权限的赋予语句：
–格式：grant 系统权限 to { public | role | username };
grant create session to lisi;
–创建会话权限，可以进入用户进行相关数据库操作
grant create table to lisi;
–创建表的权限
grant unlimited tablespace to lisi;
–使用表空间的权限
grant create session to public;
–对所有用户授予创建会话权限，public代表所有用户
–删除以上权限，不做解释
revoke create session from lisi;
revoke create table from lisi;
revoke unlimited tablespace from lisi;
–2、创建常用的对象权限

–示例
–创建用户，并制定表空间
create user username identified by password
default tablespace test_data
temporary tablespace test_temp;
–给用户授予权限
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;

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/Kilper/archive/2010/08/07/5795482.aspx

*****************************************************************************8
Oracle 转义字符
转义字符 &
在plsql里边执行:
update userinfo set pageurl=’myjsp?page=1&pagesize=10’ where id=’test’
这条sql语句往数据库的pageurl字段放进去了一个url地址，但是执行的时候却并非那么理想，因为这其中有一个oracle的特殊字符，需要进行转义，那就是字符’&’.

怎么处理上例中的特殊字符？
两个办法：
1) update userinfo set pageurl=’myjsp?page=1’||’&’||’pagesize=10’ where id=’test’
2) update userinfo set pageurl=’myjsp?page=1’||chr(38)||’pagesize=10’ where id=’test’

其中||是连字符, chr(38)跟ASCII的字符转码是一致的。
plsql中还可以set define off来关闭特殊字符，还可以用show define来查看有些特殊定义的字符。

转义字符 %

处理手法如&

转义字符 ’ 单引号在PL/SQL 里两个单引号等于一个单引号

双引号 = chr(34)

*****************************************************************************8
查询Oracle中某些表或者某张表的信息（备注、主键、owner等）2009年02月13日星期五 11:38查询某张或者某些表的整体信息：

/first/
select distinct a.table_name,
b.comments,
A.OWNER,
a.tablespace_name,
null as is_used
from all_tables a, all_tab_comments b
where a.table_name = b.table_name
and a.table_name like ‘%YOUR_TABLE_SPECIAL_PART%’
and a.tablespace_name = ‘YOUR_TABSPACE_NAME’

/second/
select distinct a.table_name,
b.comments,
c.constraint_name,
d.column_name,
A.OWNER,
a.tablespace_name,
null as is_used
from all_tables a,
all_tab_comments b,
all_constraints c,
all_cons_columns d
where a.table_name = b.table_name
and b.table_name = c.table_name
and c.table_name = d.table_name and c.constraint_name = d.constraint_name
and a.table_name like ‘%%YOUR_TABLE_SPECIAL_PART%%’ and c.constraint_type = ‘P’
and a.tablespace_name = ‘YOUR_TABSPACE_NAME’

*****************************************************************************8

根据表明，查询列名以及字段数量
最近正在研究JVM，发现函数参数的索引下标是Byte类型，这样不是只能写255个参数吗？
试了一下，写了一个有300个参数的函数，果然编译不通过。显示参数过多。
但是印象中没有在任何一本书中看到参数个数有限制啊！

*****************************************************************************8

Error: ORA-02069: global_names parameter must be set to TRUE for this operation
Cause:
You tried to execute an operation that requires a remote mapping. This can not be done because the parameter called GLOBAL_NAMES is not set to TRUE.

Action:
The options to resolve this Oracle error are:

Try setting the GLOBAL_NAMES parameter to TRUE with the following statement:

Code:

ALTER SESSION SET GLOBAL_NAMES = TRUE;

*****************************************************************************8
请教怎么修改 Global_names 的参数值为 TRUE

SQL> show parameter global_name;

NAME TYPE VALUE

global_names boolean FALSE
SQL> alter system set global_names=true scope=both;
alter system set global_names=true scope=both
*
ERROR 位于第 1 行:
ORA-32001: 已请求写入 SPFILE, 但是在启动时未指定 SPFILE

SQL> conn system/manager@portal
已连接。
SQL> alter system set global_names=true scope=both;
alter system set global_names=true scope=both
*
ERROR 位于第 1 行:
ORA-32001: 已请求写入 SPFILE, 但是在启动时未指定 SPFILE

SQL> alert system set global_name = true scope = both;
SP2-0734: 未知的命令开头 “alert syst…” - 忽略了剩余的行。
SQL> alter system set open_links=4 scope=both;
alter system set open_links=4 scope=both
*
ERROR 位于第 1 行:
ORA-02095: 无法修改指定的初始化参数

SQL> alter system set job_queue_processes=10 scope=both;
alter system set job_queue_processes=10 scope=both
*
ERROR 位于第 1 行:
ORA-32001: 已请求写入 SPFILE, 但是在启动时未指定 SPFILE

SQL>SHOW PARAMETER SPFILE
没有的话是指定了IFILE了
SQL>SHOW PARAMETER IFILE(这个参数里应该有内容的）

在INIT.ORA里曾加一行
GLOBAL_NAMES=TRUE

THEN RESTART DATABASE

你没有使用spfile，而这个参数不能动态修改的，所以你只能在你的init.ora文件中修改这个参数，要重启动database的

*****************************************************************************8
ORA-02069:解决方法
今天遇到了ORA-02069: global_names parameter must be set to TRUE for this operation

查了METALINK原因是不能在通过DBLINK操作远程表的DML语句中使用本地函数

原文如下：

Because of a limitation, it is not possible to use a local function when doing a dml operation on a remote tableITPUB个人空间w zxR/^#t^
When this is attempted, the ora-2069 is raised.

解决方法

1、使用 global_names=true个人觉得麻烦还要修改DBLINK名

2、把函数放到远端数据库去，这个比较可行。

*****************************************************************************8
创建DB_link
create database link SSS connect to dcf IDENTIFiED BY dcfl123 USING ‘ds392’
SSS: db_link 的名字
dcf: 用户名
dcf123: 密码
ds392：为TNS配置的名称

*****************************************************************************8

*****************************************************************************8
plsql 默认字体：
main Fount： Courier New 常规 10
藏青色西方

*****************************************************************************8
sbSql.append(” decode(‘”+bcm1103t.getJno()+”’,’null’,”,”,”,’”+bcm1103t.getJno()+”’), \n”);

*****************************************************************************8
oracle字符替换
文章分类:数据库

select REGEXP_REPLACE( ‘ABC5DD6D ‘, ‘5|6’, ‘x’) x from dual;

SELECT COUNT(*) FROM dual WHERE REGEXP_LIKE(‘abcdef’,’a|x’)

Oracle提供了一个字符替换函数translate，不同于replace函数的是，translate函数是字符级别的替换，而不是字符串的替换。

其语法如下：

TRANSLATE ( expr , from_string , to_string )

简单的说就是对expr内容，用to_string中的字符逐一替换from_string 中的字符，举例说明如下：

SQL> select translate(‘123456789’,’456’,’abc’) from dual;

TRANSLATE

123abc789

SQL> select translate(‘123456789’,’456’,’ab’) from dual;

TRANSLAT

123ab789

SQL> select translate(‘123456789’,’4564’,’a’) from dual;

TRANSLAT

123a789

SQL> select translate(‘123456789’,’4564’,’abcd’) from dual;

TRANSLATE

123abc789

可以看到translate函数是以字符为单位逐一替换的，从第二、三个例子可以看出，如果第三个参数to_string的长度小于第二个参数 from_string，那么from_string的后边超出的部分都被替换为空值。从第三、四个例子可见，如果某一个字符多次出现，则以第一次替换的内容为准。

以此可以实现的一个有用的功能是统计一个字符串中某个字符出现的次数：

select length(translate(‘expl’,’x’||’expl’,’x’)) from dual

官方文档10g Release 2 (10.2) B14200-02 提供了一个例子如下：

Examples

The following statement translates a book title into a string that could be used (for example) as a filename. The from_string contains four characters: a space, asterisk, slash, and apostrophe (with an extra apostrophe as the escape character). The to_string contains only three underscores. This leaves the fourth character in the from_string without a corresponding replacement, so apostrophes are dropped from the returned value.

SELECT TRANSLATE(‘SQLPlus User”s Guide’, ’ /”’, ‘_‘) FROM DUAL;

SQL_Plus_Users_Guide

字符串别的替换相当简单：
select replace(‘/jsp/spms/sampleplan/samplePlanMng.jsp’,’spms’,’cpfr’) from dual;

/jsp/spms/sampleplan/samplePlanMng.jsp =》
/jsp/cpfr/sampleplan/samplePlanMng.jsp

UPDATE sys_menu_inf t
SET t.menu_url = REPLACE(t.menu_url,’spms’, ‘cpfr’)
*****************************************************************************8

Oracle DBA常用SQL
一、查询数据库配置

查询数据库名字、创建日期
select name,created,log_mode,open_mode from v$database

查询主机名、实例名及版本
select host_name,instance_name,version from v$instance

查看详细版本信息
select * from v$version

查看控制文件
select * from v$controlfile

查看重做日志配置信息
select group#,members,bytes,status,archived from v$log

查看重做日志（成员）文件存放的具体位置
select * from v$logfile

查看数据库备份和恢复策略及归档文件位置
archive log list
执行此语句需sysdba权限

查询表空间情况
select tablespace_name,　block_size,　status, contents, logging from dba_tablespaces

查询表空间存储文件
select file_id, file_name, tablespace_name, status, bytes from dba_data_files

查询用户情况
select username,created from dba_users

二、与重做日志相关的命令

查看重做日志组信息：
select * from v $log 查看重做日志组成员信息： select * from v$ logfile

重做日志切换命令
alter system switch logfile

创建新的日志组
alter database add logfile (‘c:\1.log’,’d:\3.log’);
删除日志组
alter database drop logfile group 组号

添加日志组成员
alter database add logfile member ‘c:\redo01b.log’ to group 1,’d:\redo02b.log’ to group 2
删除日志组成员
alter database drop logfile member ‘c:\redo01b.log’

初始化联机重做日志
alter database clear [unarchived] logfile group 组号
若崩溃的重做日志已不能归档，可以使用unarchived关键字避免对它们进行归档。

3、其它常见问题
查看表占用的空间：
select owner,table_name,
NUM_ROWS,
BLOCKS*001/1024/1024 “Size M”,
EMPTY_BLOCKS,
LAST_ANALYZED
from dba_tables
where table_name=’XXX’;
Here: 001 is the value of db_block_size ;
XXX is the table name you want to check

查看系统允许的最大会话数：
select * from v$parameter where name like ‘proc%’;
或者：
show parameter processes

查看曾经到达的最大会话数记录(SESSIONS_HIGHWATER)
select * from v$license;

请问如何分辨某个用户是从哪台机器登陆ORACLE的?
SELECT machine , terminal FROM V$SESSION;

查看系统的数据字典
select * from dictionary

查看数据字典的字段备注
select column_name,comments from dict_columns where table_name=’XXXX’;
备注内容不是很全，但总比没有强

查看系统被锁的事务
select * from v$locked_object;

获取正在使用数据库的用户
select username from v$session;

查询用户系统权限
select * from dba_sys_privs where grantee=’HR’

修改表使用的表空间
alter table YOUT_TABLE_NAME move NEW_TABLESPACE_NAME;

修改索引使用的表空间
alter index YOUR_INDEX_NAME rebuild tablespace NEW_TABLESPACE_NAME;

如何解锁?
ALTER SYSTEM KILL SESSION ‘SID,SERIR#’;

Oracle产生随机数的函数
dbms_random.random

查看数据库字符状况?
SELECT * FROM NLS_DATABASE_PARAMETERS;
SELECT * FROM V$NLS_PARAMETERS;

查询表空间信息?
SELECT * FROM DBA_DATA_FILES;

如何给表、列加注释？
comment on table 表 is ‘表注释’;
comment on column 表.列 is ‘列注释’;

如何在字符串里加回车
select ‘我的博客：’||chr(10)||’wallimn.javaeye.com’ from dual

怎幺可以快速做一个和原表一样的备份表?
create table new_table as (select * from old_table);

怎幺在sqlplus下修改procedure?
select line,trim(text) t from user_source
where name =’A’ order by line;

EXP 如何加query参数？
EXP USER/PWD FILE=A.DMP TABLES(BSEMPMS)
QUERY=‘“WHERE EMP_NO=\’S09394\’\”;

如果修改表名?
alter table old_table_name rename to new_table_name;

*****************************************************************************8

相当于alias(别名),比如把user1.table1在user2中建一个同义词table1
create synonym table1 for user1.table1;
这样当你在user2中查select * from table1时就相当于查select * from user1.table1;
优点自己总结吧。

例如：
在oracle中对用户的管理是使用权限的方式来管理的，
也就是说，如果我们想使用数据库，我们就必须得有权限，
但是如果是别人将权限授予了我们，我们也是能对数据库进行操作的，
但是我们必须要已授权的表的名称前键入该表所有者的名称，所以这就是比较麻烦的，
遇到这种情况，我们该怎么办呢？创建个同义词吧！这样我们就可以直接使用同义词来使用表了。

*****************************************************************************8
同义词可分为两种：1）公有同义词。2）私有同义词。
create public synonym 同义词名称 for 数据库对象名;—-公有同义词
create synonym 同义词名称 from 数据库对象;—–私有同义词

*****************************************************************************8
创建了dblink以后就能直接用了，而不必再去创建同义词了，使用方法如下:

select * from pub_employee@连接名

如果查不到数据或者报错的话，那么肯定是数据库链接创建的还有问题了

*****************************************************************************8
Oracle的同义词(2008-09-08 11:41:06)转载标签：杂谈分类： Oracle
Oracle中创建同义词语句：
create synonym table_name for user.table_name;
其中第一个user_table和第二个user_table可以不一样。
此外如果要创建一个远程的数据库上的某张表的同义词，需要先创建一个Database Link（数据库连接）来扩展访问，然后在使用如下语句创建数据库同义词：create synonym table_name for table_name@DB_Link;

当然，你可能需要在user用户中给当前用户(user2)授权： grant select/delete/update on user2

创建好同义词之后，你可以对它进行DML（insert，update，delete）操作，当然select语句时肯定支持的。删除同义词：drop synonym table_name;这里的table_name是指同义词的表名。

同义词拥有如下好处：节省大量的数据库空间，对不同用户的操作同一张表没有多少差别；扩展的数据库的使用范围，能够在不同的数据库用户之间实现无缝交互；同义词可以创建在不同一个数据库服务器上，通过网络实现连接。

待研究：不同用户对通一张表操作时，使用锁对多用户的影响，能否使用锁，事务进行操作

查看所有同义词：

　　select * from dba_synonyms

在oracle中创建同义词
2008年01月26日星期六 20:52

我们都知道，在oracle中对用户的管理是使用权限的方式来管理的，也就是说，如果我们想使用数据库，我们就必须得有权限，但是如果是别人将权限授予了我们，我们也是能对数据库进行操作的，但是我们必须要已授权的表的名称前键入该表所有者的名称，所以这就是比较麻烦的，遇到这种情况，我们该怎么办呢？创建个同义词吧！这样我们就可以直接使用同义词来使用表了。

创建同义词的具体语法是：create [public] SYNONYM synooym for object;其中synooym表示要创建的同义词的名称，object表示表，视图，序列等我们要创建同义词的对象的名称。

CREATE PUBLIC SYNONYM public_emp FOR jward.emp; – jward 用户名

删除同义词
使用DROP SYNONYM语句删除不再需要的同义词要想删除私有同义词就省略 PUBLIC 关键字要想删除公共同义词就要包括PUBLIC 关键字

DROP SYNONYM emp; –删除名为emp 的私有同义词
DROP PUBLIC SYNONYM public_emp; –删除名为public_emp的公有同义词

Oracle同义词建立方法

作者: ckernel 发布日期: 2006-10-19 查看数:
问题描述：
有两个oralce数据库
SID分别为 A B
A中a用户下含有表objects
B中含有b用户
使在B中用b用户登录后执行
select * from objects
能显示出A中a.objects的全部内容

问题解答：
用b用户登录到B中，执行：
create public synonym objects for a.objects@A
此时执行select * from objects 就能显示出a.objects的全部内容

需要注意的一点：
B中不能含有table b.objects否则语句可以执行，但是执行select操作时显示的仍然为b.objects的内容，
需要先把b.objects删除掉，重新建立synonym即可。

*****************************************************************************8

 <statement name="cudMessageKo">

MERGE INTO MESSAGE_KO A
USING (SELECT ${message}  AS MESSAGE,
              ${code}     AS CODE
         FROM DUAL) B
ON (A.CODE = B.CODE)
WHEN MATCHED THEN
   UPDATE
   SET MESSAGE = B.MESSAGE
WHEN NOT MATCHED THEN
  INSERT(
          CODE,
          MESSAGE
        )
  VALUES(
          B.CODE,
          B.MESSAGE
        )

参考Oracle 的SQL Reference，可以看到Merge Statement的语法如下：
MERGE [hint] INTO [schema .] table [t_alias]
USING [schema .] { table | view | subquery } [t_alias]
ON ( condition )
WHEN MATCHED THEN
merge_update_clause
WHEN NOT MATCHED THEN
merge_insert_clause;

作用：merge into 解决用B表跟新A表数据，如果A表中没有，则把B表的数据插入A表；当处理大数据量是，该方法的效率很高。
语法：
MERGE INTO [your table-name] [rename your table here]
USING ( [write your query here] )[rename your query-sql and using just like a table]
ON ([conditional expression here] AND […]…)
WHEN MATHED THEN [here you can execute some update sql or something else ]
WHEN NOT MATHED THEN [execute something else here ! ]

实例：

例如：SQL>
merge into acct a
using subs b
on (a.msid = b.msid)
when MATCHED then
update set a.areacode = b.areacode
when NOT MATCHED then
insert
(msid, bill_month, areacode)
values
(b.msid, ‘200702’, b.areacode);

*****************************************************************************8
SELECT t.approver_id,
a.user_name approver_nm,
t.appr_row_no appr_row_no
FROM cdms_appr_dtl t,
sys_user_inf a
WHERE t.doc_app_no = ${docAppNo} AND t.appr_row_no = (SELECT MAX(t.appr_row_no) + 1 next_row_no FROM cdms_appr_dtl t WHERE t.doc_app_no =$ {docAppNo}
AND t.approver_id = ${approverId})
AND t.approver_id = a.user_id(+)

*****************************************************************************8
Oracle的instr函数
文章分类:数据库
1.instr

在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。

语法如下：instr( string1, string2 [, start_position [, nth_appearance ] ] )

string1 源字符串，要在此字符串中查找。

string2 要在string1中查找的字符串.

start_position 代表string1 的哪个位置开始查找。此参数可选，如果省略默认为1. 字符串索引从1开始。如果此参数为正，从左到右开始检索，如果此参数为负，从右到左检索，返回要查找的字符串在源字符串中的开始索引。

nth_appearance 代表要查找第几次出现的string2. 此参数可选，如果省略，默认为 1.如果为负数系统会报错。

注意：

如果String2在String1中没有找到，instr函数返回0.

应用于：

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g
举例说明：

select instr(‘abc’,’a’) from dual; – 返回 1
select instr(‘abc’,’bc’) from dual; – 返回 2
select instr(‘abc abc’,’a’,1,2) from dual; – 返回 5
select instr(‘abc’,’bc’,-1,1) from dual; – 返回 2
select instr(‘abc’,’d’) from dual; – 返回 0

注：也可利用此函数来检查String1中是否包含String2，如果返回0表示不包含，否则表示包含。

*****************************************************************************8
oracle截取字符串

select k(字段,1,INSTR(字段,’/’)-1)
from dual

例如
select Substr(‘sdhfuiasdh/asdfsadf’,1,INSTR(‘sdhfuiasdh/asdfsadf’,’/’)-1)
from dual

执行
SELECT CASE
WHEN INSTR(WH_CD, ‘+’, 1, 1) > 0
THEN SUBSTR(WH_CD, 1, INSTR(WH_CD, ‘+’, 1, 1) - 1)
ELSE WH_CD
END AS WH_CD
FROM T_RCV_ISSUE

结果：MR-PS，MR-WS,MR-WS,MR-PS

select t.model_suffix,
substr(t.model_suffix, 1, INSTR(t.model_suffix, ‘.’) - 1) model,
substr(t.model_suffix,
INSTR(t.model_suffix, ‘.’) + 1,
length(t.model_suffix)) suffix
from aaa_test2 t;

*****************************************************************************8
CREATE OR REPLACE VIEW V_CDMS_MASTER_SNSHOP AS
SELECT SNSHOP.SHOP_CD,
SNSHOP.SHOP_DESC,
SNBRANCH.BRANCH_CD SN_BRANCH_CD,
SNBRANCH.BRANCH_NM SN_BRANCH_NM,
SNDIV.CD_CODE SN_DIV_CD,
SNDIV.DESC_LOC SN_DIV_NM,
SHIP.CUST_CD SHIP_TO,
SHIP.SHORT_NAME SHIP_TO_NM,
SHIP.FULL_NAME SHIP_TO_FULLNM,
BILL.CUST_CD BILL_TO,
BILL.SHORT_NAME BILL_TO_NM,
BILL.FULL_NAME BILL_TO_FULLNM,
DEPT.DIV_CD,
DEPT.DEPT_CD,
DEPT.DEPT_NAME,
SNSHOP.PARENT_DC
FROM CDMS_BRANCH_SN SNBRANCH,
CDMS_SHOP_SN SNSHOP,
CDMS_CCUST SHIP,
CDMS_CCUST BILL,
CDMS_COMM_CD SNDIV,
CDMS_DEPARTMENT DEPT
WHERE SNBRANCH.BRANCH_CD = SNSHOP.BRANCH_CD
AND SNSHOP.SHIP_TO = SHIP.CUST_CD
AND SHIP.CUST_CD = SHIP.SHIP_TO
AND BILL.CUST_CD = BILL.BILL_TO
AND SHIP.BILL_TO = BILL.CUST_CD
AND SNDIV.CD_CLASS = ‘SN_DIV’
AND SNDIV.CD_CODE = SNBRANCH.BRANCH_DIV_CD
AND SHIP.DEPT_CD = DEPT.DEPT_CD
AND NVL(SNSHOP.USE_FLAG,’Y’) = ‘Y’;

*****************************************************************************8

ORA-01691 unable to extend lob segment
表空间不够，需要扩容

*****************************************************************************8

创建同义词步骤：
想要在cdms用户下，使用Lgespm用户下的表

01.首先在需要真实查询的用户Lgespm下的数据库为待查询的CDMS数据库增加权限

– Grant/Revoke object privileges
grant select on SPMS_PRPS_MASTER to CDMS;

在待查询的数据库增加同义词
– Create the synonym
create or replace synonym CDMS_PRPS_MASTER
for LGESPM.SPMS_PRPS_MASTER;

*****************************************************************************8
SQL 按月统计(2009-08-12 23:32:40)转载标签： sql按月统计oracleit 分类： Oracle
前提：
created_date 是表 A 的一个日期字段
1、按月分组统计 (1\2\3\4等自然月统计)
select sum(quantity) from A group by trunc(created_date，‘mm’);
用 trunc 函数将日期转化为每个月的第一天（或者说是忽略月（mm）后面得日期）；

2、如果需求是要按每个月的25号来划分的话（实际经常碰到）
select sum(quantity) from A
group by trunc(created_date+(add_months(created_date,1)-created_date-20),’mm’)；
20 的作用是每个月按20号分割月之间如果是按每个月的15号分割
那么 20 的位置就是15；
蓝色字体的意思是求得所给日期 created_date 所在月的天数；
减去20 是求得还需要再加上多少天就到下个月了；
*****************************************************************************8
sql 按字段日期拆分
一个表里面的数据有一个

id 开始时间结束时间

1 2009-12-01 11:31:40 2009-12-05 11:31:46
sql 怎么拆分成
1 2009-12-01 11:31:40
1 2009-12-02 11:31:40
1 2009-12-03 11:31:40
1 2009-12-04 11:31:40
1 2009-12-05 11:31:40

回答人的补充 2009-12-15 14:40

declare @Day int,@i int,@BeginTime Datetime,@EndTime Datetime
set @BeginTime=’2009-11-11’ –这里换成你的开始时间
set @EndTime=’2009-11-15’ –这里换成你的结束时间
select @Day=datediff(d,@BeginTime,@EndTime)
set @i=0
while @i<@Day+1
begin
select Dateadd(d,@i,@BeginTime)
set @i = @i +1
end

拆分转置：
原始：’aassa 2222 333’
转置之后：’aassa’
‘2222’
‘333’

SELECT DISTINCT REGEXP_SUBSTR (REPLACE(‘aassa 2222 333’, ’ ‘, ‘,’),’[^,]+’,1,LEVEL) as “token”
FROM DUAL
CONNECT BY REGEXP_SUBSTR (REPLACE(‘aassa 2222 333’, ’ ‘, ‘,’),’[^,]+’,1,LEVEL) IS NOT NULL
order by 1

*****************************************************************************8
SELECT *,
IF(b.id <= HOUR(a.StartTime) OR (b.id > HOUR(a.StartTime) AND b.id < HOUR(a.StopTime))
,CAST(CONCAT(DATE(a.StartTime),’ ‘,b.id + 1,’:00:00’) AS DATETIME)
,IF(b.id = HOUR(a.StopTime),(a.StopTime),CAST(CONCAT(DATE(a.StartTime),’ ‘,b.id,’:00:00’) AS DATETIME))) AS newstop,

   IF(b.id <= HOUR(a.StartTime),a.StartTime,IF(b.id > HOUR(a.StartTime) AND b.id < HOUR(a.StopTime),
   CAST(CONCAT(DATE(a.StartTime),' ',b.id,':00:00') AS DATETIME),IF(b.id = HOUR(a.StopTime),
   CAST(CONCAT(DATE(a.StartTime),' ',b.id,':00:00') AS DATETIME),b.id))) AS newstart

FROM ttf a
LEFT JOIN zz.lsb1 b ON b.id BETWEEN HOUR(a.StartTime)
AND HOUR(a.StopTime)

*****************************************************************************8
One Row：001 2003.10.11 ~ 2004,02.05
===》
N Rows:
001 2003.10
001 2003.11
001 2003.12
001 2004.01
001 2004.02
001 2004.03

年份，期间，查询，行转列
解决方法：
查询日期期间所有日期
oracle:
SELECT to_char(a.everyDay,’YYYYMM’) yyyymm
FROM (
SELECT add_months(to_date(‘200611’,’yyyymm’), LEVEL-1) AS everyDay
FROM dual
CONNECT BY LEVEL <= Months_between(to_date(‘200711’,’yyyymm’) , to_date(‘200611’,’yyyymm’) )+1
)a
;

SELECT TO_CHAR(ADD_MONTHS(TO_DATE(‘201404’, ‘YYYYMM’), ROWNUM - 1),’YYYYMM’) MONTH_ID
FROM DUAL
CONNECT BY ROWNUM <=
months_between(to_date(‘201504’, ‘yyyymm’),to_date(‘201404’, ‘yyyymm’)) + 1
;

SELECT ROWNUM
,TO_CHAR(ADD_MONTHS(TO_DATE( ‘201403’,’YYYYMM’),DECODE(ROWNUM,0,0,-(11-ROWNUM))),’MM’) format_month
,TO_CHAR(ADD_MONTHS(TO_DATE( ‘201403’,’YYYYMM’),DECODE(ROWNUM,0,0,-(11-ROWNUM))),’YYYYMM’) full_format_month
FROM TB_COM_CODE
WHERE ROWNUM < 12
ORDER BY 1
1 1 05 201305
2 2 06 201306
3 3 07 201307
4 4 08 201308
5 5 09 201309
6 6 10 201310
7 7 11 201311
8 8 12 201312
9 9 01 201401
10 10 02 201402
11 11 03 201403
DB2：
WITH n(level, mdate) AS
(SELECT 1, to_char(date(‘2009-01-01’),’YYYY-MM-DD’) as mdate
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT n.level + 1, to_char(ADD_MONTHS(n.mdate,1),’YYYY-MM-DD’)
FROM SYSIBM.SYSDUMMY1 as nplus1, n
WHERE MONTHS_BETWEEN(date(‘2011-11-01’),date(n.mdate))>0
)
SELECT level, to_char(mdate,’YYYYMM’) FROM n;

select 001，substr(日期，‘YYYY.MM’),count(*)
where * = 001 and ***date between ‘2003.10.11’ and 2004.02.05
group by 001，substr(日期，‘YYYY.MM’)

将一行数据根据空格拆分成多行
SELECT TMP.SUBSTITUDE_PART_NO SUBSTITUDE_PART_NO
FROM (SELECT DISTINCT REGEXP_SUBSTR(REPLACE(‘011133005106 011133005106 194712007830 192512018870 R193616000010’, ’ ‘, ‘,’), ‘[^,]+’, 1, LEVEL) AS SUBSTITUDE_PART_NO
FROM DUAL
CONNECT BY REGEXP_SUBSTR(REPLACE(‘011133005106 011133005106 194712007830 192512018870 R193616000010’, ’ ‘, ‘,’), ‘[^,]+’, 1, LEVEL) IS NOT NULL
ORDER BY 1) TMP;

结果：
011133005106
192512018870
194712007830
R193616000010

*****************************************************************************8
年月日：日期期间：
SELECT *
FROM (SELECT TO_DATE(‘20061101’, ‘yyyymmdd’) + LEVEL - 1 AS EVERYDAY
FROM DUAL
CONNECT BY LEVEL <= (TO_DATE(‘20071201’, ‘yyyymmdd’) - TO_DATE(‘20061101’, ‘yyyymmdd’) + 1));

日期期间整月
select * from ( select to_date(‘20061101’,’yyyymmdd’) + level - 1 as everyDay from dual
connect by level <=
(last_day(to_date(‘20061101’,’yyyymmdd’)) - to_date(‘20061101’,’yyyymmdd’) +1));

日期期间
SELECT ROWNUM
,TO_CHAR(ADD_MONTHS(TO_DATE( ‘201403’,’YYYYMM’),DECODE(ROWNUM,0,0,-(11-ROWNUM))),’MM’) format_month
,TO_CHAR(ADD_MONTHS(TO_DATE( ‘201403’,’YYYYMM’),DECODE(ROWNUM,0,0,-(11-ROWNUM))),’YYYYMM’) full_format_month
FROM TB_COM_CODE
WHERE ROWNUM < 12
ORDER BY 1

日期期间：
获取某个月的所有天数
SELECT TO_CHAR(trunc(add_months(last_day(SYSDATE), -1) + 1) + ROWNUM - 1, ‘YYYYMMDD’) dt_id
FROM DUAL
CONNECT BY ROWNUM <= last_day(SYSDATE) - trunc(add_months(last_day(SYSDATE), -1) + 1) + 1;

http://www.baidu.com/s?bs=sql+%C8%D5%C0%FA&f=8&wd=oralce+sql+%C8%D5%C0%FA

*****************************************************************************8
WITH n(level, empid, name) AS
(SELECT 1, empid, name
FROM emp
WHERE name = ‘Goyal’
UNION ALL
SELECT n.level + 1, nplus1.empid, nplus1.name
FROM emp as nplus1, n
WHERE n.empid = nplus1.mgrid)
SELECT level, name FROM n;

WITH n(level, mdate) AS
(SELECT 1, to_char(date(‘2009-01-01’),’YYYY-MM-DD’) as mdate
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT n.level + 1, to_char(ADD_MONTHS(n.mdate,1),’YYYY-MM-DD’)
FROM SYSIBM.SYSDUMMY1 as nplus1, n
WHERE MONTHS_BETWEEN(date(‘2011-11-01’),date(n.mdate))>0
)
SELECT level, to_char(mdate,’YYYYMM’) FROM n;

select to_char(date(‘2008-01-08’),’YYYYMM’) from SYSIBM.SYSDUMMY1;
select date(‘2008-05-08’)-date(‘2008-01-08’) from SYSIBM.SYSDUMMY1;

select MONTHS_BETWEEN(date(‘2011-11-08’),date(‘2009-01-12’)) from SYSIBM.SYSDUMMY1;

select ADD_MONTHS(‘2008-05-08’,5)from SYSIBM.SYSDUMMY1;
select ADD_MONTHS(‘20080508’,5)from SYSIBM.SYSDUMMY1;

*****************************************************************************8
SELECT ‘LG-SU660’ model_name
,MAX(temp3.max_value) AS max_value
,MAX(temp3.sw_confirm) AS sw_confirm
,MAX(temp3.bug_clear) AS bug_clear
FROM (SELECT t.maturity_level_index || ‘(’ || MIN(t.maturity_week) || ‘W)’ AS max_value
,” AS sw_confirm
,” AS bug_clear
FROM svms_mgr.tb_maturity_index t
WHERE t.maturity_level_index =
(SELECT MAX(t.maturity_level_index)
FROM svms_mgr.tb_maturity_index t
WHERE 1 = 1
AND t.model_name = ‘LG-SU660’ AND t.buyer_id = ‘41’ )
AND t.model_name = ‘LG-SU660’ AND t.buyer_id = ‘41’
GROUP BY t.maturity_level_index
UNION ALL
SELECT ” AS max_value
,t.maturity_level_index || ‘(’ || temp2.week || ‘W)’ AS sw_confirm
,” AS bug_clear
FROM svms_mgr.tb_maturity_index t
,(SELECT temp.td_event
,temp.step
,MAX(t2.maturity_week) AS week
FROM (SELECT t.td_event
,MAX(t.td_step) AS step
FROM svms_mgr.tb_maturity_index t
WHERE 1 = 1
AND t.model_name = ‘LG-SU660’ AND t.buyer_id = ‘41’
GROUP BY t.td_event) temp
,svms_mgr.tb_maturity_index t2
WHERE 1 = 1
AND temp.td_event = ‘SW’
AND temp.td_event = t2.td_event
AND t2.td_step = temp.step
GROUP BY temp.td_event
,temp.step) temp2
WHERE temp2.td_event = t.td_event
AND temp2.step = t.td_step
AND temp2.week = t.maturity_week
UNION ALL
SELECT ” AS max_value
,” AS sw_confirm
,’0 (’ || CASE COUNT(*)
WHEN 0 THEN
0
ELSE
MIN(t.maturity_week)
END || ‘W)’ AS bug_clear
FROM svms_mgr.tb_maturity_index t
WHERE 1 = 1
AND t.model_name = ‘LG-SU660’ AND t.buyer_id = ‘41’
AND t.in_flow_total = ‘0’) temp3

*****************************************************************************8
当查询结果为0 ，则也显示一行记录，对应值为零
SELECT ” AS max_value
,” AS sw_confirm
,’0 (’ || CASE COUNT(*)
WHEN 0 THEN
0
ELSE
MIN(t.maturity_week)
END || ‘W)’ AS bug_clear
FROM svms_mgr.tb_maturity_index t
WHERE 1 = 1
AND t.model_name = ‘LG-SU660’ AND t.buyer_id = ‘41’
AND t.in_flow_total = ‘0’

*****************************************************************************8
DEVy_11=CSTMT.E Laptime : 23
DEVy_11={call PKG_OSMS_GEN_KPI.MAIN( ‘201145’
DEVy_11= , ‘10001211’,’admin’
DEVy_11= , ‘sqlType:Types.VARCHAR’
DEVy_11= , ‘sqlType:Types.VARCHAR’
DEVy_11= )}
[FRM_DAO_007]　devonframework.persistent.autodao.procedure.LStandardStoredProcedure-execute(LCommonDao dao, CallableStatement cstmt) ? Error occurred while executing Stored Procedure or getting results. : java.sql.SQLException: ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 ‘MAIN’ 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored

  {call PKG_OSMS_GEN_KPI.MAIN( ?
                             , ?,?
                             , ?  
                             , ?
                             )}

parameter : [201145, 10001211, admin, null, null]

osms.smReport.weeklyRpt.biz.WeeklyReportBiz.createWeeklyReportDo()=>Error occurred while executing Stored Procedure or getting results. : java.sql.SQLException: ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 ‘MAIN’ 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored

2010-03-31 00:00:00

8.截取到分钟
sys@secooler> select trunc(sysdate,’MI’) trunc_minute from dual;

TRUNC_MINUTE

2010-03-31 00:22:00

9.trunc应用–解决因时分秒不同导致无返回记录的问题
sys@secooler> create table t (x date,y timestamp);

Table created.

sys@secooler> insert into t values (sysdate,sysdate);

1 row created.

sys@secooler> update t set y=sysdate;

1 row updated.

sys@secooler> commit;

Commit complete.

sys@secooler> select * from t;

X Y

2010-03-31 00:31:20 31-MAR-10 12.31.28.000000 AM

此时我们如果直接查询x与y相等的记录是没有返回结果的。
sys@secooler> select * from t where x = y;

no rows selected

如果我们同时使用trunc截取到天，结果将不同
sys@secooler> select * from t where trunc(x,’DD’)=trunc(y,’DD’);

X Y

2010-03-31 00:31:20 31-MAR-10 12.31.28.000000 AM

10.小结
在Oracle数据中日期的操作相对复杂，如果可以善加利用与日期相关的函数，便会达到事半功倍的效果。

Good luck.

secooler
10.03.30

– The End –

*****************************************************************************8
根据某个表数据修改数据
update spms_shop_dtl d
set d.shop_grade =
(select t.shop_level_new
from shop_level_update_2012 t
where d.shop_cd = t.shop_cd)
where d.shop_cd in (select a.shop_cd from shop_level_update_2012 a);

修改channel_grade
UPDATE spms_shop_dtl t
SET t.channel_grade = (SELECT t2.channel_grade FROM test03 t2 WHERE t2.shop_cd = t.shop_cd)
WHERE t.shop_cd IN (SELECT t3.shop_cd FROM test03 t3);
*****************************************************************************8
UPDATE spms_shop_dtl t
SET t.channel_grade = ”
WHERE NOT EXISTS (SELECT t2.shop_cd FROM test03 t2 WHERE t2.shop_cd = t.shop_cd)
AND t.shop_cd IN (SELECT t1.shop_cd
FROM spms_shop_mst t1,
spms_shop_dtl t2
WHERE t1.shop_cd = t2.shop_cd
AND t1.com_cd = t2.com_cd
AND t1.use_flag = ‘Y’)

*****************************************************************************8
job 备份
begin
sys.dbms_job.submit(job => :job,
what => ‘pro_plan_job;’,
next_date => to_date(‘06-03-2012 17:33:11’, ‘dd-mm-yyyy hh24:mi:ss’),
interval => ‘sysdate+2/1440’);
commit;
end;

/

begin
sys.dbms_job.submit(job => :job,
what => ‘PK_EZS_AG_DATA.main();’,
next_date => to_date(‘06-03-2012 17:39:56’, ‘dd-mm-yyyy hh24:mi:ss’),
interval => ‘sysdate+10/1440’);
commit;
end;
/

*****************************************************************************8
比如现在有一人员表（表名：peosons）
若想将姓名、身份证号、住址这三个字段完全相同的记录查询出来
select p1.*
from persons p1,persons p2
where p1.id<>p2.id
and p1.cardid = p2.cardid and p1.pname = p2.pname and p1.address = p2.address

可以实现上述效果．

几个删除重复记录的SQL语句

1.用rowid方法

2.用group by方法

3.用distinct方法

1。用rowid方法

据据oracle带的rowid属性，进行判断，是否存在重复,语句如下：

查数据:

 select * from table1 a where rowid !=(select max(rowid) 
 from table1 b where a.name1=b.name1 and a.name2=b.name2......)

删数据：

delete  from table1 a where rowid !=(select max(rowid) 
from table1 b where a.name1=b.name1 and a.name2=b.name2......)

2.group by方法

查数据:

　　select count(num), max(name) from student –列出重复的记录数，并列出他的name属性
　　group by num
　　having count(num) >1 –按num分组后找出表中num列重复，即出现次数大于一次

删数据：

　　delete from student
　　group by num
　　having count(num) >1
　　这样的话就把所有重复的都删除了。

3.用distinct方法 -对于小的表比较有用

create table table_new as select distinct * from table1 minux
truncate table table1;
insert into table1 select * from table_new;

查询及删除重复记录的方法大全

1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
delete from people
where peopleId in (select peopleId from people group by peopleId
having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

3、查找表中多余的重复记录（多个字段）
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

(二)
比方说
在A表中存在一个字段“name”，
而且不同记录之间的“name”值有可能会相同，
现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；
Select Name,Count() From A Group By Name Having Count() > 1
如果还查性别也相同大则如下:
Select Name,sex,Count() From A Group By Name,sex Having Count() > 1

(三)
方法一
declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count() from 表名 group by 主字段 having count() >； 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from 表名 where 主字段 = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0

方法二
＂重复记录＂有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，
比如Name字段重复，而其他字段不一定重复或都重复可以忽略。

1、对于第一种重复，比较容易解决，使用
select distinct * from tableName就可以得到无重复记录的结果集。
如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp
发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。

2、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下
假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集
select identity(int,1,1) as autoID, * into #Tmp from tableName
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2)
　　最后一个select即得到了Name，Address不重复的结果集（但多了一个autoID字段，实际写时可以写在select子句中省去此列）

(四)
查询重复
select * from tablename where id in (
select id from tablename
group by id
having count(id) > 1
)

例子
delete from w_m_mjout where ksid in
(select ksid from tableName group by ksid HAVING COUNT(ksid)>1)
and rowid not in (select min(ROWID) from tableName group by ksid HAVING COUNT(ksid)>1)

*****************************************************************************8
查询重复信息：
select t.*
from mob_ag_basic_promoter_inf t
where t.rowId <> (select min(t2.rowId)
from mob_ag_basic_promoter_inf t2
where t2.agent_promoter_code = t.agent_promoter_code
and t2.agent_code = ‘ASD’)

删除重复信息：（适合较大数据量）
delete from mob_ag_basic_shop_inf t
where t.agent_shop_code in (select t1.agent_shop_code
from mob_ag_basic_shop_inf t1
where t1.agent_code = ‘ASD’
group by t1.agent_shop_code
having count(1) > 1)
and rowId not in (select min(rowId)
from mob_ag_basic_shop_inf t2
where t2.agent_code = ‘ASD’
group by t2.agent_shop_code
having count(1) > 1)
;
*****************************************************************************8
Oracle截取字符串和查找字符串
oracle 截取字符(substr)，检索字符位置(instr) case when then else end语句使用收藏
常用函数：substr和instr
1.SUBSTR（string,start_position,[length]）求子字符串，返回字符串
解释：string 元字符串
start_position 开始位置（从0开始）
length 可选项，子字符串的个数
For example:
substr(“ABCDEFG”, 0); //返回：ABCDEFG，截取所有字符
substr(“ABCDEFG”, 2); //返回：CDEFG，截取从C开始之后所有字符
substr(“ABCDEFG”, 0, 3); //返回：ABC，截取从A开始3个字符
substr(“ABCDEFG”, 0, 100); //返回：ABCDEFG，100虽然超出预处理的字符串最长度，但不会影响返回结果，系统按预处理字符串最大数量返回。
substr(“ABCDEFG”, 0, -3); //返回：EFG，注意参数-3，为负值时表示从尾部开始算起，字符串排列位置不变。

2.INSTR（string,subString,position,ocurrence）查找字符串位置
解释：string：源字符串
subString：要查找的子字符串
position：查找的开始位置
ocurrence：源字符串中第几次出现的子字符串
For example:
INSTR(‘CORPORATE FLOOR’,’OR’, 3, 2)中，源字符串为’CORPORATE FLOOR’, 目标字符串为’OR’，起始位置为3，取第2个匹配项的位置；返回结果为 14 ‘

*****************************************************************************8
Oracle 函数大全(字符串函数，数学函数，日期函数，逻辑运算函数，其他函数)
关键词： Oracle函数字符串函数数学函数日期函数逻辑运算函数

SQL中的单记录函数
1.ASCII
返回与指定的字符对应的十进制数;
SQL> select ascii(’A’) A,ascii(’a’) a,ascii(’0’) zero,ascii(’ ’) space from dual;
A A ZERO SPACE

65 97 48 32

2.CHR
给出整数,返回对应的字符;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;
ZH C

赵 A
3.CONCAT
连接两个字符串;
SQL> select concat(’010-’,’88888888’)||’转23’ 高乾竞电话 from dual;

高乾竞电话

010-88888888转23
4.INITCAP
返回字符串并将字符串的第一个字母变为大写;
SQL> select initcap(’smith’) upp from dual;

UPP

Smith

5.INSTR(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1
SQL> select instr(’oracle traning’,’ra’,1,2) instring from dual;

INSTRING

6.LENGTH
返回字符串的长度;
SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst;
NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))

高乾竞 3 北京市海锭区 6 9999.99 7

7.LOWER
返回字符串,并将所有的字符小写
SQL> select lower(’AaBbCcDd’)AaBbCcDd from dual;

AABBCCDD

aabbccdd

8.UPPER
返回字符串,并将所有的字符大写
SQL> select upper(’AaBbCcDd’) upper from dual;

UPPER

AABBCCDD

9.RPAD和LPAD(粘贴字符)
RPAD 在列的右边粘贴字符
LPAD 在列的左边粘贴字符
SQL> select lpad(rpad(’gao’,10,’’),17,’’)from dual;

LPAD(RPAD(’GAO’,1

**gao**
不够字符则用*来填满

10.LTRIM和RTRIM
LTRIM 删除左边出现的字符串
RTRIM 删除右边出现的字符串
SQL> select ltrim(rtrim(’ gao qian jing ’,’ ’),’ ’) from dual;

LTRIM(RTRIM(’

gao qian jing

11.SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL> select substr(’13088888888’,3,8) from dual;

SUBSTR(’

08888888

12.REPLACE(’string’,’s1’,’s2’)
string 希望被替换的字符或变量
s1 被替换的字符串
s2 要替换的字符串
SQL> select replace(’he love you’,’he’,’i’) from dual;

REPLACE(’H

i love you

13.SOUNDEX
返回一个与给定的字符串读音相同的字符串
SQL> create table table1(xm varchar(8));
SQL> insert into table1 values(’weather’);
SQL> insert into table1 values(’wether’);
SQL> insert into table1 values(’gao’);
SQL> select xm from table1 where soundex(xm)=soundex(’weather’);

XM

weather
wether

14.TRIM(’s’ from ’string’)
LEADING 剪掉前面的字符
TRAILING 剪掉后面的字符
如果不指定,默认为空格符
15.ABS
返回指定值的绝对值
SQL> select abs(100),abs(-100) from dual;
ABS(100) ABS(-100)

100 100

16.ACOS
给出反余弦的值
SQL> select acos(-1) from dual;

ACOS(-1)

3.1415927

17.ASIN
给出反正弦的值
SQL> select asin(0.5) from dual;

ASIN(0.5)

.52359878

18.ATAN
返回一个数字的反正切值
SQL> select atan(1) from dual;

ATAN(1)

.78539816

19.CEIL
返回大于或等于给出数字的最小整数
SQL> select ceil(3.1415927) from dual;

CEIL(3.1415927)

20.COS
返回一个给定数字的余弦
SQL> select cos(-3.1415927) from dual;

COS(-3.1415927)

-1
21.COSH
返回一个数字反余弦值
SQL> select cosh(20) from dual;

COSH(20)

242582598

22.EXP
返回一个数字e的n次方根
SQL> select exp(2),exp(1) from dual;
EXP(2) EXP(1)

7.3890561 2.7182818

23.FLOOR
对给定的数字取整数
SQL> select floor(2345.67) from dual;

FLOOR(2345.67)

2345

24.LN
返回一个数字的对数值
SQL> select ln(1),ln(2),ln(2.7182818) from dual;
LN(1) LN(2) LN(2.7182818)

0 .69314718 .99999999

25.LOG(n1,n2)
返回一个以n1为底n2的对数
SQL> select log(2,1),log(2,4) from dual;
LOG(2,1) LOG(2,4)

0 2

26.MOD(n1,n2)
返回一个n1除以n2的余数
SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;
MOD(10,3) MOD(3,3) MOD(2,3)

1 0 2

27.POWER
返回n1的n2次方根
SQL> select power(2,10),power(3,3) from dual;
POWER(2,10) POWER(3,3)

1024 27

28.ROUND和TRUNC
按照指定的精度进行舍入
SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)

56 -55 55 -55

29.SIGN
取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
SQL> select sign(123),sign(-100),sign(0) from dual;
SIGN(123) SIGN(-100) SIGN(0)

1 -1 0

0 A
100+ B
其他： C
sign(100)+100:
1:101
-1:99
0:100

30.SIN
返回一个数字的正弦值
SQL> select sin(1.57079) from dual;

SIN(1.57079)

31.SIGH
返回双曲正弦的值
SQL> select sin(20),sinh(20) from dual;
SIN(20) SINH(20)

.91294525 242582598

32.SQRT
返回数字n的根
SQL> select sqrt(64),sqrt(10) from dual;
SQRT(64) SQRT(10)

8 3.1622777

33.TAN
返回数字的正切值
SQL> select tan(20),tan(10) from dual;
TAN(20) TAN(10)

2.2371609 .64836083

34.TANH
返回数字n的双曲正切值
SQL> select tanh(20),tan(20) from dual;
TANH(20) TAN(20)

1 2.2371609

35.TRUNC
按照指定的精度截取一个数
SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;
TRUNC1 TRUNC(124.16666,2)

100 124.16

36.ADD_MONTHS
增加或减去月份
SQL> select to_char(add_months(to_date(’199912’,’yyyymm’),2),’yyyymm’) from dual;

TO_CHA

200002
SQL> select to_char(add_months(to_date(’199912’,’yyyymm’),-2),’yyyymm’) from dual;

TO_CHA

199910

37.LAST_DAY
返回日期的最后一天
SQL> select to_char(sysdate,’yyyy.mm.dd’),to_char((sysdate)+1,’yyyy.mm.dd’) from dual;
TO_CHAR(SY TO_CHAR((S

2004.05.09 2004.05.10
SQL> select last_day(sysdate) from dual;

LAST_DAY(S

31-5月 -04

38.MONTHS_BETWEEN(date2,date1)
给出date2-date1的月份
SQL> select months_between(’19-12月-1999’,’19-3月-1999’) mon_between from dual;

MON_BETWEEN

9
SQL>selectmonths_between(to_date(’2000.05.20’,’yyyy.mm.dd’),to_date(’2005.05.20’,’yyyy.mm.dd’)) mon_betw from dual;

MON_BETW

-60

39.NEW_TIME(date,’this’,’that’)
给出在this时区=other时区的日期和时间
SQL> select to_char(sysdate,’yyyy.mm.dd hh24:mi:ss’) bj_time,to_char(new_time
2 (sysdate,’PDT’,’GMT’),’yyyy.mm.dd hh24:mi:ss’) los_angles from dual;
BJ_TIME LOS_ANGLES

2004.05.09 11:05:32 2004.05.09 18:05:32

40.NEXT_DAY(date,’day’)
给出日期date和星期x之后计算下一个星期的日期
SQL> select next_day(’18-5月-2001’,’星期五’) next_day from dual;

NEXT_DAY

25-5月 -01
41.SYSDATE
用来得到系统的当前日期
SQL> select to_char(sysdate,’dd-mm-yyyy day’) from dual;

TO_CHAR(SYSDATE,’

09-05-2004 星期日
trunc(date,fmt)按照给出的要求将日期截断,如果fmt=’mi’表示保留分,截断秒
SQL> select to_char(trunc(sysdate,’hh’),’yyyy.mm.dd hh24:mi:ss’) hh,
2 to_char(trunc(sysdate,’mi’),’yyyy.mm.dd hh24:mi:ss’) hhmm from dual;
HH HHMM

2004.05.09 11:00:00 2004.05.09 11:17:00

42.CHARTOROWID
将字符数据类型转换为ROWID类型
SQL> select rowid,rowidtochar(rowid),ename from scott.emp;
ROWID ROWIDTOCHAR(ROWID) ENAME

AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH
AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN
AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD
AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES

43.CONVERT(c,dset,sset)
将源字符串 sset从一个语言字符集转换到另一个目的dset字符集
SQL> select convert(’strutz’,’we8hp’,’f7dec’) “conversion” from dual;

conver

strutz

44.HEXTORAW
将一个十六进制构成的字符串转换为二进制

45.RAWTOHEXT
将一个二进制构成的字符串转换为十六进制

46.ROWIDTOCHAR
将ROWID数据类型转换为字符类型

47.TO_CHAR(date,’format’)
SQL> select to_char(sysdate,’yyyy/mm/dd hh24:mi:ss’) from dual;

TO_CHAR(SYSDATE,’YY

2004/05/09 21:14:41

48.TO_DATE(string,’format’)
将字符串转化为ORACLE中的一个日期

49.TO_MULTI_BYTE
将字符串中的单字节字符转化为多字节字符
SQL> select to_multi_byte(’高’) from dual;

TO

高

50.TO_NUMBER
将给出的字符转换为数字
SQL> select to_number(’1999’) year from dual;

YEAR

1999

51.BFILENAME(dir,file)
指定一个外部二进制文件
SQL>insert into file_tb1 values(bfilename(’lob_dir1’,’image1.gif’));

52.CONVERT(’x’,’desc’,’source’)
将x字段或变量的源source转换为desc
SQL> select sid,serial#,username,decode(command,
2 0,’none’,
3 2,’insert’,
4 3,
5 ’select’,
6 6,’update’,
7 7,’delete’,
8 8,’drop’,
9 ’other’) cmd from v$session where type!=’background’;
SID SERIAL# USERNAME CMD

1 1 none
2 1 none
3 1 none
4 1 none
5 1 none
6 1 none
7 1275 none
8 1275 none
9 20 GAO select
10 40 GAO none

53.DUMP(s,fmt,start,length)
DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值
SQL> col global_name for a30
SQL> col dump_string for a50
SQL> set lin 200
SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;
GLOBAL_NAME DUMP_STRING

ORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D

54.EMPTY_BLOB()和EMPTY_CLOB()
这两个函数都是用来对大数据类型字段进行初始化操作的函数

55.GREATEST
返回一组表达式中的最大值,即比较字符的编码大小.
SQL> select greatest(’AA’,’AB’,’AC’) from dual;

GR

AC
SQL> select greatest(’啊’,’安’,’天’) from dual;

GR

天

56.LEAST
返回一组表达式中的最小值
SQL> select least(’啊’,’安’,’天’) from dual;

LE

啊

57.UID
返回标识当前用户的唯一整数
SQL> show user
USER 为”GAO”
SQL> select username,user_id from dba_users where user_id=uid;
USERNAME USER_ID

GAO 25

58.USER
返回当前用户的名字
SQL> select user from dual;

USER

GAO

59.USEREVN
返回当前用户环境的信息,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA 查看当前用户是否是DBA如果是则返回true
SQL> select userenv(’isdba’) from dual;

USEREN

FALSE
SQL> select userenv(’isdba’) from dual;

USEREN

TRUE
SESSION
返回会话标志
SQL> select userenv(’sessionid’) from dual;

USERENV(’SESSIONID’)

152
ENTRYID
返回会话人口标志
SQL> select userenv(’entryid’) from dual;

USERENV(’ENTRYID’)

0
INSTANCE
返回当前INSTANCE的标志
SQL> select userenv(’instance’) from dual;

USERENV(’INSTANCE’)

1
LANGUAGE
返回当前环境变量
SQL> select userenv(’language’) from dual;

USERENV(’LANGUAGE’)

SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LANG
返回当前环境的语言的缩写
SQL> select userenv(’lang’) from dual;

USERENV(’LANG’)

ZHS
TERMINAL
返回用户的终端或机器的标志
SQL> select userenv(’terminal’) from dual;

USERENV(’TERMINA

GAO
VSIZE(X)
返回X的大小(字节)数
SQL> select vsize(user),user from dual;
VSIZE(USER) USER

6 SYSTEM

60.AVG(DISTINCT|ALL)
all表示对所有的值求平均值,distinct只对不同的值求平均值
SQLWKS> create table table3(xm varchar(8),sal number(7,2));
语句已处理。
SQLWKS> insert into table3 values(’gao’,1111.11);
SQLWKS> insert into table3 values(’gao’,1111.11);
SQLWKS> insert into table3 values(’zhu’,5555.55);
SQLWKS> commit;
SQL> select avg(distinct sal) from gao.table3;

AVG(DISTINCTSAL)

3333.33
SQL> select avg(all sal) from gao.table3;

AVG(ALLSAL)

2592.59

61.MAX(DISTINCT|ALL)
求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
SQL> select max(distinct sal) from scott.emp;

MAX(DISTINCTSAL)

5000

62.MIN(DISTINCT|ALL)
求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次
SQL> select min(all sal) from gao.table3;

MIN(ALLSAL)

1111.11

63.STDDEV(distinct|all)
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
SQL> select stddev(sal) from scott.emp;

STDDEV(SAL)

1182.5032
SQL> select stddev(distinct sal) from scott.emp;

STDDEV(DISTINCTSAL)

1229.951

64.VARIANCE(DISTINCT|ALL)
求协方差
SQL> select variance(sal) from scott.emp;

VARIANCE(SAL)

1398313.9

65.GROUP BY
主要用来对一组数进行统计
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno;
DEPTNO COUNT(*) SUM(SAL)

10 3 8750
20 5 10875
30 6 9400

66.HAVING
对分组统计再加限制条件
SQL> select deptno,count(),sum(sal) from scott.emp group by deptno having count()>=5;
DEPTNO COUNT(*) SUM(SAL)

20 5 10875
30 6 9400
SQL> select deptno,count(),sum(sal) from scott.emp having count()>=5 group by deptno ;
DEPTNO COUNT(*) SUM(SAL)

20 5 10875
30 6 9400

67.ORDER BY
用于对查询到的结果进行排序输出
SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc;
DEPTNO ENAME SAL

10 KING 5000
10 CLARK 2450
10 MILLER 1300
20 SCOTT 3000
20 FORD 3000
20 JONES 2975
20 ADAMS 1100
20 SMITH 800
30 BLAKE 2850
30 ALLEN 1600
30 TURNER 1500
30 WARD 1250
30 MARTIN 1250
30 JAMES 950

*****************************************************************************8

根据中文拼音顺序排序：
select sys_id code, sys_short_nm val
from mob_shop_sys
where use_yn = ‘Y’
order by nlssort(sys_short_nm, ‘NLS_SORT=SCHINESE_PINYIN_M’)
*****************************************************************************8
create or replace trigger TG_UPDATE_MOB_SHOP_HIST
after update or insert or delete on mob_shop_mapping
for each row
declare
begin
if /updating or /deleting then

   insert into mob_shop_mapping_hist(COM_CD,
                        SHOP_CD,
                        PS_ID,
                        PR_ID,
                        PROD_CD,
                        SHOP_TP,
                        create_date,
                        create_emp,
                        USE_YN)
     values (:old.COM_CD,
             :old.SHOP_CD,
             :old.PS_ID,
             :old.PR_ID,
             :old.PROD_CD,
             :old.SHOP_TP,
             sysdate,
             :old.create_emp,
             'N' );

elsif inserting then

 insert into mob_shop_mapping_hist(COM_CD,
                        SHOP_CD,
                        PS_ID,
                        PR_ID,
                        PROD_CD,
                        SHOP_TP,
                        create_date,
                        create_emp,
                        USE_YN)
     values (:NEW.COM_CD,
             :NEW.SHOP_CD,
             :NEW.PS_ID,
             :NEW.PR_ID,
             :NEW.PROD_CD,
             :NEW.SHOP_TP,
             sysdate,
             :NEW.create_emp,
             'Y' );

end if;
end TG_UPDATE_MOB_SHOP_HIST;

*****************************************************************************8
关于rownum：

在查询中，我们可以注意到，
类似于“select xx from table where rownum < n”(n>1)这样的查询是有正确含义的，
而“select xx from table where rownum = n”这样的查询只在n=1的时候成立，
“select xx from table where rownum > n”(n>1)这样的查询只能得到一个空集。
另外“select xx from table where rownum > 0”这个查询会返回所有的记录。

它的特点是按顺序标记，而且是逐次递加的，换句话说就是只有有rownum=1的记录，才可能有rownum=2的记录。

为什么当条件为rownum = n或者rownum >= n时明明有数据却只能得到一个空集呢?假设我们的查询条件为rownum = 2，
那么在查询出的第一条记录的时候，oracle标记此条记录rownum为1，结果发现和rownum=2的条件不符，于是结果集为空。

*****************************************************************************8
使用imp导入数据库是出现ora-01695错误，此错误时由于表空间小所导致，解决办法如下四种：
1。加空间。增加原表空间大小
ALTER DATABASE DATAFILE ‘/dev/vg01/file.dbf’
RESIZE 999M;
2。 exp compress=n
3。create table first, and then imp ignore=y
4。consider out of line lob and put lob in dedicated tablespace.

*****************************************************************************8
根据条件删除多条记录：
delete from as_shop_dtl t3
where (t3.com_code, t3.cust_cd, t3.china_product) in
(select t1.com_code, t1.cust_cd, t1.china_product
from as_shop_dtl t1, aaa_test5 t2
where t1.cust_cd = t2.shop_cd)

*****************************************************************************8
执行package：
批量执行多个procedure：
执行procedure：

编写1.txt文件：
execute PR_SPMS_CALL_MONTHLY_SUMMARY(‘201205’);
execute PR_SPMS_CALL_DAILY_ACTSAL(‘20120508’);
spool d:/1.log
spool off
在plsqldev common window
执行 @d:/1.txt

@D:/insert menu.txt

执行结果：
PL/SQL procedure successfully completed

PL/SQL procedure successfully completed
Started spooling to d:\1.log
Stopped spooling to d:\1.log

*****************************************************************************8

如何查询 procedure是否正在运行？

select * from v$access where object=’存储过程名称’

*****************************************************************************8
SELECT ROLE_ID,
CREATE_EMP,
MENU_ID,
MENU_NAME_CN,
MENU_REMARK,
MENU_NAME_EN,
MENU_NAME_KR,
HIGH_MENU_ID,
MENU_LEVEL,
ORD_MENU_ID
FROM (SELECT distinct a.ROLE_ID,
a.CREATE_EMP,
a.MENU_ID,
b.MENU_NAME_CN,
b.MENU_REMARK,
b.MENU_NAME_EN,
b.MENU_NAME_KR,
b.HIGH_MENU_ID,
b.MENU_LEVEL,
b.ORD_MENU_ID
FROM sys_role_menu_inf a, sys_menu_inf b
WHERE a.USE_YN = ‘Y’
AND a.ROLE_ID = ${roleId}
AND a.MENU_ID = b.menu_id)
START WITH HIGH_MENU_ID = ‘M0000’
CONNECT BY PRIOR MENU_ID = HIGH_MENU_ID
ORDER SIBLINGS BY decode(length(ORD_MENU_ID),1,’0’||ORD_MENU_ID,ORD_MENU_ID)
*****************************************************************************8

BEGIN

DELETE as_mob_shop A
 WHERE (com_code, cust_cd, promoter, china_product) IN
       (SELECT com_cd, shop_cd, pr_id, prod_cd 
          FROM mob_shop_mapping_hist@DL_MKT_EZSELOUT B
         WHERE B.CREATE_DATE >= TRUNC(run_time)
           AND B.CREATE_DATE < TRUNC(run_time + 1)
           AND B.use_yn = 'N');

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
PR_PROCESS_ERROR(‘PR_SHOP_MAPPING’,
‘AS_MOB_SHOP’,
‘DELETE’,
SQLCODE,
SUBSTR(SQLERRM, 1, 2000));
RETURN;
END;

*****************************************************************************8
PL/SQL笔记
PL/SQL块中只能直接嵌入SELECT,DML(INSERT,UPDATE,DELETE)以及事务控制语句(COMMIT,ROLLBACK,SAVEPOINT),而不能直接嵌入DDL语句(CREATE,ALTER,DROP)和DCL语句(GRANT,REVOKE)
1.检索单行数据

1.1使用标量变量接受数据
v_ename emp.ename%type;
v_sal emp.sal%type;
select ename,sal into v_ename,v_sal from emp where empno=&no;
1.2使用记录变量接受数据
type emp_record_type is record(
ename emp.ename%type,sal emp.sal%type);
emp_record emp_record_type;
select ename,sal into emp_record from emp where empno=&no;
1.3嵌入SELECT语句注意事项:
使用SELECT INTO语句时,必须要返回一条数据,并且只能返回一条数据

no_date_found:
select into没有返回数据
too_many_rows:
select into返回多条数据

where子句使用注意事项:
使用的变量名不能与列名相同,否则触发TOO_MANY_ROWS例外.

2.操纵数据
2.1使用VALUES子句插入数据
v_deptno dept.deptno%type;
v_dname dept.dname%type;
v_deptno:=no;
v_dname:=’&name’;
insert into dept (deptno,dname) values(v_deptno,v_dname);
2.2使用子查询插入数据
v_deptno emp.deptno%type:=&no;
insert into employee select * from emp where deptno=v_deptno;

2.3更新数据
使用表达式更新列值
v_deptno dept.deptno%type:=no;
v_loc dept.loc%type:=’&loc’;
update dept set loc=v_loc where deptno=v_deptno;
2.4使用子查询更新列值
v_ename emp.ename%type:=’&name’;
update emp set (sal,comm) = (select sal,comm from emp where ename=v_ename) where job = (select job from emp where ename=v_ename)
2.5删除数据
使用变量删除数据
v_deptno dept.deptno%type:=&no;
delete from dept where deptno=v_deptno;

2.6使用子查询删除数据
v_ename emp.ename%type:=’&name’;
delete from emp where deptno=(select deptno from emp where ename=v_ename);

3.SQL游标
游标是指向上下文区的指针,包括隐含游标(SQL游标)和显式游标两种类型
SQL游标用于处理SELECT INTO ,INSERT,UPDATE以及DELETE语句.
显式游标用于处理多行的SELECT语句
SQL游标包括:SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT,SQL%ISOPEN等四种属性

3.1 SQL%ISOPEN:执行时,会隐含的打开和关闭游标.因此该属性的值永远都是FALSE

3.2 SQL%FOUND:用于确定SQL语句执行是否成功.当SQL有作用行时,为TRUE,否则为FALSE
v_deptno emp.deptno%type:=&no;
update emp set sal=sal*1.1 where deptno=v_deptno;
if sql%found then dbms_output.put_line(‘执行成功’); else dbms_output.putline(‘失败’); endif

3.3 sql%notfound:确定SQL语句执行是否成功,当SQL有作用行时,为false,否则为true

3.4 sql%rowcount:返回SQL语句所作用的总计行数
v_deptno emp.deptno%type:=&no;
update emp set sal=sal*1.1 where deptno=v_deptno;
dbms_output.put_line(‘修改了’||sql%rowcount||’行’);

4.事务控制语句
事务控制语句包括COMMIT,ROLLBACK以及SAVEPOINT等三种语句

v_sal emp.sal%type:=&salary;
v_ename emp.ename%type:=’&name’;
update emp set sal=v_sal where ename=v_ename;
commit;
exception
when others then
rollback;
insert into temp values(1);
savepoint a1;
insert into temp values(2);
savepoint a2;
insert into temp values(3);
savepoint a3;
rollback to a2;
commit;

5.控制结构
条件分支语句

5.1简单条件判断
v_sal number(6,2);
select sal into v_sal from emp where lower(ename)=lowe(‘&&name’);
if v_sal<2000 then update emp set sal=v_sal+200 where lower(ename)=lower(‘&name’)
end if
5.2二重条件分支
v_comm number(6,2);
select comm into v_comm from emp where empno=&&no;
if v_comm<>0 then update emp set comm=v_comm+100 where empno=&no;
else update emp set comm=200 where empno=&no;
end if
5.3多重条件分支
v_job varchar2(10);
v_sal number(6,2);
select job,sal into v_job,v_sal from emp where empno=&&no;
if v_job=’president’ then update emp set sal=v_sal+1000 where empno=&no;
elseif v_job=’manager’ then update emp set sal=v_sal+500 where empno=&no;
else update emp set sal=v_sal+200 where empno=&no;
end if;

5.4 CASE语句:
在CASE语句中使用单一选择符进行等值比较
declare
v_deptno emp deptno%type;
begin
v_deptno:=&no;
case v_deptno
when 10 then update emp set comm=100 where deptno=v_deptno;
when 20 then update emp set comm=80 where deptno=v_deptno;
when 30 then update emp set comm=50 where deptno=v_deptno;
else
dbms_output.put_line(“不存在’);
end case;
end;
5.5 在CASE语句中使用多种条件比较
declare
v_sal emp.sal%type;
v_ename emp.ename%type;
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
case
when v_sal<1000 then update emp set comm=100 where ename=v_ename;
when v_sal<2000 then update emp set comm=80 where ename=v_ename;
when v_sal<6000 tehn update emp set comm=50 where ename=v_ename;
end case;
end;

5.6循环语句
有基本循环,WHILE循环,FOR循环

基本循环:一定要包含EXIT语句,定义循环控制变量
create table temp(cola int);
declare
i int:=1;
begin
loop
insert into temp values(i);
exit when i=10;
i:=i+1;
end loop;
end;
5.7 WHILE循环:定义循环控制变量,并在循环体内改变循环控制变量的值
declare
i int:=1;
begin
while i<=10 loop
insert into temp values(i);
i:=i+1;
end loop;
end;
5.8 for循环:使用FOR循环时,ORACLE会隐含定义循环控制变量.
for counter in[reverse]
lower_bound..upper_bound loop
statement1;
statement2;
…….
end loop;
5.9 counter是循环控制变量,并且该变量由ORACLE隐含定义,不需要显示定义;lower_bound和upper_bound分别对应循环控制变量的上下界值.默认情况下,FOR循环,每次会自动增一,指定REVERSE选项时,每次循环控制变量会减一
begin
for i in reverse 1..10 loop
insert into temp values(i);
end loop;
end;

5.10嵌套循环和标号:通过在嵌套循环中使用标号,可以区分内层循环和外层循环,并且可以在内层循环中直接退出外层循环
declare
result int;
begin
<>
for i in 1..100 loop
<>
for j in 1..100 loop
result:=i*j;
exit outer when result=1000;
exit when result=500;
end loop inner;
dbms_ouput.put_line(result);
end loop outer;
dbms_output.put_line(result);
end;

6.顺序控制语句
PL/SQL不仅提供了条件分支语句和循环控制语句,而且还提供了顺序控制语句GOTO和NULL.一般情况下不使用

6.1 GOTO:用于跳转到特定标号处去执行语句.
GOTO LABEL_NAME;

declare
i int :=1;
begin
loop
insert into temp values(i);
if i=10 then
goto end_loop
end if;
i:=i+1;
end loop;
<>
dbms_output.put_line(‘循环结束’);
end;

6.2 null:不会执行任何操作,并且会直接将控制传递到下一条语句.
declare
v_sal emp.sal%type;
v_ename emp.ename%type;
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
if v_sal<3000 then update emp set comm=sal*0.1 where ename=v_ename;
else
null;
end if;
end;

7.复合数据类型
7.1定义PL/SQL记录

自定义PL/SQL记录:需要定义PL/SQL记录类型和记录变量,identifier用于指定记录变量名
type type_name is record(
field_declaration[,
field_declaration]…
);
identifier type_name;

declare
type emp_record_type is record(
name emp.ename%type,
salary em.sal%type,
dno emp.deptno%type);
emp_record emp_record_type;

使用%rowtype属性定义记录变量:可以基于表或视图定义记录变量
当使用%ROWTYPE属性定义记录变量时,记录成员个数,名称,类型与表或视图列的个数，名称,类型完全相同.
dept_record dept%rowtype;
emp_record emp%rowtype;

在select into 语句中使用PL/SQL 记录
在select into 语句中使用记录变量
set serveroutput on
declare
  type emp_record_type is record(
       name emp.ename%type,
       salary em.sal%type,
       dno emp.deptno%type);
  emp_record emp_record_type;
begin
  select ename,sal,deptno into emp_record from emp where empno=&no;
  dbms_output.put_line(emp_record.name);
end;
7.2在select into 语句中使用记录成员
declare 
  type emp_record_type is record(
       name emp.ename%type,
       salary emp.sal%type,
       dna emp.deptno%type);
  emp_record emp_record_type;
begin
  select ename,sal into emp_record.name,emp_record.salary from emp where empno=&no;
  dbms_output.put_line(emp_record.name);
end;

7.3在insert语句中使用PL/SQL记录
在VALUES子句中使用记录变量
declare 
  dept_record dept%rowtype;
begin
  dept_record.deptno:=50;
  dept_record.dname:='administrator';
  dept_record.loc:='beijing';
  insert int dept values dept_record;
end;
在VALUES子句中使用记录成员
declare
  dept_record dept%rowtype;
begin
  dept_record.deptno:=60;
  dept_record.dname:='sales';
  insert into dept (deptno,dname) values (dept_record.deptno,dept_record.dname);
end;
7.4在UPDATE语句中使用PL/SQL记录
在SET子句中使用记录变量
declare
  dept_record dept%rowtype;
begin
  dept_record.deptno:=30;
  dept_record.dnama:='sales';
  dept_record.loc:='shanghai';
  update dept set row=dept_record where deptno=30;

end;
在SET子句中使用记录成员
declare
dept_record dept%rowtype;
begin
dept_record.loc:=’guangzhou’;
update dept set loc=dept_record.loc where deptno=10;
end;

7.5在DELETE语句中使用PL/SQL记录:只能在DELETE语句中的WHERE子句中使用记录成员
declare
dept_record dept%rowtype;
begin
dept_record.deptno:=50;
delete from dept where deptno=dept_record.deptno;
end;

8.pl/sql集合
处理单行单列数据,可以使用标量变量,处理单行多列的数据，可以使用PL/SQL记录
处理单列多行数据,可以使用PL/SQL集合

PL/SQL集合类型类似于高级语言数组的一种复合数据类型
包括:索引表(PL/SQL表),嵌套表(NESTED TABLE),变长数组(VARRAY)三种

8.1 索引表:PL/SQL表元素个数没有限制,并且下标可以是负值
定义索引表:
type type_name is table of element_type [not null] index by key_type;
identifier type_name;

在索引表中使用BINARY_INTEGER和PLS_INTEGER
set serveroutput on
declare
type ename_table_type is table of emp.ename%type index by binary_integer;
ename_table ename_table_type;
begin
select ename into ename_table(-1) from emp where empno=&no;
dbms_output.put_line(‘雇员名:’||ename_table(-1));
end;
在索引表中使用VARCHAR2
set serveroutput on
declare
type area_table_type is table of number index by varchar2(10);
area_table area_table_type;
begin
area_table(‘北京’):=1;
area_table(‘上海’):=2;
area_table(‘广州’):=3;
dbms_output.put_line(‘第一个元素:’||area_table.first);
dbms_output.put_line(‘最后一个元素:’||area_table.last);
end;
8.2 嵌套表:元素个数从1开始,并且元素个数没有限制
定义嵌套表:
type type_name is table of element_type;
identifier type_name;

  declare
    type ename_table_type is table of emp.ename%type;
    ename_table ename_table_type:=ename_table_type('A','A');

在PL/SQL块中使用嵌套表:使用嵌套表变量时,必须首先使用构造方法初始化嵌套表变量,然后才能在块内引用嵌套表元素
declare
type ename_talbe_type is table of emp.ename%type;
ename_talbe ename_table_type;
begin
ename_table:=ename_table_type(‘mary’,’mary’,’mary’);
select ename into ename_table(2) from emp where empno=&no;
dbms_output.put_line(‘雇员名:’||ename_table(2));
end;

在表列中使用嵌套表:
在表列中使用嵌套表类型,必须首先使用CREATE TYPE命令建立嵌套表类型.
当使用嵌套表类型作为表列的数据类型时,必须要为嵌套表列指定专门的存储表
create type phone_type is table of varchar2(20);
/
create table employee(
id number(4),name varchar2(10),sal number(6,2),
phone phone_type
)nested table phone store as phone_table;
8.3 在PL/SQL块中为嵌套表列插入数据
当定义嵌套表类型时,ORACLE自动为该类型生成相应的构造方法.当为嵌套表列插入数据时,需要使用嵌套表的构造方法
begin
insert into employee values(1,’scott’,800,phone_type(‘0471-3456788’,’13804711111’));
end;
在PL/SQL块中检索嵌套表列的数据
当在PL/SQL块中检索嵌套表列的数据时,需要定义嵌套表类型的变量接受其数据.
set serveroutput on
declare
phone_table phone_type;
begin
select phone into phone_table from employee where id=1;
for i in 1..phone_table.count loop
dbms_output.put_line(‘电话:’||phone_table(i));
end loop;
end;

8.4 在pl/sql块中更新嵌套表列的数据
更新嵌套表列的数据时,首先需要定义嵌套表变量,并使用构造方法初始化变量,然后才可在执行部分使用UPDATE语句更新数据
declare
phone_table phone_type:=phone_type(‘0471-3456788’,’13804711111’,’0471-2233066’,’13056278568’);
begin
update employee set phone=phone_talbe where id=1;
end;

8.5变长数组(varray)
VARRAY也是一种用于处理PL/SQL数组的数据类型, 它也可以做为表列的数据类型使用.
元素下标以1开始,并且元素的最大个数是有限制的
定义VARRAY的语法:
type type_name is varray(size_limite) of element_type [not mull];
identifier type_name;
当使用VARRAY元素时,必须要使用其构造方法初始化VARRAY元素.
declare
type ename_table_type is varrar(20) of emp.ename%type;
ename_talbe ename_table_type:=ename_table_type(‘A’,’A’);
8.6 在PL/SQL块中使用VARRAY
必须首先使用其构造方法来初始化VARRAY变量,然后才能在PL/SQL块内引用VARRAY元素
declare
type ename_table_type is varray(20) of emp.ename%type;
ename_table ename_table_type:=ename_table_type(‘mary’);
begin
select ename into ename_table(1) from emp where empno=&no;
dbms_output.put_line(‘雇员名:’||ename_table(1));
end;
在表列中使用varray
要在表列中引用该数据类型,则必须使用CREATE TYPE命令建立VARRAY类型
create type phone_type is varray(20) of varchar2(20);
/
create table employee(
id number(4),name varchar2(10),
sal number(6,2),phone phone_type);
在PL/SQL块中操纵VARRAY列的方法与操纵嵌套表列的方法完全相同.嵌套表列元素个数没有限制,而VARRAY列的元素个数是有限制的.

PL/SQL记录表
PL/SQL变量处理单行单列数据
PL/SQL记录处理单行多列数据
PL/SQL集合处理多行单列数据
PL/SQL记录表处理多行多列数据

8.7 PL/SQL记录表结合了PL/SQL记录和PL/SQL集合的优点
declare
type emp_talbe_type is table of emp%rowtype index by binary_integer;
emp_table emp_table_type;
begin
select * into emp_table(1) from emp where empno=&no;
dbms_output.put_line(‘雇员姓名:’||emp_table(1).ename);
dbms_output.put_line(‘雇员姓名:’||emp_table(1).sal);
end;
8.8 多级集合
多级集合是指嵌套了集合类型的集合类型
在PL/SQL块中使用多级VARRAY:实现多维数组功能
定义二维VARRAY(10,10):
declare
type a1_varray_type is varray(10) of int;–定义一维VARRAY
type na1_varray_type is varray(10) of a1_varray_type;–定义二维VARRAY集合
–初始化二维集合变量
nv1 nal_varray_type:=nal_varray_type(
a1_varray_type(58,100,102),
a1_varray_type(55,6,73),
a1_varray_type(2,4);
begin
dbms_output.put_line(‘显示二维数组所有元素’);
for i in 1..nv1.count loop
for j in 1..nv1(i).count loop
dbms_output.put_line(‘nvl(‘||i||’,’||j||’)=’||nvl(i)(j));
end loop;
end loop;
end;
在PL/SQL块中使用多级嵌套表
如果多维数组的元素个数没有限制,那么可以在嵌套表类型中嵌套另一个嵌套表类型

8.9 二维嵌套表
declare
type a1_table_type is table of int;–定义一维嵌套表
type nal_table_type is table of a1_table_type;–定义二维嵌套表集合
–初始化二维集合变量
nvl nal_table_type:=nal_table_type(
a1_table_type(2,4),
a1_table_type(5,73));
begin
dbms_output.put_line(‘显示二维数组所有元素’);
for i in 1..nvl.count loop
for j in 1..nvl(i).count loop
dbms_output.put_line(‘nvl(‘||i||’,’||j||’)=’||nvl(i)(j));
end loop;
end loop;
end
在PL/SQL块中使用多级索引表
二维索引表:
declare
type a1_table_type is table of int index by binary_integer;
type nal_table_type is table of al_table_type index by binary_integer;
nvl nal_table_type;
begin
nvl(1)(1):=10;
nvl(1)(2):=5;
nvl(2)(1):=100;
nvl(2)(2):=50;
dbms_output.put_line(‘显示二维数组所有元素’);
for i in 1..nvl.count loop
for j in 1..nvl(i).count loop
dbms_output.put_line(‘nvl(‘||i||’,’||j||’)=’||nvl(i)(j));
end loop;
end loop;
end;

8.10集合方法:ORACLE提供的用于操纵集合变量的内置函数或过程,其中EXISTS,COUNT,LIMIT,FIRST,NEXT,FRIOR和NEXT是函数
而EXTEND,TRIM和DELETE则是过程
集合方法只能在PL/SQL语句中使用,不能在SQL语句中使用.
集合方法EXTEND和TRIM只适用于嵌套表和VARRAY,而不适合于索引表
1.EXISTS:用于确定集合元素是否存在
declare
type ename_table_type is table of emp.ename%type;
ename_table ename_table_type;
begin
if ename_table.exists(1) then
ename_table(1):=’SCOTT’;
else
dbms_output.put_line(‘必须初始化集合元素’);
end if;
end;
2.COUNT:用于返回当前集合变量中的元素总个数.
declare
type ename_table_type is table of emp.ename%type index by binary_integer;
ename_table ename_table_type;
begin
ename_table(-5):=’scott’;
ename_table(1):=’smith’;
ename_table(5):=’mary’;
ename_table(10):=’blake’;
dbms_output.put_line(‘集合元素总个数:’||ename_table.count);
end;
3.LIMIT:用于返回集合元素的最大个数.因为嵌套表和索引表的余数个数没有限制,返回NULL
对于VARRAY来说，该方法返回VARRAY所允许的最大元素个数
declare
type ename_table_type is varray(20) of emp.ename%type;
ename_table ename_table_type:=ename_table_type(‘mary’);
begin
dbms_output.put_line(‘集合元素的最大个数:’||ename_table.limit);
end;
4.FIRST和LAST:FIRST用于返回集合变量第一个元素的下标,而LAST方法则用于返回集合变量最后一个元素的下标
declare
type ename_table_type is table of emp.ename%type index by binary_integer;
ename_table ename_table_type;
begin
ename_table(-5):=’scott’;
ename_table(1):=’smith’;
ename_table(5):=’mary’;
ename_table(10):=’blake’;
dbms_output.put_line(‘第一个元素:’||ename_table.first);
dbms_output.put_line(‘最后一个元素:’||ename_table.last);
end;
5.FRIOR和NEXT:PRIOR返回当前集合元素的前一个元素的下标,而NEXT方法则用于返回当前集合元素的后一个元素的下标
declare
type ename_table_type is table of emp.ename%type index by binary_integer;
ename_table ename_table_type;
begin
ename_table(-5):=’scott’;
ename_table(1):=’smith’;
ename_table(5):=’mary’;
ename_table(10):=’blake’;
dbms_output.put_line(‘元素5的前一个元素:’||ename_table.prior(5));
dbms_output.put_line(‘元素5的后一个元素:’||ename_table.next(5));
end;
6.EXTEND:用于扩展集合变量的尺寸,并为它们增加元素.只适用于嵌套表和VARRAY.
三种调用格式:EXTEND,EXTEND(N),EXTEND(N,I):添加N个元素,值与第I个元素相同
declare
type ename_table_type is varray(20) of varchar2(10);
ename_table ename_table_type;
begin
ename_table:=ename_table_type(‘mary’);
ename_table.extend(5,1);
dbms_output.put_line(‘元素个数:’||ename_table.count);
end;
7.TRIM:用于从集合尾部删除元素,有TRIM和TRIM(N)两种调用格式.
只适用于嵌套表和VARRAY
declare
type ename_table_type is table of varchar2(10);
ename_table ename_table_type;
begin
ename_table:=ename_table_type(‘a’,’a’,’a’,’a’,’a’);
ename_table.trim(2);
dbms_table.put_line(‘元素个数:’||ename_table.count);
end;
8.DELETE:删除结合元素,但该方法只适用于嵌套表和索引表,不适用于VARRAY.
有DELETE,DELETE(N),DELETE(M,N)三种调用方式.
DETELE(M,N)删除集合变量从M到N之间的所有元素
declare
type ename_table_type is table of emp.ename%type index by binary_integer;
ename_table ename_table_type;
begin
ename_table(-5):=’scott’;
ename_table(1):=’smith’;
ename_table(5):=’mary’;
ename_table(10):=’blake’;
ename_table.delete(5);
dbms_output.put_line(‘元素总个数:’||ename_table.count);
end;

8.11集合赋值
使用嵌套表和VARRAY时,通过执行INSERT,UPDATE,FETCH,SELECT赋值语句,可以将一个集合的数据赋值给另一个集合.
当给嵌套表赋值时,还可以使用SET,MULTISET UNION,MULTISET INTERSECT,MULTISET EXCEPT等集合操作符
SET:用于取消嵌套表中的重复值.
MULTISET UNION:取得两个嵌套表的并集(DISTINCT)
MULTISET INTERSECT:用于取得两个嵌套表的交集.
NULTISET EXCEPT:用于取得两个嵌套表的差集

1.将一个集合的数据赋值个另一个集合
源集合和目标集合的数据类型必须完全一致.
declare
type name_varray_type is varray(4) of varchar2(10);
name_array1 name_varray_type;
name_array2 name_varray_type;
begin
name_array1:=name_varray_type(‘scott’,’smith’);
name_array2:=name_varray_type(‘a’,’a’,’a’,’a’);
dbms_output.put_line(‘name_array2的原数据:’);
for i in 1..name_array2.count loop
dbms_output.put_line(’ ‘||name_array2(i));
end loop;
dbms_output.new_line;
name_array2:=name_array1;
dbms_output.put(‘name_array2的新数据:’);
for i in 1..name_array2.count loop
dbms_output.put(’ ‘||name_array2(i));
end loop;
dbms_output.new_line;
end;

2.给集合赋NULL值:清空集合变量的所有数据(集合方法DETELE,TRIM也可以)
declare
type name_varray_type is varray(4) of varchar2(10);
name_array name_varray_type;
name_empty name_varray_type;
begin
name_array:=name_varray_type(‘scott’,’smith’);
dbms_output.put_line(‘name_array的原有元素个数:’||name_array.count);
name_array:=name_empty;
if name_array is null then
dbms_output.put_line(‘name_array的现有元素个数:0’);
end if;
end;
3.使用集合操作符给嵌套表赋值

1.使用SET操作符:用于取消特定嵌套表中的重复值.

declare
type nt_table_type is table of number;
nt_table nt_table_type:=nt_table_type(2,4,3,1,2);
result nt_table_type;
begin
result:=set(nt_table);
dbms_output.put(‘result:’);
for i in 1..result.count loop
dbms_output.put(’ ‘||result(i));
end loop;
dbms_output.new_line;
end;
2.使用MULTISET UNION操作符:取得两个嵌套表的并集.结果集中会包含重复值
declare
type nt_table_type is table of number;
nt1 nt_table_type:=nt_table_type(1,2,3);
nt2 nt_table_type:=nt_table_type(3,4,5);
result nt_table_type;
begin
result:=nt1 MULTISET union nt2;
dbms_output.put(‘result:’);
for i in 1..result.count loop
dbms_output.put(’ ‘||result(i));
end loop;
dbms_output.new_line;
end;
3.使用MULTISET UNION DISTINCT操作符:用于取得两个嵌套表的并集,并取消重复结果.
declare
type nt_table_type is table of number;
nt1 nt_table_type:=nt_table_type(1,2,3);
nt2 nt_table_type:=nt_table_type(3,4,5);
result nt_table_type;
begin
result:=nt1 multiset union distinct nt2;
dbms_output.put(‘result:’);
for i in 1..result.count loop
dbms_output.put(’ ‘||result(i));
end loop;
dbms_output.new_line;
end;

4.使用MULTISET INTERSECT操作符:用于取得两个嵌套表的交集
declare
type nt_table_type is table of number;
nt1 nt_table_type:=nt_table_type(1,2,3);
nt2 nt_table_type:=nt_table_type(3,4,5);
result nt_table_type;
begin
result:=nt1 multiset intersect nt2;
dbms_output.put(‘result:’);
for i in 1..result.count loop
dbms_output.put(’ ‘||result(i));
end loop;
dbms_output.new_line;
end;

5.使用MULTISET EXCEPT操作符:取得两个嵌套表的差集.在NT1中存在,但在NT2中不存在
declare
type nt_table_type is table of number;
nt1 nt_table_type:=nt_table_type(1,2,3);
nt2 nt_table_type:=nt_table_type(3,4,5);
result nt_table_type;
begin
result:=nt1 multiset except nt2;
dbms_output.put(‘result:’);
for i in 1..result.count loop
dbms_output.put(’ ‘||result(i));
end loop;
dbms_output.new_line;
end;
8.4比较集合
函数cardinality用于返回嵌套表变量的元素个数
操作符SUBMULTISET OF用于确定一个嵌套表是否为另一个嵌套表的子集
操作符MEMBER OF用于检测特定数据是否为嵌套表元素
操作符IS A SET用于检测嵌套表是否包含重复的元素值
操作符IS EMPTY用于检测嵌套表是否为NULL.

1.检测集合是否为NULL
declare
type name_table_type is table of varchar2(10);
name_table name_table_type;
begin
if name_table is empty then
dbms_output.put_line(‘name_table未初始化’);
end if;
end;
2.比较嵌套表是否相同
使用比较符=和!=检测两个嵌套表是否相同.不能比较VARRAY和索引表
declare
type name_table_type is table of varchar2(10);
name_table1 name_table_type;
name_table2 name_table_type;
begin
name_table1:=name_table_type(‘scott’);
name_table2:=name_table_type(‘smith’);
if name_table1=name_table2 then
dbms_output.put_line(‘两个嵌套表完全相同’);
else
dbms_output.put_line(‘两个嵌套表数值不同’);
end if;
end;
3.在嵌套表上使用集合操作符
在嵌套表上使用ANSI集合操作符CARDINALITY,MEMBER OF, IS A SET.不适用于VARRAY和索引表
使用函数CARDINALITY
declare
type nt_table_type is table of number;
nt1 nt_table_type:=nt_table_type(1,2,3,1);
begin
dbms_output.put_line(‘元素个数:’||cardinality(nt1));
end;
使用操作符SUBMULTISET OF:用于确定一个嵌套表是否为另一个嵌套表的子集.
declare
type nt_table_type is table of number;
nt1 nt_table_type:=nt_table_type(1,2,3);
nt2 nt_table_type:=nt_table_type(1,2,3,4);
begin
if nt1 submultiset of nt2 then
dbms_output.put_line(‘nt1是nt2的子集);
end if;
end;
使用操作符MEMBER OF :用于检测特定数据是否为嵌套表的元素.
declare
type nt_table_type is table of number;
nt1 nt_table_type:=nt_table_type(1,2,3,5);
v1 number:=&v1;
begin
if v1 MEMBER OF nt1 then
dbms_output.put_line(‘v1是nt1的元素’);
end if;
end;
使用操作符IS A SET:用于检测嵌套表是否包含重复的元素值
declare
type nt_table_type is table of number;
nt1 nt_table_type:=nt_table_type(1,2,3,5);
begin
if nt1 is a set then
dbms_output.put_line(‘嵌套表NT1无重复值’);
end if;
end;

8.5批量绑定
当在select,insert,update,delete语句上处理批量数据时,通过批量绑定,可以极大的加快数据处理速度,提高应用程序性能
批量绑定是使用BULK COLLECT子句和FORALL语句来完成的
BULK COLLECT 子句用于取得批量数据,该子句只能用于SELECT语句,FETCH语句和DML返回子句
FORALL只适用于执行批量的DML操作
1.不使用批量绑定
declare
type id_table_type is table of number(6) index by binary_integer;
type name_table_type is table of varchar2(10) index by binary_integer;
id_table id_table_type;
name_table name_table_type;
start_time number(10);
end_time number(10);
begin
for i in 1..5000 loop
id_table(i):=i;
name_table(i);=’name’||to_char(i);
end loop;
start_time:=dbms_utility.get_time;
for i in 1..id_table.count loop
insert into demo values(id_table(i),name_table(i));
end loop;
end_time:=dbms_utility.get_time;
dbms_output.put_line(‘总计时间(秒):’||to_char((end_time-start_time)/100));
end;
2.使用批量绑定
declare
type id_table_type is table of number(6) index by binary_integer;
type name_table_type is table of varchar2(10) index by binary_integer;
id_table id_table_type;
name_table name_table_type;
start_time number(10);
end_time number(10);
begin
for i in 1..5000 loop
id_table(i):=i;
name_table(i);=’name’||to_char(i);
end loop;
start_time:=dbms_utility.get_time;
forall i in 1..id_table.count
insert into demo values(id_table(i),name_table(i));
end_time:=dbms_utility.get_time;
dbms_output.put_line(‘总计时间(秒):’||to_char((end_time-start_time)/100));
end;

8.6 FORALL语句
执行批量insert,update,delete操作时,使用forall语句,FORALL不是循环语句
oracle9i当使用FORALL语句时,必须具有连续的元素
oracle10g通过使用indices of和values of子句,可以使用不连续的集合元素.
forall三种执行语法:
forall index in lower_bound..upper_bound sql_statement;
forall index in indices of collection [between lower_bond.and.upper_bound] sql_statement;
forall index in values of index_collection sql_statement;
1.在insert语句上使用批量绑定
declare
type id_table_type is table of number(6) index by binary_integer;
type name_table_type is table of varchar2(10) index by binary_integer;
id_table id_table_type;
name_table name_table_type;
begin
for i in 1..10 loop
id_table(i):=i;
name_table(i):=’name’||to_char(i);
end loop;
forall i in 1..id_table.count
insert into demo values(id_table(i),name_table(i));
end;
2.在update语句上使用批量绑定
declare
type id_table_type is table of number(6) index by binary_integer;
type name_table_type is table of varchar2(10) index by binary_integer;
id_table id_table_type;
name_table name_table_type;
begin
for i in 1..5 loop
id_table(i):=i;
name_table(i):=’n’||to_char(i);
end loop;
forall i in 1..id_table.count
update demo set name=name_table(i) where id=id_table(i);
end;
3.在DELETE语句上使用批量绑定
declare
type id_table_type is table of number(6) index by binary_integer;
id_table id_table_type;
begin
for i in 1..3 loop
id_table(i):=i;
end loop;
forall i in 1..id_table.count
delete from demo where id=id_table(i);
end;
4.在FORALL语句中使用部分集合元素
declare
type id_table_type is table of number(6) index by binary_integer;
id_table id_table_type;
begin
for i in 1..10 loop
id_table(i):=11-i;
end loop;
forall i in 8..10
insert into demo (id) values (id_table(i));
end;
5.在FORALL语句上使用INDECES OF子句:用于跳过NULL集合元素
declare
type id_table_type is table of number(6);
id_table id_table_type;
begin
id_table:=id_table_type(1,null,3,null,5);
forall i in indices of id_table
delete from demo where id=id_table(i);
end;

6.在FORALL语句上使用VALUES OF子句
create table new_demo as select * from demo where 1=0
declare
type id_table_type is table of demp.id%type;
type name_table_type is table of demo.name%type;
id_table id_table_type;
name_table name_table_type;
type index_pointer_type is table of pls_integer;
index_pointer index_pointer_type;
begin
select * bulk collect into id_table,name_table from demo;
index_pointer:=index_pointer_type(6,8,10);
forall i in values of index_pointer
insert into new_demo values(id_table(i),name_table(i));
end;
7.使用SQL%BULK_ROWCOUNT属性:专门为FORALL语句提供,用于取得在执行批量绑定操作时第i个元素所作用的行数
declare
type dno_table_type is table of number(3);
dno_table dno_table_type:=dno_table_type(10,20);
begin
forall i in 1..dno_table.count
update emp set sal=sal*1.1 where deptno=dno_table(i);
dbms_output.put_line(‘第2个元素更新的行数:’||sql%bulk_rowcount(2));
end;

8.7 BULK COLLECT子句
用于取得批量数据，只适用于select into 语句,fetch into 语句和DML返回子句
可将批量数据存放到PL/SQL集合变量中
1.在select into 语句中使用BULK COLLECT 子句:可以一次将SELECT语句的多行结果检索到集合变量中
declare
type emp_table_type is table of emp%rowtype index by binary_integer;
emp_table emp_table_type;
begin
select * bulk collect into emp_table from emp where deptno=&no;
for i in 1..emp_table.count loop
dbms_output.put_line(‘雇员姓名:’||emp_table(i).ename);
end loop;
end;
2.在DML的返回语句中使用BULK COLLECT子句
为了取得DML操作所改变的数据,可以使用RETURNING子句.
declare
type ename_table_type is table of emp.ename%type;
ename_table ename_table_type;
begin
delete from emp where deptno=&no;
returning ename bulk collect into ename_table;
dbms_output.put(‘雇员名’);
for i in 1..ename_table.count loop
dbms_output.put(ename_table(i)|| ’ ‘);
end loop;
dbms_output.new_line;
end;

9.使用游标
9.1当在PL/SQL块中执行查询语句SELECT和数据操纵语句DML时,ORACLE会为其分配上下文区(CONTEXT AREA),游标指上下文区指针
对于数据操纵语句和单行SELECT INTO语句来说,ORACLE会为他们分配隐含游标.
使用显示游标处理多行数据,也可使用SELECT..BULK COLLECT INTO 语句处理多行数据.
1.显示游标
定义游标
cursor cursor_name is select_statement;
2.打开游标:执行对应的SELECT语句并将SELECT语句的结果暂时存放到结果集中.
open cursor_name;
3.提取数据
打开游标后,SELECT语句的结果被临时存放到游标结果集中,使用FETCH语句只能提取一行数据
通过使用FETCH..BULK COLLECT INTO语句每次可以提取多行数据
fetch cursor_name into variable1,varibale2,…;
fetch cursor_name bulk collect into collect1,collect2,…[limit rows];
4.关闭游标
close cursor_name;

9.2显示游标属性
用于返回显示游标的执行信息,包括%isopen,%found,%notfound,%rowcount
1.%isopen:确定游标是否打开 if cl%isopen then … else open c1; end if;
2.%found:检查是否从结果集中提取到了数据
loop
fetch c1 into var1,var2;
if c2%found then … else exit;
end loop;
3.%notfound
loop
fetch c1 into var1,var2;
exit when c2%notfound;
…
end loop;
4.%rowcount:返回当前行为止已经提取到的实际行数
loop
fetch c1 into my_ename,my_deptno;
if c1%rowcount>10 then
…
end if;
…
end loop;

9.3显示游标使用示例
1.在显示游标中使用fetch..into语句:只能处理一行数据,除非用循环语句
declare
cursor emp_cursor is select ename,sal from emp where deptno=10;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_ename,v_sal;
exit when emp_cursor%notfound;
dbms_output.put_line(v_ename||’: ‘||v_sal);
end loop;
close emp_cursor;
end;
2.在显示游标中,使用FETCH..BALK COLLECT INTO语句提取所有数据
declare
cursor emp_cursor is select ename from emp where deptno=10;
type ename_table_type is table of varchar2(10);
ename_table ename_table_type;
begin
open emp_cursor;
fetch emp_cursor bulk collect into ename_table;
for i in 1..ename_table.count loop
dbms_output.put_line(ename_table(i));
end loop;
close emp_cursor;
end;
3.在显示游标中使用FETCH..BULK COLLECT INTO ..LIMIT语句提取部分数据
declare
type name_array_type is varray(5) of varchar2(10);
name_array name_array_type;
cursor emp_cursor is select ename from emp;
rows int:=5;
v_count int:=0;
begin
open emp_cursor;
loop
fetch emp_cursor bulk collect into name_array limit rows;
dbms_output.pur(‘雇员名’);
for i in 1..(emp_currsor%rowcount-v_count) loop
dbms_output.put(name_array(i)||’ ‘);
end loop;
dbms_output.new_line;
v_count:=emp_cursor%rowcount;
exit when emp_cursor%notfound;
end loop;
close emp_cursor;
end;
4.使用游标属性
declare
cursor emp_cursor is select ename from emp where deptno=10;
type ename_table_type is table of varchar2(10);
ename_table ename_table_type;
begin
if not emp_cursor%isopen then
open emp_cursor;
end if;
fetch emp_cursor bulk collect into ename_table;
dbms_output.put_line(‘提取的总计行数:’||emp_cursor%rowcount);
close emp_cursor;
end;
5.基于游标定义记录变量
declare
cursor emp_cursor is select ename,sal from emp;
emp_record emp_cursor%rowtype;
begin
open emp_cursor;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
dbms_output.put_line(‘雇员名:’||emp_record.ename||’,雇员工资:’||emp_record.sal);
end loop;
end;

9.4参数游标
定义参数游标时,游标参数只能指定数据类型,而不能指定长度.
cursor cursor_name(parameter_name datatype) is select_statment;
declare
cursor emp_cursor(no number) is select ename from emp where deptno=no;
v_ename emp.ename%type;
begin
open emp_cursor(10);
loop
fetch emp_cursor into v_ename;
exit when emp_cursor%notfound;
dbms_output.put_line(v_ename);
end loop;
close emp_cursor;
end;
9.5使用游标更新或删除数据
要通过游标更新或删除数据,在定义游标时必须要带有FOR UPDATE子句
cursor cursor_name(parameter_name datetype) is select_statement for update [of column_reference] [nowait];
for update子句用于在游标结果集数据上家行共享锁,防止其他用户在相应行执行DML操作
of子句确定哪些表要加锁,没有OF子句,则在所引用的全部表上加锁
nowait子句用于指定不等待锁
必须在UPDATE后DELETE语句中引用WHERE CURRENT OF子句
update table_name set column=.. where current of cursor_name;
delete table_name where current of cursor_name;

1.使用游标更新数据
declare
cursor emp_cursor is select ename,sal from emp for update;
v_ename emp.ename%type;
v_sal emp.sal%tyep;
begin
open emp_cursor;
loop
fetch emp_cursor into v_ename,v_oldsal;
exit when emp_cursor%notfound;
if v_oldsal<2000 then
update emp set sal=sal+100 where current of emp_cursor;
end if;
end loop;
close emp_cursor;
end;
2.使用游标删除数据
declare
cursor emp_cursor is select ename,sal,deptno from emp for update;
v_ename emp.ename%type;
v_oldsal emp.sal%type;
v_deptno emp.deptno%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_ename,v_oldsal,v_deptno;
exit when emp_cursor%notfound;
if v_deptno=30 then
delete from emp where current of emp_cursor;
end if;
end loop;
close emp_cursor;
end;
3.使用OF子句在特定表上加行共享锁
declare
cursor emp_cursor is select ename,sal,dname,emp.deptno from emp,dept where emp.deptno=dept.deptno
for update of emp.deptno;
emp_record emp_cursor%type;
begin
open emp_cursor;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
if emp_record.deptno=30 then
update emp set sal=sal+100 where current of emp_cursor;
end if;
dbms_output.put_line(‘雇员名:’||emp_record.ename||’,工资:’||emp_record.sal||’,部门名:’||emp_record.dname);
end loop;
close emp_cursor;
end;
4.使用nowait子句
通过在FOR UPDATE子句中指定NOWAIT语句,可以避免等待锁.若已经被作用行加锁,则提示错误信息
declare
cursor emp_cursor is select ename,sal from emp for update nowait;
v_ename emp.ename%type;
v_oldsal emp.sal%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_ename,v_sal;
exit when emp_cursor%notfound;
if v_oldsal<2000 then
update emp set sal=sal+100 where current of emp_cursor;
end if;
end loop;
close emp_cursor;
end;

9.6游标FOR循环
使用FOR循环时,ORACLE会隐含的打开游标,提取游标数据并关闭游标
for record_name in cursor_name loop
statement1;
statement2;
…
end loop;
每循环一次提取一次数据，在提取了所有数据后,自动退出循环并隐含的关闭游标
1.使用游标FOR循环
declare
cursor emp_cursor is select ename,sal from emp;
begin
for emp_record in emp_cursor loop
dbms_output.put_line(‘第’||emp_curosr%rowcount||’个雇员: ‘||emp_record.ename);
end loop;
end;
2.在游标FOR循环中直接使用子查询
begin
for emp_record in (select ename,sal from emp) loop
dbms_output.put_line(emp_record.ename);
end loop;
end;
9.7使用游标变量
PL/SQL的游标变量中存放着指向内存地址的指针.

1.游标变量使用步骤
包括定义游标变量,打开游标,提取游标数据,关闭游标等四个阶段
1.1定义ref cursor类型和游标变量
type ref_type_name is ref cursor [return return_type];
cursor_varibale ref_type_name;
当指定RETURN子句时,其数据类型必须是记录类型,不能在包内定义游标变量
1.2打开游标
open cursor_variable for select_statement;
1.3提取游标数据
fetch cursor_varibale into variable1,variable2,…;
fetch cursor_varibale bulk collect into collect1,collect2,…[limit rows]
1.4关闭游标变量
close cursor_varibale;
2.游标变量使用示例
2.1在定义FEF CURSOR类型时不指定RETURN子句
在打开游标时可以指定任何的SELECT语句
declare
type emp_cursor_type is ref cursor;
emp_cursor emp_cursor_type;
emp_record emp%rowtype;
begin
open emp_cursor for select * from emp where deptno=10;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
dbms_output.put_line(‘第’||emp_curosr%rowcount||’个雇员: ‘||emp_record.ename);
end loop;
close emp_cursor;
end;
2.2在定义REF CURSOR类型时指定RETURN子句
在打开游标时SELECT语句的返回结果必须与RETURN子句所指定的记录类型相匹配.
declare
type emp_record_type is record(name varchar2(10),salary number(6,2));
type emp_cursor_type is ref cursor return emp_record_type;
emp_cursor emp_cursor_type;
emp_record emp_record_type;
begin
open emp_cursor for select ename,sal from emp where deptno=20;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
dbms_output.put_line(‘第’||emp_curosr%rowcount||’个雇员: ‘||emp_record.ename);
end loop;
close emp_cursor;
end;

9.7使用CURSOR表达式
CURSOR表达式用于返回嵌套游标
结果集不仅可以包含普通数据,而且允许包含嵌套游标的数据
cursor(subquery)
declare
type refcursor is ref cursor;
cursor dept_cursor(no number) is select a.dname,cursor(select ename,sal from emp where deptno=a.deptno)
from dept a where a.deptno=no;
empcur refcursor;
v_dname dept.dname%type;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open dept_cursor(&no);
loop
fetch dept_cursor into v_danme,empcur;
exit when dept_cursor%notfound;
dbms_output.put_line(‘部门名:’||v_dname);
loop
fetch empcur into v_ename,v_sal;
exit when empcur%notfound;
dbms_output.put_line(‘雇员名:’||v_ename||’,工资:’||v_sal);
end loop;
end loop;
close dept_cursor;
end;

10开发子程序:过程和函数
过程:执行特定操作
函数:用于返回特定数据

10.1过程
语法:create [or replace] procedure procedure_name(argument1 [model] datatype1,arguement2 [mode2],…)
is [as]
pl/sql block;
1.建立过程:不带任何参数
create or replace procecdure out_time
is
begin
dbms_output.put_line(systimestemp);
end;
2.调用过程
set serveroutput on
exec out_time
set serveroutput on
call out_time();
3.建立过程:带有IN参数
create or replace procedure add_employee
(eno number,name varchar2,sal number,job varchar2 default ‘clerk’,dno number)
is
e_integrity exception;
pragma exception_init(e_integrity,-2291);
begin
insert into imp(empno,ename,sal,job,deptno) valres(eno,name,sal,job,dno);
exception
when dup_val_on_index then
raise_application_error(-20000,’雇员号不能重复’);
when e_integrity then
raise_application_error(-20001,’部门不存在’);
end;
exec add_employee(1111,’clark’,2000,’manager’,10)
4.建立过程:带有OUT参数
create or replace procedure query_employee
(eno number,name out varchar2,salary out number)
is
begin
select ename,sal into name,salary from emp where empno=eno;
exception
when no_date_found then
raise_application_error(-20000,’该雇员不存在’);
end;
当在应用程序中调用该过程时,必须要定义变量接受输出参数的数据
sql>var name varchar2(10)
var salary number
exec query_employee(7788,:name,:salary)
print name salary

5.建立过程:带有IN OUT参数(输入输出参数)
create or replace procedure compute
(num1 in out number,num2 in out number)
is
v1 number;
v2 number;
begin
v1:num1/num2;
v2:mod(num1,num2);
num1:=v1;
num2:=v2;
end;
sql>var n1 number
var n2 number
exec :n1:=100
exec :n2:=30
exec ecmpute(:n1,:n2)
print n1 n2
6.为参数传递变量和数据
位置传递,名称传递,组合传递三种
1.位置传递:在调用子程序时按照参数定义的顺序为参数指定相应的变量或数值
exec add_dept(40,’sales’,’new york’);
exec add_dept(10);
2.名称传递:在调用子程序时指定参数名,并使用关联符号=>为其提供相应的数值或变量
exec add_dept(dname=>’sales’,dno=>50);
exec add_dept(dno=>30);
3.组合传递:同时使用位置传递和名称传递
exec add_dept(50,loc=>’new york’);
exec add_dept(60,dname=>’sales’,loc=>’new york’);
7.查看过程原代码
oracle会将过程名,源代码以及其执行代码存放到数据字典中.执行时直接按照其执行代码执行
可查询数据字典(user_source)
select text from user_source where name=’add_dept’;

删除过程
drop procedure add_dept;

10.2函数
用于返回特定函数
语法:create [or replace] function function_name
(argument1 [mode1] datatype1,
argument2 [mode2] datatype2,
…..)
return datatype –函数头部必须要带有RETURN子句,至少要包含一条RETURN语句
is|as pl/sql block;

1.建立函数:比带任何参数
create or replace function get_user
return varchar2
is
v_user varchar2(100);
begin
select username into v_user from user_users;
return v_user;
end;
2.使用变量接受函数返回值
sql>var v1 varchar2(100)
exec :v1:=get_user
print v1
在SQL语句中直接调用函数
select get_user from dual;
使用DBMS_OUTPUT调用函数
set serveroutput on
exec dbms_output.put_line(‘当前数据库用户:’||ger_user)

3.建立函数:带有IN参数
create or replace function get_sal(name in varchar2)
return number
as
v_sal emp.sal%type;
begin
select sal into v_sal from emp where upper(ename)=upper(name);
return v_sal;
exception
when no_data_found then
raise_application_error(-20000,’该雇员不存在’);
end;
4.建立函数:带有out参数
create or replace function get_info(name varchar2,title out varchar2)
return varchar2
as
deptname dept.dname%type;
begin
select a.job,b.dname into title,deptname from emp a,dept b and a.deptno=b.deptno
and upper(a.ename)=upper(name);
return deptname
exception
when no_data_found then
raise_application_error(-20000,’该雇员不存在’);
end;
sql>var job varchar2(20)
var dname varchar2(20)
exec :dname:=get_info(‘scott’,:job)
print danme job
5.建立函数:带有IN OUT参数
create or replace function result(num1 number,num2 in out number)
return number
as
v_result number(6);
v_remainder number;
begin
v_result:=num1/num2;
v_remainder:=mod(num1,num2);
num2:=v_remainder;
return v_result;
exception
when zero_divide then
raise_application_error(-20000,’不能除0’);
end;
sql>var result1 number
var result2 number
exec :result2:=30
exec :result1:=result(100,:result2)
print result result2
6.函数调用限制
SQL语句中只能调用存储函数(服务器端),而不能调用客户端的函数
SQL只能调用带有输入参数,不能带有输出,输入输出函数
SQL不能使用PL/SQL的特有数据类型(boolean,table,record等)
SQL语句中调用的函数不能包含INSERT,UPDATE和DELETE语句
7.查看函数院源代码
oracle会将函数名及其源代码信息存放到数据字典中user_source
set pagesize 40
select text from user_source where name=’result’;
8.删除函数
drop function result;

10.3管理子程序
1.列出当前用户的子程序
数据字典视图USER_OBJECTS用于显示当前用户所包含的所有对象.(表,视图,索引,过程,函数,包)
sql>col object_name format a20
select object_name,created,status from user_objects where object_type in (‘procedure’,’function’)

2.列出子程序源代码
select text from user_source where name=’raise_salsry’;
3.列出子程序编译错误
使用SHOW ERRORS命令确定错误原因和位置
show errors procedure raise_salary

使用数据字典视图USER_ERRORS确定错误原因和位置
col text format a50
select line||’/’||position as “line/col”,text error from user_errors where name=’raise_salary’;
4.列出对象依赖关系
使用数据字典视图USER_DEPENDENCIES确定直接依赖关系
select name,type from user_dependencies where referenced_name=’emp’;
使用工具视图DEPTREE和IDEPTREE确定直接依赖和间接依赖关系
先运行SQL脚本UTLDTREE.SQL来建立这两个视图和过程DEPTREE_FILL,然后调用DEPTREE_FILL填充这两个视图
sql>@%oracle_home%\rdbms\admin\utldtree
exec deptree_fill(‘TABLE’,’scott’,’emp’)
执行后会将直接或间接依赖于SCOTT.EMP表的所有对象填充到视图DEPTREE和IDEPTREE中.
select nested_level,name,type from deptree;
select * from ideptree
5.重新编译子程序
当修改了被引用对象的结构时,就会将相关依赖对象转变为无效(INVALID)状态。
alter table emp add remark varchar2(10);
select object_name,object_type from user_objects where status=’invalid’;
为了避免子程序的运行错误,应该重新编译这些存储对象
alter procedure add_employee compile;
alter view dept10 compile;
alter function get_info compile;

11.开发包
包用于逻辑组合相关的PL/SQL类型,项和子程序,由包规范和包体组成
1.建立包规范:包规范是包与应用程序之间的接口,用于定义包的公用组件,包括常量,变量,游标,过程,函数等
create [or replace] package package_name
is|as
public type and item declarations
subprogram specificationsend package_name;
create or replace package emp_package is
g_deptno number(3):=30;
procedure add_employee(eno number,name varchar2,salary number,dno number default g_deptno);
procedure fire_employee(eno number);
function get_sal(eno number) return number;
end emp_package;
2.建立包体:用于实现包规范所定义的过程和函数
create [or replace] package body package_name
is|as
private type and item declarations
subprogram bodies
end package_name;
create or repalce package body emp_package is
function validate_deptno(v_deptno number)
return boolean
is
v_temp int;
begin
select 1 into v_temp from dept where deptno=v_deptno;
return true;
exception
when no_date_found then
return false;
end;

procedure add_employee(eno number,name varchar2,salary number,dno number default g_deptno)
is
begin
  if validate_deptno(dno) then
     insert into emp(empno,ename,sal,deptno) values(eno,name,salsry,dno);
  else
     raise_application_error(-20010,'不存在该部门');
  end if;
exception
  when dup_val_on_index then
     raise_application_error(-20012,'该雇员已存在');
end;

procedure fire_employee(eno number) is
begin
  delete from emp where empno=eno;
  if sql%notfound then 
     raise_application_error(-20012,'该雇员不存在');
  end if;
end;

function get_sal(eno number) return number
is
  v_sal emp.sal%type;
begin
  select sal into v_sal from emp where empno=eno;
  return v_sal;
exception
  when no_data_found then
    raise_application_error(-20012,'该雇员不存在');
end;

end emp_package;
3.调用包组件
3.1在同一个包内调用包组件
create or replace package body emp_package is

procedure add_employee(eno number,name vauchar2,salary number,dno number default g_deptno)
is
begin
if validate_deptno(dno) then
insert into emp(empno,ename,sal,deptno) values(eno,name,salary,dno);
else
raise_application_error(-20010,’该部门不存在’)
end if;
exception
when dup_val_on_index then
raise_application_error(-20011,’该雇员已存在’)
end;
………
3.2调用包公用变量
exec emp_package.g_deptno:=20
3.3调用包公用过程
exec emp_package.add_employee(1111,’mary’,2000)
3.4调用包公用函数
var salary number
exec :salary:=emp_package.get_sal(7788)
print salary
3.5以其他用户身份调用包公用组件
conn system/manager
exec scott.emp_package.add_employee(1115,’scott’,1200)
exec scott.emp_package.fire_employee(1115)
3.6调用远程数据库包的公用组件
exec emp_package.add_employee@orasrv(1116,’scott’,1200)

4.查看源代码:存放在数据字典USER_SCOURCE中
select text from user_source where name=’emp-package’ and type=’package’;
5.删除包
drop package emp_package;

6.使用包重载
重载(overload)是指多个具有相同名称的子程序
1.建立包规范
同名的过程和函数必须具有不同的输入参数,同名函数返回值的数据类型必须完全相同
create or replace package overload is
function get_sal(eno number) return number;
function get_sal(name varchar2) return number;
procedure file_employee(eno number);
procedure file_employee(name varchar2);
end;
2.建立包体
必须要给不同的重载过程和重载函数提供不同的实现代码
create or replace package body overload is

function get_sal(eno number) return number
is 
  v_sal emp.sal%type;
begin
  select sal into v_sal from emp where empno=eno;
  return v_sal;
exception
  when no_data_found then
    raise_application_error(-20020,'该雇员不存在');
end;
function get_sal(name varchar2) return  number
is
  v_sal emp.sal%type;
begin
  select sal into v_sal from emp where upper(ename)=upper(name);
  return v_sal;
exception
  when no_data_found then
    raise_application_error(-20020,'该雇员不存在');
end;
procedure fire_employee(eno number) is
begin
  delete from emp where empno=no;
  if sql%notfound then
    raise_application_error(-20020,'该雇员不存在');
  end if;
end;
procedure fire_employee(name varchar2) is
begin
  delete from emp where upper(ename)=upper(name);
  if sql%notfound then
    raise_application_error(-20020,'该雇员不存在');
  end if;
end;

end;

3.调用重载过程和重载函数
var sal1 number
var sal2 number
exec :sal1:=overload.get_sal(‘scott’)
exec :sal2:=overload.get_sal(7685)
exec overload.fire_employee(7369)
exec overload.fire_employee(‘scott’)
7.使用包构造过程
类似于高级语言中的构造函数和构造方法
1.建立包规范
包的构造过程用于初始化包的全局变量.
create or replace package emp_package is
minsal number(6,2);
maxsal number(6,2);
procedure add_employee(eno number,name varchar2,salary number,dno number);
procedure upd_sal(eno number,salary number);
procedure upd_sal(name varchar2,salary number);
end;

2.建立包体
包的构造过程没有任何名称,它是实现了包的其他过程后,以BEGIN开始,END结束的部分
create or replace package body emp_package is

procedure add_employee(eno number,name varchar2,salary number,dno number)
is
begin
if salary between minsal and maxsal then
  insert into emp (empno,ename,sal,deptno) values(eno,name,salary,dno);
else
  raise_application_error(-20001,'工资不在范围内');
end if;
exception
  when dup_val_on_index then
    raise_application_error(-20002,'该雇员已经存在');
end;

procedure upd_sal(eno number,salary number) is
begin
  if salary between minsal and maxsal then
     update emp set sal=salary where empno =eno;
     if sql%notfound then
       raise_application_error(-20003,'不存在雇员号');
     end if;
  else
     raise_application_errpr(-20001,'工资不在范围内');
  end if;

end;
procedure upd_sal(name varchar2,salary number) is
begin
if salary between minsal and maxsal then
update emp set sal=salary where upper(ename)=upper(name);
if sql%notfound then
raise_application_error(-20004,’不存在该雇员名’);
end if;
else
raise_application_error(-20001,’工资不在范围内’);
end if;
end;
begin
select mi(sal),max(sal) into minsal,maxsal from emp ;
end;
调用包公用组件:构造过程只调用一次
exec emp_package.add_employee(1111,’mary’,3000,20)
exec emp_package.upd_sal(‘mary’,2000)

8.使用纯度级别
在SQL中引用包的公用函数，该公用函数不能包含DML语句(insert,update,delete),也不能读写远程包的变量
为了对包的公用函数加以限制,在定义包规范时,可以使用纯度级别(purity level)限制公用函数
语法:pragma restrict_references (function_name,wnds[,wnps][,rnds][,rnps]);
wnds:用于限制函数不能修改数据库数据(禁止DML)
wnps:用于限制函数不能修改包变量(不能给包变量赋值)
rnds:用于限制函数不能读取数据库数据(禁止SELECT操作)
rnps:用于限制函数不能读取包变量(不能将包变量赋值给其他变量)
1.建立包规范
create or replace package purity is
minsal number(6,2);
maxsal number(6,2);
function max_sal return number;
function min_sal return number;
pragma restrict_references(max_sal,wnps);–不能修改
pragma restrict_references(min_sal,wnps);
end;

2.建立包体
create or replace package body purity is
function max_sal return number
is
begin
return maxsal;
end;

function min_sal return number
is
begin
return minsal;
end;

begin
select min(sal),max(sal) into minsal,maxsal from emp;
end;
3.调用包的公用函数
var minsal number
var maxsal number
exec :minsal:=purity.minsal()
exec :maxsal:=purity.maxsal()
print minsal maxsal

12.触发器
存放在数据库中，并被隐含执行的存储过程.
可基于表和视图的DML(INSERT,UPDATE,DELETE),系统事件(启动,关闭,登陆数据库)和DDL操作建立触发器.
触发器由触发事件,触发条件,触发操作三部分组成
1.1触发事件
启动和关闭数据库,ORACLE错误消息,用户登录和断开会话,特定表或视图的DML操作,在任何方案上的DDL语句

1.2.触发条件(可选)
指使用WHEN子句指定一个BOOLEAN表达式,返回为TRUE时,触发

1.3.触发操作
指包含SQL语句和其他执行代码的PL/SQL块.
触发器的代码大小不能超过32K.(可使用CALL语句调用存储过程)
出发器只能包含SELECT,INSERT,UPDATE,DELETE语句,不能包含DDL语句(CREATE,ALTER,DROP)和事务控制语句(COMMIT,ROLLBACK,SAVEPOINT)

2.建立DML触发器
当建立DML触发器时,需要指定触发时机(BEFORE,AFTER),触发事件(INSERT,UPDATE,DELETE),表名,触发类型,触发条件,操作.
2.1触发时机
指定触发器的触发时间,之前(BEFORE),之后(AFTER)
2.2触发事件
指定导致触发器执行的DML操作,也即INSERT,UPDATE,DELTE操作
2.3表名
必须指定DML操作所对应的表
2.4触发类型
指定触发事件发生后,需要执行几次触发操作,默认执行一次触发器代码
如果指定行触发类型,则会在每个被作用行上执行一次触发器代码
2.5触发条件
指定执行触发器代码的条件
当编写DML触发器时,只允许在行触发器上指定触发条件
2.6触发操作
指定触发器执行代码.(若使用PL/SQL,JAVA,或外部存储过程,可直接使用CALL调用相应过程)
2.7DML触发器触发顺序
DML触发器在单行数据上的触发顺序
对于单行数据而言,无论是语句触发器,还是行触发器,触发器代码实际只被执行一次,顺序为:
before语句,before行,dml操作,after行,after语句

DML触发器在多行数据上的触发顺序
对于多行数据而言,语句触发器只被执行一次,而行触发器在每个作用行上都执行一次

3.语句触发器
当执行DML操作时会自动执行触发器的相应代码.
使用语句触发器时,不能记录列数据的变化
语法:create [or replace] trigger trigger_name
timing event1 [or event2 or event3]
on table_name
pl/sql block
3.1建立BEFORE语句触发器
create or replace trigger tr_sec_emp
before insert or update or delete on emp
begin
if to_char(sysdate,’DY’,’nls_date_language=AMERICAN’) IN (‘SAT’,’SUN’) THEN
raise_application_error(-20001,’不能在休息日改变雇员信息’);
end if;
end;
3.2使用条件谓词:inserting,updating,deleting是该操作时返回TRUE,否则FALSE
create or replace trigger tr_sec_emp
before insert or update or delete on emp
begin
if to_char(sysdate,’DY’,’nls_date_language=AMERICAN’) IN (‘SAT’,’SUN’) THEN
CASE
WHEN INSERTING THEN
raise_application_error(-20001,’不能在休息日增加雇员’);
WHEN UPDATING THEN
raise_application_error(-20002,’不能在休息日更新雇员’);
WHEN DELETING THEN
raise_application_error(-20003,’不能在休息日解雇雇员’);
end case;
end if;
end;

4.建立AFTER语句触发器
为了审计DML操作,或者在DML操作之后执行汇总运算.(计算INSERT,UPDATE,DELETE的操作次数)
create table audit_table(name varchar2(20),ins int,upd int,del int,starttime date,endtime date);

create or replace trigger tr_audit_emp
after insert or update or delete on emp
declare
v_temp int;
begin
select count(*) into v_temp from audit_table where name=’emp’;
if v_temp=0 then
insert into audit_table values(‘emp’,0,0,0,sysdate,null);
end if;
case
when inserting then update audit_table set ins=ins+1,endtime=sysdate where name=’emp’;
when updating then update audit_table set upd=upd+1,endtime=sysdate where name=’emp’;
when deleting then update audit_table set del=del+1,endtime=sysdate where name=’emp’;
end case;
end;
5.行触发器
执行DML操作时,每作用一行就触发一次的触发器.审计数据变化时,可以使用行触发器
create [or replace] trigger trigger_name
timing event1 [or event2 or event3]
on table_name
[referencing old as old |new as new]
for each row
[when condition]
pl/sql block;
5.1建立BEFORE行触发器
create or relpace trigger tr_emp_sal
before update of sal on emp
for each row
begin
if :new.sal<:old.sal then
raise_application_error(-20010,’工资只涨不降’);
end if ;
end;
5.2建立AFTER行触发器
为了审计DML操作数据变化，则应该使用AFTER行触发器
create table audit_emp_change(name varchar2(10),oldsal number(6,2),newsal number(6,2),time date);

create or replace trigger tr_sal_change
after update of sal on emp
for each row
declare
v_temp int;
begin
select count(*) into v_temp from audit_emp_change where name=:old.ename;
if v_temp=0 then
insert into audit_emp_change values(:old.ename,:old.sal,:new,sal,sysdate);
else
update audit_emp_change set oldsal=:old.sal,newsal=:new.sal,time=sysdate where name=:old.ename;
end if;
end;
5.3限制行触发器
使用行触发器时,默认情况下会在每个被作用行上执行一次触发器代码,为了使得在特定条件下执行行触发器代码,就需要使用WHEN子句对触发器加以限制
create or replace trigger tr_sal_change
after update of sal on emp
for each row
when (old.job=’salesman’)
declare
v_temp int;
begin
select count(*) into v_temp from audit_emp_change where name=:old.ename;
if v_temp=0 then
insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,sysdate);
else
update audit_emp_change set oldsal=:old.sal,newsal=:new.sal,time=sysdate where name=:old.ename;
end if;
end;
5.4DML触发器使用注意事项
编写DML触发器时,不能从触发器所对应的基表中读取数据.建立时不会出现错误,但在执行相应触发操作时会显示错误信息

6.使用DML触发器
DML触发器可以用于实现数据安全保护,数据审计,数据完整性,参照完整性,数据复制等
6.1控制数据安全
在服务器级控制数据安全是通过授予和收回对象权限来完成的.
conn scott/tiger
grant select,insert,update,delete on emp to smith;
create or replace trigger tr_emp_time
before insert or update or delete on emp
begin
if to_char(sysdate,’hh24’) not between ‘9’ and ‘17’ then
raise_application_error(-20101,’非工作时间’);
end if;
end;
6.2实现数据审计
审计可以用于监视非法和可疑的数据库活动.ORACLE数据库本身提供了审计功能
对EMP表上的DML操作进行审计
audit insert,update,delete on emp by access;
只能审计SQL操作,写入到数据字典中,而不会记载数据变化.为了审计SQL操作引起的数据变化,使用DML触发器
create or replace trigger tr_sal_change
after update of sal on emp
for each row
declare
v_temp int;
begin
select count(*) into v_temp from audit_emp_change where name=:old.ename;
if v_temp=0 then
insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,sysdate);
else
update audit_emp_change set oldsal=:old.sal,newsal=:new.sal,time=sysdate where name=:old.ename;
end if;
end;
6.3实现数据完整性
数据完整性用于确保数据库数据满足特定的商业逻辑或企业规则.
CHECK约束:alter table emp add constraint ck_sal check (sal>=800);
create or replace trigger tr_check_sal
before update of sal on emp
for each row
when (new.sal

时间

2001/01/07 23:51:21
2001/01/07 23:52:22
2001/01/07 23:53:24
删除JOB
SQL> begin
2 dbms_job.remove(:job1);
3 end;
4 /
PL/SQL 过程已成功完成。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

a、利用dbms_job.run()立即执行该job
sql>begin
sql>dbms_job.run(:jobno) 该jobno为submit过程提交时返回的job number
sql>end;
sql>/
b、利用dbms_job.broken()重新将broken标记为false
sql>begin
sql>dbms_job.broken (:job,false,next_date)
sql>end;
sql>/
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
SQL> create table a(a date);
Table created
创建一个过程
SQL> create or replace procedure test as
2 begin
3 insert into a ＆#118alues(sysdate);
4 end;
5 /
Procedure created
提交作业
SQL> declare
2 job1 number; //定义一个数字型变量
3 begin
4 dbms_job.submit(:job1,’test;’,sysdate,’sysdate+1/1440’); //按分钟算一天1440分钟
5 end;
6 /
PL/SQL procedure successfully completed

job1

4
SQL> commit;
Commit complete
运行作业
SQL> begin
2 dbms_job.run(4);
3 end;
4 /
PL/SQL procedure successfully completed
删除作业
SQL> begin
2 dbms_job.remove(4);
3 end;
4 /
PL/SQL procedure successfully completed
SQL> commit;
Commit complete

job change//修改作业
execute dbms_job.change(186,null,null,’sysdate+3’);
execute dbms_job.change(186,’scott.test(update)’);
DBA_JOBS
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
字段（列）类型描述
JOB NUMBER 任务的唯一标示号
LOG_USER VARCHAR2(30) 提交任务的用户
PRIV_USER VARCHAR2(30) 赋予任务权限的用户
SCHEMA_USER VARCHAR2(30) 对任务作语法分析的用户模式
LAST_DATE DATE 最后一次成功运行任务的时间
LAST_SEC VARCHAR2(8) 如HH24:MM:SS格式的last_date日期的小时，分钟和秒
THIS_DATE DATE 正在运行任务的开始时间，如果没有运行任务则为null
THIS_SEC VARCHAR2(8) 如HH24:MM:SS格式的this_date日期的小时，分钟和秒
NEXT_DATE DATE 下一次定时运行任务的时间
NEXT_SEC VARCHAR2(8) 如HH24:MM:SS格式的next_date日期的小时，分钟和秒
TOTAL_TIME NUMBER 该任务运行所需要的总时间，单位为秒
BROKEN VARCHAR2(1) 标志参数，Y标示任务中断，以后不会运行
INTERVAL VARCHAR2(200) 用于计算下一运行时间的表达式
FAILURES NUMBER 任务运行连续没有成功的次数
WHAT VARCHAR2(2000) 执行任务的PL/SQL块
CURRENT_SESSION_LABEL RAW MLSLABEL 该任务的信任Oracle会话符
CLEARANCE_HI RAW MLSLABEL 该任务可信任的Oracle最大间隙
CLEARANCE_LO RAW MLSLABEL 该任务可信任的Oracle最小间隙
NLS_ENV VARCHAR2(2000) 任务运行的NLS会话设置
MISC_ENV RAW(32) 任务运行的其他一些会话参数

描述 INTERVAL参数值
每天午夜12点 ‘TRUNC(SYSDATE + 1)’
每天早上8点30分 ‘TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)’
每星期二中午12点 ‘NEXT_DAY(TRUNC(SYSDATE ), ”TUESDAY” ) + 12/24’
每个月第一天的午夜12点 ‘TRUNC(LAST_DAY(SYSDATE ) + 1)’
每个季度最后一天的晚上11点 ‘TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), ‘Q’ ) -1/24’
每星期六和日早上6点10分 ‘TRUNC(LEAST(NEXT_DAY(SYSDATE, ”SATURDAY”), NEXT_DAY(SYSDATE, “SUNDAY”))) + （6×60+10）/（24×60）’

*****************************************************************************8
job查询：
select * from user_jobs;

创建
variable job number;
begin
sys.dbms_job.submit(job => :job,
what => ‘PK_EZS_AG_DATA.main();’,
next_date => to_date(‘20120818 09:01:00’, ‘yyyy-mm-dd hh24:mi:ss’),
interval => ‘sysdate+10/1440’);
commit;
end;
/

删除
exec dbms_job.remove(29);

停止一个JOB
SQL> exec dbms_job.broken(1,true)

启动job
SQL> exec dbms_job.broken(1,false)

执行job：
exec dbms_job.run(4);
*****************************************************************************8
alter system set job_queue_processes = 10
(根據需要可以調整為不同的值，一般10個左右就夠的了。

*****************************************************************************8
ora-00054:resource busy and acquire with nowait specified解决方法

当某个数据库用户在数据库中插入、更新、删除一个表的数据，或者增加一个表的主键时或者表的索引时，常常会出现ora-00054:resource busy and acquire with nowait specified这样的错误。

主要是因为有事务正在执行（或者事务已经被锁），所有导致执行不成功。

1、用dba权限的用户查看数据库都有哪些锁

select t2.username,t2.sid,t2.serial#,t2.logon_time
from v $locked_object t1,v$ session t2
where t1.session_id=t2.sid order by t2.logon_time;

如：testuser 339 13545 2009-3-5 17:40:05
知道被锁的用户testuser，sid为339，serial#为13545

2、根据sid查看具体的sql语句，如果sql不重要，可以kill

select sql_text from v $session a,v$ sqltext_with_newlines b
where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value
and a.sid=&sid order by piece;

查出来的sql，如： begin :id := sys.dbms_transaction.local_transaction_id; end;

3、kill该事务
alter system kill session ‘339,13545’;

4、这样就可以执行其他的事务sql语句了

如增加表的主键：
alter table test
add constraint PK_test primary key (test_NO)；

=====================================================================cmd进入：
1. D:\oracle\ora92\bin>
2. sqlplus /nolog
3. conn sys/sys as sysdba

sqlplus进入：
username：sys
password：sys
HostString： test as sysdba

用户管理
创建用户，设定权限

create user fusp identified by fusp default tablespace users temporary tablespace temp;
grant dba to fusp; –dba,recource,connect –赋权限
alter system set resource_limit=true scope=both; –期限，密码等
disconnect；
conn fusp/fusp；
show user;

创建profile
create profile px_test limit
sessions_per_user 3
idle_time 10; –单位：分钟

创建用户
create user dd identified by dd
default tablespace users
temporary tablespace temp
profile px_test;

创建的dba用户，都会存放到dba_users表中
修改用户的默认表空间：
alter user fusp default tablespace users;

查询：
select username,DEFAULT_TABLESPACE from dba_users;

启动：
SQL> startup
ORACLE instance started.

    Total System Global Area  143727516 bytes
    Fixed Size                   453532 bytes
    Variable Size             109051904 bytes
    Database Buffers           33554432 bytes
    Redo Buffers                 667648 bytes
    Database mounted.
    Database opened.

查看归档相关初始化参数
show parameter archive

创建spfile
1. startup pfile=D:\oracle\admin\test\pfile\init.ora
2. create pfile from spfile;（根据提示将相应的文件拷贝）

把归档日志文件切换:
1. alter system switch logfile;

创建表空间:
create tablespace exam datafile ‘D:\oracle\admin\test\temp\example1.dbf’ size 10M;

修改表空间：
alter tablespace exam add datafile ‘D:\oracle\admin\test\temp\example2.dbf’ size 5M;

查看表空间大小：
select tablespace_name,sum(bytes/1024/1024)
from dba_data_files group by tablespace_name;

查询动态表空间
select * from v$tablespace;

区管理方式
Select tablespace_name,extent_management from dba_tablespaces;

表空间类型
Select tablespace_name,status,contents from dba_tablespaces;

数据文件自动扩展
Select file_name, AUTOEXTENSIBLE,status,bytes/1024/1024 from dba_data_files where tablespace_name= ‘USERS’;

查看日志
select * from v $logfile; select * from v$ log;

添加group
alter database add logfile ‘d:\temp\aa.log’ size 5M;
alter database add logfile group 5 ‘d:\temp\aa1.log’ size 5M;

添加成员member
alter database add logfile member ‘d:\temp\aa2.log’ to group 4;

控制文件位置：
1.查看初始化参数：init.ora
2 打开文件找到control_files：*

生成本地Tablespace
create tablespace example02 datafile ‘c:\’ ????????????????????????????

修改表空间状态 (读和写在线与下线)
变：
alter tablespace exam read only;
alter tablespace exam read write;
查：
select tablespace_name,status,contents
from dba_tablespaces;

变：
alter tablespace exam offline;
alter tablespace exam online;
查：
select name,status from v$datafile;

创建临时表空间：
create temporary tablespace temptablespace tempfile ‘d:\temp\temptablespace.dbf’ size 5M
extent management local uniform size 100k;

付少苹
查询表数据文件实际大小
select file_name,bytes/1024/1024 from dba_data_files ;

查询表空间空闲
select tablespace_name,sum(bytes/1024/1024)
from dba_free_space group by tablespace_name;

查询临时表空间实际大小
select tablespace_name,file_name,bytes/1024/1024 from dba_temp_files;

设置行长
set linesize 100;

设置格式
col tablespace_name format a50;

查看表空间大小：
select tablespace_name,sum(bytes/1024/1024)
from dba_data_files
where tablespace_name=’EXAM’
group by tablespace_name;

修改表空间大小
alter database datafile ‘D:\oracle\admin\test\temp\example2.DBF’ resize 20M;

自动变更表空间大小
alter database datafile ‘D:\oracle\admin\test\temp\example2.DBF’ autoextend on next 1M maxsize 20M;

删除表空间：
drop tablespace example02;
including contents and datafiles;(连带数据文件一起删除)

查询表空间tablesapce：
select tablespace_name,status from dba_tablespaces;
select * from v$tablespace;

查询块segment:
select segment_name,tablespace_name from dba_segments where segment name=’DEPT’;

查询extent：
select segment_name,extent_id from dba_extents where segment_name=’DEPT’;

DBA基础操作：

创建表：
create table employee
(
empno char(5),
ename varchar2(10),
job varchar2(20),
deptno char(2)
)
–storage(initial 200k next 200k minextents 2,maxextents 100)
tablespace exam;

变更表：
alter table employee storage(next 300k maxextents 150);

alter table employee allocate extent(size 600k);

alter table employee deallocate unused;

添加column：
alter table employee add column(sal number(5));

删除里column:
1.alter table employee drop column job;

2.alter table employee set unused column job; –需要先将其置为不用，然后再进行删除，提高性能
alter table employee drop unused columns;

创建临时表：
1. create global temporary table temp1 –事务提交时，数据被删除
(a number, b number)
on commit delete rows;

create global temporary table temp2 –session结束时将其删除，与事务无关
(a number, b number)
on commit preserve rows;

删除表数据：
Truncate table employee; –不可恢复；

删除表
drop table employee;

删除表连带相关约束
drop table dept cascade constraints;

查看该用户下，所有的表：
select table_name from user_tables;

***********************************************************************8
导出数据库：
在cmd化境下：
exp scott/tiger file=d:/test1.dmp log=d:/test1.log tables=dept –tables
exp scott/tiger file=d:/test1.dmp log=d:/test1.log owner=scott –user
exp scott/tiger file=d:/test1.dmp log=d:/test1.log tablespaces=users –tablespaces
exp scott/tiger file=d:/test1.dmp log=d:/test1.log full=y –all