/**PageBeginHtml Block Begin **/ /***自定义返回顶部小火箭***/ /*生成博客目录的JS 开始*/ /*生成博客目录的JS 结束*/

oracle_基本SQL语言 

 

一：DDL数据定义语言

1:create（创建）

      创建表

  1 CREATE TABLE <table_name>(
  2 column1 DATATYPE [NOT NULL] [PRIMARY KEY],
  3 column2 DATATYPE [NOT NULL],
  4 ...
  5 [constraint <约束名> 约束类型 (要约束的字段)
  6 ... ] ）
  7 /*说明：
  8 DATATYPE --是Oracle的数据类型,可以查看附录。
  9 NUT NULL --可不可以允许资料有空的（尚未有资料填入）。
 10 PRIMARY KEY --是本表的主键。
 11 constraint --是对表里的字段添加约束.(约束类型有
 12             Check,Unique,Primary key,not null,Foreign key)。
 13 */
 14 ---示例:
 15 create table stu(
 16 s_id number(8) PRIMARY KEY,
 17 s_name varchar2(20) not null,
 18 s_sex varchar2(8),
 19 clsid number(8),
 20 constraint u_1 unique(s_name),
 21 constraint c_1 check (s_sex in ('MALE','FEMALE'))
 22 );
 23

  复制表

  1 CREATE TABLE <table_name> as <SELECT 语句>
  2 
  3 ---(需注意的是复制表不能复制表的约束);
  4 
  5 --示例:
  6 create table test as select * from emp;
  7 
  8 ---如果只复制表的结构不复制表的数据则:
  9 create table test as select * from emp where 1=2;

创建索引

  1 --创建索引
  2 
  3 --------------------------------------------------------------------------------
  4 CREATE [UNIQUE] INDEX <index_name> ON <table_name>(字段 [ASC|DESC]);
  5 /*
  6 UNIQUE --确保所有的索引列中的值都是可以区分的。
  7 [ASC|DESC] --在列上按指定排序创建索引。
  8 
  9 (创建索引的准则：
 10 1.如果表里有几百行记录则可以对其创建索引(表里的记录行数越多索引的效果就越明显)。
 11 2.不要试图对表创建两个或三个以上的索引。
 12 3.为频繁使用的行创建索引。
 13 )
 14 */
 15 
 16 --示例
 17 create index i_1 on emp(empno asc);

创建同义词

  1 --创建同义词
  2 
  3 --------------------------------------------------------------------------------
  4 
  5 CREATE SYNONYM <synonym_name> for <tablename/viewname>
  6 
  7 --同义词即是给表或视图取一个别名。
  8 
  9 --示例:
 10 create synonym mm for emp;
 11

2:alter（修改）

  1 --修改表
  2 
  3 --------------------------------------------------------------------------------
  4 
  5 --1.向表中添加新字段
  6 ALTER TABLE <table_name> ADD (字段1 类型 [NOT NULL],字段2 类型 [NOT NULL].... );
  8 
  9 --2.修改表中字段
 10 ALTER TABLE <table_name> modify(字段1 类型,字段2 类型.... );
 11 
 12 --3 .删除表中字段
 13 ALTER TABLE <table_name> drop(字段1,字段2 .... );
 14 
 15 --4 .修改表的名称
 16 RENAME <table_name> to <new table_name>;
 17 
 18 --5 .对已经存在的表添加约束
 19 ALTER TABLE <table_name> ADD CONSTRAINT <constraint_name> 约束类型 (针对的字段名);
 20 ---示例：
 21 Alter table emp add constraint S_F Foreign key (deptno) references dept(deptno);
 22 
 23 --6 .对表里的约束禁用;
 24 ALTER TABLE <table_name> DISABLE CONSTRAINT <constraint_name>;
 25 
 26 ---7 .对表里的约束重新启用;
 27 ALTER TABLE <table_name> ENABLE CONSTRAINT <constraint_name>;
 28 
 29 ---8 .删除表中约束
 30 ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>;
 31 ---示例:
 32 ALTER TABLE emp drop CONSTRAINT <Primary key>;
 33 
 34 
 35

2:drop（删除）

  删除表

  1 --删除表
  2 
  3 --------------------------------------------------------------------------------
  4 DROP TABLE <table_name>;
  5 
  6 ---示例
  7 drop table emp;
  8

 

删除索引

  1 --删除索引
  2 
  3 --------------------------------------------------------------------------------
  4 DROP INDEX <index_name>;
  5 
  6 --示例
  7 drop index i_1;
  8

 

删除同义词

  1 --删除同义词
  2 
  3 --------------------------------------------------------------------------------
  4 DROP SYNONYM <synonym_name>;
  5 
  6 --示例
  7 drop synonym mm;
  8

 

二：DML数据操纵语言 

插入记录  insert into

  1 --插入记录
  2 
  3 --------------------------------------------------------------------------------
  4 INSERT INTO table_name (column1,column2,...)
  5 values ( value1,value2, ...);
  6 
  7 --示例
  8 insert into emp (empno,ename) values(9500,'AA');
  9 
 10 
 11 --把 一个表中的数据插入另一个表中
 12 
 13 INSERT INTO <table_name> <SELECT 语句>
 14 --示例
 15 create table a as select * from emp where 1=2;
 16 insert into a select * from emp where sal>2000;
 17 
 18 
 19

 

查询记录  select

 

  1 --查询记录
  2 
  3 --------------------------------------------------------------------------------
  4 
  5 --一般查询
  6 SELECT [DISTINCT] <column1 [as new name] ,columns2,...>
  7 FROM <table1>
  8 [WHERE <条件>]
  9 [GROUP BY <column_list>]
 10 [HAVING <条件>]
 11 [ORDER BY <column_list> [ASC|DESC]]
 12 
 13 /*
 14 DISTINCT --表示隐藏重复的行
 15 WHERE --按照一定的条件查找记录
 16 GROUP BY --分组查找(需要汇总时使用)
 17 HAVING --分组的条件
 18 ORDER BY --对查询结果排序
 19 
 20 
 21 要显示全部的列可以用*表示 */
 22 --示例：
 23 select * from emp;
 24 
 25 WHERE 语句的运算符
 26 where <条件１>AND<条件２> --两个条件都满足
 27 --示例：
 28 select * from emp where deptno=10 and sal>1000;
 29 
 30 where <条件１>OR<条件２> --两个条件中有一个满足即可
 31 --示例：
 32 select * from emp where deptno=10 OR sal>2000;
 33 
 34 where NOT <条件> --不满足条件的
 35 --示例：
 36 select * from emp where not deptno=10;
 37 
 38 where IN(条件列表) --所有满足在条件列表中的记录
 39 --示例：
 40 select * from emp where empno in(7788,7369,7499);
 41 
 42 where BETWEEN .. AND　..　 --按范围查找
 43 --示例：
 44 select * from emp where sal between 1000 and 3000;
 45 
 46 where 字段 LIKE --主要用与字符类型的字段 
 47 --示例1：
 48 select * from emp where ename like '_C%'; --查询姓名中第二个字母是'C'的人
 49 --'-' 表示任意字符;
 50 --'%' 表示多字符的序列；
 51 
 52 where 字段 IS [NOT] NULL --查找该字段是[不是]空的记录
 53 
 54 --汇总数据是用的函数
 55 --SUM --求和
 56 --示例：
 57 select deptno,sum(sal) as sumsal from emp GROUP BY deptno;
 58 
 59 /*
 60 AVG --求平均值
 61 MAX --求最大值
 62 MIN --求最小值
 63 COUNT --求个数
 64 */
 65 
 66 --子查询 
 67 SELECT <字段列表> from <table_name> where 字段 运算符(<SELECT 语句>);
 68 
 69 --示例：
 70 select * from emp where sal=(select max(sal) from emp);
 71 
 72 --运算符
 73 Any
 74 --示例：
 75 select * from emp where sal>ANY(select sal from emp where deptno=30) and deptno<>30;
 76 --找出比deptno=30的员工最低工资高的其他部门的员工
 77 
 78 --ALL 
 79 select * from emp where sal>ALL(select sal from emp where deptno=30) and deptno<>30;
 80 --找出比deptno=30的员工最高工资高的其他部门的员工
 81 
 82 
 83 --连接查询 
 84 SELECT <字段列表> from <table1,table2> WHERE table1.字段[(+)]=table2.字段[(+)]
 85 
 86 --示例
 87 select empno,ename,dname from emp,dept where emp.deptno=dept.deptno;
 88 
 89 
 90 --查询指定行数的数据
 91 SELECT <字段列表> from <table_name> WHERE ROWNUM<行数;
 92 --示例:
 93 select * from emp where rownum<=10;--查询前10行记录
 94 --注意ROWNUM只能为1 因此不能写 select * from emp where rownum between 20 and 30;
 95 
 96 --要查第几行的数据可以使用以下方法:
 97 select * from emp where rownum<=3 and empno not in (select empno from emp where rownum<=3);
 98 --结果可以返回整个数据的3-6行;
 99 --不过这种方法的性能不高；如果有别的好方法请告诉我。
100 
101 
102 
103 
104 
105

 

更新数据  update

  1 --更新数据
  2 
  3 --------------------------------------------------------------------------------
  4 UPDATE table_name set column1=new value,column2=new value,...
  5 WHERE <条件>
  6 
  7 --示例
  8 update emp set sal=1000,empno=8888 where ename='SCOTT'
  9

 

删除数据  delete

  1 --更新数据
  2 
  3 --------------------------------------------------------------------------------
  4 DELETE FROM <table_name>
  5 WHERE <条件>
  6 
  7 --示例
  8 delete from emp where empno='7788'
  9

 

三：DCL数据控制语言 

数据控制语言

  1 --数据控制语言
  2 
  3 --------------------------------------------------------------------------------
  4 --1.授权
  5 GRANT <权限列表> to <user_name>;
  6 
  7 --2.收回权限
  8 REVOKE <权限列表> from <user_name>
  9 
 10 /*
 11 Oracle 的权限列表
 12 connect 连接
 13 resource 资源
 14 unlimited tablespace 无限表空间
 15 dba 管理员
 16 session 会话
 17 */

 

四：TCL事务控制语言 

  1 --数据控制语言
  2 
  3 --------------------------------------------------------------------------------
  4 --1.提交；
  5 COMMIT;
  6 
  7 --2.回滚;
  8 ROLLBACK [TO savepoint]
  9 
 10 --3.保存位置。
 11 SAVEPOINT <savepoint>
 12 
 13

 

五：Oracle 其他对象

 

视图：

 

  1 --创建视图 
  2 --------------------------------------------------------------------------------
  3 CREATE [OR REPLACE] VIEW <view_name>
  4 AS
  5 <SELECT 语句>;
  6 
  7 OR REPLACE --表示替换以有的视图
  8 
  9 --删除视图 
 10 --------------------------------------------------------------------------------
 11 
 12 DROP VIEW <view_name>
 13 
 14

 

 

序列：

  1 --创建序列 
  2 --------------------------------------------------------------------------------
  3 
  4 CREATE SEQUENCE <sequencen_name>
  5 INCREMENT BY n
  6 START WITH n
  7 [MAXVALUE n][MINVALUE n]
  8 [CYCLE|NOCYCLE]
  9 [CACHE n|NOCACHE];
 10 
 11 /*
 12 INCREMENT BY n --表示序列每次增长的幅度;默认值为1.
 13 START WITH n --表示序列开始时的序列号。默认值为1.
 14 MAXVALUE n --表示序列可以生成的最大值(升序).
 15 MINVALUE n --表示序列可以生成的最小值(降序).
 16 CYCLE --表示序列到达最大值后，在重新开始生成序列.默认值为 NOCYCLE。
 17 CACHE --允许更快的生成序列.
 18 */
 19 
 20 --示例:
 21 create sequence se_1
 22 increment by 1
 23 start with 100
 24 maxvalue 999999
 25 cycle;
 26 
 27 
 28 --修改序列 
 29 
 30 
 31 --------------------------------------------------------------------------------
 32 
 33 ALTER SEQUENCE <sequencen_name>
 34 INCREMENT BY n
 35 START WITH n
 36 [MAXVALUE n][MINVALUE n]
 37 [CYCLE|NOCYCLE]
 38 [CACHE n|NOCACHE];
 39 
 40 
 41 
 42 --删除序列
 43 
 44 
 45 --------------------------------------------------------------------------------
 46 
 47 DROP SEQUENCE <sequence_name>
 48 
 49 
 50 
 51 
 52 --使用序列
 53 
 54 
 55 --------------------------------------------------------------------------------
 56 
 57 1.CURRVAL
 58 返回序列的当前值.
 59 注意在刚建立序列后,序列的CURRVAL值为NULL，所以不能直接使用。
 60 可以先初始化序列:
 61 方法:select <sequence_name>.nextval from dual;
 62 示例:select se_1.nextval from dual;
 63 之后就可以使用CURRVAL属性了
 64 
 65 2.NEXTVAL
 66 返回序列下一个值；
 67 示例:
 68 begin
 69 for i in 1..5
 70 loop
 71 insert into emp(empno) values(se_1.nextval);
 72 end loop;
 73 end;
 74 
 75 
 76 --查看序列的当前值
 77 select <sequence_name>.currval from dual;
 78 
 79 --示例:select se_1.currval from dual;
 80 
 81 
 82

 

用户 

  1 --创建用户 
  2 --------------------------------------------------------------------------------
  3 
  4 CREATE USER <user_name> [profile "DEFAULT"]
  5 identified by "<password>" [default tablespace "USERS"]
  6 
  7 
  8 --删除用户
  9 
 10 
 11 --------------------------------------------------------------------------------
 12 
 13 DROP USER <user_name> CASCADE
 14 
 15

 

角色

  1 --创建角色 
  2 --------------------------------------------------------------------------------
  3 
  4 CREATE ROLE <role_name>
  5 identified by "<password>"
  6 
  7 
  8 --删除角色
  9 --------------------------------------------------------------------------------
 10 
 11 DROP ROLE <role_name>
 12 
 13

 

 

六：PL/SQL

 

PL/SQL 结构

 

  1 ---PL/SQL 结构
  2 
  3 --------------------------------------------------------------------------------
  4 DECLARE        --声明部分
  5         声明语句
  6 BEGIN         --执行部分
  7         执行语句
  8 
  9 EXCEPTION         --异常处理部分
 10         执行语句
 11 
 12 END;
 13 
 14 变量声明
 15 <变量名> 类型[:=初始值];
 16 特殊类型 字段%type
 17 示例: name emp.ename%type --表示name的类型和emp.ename的类型相同
 18 表 %rowtype
 19 示例: test emp%rowtype --表示test的类型为emp表的行类型;也有 .empno; .ename; .sal ;等属性 
 20 
 21 常量声明
 22 <变量名> CONSTANT 类型:=初始值;
 23 示例: pi constant number(5,3):=3.14;
 24 
 25 全局变量声明
 26 VARIABLE <变量名> 类型;
 27 示例: VARIABLE num number;
 28 
 29 使用全局变量
 30 :<变量名>
 31 示例:
 32 :num:=100;
 33 i=:num;
 34 
 35 查看全局变量的值
 36 print <变量名>
 37 示例: print num;
 38 
 39 赋值运算符: :=
 40 示例: num := 100;
 41 
 42 使用SELECT <列名> INTO <变量名> FROM <表名> WHERE <条件>
 43 注意select into 语句的返回结果只能为一行;
 44 示例：test emp%rowtype;
 45 select * into test from emp where empno=7788;
 46 
 47 用户交互输入
 48 <变量>:='&变量'
 49 示例：
 50 num:=&num;
 51 
 52 注意oracle的用户交互输入是先接受用户输入的所有值后在执行语句；
 53 所以不能使用循环进行用户交互输入;
 54 
 55 
 56 条件控制语句
 57 IF <条件1> THEN
 58     语句
 59 [ELSIF <条件2> THEN
 60    语句
 61           .
 62           .
 63           .
 64 ELSIF <条件n> THEN
 65    语句]
 66 [ELSE
 67     语句]
 68 END IF;
 69 
 70 
 71 
 72 循环控制语句
 73 1.LOOP
 74 LOOP
 75    语句;
 76    EXIT WHEN <条件>
 77 END LOOP;
 78 
 79 2.WHILE LOOP
 80 WHILE <条件>
 81 LOOP
 82    语句;
 83 END LOOP;
 84 
 85 3.FOR
 86 FOR <循环变量> IN 下限..上限
 87 LOOP
 88    语句;
 89 END LOOP;
 90 
 91 
 92 NULL 语句
 93 null;
 94 表示没有操作;
 95 
 96 
 97 注释使用
 98 单行注释： --
 99 多行注释：/* .......
100       ...............*/
101 
102 
103 异常处理
104 
105 EXCEPTION
106    WHEN <异常类型> THEN
107             语句;
108    WHEN OTHERS THEN
109             语句;
110 END;
111 关于异常类型请查看附录.
112 
113 
114 
115 
116

 

游标

  1 显示游标
  2 --------------------------------------------------------------------------------
  3 
  4 定义:CURSOR <游标名> IS <SELECT 语句> [FOR UPDATE | FOR UPDATE OF 字段];
  5 
  6 [FOR UPDATE | FOR UPDATE OF 字段] --给游标加锁,既是在程序中有"UPDATE","INSERT","DELETE"语句对数据库操作时。
  7 游标自动给指定的表或者字段加锁，防止同时有别的程序对指定的表或字段进行"UPDATE","INSERT","DELETE"操作.
  8 在使用"DELETE","UPDATE"后还可以在程序中使用CURRENT OF <游标名> 子句引用当前行.
  9 
 10 操作:OPEN <游标名> --打开游标
 11     FETCH <游标名> INTO 变量1,变量2,变量3,....变量n,;
 12                 或者
 13     FETCH <游标名> INTO 行对象;         --取出游标当前位置的值 
 14     CLOSE <游标名> --关闭游标
 15 属性: %NOTFOUND --如果FETCH语句失败，则该属性为"TRUE"，否则为"FALSE";
 16     %FOUND --如果FETCH语句成果，则该属性为"TRUE"，否则为"FALSE";
 17     %ROWCOUNT --返回游标当前行的行数;
 18     %ISOPEN --如果游标是开的则返回"TRUE"，否则为"FALSE";
 19 
 20 使用:
 21 LOOP循环
 22    示例:
 23 DECLARE
 24      cursor c_1 is select * from emp;     --定义游标
 25      r c_1%rowtype;      --定义一个行对象，用于获得游标的值
 26 BEGIN
 27      if c_1%isopen then
 28           CLOSE c_1;
 29      end if;
 30      OPEN c_1;          --判断游标是否打开.如果开了将其关闭，然后在打开
 31      dbms_output.put_line('行号 姓名 薪水');
 32      LOOP
 33      FETCH c_1 INTO r;     --取值
 34      EXIT WHEN c_1%NOTFOUND;     --如果游标没有取到值，退出循环.
 35      dbms_output.put_line(c_1%rowcount||''||r.ename||''||r.sal);    --输出结果,需要 set serverout on 才能显示.
 36      END LOOP;
 37 END;
 38 
 39 
 40 FOR循环
 41    示例:
 42 DECLARE
 43      cursor c_1 is select ename,sal from emp;     --定义游标     
 44 BEGIN
 45      dbms_output.put_line('行号 姓名 薪水');
 46      FOR i IN c_1         --for循环中的循环变量i为c_1%rowtype类型;
 47      LOOP
 48      dbms_output.put_line(c_1%rowcount||''||i.ename||''||i.sal);    --输出结果,需要 set serverout on 才能显示.
 49      END LOOP;
 50 END;
 51 
 52 for循环使用游标是在循环开始前自动打开游标，并且自动取值到循环结束后，自动关闭游标.
 53 
 54 游标加锁示例:
 55 DECLARE
 56      cursor c_1 is select ename,sal from emp for update of sal;     --定义游标对emp表的sal字段加锁.     
 57 BEGIN
 58      dbms_output.put_line('行号 姓名 薪水');
 59      FOR i IN c_1         --for循环中的循环变量i为c_1%rowtype类型;
 60      LOOP
 61      UPDATE EMP set sal=sal+100 WHERE CURRENT OF c_1; --表示对当前行的sal进行跟新.
 62      END LOOP;
 63      FOR i IN c_1
 64      LOOP
 65      dbms_output.put_line(c_1%rowcount||''||i.ename||''||i.sal);    --输出结果,需要 set serverout on 才能显示.
 66      END LOOP;
 67 END;
 68 
 69 
 70 
 71 代参数的游标
 72 定义:CURSOR <游标名>(参数列表) IS <SELECT 语句> [FOR UPDATE | FOR UPDATE OF 字段];
 73 示例:
 74 DECLARE
 75      cursor c_1(name emp.ename%type) is select ename,sal from emp where ename=name;     --定义游标     
 76 BEGIN
 77      dbms_output.put_line('行号 姓名 薪水');
 78      FOR i IN c_1('&name')         --for循环中的循环变量i为c_1%rowtype类型;
 79      LOOP
 80      dbms_output.put_line(c_1%rowcount||''||i.ename||''||i.sal);    --输出结果,需要 set serverout on 才能显示.
 81      END LOOP;
 82 END;
 83 
 84 
 85 隐试游标
 86 --------------------------------------------------------------------------------
 87 隐试游标游标是系统自动生成的。每执行一个DML语句就会产生一个隐试游标，起名字为SQL;
 88 
 89 隐试游标不能进行"OPEN" ,"CLOSE","FETCH"这些操作;
 90 
 91 属性:
 92     %NOTFOUND --如果DML语句没有影响到任何一行时，则该属性为"TRUE"，否则为"FALSE";
 93     %FOUND --如果DML语句影响到一行或一行以上时，则该属性为"TRUE"，否则为"FALSE";
 94     %ROWCOUNT --返回游标当最后一行的行数;
 95 
 96 个人认为隐试游标的作用是判断一个DML语句；
 97 示例：
 98 BEGIN
 99     DELETE FROM EMP WHERE empno=&a;
100     IF SQL%NOTFOUND THEN
101         dbms_output.put_line('empno不存在');
102     END IF;
103    IF SQL%ROWCOUNT>0 THEN
104         dbms_output.put_line('删除成功');
105     END IF;
106 END;

PL/SQL表

  1 PL/SQL表
  2 --------------------------------------------------------------------------------
  3 pl/sql表只有两列,其中第一列为序号列为INTEGER类型,第二列为用户自定义列.
  4 
  5 定义:TYPE <类型名> IS TABLE OF <列的类型> [NOT NULL] INDEX BY BINARY_INTEGER;
  6 <列的类型>可以为Oracle的数据类行以及用户自定义类型;
  7 
  8 属性方法:
  9 .count --返回pl/sql表的总行数
 10 .delect --删除pl/sql表的所有内容
 11 .delect(行数) --删除pl/sql表的指定的行
 12 .delct(开始行，结束行) --删除pl/sql表的多行
 13 .first --返回表的第一个INDEX;
 14 .next(行数) --这个行数的下一条的INDEX;
 15 .last --返回表的最后一个INDEX;
 16 
 17 使用
 18 示例:
 19 DECLARE
 20      TYPE mytable IS TABLE OF VARCHAR2(20) index by binary_integer; --定义一个名为mytable的PL/sql表类型;
 21      cursor c_1 is select ename from emp;
 22      n number:=1;
 23      tab_1 mytable; --为mytable类型实例化一个tab_1对象;
 24 BEGIN
 25      for i in c_1
 26      loop
 27           tab_1(n):=i.ename; --将得到的值输入pl/sql表
 28           n:=n+1;
 29     end loop;
 30      n:=1;
 31      tab_1.delete(&要删除的行数); --删除pl/sql表的指定行
 32      for i in tab_1.first..tab_1.count
 33      loop
 34           dbms_output.put_line(n||''||tab_1(n)); --打印pl/sql表的内容
 35           n:=tab_1.next(n);
 36      end loop;
 37 EXCEPTION
 38      WHEN NO_DATA_FOUND THEN                    --由于删除了一行，会发生异常，下面语句可以接着删除的行后显示
 39           for i in n..tab_1.count+1
 40      loop
 41           dbms_output.put_line(n||''||tab_1(n));
 42           n:=tab_1.next(n);
 43      end loop;
 44 END;

PL/SQL记录

  1 PL/SQL记录
  2 --------------------------------------------------------------------------------
  3 pl/sql表只有一行,但是有多列。
  4 
  5 定义:TYPE <类型名> IS RECORD <列名1 类型1,列名2 类型2,...列名n 类型n,> [NOT NULL]
  6 <列的类型>可以为Oracle的数据类行以及用户自定义类型;可以是记录类型的嵌套
  7 
  8 使用
  9 示例:
 10 DECLARE
 11      TYPE myrecord IS RECORD(id emp.empno%type,
 12      name emp.ename%type,sal emp.sal%type);     --定义一个名为myrecoed的PL/sql记录类型;
 13      rec_1 myrecord; --为myrecord类型实例化一个rec_1对象;
 14 BEGIN
 15           select empno,ename,sal into rec_1.id,rec_1.name,rec_1.sal
 16           from emp where empno=7788;        --将得到的值输入pl/sql记录
 17           dbms_output.put_line(rec_1.id||''||rec_1.name||''||rec_1.sal); --打印pl/sql记录的内容
 18 END;
 19 
 20 
 21 
 22 结合使用PL/SQL表和PL/SQL记录
 23 示例:
 24 DECLARE
 25      CURSOR c_1 is select empno,ename,job,sal from emp;
 26      TYPE myrecord IS RECORD(empno emp.empno%type,ename emp.ename%type,
 27 job emp.job%type,sal emp.sal%type);     --定义一个名为myrecoed的PL/sql记录类型;
 28      TYPE mytable IS TABLE OF myrecord index by binary_integer;
 29                                                                    --定义一个名为mytable的PL/sql表类型;字段类型为PL/sql记录类型；
 30 
 31      n number:=1;
 32      tab_1 mytable; --为mytable类型实例化一个tab_1对象;
 33 BEGIN
 34           --赋值
 35           for i in c_1
 36           loop
 37                tab_1(n).empno:=i.empno;
 38                tab_1(n).ename:=i.ename;
 39                tab_1(n).job:=i.job;
 40                tab_1(n).sal:=i.sal;
 41                n:=n+1;
 42           end loop;
 43           n:=1;
 44           --输出
 45           for i in n..tab_1.count
 46           loop
 47                 dbms_output.put_line(i||''||tab_1(i).empno
 48                 ||''||tab_1(i).ename||''||tab_1(i).job||''||tab_1(i).sal);
 49           end loop;
 50 END;
 51

REF游标

  1 强型REF游标
  2 --------------------------------------------------------------------------------
  3 
  4 定义:TYPE <游标名> IS REF CURSOR RETURN<返回类型>;
  5 
  6 
  7 操作:OPEN <游标名> For <select 语句> --打开游标
  8     FETCH <游标名> INTO 变量1,变量2,变量3,....变量n,;
  9                 或者
 10     FETCH <游标名> INTO 行对象;         --取出游标当前位置的值 
 11     CLOSE <游标名> --关闭游标
 12 属性: %NOTFOUND --如果FETCH语句失败，则该属性为"TRUE"，否则为"FALSE";
 13     %FOUND --如果FETCH语句成果，则该属性为"TRUE"，否则为"FALSE";
 14     %ROWCOUNT --返回游标当前行的行数;
 15     %ISOPEN --如果游标是开的则返回"TRUE"，否则为"FALSE";
 16 
 17 使用:
 18    示例:
 19 DECLARE
 20      type c_type is ref cursor return emp%rowtype;     --定义游标
 21      c_1 c_type;      --实例化这个游标类型
 22      r emp%rowtype;
 23 BEGIN
 24      dbms_output.put_line('行号 姓名 薪水');
 25      open c_1 for select * from emp;
 26      loop
 27      fetch c_1 into r;
 28      exit when c_1%notfound;
 29      dbms_output.put_line(c_1%rowcount||''||r.ename||''||r.sal);    --输出结果,需要 set serverout on 才能显示.
 30      END LOOP;
 31 close c_1;
 32 END;
 33 
 34 
 35 弱型REF游标
 36 --------------------------------------------------------------------------------
 37 定义:TYPE <游标名> IS REF CURSOR;
 38 
 39 
 40 操作:OPEN <游标名> For <select 语句> --打开游标
 41     FETCH <游标名> INTO 变量1,变量2,变量3,....变量n,;
 42                 或者
 43     FETCH <游标名> INTO 行对象;         --取出游标当前位置的值 
 44     CLOSE <游标名> --关闭游标
 45 属性: %NOTFOUND --如果FETCH语句失败，则该属性为"TRUE"，否则为"FALSE";
 46     %FOUND --如果FETCH语句成果，则该属性为"TRUE"，否则为"FALSE";
 47     %ROWCOUNT --返回游标当前行的行数;
 48     %ISOPEN --如果游标是开的则返回"TRUE"，否则为"FALSE";
 49 示例：
 50 set autoprint on;
 51 var c_1 refcursor;
 52 DECLARE
 53    n number;
 54 BEGIN
 55    n:=&请输入;
 56    if n=1 then
 57          open :c_1 for select * from emp;
 58    else
 59          open :c_1 for select * from dept;
 60    end if;
 61 END;
  1 过程
  2 --------------------------------------------------------------------------------
  3 
  4 定义:CREATE [OR REPLACE] PROCEDURE <过程名>[(参数列表)] IS
  5          [局部变量声明]
  6          BEGIN
  7             可执行语句
  8           EXCEPTION
  9             异常处理语句
 10           END [<过程名>];
 11 
 12 变量的类型:in 为默认类型,表示输入; out 表示只输出;in out 表示即输入又输出;
 13 
 14 
 15 操作以有的过程:在PL/SQL块中直接使用过程名;在程序外使用execute <过程名>[(参数列表)]
 16 
 17 使用:
 18    示例:
 19 创建过程:
 20 create or replace procedure p_1(n in out number) is
 21     r emp%rowtype;
 22 BEGIN
 23      dbms_output.put_line('姓名 薪水');
 24      select * into r from emp where empno=n;
 25      dbms_output.put_line(r.ename||''||r.sal);    --输出结果,需要 set serverout on 才能显示.
 26     n:=r.sal;
 27 END;
 28 使用过程:
 29 declare
 30     n number;
 31 begin
 32     n:=&请输入员工号;
 33     p_1(n);
 34     dbms_output.put_line('n的值为 '||n);
 35 end;
 36 
 37 
 38 删除过程:
 39     DROP PROCEDURE <过程名>;

函数

 

  1 函数
  2 --------------------------------------------------------------------------------
  3 
  4 定义:CREATE [OR REPLACE] FUNCTION <过程名>[(参数列表)] RETURN 数据类型 IS
  5          [局部变量声明]
  6          BEGIN
  7             可执行语句
  8           EXCEPTION
  9             异常处理语句
 10           END [<过程名>];
 11 
 12 变量的类型:in 为默认类型,表示输入; out 表示只输出;in out 表示即输入又输出;
 13 
 14 
 15 使用:
 16    示例:
 17 创建函数:
 18 create or replace function f_1(n number) return number is
 19     r emp%rowtype;
 20 BEGIN
 21      dbms_output.put_line('姓名 薪水');
 22      select * into r from emp where empno=n;
 23      dbms_output.put_line(r.ename||''||r.sal);    --输出结果,需要 set serverout on 才能显示.
 24      return r.sal;
 25 END;
 26 使用函数:
 27 declare
 28     n number;
 29      m number;
 30 begin
 31     n:=&请输入员工号;
 32     m:=f_1(n);
 33     dbms_output.put_line('m的值为 '||m);
 34 end;
 35 
 36 
 37 删除函数:
 38     DROP FUNCTION <函数名>;

 

数据包

  1 数据包
  2 --------------------------------------------------------------------------------
  3 
  4 定义:
  5    定义包的规范
  6       CREATE [OR REPLACE] PACKAGE <数据包名> AS
  7                --公共类型和对象声明
  8                --子程序说明
  9       END;
 10     定义包的主体
 11       CREATE [OR REPLACE] PACKAGE BODY <数据包名> AS
 12                --公共类型和对象声明
 13                --子程序主体
 14       BEGIN
 15             -初始化语句
 16       END;
 17 
 18 
 19 使用:
 20    示例:
 21 创建数据包规范:
 22 create or replace package pack_1 as
 23       n number;
 24       procedure p_1;
 25       FUNCTION f_1 RETURN number;
 26 end;
 27 
 28 创建数据包主体:
 29 create or replace package body pack_1 as
 30   procedure p_1 is
 31        r emp%rowtype;
 32   begin
 33        select * into r from emp where empno=7788;
 34        dbms_output.put_line(r.empno||''||r.ename||''||r.sal);
 35    end;
 36 
 37    FUNCTION f_1 RETURN number is
 38        r emp%rowtype;
 39    begin
 40         select * into r from emp where empno=7788;
 41        return r.sal;
 42    end;
 43 end;
 44 
 45 使用包:
 46 declare
 47       n number;
 48 begin
 49       n:=&请输入员工号;
 50       pack_1.n:=n;
 51       pack_1.p_1;
 52       n:=pack_1.f_1;
 53       dbms_output.put_line('薪水为 '||n);
 54 end;
 55 
 56 在包中使用REF游标
 57 示例:
 58 创建数据包规范:
 59 create or replace package pack_2 as
 60      TYPE c_type is REF CURSOR; --建立一个ref游标类型
 61      PROCEDURE p_1(c1 in out c_type); --过程的参数为ref游标类型;
 62 end;
 63 
 64 创建数据包主体:
 65 create or replace package body pack_2 as
 66   PROCEDURE p_1(c1 in out c_type) is
 67   begin
 68        open c1 for select * from emp;
 69    end;
 70 end;
 71 
 72 使用包:
 73 var c_1 refcursor;
 74 set autoprint on;
 75 execute pack_2.p_1(:c_1);
 76 
 77 
 78 
 79 删除包:
 80     DROP PACKAGE <包名>;

 

触发器

  1 触发器
  2 
  3 --------------------------------------------------------------------------------
  4 创建触发器:
  5     CREATE [OR REPLACE] TRIGGER <触发器名>
  6     BEFORE|AFTER
  7     INSERT|DELETE|UPDATE [OF <列名>] ON <表名>
  8     [FOR EACH ROW]
  9      WHEN (<条件>)
 10      <pl/sql块>
 11 
 12      关键字"BEFORE"在操作完成前触发;"AFTER"则是在操作完成后触发;
 13      关键字"FOR EACH ROW"指定触发器每行触发一次.
 14       关键字"OF <列名>" 不写表示对整个表的所有列.
 15      WHEN (<条件>)表达式的值必须为"TRUE".
 16 
 17 特殊变量:
 18      :new --为一个引用最新的列值;
 19      :old --为一个引用以前的列值; 
 20 这些变量只有在使用了关键字 "FOR EACH ROW"时才存在.且update语句两个都有,而insert只有:new ,delect 只有:old;
 21 
 22 使用RAISE_APPLICATION_ERROR
 23      语法:RAISE_APPLICATION_ERROR(错误号(-20000到-20999),消息[,{true|false}]);
 24      抛出用户自定义错误.
 25      如果参数为'TRUE',则错误放在先前的堆栈上.
 26 
 27 INSTEAD OF 触发器
 28      INSTEAD OF 触发器主要针对视图（VIEW)将触发的dml语句替换成为触发器中的执行语句，而不执行dml语句.
 29 
 30 
 31 禁用某个触发器
 32      ALTER TRIGGER <触发器名> DISABLE
 33 重新启用触发器
 34      ALTER TRIGGER <触发器名> ENABLE
 35 禁用所有触发器
 36      ALTER TRIGGER <触发器名> DISABLE ALL TRIGGERS
 37 启用所有触发器
 38      ALTER TRIGGER <触发器名> ENABLE ALL TRIGGERS
 39 删除触发器
 40      DROP TRIGGER <触发器名>
 41 
 42

自定义对象

  1 自定义对象
  2 
  3 --------------------------------------------------------------------------------
  4 创建对象:
  5     CREATE [OR REPLACE] TYPE <对象名> AS OBJECT(
  6     属性1 类型
  7     属性2 类型
  8           .
  9            .
 10     方法1的规范(MEMBER PROCEDURE <过程名>
 11     方法2的规范 (MEMBER FUNCTION <函数名> RETURN 类型)
 12           .
 13            .
 14     PRAGMA RESTRIC_REFERENCES(<方法名>,WNDS/RNDS/WNPS/RNPS);
 15       关键字"PRAGMA RESTRIC_REFERENCES"通知ORACLE函数按以下模式之一操作;
 16       WNDS-不能写入数据库状态;
 17       RNDS-不能读出数据库状态;
 18       WNPS-不能写入包状态;
 19       RNDS-不能读出包状态;
 20 
 21 
 22     创建对象主体:
 23       CREATE [OR REPLACE] TYPE body <对象名> AS
 24      方法1的规范(MEMBER PROCEDURE <过程名> is   <PL/SQL块>
 25     方法2的规范 (MEMBER FUNCTION <函数名> RETURN 类型 is <PL/SQL块>
 26      END;
 27 
 28 使用MAP方法和ORDER方法
 29      用于对自定义类型排序。每个类型只有一个MAP或ORDER方法。
 30      格式:MAP MEMBER FUNCTION <函数名> RETURN 类型
 31             ORDER MEMBER FUNCTION <函数名> RETURN NUMBER
 32 
 33 创建对象表
 34       CREATE TABLE <表名> OF <对象类型>
 35 
 36 示例:
 37      1. 创建name 类型
 38      create or replace type name_type as object(
 39           f_name varchar2(20),
 40           l_name varchar2(20),
 41           map member function name_map return varchar2);
 42 
 43      create or replace type body name_type as
 44           map member function name_map return varchar2 is --对f_name和l_name排序
 45           begin
 46                return f_name||l_name;
 47           end;
 48           end;
 49       2 创建address 类型
 50      create or replace type address_type as object
 51        ( city varchar2(20),
 52            street varchar2(20),
 53            zip number,
 54       order member function address_order(other address_type) return number);
 55 
 56       create or replace type body address_type as
 57       order member function address_order(other address_type) return number is --对zip排序
 58       begin
 59            return self.zip-other.zip;
 60       end;
 61       end;
 62 
 63 3 创建stu对象
 64        create or replace type stu_type as object (
 65        stu_id number(5),
 66        stu_name name_type,
 67        stu_addr address_type,
 68        age number(3),
 69        birth date,
 70        map member function stu_map return number,
 71       member procedure update_age);
 72 
 73       create or replace type body stu_type as
 74        map member function stu_map return number is --对stu_id排序
 75        begin
 76              return stu_id;
 77        end;
 78       member procedure update_age is --求年龄用现在时间-birth
 79        begin
 80              update student set age=to_char(sysdate,'yyyy')-to_char(birth,'yyyy') where stu_id=self.stu_id;
 81        end;
 82        end;
 83 4. 创建对象表
 84       create table student of stu_type(primary key(stu_id));
 85 5.向对象表插值
 86       insert into student values(1,name_type('关','羽'),address_type('武汉','成都路',43000), null,sysdate-365*20);
 87 6.使用对象的方法
 88      delcare
 89           aa stu_type;
 90      begin
 91           select value(s) into aa from student s where stu_id=1; --value()将对象表的每一行转成行对象括号中必须为表的别名
 92           aa.update_age();
 93      end;
 94 7.select stu_id,s.stu_name.f_name,s.stu_name.l_name from student s; --查看类型的值
 95 8.select ref(s) from student s ; --ref()求出行对象的OID,括号中必须为表的别名;deref()将oid变成行队像； 
 96

其他

  1 其他
  2 
  3 --------------------------------------------------------------------------------
  4 1.在PL/SQL中使用DDL
  5      将sql语句赋给一个varchar2变量,在用execute immediate 这个varchar2变量即可;
  6      示例：
  7      declare
  8           str varchar2(200);
  9      begin
 10           str:='create table test(id number,name varchar2(20))'; --创建表
 11           execute immediate str;
 12           str:='insert into test values(3,''c'')'; --向表里插数据
 13           execute immediate str;
 14      end;
 15      但是要队这个表插入数据也必须使用execute immediate 字符变量
 16 
 17 2.判断表是否存在;
 18      示例：
 19      declare
 20           n tab.tname%type;
 21      begin
 22           select tname into n from tab where tname='&请输入表名';
 23           dbms_output.put_line('此表以存在');
 24      exception
 25           when no_data_found then
 26           dbms_output.put_line('还没有此表');
 27      end;
 28 
 29 
 30 2.查看以有的过程;
 31      示例：
 32      select object_name,object_type,status from user_objects where object_type='PROCEDURE';
 33

七： 附录

Oracle 数据库类型

 
 
数据类型
    描述
VARCHAR2(size)
可变长度的字符串,其最大长度为size个字节;size的最大值是4000,而最小值是1;你必须指定一个VARCHAR2的size;
NVARCHAR2(size)
可变长度的字符串,依据所选的国家字符集,其最大长度为size个字符或字节;size的最大值取决于储存每个字符所需的字节数,其上限为4000;你必须指定一个NVARCHAR2的size;
NUMBER(p,s)
精度为p并且数值范围为s的数值;精度p的范围从1到38;数值范围s的范围是从-84到127;
例如:NUMBER(5,2) 表示整数部分最大3位，小数部分为2位；
NUMBER(5,-2) 表示数的整数部分最大为7其中对整数的倒数2位为0,前面的取整。
NUMBER 表示使用默认值,即等同于NUMBER(5);
LONG
可变长度的字符数据,其长度可达2G个字节;
DATE
有效日期范围从公元前4712年1月1日到公元后4712年12月31日
RAW(size)
长度为size字节的原始二进制数据,size的最大值为2000字节；你必须为RAW指定一个size;
LONG RAW
可变长度的原始二进制数据，其最长可达2G字节;
CHAR(size)
固定长度的字符数据,其长度为size个字节;size的最大值是2000字节,而最小值和默认值是1;
NCHAR(size)
也是固定长度。根据Unicode标准定义
CLOB
一个字符大型对象,可容纳单字节的字符;不支持宽度不等的字符集;最大为4G字节
NCLOB
一个字符大型对象,可容纳单字节的字符;不支持宽度不等的字符集;最大为4G字节;储存国家字符集
BLOB
一个二进制大型对象;最大4G字节
BFILE
包含一个大型二进制文件的定位器,其储存在数据库的外面；使得可以以字符流I/O访问存在数据库服务器上的外部LOB;最大大小为4G字节.
 
 

 

 

函数

   
   
 字符函数
名称
     描述
CONCAT(字符串1,字符串2)

将字符串1和字符串2连接成一个新的字符串
示例: select CONCAT(job,ename) from emp

 

CONCAT(c1,c2)
【功能】连接两个字符串
【参数】c1，c2 字符型表达式
 
【返回】字符型

同：c1||c2


【示例】
 select concat('010-','88888888')||'转23' 高乾竞电话 from dual;

高乾竞电话
----------------
010-88888888转23

 

 
LPAD(字段,总的大小,添充字符)

左填充即向右对齐
示例: select empno,lpad(sal,10,'*') from emp

 

LPAD(c1,n[,c2])
【功能】在字符串c1的左边用字符串c2填充，直到长度为n时为止
【参数】C1 字符串
n 追加后字符总长度
c2 追加字符串,默认为空格
【返回】字符型
【说明】如果c1长度大于n，则返回c1左边n个字符
如果如果c1长度小于n，c2和c1连接后大于n，则返回连接后的右边n个字符


【示例】
SQL> select lpad('gao',10,'*') from dual;

lpad('gao',10,'*')
-----------------
*******gao

不够字符则用*来填满

【相似】RPAD()在列的右边粘贴字符
【相反】LTRIM() 删除左边出现的字符串

 
RPAD(字段,总的大小,添充字符)

右填充即向左对齐
示例: select empno,rpad(sal,10) from emp

 

RPAD(c1,n[,c2])
【功能】在字符串c1的右边用字符串c2填充，直到长度为n时为止
【参数】C1 字符串
n 追加后字符总长度
c2 追加字符串,默认为空格
【返回】字符型
【说明】如果c1长度大于n，则返回c1左边n个字符
如果如果c1长度小于n，c1和c2连接后大于n，则返回连接后的左边n个字符
如果如果c1长度小于n，c1和c2连接后小于n，则返回c1与多个重复c2连接(总长度>=n)后的左边n个字符

【示例】
SQL> select rpad('gao',10,'*a') from dual;

rpad('gao',10,'*a')
-----------------
gao*a*a*a*


【相似】LPAD()在列的左边粘贴字符
【相反】RTRIM() 删除右边出现的字符串

 

 
  
LTRIM(c1,[,c2])

 

LTRIM(c1,[,c2])
【功能】删除左边出现的字符串
【参数】C1 字符串
c2 追加字符串,默认为空格
【返回】字符型


【示例】
SQL> select LTRIM('   gao qian jing',' ') text from dual;
或：select LTRIM('   gao qian jing') text from dual;

text
-----------------
gao qian jing


【相似】RTRIM()删除右边出现的字符串
【相反】LPAD() 在列的左边粘贴字符

 

 
RTRIM(c1,[,c2])
 		 
RTRIM(c1,[,c2])
【功能】删除右边出现的字符串
【参数】C1 字符串
c2 追加字符串,默认为空格
【返回】字符型


【示例】
SQL> select RTRIM('gao qian jingXXXX','X') text from dual;

text
-----------------
gao qian jing


【相似】LTRIM()删除左边出现的字符串
【相反】RPAD() 在列的右边粘贴字符
LOWER(字符串)

将字符串全部变成小写;

 

LOWER(c1)
【功能】：将字符串全部转为小写
【参数】：c1，字符表达式
【返回】：字符型
【示例】
SQL> select lower('AaBbCcDd')AaBbCcDd from dual;

AABBCCDD
--------
aabbccdd


【同类】UPPER()将字符串全部转为大写。

 
UPPER(字符串)

将字符串全部变成大写;

 

UPPER(c1)
【功能】将字符串全部转为大写
【参数】c1，字符表达式
【返回】字符型
【示例】
SQL> select upper('AaBbCcDd') upper from dual;

UPPER
--------
AABBCCDD



【同类】LOWER()将字符串全部转为小写

 
INITCAP(字符串)

将字符串变成第一个字母大写,其余都变成小写;

 

INITCAP(c1)
【功能】返回字符串并将字符串的第一个字母变为大写，其它字母小写;
【参数】c1字符型表达式
 
【返回】字符型



【示例】
 SQL> select initcap('smith abc aBC') upp from dual;

UPP
-----
Smith Abc Abc

 
LENGTH(字符串)

求出字符串的长度;

 

LENGTH(c1)
【功能】返回字符串的长度;
【说明】多字节符(汉字、全角符等)，按1个字符计算
【参数】C1 字符串
【返回】数值型
【示例】
SQL> select length('高乾竞'),length('北京市海锭区'),length('北京TO_CHAR') from dual;

length('高乾竞')    length('北京市海锭区')     length('北京TO_CHAR')
----------------- ----------------            ----------------------------
            3             6                               9
LENGTH(c1)
【功能】返回字符串的长度;
【说明】多字节符(汉字、全角符等)，按2个字符计算
【参数】C1 字符串
【返回】数值型
【示例】
SQL> select length('高乾竞'),lengthB('高乾竞') from dual;

length('高乾竞')    lengthB('高乾竞')   
----------------- ----------------           
            3             6          


LENGTHC(c1).LENGTH2(c1).LENGTH4(c1)
【功能】返回字符串的长度;
【说明】多字节符(汉字、全角符等)，按1个字符计算
【参数】C1 字符串
【返回】数值型
【示例】
SQL> select length('高乾竞'),length('北京市海锭区'),length('北京TO_CHAR') from dual;

Oracle中的字符函数中，有一类函数是求字符长度的函数，length、lengthB、lengthC、length2、length4几个函数中比较常用的是length、lengthB。

他们的含义分别是：
Length函数返回字符的个数，使用定义是给定的字符集来计算字符的个数
LENGTHB给出该字符串的byte
LENGTHC使用纯Unicode
LENGTH2使用UCS2
LENGTH4使用UCS4

下面使一些例子：
Select length('你好') from dual;  2

Select lengthB('你好'),lengthC('你好'),length2('你好'), length4('你好')  from dual;
SUBSTR(字符串,开始位置,长度)

从字符串中取子串;
示例: select substr(ename,2,3) from emp;--从ename的第2位开始取3位

 

SUBSTR(c1,n1[,n2])
【功能】取子字符串
【说明】多字节符(汉字、全角符等)，按1个字符计算
【参数】在字符表达式c1里，从n1开始取n2个字符;若不指定n2,则从第y个字符直到结束的字串.
【返回】字符型

【示例】
SQL> select substr('13088888888',3,8) test from dual;

test
--------
08888888

 
INSTR(字符串,字符)

查看字符是否在字符串中存在;不存在返回0;存在则返回字符所在的的位置；如果有两个以上的字符则返回第一个的位置.
示例:select instr(ename,'S') from emp;

----------------------------------------------------------------------------------------------------------------------------------------------

 

INSTR(C1,C2[,I[,J]])
【功能】在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
【说明】多字节符(汉字、全角符等)，按1个字符计算
【参数】
C1    被搜索的字符串
C2    希望搜索的字符串
I     搜索的开始位置,默认为1
J     第J次出现的位置,默认为1
【返回】数值

【示例】select instr('oracle traning','ra',1,2) instring from dual;
返回：9


【示例】select instr('重庆某软件公司','某',1,1),instrb('重庆某软件公司','某',1,1) instring from dual;
返回：3,5

 

INSTRB(C1,C2[,I[,J]])
【功能】在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
【说明】多字节符(汉字、全角符等)，按2个字符计算
【参数】
C1    被搜索的字符串
C2    希望搜索的字符串
I     搜索的开始位置,默认为1
J     第J次出现的位置,默认为1
【返回】数值

【示例】select instr('重庆某软件公司','某',1,1),instrb('重庆某软件公司','某',1,1) instring from dual;
返回：3,5
TRIM(字符 FROM 字符串)

去掉字符串首尾的字符;
示例: select trim('S' from ename) from emp;

 

 

TRIM(c1 from c2)
【功能】删除左边和右边出现的字符串
【参数】C2 删除前字符串
c1 删除字符串,默认为空格
【返回】字符型


【示例】
 select TRIM('X' from 'XXXgao qian jingXXXX'),TRIM('X' from 'XXXgaoXXjingXXXX') text from dual;
返回：gao qian jing		gaoXXjing


【相似】LTRIM()删除左边出现的字符串  RTRIM()删除右边出现的字符串

 
TO_CHAR()
 将不是其他类型转成字符类型；
对于日期型可以控制其格式:TO_CHAR(日期,'格式');
其中格式有: 'YYYY' --以4为显示年;
'YEAR' --以标准格式显示年； 'MM' ; 'MON' ; 'DD' ; 'DAY'; 'HH' ; 'MI' ;'SS'
REPLACE(字符串,字符串1,字符串2)

将字符串中的字符1替换成字符2;
示例: select replace(ename,'SC','SS') from emp;

 

REPLACE(c1,c2[,c3])
【功能】将字符表达式值中，部分相同字符串，替换成新的字符串
【参数】
c1   希望被替换的字符或变量 
c2   被替换的字符串
c3   要替换的字符串，默认为空(即删除之意，不是空格)
【返回】字符型

【示例】
SQL> select replace('he love you','he','i') test from dual;

test
------------------------------
i love you

 

 
TRANSLATE(字符串,字符串1,字符串2)

替换多的字符;
示例: select translate(ename,'SH','AB') from emp;
--表示将ename中的'S'换成'A','H'换成'B';

 

 

TRANSLATE(c1,c2,c3)
【功能】将字符表达式值中，指定字符替换为新字符
【说明】多字节符(汉字、全角符等)，按1个字符计算
【参数】
c1   希望被替换的字符或变量 
c2   查询原始的字符集
c3   替换新的字符集，将c2对应顺序字符，替换为c3对应顺序字符
如果c3长度大于c2，则c3长出后面的字符无效
如果c3长度小于c2，则c2长出后面的字符均替换为空(删除)
如果c3长度为0，则返回空字符串。
如果c2里字符重复，按首次位置为替换依据

【返回】字符型

【示例】
select TRANSLATE('he love you','he','i'),
TRANSLATE('重庆的人','重庆的','上海男'),
TRANSLATE('重庆的人','重庆的重庆','北京男士们'),
TRANSLATE('重庆的人','重庆的重庆','1北京男士们'),
TRANSLATE('重庆的人','1重庆的重庆','北京男士们') from dual;
返回：i love you,上海男人,北京男人,1北京人,京男士人

 
ASCII(char)

 

 

ASCII(x1)
【功能】：返回字符表达式最左端字符的ASCII 码值。
【参数】：x1，字符表达式
【返回】：数值型
【示例】
SQL> select ascii('A') A,ascii('a') a,ascii(' ') space,ascii('示') hz from dual;

A         A          SPACE        hz
--------- --------- --------- ---------
65        97         32         51902


【说明】在ASCII（）函数中，纯数字的字符串可不用‘’括起来，但含其它字符的字符串必须用‘’括起来使用，否则会出错。
　　如果最左端是汉字，只取汉字最左半边字符的ASCII 码

【互反函数】：chr()
CHR(n1)
【功能】：将ASCII 码转换为字符。
【参数】：n1,为0 ~ 255，整数
【返回】：字符型
【示例】
SQL> select chr(54740) zhao,chr(65) chr65 from dual;

ZH C
-- -
赵 A



【互反函数】：ASCII

 
NLSSORT(字符串)
 对字符串排序.
  
NLS_INITCAP(x[,y])

NLS_INITCAP(x[,y])
【功能】返回字符串并将字符串的第一个字母变为大写，其它字母小写;
【参数】x字符型表达式
【参数】Nls_param可选，
查询数据级的NLS设置:select * from nls_database_parameters;

例如：
指定排序的方式(nls_sort=) 。
nls_sort=SCHINESE_RADICAL_M（部首、笔画）
nls_sort=SCHINESE_STROKE_M（笔画、部首SCHINESE_PINYIN_M（拼音））

【返回】字符型


【示例】
 select nls_initcap('ab cde') "test",
nls_initcap('a c b d e','nls_sort= SCHINESE_PINYIN_M') "test1" from dual;
返回：Ab Cde, A C B D E


 select nls_initcap('ab cde') "test",
nls_initcap('a c b d e','NLS_LANGUAGE=AMERICAN') "test1" from dual;
 
NLS_LOWER(x[,y])

NLS_LOWER(x[,y])
【功能】返回字符串并将字符串的变为小写;
【参数】x字符型表达式
【参数】Nls_param可选，指定排序的方式(nls_sort=) 。
SCHINESE_RADICAL_M（部首、笔画）
SCHINESE_STROKE_M（笔画、部首SCHINESE_PINYIN_M（拼音））
 
【返回】字符型



【示例】
 select nls_LOWER('ab cde') "test",nls_LOWER('a c b d e','nls_sort= SCHINESE_PINYIN_M') "test1" from dual;

NLS_UPPER(x[,y])
  
NLS_UPPER(x[,y])
【功能】返回字符串并将字符串的转换为大写;
【参数】x字符型表达式
【参数】Nls_param可选，指定排序的方式(nls_sort=) 。
SCHINESE_RADICAL_M（部首、笔画）
SCHINESE_STROKE_M（笔画、部首SCHINESE_PINYIN_M（拼音））
 
【返回】字符型



【示例】
 select NLS_UPPER('ab cde') "test",NLS_UPPER('a c b d e','nls_sort= SCHINESE_PINYIN_M') "test1" from dual;
返回：AB CDE,A C B D E
 
SOUNDEX(c1)
 
SOUNDEX(c1)
【功能】返回字符串参数的语音表示形式
【参数】c1,字符型
【返回】字符串

【说明】相对于比较一些读音相同，但是拼写不同的单词是非常有用的。

计算语音的算法： 
　　1.保留字符串首字母，但删除a、e、h、i、o、w、y 
　　2.将下表中的数字赋给相对应的字母 
　　(1) 1：b、f、p、v 
　　(2) 2：c、g、k、q、s、x、z 
　　(3) 3：d、t 
　　(4) 4：l 
　　(5) 5：m、n 
　　(6) 6：r 
　　3. 如果字符串中存在拥有相同数字的2个以上（包含2个）的字母在一起（例如b和f），或者只有h或w，则删除其他的，只保留1个 
　　4.只返回前4个字节，不够用0填充 
　　示例： 
　　soundex('two'),soundex('too'),soundex('to')，他们的结果都是T000 
　　soundex('cap'),soundex('cup')，他们的结果都是C100 
　　soundex('house'),soundex('horse')，他们的结果都分别是H200，H620


数学函数

名称
     描述
ABS(数字)
 一个数的绝对值
CEIL(数字)
 向上取整;不论小数后的书为多少都要向前进位;
CEIL(123.01)=124;
CEIL(-123.99)=-123;
FLOOR(数字)
 向下取整;不论小数后的书为多少都删除;|
floor(123.99)=123;
floor(-123.01)=-124;
MOD(被除数,除数)
 取余数;
MOD(20,3)=2
ROUND(数字,从第几为开始取)
 四舍五入;
ROUND(123.5,0)=124;
ROUND(-123.5,0)=-124;
ROUND(123.5,-2)=100;
ROUND(-123.5,-2)=-100;
SIGN(数字)
 判断是正数还是负数;正数返回1，负数返回-1，0返回0;
SQRT(数字)
 对数字开方；
POWER(m,n)
 求m的n次方；
TRUNC(数字,从第几位开始)
 切数字;
TRUNC(123.99,1)=123.9
TRUNC(-123.99,1)=-123.9
TRUNC(123.99,-1)=120
TRUNC(-123.99,-1)=-120
TRUNC(123.99)=123
GREATEST(数字列表)
 找出数字列表中最大的数;
示例:
select greatest(100,200,-100) from dual; --结果为200
LEAST(数字列表)
 找出数字列表中最小的数;
SIN(n)
 求n的正旋
COS(n)
 求n的余旋
TAN(n)
 求n的正切
ACos(n)
 求n的反正切
ATAN(n)
 求n的反正切
exp(n)
 求n的指数
LN(n) 
 求n的自然对数,n必须大于0
LOG(m,n)
 求n以m为底的对数,m和n为正数,且m不能为0


日期函数

名称
     描述 
sysdate
   
sysdate
【功能】：返回当前日期。
【参数】：没有参数，没有括号
【返回】：日期

【示例】select sysdate  hz from dual;
返回：2008-11-5
ADD_MONTHS(日期,数字)

在以有的日期上加一定的月份;
示例:
select add_months(hiredate,20),hiredate from emp;

 

add_months(d1,n1)
【功能】：返回在日期d1基础上再加n1个月后新的日期。
【参数】：d1，日期型，n1数字型
【返回】：日期

【示例】select sysdate,add_months(sysdate,3)  hz from dual;
返回：2008-11-5,2009-2-5

 

 
LAST_DAY(日期)
 
last_day(d1)
【功能】：返回日期d1所在月份最后一天的日期。
【参数】：d1，日期型
【返回】：日期

【示例】select sysdate,last_day(sysdate)  hz from dual;
返回：2008-11-5，2008-11-30
MONTHS_BETWEEN(日期1，日期2)
 
months_between(d1,d2)
【功能】：返回日期d1到日期d2之间的月数。
【参数】：d1，d2 日期型

【返回】：数字
如果d1>d2，则返回正数
如果d1<d2，则返回负数

【示例】
select sysdate,
months_between(sysdate,to_date('2006-01-01','YYYY-MM-DD')),
months_between(sysdate,to_date('2016-01-01','YYYY-MM-DD')) from dual;

返回：2008-11-5,34.16,-85.84

示例:
select months_between(sysdate,hiredate) from emp;
NEW_TIME(时间,时区,'gmt')
 
NEW_TIME(dt1,c1,c2)
【功能】：给出时间dt1在c1时区对应c2时区的日期和时间
【参数】：dt1，d2 日期型

【返回】：日期时间

【参数】：c1,c2对应的 时区及其简写   
  大西洋标准时间：AST或ADT   
  阿拉斯加_夏威夷时间：HST或HDT   
  英国夏令时：BST或BDT   
  美国山区时间：MST或MDT   
  美国中央时区：CST或CDT   
  新大陆标准时间：NST   
  美国东部时间：EST或EDT   
  太平洋标准时间：PST或PDT   
  格林威治标准时间：GMT   
  Yukou标准时间：YST或YDT 


【示例】
 select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,
to_char(new_time(sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;

返回：
BJ_TIME             LOS_ANGLES
------------------- -------------------
2008.11.05 20:11:58 2008.11.06 03:11:58

【示例】
 select sysdate bj_time,
new_time(sysdate,'PDT','GMT') los_angles from dual;
返回：
BJ_TIME             LOS_ANGLES
------------------- -------------------
2008-11-05 20:11:58 2008-11-06 03:11:58
 
round(d1[,c1])
  
round(d1[,c1])
【功能】：给出日期d1按期间(参数c1)四舍五入后的期间的第一天日期（与数值四舍五入意思相近）
【参数】：d1日期型,c1为字符型(参数)，c1默认为j（即最近0点日期)
【参数表】：c1对应的参数表:
最近0点日期: 取消参数c1或j
最近的星期日：day或dy或d
最近月初日期：month或mon或mm或rm 
最近季日期：q
最近年初日期：syear或year或yyyy或yyy或yy或y(多个y表示精度)  
最近世纪初日期：cc或scc


【返回】：日期

 
【示例】
select sysdate 当时日期,
round(sysdate) 最近0点日期,
round(sysdate,'day') 最近星期日,
round(sysdate,'month') 最近月初,
round(sysdate,'q') 最近季初日期, 
round(sysdate,'year') 最近年初日期 from dual;

trunc(d1[,c1])
  
trunc(d1[,c1])
【功能】：返回日期d1所在期间(参数c1)的第一天日期
【参数】：d1日期型,c1为字符型(参数)，c1默认为j（即当前日期)
【参数表】：c1对应的参数表:
最近0点日期: 取消参数c1或j
最近的星期日：day或dy或d (每周顺序：日，一，二，三，四，五，六）
最近月初日期：month或mon或mm或rm 
最近季日期：q
最近年初日期：syear或year或yyyy或yyy或yy或y(多个y表示精度)  
最近世纪初日期：cc或scc


【返回】：日期

 
【示例】
select sysdate 当时日期,
trunc(sysdate) 今天日期,
trunc(sysdate,'day') 本周星期日,
trunc(sysdate,'month') 本月初,
trunc(sysdate,'q') 本季初日期, 
trunc(sysdate,'year') 本年初日期 from dual;
NEXT_DAY(d,char)
 
next_day(d1[,c1])
【功能】：返回日期d1在下周，星期几(参数c1)的日期
【参数】：d1日期型,c1为字符型(参数)，c1默认为j（即当前日期)
【参数表】：c1对应:星期一，星期二，星期三……星期日
【返回】：日期

 
【示例】
select sysdate 当时日期,
next_day(sysdate,'星期一') 下周星期一,
next_day(sysdate,'星期二') 下周星期二,
next_day(sysdate,'星期三') 下周星期三,
next_day(sysdate,'星期四') 下周星期四,
next_day(sysdate,'星期五') 下周星期五,
next_day(sysdate,'星期六') 下周星期六,
next_day(sysdate,'星期日') 下周星期日 from dual;

extract(c1 from d1)
  
extract(c1 from d1)
【功能】：日期/时间d1中，参数(c1)的值
【参数】：d1日期型(date)/日期时间型(timestamp),c1为字符型(参数)
【参数表】：c1对应的参数表详见示例


【返回】：字符

 
【示例】
select 
extract(hour from timestamp '2001-2-16 2:38:40 ' ) 小时,
extract(minute from timestamp '2001-2-16 2:38:40 ' ) 分钟,
extract(second from timestamp '2001-2-16 2:38:40 ' ) 秒,
extract(DAY from timestamp '2001-2-16 2:38:40 ' ) 日,
extract(MONTH from timestamp '2001-2-16 2:38:40 ' ) 月,
extract(YEAR from timestamp '2001-2-16 2:38:40 ' ) 年
 from dual;

select extract (YEAR from date '2001-2-16' ) from dual; 


select sysdate 当前日期,
extract(hour from timestamp timestamp sysdate) 小时,
extract(DAY from sysdate ) 日,
extract(MONTH from sysdate ) 月,
extract(YEAR from sysdate ) 年
 from dual;


localtimestamp
  
localtimestamp
【功能】：返回会话中的日期和时间 
【参数】：没有参数，没有括号
【返回】：日期

【示例】select localtimestamp from dual;
返回：14-11月-08 12.35.37.453000 上午
 
current_timestamp
  
current_timestamp
【功能】：以timestamp with time zone数据类型返回当前会话时区中的当前日期
【参数】：没有参数，没有括号
【返回】：日期

【示例】select current_timestamp from dual;
返回：14-11月-08 12.37.34.609000 上午 +08:00
 
current_date
  
current_date
【功能】：返回当前会话时区中的当前日期 
【参数】：没有参数，没有括号
【返回】：日期

【示例】select current_date from dual;
返回：2008-11-14
 
dbtimezone
  
dbtimezone
【功能】：返回时区
【参数】：没有参数，没有括号
【返回】：字符型

【示例】select dbtimezone from dual;

SESSIONTIMEZONE
  
SESSIONTIMEZONE
【功能】：返回会话时区
【参数】：没有参数，没有括号
【返回】：字符型

【示例】select dbtimezone,SESSIONTIMEZONE from dual;
返回:+00:00   +08:00

INTERVAL c1 set1
  
INTERVAL c1 set1
【功能】：变动日期时间数值
【参数】：c1为数字字符串或日期时间字符串，set1为日期参数
【参数表】：set1具体参照示例

【返回】：日期时间格式的数值,前面多个+号
以天或天更小单位时可用数值表达式借用，如1表示1天，1/24表示1小时，1/24/60表示1分钟


【示例】
select
trunc(sysdate)+(interval '1' second), --加1秒(1/24/60/60)
trunc(sysdate)+(interval '1' minute), --加1分钟(1/24/60)
trunc(sysdate)+(interval '1' hour), --加1小时(1/24)
trunc(sysdate)+(INTERVAL '1' DAY),  --加1天(1)
trunc(sysdate)+(INTERVAL '1' MONTH), --加1月
trunc(sysdate)+(INTERVAL '1' YEAR), --加1年
trunc(sysdate)+(interval '01:02:03' hour to second), --加指定小时到秒
trunc(sysdate)+(interval '01:02' minute to second), --加指定分钟到秒
trunc(sysdate)+(interval '01:02' hour to minute), --加指定小时到分钟
trunc(sysdate)+(interval '2 01:02' day to minute) --加指定天数到分钟
from dual;
   

 

转换函数

名称
     描述

 

chartorowid(c1) 。。

chartorowid(c1) 。。
【功能】转换varchar2类型为rowid值
【参数】c1,字符串，长度为18的字符串，字符串必须符合rowid格式
【返回】返回rowid值
【示例】
SELECT chartorowid('AAAADeAABAAAAZSAAA') FROM DUAL;

【说明】
在Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是在Oracle中的哪一个数据文件、块、行上。
在重复的记录中，可能所有列的内容都相同，但rowid不会相同.

 

ROWIDTOCHAR(rowid) 。。

ROWIDTOCHAR(rowid) 。。
【功能】转换rowid值为varchar2类型
【参数】rowid,固定参数
【返回】返回长度为18的字符串

【示例】
SELECT ROWIDTOCHAR(rowid) FROM DUAL;

【说明】
在Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是在Oracle中的哪一个数据文件、块、行上。
在重复的记录中，可能所有列的内容都相同，但rowid不会相同.

 

CONVERT(c1,set1,set2)
  
CONVERT(c1,set1,set2)
【功能】将源字符串c1 从一个语言字符集set2转换到另一个目的set1字符集
【参数】c1,字符串，set1,set2为字符型参数


【返回】字符串



【示例】
select convert('strutz','we8hp','f7dec') "conversion" from dual;

conver
------
strutz


select convert(name,'us7ascii','zhs16cgb231280') "conversion" from dual;
 

 

HEXTORAW(c1)
  
HEXTORAW(c1)
【功能】将一个十六进制构成的字符串转换为二进制

【参数】c1,十六进制的字符串


【返回】字符串



【示例】
select HEXTORAW('A123')  from dual;
 

 

RAWTOHEX(c1)
  
RAWTOHEX(c1)
【功能】将一个二进制构成的字符串转换为十六进制

【参数】c1,二进制的字符串


【返回】字符串



【示例】
select RAWTOHEX('A123')  from dual;

 

TO_CHAR(x[[,c2],C3])

TO_CHAR(x[[,c2],C3])
【功能】将日期或数据转换为char数据类型
【参数】
x是一个date或number数据类型。
c2为格式参数
c3为NLS设置参数
如果x为日期nlsparm=NLS_DATE_LANGUAGE 控制返回的月份和日份所使用的语言。
如果x为数字nlsparm=NLS_NUMERIC_CHARACTERS 用来指定小数位和千分位的分隔符，以及货币符号。
NLS_NUMERIC_CHARACTERS ="dg", NLS_CURRENCY="string"

【返回】varchar2字符型


【说明1】x为数据型时

c1格式表参考：

序号 格式 简例 说明
1 ,(逗号) '9999,999' 逗号,一般以千分位出现,作为分组符号使用.如果需要您也可以当作是十分位,百分位出现,可以出现N次,视乎数字的大小而定.
变态的例子是 to_char(1234,'9,9,9,9').
注意事项:只能出现在整数部分.
2 .(点号) '99.99' 点号,不要念为"句号",句号是个圆圈,点好只能出现在小数点对应的地方.只能出现一次.
to_char(1234.34,'9,9,9,9.99')
注意事项:只能出现在一个地方,就是原来数据小数点位置
3 $(美元符号) '$999.99' 美元.其实你可以放在任意地方(在10G下)
to_char(1234.34,'9,9,9,9.$99')
注意事项:只能出现一次.
4 0(零) '0999.99' 零.在对应位置返回对应的字符,如果没有则以'0'填充.
to_char(0.34,'9,9,9,0.$99')='$0.34';to_char(1234,'9999.00')='1234.00';
注意事项:这是一个强制的符号,对应位没有,则以'o'填充,这是9很大不同地方
5 9 '999.99' 9.在小数位,则表示转换为对应字符,如果没有则以0表示;在整数位,没有对应则不填充字符.
to_char(123,'999.99')=123.00; TO_CHAR(123,'99999.9')=123.0;
注意事项:对于0和9而言,如果格式的位数不如数字的位数多,会返回'#'.
譬如to_char(12345,'9999')='#####'
6 B(空格符) 'B999' 没有其它特别作用,在整数部分最前面加一个空格,可以出现在任意位置.
'S'||TO_CHAR(1234,'99B99')='S 1234';
注意事项:只能出现在整数部位.
7 C(国际货币符号) 'C9999' 在特定的位置返回一个ISO货币符号(就是NLS_ISO_CURRENCY参数所代表的值)
TO_CHAR(1233,'C9999')='CNY1234' ,这是新的国际标准RMB,关于这个可查询"国际货币符号"
注意事项:只能出现在整数部位第一位.
可以通过alter session set NLS_ISO_CURRENCY='JAPAN';来修改当前会话的设置.
8 D(ISO 小数位符号) '999D99' 这是"点号"的国际版本(ISO),作用等同于点号,也是只能出现一次.所不同的是,数据库会根据NLS_NUMERIC_CHARACTER的参数值来设置内容.默认的这个值是点号.
注意事项:没有特别需要一般不要用这个格式符号.也不要轻易修改参数值.
也可用alter sesssion set 来修改.
alter session set nls_numeric_characters='!,';   to_char(1234.34,'9999d99')=1234!34
9 EEEE(科学计算符) 9.9EEEE 科学计算符号
TO_CHAR(2008032001,'9.9EEEE')='2.01E+09',由于是科学计算方法,所以小数位前面加一个9或者0即可,多个是没有意义的.
10 G(分组符号) 999G999 是逗号(,)的的ISO标准,作为分组符号使用,可以放在多个地方使用.
TO_CHAR(123456,'999G9G99')=123,4,56
注意事项:同第八项 -D, 此外如果要转换出小数点,则要和D配合使用,不能和点号配合.
11 L(本地货币符号) 'L999' 是C的本地版本.可以放在整个格式的最前面和最后面.
TO_CHAR(123456,'999G9G99D00L')=123,4,56.00￥
注意事项:同第七项 C
12 MI(负号) '9999MI' 如果是负数,在尾部加上负号(-),如果是正数,则尾巴加上空格
to_char(1234,'9999mi')||'S'||TO_CHAR(-5678,'9999MI') =1234 S5678-
注意事项:只能放在格式尾巴
13 PR(符号) 9999PR 是表达负数的另外一种方式.如果是正数,则头部加上空格;如果是负数,则用小简括号<>把数字包起来.
TO_CHAR(-1234.89,'9G999D00PR')=<1,234.89>
注意事项:同12
14 RN(rn) RN(rn) 把整数(1-3999)转换为罗马字符.RN表示转为大写,rn表示小写的.
declare
i int;
begin
   for i in 1..20 loop
     dbms_output.put_line(to_char(i,'RN'));
   end loop;
end;
注意事项:只能自己使用,不能和其它符号组合使用.
15 S '9999S' 是12,13的综合改进版本.为整数加一个正号+,为负数加一个符号-.S在前则加在前,在后则在后.
TO_CHAR(-1234,'S9999')=-1234;TO_CHAR(1234,'S9999')=+1234
16 TM TM9/TMe 使用这个参数等于没有用参数to_char(number)一样,应为'tm9'是默认的格式参数.
to_char(1234,'tme')=1234
注意事项:格式要么是TM9,要么是TME.
当数字长度超过64位时候,TM9的输出等同于TME的输出.
17 U U999 双币符号,例如欧元.作用同11的L
TO_CHAR(999,'U999')=￥999
注意事项：通过NLS_DUAL_CURRENCY 控制
18 V 999V9 这是个比较古怪，又不是很常使用的符号。它的作用在于做一个计算。
例如TO_CHAR(N,'999V9'),以p表示V的位置,则该表达式＝to_char(N×(10的P-1次方)).但是9个数又必须保证大于等于乘积之后表示的位数.
TO_CHAR(5,'9V')=5*1=5;
TO_CHAR(5,'9V9')=5*10=50
TO_CHAR(5,'9V99')=500
TO_CHAR(50,'9V99')='######' 9的个数不够
注意事项：格式中不能和小数表达写在一起，但是可以混合货币等。
19 X xxxx 转换为16进制。
TO_CHAR(100,'XX')= 64
注意事项：数值必须是大于等于0的整数。前面只能和0或者FM组合使用.
20 　 　 通过以上的例子，我们了解了各种数字的格式。可以说格式太多样，难于记在脑子，最好是作为一个参考存在着.
归类：
数值类： 0,9,
分组类： (.),(,),D,G ,其中点好和逗号因为表示不明显，所以用小括号凸显。
货币类：　$,C,L,U
计算转换类：EEEE,RN,V,X
正负符号：MI,PR,S
其它类：B
正统类：TM

【示例】
to_char(1210.73, '9999.9') 返回 '1210.7'
to_char(1210.73, '9,999.99') 返回 '1,210.73'
to_char(1210.73, '$9,999.00') 返回 '$1,210.73'
to_char(21, '000099') 返回 '000021'
to_char(852,'xxxx') 返回' 354'


【说明2】x为日期型,c2可用参数
　

序号 格式 简例 说明
1 - / , . ; : 略 时间分隔符号，除了标准的几个，还允许用文字作为分割符号。
"text" 例如 to_char(sysdate,'YYYY"年"mm"月"dd"日"')=2008年04月24日
2 AD 　 即拉丁文Anno Domini的简写，表示公元.会根据nls的不同转换为公元或者ad等
A.D. 无特殊注意事项
3 AM 　 上午的简写 ,同pm, p.m. （下午) , 中文环境输出为上午（如果是上午）
A.M.
4 BC 　 虽然标准的写法是B.c. (c小写） 或者BC,好在Oracle不讲究这个。表示公元前
B.C.
5 CC 　 返回世纪，以阿拉伯数字表示
SCC 如果年的后两位介于01-99那么，返回前两位+1，否则返回前两位
6 D 　 一周之中的某天，返回的是序号1-7
7 DAY 　 一周之中的某天，不过返回的是星期几而已，这和语言设置有关系，在中国环境 NLS_DATE_LANGUAGE=SIMPLIFIED CHINESE ，用星期一到星期天表示
8 DD 　 月份中的某天(1-31)
9 DDD 　 年份中的某天（1-366）
10 DL 'DL' 返回长的日期格式。受到NLS_TERRITORY,NLS_LANGUAGE参数控制。例 2008年4月28日 星期一
限制：除了DL，其它什么的都不能设置。
11 DS 　 返回短的日期格式。受到NLS_TERRITORY,NLS_LANGUAGE参数控制。 例如 2008-04-28
限制：除了DL，其它什么的都不能设置。
12 DY 　 日期的简称，就是星期几（当然这指的是中国环境下)
13 E 　 纪元简称，但是只适合以下集中日历：日本皇室，中华民国,太过佛历
14 EE 　 纪元全程，适合情况同E
15 FF [1..9] 　 就是毫秒,如果不更上数字就是用默认的精度。
只能用于timestamp类型的。
16 FM 　 值得注意的一个函数：不返回任何内容。
有点不明白oracle为什么设置这个东西.
17 FX 　 同上
18 HH 　 表示小时，为12小时制，同hh12(1-12)
19 HH12 　 表示小时，为12小时制(1-12)
20 HH24 　 表示小时，为24小时制（0-23）
21 IW 　 ISO标准的星期序号(1-52,或者1-53）
22 IYYY 　 IYY,IY,I, ISO年(4位）的4,3，2，1位数字(倒数）
IYY to_char(to_date(21120401,'yyyymmdd'),'iyyy, iyy,iy,i')=2112, 112,12,2
IY 　
I 　
23 J 　 儒略日(多用于天文的一种日历）,从公元前4712年一月一日算起，得出的结果是个整数，算法大体为 （公元日期+4712）*儒略日历年平均天数
24 MI 　 秒（0-59）
25 MM 　 2位月（1-12）
26 MON 　 月的简称，和国家有关系NLS_DATE_LANGUAGE，例如04在中文环境下用4月表示.
27 MONTH 　 月的名称，国家有关系NLS_DATE_LANGUAGE,目前在中文下04表示为4月。
28 PM 　 同am,a.m.表示下午
P.M.
29 Q 　 季度（1-4）
30 RM 　 用罗马数字表示的月份，I   ,II ,III ,IV ,V   ,VI ,VII ,VIII,IX ,X   ,XI ,XII
31 RR 　 有点四舍五入表示年的意思，具体的用法有那么一点点复杂。
以s表示输入的年份最后两位，c表示当前的年份最后两位,其输出结果(新的年份前两位）可以用函数r=f(s,c)来表示,s2,c2分别表示s,c的前两位。
1）s=[0,49],c=[0,49],则r=c2
2) s=[0,49],c=[50,99],则 r=c2+1
3) s=[50,99],c=[0,49],则r=c2-1
4) s=[50,99],c=[50,99],则 r=c2
简而言之就是靠近当前年份原则，如果和当前年份同区域那么就一样，如果比当前区域大，那么就是当作是当前世纪前一世纪，否则就是下一个世纪。
举例来说，以to_date为例子
SQL> select to_date('89-01-01','rr-mm-dd') ,to_date('12-01-01','rr-mm-dd') FROM DUAL;
　
TO_DATE('89-01-01','RR-MM-DD') TO_DATE('12-01-01','RR-MM-DD')
------------------------------ ------------------------------
1989-01-01                     2012-01-01
我想oracle会搞这个东东出来，估计有两个考虑一个是为了方便，一个是为了对付百年或者千年问题。
32 RRRR 　 如果输入参数只有两位，则同rr，否则就同yyyy作用.
33 SS 　 秒（0-59），一分钟内
34 SSSSS 　 一天从午夜开始的累积秒数.(0-86399)
35 TS 　 返回短日期格式内容，包括时分秒等,只能和dl,ds组合使用，格式是:
dl ts或者dl ts ,中间以空格间隔开。TO_CHAR(SYSDATE,'TS')=下午 4:50:04
表现形式受NLS_TERRITORY 和NLS_LANGUAGE影响。
36 TZD 　 夏令时制信息，时区简写加上夏令时信息，必须和格式tzr设置的时区对应。
包括下面三个TZ开头的，都是和时区相关，并不是直接用在to_char
37 TZH 　 时区中的小时，例如hh:mi:ss.fftzh:tzm'
38 TZM 　 时区中的分钟.
39 TZR 　 时区中的区域信息，必须是数据库支持的时区，例如US/Pacific
40 WW 　 和iw类似，也是表示星期的序号，从年的第一天算起到年的最后一个第七天。二者取值基本相同。（1-53） ，例如2008-01-01 到2008-01-07 算1，2008-01-09~2008-01-13 算2
41 W 　 一个月中的星期序号，其算法同ww,不过是局限在一月之内而已，和iso的不同。
42 X 　 代表本地根符号，没有特别用处，只能和timestamp类型一起使用.
43 Y,YYY 　 四位年，用都好分隔 例如2,008
44 YEAR 　 发音表达的年，例如 2008=two thousand eight
SYEAR S前缀表示公元前BC
45 YYYY 　 四位年，S前缀表示公元前BC
SYYYY
46 YYY 　 一次表示后面3，2，1位的年，例如2008 可以分别取值为008,08,8
YY
Y
　 总结 　 从以上看，主要就是表示时间几个部分的格式：世纪、年，月，日，时，分,秒，毫秒，以及其它一些混合格式。每个时间部分都可以有多种的表达方式，通过这样归类就比较容易记忆。
很多格式可以组合使用，这样最终可以形成足够丰富的表达其形势；
其次很多格式和nls是密切相关的；最后某些输出（返回）和格式大小写是有关系的，这在中文环境下体现不出来（目前来没有看到），但是english环境下就名下，以to_char(sysdate,'day')为例子，如果是西文环境是返回sun(假设sysdate位于周末），如果to_char(sysdate,'DAY')则返回SUN

【示例】
to_char(sysdate,'d') 每周第几天
to_char(sysdate,'dd') 每月第几天
to_char(sysdate,'ddd') 每年第几天
to_char(sysdate,'ww') 每年第几周
to_char(sysdate,'mm') 每年第几月
to_char(sysdate,'q') 每年第几季
to_char(sysdate,'yyyy') 年


SQL> select to_char(sysdate,' PM yyyy-mm-dd hh24:mi:sssss AD year mon day ddd iw') FROM DUAL;
TO_CHAR(SYSDATE,'PMYYYY-MM-DDH
--------------------------------------------------------------------------------
上午 2008-03-27 09:58:35917 公元 two thousand eight 3月 星期四 087 13
SQL> SELECT TO_CHAR(SYSTIMESTAMP,'HH24:MI:SS.FF5') FROM DUAL;
TO_CHAR(SYSTIMESTAMP,'HH24:MI:
------------------------------
10:02:28.90000
SQL>SELECT TO_CHAR(SYSDATE,'DS DL') FROM DUAL
TO_CHAR(SYSDATE,'DSDL')
-----------------------------------
2008-03-27 2008年3月27日 星期四

【示例】带C3示例

select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
返回：monday

　

 

TO_DATE(X[,c2[,c3]])

TO_DATE(X[,c2[,c3]])
【功能】将字符串X转化为日期型
【参数】c2,c3,字符型，参照to_char()
【返回】字符串

如果x格式为日期型(date)格式时，则相同表达：date x
如果x格式为日期时间型(timestamp)格式时，则相同表达：timestamp x

【相反】 to_char(date[,c2[,c3]])


【示例】
select to_date('199912','yyyymm'),
to_date('2000.05.20','yyyy.mm.dd'),
(date '2008-12-31') XXdate, 
to_date('2008-12-31 12:31:30','yyyy-mm-dd hh24:mi:ss'),
(timestamp '2008-12-31 12:31:30') XXtimestamp
from dual;

 

TO_NUMBER(X[[,c2],c3])

TO_NUMBER(X[[,c2],c3])
【功能】将字符串X转化为数字型
【参数】c2,c3,字符型，参照to_char()
【返回】数字串

【相反】 to_char(date[[,c2],c3])

【示例】
select TO_NUMBER('199912'),TO_NUMBER('450.05') from dual;


转换为16进制。 
TO_CHAR(100,'XX')= 64

 

TO_MULTI_BYTE(c1)

TO_MULTI_BYTE(c1)
【功能】将字符串中的半角转化为全角
【参数】c1,字符型
【返回】字符串

【示例】
SQL> select to_multi_byte('高A') text from dual;

test
--
高Ａ

 

to_single_byte(c1)

to_single_byte(c1)
【功能】将字符串中的全角转化为半角
【参数】c1,字符型
【返回】字符串

【示例】
SQL> select to_multi_byte('高Ａ') text from dual;

test
----
高A

 

nls_charset_id(c1)

nls_charset_id(c1)
【功能】返回字符集名称参应id值
【参数】c1,字符型
【返回】数值型


sql> select nls_charset_id('zhs16gbk') from dual;

nls_charset_id('zhs16gbk')
--------------------------
     852

 

nls_charset_name(n1)

nls_charset_name(n1)
【功能】返回字符集名称参应id值
【参数】n1,数值型
【返回】字符型


sql> select nls_charset_name(852) from dual;

nls_char
--------
zhs16gbk

 

聚合函数

 
AVG([distinct|all]x)
  
AVG([distinct|all]x)
【功能】统计数据表选中行x列的平均值。

【参数】all表示对所有的值求平均值,distinct只对不同的值求平均值，默认为all
如果有参数distinct或all，需有空格与x(列)隔开。

【参数】x，只能为数值型字段

【返回】数字值



【示例】
环境：
create table table3(xm varchar(8),sal number(7,2));
insert into table3 values('gao',1111.11);
insert into table3 values('gao',1111.11);
insert into table3 values('zhu',5555.55);
commit;

执行统计：
select avg(distinct sal),avg(all sal),avg(sal) from table3;
结果：  3333.33  2592.59  2592.59

 

  
SUM([distinct|all]x)
  
SUM([distinct|all]x)
【功能】统计数据表选中行x列的合计值。

【参数】all表示对所有的值求合计值,distinct只对不同的值求合计值，默认为all
如果有参数distinct或all，需有空格与x(列)隔开。

【参数】x，只能为数值型字段

【返回】数字值



【示例】
环境：
create table table3(xm varchar(8),sal number(7,2));
insert into table3 values('gao',1111.11);
insert into table3 values('gao',1111.11);
insert into table3 values('zhu',5555.55);
commit;

执行统计：
select SUM(distinct sal),SUM(all sal),SUM(sal) from table3;
结果：  6666.66     7777.77     7777.77

STDDEV([distinct|all]x)
 		   
STDDEV([distinct|all]x)
【功能】统计数据表选中行x列的标准误差。

【参数】all表示对所有的值求标准误差,distinct只对不同的值求标准误差，默认为all
如果有参数distinct或all，需有空格与x(列)隔开。

【参数】x，只能为数值型字段

【返回】数字值



【示例】
环境：
create table table3(xm varchar(8),sal number(7,2));
insert into table3 values('gao',1111.11);
insert into table3 values('gao',1111.11);
insert into table3 values('zhu',5555.55);
commit;

执行统计：
select STDDEV(distinct sal),STDDEV(all sal),STDDEV(sal) from table3;
结果：  3142.69366257674     2565.99863039714  2565.99863039714

 

VARIANCE([distinct|all]x)
  
VARIANCE([distinct|all]x)
【功能】统计数据表选中行x列的方差。

【参数】all表示对所有的值求方差,distinct只对不同的值求方差，默认为all
如果有参数distinct或all，需有空格与x(列)隔开。

【参数】x，只能为数值型字段

【返回】数字值



【示例】
环境：
create table table3(xm varchar(8),sal number(7,2));
insert into table3 values('gao',1111.11);
insert into table3 values('gao',1111.11);
insert into table3 values('zhu',5555.55);
commit;

执行统计：
select VARIANCE(distinct sal),VARIANCE(all sal),VARIANCE(sal) from table3;
结果： 9876523.4568     6584348.9712     6584348.9712

 

count(*|[distinct|all]x)
 		   
count(*|[distinct|all]x)
【功能】统计数据表选中行x列的合计值。

【参数】
*表示对满足条件的所有行统计，不管其是否重复或有空值(NULL)

all表示对所有的值统计,默认为all
distinct只对不同的值统计，
如果有参数distinct或all，需有空格与x(列)隔开，均忽略空值(NULL)。

【参数】x，可为数字、字符、日期型及其它类型的字段

【返回】数字值

count(*)=sum(1)

【示例】
环境：
create table table3(xm varchar(8),sal number(7,2));
insert into table3 values('gao',1111.11);
insert into table3 values('gao',1111.11);
insert into table3 values('zhu',5555.55);
insert into table3 values('',1111.11);
insert into table3 values('zhu',0);
commit;

执行统计：
select count(*),count(xm),count(all xm),count(distinct sal),count(all sal),count(sal),sum(1) from table3;
结果：  5   4  4  3   5   5  5

MAX([distinct|all]x)
 		   
MAX([distinct|all]x)
【功能】统计数据表选中行x列的最大值。

【参数】all表示对所有的值求最大值,distinct只对不同的值求最大值，默认为all
如果有参数distinct或all，需有空格与x(列)隔开。

【参数】x，可为数字、字符或日期型字段

【返回】对应x字段类型



【示例】
环境：
create table table3(xm varchar(8),sal number(7,2));
insert into table3 values('gao',1111.11);
insert into table3 values('gao',1111.11);
insert into table3 values('zhu',5555.55);
insert into table3 values('',1111.11);
insert into table3 values('zhu',0);
commit;

执行统计：
select MAX(distinct sal),MAX(xm) from table3;
结果：5555.55   zhu

 

MIN([distinct|all]x)
 		   
MIN([distinct|all]x)
【功能】统计数据表选中行x列的最大值。

【参数】all表示对所有的值求最大值,distinct只对不同的值求最大值，默认为all
如果有参数distinct或all，需有空格与x(列)隔开。

【参数】x，可为数字、字符或日期型字段

【返回】对应x字段类型
注：字符型字段，将忽略空值(NULL)


【示例】
环境：
create table table3(xm varchar(8),sal number(7,2));
insert into table3 values('gao',1111.11);
insert into table3 values('gao',1111.11);
insert into table3 values('zhu',5555.55);
insert into table3 values('',1111.11);
insert into table3 values('zhu',0);
commit;

执行统计：
select MIN(distinct sal),MIN(xm),MIN(distinct xm),MIN(all xm) from table3;
结果：0   gao  gao  gao
   

Oracle 分析函数

 

 
   
   
   

 

  
   

其他函数

  
名称
     描述
VSIZE(类型)
 求出数据类型的大小;
NVL(字符串,替换字符)
 如果字符串为空则替换，否则不替换
 
   
   
   
命令
     描述
DESC 表名
查看表的信息.
SET SERVEROUT [ON|OFF]
 设置系统输出的状态.
SET PAGESIZE <大小>
 设置浏览中没页的大小
SET LINESIZE <大小>
 设置浏览中每行的长度
SET AUTOPRINT [ON|OFF]
 设置是否自动打印全局变量的值
SELECT SYSDATE FROM DUAL
查看当前系统时间
ALTER SESSION SET nls_date_format='格式'
设置当前会话的日期格式
示例:ALTER SESSION SET nls_date_format='dd-mon-yy hh24:mi:ss'
SELECT * FROM TAB
 查看当前用户下的所有表
SHOW USER
 显示当前用户
HELP TOPIC
 显示有那些命令
SAVE <file_name>
 将buf中的内容保存成一个文件
RUN <file_name>
 执行已经保存的文件;也可以写成@<file_name>
GET <file_name>
 显示文件中的内容
LIST
 显示buf中的内容
ED
 用记事本打开buf,可以进行修改
DEL 行数
 删除buf中的单行
DEL 开始行 结束行
 删除buf中的多行
INPUT 字符串
 向buf中插入一行
APPEND 字符串
 将字符串追加到当前行
C/以前的字符串/替换的字符串
 修改buf中当前行的内容
CONNECT
 连接
DISCONNECT
 断开连接
QUIT
 退出sql*plus
EXP
 导出数据库（可以在DOS键入exp help=y 可以看到详细说明)
示例: exp scott/tiger full=y file=e:\a.dmp; --导出scott下的所有东西
        exp scott/tiger tables=(emp,dept) file=e:\emp.dmp --导出scott下的                                                                                             emp,dept表
IMP
 导入数据库（可以在DOS键入imp help=y 可以看到详细说明)
imp scott/tiger tables=(emp,dept) file=e:\emp.dmp

可以通过help <命令>获得命令的帮助 
 
   

常用命令

  
异常
    描述
CURSOR_ALREADY_OPEN
试图"OPEN"一个已经打开的游标
DUP_VAL_ON_INDEX
试图向有"UNIQUE"中插入重复的值
INVALID_CURSOR
试图对以关闭的游标进行操作
INVALID_NUMBER
在SQL语句中将字符转换成数字失败
LOGIN_DENIED
使用无效用户登陆
NO_DATA_FOUND
没有找到数据时
NOT_LOGIN_ON
没有登陆Oracle就发出命令时
PROGRAM_ERROR
PL/SQL存在诸如某个函数没有"RETURN"语句等内部问题
STORAGE_ERROR
PL/SQL耗尽内存或内存严重不足
TIMEOUT_ON_RESOURCE
Oracle等待资源期间发生超时
TOO_MANY_ROWS
"SELECT INTO"返回多行时
VALUE_ERROR
当出现赋值错误
ZERO_DIVIDE
除数为零
 
   

 

 

 

   
posted @ 2017-08-29 19:21  一品堂.技术学习笔记  阅读(545)  评论(0)  编辑  收藏  举报