Oracle数据库中游标、存储过程、函数、程序、触发器的使用

一、游标

--1.创建待加密表USER_LOGIN，创建游标使用表user_login_bak

createtable USER_LOGIN

(

  USER_ID   NUMBER,

  LOGIN      NUMBER,

  LOGIN_TIME DATE

);

createtable user_login_bak

(

  USER_ID   NUMBER,

  LOGIN      NUMBER,

  LOGIN_TIME DATE

);

--2.对待加密表USER_LOGIN插入1000000条数据

insertinto user_login

select object_id,decode(mod(rownum,2),0,0,1),sysdate+rownum/1440  from

(select a.*from dba_objects a,dba_objects b)whererownum<=1000000;  --- 笛卡尔乘积然后只取前100万行;

--3.创建待加密表USER_LOGIN的游标cur，每次批量获取5000行数据，批量插入表user_login_bak。并记录插入执行时长

declare

  cursor cur is------定义一个游标

    select USER_ID,LOGIN, LOGIN_TIME from USER_LOGIN;------定义一个游标

  v_user_id   dbms_sql.Number_Table;  ---不是记录了，是集合可以存多行数据

  v_login     dbms_sql.Number_Table;---不是记录了，是集合

  v_login_time dbms_sql.date_Table;------不是记录了，是集合

begin

  open cur;----打开游标要在循环之后立即关闭游标

  loop----循环

    fetch cur bulkcollect-----不加bulk collect 就一行一行取，所以必须加入，加入5000

      into v_user_id, v_login, v_login_time limit5000;----一次批量获取5000行；

    forall i in1 .. v_user_id.count                            ---forall一次性全部插入

      insertinto USER_LOGIN_bak

      values

        (v_user_id(i), v_login(i), v_login_time(i));  ----这里的 i 就是指1..5000

    commit;   -----在forall之后提交

    exitwhen cur%notfoundor cur%notfoundisnull;   ------退出在游标为空的时候

  endloop;    ----关闭循环

  close cur;   ------关闭游标

  commit;

end;

--3.执行查询语句查看两张表数据均为100000条

selectcount(*)from USER_LOGIN;

selectcount(*)from user_login_bak;

selectcount(*)from user_login_bak;

二、存储过程

--1.创建加密表USER_LOGIN1，过程使用表user_login_bak1

createtable USER_LOGIN1

(

  USER_ID   NUMBER,

  LOGIN      NUMBER,

  LOGIN_TIME DATE

);

createtable user_login_bak1

(

  USER_ID   NUMBER,

  LOGIN      NUMBER,

  LOGIN_TIME DATE

);

--2.对待加密表USER_LOGIN1插入1000000条数据

insertinto user_login1

select object_id,decode(mod(rownum,2),0,0,1),sysdate+rownum/1440  from

(select a.*from dba_objects a,dba_objects b)whererownum<=1000000;

--3.创建过程procedureTest1并编译

createorreplaceprocedure procedureTest1 as

  cursor cur is

    select USER_ID,LOGIN, LOGIN_TIME from USER_LOGIN1;

  v_user_id   dbms_sql.Number_Table;

  v_login     dbms_sql.Number_Table;

  v_login_time dbms_sql.date_Table;

begin

  open cur;

  loop

    fetch cur bulkcollect

      into v_user_id, v_login, v_login_time limit5000;

    forall i in1 .. v_user_id.count                        

      insertinto USER_LOGIN_bak1

      values

        (v_user_id(i), v_login(i), v_login_time(i));

    commit; 

    exitwhen cur%notfoundor cur%notfoundisnull; 

  endloop;   

  close cur; 

  commit;

end;

--4.查询USER_LOGIN_bak1，查询结果0条数据

selectcount(*)from USER_LOGIN_bak1;

--5.执行过程

begin

  procedureTest1;

end;

--6.查询USER_LOGIN_bak1，查询结果1000000条数据

selectcount(*)from USER_LOGIN_bak1;

三、函数

--1.创建加密表USER_LOGIN2，过程使用表user_login_bak2

createtable USER_LOGIN2

(

  USER_ID   NUMBER,

  LOGIN      NUMBER,

  LOGIN_TIME DATE

);

createtable user_login_bak2

(

  USER_ID   NUMBER,

  LOGIN      NUMBER,

  LOGIN_TIME DATE

);

--2.对待加密表USER_LOGIN2插入1000000条数据

insertinto user_login2

select object_id,decode(mod(rownum,2),0,0,1),sysdate+rownum/1440  from

(select a.*from dba_objects a,dba_objects b)whererownum<=1000000;

--3.创建函数function_bkg并编译

createorreplacefunction function_bkg returnvarchar2as

  cursor cur is

    select USER_ID,LOGIN, LOGIN_TIME from USER_LOGIN2;

  v_user_id   dbms_sql.Number_Table;

  v_login     dbms_sql.Number_Table;

  v_login_time dbms_sql.date_Table;

  pragmaautonomous_transaction;

begin

  open cur;

  loop

    fetch cur bulkcollect

      into v_user_id, v_login, v_login_time limit5000;

    forall i in1 .. v_user_id.count                          

      insertinto USER_LOGIN_bak2

      values

        (v_user_id(i), v_login(i), v_login_time(i)); 

    commit;   -----在forall之后提交

    exitwhen cur%notfoundor cur%notfoundisnull; 

  endloop;  

  close cur; 

  commit;

  return1;

end;

--4.查询USER_LOGIN_bak2，查询结果0条数据

selectcount(*)from user_login_bak2;

--5.执行函数体

select function_bkg from dual;

--6.查询USER_LOGIN_bak2，查询结果1000000条数据

selectcount(*)from user_login_bak2;

四、存储过程和函数

create table aaa(id int，varvarchar(10));

     insert into aaa values(1,'fds');

insert into aaa values(2,'fff');

insert into aaa values(3,'werew');

  创建相关函数及存储过程

CREATE OR REPLACE PROCEDUREtest_procedure_visit IS

BEGIN

   insert into aaa values(4,'jkl');

END;

 

CALL test_procedure_visit();

 

select * from aaa;

/

 

CREATE OR REPLACE FUNCTIONtest_function_visit

RETURN varchar

is

 l_tname varchar(10):= 'parameter';

BEGIN

 select var into l_tname from aaa where id = 1;

  return l_tname;

END test_function_visit;

 

SELECT test_function_visit() FROM dual;

/

五、程序

--1.创建加密表USER_LOGIN3，程序包使用表user_login_bak3

createtable USER_LOGIN3

(

  USER_ID   NUMBER,

  LOGIN      NUMBER,

  LOGIN_TIME DATE

);

createtable user_login_bak3

(

  USER_ID   NUMBER,

  LOGIN      NUMBER,

  LOGIN_TIME DATE

);

--2.对待加密表USER_LOGIN3插入1000000条数据

insertinto user_login3

select object_id,decode(mod(rownum,2),0,0,1),sysdate+rownum/1440  from

(select a.*from dba_objects a,dba_objects b)whererownum<=1000000;

--3.创建程序包ceshi3

CREATEORREPLACEPACKAGE ceshi3 AS

  procedure ceshi4;

END;

--4.创建程序包体并编译

CREATEORREPLACEPACKAGEBODY ceshi3 AS 

 procedure ceshi4 as

  cursor cur is

    select USER_ID,LOGIN, LOGIN_TIME from USER_LOGIN3;

  v_user_id   dbms_sql.Number_Table; 

  v_login     dbms_sql.Number_Table;

  v_login_time dbms_sql.date_Table;

begin

  open cur;

  loop

    fetch cur bulkcollect

      into v_user_id, v_login, v_login_time limit5000;

    forall i in1 .. v_user_id.count                      

      insertinto USER_LOGIN_bak3

      values

        (v_user_id(i), v_login(i), v_login_time(i));

    commit;  

    exitwhen cur%notfoundor cur%notfoundisnull;

  endloop;  

  close cur;  

  commit;

end ceshi4;

end ceshi3;

--5.查询USER_LOGIN_bak3，查询结果0条数据

selectcount(*)from USER_LOGIN_bak3;

--6.执行函数体

begin

  ceshi3.ceshi4;

end; 

--7.查询USER_LOGIN_bak3，查询结果1000000条数据

selectcount(*)from USER_LOGIN_bak3;

六、触发器

CREATE TABLE TESTCHECK

    (

     ID   NUMBER(*,0) primary key,

     COL1 VARCHAR2(50 BYTE),

     COL2 CHAR(1 BYTE) DEFAULT 'Y',

     COL3 NUMBER,

     COL4 DATE,

     COL5 NUMBER(*,0)

    );

--创建历史表

create table TESTCHECK_HISTORY

(

 idd   INTEGER,

 col11 VARCHAR2(50),

 col22 CHAR(1) default 'Y',

 col33 NUMBER,

 col44 DATE,

 col55 INTEGER,

 CZ    VARCHAR2(100)

);

--创建待触发器

--此触发器在删除、更新、插入TRI_CESHI的时会触发该触发器，把操作的历史数据插入到TESTCHECK_HISTORY

CREATE OR REPLACE TRIGGER TRI_CESHI BEFOREUPDATE OR INSERT OR DELETE ON TESTCHECK

FOR EACH ROW

BEGIN

   IF INSERTING THEN

   INSERT INTO TESTCHECK_HISTORY

     ("IDD", "COL11", "COL22","COL33", "COL44", "COL55",CZ)

   VALUES

     (:NEW.ID, :NEW.COL1, :NEW.COL2, :NEW.COL3, :NEW.COL4,:NEW.COL5,'INSERTING');

   END IF;

END;

insert into testcheck values(1,2,1,1,to_date('10-10-2016','dd-mm-yy'),1);

insert into testcheck values(2,2,1,1,to_date('10-10-2016','dd-mm-yy'),1);

insert into testcheck values(3,2,1,1,to_date('10-10-2016','dd-mm-yy'),1);

insert into testcheck values(4,2,1,1,to_date('10-10-2016','dd-mm-yy'),1);

select * from testcheck;

select * from testcheck_history;