oracle初学知识点总结

1. dba,sysdba,sysoper 区别

dba 是一种角色；角色可以包含系统权限，对象权限和角色；dba不包含sysdba,sysoper权限；

sysdba ，sysoper 是两种系统权限；这两种权限比较大最好以sys，system登陆管理，不要赋给普通用户；

2. oracle的public

public 可以理解为所有用户的集合；

grant dba to public ; 相当于所有用户都是dba；

---plsql编程：

plsql可以编写：过程，函数，触发器；

过程，函数，触发器在oracle中；

plsql是非常强大的数据库过程语言；

过程，函数可以在java中调用；

plsql优点：

提高性能；（传统的jdbc连库，执行sql，编译成数据库语言，返回值，关闭连接，多次调用多次编译，写成过程只编译一次，对调随用；）

模块化设计思想；（分页过程，订单的过程，转账的过程）

减少网络传输量；（本来需要传个大sql，现在传个过程名字就行了）

安全性高；（oracle数据库的保护，字段什么的都在库里面）

plsql缺点：

移植性不好；（oracle语言写的过程，这些过程在DB2上是没法使用的）

1.创建一个存储过程

create or replace procedure p1 is

begin

insert into mytable values('gao','123456');

end;

/

2.存储过程编程报错，查看错误：

show errors;

3.如何执行存储过程

exec p1;

------编写规范

1.单行注释： --

多行注释：/*...*/ （java中一样）

2.标示符号的命名规范：

变量：v_ (v_val)

常量：c- （c_val）

游标：-cursor (emp_cursor)

例外：e_ (e_error)

3.块结构示意图

pl/sql 块有三部分组成：定义部分，执行部分，例外部分

declear /* 定义部分-定义常量，变量，例外，复杂数据类型*/

begin /* 执行部分-要执行的pl/sql语句和sql语句*/ 必须有

exception /*例外处理部分-处理运行的各种错误*/可选

public static void main(Sting args){

int a; //定义部分

try{

a++; //执行部分

}catch(Exception e){ //异常处理部分

}

}

--只包含执行快部分的块

set serveroutput on --打开输出服务；

begin

dbms_output.put_line('hello,World！');

end;

/

--包含声明和执行部分的pl/sql块；

declare

v_passwd varchar2(10);

v_name varchar2(20);

begin

select passwd ,name into v_passwd ,v_name from mytable where name=&name; --地址符表示从控制台接受变量；

dbms_output.put_line(v_name||'的密码为：'||v_passwd);

end;

/

--包含声明，执行和 异常部分的pl/sql块

declare

v_passwd varchar2(10);

v_name varchar2(20);

begin

select passwd ,name into v_passwd ,v_name from mytable where name=&name; --地址符表示从控制台接受变量；

dbms_output.put_line(v_name||'的密码为：'||v_passwd);

exception

when no_data_found then

dbms_output.put_line('输入的用户名不存在!');

end;

----过程

过程用于执行特定的操作；可以有输入参数和输出参数，

--eg4:

create procedure changepasswd(name1 varchar2,passwd1 varchar2) is

begin

--根据用户名修改密码

update mytable set passwd=passwd1 where name=name1;

end;

-- plsql中执行

call changepasswd('wucaiming','123');

--在java中调用存储工程

//加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

//得到连接

Connection c=DriverManager.getConnection("jdbc:oracle:thin:@192.168.100.145:1521:SDZC","omgap","omgap");

//创建CallableStatement

CallableStatement cs=c.prepareCall("{call changepasswd(?,?)}");

//给？赋值

cs.setString(1,"wucaiming");

cs.setString(2,"123");

//执行

cs.execute();

/* 如何使用过程返回值？*/

函数用于返回特定的数据。

--eg5:

create or replace function f_returnpasswd(name1 varchar2) return varchar2 is

pwd varchar2(30);

begin

select passwd into pwd from mytable where name=name1;

return pwd;

end;

--plsql中的调用

var a varchar2(30);

call f_returnPasswd('wucaiming') into:a;

包用于在逻辑上组合过程和函数，它有包规范和包体两部分组成。

--创建包

create package p_package is

procedure changepasswd(name1 varchar2,passwd1 varchar2);

function f_returnpasswd(name1 varchar2) return varchar2;

end;

--实现包体

create or replace package body p_package is

procedure changepasswd(name1 varchar2,passwd1 varchar2) is

begin

--根据用户名修改密码

update mytable set passwd=passwd1 where name=name1;

end;

function f_returnpasswd(name1 varchar2) return varchar2 is

pwd varchar2(30);

begin

select passwd into pwd from mytable where name=name1;

return pwd;

end;

end;

--怎样调用包中的过程和函数？

--调用过程

exec p_package.f_returnpasswd('wucaiming');

--调用函数

var a varchar2(30);

call p_package.f_returnpasswd('wucaiming') into:a;

触发器：触发器是隐含的执行的存储过程。触发器

pl/sql中的变量类型有4类：

1.标量类型（scalar）

2.复合类型（composite）

3.参考类型(reference）

4.clob(large object)

1.标量

v_name varchar2(20); --变长字符串

v_sal number(6,2); ---9999.99~+9999.99

v_sql2 number(6,2):=5.4; --定义一个小数并赋初值为5.4，":="相当于java中的"="赋值运算符；

v_hiredate date; --定义一个日期型数据

v_valid boolean not null default false;

eg1:--把'wucaiming'的工资涨一倍，并输出

set serveroutput on;

declare

v_name varchar2(30);-- v_name mytable.name%type;

v_sal_add number(6,2);

begin

select name,sal*2 into v_name ,v_sal_add from mytable where name=&name;

dbms_output.put_line('姓名：'||v_name||' 工资：'||v_sal_add);

end;

2.复合变量

pl/sql记录

pl/sql表

嵌套表

varray

--pl/sql记录

类似于高级语言中的结构体（或者类）

set serveroutput on;

--定义一个plsql 记录 类型；

declare type mytable_record_type is record(

v_name mytable.name%type,

v_passwd mytable.passwd%type,

v_sal mytable.sal%type

);

--定义了一个变量，变量的类型是上面定义的记录类型；

r_record mytable_record_type;

begin

select name,passwd,sal into r_record from mytable where name='wucaiming';

dbms_output.put_line('姓名：'||r_record.v_name||' 密码：'||r_record.v_passwd||' 工资：'||r_record.v_sal);

end;

--pl/sql表

表相当于高级语言中的数组。这里的数组下标可以为负；

set serveroutput on;

declare type mytale_table_type is table of mytable.name%type index by binary_integer;

t_table mytale_table_type;

begin

select name ,passwd ,sal into t_table(-1),t_table(0),t_table(1) from mytable where name='wucaiming';

dbms_output.put_line('姓名：'||t_table(-1)||' 密码：'||t_table(0)||' 工资：'||t_table(1));

end;

3.参照变量 之游标变量

定义游标不需要select语句，使用游标需要使用select语句；

declare

type mytable_cursor is ref cursor;

test_cursor mytable_cursor;

v_name mytable.name%type;

v_passwd mytable.passwd%type;

v_sal mytable.sal%type;

begin

--游标与一个select关联；

open test_cursor for select name,passwd,sal from mytable where name='wucaiming';

loop

fetch test_cursor into v_name,v_passwd,v_sal;

--判断游标是否为空；

exit when test_cursor%notfound;

dbms_output.put_line('姓名：'||v_name||' 密码：'||v_passwd||' 工资：'||v_sal);

end loop;

end;

pl/sql的高级用法：（能编写高级过程，下订单过程模块）

pl/sql的控制结构：

if...then

if...then...else

if...then...elsif...then...else

eg:

create or replace procedure p_addsal(name1 varchar2) is

v_sal mytable.sal%type;

begin

select sal into v_sal from mytable where name=name1;

if v_sal <2000 then

update mytable set sal=200 where name=name1;

end if;

end;

二层条件分支：

eg:

create or replace procedure p_addsal(name1 varchar2) is

v_sal mytable.sal%type;

begin

select sal into v_sal from mytable where name=name1;

if v_sal <200 then

update mytable set sal=200 where name=name1;

else

update mytable set sal=v_sal+100 where name=name1;

end if;

end;

三层分支:

eg:

create or replace procedure p_addsal(name1 varchar2) is

v_sal mytable.sal%type;

begin

select sal into v_sal from mytable where name=name1;

if v_sal >=300 then

update mytable set sal=v_sal+150 where name=name1;

elsif v_sal>=200 then

update mytable set sal=v_sal+100 where name=name1;

else

update mytable set sal=v_sal+50 where name=name1;

end if;

end;

循环语句：

loop结构：

create or replace procedure p_add(name1 varchar2,passwd1 varchar2,sal number) is

v_num number:=1;

begin

loop

insert into mytable values(name1 ,passwd1,sal);

exit when v_num=3;--结束条件

v_num:=v_num+1;

end loop;

end;

--使用while循环；

create or replace procedure p_add(name1 varchar2,passwd1 varchar2,sal number) is

v_num number:=1;

begin

while v_num<=3

loop

insert into mytable values(name1 ,passwd1,sal);

v_num:=v_num+1;

end loop;

end;

--for循环（不建议使用）

begin

for i in reverse 1..10 loop

insert into users values(i,'gao');

end loop;

end;

--顺序控制语句

goto语句(一般不建议使用)老是乱跳；

null语句；不会执行任何操作，并且会直接将控制传递到下一条语句；

---分页过程前面

-- 1.创建表

create table book(bookId number,bookName varchar2(50),bookPubl varchar2(50));

--2.创建存储过程（只有输入参数）

create or replace procedure p_splitpage(bookId in number,bookName in varchar2,bookPubl in varchar2) is

begin

insert into book values(bookId,bookName,bookPubl);

end;

--3.创建存储过程（有输出参数）

create or replace procedure p_splitpage1(bookId1 in number,bookName1 out varchar2,bookPubl1 out varchar2) is

begin

select bookName ,bookPubl into bookName1,bookPubl1 from book where bookId=bookId1;

end;

--4.创建存储过程（输出参数是一个列表）

--创建一个包

create or replace package testpackage as

type test_cursor is ref cursor;

end testpackage;

--创建过程

create or replace procedure p_splitpage2(name1 in varchar2,c_cursor out testpackage.test_cursor) is

begin

open c_cursor for select * from mytable where name=name1;

end;

分页：（输入表名，每页显示的记录数，当前页，返回总记录数，总页数，和返回的结果集）

--分页的模板sql

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

--创建一个包

create or replace package testpackage as

type test_cursor is ref cursor;

end testpackage;

--定义存储过程

create or replace procedure p_pagesplit(

tableName in varchar2, --表名

pageSize in number, --每页显示的记录数

pageNow in number, --当前页

myRows out number, --返回的总记录数

myPageCount out number, --总页数

p_cursor out testpackage.test_cursor --返回的结果集

) is

--定义一个sql语句

v_sql varchar2(1000);

v_begin number:=(pageNow-1)*pageSize+1;

v_end number:=pageNow*pageSize;

begin

v_sql:='select * from (select rownum rn,t.* from (select * from '||tableName||') t where rownum<='||v_end||') where rn>='||v_begin;

--游标和sql关联 返回结果集

open p_cursor for v_sql;

--返回总记录数

v_sql:='select count(*) from '||tableName;

execute immediate v_sql into myRows;

--返回总页数；

if mod(myRows,pageSize)=0 then

myPageCount:=myRows/pageSize;

else

myPageCount:=myRows/pageSize+1;

end if;

if p_cursor%notfound then

close p_cursor;

end if;

end;

异常处理：

declare

v_name mytable.name%type;

begin

select name into v_name from mytable where name=&a;

exception

when no_data_found then

dbms_output.put_line('数据未找到！');

end;

1.case_no_found --没有定义这种情况。

2.cursor_already_open --游标重复打开

3.invaild_cursor --游标没有打开，打开关闭的游标。

4.invaild_number --sal=sal+'100'

5.too_many_rows --返回的记录数不止一行

6.zero_divide --2/0

7.value_error --v_name varchar2(1),太小了

其他预定义例外

1.login_denide --非法用户登陆

2.not_logged_on --没登陆就执行dml操作语句

3.storage_error --超出内存空间或是内存被破坏

4.time_on_resource --等待资源出现超时

自定义例外

create or replace procedure ex_test(name1 varchar2) is

myex exception;

begin

update mytable set sal=sal+100 where name=name1;

if sql%notfound then

raise myex;

end if;

exception

when myex then

dbms_output.put_line('没有更新任何用户');

end;

视图: (虚拟的表，在数据库中不实际存在！动态生成，从不同的表中取出部分数据构成一张虚拟的表)

视图与表的区别：

1.表占用磁盘空间，视图不占用磁盘空间

2.视图不能添加索引

3.使用视图可以简化 复杂查询

4.视图利于提高安全性

创建视图：（把mytable 表 的sal <400的雇员 映射到该视图）

create or replace View myview as select * from mytable where sal<=400;

删除view：

drop View myview;

触发器

--当用户登陆/退出或者操作某个数据对象/或者进行ddl操作时，有一个存储过程被隐藏的执行

，这个特殊的存储过程称之为触发器。

--提出问题

--用户登录时，自动记录用户名字，登陆ip

--删除记录时，自动保存到另一张表中，以便后悔恢复；

--周日不能对一张表进行操作

--触发器的分类

dml触发器

--增删改触发器

ddl触发器

--数据定义触发器（create tabel|view ..dorp）

系统触发器

--与系统相关的触发器（登陆，退出，启动数据库，关闭数据库）

--触发器的快速入门

--在一张表中添加数据时，提示添加了一条数据；

set serviceoutput on ;

create or replace trigger trigger_test after

insert on omgap.mytable --语句级触发器；

begin

dbms_output.put_line('添加了一条记录');

end;

--在某张表修改多条数据时，提示‘多次修改了数据！’（行级触发器和语句级触发器的区别）

set serviceoutput on ;

create or replace trigger trigger_test after

insert on omgap.mytable

for each row --行级触发器

begin

dbms_output.put_line('添加了一条记录');

end;

--周日不可以对某张表进行操作

create or replace trigger trigger_test

before delete on omgap.mytable

begin

if to_char(sysdate,'day') in('星期日','星期六') then

--虽然提示，但是仍然阻止不了删除操作，需要抛出程序错误才可阻止删除；

dbms_output.put_line('周末不可以删除数据！');

end if;

end;

--抛出程序错误才可阻止删除操作；

create or replace trigger trigger_test

before delete on omgap.mytable

begin

if to_char(sysdate,'day') in('星期日','星期六') then

dbms_output.put_line('周末不可以删除数据！');

--第一个参数范围在-20000~-20099

raise_application_error(-20001,'sorry，周末不可以删除数据！');

end if;

end;

--周日操作可以是inert，update ，delete 明确是哪一种

create or replace trigger triggerf_test before insert or update or delete on

omgap.mytable

begin

case

when inserting then

raise_application_error(-20003,'不能添加');

when updating then

raise_application_error(-20002,'不能更新');

when deleting then

raise_application_error(-20001,'不能删除');

end case;

end;

--使用：old 和 ：new

--显示雇员工资修改前和修改后的值

--如何确保修改员工工资不能低于原有工资

create or replace trigger trigger_test

before update on omgap.mytable

for each row

begin

if :new.sal <:old.sal then

raise_application_error(-20004,'原来工资不能低于现在工资');

else

dbms_output.put_line('原来工资：'||:old.sal||' 现在工资：'||:new.sal);

end if;

end;

--使用触发器备份一张表格

create table mytable_bak(name varchar2(30),passwd varchar2(30),sal number(6,2));

create or replace trigger trigger_test

before delete on omgap.mytable

for each row

begin

insert into mytable_bak values(:old.name,:old.passwd,:old.sal);

end;

--系统触发器

ora_login_user//返回登录用户名

ora_sysevent//返回系统事件名

--记录登录和退出触发器；

--登录

create table log_table(userName varchar2(30),logon_time date,logoff_time date,address varchar2(30));

create or replace trigger trigger_test after logon on database

begin

insert into log_table(userName,logon_time,address) values(ora_login_user,sysdate,ora_client_ip_address);

end;

--退出

create or replace trigger trigger_test before logoff on database

begin

insert into log_table(userName,logoff_time,address) values(ora_login_user,sysdate,ora_client_ip_address);

end;