第一,系统用户sysdba 3

1,创建表空间 3

2,创建临时表空间 4

2-1创建临时表空间 4

2-2删除表空间 4

3,创建用户 4

3-1创建用户,为用户分配表空间(表空间和临时的表空间) 4

3-2修改密码,锁定/解锁用户 5

3-3删除用户 5

3-4创建用户 (没有表空间,使用默认的表空间system) 5

4,给用户权限 5

4-1关于序列的权限 5

4-2关于会话的权限 6

4-3关于表空间的权限 6

4-4关表于的权限 6

4-5关于同义词的权限 6

4-6关于表对象的权限 7

4-7关于视图对象的权限 7

4-8关于序列的权限 7

4-9举例 7

1--给予用户 会话和创建表的权限 7

2--赋予张三创建会话的权限 8

3-回收权限 8

5,角色和权限传递 8

5-1权限传递 8

5-1-1传递系统权限用 with admin option 9

5-1-2传递用户权限用 with grant option 9

5-2角色管理： 9

5-2-1创建角色 9

5-2-2给角色赋予权限 9

5-2-3把角色给softeem 9

5-2-4收回角色,那么这个角色所拥有的权限全部收回 9

5-2-5删除角色,拥有此角色的用户对应的权限也失效 10

5-2-6授予角色 connect resource 10

5-2-7查询当前用户拥有的角色 10

6,数据字典 10

6-1、用户 10

6-2,表 11

6-3,索引 11

6-4,序列号 11

6-5,视图 11

6-6,同义词 12

6-7,约束条件 12

6-8,存储函数和过程 12

第二部分 实例用户中 12

1,实例中的用户 12

2创建表 13

2.1创建表 13

2.2查询表 13

2.3添加信息 13

2.4删除表 14

3数据类型 14

4创建视图 14

4-1创建视图 14

4-2查询视图 14

4-3删除视图 14

4-4添加视图数据 14

4-5删除视图内容 15

4-6修改视图内容 15

5索引 15

5-1创建索引 index 15

5-2删除索引 15

6同义词synonym 15

7连接 16

7-1等值连接 16

7-2右连接 --(+)在左边 16

7-3左连接--(+)在右边 16

7-4用union连接 16

8 PL/SQL代码快 16

8-1,块的举例 17

8-2连续if选择 18

8-3 if....elsif...选择 18

8-4 case ....when选择 19

8-5LOOP 循环 20

8-6 while 当什么的时候 20

8-7 for 相当于foreach 20

9异常类 21

10存储过程procedure 22

10-1存储过程的类型 22

10-1-1,无输入参数无输出参数 22

10-1--2,有输入参数无输出参数 22

10-1-3,无输入参数有输出参数 23

10-1-4,有输入参数有输出参数 23

10-1-5输入/输出参数 24

11触发器trigger 24

11-1创建触发器after update 24

11-2创建触发器after delete 25

11-3 触发器before update 25

11-4语句级触发器 : 表中的所有列 26

11-5 行级触发器 : 会执行多次 26

11-6当对 emp 表进行修改或者删除的时候 26

11-7列触发器 27

12函数 和 游标 28

12-1函数 function 28

12-1-1无参数的函数 28

12-1-2调用函数 28

12-2常见的系统函数 29

1,instr 29

2,lpad和rpad 29

3,trim 29

4,ceil 30

5,floor 30

6,round 30

7,power 30

8,trunc 30

13游标 cursor 31

13-1图解游标 31

13-2游标的分类 : 31

13-2-1隐式游标 31

13-2-2显式游标 31

13-2-3 ref游标 32

13-3在带有参数的存储过程中使用带有参数的游标 33

13-4利用游标对数据进行修改 33

14 程序包package 34

14-1调用存储过程 函数 35

14-2 PL/SQL 块中不能单独的执行select 语句 35

15 设置自动增长 36

16 分页存储过程 39

第一,系统用户sysdba

1,创建表空间

--创建表空间语法:create tablespace 表空间名称 datafile 'dbf文件路径' size 大小 autoextend on;

createtablespacekusy

datafile'kusy'size1000m autoextendon;

--创建表空间

myspace

'D:appAdministratororadataorclmyspace.dbf'

100m

;

2,创建临时表空间

2-1创建临时表空间

创建临时表空间语法:create temporary tablespace 表名 tempfile '临时表空间存放的dbf文件路径' size 大小 autoextend on;

temporarymytemp

tempfile'D:appAdministratororadataorclmytemp.dbf'10;

2-2删除表空间

--假如表空间为空了就用 drop tablespace 表空间名;

--假如表空间不为空就级联删除 drop tablespace 表空间名 including contents and datafiles

--但是现在被删除的数据库 dbf文件还是存在的 直接删除dbf文件

dropdb1pwd includingcontentsanddatafiles

3,创建用户

3-1创建用户,为用户分配表空间(表空间和临时的表空间)

--创建用户zhangsan

userzhangsan --用户名

identifiedby--密码

defaultmyspace --指定表空间

mytemp --指定临时表空间

quota5M myspace;

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

--创建用户yjh

yjh --指定临时表空间

3-2修改密码,锁定/解锁用户

--修改密码

alter用户名 密码 ;

--锁定用户

accountlock;

--解锁用户

unlock;

3-3删除用户

--新创建的用户,假如没有表了就用 drop user 用户名;

--假如有表了就级联删除 drop user 用户名 cascade;

zhangsan cascade;

3-4创建用户 (没有表空间,使用默认的表空间system)

--用户尽量避免使用系统表空间,所以我们在创建用户的时候 去指定用户自己创建的表空间和临时表空间

zhangsan;

lisi lisi;

-创建一个表空间-->临时表空间-->创建用户-->分配表空间和临时表空间

-->给新用户创建会权限,创建表权限,查询表的权限

4,给用户权限

给用户权限语法:grant 权限名 to 用户名

收回用户的权限:revoke 权限名 from 用户名

4-1关于序列的权限

1. Create sequence 允许被授权者在他们自己的模式中创建新的序列

2. CREATE ANY SEQUENCE允许被授权者在任意一个模式中创建新的序列

3. ALTER ANY SEQUENCE允许被授权者修改数据库中任意一个序列的属性

4. DROP ANY SEQUENCE允许从数据库内的任意一个模式中删除任意一个序列

5. SELECT ANY SEQUENCE

4-2关于会话的权限

1. CREATE SESSION允许被授权者连接到数据库。该特权对用户账户是必需的，但对软件账户可能是不受欢迎的。

2. ALTER SESSION允许被授权者执行ALTER SESSIONS语句

3. ALTER RESOURCE COST允许被授权者修改ORACLE为一个概况中的资源约束计算资源成本的方式。

4. RESTRICTED SESSION允许数据库在RESTRICTED SESSION模式时连接到数据库，一般是为了管理性目的。

4-3关于表空间的权限

1. CREATE TABLESPACE允许创建新的表空间

2. ALTER TABLESPACE允许被授权者更改现有表空间

3. DROP TABLESPACE允许删除表空间

4. MANAGE TABLESPACE允许更改表空间。例如ONLINE、OFFILE、BEGIN BACKUP或END BACKUP

5. UNLIMITED TABLESPACE允许消耗任意一个表空间中的磁盘限额。相当于给指定授权者每个表空间中的无限限额。以上介绍Oracle系统特权。

4-4关表于的权限

1. CREATE TABLE允许在自己的对象模式中创建表

2. CREATE ANY TABLE允许在任意一个对象模式中创建表

3. ALTER ANY TABLE允许更改任意一个对象模式中的表

4. DROP ANY TABLE允许从任意一个对象模式中删除表

5. COMMENT ANY TABLE允许给任意一个对象模式中的任意一个表或列注释

6. SELECT ANY TABLE允许查询任意表

7. INSERT ANY TABLE允许插入新行到任意表

8. UPDATE ANY TABLE允许更新任意表

9. DELETE ANY TABLE允许删除任意表中的行

10. LOCK ANY TABLE允许执行一条LOCK TABLE来明确锁定任意一个表

11. FLASHBACK ANY TABLE允许使用AS OF 语法对任意一个对象模式的任意一个表或视图执行一个SQL回闪查询。

4-5关于同义词的权限

1. CREATE SYNONYM允许在自己的对象模式中创建同义词

2. CREATE ANY SYNONYM允许在任意对象模式中创建新的同义词

3. CREATE PUBLIC SYNONYM允许被授权者创建新的公用同义词。这些同义词对数据库中的所有用户都是可访问的。

4. DROP ANY SYNONYM允许从任意对象模式中删除任意一个同义词

5. DROP PUBLIC SYNONYM允许被授权者从数据库中删除任意一个公用同义词

4-6关于表对象的权限

1. select允许查询指定表

2. INSERT允许在指定表创建新行

3. UPDATE允许修改指定表的现有行

4. DELETE允许删除指定表的行

5. ALTER允许添加、修改或重命名指定表中的列，转移该表到另一个表空间，乃至重命名指定表。

6. DEBUG允许被授权者借助于一个调度程序访问指定表上的任意触发器中的PL/SQL代码

7. INDEX允许被授权者在指定表上创建新的索引

8. REFERENCES允许创建参考指定表的外部键约束

4-7关于视图对象的权限

1. SELECT查询指定视图

2. INSERT允许在指定视图创建新行

3. UPDATE允许修改指定视图的现有行

4. DELETE允许删除指定视图的行

5. DEBUG允许被授权者借助于一个调度程序访问指定视图上的任意触发器中的PL/SQL代码

6. REFERENCES允许创建参考指定视图的外部键约束

4-8关于序列的权限

1. SELECT允许访问当前值和下一个值(即CURRVAL和NEXTVAL)

2. ALTER允许修改指定序列的属性

4-9举例

例如:新创建的用户连基本的登陆权限就没有,必须以管理员的身份赋予权限

1--给予用户 会话和创建表的权限

可以进行的操作为:连接数据库,创建表,查询表内容容

grantsessiontoyjh;

tableselecttb_user 无限使用表空间

unlimitedrole_test;

revokefrom* user_role_privs;

tb_user;

2--赋予张三创建会话的权限

zhangsan;

--无限使用表空间的权限

--允许数据库的权限

connect--创建序列的权限

sequence--赋予用户创建表的权限

--赋予用户查询某张表的权限

--赋予用户某张表的所有权限

all-----------------------------------

3-回收权限

--语法:revoke 权限名 from 用户名 ;

5,角色和权限传递

5-1权限传递

系统权限：grant create session to zhangsan with admin option;

(表示把系统权限授予给zhangsan，并允许其授予给其他用户)

用户权限：grant update on mytab to lisi with grant option;

(表示把用户权限授予给lisi，并允许其授予给其他用户)

5-1-1传递系统权限用 with admin option

withadminoption;

5-1-2传递用户权限用 with grant option

scott.emp ---回收了zhangsan的用户权限,那么经过zhangsan传递的权限全部失效

---回收了zhangsan的系统权限,那么经过zhangsan传递的权限不会失效

zhangsan;

5-2角色管理：

创建角色：create role roleName;

给角色授予权限：grant 权限 to roleName;

将角色授予给用户：grant roleName to userName;

用户查询拥有的角色：select * from user_role_privs;

删除角色：drop role roleName;

当给角色授予权限的时候，拥有此角色的用户也同时增加了权限；

当撤销角色权限的时候，拥有此角色的用户的对应权限也被撤销；

当角色被删除，拥有此角色的用户将丧失之前角色所有的所有权限。

5-2-1创建角色

创建角色role_test-拥有创建session和创建table的权限

rolerole_test;

5-2-2给角色赋予权限

role_test;

5-2-3把角色给softeem

role_test softeem;

5-2-4收回角色,那么这个角色所拥有的权限全部收回

softeem;

5-2-5删除角色,拥有此角色的用户对应的权限也失效

role_test;

5-2-6授予角色 connect resource

li;

resourceli;

5-2-7查询当前用户拥有的角色

6,数据字典

6-1、用户

--查看当前用户的缺省表空间

username,default_tablespace user_users;

--查看当前用户的角色

user_role_privs;

--查看当前用户的系统权限和表级权限

user_sys_privs;

user_tab_privs;

--显示当前会话所具有的权限

session_privs;

--显示指定用户所具有的系统权限

dba_sys_privs wheregrantee='GAME'--显示特权用户

v$pwfile_users;

--查看用户下所有的表

user_tables;

--查看用户下所有的表的列属性

USER_TAB_COLUMNS table_name=:table_Name;

--显示用户信息(所属表空间,用户名区分大小写)

default_tablespace,temporary_tablespace

dba_users username='SCOTT'--显示用户的PROFILE

profile

6-2,表

--查看名称包含log字符的表

object_name,object_id user_objects

instr(object_name,'LOG')>0--查看某表的创建时间

user_objects object_name=upper('&table_name');

--查看某表的大小

sum(bytes)/(1024*) as"size(M)" user_segments

segment_name=upper(--查看放在Oracle的内存区里的表

table_name,128)">cacheuser_tables instr(,255)">'Y'

6-3,索引

--查看索引个数和类别

index_name,index_type,table_name user_indexes ordertable_name;

--查看索引被索引的字段

user_ind_columns index_name=upper('&index_name'--查看索引的大小

);

6-4,序列号

--查看序列号，last_number是当前值

user_sequences;

6-5,视图

--查看视图的名称

view_name user_views;

--查看创建视图的select语句

setview_name,text_length user_views;

long2000; 说明：可以根据视图的text_length值设定set 的大小

text user_views view_name=upper('&view_name'

6-6,同义词

--查看同义词的名称

user_synonyms;

6-7,约束条件

--查看某表的约束条件

constraint_name,constraint_type,search_condition,r_constraint_name

user_constraints table_name = upper(c.constraint_name,c.constraint_type,cc.column_name

user_constraints c,user_cons_columns cc

c.owner = upper('&table_owner'c.table_name = upper()

c.owner = cc.owner c.constraint_name = cc.constraint_name

cc.position;

6-8,存储函数和过程

--查看函数和过程的状态

object_type='FUNCTION''PROCEDURE'--查看函数和过程的源代码

all_source owner=name=upper('&plsql_name'

第二部分 实例用户中

1,实例中的用户

--Nomal 普通的用户角色

--sysdba 超级用户管理员

--syspoer 系统管理员

--sys 网络管理员用户,具有最高的数据库管理权限,可以创建数据库

--system 本地管理员角色,具有权限仅次于sys的最高的数据库管理权限,不可以创建数据库

--scott 普通用户,默认的权限是锁住的,需要自己打开

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

---sys system scott 都是默认的用户,我们也可以自己创建用户去连接数据库,但是没有任何权限,需要dba给予权限

---表空间:包含 表 视图 索引

---段:包含 数据段 索引段

---数据块: 数据块是最小的逻辑存储单元

2创建表

2.1创建表

tb_user(

stu_num intprimarykeystu_name varchar2(20notnullstu_sex char4'男'check(stu_sex in'女')),128)">stu_birthday date

);

2.2查询表

--查看表的存储信息

table_name='TB_USER'--查看表的相关信息(属性类型和长度)

user_tab_columns ;

2.3添加信息

insertintotb_user(stu_num,stu_name) values'1001''a'1002'b(to_number('1003'),255)">c'1997-02-03''yyyy-mm-dd'));

tb_user;

2.4删除表

drop table tb_user;

3数据类型

--标量类型

--数值型 : number(integer,float)

--字符型 : char(2000) varchar(4000) long(2G)

--日期型 : date timestamp(不推荐)

--大字段LOB : BLOB CLOB

--属性类型

--%type

--%rowtype

4创建视图

视图: (1)查询不会影响基表

(2)增删改视图的时候,更新的相应信息同时也会更新基表的对应信息

(3)删除视图 基表还存在不会影响

tb_user;

4-1创建视图

viewv_user tb_user;

4-2查询视图

v_user;

4-3删除视图

v_user;

4-4添加视图数据

v_user(stu_num,STU_BIRTHDAY) 1005'甘小娟''1993-5-6'1009'甘娟'sysdate

4-5删除视图内容

deletestu_num=;

4-6修改视图内容

updatestu_name='大话单'1002;

5索引

--创建索引 针对表中的某一个经常用的字段

---在查询的时间会自动调用,会使查询的速度加快

5-1创建索引 index

语法:create index 索引名 on 表名(索引字段)

indexindex_emp_sal emp(sal);

---根据索引字段查询,在查询时索引是自动调用的

emp sal between;

5-2删除索引

index_emp_sal;

6同义词synonym

--创建同义词:对同义词的操作会影响到原表 synonym

--给emp创建同义词e,e 就可以代表emp

synonyme foremp;

--e=emp 意思相同

e;

sal=801ename='SMITH'--删除同义词

e;

7连接

7-1等值连接

emp,dept emp.deptno=dept.deptno empno;

emp.*,dname,loc

7-2右连接 --(+)在左边

打印了没有员工的部门 (+)在左边的为右连接,在右边的为左连接

emp.deptno(+)=dept.deptno empno;

7-3左连接--(+)在右边

dept,emp emp.deptno=dept.deptno(+) empno;

7-4用union连接

--结果集的操作 union的左边和右边查询的结果在一个表中,但是两边显示的结果的个数和类型要一样

empno,ename uniondeptno,dname dept;

8 PL/SQL代码快

代码块描述一个过程 里面包含一个语句

:

declare

声明部分

begin

业务逻辑处理

exception

异常处理部分

end

注释在声明部分定义我们需要使用的变量,不用在每个变量前使用'declare'来声明,变量名不用使用'@'符号

8-1,块的举例

declare

num number);--定义的两个变量

str 50:=;--在变量声明的部分给变量初始值

tsal begin

--给变量赋值

str:='王向新是个大逗比.....'--通过select语句给多个变量赋值

count(*),128)">(sal) num,tsal emp;

dbms_output.put_line(num ||'人的总工资为:'||tsal);

--into 是给变量赋值

-- select count(*) into num from emp;

--把值打印出来,在上面的Output看结果

--在PL/SQL中字符串拼接用 '||'

num:=num+m;

dbms_output.put_line(num || str || m ||'--->'|| tsal);

end--查询员工的数量,如果数量大于5就打印 '哈哈'否则打印'呵呵'

;

(*)sal>3000if(num>)then

dbms_output.put_line('员工的数量为:'|| num);

'哈哈'else

'呵呵'--------------------------

'@@@@@员工的数量为:'3'%%%%%%%%%%员工的数量为:''&&&&&&&&&&&员工的数量为:''嘻嘻';

8-2连续if选择

esal --查询SMITM的工资赋值给esal

(esal<'小伙子,要努力啊'(esal>esal<=40005000'土豪啊,我们做朋友吧''有钱的任性,没钱的认命'--------------------------------------------

8-3 if....elsif...选择

--查询SMITM的工资赋值给esal*************

elsif);

( esal<=----------------------------------

8-4 case ....when选择

40n '&请输入员工姓名'--弹出窗口输入

--将Scott的工资给esal

--变量赋值

m:=esal/n:=esal/-- esal:=esal/1000;

(n>m)n:=n-1casen

when'不及格''有进步'2'还不错''很牛''盆友''巅峰'dbms_output.put_line(n||'-->'||str);

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

8-5LOOP 循环

m:=loop

'aaa-->'||m);

m:=m+exitm=--当 m=10 的时候就退出,终止循环

loop---------------------------------

8-6 while 当什么的时候

whilem<10

;

8-7 for 相当于foreach

r emp%rowtyper emp)

dbms_output.put_line(r.ename ||||r.empno);

;

9异常类

--oracle中的异常分为:系统异常(编号 0~20000) 和 用户异常

--用户异常(编号可用 20001 到 20999)

--系统异常:直接由oracle数据库进行管理,当出现了系统异常,数据库会自动提示

--用户异常:

--1 在声明部分声明异常

--2 在代码部分出现异常通过 raise关键字抛出异常

--3 在exception部分通过when 来处理异常

--4 在exception部分我们可以通过raise_application_error(错误编号,'错误提示');

--也就是说将异常以系统提示的形式弹出,告知用户

--以后的oracle系统中抛出异常以后,可以通过 'ORA-编号' 搜索解决方案

--对异常的处理

me01 exceptionme02 score beginscore:='&请输入成绩'(score<--抛出第一种异常,提示分数小于0 不合法

raiseme01;

else(score>--抛出第二种异常,提示分数大于100 不合法

me02;

'成绩合法'||score);

exception

'分数小于0 不合法,输入的为:'||score);

--系统异常,会弹出error框提示

--raise_application_error(-20001,'数据错误');

'分数大于100 不合法,128)">;

10存储过程procedure

--一个任务需要多个步骤完成 通常一个select语句只能完成一个步骤,而单个的select语句是没有特定的业务逻辑关系的,所以我们把多个select语句放在一个PL/SQL代码块中

--这样以来代码快就可以完成特定的任务

:

procedure<proName>[(参数一,参数二....)]

as

;

10-1存储过程的类型

10-1-1,无输入参数无输出参数

orreplacetest1

'编号 姓名 工作'dbms_output.put_line(r.empno||' '||r.ename||||r.job);

--调用存储过程

calltest1();

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

10-1--2,有输入参数无输出参数

test2(esal emp.sal%type||' 的工资为: '||esal);

--调用

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

10-1-3,无输入参数有输出参数

--用 out 来声明参数为输出参数

--out 放在输出参数名和类型之间

test3(tsal out--在PL/SQL块中间调用存储过程,不需要写'call'调用

--调用存储过程时,输出参数无需使用out声明

ts test3(ts);

'总的工资为'||ts);

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

10-1-4,有输入参数有输出参数

test4(salary '&员工的姓名'||salary);

;

10-1-5输入/输出参数

--定义存储过程可以给 存储过程指定参数(输入参数/输出参数)

--通过 'in' 来声明一个参数为输入参数(可以省略)

--通过 'out' 来声明一个参数为输出参数(不可以省略)

--通过 'in out' 来声明一个参数既是输入参数又是输出参数

test5(param job param ename=param;

p ):='KING'test5(p);

dbms_output.put_line(p);

--把系统时间转换成指定的格式

--dual 为oracle的伪表

to_char(dual;

11触发器trigger

触发器: 触发器就是一种无需调用(在发生了某个事件时) 就自动调用执行的存储过程

触发时间

--before : 在触发的事件执行之前执行触发器

--after : 在触发事件执行之后再执行触发器

11-1创建触发器after update

--创建触发器 after 修改之后执行触发器

triggert_test1 aftertb_user

logid c d date--查询tb_log表 总记录条数给 c

(*) tb_log;

(c>--查询tb_log表 把最大的log_id给logid

max(log_id) logid:=logid+dual;

insertintotb_log(log_id,log_date,log_oper)(logid,d,255)">'update'触发:'李氏';

11-2创建触发器after delete

--创建触发器 after 删除之后执行触发器

t_test2 tb_log (log_id,255)">'delete';

11-3 触发器before update

--触发器 befor在执行事件之前进行执行

t_test3 beforeemp

declaredbms_output.put_line(r.ename||||r.sal);

;

11-4语句级触发器 : 表中的所有列

--当前的触发器只是普通的触发器,监听表中的所有的列

--只要对emp表 进行修改就会触发

--语句级触发器不管影响多少条语句都执行一次

--行级触发器 影响几次 就执行几次t_test4 ;//多条记录

'***********'------------------------------------------------------

--修改触发

job='softeem'------------------------------------------------------

11-5 行级触发器 : 会执行多次

t_test5 eachrow

'<><><><<<><><><><'------------------------------------------------------

触发条件

sal=sal+deptmo=

11-6当对 emp 表进行修改或者删除的时候

t_test6 emp

'&&&&&&&&&&&&';

11-7列触发器

--列触发器 : 在操作后面通过 'of 列名'来指定列触发器(update insert)

--只监听一列数据 当列进行修改的时候 执行该触发器

--update job列时执行该触发器

-----触发器

t_test7 ofdbma_output.put_line('%%%%%%%%'-------------------------------------------------

tb_user;

1010'张三''2013-2-2'));

--:new 用在insert中表示新添加的这条数据

t_test8 row

(:new.stu_sex='数据正确''数据错误'-----------------------------

--:old 用在delete语句中 表示删除的语句

--触发器

t_test9 dbms_output.put_line(:old.stu_num||||:.stu_name);

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

--禁用触发器

disable--解除禁用

enable;

12函数 和 游标

12-1函数 function

函数 : 是一个特殊的存储过程 相当于java中的方法

--存储过程只有参数(输入/输出参数)

--函数总有输入参数 但是有返回值

--函数 的定义:有参数 function

functionf_test1(m returnnumber

a a:=m+n;

a;

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

12-1-1无参数的函数

f_test2

varchar2

--into 一种给变量的赋值方式,把值给name

dname dept deptno=;

12-1-2调用函数

--1,通过dual 系统表(数据字典)调用

f_test1(f_test2() dual;

--2,在 PL/SQL 块中调用

s --调用方法 并把返回值赋给 s

s:=f_test1(9dbms_output.put_line(s);

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

12-2常见的系统函数

1,instr

instr(str1,str2):indexof

--返回第二个字符串在第一个字符串中第一次出现的位置

--如果第一个字符串中不包含第二个字符串返回 0

num:=instr('HELLOWORD''H'dbms_output.put_line(num);

;

2,lpad和rpad

lpad(str1,len,str2) : 将 str2 拼接到 str1 的左边,直到str1的长度等于 len

rpad(str1,str3) : 将 str2 拼接到 str1 的右边,直到str1的长度等于len

str:=lpad('abcd'7'1234'dbms_output.put_line(str);

str:=rpad(;

3,trim

--trim:去除字符串首尾的空格

--ltrim:去除字符串左边(首)的空格

--rtrim:去除字符串右边(尾)的空格

str:=rtrim(' abc bbb ''****'||str||'******';

4,ceil

--ceil(num):返回大于等于num 的最小整数(只入不舍)

dbms_output.put_line(ceil(15.1));

;

5,floor

--floor(num):返回小于等于num 的最小整数(只舍不入)

dbms_output.put_line(floor(15.999;

6,round

--round(num) : 四舍五入

dbms_output.put_line(round(;

7,power

--power(num1,num2) : 返回 num1 的 num2 次方的值

power(

8,trunc

--trunc(num1,num2)保留num1的num2位小数

trunc(3.1415926dual;

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

13游标 cursor

--游标概念:游标相当于一个结果集,用来存储和遍历

--一个查询SQL的结果集---> ResultSet

13-1图解游标

cursormy_cur <---select * from emp

my_cur----->r

my_cur;

13-2游标的分类 :

--静态游标 :在定义静态游标的时候要指定其结果集

--隐式游标:(系统游标)无需定义其初始化,直接使用

--显示游标:(自定义游标)用户自己定义的游标变量,并手动将结果集赋值给游标

--ref 游标:动态赋予结果集

13-2-1隐式游标--隐式游标:是由oracle系统提供,我们可以直接使用的

--特性:自动创建打开和关闭

--隐式游标的属性:

--SQL%FOUND--如果SQL游标有结果集返回true

--SQL%NOTFOUND--如果SQL游标没有结果集返回true

--SQL%ROWCOUNT--返回SQL游标中的记录条数

--SQL%ISOPEN--始终返回false.

SQL%ROWCOUNTtb_user;

13-2-2显式游标

--显式游标: 用户自己定义的游标,在定义的时候同时需要通过'IS'指定结果集

--特性:

--打开游标--->取值---->关闭游标

my_cur ISe emp%--打开游标

openmy_cur;

--对游标进行抓取

fetchmy_cur%NOTFOUNDdbms_output.put_line(e.empno);

--关闭游标

close---for 循环变量游标,无需使用手动打开和关闭游标

--把查询emp表记录的值 给游标

is--对游标进行循环打印

dbms_output.put_line(e.ename||||e.sal);

;

13-2-3 ref游标

--ref 游标:动态结果集游标,在声明的时候无需指定结果集

--声明一个ref游标 : 1声明一个类型为 ref cursor

my_type refres my_type; -->studernt---stu

sqlStr 200--存放sql查询语句

--结果对象(存放查询出的某一条记录)

sqlStr:='select * from emp'--打开游标 通过for来指定结果集

res sqlStr;

res%notfound--当游标没有值的时候就退出

dbms_output.put_line(e.empno||res;

13-3在带有参数的存储过程中使用带有参数的游标

emp1(sal1 :=sal1+sal2;

--声明一个游标的时候可以带参数,在is后面的select中可以使用参数

my_cur(p1 p1 p2;

--当打开游标的时候将参数传递给游标 先是SQL语句的加载参数 然后

--执行SQL语句,将执行的结果赋值给游标

my_cur(sal1,sal2);

my_cur;emp1();

ename like'%M%'---创建游标

--for update 和下面的 current of 进行对应

--游标遍历出一个符合条件的用户,我们就修改一个

currentcommit;

14 程序包package

--用来存放存储过程 函数 游标 等

--程序包有两部分组成:包的声明部分(package),包的实现部分(package body).

--package :　只包含存储过程　触发器　游标　函数　等的声明（接口）

--package body : 存储过程 触发器 函数 等的实现部分

packagepack_test1

is

proc_test1(num1 --函数 没有参数的时候函数名后面不带括号

func_test1 mytype --定义游标 类型为 ref cursor

pack_test1;

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

bodyis

--对存储过程的实现

num1 num2)

;

--对函数的实现

num;

pack_test1;

14-1调用存储过程 函数

--包中的存储过程和函数的调用与普通的调用方式相同

--只是在存储过程/函数前面加上'包名'

pack_test1.proc_test1(pack_test1.func_test1 dual;

14-2 PL/SQL 块中不能单独的执行select 语句

--如果有select语句 那么就在PL/SQL块中就是赋值语句

empno empno =122---------------------------------------------

emp2(sal1 pack_test1.mytype)

'select * from emp where sal between 1000 and 2000'cs sqlstr;

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

--调用带存储过程的游标

cur pack_test1.mytype;--包里面的游标 ref cursor

emp2(cur cur%;

15 设置自动增长

--序列(sequence) : MySQL : auto_increment SQLServer:identity by (1,1)

--oracle 中的列不能实现自动增长 我们可以使用序列来实现

--创建序列

--序列名: seq_test1

--序列的初始值: 1

--序列的增量: 1

--序列的最大值: 9999

----获取序列当前值: currentVal

----下一个值 : nextVal

---获取到下一个值,并把下一个值设置为当前值

--第一次nextVal返回的是初始值,随后nextVal会自动增长你定义的 increment by

--然后返回的是增加后的值

--currentval 返回的是当前sequence 的值 但是在第一次使用nextVal初始之后才能使用currentval 否则回报错

--如果指定cache 值(缓存):oracle就会预先在内存里放一些sequence 这样在获取的时候,就更快一点,cache里面取完之后 oracle会再存放一组到cache中使用cache的时候或许会跳号 必须数据库正常的关闭,0)">否则这个时候cache中的sequence会丢失,所以在创建序列的时候尽量nocache防止这种情况.

seq_test1

start--序列从 1 开始

increment--每次增长 1

maxvalue9999--最大值 nomaxvalue(不设置最大值)

nocache--缓冲区 默认20,nocache(不建立缓冲区)

//结束

users(

user_id user_name uniqueuser_pwd 16users (seq_test1.nextval'aa''123'nextVal'bb''124'users;

roles--清空表

truncateusers;

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

--创建一张角色表

(

role_id role_name 32role_des )

--共用一个序列

'系统管理员''最高权限''游客''普通权限'------------------------------------------------------------------

--创建班级表

tb_class(

class_no --主键

class_name class_num null

--多种数据表存储结构.常用的三种:

--3,聚簇表(cluster table)

--创建学生表

tb_student(

stu_no --将tb_class表的class_no 字段设置成外键foreign(class_no) referencestb_class(class_no)cascade

organization--按索引组织的表,如果要将一张表设置成按索引的表,这张表就一定要有主键

storage(--设置存储的参数

initialk ---初始区间的大小

next--下一个区间的大小

minextents6--最小区间数

maxextents99--最大区间数

pctincrease--下一个区间相对与上一个区间的增量,建议为0,减少系统开销

tb_class;

tb_student;

----添加数据

tb_class '1班''java班'33'2班''安卓班'30tb_student '钟竹'--设置了级联删除,直接删除主键,会把外键一起删除

class_no=-------------------------------------------------------------

--伪列

rownum1500dept;

--伪列 rownum 是oracle 自带的,对查询的结果自动排序

<=---------------------------------------分-页-----------------------------------------

---创建存储过程分页

split01(

tableName pk pageSize pageNum sys_refcursor

sqlStr2 excCount :=pageSize*(pageNum-sqlStr2:=' select '||pk|| ' from '||tableName|| ' where rownum<='||excCount ;

'select * from '' where '' not in ('||sqlStr2|| ') and rownum<='||pageSize ;

dbms_output.put_line(sqlStr);

注释:分页SQL语句

Select * from 表名 where pk(分页的排序主键) not in (select pk from 表名 where rownum <= (当前页-1)*每页显示的个数 ) and rownum<=每页显示的个数

sys_refcursorsplit01('emp''empno'cs%;

--页数

--每页的记录数

--表

priColum condition --分页条件

--输出参数

pageCount --总页数

recordCount --总记录数

resultCur sys_refcursor--游标:存放查询的数据

vsql --分页sql语句

vsql2 vsql3 --统计总记录数

pNum pNum:=pageNum;--页码

vsql3:='select count(*) from '||tableName||' where 1=1 '||condition;

executeimmediaterecordCount;

--计算总页数

pageCount:=ceil(recordCount/pageSize);

--判断pageNum的合法性

pageNum<=pNum:=pageNum>=pageCount pNum:=pageCount;

--拼接分页sql语句

vsql2:='select '||priColum||' where rownum<= '||pageSize*(pNum-vsql:=' not in ( '||vsql2||' ) and rownum<= '||pageSize||dbms_output.put_line(vsql);

--将vsql执行结果赋值给游标

vsql;

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

--调用带判断的存储过程分页

pc rc rcur pack_test1.mytype;--包里游标

proc_split2('and sal>1500''总记录数:'||rc);

'总页数:'||pc);

rcur rcur%;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!