存储过程

1. 存储过程优点[摘录]

a. 存储过程可以使得程序执行效率更高、安全性更好，因为过程建立之后已经编译并且储存到数据库，直接写sql就需要先分析再执行因此过程效率更高，直接写sql语句会带来安全性问题，如：sql注入

b. 建立过程不会很耗系统资源，因为过程只是在调用才执行。

c. 存储过程可以用于降低网络流量，存储过程代码直接存储于数据库中，所以不会产生大量T-sql语句的代码流量。

2. 包【含包头与包体】

a. 包头

包含对外可用的函数的声明、存储过程的声明、变量的声明等

b. 包体

包含包的实现，如函数的实现、过程的实现等

3. 包头的定义

create or replace packge PKG_USERS is
-- Public variable declarations
type userCursor is ref cursor;//定义一个游标变量，用于存储过程和函数返回结果集时使用，因为PL/SQL不允许存储过程或函数直接返回结果集，但可以返回类型变量, 于是引用游标的类型变量作为输出参数或返回值就应运而生了
-- Public function and procedure declarations
procedure GetUserByUid(p_Uid in integer, v_cursor out userCursor);
procedure sp_User_Search(p_LoginEmail in nvarchar2, p_Mobile in nvarchar2, v_cursor out userCursor);
...
end PKG_USERS;

4. 包体的定义

create or replace packge body PKG_USERS is
-- Function and procedure implementations
procedure GetUserByUid(p_Uid in integer, v_cursor out userCursor)
is
begin
open v_cursor for

select user_id, user_name, login_email from tbl_user 
where user_id = p_Uid

end GetUserByUid;

end PKG_USERS;

----open cursor for select 作用是：打开一个游标，游标的内容为查询的结果集

如：open cursor for select * from tbl_user 目的就是遍历tbl_user这个表中的每条数据

5. 在包外声明一个存储过程

create or replace procedure sp_user_add
(
   p_LoginEmail in nvarchar2,
   p_FullName   in nvarchar2,
   p_Gender     in integer,
   v_UserID     out integer
)
is
begin

insert into tbl_user(LOGIN_EMAIL, FULLNAME, GENDER)
values(p_LoginEmail, p_FullName, p_Gender)

select tbl_user_seq.currval into v_UserID from dual;

end sp_user_add;

select tbl_user_seq.currval into v_UserID from dual 的作用：

把表tbl_user_seq中的当前值赋给v_UserID，tbl_user_seq是为tbl_user建立的sequence表，在oracle中sequence就是所谓的序列号，每次取的时候它会自动增加，一般用在需要按序列号排序的地方，由于在设计tbl_user时把v_UserID列设计成了自增列，所以需要再为tbl_user再设计一个sequence表，语法如下：

-- Create sequence 
create sequence tbl_user_seq
minvalue 1
maxvalue 999999999999999999999999999
start with 9265
increment by 1
cache 20;

一旦定义好了tbl_user_seq之后，就可以访问currval和nextval了
currval = 返回sequence的当前值
nextval = 返回sequence的当前值+increment by值

到此，还得为tbl_user再建一个触发器，语法如下：

create OR replace trigger tbl_user_trg before insert on tbl_user
for each row
begin
  <<COLUMN_SEQUENCES>>
  begin
    if :new.USER_ID is null then       //USER_ID为列名
      select tbl_user_seq.nextval into :new.USER_ID from dual;
    end if;
  end COLUMN_SEQUENCES;
end;

这样，在对tbl_user进行插入操作时会先触发这个触发器，这个触发器的作用就是从tbl_user_seq表中取出自增值赋给USER_ID

6. 关于dual表【网上摘录】

dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。我们可以用它来做很多事情，如下：

a. 查看当前用户，可以在 SQL Plus中执行下面语句 select user from dual;

b. 用来调用系统函数

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--获得当前系统时间

select SYS_CONTEXT('USERENV','TERMINAL') from dual;--获得主机名

select SYS_CONTEXT('USERENV','language') from dual;--获得当前 locale

select dbms_random.random from dual;--获得一个随机数

c. 得到序列的下一个值或当前值，用下面语句

　　 select your_sequence.nextval from dual; //获得序列your_sequence的下一个值

　　 select your_sequence.currval from dual; //获得序列your_sequence的当前值

d. 可以用做计算器 select 7*9 from dual;

7. 动态sql

例子：根据email和mobile联合查询

create or replace procedure sp_user_search
(
   p_Email in nvarchar2,
   p_Mobile     in nvarchar2,
   v_cursor    OUT PCKG_CURSOR.coursor_type
)
is
searchStr varchar2(1000);//定义一个varchar2型变量，用于拼装sql
begin

searchStr :='select user_id, login_email, gender, mobile, status from tbl_user where status<>8';

if(p_LoginEmail is not null) then
searchStr := searchStr || 'and login_email like ''%' || p_Email || '%'' ';
end if;

if(p_Mobile is not null) then
searchStr := searchStr || 'and mobile = ''' || p_Mobile || ''' ';
end if;

open v_cursor for searchStr;

end sp_user_search;