Oracle 基础知识

 Oracle 中有默认的2个用户一个是Sys另外一个是System
Sys:是超级用户,具有最高权限,具有SYSDBA角色,有创建数据库的权限
System:是操作管理员,具有SYSOPER角色,没有创建数据库的权限

Oracle 的卸载
软件环境：
1、Windows XP + Oracle 10g
2、Oracle安装路径为：d:\Oracle
实现方法：
1、开始－＞设置－＞控制面板－＞管理工具－＞服务停止所有Oracle服务.
2、开始－＞程序－＞Oracle – OraDb10g_home1＞Oracle Installation Products－＞ Universal Installer 卸装所有Oracle产品,但Universal Installer本身不能被删除;
3、运行regedit,选择HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE,按del键删除这个入口.
4、运行regedit,选择HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services,滚动这个列表,删除所有Oracle入口.
5、运行refedit,选择HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services \Eventlog\Application,删除所有Oracle入口.
6、开始－＞设置－＞控制面板－＞系统－＞高级－＞环境变量,删除环境变量CLASSPATH和PATH中有关Oracle的设定.
7、从桌面上、STARTUP（启动）组、程序菜单中,删除所有有关Oracle的组和图标.
8、删除c:\Program Files\Oracle目录.
9、重新启动计算机,重起后才能完全删除Oracle所在目录.
10、删除与Oracle有关的文件,选择Oracle所在的缺省目录C:\Oracle,删除这个入口目录及所有子目录,并从Windows XP目录（一般为d:\WINDOWS）下删除以下文件ORACLE.INI、oradim73.INI、oradim80.INI、 oraodbc.ini等等.
11、WIN.INI文件中若有[ORACLE]的标记段,删除该段.
12、如有必要,删除所有Oracle相关的ODBC的DSN.
13、到事件查看器中,删除Oracle相关的日志 说明:如果有个别DLL文件无法删除的情况,则不用理会,重新启动,开始新的安装,安装时,选择一个新的目录,则安装完毕并重新启动后,原来的目录及文件就可以删除掉了

Oracle 系统命令
1、连接数据库

Sql代码

1. SQL> conn 用户名/密码@网络服务名SID [as sysdba/sysoper]   当用特权用户身份连接的时,必须带上as sysdba或者as sysoper  

SQL> conn 用户名/密码@网络服务名SID [as sysdba/sysoper]   当用特权用户身份连接的时,必须带上as sysdba或者as sysoper

2、显示当前用户

Sql代码

1. SQL> show user;  

SQL> show user;

3、修改密码

Sql代码

1. 管理员自己修改密码: passw    管理员修改其他用户密码: passw  用户名    

管理员自己修改密码: passw    管理员修改其他用户密码: passw  用户名  

4、退出

Sql代码

1. SQL> exit;  

SQL> exit;

5、运行sql脚本

Sql代码

1. SQL> start c:\test.sql; 或者SQL> @ c:\test.sql;  

SQL> start c:\test.sql;或者SQL> @ c:\test.sql;

6、编辑sql脚本

Sql代码

1. SQL> edit c:\test.sql;  

SQL> edit c:\test.sql;

7、将指定查询内容输出到指定文件中去
把emp表中的数据放在d:\test.sql这个文件中

Sql代码

1. SQL> spool d:\test.sql;  
2. SQL> select * from emp;  
3. SQL> sppool off;  

SQL> spool d:\test.sql;SQL> select * from emp;SQL> sppool off;

Oracle 用户管理
切换用户

Sql代码

1. SQL> conn system/manager  

SQL> conn system/manager

创建用户

Sql代码

1. SQL> create user 用户名  identified by 密码  

SQL> create user 用户名  identified by 密码

修改别人密码

Sql代码

1. SQL> password 用户名  
2. SQL> alter user 用户名 identified by 新密码  

SQL> password 用户名SQL> alter user 用户名 identified by 新密码

删除用户(自己删除自己不可以)
如果要删除用户,并且同时要删除用户所创建在表,那么就需要在删除时带一个参数cascade;

Sql代码

1. SQL> drop user 用户名 [cascade]  

SQL> drop user 用户名 [cascade]

角色管理权限
受理连接数据库权限(connect)给一个用户

Sql代码

1. SQL> grant connect to 用户名  

SQL> grant connect to 用户名

授权username查询emp权限

Sql代码

1. SQL> grant select on emp to username  
2. SQL> grant insert on emp to username  
3. SQL> grant update on emp to username  
4. SQL> grant delete on emp to username  

SQL> grant select on emp to usernameSQL> grant insert on emp to usernameSQL> grant update on emp to usernameSQL> grant delete on emp to username

授权username对emp所有权限

Sql代码

1. SQL> grant all on emp to username  

SQL> grant all on emp to username

权限传递
如果是对象权限,权限在传递就在后面加with grant option

Sql代码

1. SQL> grant select on emp to username with grant option  

SQL> grant select on emp to username with grant option

如果是系统权限,权限在传递就在后面加with admin option 这使username可以把select权限传递给其他人

Sql代码

1. SQL> grant connect on emp to username with admin option  

SQL> grant connect on emp to username with admin option

收回username在我的emp表上在select权限

Sql代码

1. SQL> revoke select on emp from username  

SQL> revoke select on emp from username

profile 规则
账户锁定
指定账户登陆最多可以输入密码在次数,也可以指定用户锁定在时间(天)一般用dba在身份去执行该命令

Sql代码

1. SQL> create profile lock_account limit failed_login_attempts 3 password_lock_time 2;  
2. lock_account: 名称随便取名  
3. 3:最多输入3次密码  
4. 2:锁定2天  
5. 其他的都是关键字  

SQL> create profile lock_account limit failed_login_attempts 3 password_lock_time 2;lock_account:名称随便取名3:最多输入3次密码2:锁定2天其他的都是关键字

把锁lock_account给scott.如果用户连续输入3次错误密码,那么你就被锁定2天,2天后才能登陆

Sql代码

1. SQL> alter user scott profile lock_account;  

SQL> alter user scott profile lock_account;

把 profile文件删除

Sql代码

1. SQL> drop profile profilename cascade  

SQL> drop profile profilename cascade

表管理
char 与varchar2的区别
char定长 varchar2不定长.char查询效率快浪费硬盘空间,varchar2查询慢节省空间

存图片使用blob数据类型

向表里面添加一个字段

Sql代码

1. SQL> alter table tablename add(columnName varchar2(10));  

SQL> alter table tablename add(columnName varchar2(10));

修改字段长度

Sql代码

1. SQL> alter table tablename modify(columnName varchar2(30));  

SQL> alter table tablename modify(columnName varchar2(30));

修改字段的类型或名字(在这列没有数据的情况下才能修改)

Sql代码

1. SQL> alter table tablename modify(columnName varchar2(20));  

SQL> alter table tablename modify(columnName varchar2(20));

删除一个字段

Sql代码

1. SQL> alter table tablename drop column columnName;  

SQL> alter table tablename drop column columnName;

修改表名

Sql代码

1. SQL> rename tablename to newtablename;  

SQL> rename tablename to newtablename;

Oracle 中默认的时间格式是dd-mm-yyyy,而且插入时间mm必须带一个"月",也就是说insert into test(testTime)
values('01-5月-09');
可以使用nls_date_format来改变Oracle默认的日期格式

Sql代码

1. SQL> alter session set nls_date_format='yyyy-mm-dd' set timing on;  

SQL> alter session set nls_date_format='yyyy-mm-dd' set timing on;

设置查询时间点

Sql代码

1. set timing on;  

set timing on;

nvl()函数
nvl(num,0)

Sql代码

1. 如果 num为空,那么把0赋值给num  

如果num为空,那么把0赋值给num

%表示任意字符
_表示一个字符

查询比deptno=30所有人工资都高的人

Sql代码

1. SQL> select * from emp where sal> all(select sal from emp where deptno=30)  

SQL> select * from emp where sal> all(select sal from emp where deptno=30)

查询比deptno=30任何人工资都高的人

Sql代码

1. SQL> select * from emp where sal> any(select sal from emp where deptno=30)  

SQL> select * from emp where sal> any(select sal from emp where deptno=30)

给表加别名的时候不能加as,给列加别名的时候可以加as

oracle 分页有3种方式
1、rownum方式
2、rowid方式
3、分析函数来分


rownum 方式

Sql代码

1. SQL> select t.*,rownum rn from (select * from emp) t;  

SQL> select t.*,rownum rn from (select * from emp) t;

rownum 是oracle分配的行号
查询前10条数据

Sql代码

1. SQL> select t.*,rownum rn from (select * from emp) t where rownum<=10;  

SQL> select t.*,rownum rn from (select * from emp) t where rownum<=10;

注意:rownum只能用一次看下面:查询emp表  第6条-第10条数据

Sql代码

1. SQL> select * from (select t.*,rownum rn from (select * from emp) t where rownum<=10) where rn>=6;  

SQL> select * from (select t.*,rownum rn from (select * from emp) t where rownum<=10) where rn>=6;

如果要指定查询列那么只需该修改最里面的select查询(select * from emp)把这个改了就行了

合并查询用到的几个关键字

Sql代码

1. union,union all,intersect,minus  

union,union all,intersect,minus

删除表中所有记录不写日志,无法回滚

Sql代码

1. SQL> truncate table student;  

SQL> truncate table student;

设置事务保存点

Sql代码

1. SQL> savepoint a  

SQL> savepoint a

回滚事务

Sql代码

1. SQL> rollback to a  

SQL> rollback to a

常见的sql函数,字符函数

Sql代码

1. 将字符串转换为小写  
2. lower(char)  
3.   
4. 将字符串转换为大写  
5. upper(char)  
6.   
7. 返回字符串的长度  
8. length(char)  
9.   
10. 取字符串字串  
11. substr(char,start,end)  
12.   
13. 替换字符串  
14. replace(char,search_string,replace_string);  
15.   
16. 字符串的位置  
17. instr(char1,char2)  

将字符串转换为小写lower(char)将字符串转换为大写upper(char)返回字符串的长度length(char)取字符串字串substr(char,start,end)替换字符串replace(char,search_string,replace_string);字符串的位置instr(char1,char2)

数学函数

Sql代码

1. 执行四舍五入  
2. round(n,[m])  
3.   
4. 截取数字  
5. trunc(n,[m])  
6.   
7. 返回小于或等于n的最大整数  
8. floor(n)  
9.   
10. 返回大于或等于n的最大整数  
11. ceil(n)  

执行四舍五入round(n,[m])截取数字trunc(n,[m])返回小于或等于n的最大整数floor(n)返回大于或等于n的最大整数ceil(n)

日期函数

Sql代码

1. oracle 系统时间  
2. sysdate  
3.   
4. 加月数  
5. add_months(d,n)  
6.   
7. 返回指定日期在月份的最后一天  
8. last_day(d)  

oracle系统时间sysdate加月数add_months(d,n)返回指定日期在月份的最后一天last_day(d)

转换函数

Sql代码

1. to_char(d,'yyyy-mm-dd hh24:mi:ss');  
2. 数字转换成金额格式  
3. to_char()  

to_char(d,'yyyy-mm-dd hh24:mi:ss');数字转换成金额格式to_char()

系统函数sys_context()

Sql代码

1. 数据库名称  
2. SQL> select sys_context('userenv','db_name') from dual;  
3. 数据库语言  
4. SQL> select sys_context('userenv','language')  from dual;  
5. 当前用户  
6. SQL> select sys_context('userenv','session_user')  from dual;  
7. 方案名  
8. SQL> select sys_context('userenv','current_schema') from dual;  

数据库名称SQL> select sys_context('userenv','db_name') from dual;数据库语言SQL> select sys_context('userenv','language')  from dual;当前用户SQL> select sys_context('userenv','session_user')  from dual;方案名SQL> select sys_context('userenv','current_schema') from dual;

数据库管理

数据库(表)的逻辑备份与恢复
数据字典和动态性能试图
管理表空间和数据文件


SYS: 所有Oracle的数据字典的基表和试图都存放在SYS用户中,这些基表和试图对于Oracle的运行至关重要的,
由数据库自己维护,任何用户都不能手动更改.SYS用户拥有DBA,SYSDBA,SYSOPER角色或权限,是Oracle权限最高的

用户

SYSTEM: 用于存放次一级的内部数据,如Oracle的一些特性或工具的管理信息.SYSTEM用户拥有DBA,SYSDBA角色或
系统权限

SYS 用户必须以as sysdba或as sysoper形式登录.不能以normal方式登录数据库
SYSTEM如果正常登录,它其实就是个普通的dba用户,但是如果以as sysdba登录,其结果实际上它是作为sys用户登录的



数据库的导入导出
导出:
导出具体分为:导出表,导出方案,导出数据库三种方式..
导出用exp命令,该命令常用的有:
userid: 用于指定执行导出操作的用户名、口令,连接字符串
tables: 用于指定执行导出操作的表
owner: 用于指定执行导出操作的方案
full=y: 用于指定执行导出操作的数据库
inctype: 用于指定执行导出操作的增量类型
rows: 用于指定执行导出操作是否导出表中的数据
file: 用于指定导出文件名

导出表结构和数据

Sql代码

1. SQL> exp userid=soctt/tiger@orcl file=c:\workplan.dmp  

SQL> exp userid=soctt/tiger@orcl file=c:\workplan.dmp

导出指定的表结构和数据emp表和dept表

Sql代码

1. SQL> exp userid=soctt/tiger@orcl tables=(emp,dept) file=c:\workplan.dmp  

SQL> exp userid=soctt/tiger@orcl tables=(emp,dept) file=c:\workplan.dmp

导出表结构

Sql代码

1. SQL> exp userid=soctt/tiger@orcl file=c:\workplan.dmp rows=n  

SQL> exp userid=soctt/tiger@orcl file=c:\workplan.dmp rows=n

直接导出表和数据,速度非常快

Sql代码

1. SQL> exp userid=soctt/tiger@orcl  file=c:\workplan.dmp direct=y  

SQL> exp userid=soctt/tiger@orcl  file=c:\workplan.dmp direct=y

导出scott方案

Sql代码

1. SQL> exp userid=soctt/tiger@orcl owner=scott file=c:\workplan.dmp  

SQL> exp userid=soctt/tiger@orcl owner=scott file=c:\workplan.dmp

导出数据库(增量备份)

Sql代码

1. SQL> exp userid=system/manager@orcl full=y inctype=complete file=c:\workplan.dmp  

SQL> exp userid=system/manager@orcl full=y inctype=complete file=c:\workplan.dmp

导入:
导入具体分为:导入表,导入方案,导入数据库三种方式..
导出用exp命令,该命令常用的有:
userid: 用于指定执行导入操作的用户名、口令,连接字符串
tables: 用于指定执行导入操作的表
formuser: 用于指定源用户
touser: 用于指定目标用户
file: 用于指定导入文件名
full=y: 用于指定执行导入整个文件
inctype: 用于指定执行导入操作的增量类型
rows: 指定是否要导入表行
ignore: 如果表存在,则只导入数据

导入自己表

Sql代码

1. SQL> imp userid=scott/tiger@orcl tables=(emp) file=d:\gbzh2.dmp  

SQL> imp userid=scott/tiger@orcl tables=(emp) file=d:\gbzh2.dmp

导入其他用户

Sql代码

1. SQL> imp userid=system/manager@orcl tables=(emp) file=d:\gbzh2.dmp touser=scott  

SQL> imp userid=system/manager@orcl tables=(emp) file=d:\gbzh2.dmp touser=scott

导入表结构

Sql代码

1. SQL> imp userid=scott/tiger@orcl tables=(emp) file=d:\gbzh2.dmp rows=n  

SQL> imp userid=scott/tiger@orcl tables=(emp) file=d:\gbzh2.dmp rows=n

导入数据

Sql代码

1. SQL> imp userid=scott/tiger@orcl tables=(emp) file=d:\gbzh2.dmp ignore=y  

SQL> imp userid=scott/tiger@orcl tables=(emp) file=d:\gbzh2.dmp ignore=y

导入方案

Sql代码

1. SQL> imp userid=scott/tiger@orcl file=d:\gbzh2.dmp   

SQL> imp userid=scott/tiger@orcl file=d:\gbzh2.dmp 

导入其他方案

Sql代码

1. SQL> imp userid=scott/tiger@orcl file=d:\gbzh2.dmp formuser=system touser=scott  

SQL> imp userid=scott/tiger@orcl file=d:\gbzh2.dmp formuser=system touser=scott

导入数据库

Sql代码

1. SQL> imp userid=scott/tiger@orcl file=d:\gbzh2.dmp full=y  

SQL> imp userid=scott/tiger@orcl file=d:\gbzh2.dmp full=y

数据字典:
数据字典是Oracle数据库中最重要的组成部分,它提供了数据库的一些系统信息.静态信息和动态信息.
数据字典记录了数据库系统的信息,它是只读表和动态性能试图的集合,数据字典的所有者为SYS用户.
用户只能在数据字典上执行查询操作,而其维护是由系统自动完成的
数据字典包括(基表+动态性能试图)

基表:
存储着数据库的基本信息.静态信息

动态性能试图:
动态性能试图是记载了例程启动后相关信息.动态信息
user_XXX,all_XXX,dba_XXX



用户、权限、角色
数据库用户详细信息
dba_users;

可以显示所有用户具有的系统权限
dba_sys_privs

可以显示所有用户具有的对象权限
dba_tab_privs

可以显示所有用户具有的例权限
dba_col_privs

可以显示所有用户具有的角色
dba_role_privs


表空间
表空间是数据库的逻辑组成部分.从物理上讲,数据库数据存放在数据文件中;从逻辑上讲,数据库则是存放在表空
间中,表空间是由一个或多个数据文件组成

Oracle 逻辑结构包括表空间、段、区和块.
数据库有是由表空间构成,而表空间是由段构成,而段又是由区构成,
而区又是由Oracle块构成的这样的一种结构,可以提高数据库的效率

表空间用于逻辑上组织数据库的数据.数据库逻辑上是由一个或多个表空间组成的.通过表空间可以达到以下作用:
(1)控制数据库占用的磁盘空间
(2)dba可以将不同数据类型部署到不同位置,这样有利于提高I/O的性能,同时有利于备份和恢复等管理操作

创建表空间:sp001是表空间名

Sql代码

1. SQL> create tablespace sp001 datafile 'd:\sp001' size=5M uniform size 128K;  

SQL> create tablespace sp001 datafile 'd:\sp001' size=5M uniform size 128K;

表空间的几种状态
让表空间脱机

Sql代码

1. SQL> alter tablespace sp001 offline;  

SQL> alter tablespace sp001 offline;

让表空间联机

Sql代码

1. SQL> alter tablespace sp001 online;  

SQL> alter tablespace sp001 online;

让表空间只读

Sql代码

1. SQL> alter tablespace sp001 read only;  

SQL> alter tablespace sp001 read only;

让表空间可读可写

Sql代码

1. SQL> alter tablespace sp001 read write;  

SQL> alter tablespace sp001 read write;

查看表空间里面的表

Sql代码

1. SQL> select * from all_tables where tablespace_name='TABLESPACE_NAME';  

SQL> select * from all_tables where tablespace_name='TABLESPACE_NAME';

查看表所属哪个表空间

Sql代码

1. SQL> select tablespace_name,table_name from user_tables where table_name='TABLE_NAME'  

SQL> select tablespace_name,table_name from user_tables where table_name='TABLE_NAME'

删除表空间

Sql代码

1. SQL> drop tablespace sp001  

SQL> drop tablespace sp001

彻底删除表空间里面所有内容

Sql代码

1. SQL> drop tablespace sp001 including contents and datafiles;  

SQL> drop tablespace sp001 including contents and datafiles;

扩展表空间,有3种方式
1、增加数据文件

Sql代码

1. SQL>  alter tablespace sp001 add datafile 'd:\TABLESPACETEST1.dbf' size 20M;  

SQL>  alter tablespace sp001 add datafile 'd:\TABLESPACETEST1.dbf' size 20M;

2、增加数据文件大小,这里的resize不能大于500M

Sql代码

1. SQL> alter tablespace sp001 'd:\TABLESPACETEST1.dbf' resize 50M;  

SQL> alter tablespace sp001 'd:\TABLESPACETEST1.dbf' resize 50M;

3、设置文件自动增长

Sql代码

1. SQL> alter tablespace sp001 'd:\TABLESPACETEST1.dbf' autoextend on next 10M maxsize 500M;  

SQL> alter tablespace sp001 'd:\TABLESPACETEST1.dbf' autoextend on next 10M maxsize 500M;

移动表空间、数据迁移
1、确定数据文件所在的表空间

Sql代码

1. SQL> select sp001 from dba_data_files where file_name='d:\TABLESPACETEST1.dbf';  

SQL> select sp001 from dba_data_files where file_name='d:\TABLESPACETEST1.dbf';

2、使表空间在脱机状态

Sql代码

1. SQL> alter tablespace sp001 offline;  

SQL> alter tablespace sp001 offline;

3、使用命令移动数据文件到指定目标位置

Sql代码

1. SQL> host move 'd:\TABLESPACETEST1.dbf' 'c:\TABLESPACETEST1.dbf'  

SQL> host move 'd:\TABLESPACETEST1.dbf' 'c:\TABLESPACETEST1.dbf'

4、移动数据文件,在物理上移动了数据后,还必须对数据库文件进行逻辑修改

Sql代码

1. SQL> alter tablespace sp001 rename datafile 'd:\TABLESPACETEST1.dbf' to 'c:\TABLESPACETEST1.dbf'  

SQL> alter tablespace sp001 rename datafile 'd:\TABLESPACETEST1.dbf' to 'c:\TABLESPACETEST1.dbf'

5、使表空间在连机状态

Sql代码

1. SQL> alter tablespace sp001 online;  

SQL> alter tablespace sp001 online;

还有几种重要的表空间:
1、索引表空间
2、 undo表空间
3、临时表空间
4、非标准块表空间

Oracle 数据完整性
在 Oracle中数据完成性可以使用约束、触发器、应用程序(过程、函数)三种方法来实现
在Oracle中包括5种约束:not null(非空),unique(唯一),primary key(主键),foreign key(外键),check(检查)

添加表主键

Sql代码

1. SQL>   

SQL> 

添加表外键[b][/b]

Sql代码

1. SQL>   

SQL> 

添加表约束,10-20之间

Sql代码

1. SQL>   

SQL> 

删除约束

Sql代码

1. SQL> alter table tableName drop constraint 约束名称;  

SQL> alter table tableName drop constraint 约束名称;

删除主键的时候会报错,所有表引用那么就可以加cascade关键字来删除主从表的约束

Sql代码

1. SQL> alter table tableName drop primary key cascade;  

SQL> alter table tableName drop primary key cascade;

显示约束信息

Sql代码

1. SQL> select constraint_name,constraint_type,status,validated from user_constraints where table_name='表名'  

SQL> select constraint_name,constraint_type,status,validated from user_constraints where table_name='表名'

显示约束列

Sql代码

1. SQL> select column_name,position from user_cons_columns where constraint_name='约束名'  

SQL> select column_name,position from user_cons_columns where constraint_name='约束名'

列级定义
列级定义是在定义列的时同时定义约束



表级定义
表级定义在把表创建好之后,再定义约束,需要注意到是 not null 约束只能在列级定义上



索引
索引是加速数据存取的数据对象.合理的使用索引可以大大的降低I/O次数,从而提高数据访问性能.



创建索引
单列索引
要经常查询ename则就在ename上建立一个索引

Sql代码

1. SQL> select * from emp where ename='SMITH';  

SQL> select * from emp where ename='SMITH';

Sql代码

1. SQL> create index indexName on emp(ename);  

SQL> create index indexName on emp(ename);

复合索引
要经常查询job,deptno则就在job,deptno上建立一个索引

Sql代码

1. SQL> select * from emp where job='CLERK' and deptno='20';  

SQL> select * from emp where job='CLERK' and deptno='20';

Sql代码

1. SQL> create index indexName on emp(job,deptno);  

SQL> create index indexName on emp(job,deptno);

建立索引的原则
1、在大表上建立索引才有意义
2、在where语句或者连接条件上经常引用的列上建立索引



索引的缺点:
1、建立索引,系统要占用大约为表1.2倍的硬盘和内存空间来保存条件
2、更新数据的时候,系统必须要有额外的时间来同时对索引进行更新,以维持数据和索引的一致性.


[/b]
索引的分类
1: 按照数据存储方式:可以分为B*树、反向索引、位图索引
2: 按照索引列的个数分类:可分为单列索引和复合索引
3: 按照索引列值得唯一性:可分为唯一索引和非唯一索引
4: 此外还有函数索引,全局索引,分区索引
c

[b]查询索引

Sql代码

1. SQL> select * from user_indexes where table_name='表名';  

SQL> select * from user_indexes where table_name='表名';

存储过程
创建一个存储过程

Sql代码

1. create procedure sp_pro1 is  
2. begin  
3. insert into mytest values ('hanshunping','m123');  
4. end;  

create procedure sp_pro1 isbegininsert into mytest values ('hanshunping','m123');end;

创建或者替换一个存储过程create or replace

Sql代码

1. create or replace procedure sp_pro1 is  
2. begin  
3. insert into mytest values ('hanshunping','m123');  
4. end;  

create or replace procedure sp_pro1 isbegininsert into mytest values ('hanshunping','m123');end;

查看错误信息用show error;


执行过程
1、exec 过程名(参数1,参数2,参数3...)
2、call 过程名(参数1,参数2,参数3...)


exec 与call有什么区别

Sql代码

1. declare   
2. /*定义常量,变量,游标...*/  
3. v_ename varchar2(30);等价于 v_ename emp.ename%type;  
4. v_ename 表名.列名%type;  
5.   
6.   
7. begin  
8. /*要执行 pl/sql语句*/   
9. exception  
10. /*处理各种错误*/  
11. end;  
12.   
13. declare  
14.   v_ename varchar2(30);  
15. begin  
16.   select ename into v_ename from emp where empno=&no;  
17.   --&代表从控制台输入一个字符  
18.   dbms_output.put_line('用户名是:' || v_ename);  
19. end;  

declare /*定义常量,变量,游标...*/v_ename varchar2(30);等价于v_ename emp.ename%type;v_ename 表名.列名%type;begin/*要执行pl/sql语句*/exception/*处理各种错误*/end;declare  v_ename varchar2(30);begin  select ename into v_ename from emp where empno=&no;  --&代表从控制台输入一个字符  dbms_output.put_line('用户名是:' || v_ename);end;

处理异常

Sql代码

1. declare  
2.   v_ename varchar2(30);  
3. begin  
4.   select ename into v_ename from emp where empno=&no;  
5.   --&代表从控制台输入一个字符  
6.   dbms_output.put_line('用户名是:' || v_ename);  
7. exception   
8. when no_data_found then  
9.     dbms_output.put_line('没有此用户');  
10. end;  

declare  v_ename varchar2(30);begin  select ename into v_ename from emp where empno=&no;  --&代表从控制台输入一个字符  dbms_output.put_line('用户名是:' || v_ename);exception when no_data_found thendbms_output.put_line('没有此用户');end;

记录类型

Sql代码

1. declare  
2. /*定义一个  
3. 记录类型emp_record_type  
4. 包含三个数据分别是v_ename,v_sal,v_job  
5. 也就是emp_record_type储存了 v_ename,v_sal,v_job.这么一来方便了管理  
6. */  
7. type emp_record_type is record(v_ename emp.ename%type,v_sal emp.sal%type,v_job emp.job%type);  
8. /* 定义了一个变量sp_record,它的类型是emp_record_type*/  
9. sp_record emp_record_type;  
10. begin  
11.     select ename,sal,job into sp_record from emp where empno=7788;  
12. dbms_output.put_line('员工名:'||sp_record.v_ename);  
13. end;  

declare/*定义一个记录类型emp_record_type包含三个数据分别是v_ename,v_sal,v_job也就是emp_record_type储存了v_ename,v_sal,v_job.这么一来方便了管理*/type emp_record_type is record(v_ename emp.ename%type,v_sal emp.sal%type,v_job emp.job%type);/*定义了一个变量sp_record,它的类型是emp_record_type*/sp_record emp_record_type;beginselect ename,sal,job into sp_record from emp where empno=7788;dbms_output.put_line('员工名:'||sp_record.v_ename);end;

表
Oracle就相当于数组

Sql代码

1. declare  
2. /*定义一个  
3. pl/sql表类型emp_table_type,该类型用于存放emp.ename%type  
4. index by binary_integer 标识下表为整数  
5. emp_type是定义一个变量类型为emp_table_type  
6. */  
7. type emp_table_type is table of emp.ename%type index by binary_integer;  
8. /* 定义了一个变量sp_record,它的类型是emp_record_type*/  
9. emp_type emp_table_type;  
10. begin  
11.     select ename into emp_type(0) from emp where empno=7788;  
12. dbms_output.put_line('员工名:'||emp_type(0));  
13. end;  

declare/*定义一个pl/sql表类型emp_table_type,该类型用于存放emp.ename%typeindex by binary_integer标识下表为整数emp_type是定义一个变量类型为emp_table_type*/type emp_table_type is table of emp.ename%type index by binary_integer;/*定义了一个变量sp_record,它的类型是emp_record_type*/emp_type emp_table_type;beginselect ename into emp_type(0) from emp where empno=7788;dbms_output.put_line('员工名:'||emp_type(0));end;

Oracle 循环

Sql代码

1. for  
2. loop  
3. while  

forloopwhile

函数
创建函数

Sql代码

1. create or replace function sp_fun(vname varchar2)  
2. return number is vsal number;  
3. begin  
4.     select sal into vsal from emp where emp.ename=vname;  
5.     return vsal;  
6. end;  

create or replace function sp_fun(vname varchar2)return number is vsal number;beginselect sal into vsal from emp where emp.ename=vname;return vsal;end;

执行函数

Sql代码

1. SQL> var abc number;  
2. SQL> call sp_fun('SCOTT') into:abc;  

SQL> var abc number;SQL> call sp_fun('SCOTT') into:abc;

包
创建包

Sql代码

1. create package sp_package is  
2. end;  

create package sp_package isend;

创建包体

Sql代码

1. create package body sp_package is  
2. end;  

create package body sp_package isend;

触发器
触发器实质上是隐含的存储过程.当定义触发器时,必须要指定触发的事件和触发的操作,常用的触发事件包括
insert,update,delete



视图


在pl/sql程序中包括有:
标量类型
复合类型
参展类型



一个有用的分页存储过程

Sql代码

1. create or replace package body mypakage is  
2.   
3.  procedure page1(  
4.            tableName     in    varchar2,--一页显示多少条录数  
5.            pageSize      in    number,--  
6.            pageNow       in    number,--  
7.            myRows        out   number,-- 总记录数  
8.            myPageCount   out   number,-- 总页数  
9.            o_cursor      out   c_cursor)  
10.  is  
11.      v_sql      varchar2(2000);  
12.      v_begin    number:=(pageNow-1)*pageSize+1;  
13.      v_end      number:=pageNow*pageSize;  
14.  begin  
15.      v_sql := 'select * from (select t.*,rownum rn from (select * from '|| tableName ||') t where rownum <= '|| v_end ||') where rn>='|| v_begin ||'';  
16.      open o_cursor for v_sql;  
17.      v_sql:='select count(*) from '|| tableName;  
18.      execute immediate v_sql into myRows;  
19.      --if()  
20.      if mod(myRows,pageSize)=0 then  
21.         myPageCount := myRows/pageSize;  
22.      else  
23.         myPageCount := myRows/pageSize + 1;  
24.      end if;  
25.      --关闭游标  
26.      close o_cursor;  
27.  end;  
28.    
29. end mypakage;