数据库中序列、视图、游标、包、动态SQL的使用

序列

  Oracle数据库的表中是不能实现自增功能的，所以要通过序列来实现自增功能

--创建序列
create sequence seq_users
start with 1  --开始值为1
increment by 1;--每次增长一个（步长、频次）

--使用序列
insert into users values(seq_users.nextval,'admin','123','男'，'123@163.com')

--删除
drop sequence seq_users;

动态SQL

  什么是动态的SQL？

编译期间SQL语句是不确定的，并且在运行时允许发生改变

  动态SQL应用场合？

要执行一个DDL语句时

需要增加程序的灵活性时

使用包DBMS_SQL动态执行SQL语句时

  动态SQL的执行：

   

PL/SQl动态SQL创建表：

begin
  execute immediate 'create table test_demo (id number primary key,uname varchar2(20) not null)';
end;

测试代码：

declare 
  id_v number :=1;
  uname_v VARCHAR2(20) :='admin';
  sql_v VARCHAR2(50);
begin
    --占位符  :1代表队第几个参数
  sql_v :='insert into test_demo values (:1,:2)';
  execute immediate sql_v using id_v, uname_v;
  dbms_output.put_line('操作成功');
end;

PL/SQL动态SQL综合应用实例：创建表，首先判断该表是否存在，若存在先删除再重新创建

--创建用户表tem_user
--判断该表是否存在，若存在先删除
set serveroutput on;
declare 
  num binary_integer;
  id_v number :=1001;
  uname_v VARCHAR2(20) :='孙悟空';
  age NUMBER :=30;
  drop_sql VARCHAR2(100);
  insert_sql VARCHAR2(100);
  select_sql VARCHAR2(100);
begin 
  select count(*) into num from all_tables where table_name='tem_user' and owner='theme';
  if num >=0
    then drop_sql:='drop table tem_user';
    EXECUTE IMMEDIATE drop_sql;
  end if;
     execute immediate 'create table tem_user (id number primary key,uname varchar2(20) not null,age number)';
     insert_sql :='insert into tem_user values(:1,:2,:3)';
     EXECUTE IMMEDIATE insert_sql using id_v,uname_v,age;
     COMMIT;
     dbms_output.put_line('添加成功！！');
EXCEPTION
  when OTHERS then ROLLBACK;
  dbms_output.put_line('添加失败！');
end;

视图

 视图的特点：

   1.是一个数据库中虚拟的表

   2.经过查询操作形成的结果

   3.具有普通表的结构

   4.不能实现数据的存储

   5.对视图的修改将会影响实际的数据表

视图的应用：

--添加视图
create or replace view emp_dept_view
as select * from emp NATURAL JOIN dept;

--查询
select * from emp_dept_view;

--删除视图
drop view emp_dept_view;

游标

 游标用来处理从数据库中检索的多行记录(使用select语句)，利用游标，程序可以逐个的处理和遍历一次检索返回的整个记录集
 游标的分类

   静态游标:结果集已经确定

隐式游标：所有的DML语句为隐式游标（不需要打开和关闭游标）

显式游标：用户显示声明（需要打开和关闭游标open、close）

   动态游标。

游标的应用:

   

游标的属性：

游标的基本属性：遍历for和loop实例

set serveroutput on;
--查询所有的员工信息，并打印信息（loop循环）
declare
  CURSOR emp_info is select * from emp;--一组数据
  emp_ emp%rowtype;  --定义变量，一条数据
begin
  open emp_info;
  loop
    fetch emp_info into emp_;--把游标数据（结果集）放入到变量中
    exit when emp_info%notfound;--当不存在下一条数据时就结束循环
    dbms_output.put_line('员工编号；'||emp_.empno||'员工姓名：'||emp_.ename||'员工基本工资：'||emp_.sal);  
  end loop;
  close emp_info;--关闭游标
end;

--for循环
declare 
  cursor emp_info is select * from emp;
  emp_ emp%rowtype;
begin
  for emp_ in emp_info loop
    dbms_output.put_line('员工编号是'||emp_.empno||',员工姓名是'||emp_.ename||',基本工资是'||emp_.sal);
  end loop;
end;

游标属性isopen实例：

--游标基本属性isopen
set serveroutput on;
declare
  CURSOR emp_info is select * from emp;
  emp_ emp%rowtype;  
begin
  if emp_info%isopen
    then dbms_output.put_line('游标已打打开');
  else
    open emp_info;
  end if;
  loop
    fetch emp_info into emp_;
    exit when emp_info%notfound;
    dbms_output.put_line('员工编号；'||emp_.empno||'员工姓名：'||emp_.ename||'员工基本工资：'||emp_.sal);  
  end loop;
  close emp_info;
end;

游标属性rowcount实例：

--rowcount
set serveroutput on;
declare
  CURSOR emp_info is select * from emp;
  emp_ emp%rowtype;
begin
  if emp_info%isopen -- 判断游标是否打开
    then dbms_output.put_line('游标已打开');
  else
    open emp_info;
  end if;
  loop 
    fetch emp_info into emp_;
    exit when emp_info%notfound;
    dbms_output.put_line(emp_.empno||'当前的行号'||emp_info%rowcount);
  end loop;
  close emp_info;
end;

游标属性while循环实例：

set serveroutput on;
declare
  CURSOR emp_info is select * from emp;
  emp_ emp%rowtype;
begin
  open emp_info;--打开游标
  loop
    fetch emp_info into emp_;
    if emp_info%found
      then dbms_output.put_line('--------');
    else 
      exit;
    end if;
  end loop;
  close emp_info;
end;

declare 
  cursor emp_info is select * from emp;
  emp_ emp%rowtype;
begin
  open emp_info;
  fetch emp_info into emp_;--第一步获取第一条数据
  while emp_info%found loop
    dbms_output.put_line('编号：'||emp_.empno);
    fetch emp_info into emp_;--第二步，循环下一条数据
  end loop;
  close emp_info;
end;

Oracle数据库中的包

 什么是包？

   包是有存储在一起的相关对象组成的PL/SQL结构

   用于逻辑组合相关的自定义类型、变量、游标、过程和函数

 包的组成

   包的规范（又称包头）

     用于定义常量、变量、游标、过程和函数等用户与程序的接口

     可以在包内引用，也可以被外部程序调用

   包的主体

     是包规范的实现，包括变量、游标、过程和函数等

     包体内的内容不能被外部应用程序调用

包的规范

create or replace package test_package
as 
--声明一个存储过程
procedure add_emp_pro(emp_ in emp1%rowtype);

--声明一个函数
function sum_(num1 number,num2 number)
return number;

end test_package;

包的主体部分:

create or replace package body test_package
as
PROCEDURE add_emp_pro(emp_ in emp1%rowtype)
as
begin
  dbms_output.put_line('成功添加一条数据');
end;

--实现函数 
function sum_func(num1 number,num2 number)
RETURN NUMBER
as
BEGIN
  RETURN num1+ num2;
end;
end test_package;

包的调用：

declare 
  emp_ emp1%rowtype;
begin
  emp_.empno:=9527;
  emp_.ename:='老张';
  test_package.add_emp_pro(emp_);
end;

begin
  dbms_output.put_line('两数之和是'||test_package.sum_func(3,5));
end;