sql server 定义集合变量 sql定义变量的语法是什么

语法部分

一. 变量

1.变量介绍

在编写pl/sql程序时，可以定义变量和常量；在pl/sql程序中包括有：
1)、标量类型(scalar)
2)、复合类型(composite) --用于操作单条记录
3)、参照类型(reference) --用于操作多条记录
4)、lob(large object)

2、标量–常用类型

定义（名 类 空否 默认值）

1)在编写pl/sql块时，如果要使用变量，需在定义部分定义变量。
pl/sql中定义变量和常量的语法如下：
identifier [constant] datatype [not null] [:=| default expr]
identifier: 名称
constant：指定常量。需要指定它的初始值，且其值是不能改变的
datatype：数据类型
not null：指定变量值不能为null
:= 给变量或是常量指定初始值
default 用于指定初始值
expr ：指定初始值的pl/sql表达式，可以是文本值、其它变量、函数等。

定义例子

1.定义一个变长字符串
v_ename varchar2(10);
2.定义一个小数,范围-9999.99~9999.99
v_sal number(6,2);
3.定义一个小数并给一个初始值为5.4，:=是pl/sql的赋值号
v_sal2 number(6,2):=5.4;
4.定义一个日期类型的数据
v_hiredate date;
5.定义一个布尔变量，不能为空，初始值为false
v_valid boolean not null default false;

使用标量例子

set serveroutput on; --打开输出选项
DECLARE
  --税率为0.03
  C_TAX_RATE NUMBER(3, 2) :=0.03;
  --雇员姓名
  V_ENAME   VARCHAR2(5);
  --工资
  V_SAL     NUMBER(7, 2);
  --个人所得税
  V_TAX_SAL NUMBER(7, 2);
BEGIN
  --执行
  SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP WHERE EMPNO=&empno; --7369
  --计算所得税
  V_TAX_SAL := V_SAL * C_TAX_RATE;
  --输出
  DBMS_OUTPUT.PUT_LINE('雇员姓名：' || V_ENAME || '工资：' || V_SAL || ' 交税：' || V_TAX_SAL);
END;
/

使用%type类型

对于上面的pl/sql块有一个问题：就是如果员工的姓名超过了5个字符的话，就会有“ORA-06502: PL/SQL: 数字或值错误 :
字符串缓冲区太小”错误，为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度。
使用%TYPE 特性的优点在于：
1.所引用的数据库列的数据类型可以不必知道；
2. 所引用的数据库列的数据类型可以实时改变。
因此，&type应运而生，登登登等：
标识符名 表名.列名%type;
比如上例的v_ename： v_ename emp.ename%type;

使用%Rowtype 类型

PL/SQL 提供%ROWTYPE 操作符, 返回一个记录类型, 其数据类型和数据库表的数据结构相一致。
使用%ROWTYPE 特性的优点在于：
1.所引用的数据库中列的个数和数据类型可以不必知道；
2.所引用的数据库中列的个数和数据类型可以实时改变。

复合变量

主要包括这几种：
1)、pl/sql记录
2)、pl/sql表
3)、嵌套表
4)、varray

复合记录

类似于高级语言中的结构体，需要注意的是，当引用pl/sql记录成员时，必须要加记录变量作为前缀(记录变量.记录成员)如下：

set serveroutput on; --打开输出选项
DECLARE
  --定义一个pl/sql记录类型emp_record_type，
  --类型包含3个数据NAME, SALARY, TITLE。说白了，就是一个类型可以存放3个数据，主要是为了方便管理 
  TYPE EMP_RECORD_TYPE IS RECORD(
    NAME   EMP.ENAME%TYPE,
    SALARY EMP.SAL%TYPE,
    TITLE  EMP.JOB%TYPE);
  --定义了一个sp_record变量，这个变量的类型是emp_record_type
  SP_RECORD EMP_RECORD_TYPE;
BEGIN
  SELECT ENAME, SAL, JOB INTO SP_RECORD FROM EMP WHERE EMPNO = 7788;
  DBMS_OUTPUT.PUT_LINE('员工名:' || SP_RECORD.NAME || '工资：' || SP_RECORD.SALARY);
END;
/

复合表

相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下标不能为负数，而pl/sql是可以为负数的，并且表元素的下标没有限制

方法一(推荐)：
 set serveroutput on; --打开输出选项
 DECLARE
   --定义了一个pl/sql表类型sp_table_type，该类型是用于存放EMP.ENAME%TYPE
   --INDEX BY VARCHAR2(20)表示下标是字符串
   TYPE SP_TABLE_TYPE IS TABLE OF EMP.ENAME%TYPE INDEX BY VARCHAR2(20);
   --定义了一个sp_table变量，这个变量的类型是sp_table_type
   SP_TABLE SP_TABLE_TYPE;
 BEGIN
   SELECT ENAME, sal INTO SP_TABLE('ename'), SP_TABLE('sal') FROM EMP WHERE EMPNO = 7788;
   DBMS_OUTPUT.PUT_LINE('员工名:' || SP_TABLE('ename')||'工资：'||SP_TABLE('sal'));
 END;
 /

方法二：
set serveroutput on; --打开输出选项
DECLARE
--定义了一个pl/sql 表类型sp_table_type，该类型是用于存放EMP.ENAME%TYPE
--index by binary_integer表示下标是整数
TYPE SP_TABLE_TYPE IS TABLE OF EMP.ENAME%TYPE INDEX BY BINARY_INTEGER;

--注意binary_integer如果换为integer就会报错，知道的朋友欢迎告诉我下
--定义了一个sp_table变量，这个变量的类型是sp_table_type
SP_TABLE SP_TABLE_TYPE;
BEGIN
SELECT ENAME,sal INTO SP_TABLE(-1),SP_TABLE(-2) FROM EMP WHERE EMPNO = 7788;
DBMS_OUTPUT.PUT_LINE('员工名:' || SP_TABLE(-1)||'工资：'||SP_TABLE(-2));
END;

关于表类型的理解

参考官方文档：

看成key-value的结构：

INDEX BY VARCHAR2(20)–设置了key的类型

TYPE SP_TABLE_TYPE IS TABLE OF EMP.ENAME%TYPE–设置了value的类型

二.控制结构

分支

CREATE OR REPLACE PROCEDURE SP_PRO6(SPNO NUMBER) IS
  --定义
  V_JOB EMP.JOB%TYPE;
BEGIN
  --执行
  SELECT JOB INTO V_JOB FROM EMP WHERE EMPNO = SPNO;
  IF V_JOB = 'PRESIDENT' THEN
    UPDATE EMP SET SAL = SAL + 1000 WHERE EMPNO = SPNO;
  ELSIF V_JOB = 'MANAGER' THEN
    UPDATE EMP SET SAL = SAL + 500 WHERE EMPNO = SPNO;
  ELSE
    UPDATE EMP SET SAL = SAL + 200 WHERE EMPNO = SPNO;
  END IF;
  COMMIT;
END;
/
--调用存储过程
exec SP_PRO6(7499);

循环

loop循环

CREATE OR REPLACE PROCEDURE SP_PRO6(SPNAME VARCHAR2) IS
  --定义 :=表示赋值
  V_NUM NUMBER := 1;
BEGIN
  LOOP
    INSERT INTO USERS VALUES (V_NUM, SPNAME);
    --判断是否要退出循环
    EXIT WHEN V_NUM = 10;
    --自增
    V_NUM := V_NUM + 1;
  END LOOP;
  COMMIT;
END;
/

--调用存储过程
EXEC SP_PRO6('ALLEN');

while循环

CREATE OR REPLACE PROCEDURE SP_PRO6(SPNAME VARCHAR2) IS
  --定义 :=表示赋值
  V_NUM NUMBER := 11;
BEGIN
  WHILE V_NUM <= 20 LOOP
    --执行
    INSERT INTO USERS VALUES (V_NUM, SPNAME);
    V_NUM := V_NUM + 1;
  END LOOP;
  COMMIT;
END;
/

--调用存储过程
EXEC SP_PRO6('ALLEN');

--调用存储过程
EXEC SP_PRO6('ALLEN');

for循环

CREATE OR REPLACE PROCEDURE SP_PRO6 IS--注意如果无参记得不要加()
BEGIN
  FOR I IN REVERSE 1 .. 10 LOOP --REVERSE反转函数，表示I从10到1递减，去掉REVERSE表示I从1到10递增
    INSERT INTO USERS VALUES (I, 'shunping');
  END LOOP;
END;
/

--调用存储过程
EXEC SP_PRO6;

顺序控制

goto语句

set serveroutput on;
DECLARE
  I INT := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('输出i=' || I);
    IF I = 1 THEN
      GOTO END_LOOP;
    END IF;
    I := I + 1;
  END LOOP;
  <<END_LOOP>>
  DBMS_OUTPUT.PUT_LINE('循环结束');
END;
/

null语句

SET serveroutput ON;

DECLARE
  V_SAL    LearningTb.SAL%TYPE;

  V_ENAME  LearningTb.ENAME%TYPE;

BEGIN
  SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM LearningTb WHERE EMPNO = &NO;
 
  IF V_SAL < 3000 THEN
   	 UPDATE LearningTb SET COMM = SAL * 0.1 WHERE ENAME = V_ENAME;
  
  	dbms_output.put_line('1111');
  
  ELSE
	NULL; --null语句不会执行任何操作，并且会直接将控制传递到下一条语句。使用null语句的主要好处是可以提高pl/sql的可读性
	dbms_output.put_line('null的下一条执行了');
	dbms_output.put_line('null的下下一条的执行了'); 
  END IF;
END;

/