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[assysdba/sysoper]当用特权用户身份连接的时,必须带上assysdba或者assysoper

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

2、显示当前用户

Sql代码

1. SQL>showuser;

SQL> show user;

3、修改密码

Sql代码

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

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

4、退出

Sql代码

1. SQL>exit;

SQL> exit;

5、运行sql脚本

Sql代码

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

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

6、编辑sql脚本

Sql代码

1. SQL>editc:\test.sql;

SQL> edit c:\test.sql;

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

Sql代码

1. SQL>spoold:\test.sql;
2. SQL>select*fromemp;
3. SQL>sppooloff;

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

Oracle 用户管理
切换用户

Sql代码

1. SQL>connsystem/manager

SQL> conn system/manager

创建用户

Sql代码

1. SQL>createuser用户名identifiedby密码

SQL> create user 用户名  identified by 密码

修改别人密码

Sql代码

1. SQL>password用户名
2. SQL>alteruser用户名identifiedby新密码

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

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

Sql代码

1. SQL>dropuser用户名[cascade]

SQL> drop user 用户名 [cascade]

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

Sql代码

1. SQL>grantconnectto用户名

SQL> grant connect to 用户名

授权username查询emp权限

Sql代码

1. SQL>grantselectonemptousername
2. SQL>grantinsertonemptousername
3. SQL>grantupdateonemptousername
4. SQL>grantdeleteonemptousername

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

授权username对emp所有权限

Sql代码

1. SQL>grantallonemptousername

SQL> grant all on emp to username

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

Sql代码

1. SQL>grantselectonemptousernamewithgrantoption

SQL> grant select on emp to username with grant option

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

Sql代码

1. SQL>grantconnectonemptousernamewithadminoption

SQL> grant connect on emp to username with admin option

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

Sql代码

1. SQL>revokeselectonempfromusername

SQL> revoke select on emp from username

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

Sql代码

1. SQL>createprofilelock_accountlimitfailed_login_attempts3password_lock_time2;
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>alteruserscottprofilelock_account;

SQL> alter user scott profile lock_account;

把 profile文件删除

Sql代码

1. SQL>dropprofileprofilenamecascade

SQL> drop profile profilename cascade

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

存图片使用blob数据类型

向表里面添加一个字段

Sql代码

1. SQL>altertabletablenameadd(columnNamevarchar2(10));

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

修改字段长度

Sql代码

1. SQL>altertabletablenamemodify(columnNamevarchar2(30));

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

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

Sql代码

1. SQL>altertabletablenamemodify(columnNamevarchar2(20));

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

删除一个字段

Sql代码

1. SQL>altertabletablenamedropcolumncolumnName;

SQL> alter table tablename drop column columnName;

修改表名

Sql代码

1. SQL>renametablenametonewtablename;

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>altersessionsetnls_date_format='yyyy-mm-dd'settimingon;

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

设置查询时间点

Sql代码

1. settimingon;

set timing on;

nvl()函数
nvl(num,0)

Sql代码

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

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

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

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

Sql代码

1. SQL>select*fromempwheresal>all(selectsalfromempwheredeptno=30)

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

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

Sql代码

1. SQL>select*fromempwheresal>any(selectsalfromempwheredeptno=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>selectt.*,rownumrnfrom(select*fromemp)t;

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

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

Sql代码

1. SQL>selectt.*,rownumrnfrom(select*fromemp)twhererownum<=10;

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

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

Sql代码

1. SQL>select*from(selectt.*,rownumrnfrom(select*fromemp)twhererownum<=10)wherern>=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,unionall,intersect,minus

union,union all,intersect,minus

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

Sql代码

1. SQL>truncatetablestudent;

SQL> truncate table student;

设置事务保存点

Sql代码

1. SQL>savepointa

SQL> savepoint a

回滚事务

Sql代码

1. SQL>rollbacktoa

SQL> rollback to a

常见的sql函数,字符函数

Sql代码

1. 将字符串转换为小写
2. lower(char)
4. 将字符串转换为大写
5. upper(char)
7. 返回字符串的长度
8. length(char)
10. 取字符串字串
11. substr(char,start,end)
13. 替换字符串
14. replace(char,search_string,replace_string);
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])
4. 截取数字
5. trunc(n,[m])
7. 返回小于或等于n的最大整数
8. floor(n)
10. 返回大于或等于n的最大整数
11. ceil(n)

执行四舍五入
round(n,[m])

截取数字
trunc(n,[m])

返回小于或等于n的最大整数
floor(n)

返回大于或等于n的最大整数
ceil(n)

日期函数

Sql代码

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

oracle系统时间
sysdate

加月数
add_months(d,n)

返回指定日期在月份的最后一天
last_day(d)

转换函数

Sql代码

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

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

系统函数sys_context()

Sql代码

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

数据库名称
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>expuserid=soctt/tiger@orclfile=c:\workplan.dmp

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

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

Sql代码

1. SQL>expuserid=soctt/tiger@orcltables=(emp,dept)file=c:\workplan.dmp

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

导出表结构

Sql代码

1. SQL>expuserid=soctt/tiger@orclfile=c:\workplan.dmprows=n

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

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

Sql代码

1. SQL>expuserid=soctt/tiger@orclfile=c:\workplan.dmpdirect=y

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

导出scott方案

Sql代码

1. SQL>expuserid=soctt/tiger@orclowner=scottfile=c:\workplan.dmp

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

导出数据库(增量备份)

Sql代码

1. SQL>expuserid=system/manager@orclfull=yinctype=completefile=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>impuserid=scott/tiger@orcltables=(emp)file=d:\gbzh2.dmp

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

导入其他用户

Sql代码

1. SQL>impuserid=system/manager@orcltables=(emp)file=d:\gbzh2.dmptouser=scott

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

导入表结构

Sql代码

1. SQL>impuserid=scott/tiger@orcltables=(emp)file=d:\gbzh2.dmprows=n

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

导入数据

Sql代码

1. SQL>impuserid=scott/tiger@orcltables=(emp)file=d:\gbzh2.dmpignore=y

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

导入方案

Sql代码

1. SQL>impuserid=scott/tiger@orclfile=d:\gbzh2.dmp

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

导入其他方案

Sql代码

1. SQL>impuserid=scott/tiger@orclfile=d:\gbzh2.dmpformuser=systemtouser=scott

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

导入数据库

Sql代码

1. SQL>impuserid=scott/tiger@orclfile=d:\gbzh2.dmpfull=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>createtablespacesp001datafile'd:\sp001'size=5Muniformsize128K;

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

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

Sql代码

1. SQL>altertablespacesp001offline;

SQL> alter tablespace sp001 offline;

让表空间联机

Sql代码

1. SQL>altertablespacesp001online;

SQL> alter tablespace sp001 online;

让表空间只读

Sql代码

1. SQL>altertablespacesp001readonly;

SQL> alter tablespace sp001 read only;

让表空间可读可写

Sql代码

1. SQL>altertablespacesp001readwrite;

SQL> alter tablespace sp001 read write;

查看表空间里面的表

Sql代码

1. SQL>select*fromall_tableswheretablespace_name='TABLESPACE_NAME';

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

查看表所属哪个表空间

Sql代码

1. SQL>selecttablespace_name,table_namefromuser_tableswheretable_name='TABLE_NAME'

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

删除表空间

Sql代码

1. SQL>droptablespacesp001

SQL> drop tablespace sp001

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

Sql代码

1. SQL>droptablespacesp001includingcontentsanddatafiles;

SQL> drop tablespace sp001 including contents and datafiles;

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

Sql代码

1. SQL>altertablespacesp001adddatafile'd:\TABLESPACETEST1.dbf'size20M;

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

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

Sql代码

1. SQL>altertablespacesp001'd:\TABLESPACETEST1.dbf'resize50M;

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

3、设置文件自动增长

Sql代码

1. SQL>altertablespacesp001'd:\TABLESPACETEST1.dbf'autoextendonnext10Mmaxsize500M;

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

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

Sql代码

1. SQL>selectsp001fromdba_data_fileswherefile_name='d:\TABLESPACETEST1.dbf';

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

2、使表空间在脱机状态

Sql代码

1. SQL>altertablespacesp001offline;

SQL> alter tablespace sp001 offline;

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

Sql代码

1. SQL>hostmove'd:\TABLESPACETEST1.dbf''c:\TABLESPACETEST1.dbf'

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

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

Sql代码

1. SQL>altertablespacesp001renamedatafile'd:\TABLESPACETEST1.dbf'to'c:\TABLESPACETEST1.dbf'

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

5、使表空间在连机状态

Sql代码

1. SQL>altertablespacesp001online;

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>altertabletableNamedropconstraint约束名称;

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

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

Sql代码

1. SQL>altertabletableNamedropprimarykeycascade;

SQL> alter table tableName drop primary key cascade;

显示约束信息

Sql代码

1. SQL>selectconstraint_name,constraint_type,status,validatedfromuser_constraintswheretable_name='表名'

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

显示约束列

Sql代码

1. SQL>selectcolumn_name,positionfromuser_cons_columnswhereconstraint_name='约束名'

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

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



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



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



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

Sql代码

1. SQL>select*fromempwhereename='SMITH';

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

Sql代码

1. SQL>createindexindexNameonemp(ename);

SQL> create index indexName on emp(ename);

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

Sql代码

1. SQL>select*fromempwherejob='CLERK'anddeptno='20';

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

Sql代码

1. SQL>createindexindexNameonemp(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*fromuser_indexeswheretable_name='表名';

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

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

Sql代码

1. createproceduresp_pro1is
2. begin
3. insertintomytestvalues('hanshunping','m123');
4. end;

create procedure sp_pro1 is
begin
insert into mytest values ('hanshunping','m123');
end;

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

Sql代码

1. createorreplaceproceduresp_pro1is
2. begin
3. insertintomytestvalues('hanshunping','m123');
4. end;

create or replace procedure sp_pro1 is
begin
insert 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_enamevarchar2(30);等价于 v_enameemp.ename%type;
4. v_ename表名.列名%type;
7. begin
8. /*要执行 pl/sql语句*/
9. exception
10. /*处理各种错误*/
11. end;
13. declare
14. v_enamevarchar2(30);
15. begin
16. selectenameintov_enamefromempwhereempno=&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_enamevarchar2(30);
3. begin
4. selectenameintov_enamefromempwhereempno=&no;
5. --&代表从控制台输入一个字符
6. dbms_output.put_line('用户名是:'||v_ename);
7. exception
8. whenno_data_foundthen
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 then
	dbms_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. typeemp_record_typeisrecord(v_enameemp.ename%type,v_salemp.sal%type,v_jobemp.job%type);
8. /* 定义了一个变量sp_record,它的类型是emp_record_type*/
9. sp_recordemp_record_type;
10. begin
11. selectename,sal,jobintosp_recordfromempwhereempno=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;
begin
	select 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. indexbybinary_integer 标识下表为整数
5. emp_type是定义一个变量类型为emp_table_type
6. */
7. typeemp_table_typeistableofemp.ename%typeindexbybinary_integer;
8. /* 定义了一个变量sp_record,它的类型是emp_record_type*/
9. emp_typeemp_table_type;
10. begin
11. selectenameintoemp_type(0)fromempwhereempno=7788;
12. dbms_output.put_line('员工名:'||emp_type(0));
13. end;

declare
/*定义一个
pl/sql表类型emp_table_type,该类型用于存放emp.ename%type
index 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;
begin
	select 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

for
loop
while

函数
创建函数

Sql代码

1. createorreplacefunctionsp_fun(vnamevarchar2)
2. returnnumberisvsalnumber;
3. begin
4. selectsalintovsalfromempwhereemp.ename=vname;
5. returnvsal;
6. end;

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

执行函数

Sql代码

1. SQL>varabcnumber;
2. SQL>callsp_fun('SCOTT')into:abc;

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

包
创建包

Sql代码

1. createpackagesp_packageis
2. end;

create package sp_package is
end;

创建包体

Sql代码

1. createpackagebodysp_packageis
2. end;

create package body sp_package is
end;

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



视图


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



一个有用的分页存储过程

Sql代码

1. createorreplacepackagebodymypakageis
3. procedurepage1(
4. tableNameinvarchar2,--一页显示多少条录数
5. pageSizeinnumber,--
6. pageNowinnumber,--
7. myRowsoutnumber,-- 总记录数
8. myPageCountoutnumber,-- 总页数
9. o_cursoroutc_cursor)
10. is
11. v_sqlvarchar2(2000);
12. v_beginnumber:=(pageNow-1)*pageSize+1;
13. v_endnumber:=pageNow*pageSize;
14. begin
15. v_sql:='select*from(selectt.*,rownumrnfrom(select*from'||tableName||')twhererownum<='||v_end||')wherern>='||v_begin||'';
16. openo_cursorforv_sql;
17. v_sql:='selectcount(*)from'||tableName;
18. executeimmediatev_sqlintomyRows;
19. --if()
20. ifmod(myRows,pageSize)=0then
21. myPageCount:=myRows/pageSize;
22. else
23. myPageCount:=myRows/pageSize+1;
24. endif;
25. --关闭游标
26. closeo_cursor;
27. end;
29. endmypakage;