Oracle存储过程完整结构讲解

一、弄清我们和oracle的主仆角色关系

       我们是主人，oracle是仆人。

       它具备创建、执行和管理存储过程的能力，而我们除了下命令其它什么都不会

       我们只需要向oracle讲清楚需要干什么，便能够得到我们想要的东西

二、告诉oracle我们想创建一个什么样的存储过程

        create or replace procedure prc_test(i_param1 in varchar2,i_param2 in date,i_param3 IN number ,o_result_code out varchar2)

        1、要干嘛？：create or replace 创建或替换一样东西（加“or replace”是需要修改时省去先删除再重新创建的麻烦）

        2、要创建或替换什么东西？：procedure 存储过程（oracle还支持创建表table、表空间tablespace、同义词synonym、序列sequence等等）

        3、给这个存储过程取什么名字？：prc_xxxxx（prc_开头是为了便于一眼看出是个存储过程）

        4、这个存储过程执行时需不需要参数？：i_param_name in type

               i_param_name参数名，i开头便于一眼看出是入参；

               in标识是入参，出参用out；

               type是类型，如varchar2，date，number等，多个参数用“；”隔开）

        5、这个存储过程执行结束需不需要返回结果？：o_param_name out type，同入参

三、告诉oracle需要这个存储过程干什么事

        我们知道做什么事，但是不会做；oracle提供了很多工具和做事能力，但是不知道做什么事。

        因此我们先得了解清楚oracle提供的工具和干事的能力，然后加以组装从而指导oracle帮我们把事干掉

        3.1 存放临时数据的容器

              在处理事情的过程中，总会临时产生或得到一些数据，暂时不用，但又不能扔，因为后面又会用到，怎么办？oracle提供了几种临时存放的容器

              存储过程定义和begin之间，用is/as标识

create or replace procedure prc_xxx(in,out)
as/is
临时变量列表；
begin

              3.1.1 存放单个值——基本类型临时变量

                       a、写明具体变量类型

                               字符串：t_param_name  varchar2(20)，注意，临时变量必须指明大小，入参不用；

                               数字：t_param_name   number(9);整型数字，最大9位数

                                  t_param_name   number(9,4);带小数的数字，最大9位，其中小数部分4位，整数部分5位（9-4）

                               时间：t_param_name   date;

                       b、指定跟现存的某张表的某个一致

                             t_name  user.name%type;   现存有一张user表，含有字段name，该临时变量跟该表的name的类型一致

                             这样写的目的：如果要把user表的name的值临时存放，就不需要关心到底该字段是什么类型，保持一致就ok了

              3.1.2 存放一条记录——复合临时变量

                      如果要存放的是一条记录，比如一个用户user，该记录含有用户名、密码、年龄、部门等，放在一个对象变量会简单很多

                      a、根据单张表定义复合变量

                            t_user_info  user%rowtype; 当前存在一张user表，该对象变量跟user一致，即字段相同，每个字段的类型和长度相同

                      b、根据多表关联查询定义复合变量

                            可以直接根据各表的相应字段确定类型

type user_info is record(
     u_name     user.name%type,
     u_dept_no  depet.dept_no%type
);
t_user_info user_info;

                      c、自定义复合变量

type user_info is record(
    u_name varchar2(20),
    u_age  varchar2(500)
);
t_user_info user_info;

      3.1.3 存放多条记录—— 游标（数组）临时变量

            游标临时存放一个sql的查询结果集，可以通过对该结果集进行遍历，从而逐条处理。此处只是定义游标，并不执行，真正的执行时打开游标时

cursor c_ps_action is select * from tab_user a,tab_dept b where a.dept_no=b.dept_no and  t.age >20;

        ok，oracle提供的临时存储变量的容器介绍完毕，支持我们存放单个值（简单变量）、单条记录（复合变量）、一组记录（游标），下面讲解如何处理业务。

        3.2 使用临时变量和入/出参

              3.2.1 使用临时变量

                        无需添加任何额外标识符，直接使用即可，比如定义了临时变量t_param，使用的时候直接用t_param即可；符合对象t_user_info.u_name，即".“+属性

              3.2.2 给临时变量赋值

                       a、手工赋值“：=”

                             简单变量   t_param ：=11；

                       b、通过查询sql赋值

                             普通的sql语句，查询结果字段后面加”into  临时变量“

                             单个变量赋值：select  user_name into  t_u_name from tab_user ; t_u_name为临时变量

                             多个变量赋值：select  user_name, user_age into t_u_name,t_u_age from tab_user ; 两个变量，按先后顺序赋值

                             符合对象赋值：select  user_name, user_age into  t_user_info from  tab_user ; t_user_info含有t_u_name和t_u_age字段，若顺序颠倒，可以用".属性"指定

       3.3  执行开始和结束

              begin                             开始

                       return;                  强制结束

                       commit;                提交执行结果（若有增删改，必须写commit提交，否则白做）

              end 存储过程名;            结束

      

关于as和is的区别：视图只能用as，游标只能用is，其它地方无区别