oracle之存储过程

1.声明变量的注意点：

1）声明变量必须在"begin"关键字之前；

2)  声明变量不需要使用关键字"DECLARE"；

3）声明变量的变量不区分大小写；

下面进行声明变量的3种方式有

第一种：直接声明数据类型（按照数据类型的声明方式进行区分）

格式：变量名 数据类型(大小)

V_STUDENT_NAME VARCHAR2(60); // 正确的例子
v_NUM number; // 数字类型，无需设置大小

　注意：如果设置为字符串类型时，需要指定大小，否则报错；如果设置为数字类型，则不需要设置大小

例子有：

 

declare // 可有可无
  mmsql varchar2(4000);
  tsCount number(2);
  ssCount number(2);

 

　　声明变量错误的用法有：

--错误用法，原因是变量声明再begin后面了
BEGIN
v_sql varchar2(100) 

 

 第二种：使用%TYPE声明

　　格式：变量名 表名.字段名%TYPE

　　含义：该变量的数据类型与指定表的指定字段的数据类型一致

V_STUDENT_NAME STUDENT.STUDENT_NAME%TYPE;　　　

　第三种：使用%ROWTYPE声明

　　格式：变量名 表名%ROWTYPE

　　含义：该变量的数据类型与指定表的指定行记录（所有字段）的数据类型一致

--VIRTUAL_CARD表整行数据
V_ROW_VIRTUAL_CARD VIRTUAL_CARD%ROWTYPE;

　---------------------------

2.变量赋值的3种方式

方式一：直接赋值，使用":="

　　使用条件：适用于声明变量的前2种方式。

例如：V_STUDENT_NAME := '张三';　　

说明：

　　变量后面跟不能直接是sql语句，sql不会执行，可以参考方式三。

--错误用法
V_STUDENT_NAME := SELECT STUDENT_NAME INTO V_STUDENT_NAME FROM STUDENT;
--正确用法
V_STUDENT_NAME := 'SELECT STUDENT_NAME INTO V_STUDENT_NAME FROM STUDENT';

　　另外：变量的声明和赋值可以一块进行如：

--正确用法
v_sql varchar2(100) := 'SELECT STUDENT_NAME FROM STUDENT where orgcode=to_char(410621101233)';
BEGIN
 /* 具体业务 */
END;

　方式二：select 表字段 into 变量 from 表

　　变形一：查询指定表的一个指定字段

　　使用条件：适用于声明变量的前2种方式可以使用

--根据医疗机构ID查询对应的父机构的机构序列
SELECT ORGSEQ INTO V_ORGSEQ FROM BASE_ORG_INFO
 WHERE ORGID =
       (SELECT PARENTORGID FROM BASE_ORG_INFO WHERE ORGID = V_ORGID);

　　变形二：查询指定表的所有字段

　　使用条件：只适用于声明变量的第3种方式

1.  
   --将id=5120的VIRTUAL_CARD表数据赋值给变量V_ROW_VIRTUAL_CARD
2.  
   SELECT * INTO V_ROW_VIRTUAL_CARD FROM VIRTUAL_CARD T where t.id = 5120;

　　说明：

　　查询结果只能返回一条记录；

　　查询的表字段必须是该表的所有字段。

　　错误举例:

--错误举例一：查询的是所有表记录
SELECT * INTO V_ROW_VIRTUAL_CARD FROM VIRTUAL_CARD T;
--错误举例二：查询的是该表的多个字段
SELECT t.id,t.name INTO V_ROW_VIRTUAL_CARD FROM VIRTUAL_CARD T; 

方式三：execute immediate sql语句字符串 into 变量

1.  
   declare/* 存储过程，不需要声明 */
2.  
   v_sql varchar2(100);
3.  
   V_ORGSEQ varchar2(100);
4.  
   begin
5.  
   v_sql := 'SELECT ORGSEQ FROM BASE_ORG_INFO where orgcode=to_char(410621101233)';
6.  
   --V_ORGSEQ赋值
7.  
   execute immediate v_sql INTO V_ORGSEQ;
8.  
   --打印结果
9.  
   DBMS_OUTPUT.put_line(V_ORGSEQ);
10.  
    END;

工作中例子：

declare
  ssql varchar2(4000); // 声明变量
  tCount number(2); // 声明变量
  sCount number(2); // 声明变量
begin 
  tCount := 0; // 初始化赋值
  select count(*) into tCount from user_tables t where upper(t.table_name) = upper('PPOS_FUND_INFO'); // 赋值
  if tCount = 0 then // 判断逻辑
      ssql := 'create table PPOS_FUND_INFO(
          fund_id           	varchar2(6) not null,
          fund_name        		varchar2(255) not null,
          fund_short_name   	varchar2(255),
		  fund_eng_name			varchar2(255),
		  ta_fund_code			varchar2(32),
		  ts_fa_code			varchar2(255),
		  os_fa_code			varchar2(255),
		  fund_type				varchar2(64),
		  service_type			varchar2(32),
		  manager_code			varchar2(32),
		  fund_status			varchar2(2),
		  fund_sub_status		varchar2(2),
		  ta_fund_status		varchar2(2),
		  ass_record_state		varchar2(2),
		  ass_record_code		varchar2(64),
		  is_structured_fund	varchar2(2),
		  is_mother_child_fund	varchar2(2),
		  is_fof_fund			varchar2(2),
		  fund_lev				varchar2(2),
		  praent_fund_id		varchar2(6) not null,
		  constraint PK_PPOS_FUND_INFO PRIMARY KEY(fund_id) using index tablespace FUND_INDEX
		)tablespace FUND_TABLE';
    execute immediate ssql; // 执行sql
  end if;
end; // 结束出口
/

　　

1. --正确用法
2.  
   v_sql varchar2(100) := 'SELECT ORGSEQ FROM BASE_ORG_INFO where orgcode=to_char(410621101233)';
3.  
   BEGIN
4.  
   /* 具体业务 */
5.  
   END;