Oracle笔记三、function 、select

2012-04-16

Scott表下有这么几个常用的表，而且还带有数据。分别是emp、dept、salgrade；
1、查看表结构用desc
desc emp;

2、空表dual，最常用的空表，如：
select 2 * 4 from dual;
select sysdate from dual;

3、双引号能保持格式
如：select sysdate “toDay 日期” from dual;

4、|| 字符串连接
如：select 2*3 || 8 from dual;
select ename || sal from scott.emp;
select ename || ‘ORACLE’ from scott.emp;

5、单引号，如：select 2 * 2 || ‘abc’’efg’ from dual;
用两个单引号表示一个单引号

6、去掉重复数据distinct
select distinct deptno from scott.emp;
去掉重复组合：select distinct deptno,job from scott.emp;

7、where查询
A、=查询，select * from scott.emp where sal = 1500;

B、比较<、>、>=、<=
    select * from scott.emp where sal > 1500;
C、and or
    select * from scott.emp where sal > 1500 and sal <= 5000 or deptno = 10;
D、in、not in
    select * from scott.emp where sal in (1500, 800) and deptno not in (10, 20)

E、like模糊 escape 转义
    Select * from scott.emp where ename like ‘%in%’;
    Select * from scott.emp where ename like ‘%in\%k%’;
    Select * from scott.emp where ename like ‘%in#%k%’ escape ‘#’;
    表示like中的#号是转义字符，相当于\
F、is null、is not null
K、    order by
    select sal, ename from scott.emp order by sal;
    select sal, ename from scott.emp order by sal asc;
    select sal, ename from scott.emp order by sal desc;
    select sal, ename from scott.emp where sal > 2000 order by sal desc;
    select sal, deptno, ename from scott.emp order by sal,deptno desc;

8、function
A、lower、upper、substr
select lower(‘abcABC’) from dual;
select upper(‘abcABC’) from dual;
substr(target, startIndex, length)
select substr(‘abcABC’, 1, 3) from dual;

B、chr、ascii
    将数字安装ascii值转换成字符：select char(65) from dual;
    将字符转换成ascii值：select ascii(‘Z’) from dual;

C、round、to_char
    精确小数
    select round(22.456) from dual;
    保留2位小数：select round(22.456, 2) from dual;
    精确到个位：select round(22.456, -1) from dual;

    货币
    设置货币格式，000前面不足就用0代替
    select to_char(sal, '$000,000.00') from scott.emp;
    999就不会替换不足的地方，只会安装格式输出
    select to_char(sal, '$999,999.99') from scott.emp;
    本地货币格式
    select to_char(sal, 'L999,999.99') from scott.emp;

    日期
    日期格式 
    格式控制 描述 
    YYYY、YYY、YY 分别代表4位、3位、2位的数字年 
    YEAR 年的拼写 
    MM 数字月 
    MONTH 月的全拼 
    MON 月的缩写 
    DD 数字日 
    DAY 星期的全拼 
    DY 星期的缩写 
    AM 表示上午或者下午 
    HH24、HH12 12小时制或24小时制 
    MI 分钟 
    SS 秒钟 
    SP 数字的拼写 
    TH 数字的序数词 

    “特殊字符” 假如特殊字符 
    HH24:MI:SS AM 15:43:20 PM
    select to_char(sysdate, 'YYYY-MM-DD HH:MI:SS') from dual;
    select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;

 D、to_date、to_number、nvl
    to_date(target, current_format)
    select to_date('2011-4-2 17:55:55', 'YYYY-MM-DD HH:MI:SS') from dual;
    select to_number('$12,322.56', '$999,999.99') + 10 from dual;
    select to_number('$12,322.56', '$00,000.00') + 10 from dual;
    select to_number('22.56') + 10 from dual;
    nvl可以将某个字段的空值转换成指定的值
    select ename, sal, nvl(comm, 1.00) from scott.emp;

9、group function 组函数：min、max、avg、sum、count
select max(sal) from scott.emp;
select min(sal) from scott.emp;
select avg(sal) from emp;
select round(avg(sal), 2) from emp;
select to_char(avg(sal), ‘L999,999.99’) from emp;
select sum(sal) from emp;
select count(comm) from emp;
select count(distinct deptno) from emp;

10、group by 分组
select deptno, avg(sal) from emp group by deptno;
select deptno, job, avg(sal) from emp group by deptno, job;
求部门最高工资的所在部门的员工信息：
select deptno, ename, sal from emp where sal in (select max(sal) from emp group by deptno);

11、having 对分组数据进行过滤
求部门评价工资：
select * from (select avg(sal) sal, deptno from emp group by deptno) where sal > 2000;
select avg(sal) sal, deptno from emp group by deptno having avg(sal) > 2000;

12、子查询
求部门分组后工资最高的员工信息
select emp.ename, emp.sal, emp.deptno from emp, (select max(sal) max_sal, deptno from emp group by deptno) t where emp.sal = t.max_sal and emp.deptno = t.deptno;
求部门平均工资等级
select s.grade, t.deptno, t.avg_sal from scott.salgrade s, (select deptno, avg(sal) avg_sal from emp group by deptno) t where t.avg_sal > s.losal and t.avg_sal < s.hisal;（between）

13、自连接
select a.ename, b.ename mgr_name from emp a, emp b where a.empno = b.mgr;

14、连接查询
select dname, ename from dept, emp where dept.deptno = emp.deptno;
select dname, ename from dept join emp on dept.deptno = emp.deptno;
select dname, ename from dept join emp using(deptno);
select dname, ename from dept left join emp on dept.deptno = emp.deptno;
select dname, ename from dept right join emp on dept.deptno = emp.deptno;
select dname, ename from dept full join emp on dept.deptno = emp.deptno;
select a.ename, b.ename mgr_name from emp a join emp b on a.mgr = b.empno;
select a.ename, b.ename mgr_name from emp a left join emp b on a.mgr = b.empno;

15、 Rownum
select rounum, deptno, dname from dept;
select from (
select rownum r, dept. from dept
) t where t.r > 2;

16、树状结构查询
select level, empno, ename, mgr from emp
connect by prior mgr = empno;

17、排序函数
–按部门分组，给出分组后的序号
select row_number() over(partition by deptno order by sal), emp.* from emp;

--rank排序，空出相同部分
select rank() over(partition by deptno order by sal), emp.* from emp;
select rank() over(order by deptno), emp.* from emp;
select rank() over(order by sal), emp.* from emp;

--dense_rank排序给出相同序号,不空留序号
select rank() over(order by sal), emp.* from emp;
select dense_rank() over(order by sal), emp.* from emp;

18、交集、并集、割集查询
–并集：不带重复数据
select from emp
union
select from emp2;

--并集：带重复数据
select * from emp
union all
select * from emp2;        

--割集,显示不同部分
select * from emp
minus
select * from emp2;

19、查询系统表、视图
select owner, object_name, object_type, status, dba_objects.* from dba_objects where object_type = ‘view’ and status = ‘invalid’;

select * from user_objects where object_type like 'PROCEDURE';

20、练习题
–部门最高薪资员工信息
select ename, sal, deptno from emp
where sal in (select max(sal) from emp group by deptno);

--部门最高薪资员工信息
select ename, sal, emp.deptno from emp 
join (select max(sal) max_sal, deptno from emp group by deptno) t 
on emp.deptno = t.deptno and emp.sal = t.max_sal;

--部门平均薪资等级
select grade, losal, hisal, t.avg_sal from salgrade 
join (select avg(sal) avg_sal, deptno from emp group by deptno) t
on t.avg_sal between losal and hisal;

--经理人
select ename, job from emp where empno in (select mgr from emp);

--不用分组函数，查询薪水最高值
select * from (select sal, ename from emp order by sal desc) where rownum = 1;
select distinct a.sal from emp a join emp b on a.sal > b.sal where rownum = 1;
select sal from emp where sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal);

--部门平均薪水最高的部门编号
select deptno, t.avg_sal from (select avg(sal) avg_sal, deptno from emp group by deptno) t
where avg_sal = (
    select max(avg_sal) max_sal from (select avg(sal) avg_sal, deptno from emp group by deptno)
);

select deptno, t.avg_sal from (select avg(sal) avg_sal, deptno from emp group by deptno) t
where avg_sal = (
    select max(avg(sal)) max_sal from emp group by deptno
);

--部门平均薪水最高的部门名称
select dname from dept where deptno = (
 select deptno from (select avg(sal) avg_sal, deptno from emp group by deptno) t
 where avg_sal = (
        select max(avg_sal) max_sal from (select avg(sal) avg_sal, deptno from emp group by deptno)
 )
);

select dname from dept where deptno = (
    select deptno from (select avg(sal) avg_sal, deptno from emp group by deptno) t
    where avg_sal = (
           select max(avg(sal)) from emp group by deptno
    )
);

--平均薪水最低的部门的部门名称
select dname from dept where deptno = (
  select deptno from (select avg(sal) avg_sal, deptno from emp group by deptno) 
  where avg_sal = (
    select min(avg_sal) min_sal from (
           select avg(sal) avg_sal from emp group by deptno
    )
  )
);

select dname from dept where deptno = (
    select deptno from (select avg(sal) avg_sal, deptno from emp group by deptno) 
    where avg_sal = (    
      select min(avg(sal)) avg_sal from emp group by deptno
    )
);

--平均薪水等级最低的部门的部门名称
select dname from dept where deptno = (
select deptno from (
     select grade, t.deptno from salgrade s join (
        select avg(sal) avg_sal, deptno from emp group by deptno
     ) t
     on t.avg_sal between s.losal and s.hisal
  )
  where grade = (
    select min(grade) from salgrade s join (
        select avg(sal) avg_sal, deptno from emp group by deptno
    ) t
    on t.avg_sal between s.losal and s.hisal
  )
);

--部门经理人中，平均薪水最低的部门名称
select t.deptno, dname from (
    select sal, deptno from emp where empno in (select distinct mgr from emp)
) t join dept 
on t.deptno = dept.deptno
where sal = (
    select min(sal) from emp where empno in (select distinct mgr from emp)
);

--比普通员工的最高薪水还要高的经理人名称
select * from (
    select empno, ename, sal from emp where empno in (select distinct mgr from emp where mgr is not null)
) t
where t.sal > (
    select max(sal) max_sal from emp where empno not in (
     select distinct mgr from emp where mgr is not null
    )
);

Oracle笔记二、常用dba命令行

2012-04-15

Oracle笔记二、常用dba命令行
1、给用户解锁

alter user scott account unlock;

2、注销、断开、切换当前用户连接

quit

conn scott/tiger

3、用户权限查询

A .查看所有用户：

select * from dba_users;

select * from all_users;

select * from user_users;

B.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：

select * from dba_sys_privs;

select * from user_sys_privs;

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

select * from role_sys_privs;

D.查看用户对象权限：

select * from dba_tab_privs;

select * from all_tab_privs;

select * from user_tab_privs;

E.查看所有角色：

select * from dba_roles;

F.查看用户或角色所拥有的角色：

select * from dba_role_privs; s

elect * from user_role_privs;

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

select * from V$PWFILE_USERS

4、用户管理

A、创建用户

create user username identified by password;

create user username identified by password default tablespace users quota 10M on users;

B、修改密码

alter user username identified by pass;

密码就从password改成pass了；同样登陆后输入password也可以修改密码

C、删除用户

drop user username;

drop user username cascade;

5、三种标准的角色（role）：CONNECT、RESOURCE和DBA。

I、Connect连接、登陆权限

II、Resource可以创建表、序列、过程（procedure）、触发器（trigger）、索引（index）和簇（cluster）。

III、Dba管理员

6、用户权限

A、给用户授权

grant connect, resource to username;

grant create session, create table, create view to username;

B、撤销权限

revoke connect from username;

7、角色管理

A、创建角色

create role LOGIN;

B、删除角色

drop role LOGIN;

8、导出角色资源

A、首先进入控制台cmd

B、进入某个目录，如：cd c:/userdir

C、输入命令，exp

D、提示输入用户名、口令，也就是即将导出角色的数据

E、以下直接默认，按回车enter键即可

F、导出完成后，即可看到导出的文件EXPDAT.DMP

9、导入角色数据

A、首先进入dos控制台

B、进入导出数据的目录，如：cd c:/userdir

C、输入命令，imp

D、输入用户名、口令，将此数据导入给的用户

E、后面一直回车，enter键即可

F、提示输入用户名、口令，这个是数据从哪个用户中导出的，就是那个用户的

10、Sqlplus常用命令

A、set lineSize 120可以设置sqlplus的显示缓冲区大小；

B、set pageSize 20 可以设置每页显示大小

C、edit 可以编辑当前sql语句，保存后输入/回车运行

D、spool c:/temp.sql; 和 spool off 可以保存之间所有的显示内容；

E、start [filepath]可以批量执行sql语句；

F、desc tableName 可以查看指定表结构

G、 show user 可以查看当前用户

H、 set serveroutput on 运行dbms_output输出信息

I、 show error 查看编译错误信息

11、用户常用系统表、表字典

select table_name from user_tables;

select view_name from user_views;

select constraint_name, constraint_type, table_name from user_constraints;

select table_name from dictionary;

select index_name from user_indexes;

第7章Oreacle开发PL/SQL子程序和包

2012-04-10

开发PL/SQL子程序和包

总结

子程序是已命名的PL/SQL块，可带参数并可在需要时随时调用.
子程序可以具有声明部分可执行部分和异常处理部分.
PL/SQL支持两种类型的子程序即过程和函数。
过程用于执行特定任务，而函数用于执行任务并返回值。
要将值传递给子程序，有3种参数（in int 传入 out int输出 in out 传入值输出返回更新的值）
包是一种数据库对象，他是对相关PL/SQL类型。子程序。游标。异常。变量和常量的封装。
包由两部分组成：即包规范和包体。
在包体声明的项可视为“私有”，因为他们只限于包内使用。
在包规范中声明的项也可以在包之外使用。这样的项称为：“公用对象”。
包规范包含公用对象及其类型
PL/SQL允许两个或多个打包的子程序具有同一名称。在需要子程序接受属于不同数据类型的参数是，可以使用此选项此概念称为重载。
使用包的优点是：模块化、更轻松的应用程序设计、信息的隐藏、新增功能以及性能更佳、

/包规范的创建关键字（package ）/
create or replace package 包名 is
包接口定义、让包体去实现
procedure pro_test_pack(v_id in int);
end 包名;
/包体实现包规范中定义的接口使用关键字（package body）/
create or replace package body 包名 is
实现包规范中定义的接口
end;
end 包名；

完整代码

/包规范/
create or replace packagepack_name is
v_var varchar2(100);
procedure pro_test_pack(v_id in int);
end pack_name;
/创建包体/
create or replace package bodypack_name is
procedure pro_test_pack(v_id in int) is
begin
v_var :=’adasf’;
dbms_output.put_line(v_var);
end;
end pack_name;
call pack_name.pro_test_pack(123);

第6章OreaclePL/SQL动态SQL

2012-04-09

动态SQL分为：

早期绑定和晚期绑定
编译早期绑定（编译时就确定的对象）
执行晚期绑定（不确定性要通过执行之后才确定下来）

execute immediae关键字执行动态SQL语句using是预编译返回
create or replace procedure proc_sqlstring(empid in int)is
begin
execute immediate 'delete from emp where empno=:id' using empid;
end;
call proc_sqlstring(7782);
/*1、使用动态SQL完成创建一张表*/
declare
  str_sqltring varchar2(4000);
begin
  str_sqltring := 'create table t_table(
id int primary key not null,
name varchar2(100)
)';
  execute immediate str_sqltring;
end;

第5章Oracle PL/SQL使用游标获取

2012-04-08

### 1. 游标的概述 ###

1. 隐式游标

   Oracle在执行时会自动为我们处理很多和游标相关的操作（声明游标、打开游标、提取记录关闭游标）。隐式游标是最简单最有效的获取数据的方式。
缺点：代码中存在大量相同或相似的select语句，不利于代码的重用。

2. 显式游标

显式游标用在需要获取多行记录的情况下
必须在手动的在PL/SQL块的声明部分定义
手动执行每个操作（打开、提取、数据、关闭）

3. 典型的查询操作

 a、 解析 （处理SQL语句的一个步骤是解析，确保SQL语句是有效的）
 b、 绑定（将PL/SQL程序中的变量与绑定变量关联）
  c、打开（打开游标）
 d、 执行（从SQL引擎中运行SQL语句）
 e、 提取（在游标的结果集中获取下一行）
 f、 关闭（关闭 游标）
游标获取数据库中的数据集合，然后打开提取每次提取后都会定位到下一行，然后从SGA中将数据拿到程序变量中，在此过程中是由Oracle自动将绑定变量与程序变量相关联。

4. 游标属性

      a、%found      如果记录成功获取，返回true，否则返回false
      b、%notfound   如果记录获取失败，返回true，否则返回false
      c、%rowcount   返回从游标中获取的记数
      d、%isopen     如果游标是打开的，返回true，否则返回false
      e、bulk_rowcount 返回forall语句更改的每个集合元素的记录数
      f、bulk_exception 返回forall语句更改的每个集合元素的记录的异常信息
注意：可以在PL/SQL代码中引用游标属性，不能在SQL语句中使用这些属性。

2. 使用显式游标

1. 声明显示游标
      cursor 游标名     is select 语句 for update;
                 游标名 指向的影响这一行类型%rowtype或type类型
2. 打开显式游标
      open 显式游标名；
3. 从显式游标中提取记录
      fetch 游标名 into 记录或变量列表；
4. 关闭显式游标
      close 游标名

游标for循环
```
for 记录 in 游标名 loop
   执行语句
end loop；
```
使用引用游标:
create or replace procedure proc_info(id in int) is
type cur_info is ref cursor; –类型type 游标名 IS ref cursor引用游标
cur_emp cur_info; –游标标量
cur_emp_info emp%rowtype; –影响的整列的数据
begin
open cur_emp for – 打开游标
select from emp where empno=id;
loop
fetch cur_emp into cur_emp_info; –从游标中提取数据
exit when cur_emp%notfound; –退出条件没有记录就退出
dbms_output.put_line(‘ID：’||cur_emp_info.empno|| ‘姓名：’||cur_emp_info.ename ||’工资：’||v_vv.sal);
end loop;
close cur_emp; –关闭游标
end;
call v_v(7499);
select from emp;