Oracle数据库的学习（一）

--使用SQL Plus登录账户
请输入用户名：如下

例：

<span style="font-size:14px;">1）请输入用户名：conn/as sysdba
   输入口令：（直接回车）
2）请输入用户名：/as sysdba;//登陆sys帐户
3）请输入用户名：sys as sysdba;//同上
4）请输入用户名：scott/tiger;//登陆普通用户scott</span>

--创建用户
create user username identified by password;
例：create user my_test identified by Oracle123;
--修改密码
alert user username identified by newPassword;
例：alert user scott identified by tiger;
--删除用户
drop user username cascade;//级联
例：drop user my_test cascade;
--授权
connect--用户连接，或者是登录
resource --资源权限
dba --最大的权限
	系统权限
	CREATE SESSION                     创建会话
	CREATE SEQUENCE                    创建序列
	CREATE SYNONYM                     创建同名对象
	CREATE TABLE                       在用户模式中创建表
	CREATE ANY TABLE                   在任何模式中创建表
	DROP TABLE                         在用户模式中删除表
	DROP ANY TABLE                     在任何模式中删除表
	CREATE PROCEDURE                   创建存储过程
	EXECUTE ANY PROCEDURE              执行任何模式的存储过程
	CREATE USER                        创建用户
	DROP USER                          删除用户
	CREATE VIEW                        创建视图
grant connect,resource,dba to username;

--取消授权
grant dba to user
revoke dba from user
<p class="sql" name="code">
</p><p class="sql" name="code">--创建一个序列<div class="sql" name="code"><pre class="sql" name="code">create sequence my_seq
minvalue 1
maxvalue 200
start with 1
increment by 1
cache 99;

--删除序列
drop sequence my_seq;

--零时表dual
select my_seq.nextval from dual;

触发器

创建一个触发器：插入一条数据，同时监听一下，将插入的数据记录下来

create or replace trigger trigger_insert
  after insert on dept
  for each row	--对表的每一行触发器执行一次
begin
  insert into dept_bak
  values
    (my_seq.nextval, :new.deptno, :new.dname, :new.loc);  --:new表示新插入的那条记录
end;

向表dept插入一条新数据

insert into dept values(10,'翻译部门','太原');

查询表dept_bak结果

select * from dept_bak;

再创建一个触发器：删除表dept的数据时，将该删除数据记录到表dept_bak中

create or replace trigger trigger_delete
  after delete on dept
  for each row
begin
  insert into dept_bak
    (id, deptno, dname, loc, name)
  values
    (my_seq.nextval, :old.deptno, :old.dname, :old.loc, '删除数据');
end;

删除表dept中的一条数据

delete from dept where deptno = 1;

查询表dept_bak结果

select * from dept_bak;

创建一个触发器：如果插入数据就将插入的数据记录下来，如果删除数据就将删除的数据记录下来，如果是修改数据就将修改前的数据记录下来

create or replace trigger my_trigger
  after insert or delete or update on dept
  for each row
begin
  --如果是插入数据
  if inserting then
    insert into dept_bak
      (id, deptno, dname, loc, name)
    values
      (my_seq.nextval, :new.deptno, :new.dname, :new.loc, '新增数据');
  end if;
  --如果是删除数据
  if deleting then
    insert into dept_bak
      (id, deptno, dname, loc, name)
    values
      (my_seq.nextval, :old.deptno, :old.dname, :old.loc, '删除数据');
  end if;
  --如果是修改数据
  if updating then
    insert into dept_bak
      (id, deptno, dname, loc, name)
    values
      (my_seq.nextval, :old.deptno, :old.dname, :old.loc, '修改数据');
  end if;
end;

测试一下：

--插入一条数据
insert into dept values(9,'财务部门','北京');

select * from dept_bak;

--删除一条数据
delete from dept where deptno = 2;

<pre class="sql" name="code">select * from dept_bak;

--修改一条数据
update dept set loc = '南京' where deptno = 20;

select * from dept_bak;

存储过程

写一个存储过程查询数据

create or replace procedure my_first_pro as 
  my_id number;
begin
  select count(*) into my_id from dept;
  dbms_output.put_line('数据的个数：'||my_id);
  commit;
end;

begin
  my_first_pro();
end;

数据的个数：2

写一个存储过程删除数据

create or replace procedure my_second_pro(id number) as
  dept_exception exception;
  pragma exception_init(dept_exception, -1);
begin
  delete from dept where deptno = id;
  commit;
exception
  when dept_exception then
    dbms_output.put_line('删除数据失败！');
    dbms_output.put_line(sqlcode || '------------' || sqlerrm);
  when others then
    dbms_output.put_line(sqlcode || '------------' || sqlerrm);
end;

新建一个测试表user_test

create table user_test(id number(10),username varchar2(20),primary key(id));
insert into user_test values(1,'倪好');

写一个存储过程插入数据

create or replace procedure insert_pro(id number, username varchar2) as
  my_exception exception;
  pragma exception_init(my_exception, -1);
begin
  insert into user_test values (id, username);
  commit;
exception
  when my_exception then
    dbms_output.put_line('违反主键约束');
    dbms_output.put_line(sqlcode || '------------' || sqlerrm);
end;

set serveroutput on;--设置在窗口可以打印数据
execute insert_pro(1,'hello');

违反主键约束
-1------------ORA-00001：违反唯一约束条件（CY.SYS_C0011056）

---使用loop插入多条数据

create procedure insert_loop(id number, username varchar2) as
begin
  for i in 1 .. 100 loop
    insert into user_test01 values (id, username);
    commit;
  end loop;
end;<span style="font-family:FangSong_GB2312">
</span>

游标

查询有多条数据，把多条数据打印到控制台上，使用plsql

声明游标后，通过for循环来获取游标中的数据

create or replace procedure select_cursor as
  cursor my_cursor is
    select * from dept;
  row my_cursor%rowtype;--定义一个游标变量，类型是游标的一行数据
begin
  for row in my_cursor loop
    dbms_output.put_line(row.deptno || ',' || row.dname || ',' || row.loc);
  end loop;
end;

声明游标后，通过fetch into来打开游标从而获取游标中的数据

create or replace procedure second_select_cursor(deptno number) as
  cursor my_cursor is
    select * from emp where deptno = deptno;
  row my_cursor%rowtype;
begin
  --获取游标中的数据
  open my_cursor;
  loop
    fetch my_cursor
      into row;
    --设置退出的游标
    exit when my_cursor%notfound;
    dbms_output.put_line(row.empno || ',' || row.ename || ',' || row.sal);
  end loop;
  close my_cursor;
end;

包体

第一部分是包：组件的声明

create or replace package my_package is
  procedure my_pro01(id number);--声明组件
  procedure my_pro02;--声明组件
end my_package;

第二部分是包体：组件内容的具体实现

create or replace package body my_package is
  --声明组件
  procedure my_pro01(id number) is
  begin
    insert into dept (deptno, dname) values (id, '技术部门');
  end my_pro01;

  --声明组件
  procedure my_pro02 is
  begin
    insert into dept (deptno, dname) values (2, '市场调研部门');
  end my_pro02;
end my_package;

--执行包体的语法：exec/execute package.procedure;

exec my_package.my_pro01(5);
execute my_package.my_pro02;

分页

select b.*, b.rm
  from (select a.*, rownum rm 
       from (select e.* from emp e) a) b
  where b.rm between 11 and 15;

page：页数	pageNumber：每一页最多展示5条数据
第1页：	(1,pageNumber)	1-5
第2页：	((2-1)*pageNumber+1,pageNumber*2)	6-10
第3页：	((3-1)*pageNumber+1,pageNumber*3)	11-15
第page页：	((page-1)*pageNumber+1,pageNumber*page)