--开始介绍变量之前，我们先看下怎么在PLSQL写程序，如下我们写了一个块
declare
--声明部分，声明变量
v_name varchar2(30) :='hello world'; begin
--执行区域
dbms_output.put_line(v_name);--如果使用SQL*PLUS，需要先执行set SERVEROUTPUT ON SIZE XXXXXX 才能显示打印信息 exception
when others then
null ; --异常处理区域
end ; --结束符
/
--以上是一个匿名块，它包含三部分，声明部分（声明变量、常量，游标，定义过程、函数）、执行区(执行SQL代码或PLSQL代码)，异常处理部分
-- 声明部分和异常处理部分是可选的，比如可以这样,这是最简单的块
begin
dbms_output.put_line('hello world');--
end ;
/
--也可以什么都不做,可以使用NULL
begin
null ;
end ;
/
--需注意的是，每个语句结束都是用;（分号）作为结束符，比如END后面需要带上;,如果是匿名库，后面需要带上/，不然使用客户端比如SQLPLUS执行代码，是不会执行的
--有匿名块，相反也有命名块，命名块包含过程块，函数块。
-- 过程块,过程块接受（多个）入参和出参，但不直接返回值，不可以在SQL或查询语句中调用
create or replace procedure test_procedure(i_name in varchar2/*,o_age out number*/) is begin dbms_output.put_line(i_name) ;
/* o_age := 25;*/ end test_procedure; --怎么调用过程块呢？ begin test_procedure('hello world'); --在匿名块中直接调用 end ;
/
--在sqlplus中也可以使用 execute test_procedure('Hi');
--也可以使用execute immediate 来执行动态语句,比如
declare
v_sql varchar2(300);
v_name varchar2(30):='hello execute immediate';
begin v_sql := 'begin test_procedure(:v_name); end;'; execute immediate v_sql using v_name; end ;
/
--与过程块不同，函数可以直接返回值，但不接受出参，可以在sql或查询语句中直接调用
create or replace function test_function(i_name in varchar2)
return varchar2 is begin
return i_name ||' hello world'; end test_function; --可以在查询语句中调用,或直接赋值给变量，比如
select test_function('Bob') from dual ;
declare
v_name varchar2(300);
begin
v_name := test_function('Bob');
dbms_output.put_line(v_name);
end ;
/
-- 在SQLPLUS不能使用execute调用函数，因为前者不管理函数返回值
-- 可以使用VARIABLE 定义一个会话级的变量，然后使用CALL XXXXX INTO 变量
--块与块之间可以互相嵌套，比如在匿名块中，可以定义命名块，在命名块中可以使用匿名块，比如
declare procedure test_inline_block(i_name in varchar2,i_age in number) is
begin
dbms_output.put_line(i_name||',今年'||i_age||'岁了'); end ;
begin test_inline_block('my baby',5);
end ;
/ BEGIN test_inline_block('my baby',5);
END ;
--现在来看下面的例子，能否执行成功? DECLARE
--定义过程
PROCEDURE TEST_B IS
BEGIN DBMS_OUTPUT.put_line('Hello '||TEST_A); --调用函数
END TEST_B; --定义函数
FUNCTION TEST_A RETURN VARCHAR2 IS
BEGIN
RETURN 'WORLD';
END TEST_A ; BEGIN TEST_B; END;
/
--执行结果是报错了，TEST_A没有定义。
-- 为什么呢？PL/SQL是按照自顶向下的顺序，将标识符读取到内存中，这是一个单次分析过程。
--命名块也是标识符。程序在编译时，会对程序进行分析，并且识别标识符。在TEST_B调用TEST_A时，TEST_A还没有定义
--这种情况怎么解决呢？比较笨的方法是，把TEST_A放到TEST_B之前,比如
DECLARE --定义函数
FUNCTION TEST_A RETURN VARCHAR2 IS
BEGIN
RETURN 'WORLD';
END TEST_A ;
--定义过程
PROCEDURE TEST_B IS
BEGIN DBMS_OUTPUT.put_line('Hello '||TEST_A); --调用函数
END TEST_B; BEGIN TEST_B; END;
/
--也可以使用前向引用的方法，可以先声明命名块，占个位（STUB），再进行定义，比如 DECLARE
PROCEDURE TEST_B;
FUNCTION TEST_A RETURN VARCHAR2; --定义过程
PROCEDURE TEST_B IS
BEGIN DBMS_OUTPUT.put_line('Hello '||TEST_A); --调用函数
END TEST_B; --定义函数
FUNCTION TEST_A RETURN VARCHAR2 IS
BEGIN
RETURN 'WORLD';
END TEST_A ; BEGIN TEST_B; END;
/
--结果可以执行成功。
--接下来，我们来了解下变量
--基本上每一个PLSQL程序都要定义和操作数据，变量可以用来临时存储数据，操作以存储的数据，并且可复用。
--前面说过，变量可以在块中的声明部分进行定义，那么怎么命名变量呢，有如下几个规则
--名字必须以字母开始
--名字的长度最长可以30个字符
--第一个字母之后，可以使用字符包括，字母、数字、$、#、_
--所有的名字是不区分大小写的（除非名字被放在双引号里）
--不能使用关键字作为变量，比如number等
DECLARE D_$ NUMBER ;
--d_$ number ;
BEGIN
dbms_output.put_line(d_$) ;
END ; --现在看一段代码 select * from MA_USERS;
update ma_users t
set t.user_score = null
where 1=1;
commit; DECLARE USER_Score NUMBER := 100;
BEGIN
dbms_output.put_line(USER_Score) ;
UPDATE MA_USERS T
SET T.User_Score = NVL(USER_Score,0)
WHERE T.USER_SCORE IS NULL ; END ;
/
-- 大家可以先考虑下，这段代码的结果是什么，表中字段USER_CORE最终被更新成什么了？
--这是初学常会犯的错误，变量的名称跟表中的字段一样，结果使用的时候，很困惑，为什么变量的值为NULL，明明已经初始化了。
--命名有两点建议
-- 保证每个名字能准确地反映它的用途且一望即知
--建立一个一致的，明显的命名规范
--一般局部变量需要带一个前缀，我一般喜欢带"v_"，游标变量带"cur_",全局变量带“g_”
--怎样声明一个变量呢？
declare
-- 变量名称 数据类型 [not null] [:= |defualt 初始化值]
v_num number ;--数值，浮点小数
v_date date ;--日期
v_vch varchar2(100);--字符串，可变长度
v_ch char(100);
v_bool boolean ; -- 布尔值 true ,false null v_num1 number(10,2):=0.3; -- 定义小数，左边八位，小数点右边两位
v_num2 number(2) := 1;--整数
-- 指定了not null 这个变量值不能为空
v_date1 date not null default sysdate; begin
v_date1 := date '2015-9-1' ;
v_vch :='test';
end;
/
--赋值操作语法跟DEFAULT语法是等价的，可以互换使用，一般常用赋值操作语法（:=）
--如果需要从数据库表或者其他PLSQL程序结构获得数据，一个好的事件方式是将变量和对象“锚定”在一起
--如果是标量变量使用%TYPE，如果是复合变量，则使用%ROWTYPE DECLARE V_USER_NAME MA_USERS.USER_NAME%TYPE;--此变量数据类型与表中字段类型一样，有依赖关系 BEGIN SELECT t.user_name into V_USER_NAME FROM MA_USERS t where rownum =1; dbms_output.put_line(V_USER_NAME); END ;
/
--使用“锚定”的好处
--与数据库列的同步
--局部变量的标准化
--对于第一个种情况，我们假设原来ma_user表中的USER_NAME 原来是varchar2(50)，这样使用 DECLARE V_USER_NAME varchar2(50); BEGIN SELECT t.user_name into V_USER_NAME FROM MA_USERs t where rownum =1; dbms_output.put_line(V_USER_NAME); END ;
/
--后来由于业务发展需要，或者满足更长的用户名的需要，这个字段需要扩展到varchar2(100)，这样问题就来了，上面的代码需要改造，不然会报错（想想为什么？）
--如果程序里使用的情况多的话，需要一一找出来，做影响分析，然后进行修改，修改的点还要一一测试，这需要耗费很多的人力
--如果一开始使用%type方式声明，就不存在这种情况
--前面，有涉及到标量一词，在PL/SQL中，有标量和复合变量之分
--什么是标量？标量只是一个单独的值构成，比如一个数字或者字符串
--常用的标量数据类型，有char,varchar2,number,date,boolean
declare
-- 变量名称 数据类型 [not null] [:= |defualt 初始化值]
v_num number ;--数值，浮点小数
v_date date ;--日期
v_vch varchar2(100);--字符串，可变长度
v_bool boolean ; -- 布尔值 begin
null ;
end;
/
--关于CHAR和VARCHAR2,一个是定长，一个是可变长度，比较常用的是可变长度的字符串，节省空间,看以下例子
DECLARE
V_CH CHAR(100);
V_VAR VARCHAR2(100);
BEGIN
V_CH := 'TEST';
V_VAR :='TEST';
DBMS_OUTPUT.put_line(LENGTH(V_CH));
DBMS_OUTPUT.put_line(LENGTH(V_VAR));
END ;
/
--数值类型的变量使用时，需主要值的精度，比如以下两个变量，v_num打印的结果是保留了两位小数，四舍五入
--V_NUM1直接报错，精度不一致。
declare
v_num number(5,2) := 52.2365;
--v_num1 number(3,2) := 52.2365;
begin dbms_output.put_line(v_num); end ;
/
-- 什么是复合变量？有多个值组成，比如一个记录，一个集合或者一个对象
--复合变量包括记录，表，嵌套表，VARRAY
--记录类型是一种复合的数据结构，由多个元素或成员组成的，每个成员都有自己的值，类似与C中的结构体
--PL/SQL中的记录类型从结构上和概念上非常类似于数据库表的行
--记录有三种定义方式
--第一种是基于表的记录类型，使用表名加上%rowtype声明一个记录类型
DECLARE
V_USER MA_USERS%ROWTYPE; BEGIN
SELECT * INTO V_USER FROM MA_USERS
WHERE ROWNUM =1;
DBMS_OUTPUT.put_line(V_USER.USER_NAME); END ;
/
--基于游标的记录类型，使用显示声明的游标或游标变量加上%rowtype声明基于一个游标的记录类型
DECLARE
CURSOR CUR_USER IS
SELECT *
FROM MA_USERS T
WHERE T.USER_NAME LIKE 's%'; v_user_cur CUR_USER%ROWTYPE; BEGIN
OPEN CUR_USER;
LOOP
FETCH CUR_USER
INTO V_USER_CUR;
EXIT WHEN CUR_USER%NOTFOUND;
DBMS_OUTPUT.put_line(V_USER_CUR.USER_NAME);
END LOOP;
CLOSE CUR_USER;
END;
/
--另外一种声明记录的方式是隐式的，主要见于游标型FOR循环中，关于游标详细的内容在下节课详讲
--自定义记录类型
--使用TYPE XXXX IS RECORD定义，比如 DECLARE
--定义记录类型
TYPE USER_TYP IS RECORD(
USER_NAME MA_USERS.USER_NAME%TYPE,
USER_SCORE MA_USERS.USER_SCORE%TYPE);
--声明记录类型变量
V_USER USER_TYP; BEGIN SELECT USER_NAME, USER_SCORE
INTO V_USER
FROM MA_USERS T
WHERE T.USER_NAME = 'scott'; DBMS_OUTPUT.put_line(V_USER.USER_NAME || ',积分' || V_USER.USER_SCORE); END;
/
---------------------------------------------------------
假如现在要做一个线上订购系统，这个系统首先有一个用户模块，所以我们要设计这个模块，
首先要设计一张用户表存放用户信息要求记录用户的用户名，密码，证件号，真实姓名，性别，生日，手机号，地址，email地址，积分，激活状态等，其它信息可各自补充
1. 请大家设计这个用户表
2.假如现在系统已经完成，网站要做一个贴心活动，对于注册且激活了且生日即将到达的用户（生日前五天），
我们根据其注册时间的长短，送积分，积分数=注册时间月数*100，请大家用PLSQL实现这个功能
oracle环境
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE 12.1.0.1.0 Production
TNS for Linux: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production
使用工具
Toad for Oracle 1 创建表
CREATE TABLE PLSQLXUNLIAN_USERS
(
USER_ID VARCHAR2(32 BYTE) DEFAULT sys_guid(),
USER_NAME VARCHAR2(64 BYTE) NOT NULL,
USER_PASSWORD VARCHAR2(64 BYTE) NOT NULL,
ID_NO VARCHAR2(64 BYTE) NOT NULL,
REAL_NAME VARCHAR2(20 BYTE),
SEX NUMBER(1),--0=woman,1=man
BIRTHDAY DATE NOT NULL,
MOBILE_PHONE NUMBER(15),
ADDRESS VARCHAR2(100 BYTE),
EMAIL VARCHAR2(100 BYTE),
USER_INTEGRAL NUMBER DEFAULT 0,
USER_STATUS NUMBER(2) NOT NULL,--0=inactive,1=active,-1=delete
USER_PHOTO VARCHAR2(100 BYTE),
REGISTER_DATE DATE NOT NULL,
CREATE_ON DATE DEFAULT sysdate NOT NULL,
UPDATE_ON DATE
)
2 插入测试数据
insert into plsqlxunlian_users(user_name,user_password,id_no,real_name,sex,birthday,user_status,register_date)
select ename,sys_guid(),empno,empno,1,hiredate,1,sysdate-50 from emp； 3 plsql代码
/* Formatted on 2016/2/24 18:24:30 (QP5 v5.149.1003.31008) */
DECLARE
v_user_id VARCHAR2 (100);
v_birthday DATE;
v_user_integral NUMBER;
v_user_status NUMBER;
v_register_date DATE;
v_sysdate DATE DEFAULT SYSDATE;
v_months NUMBER DEFAULT 0; CURSOR cur_user
IS
SELECT user_id,
birthday,
user_integral,
user_status,
register_date
FROM plsqlxunlian_users t
WHERE t.user_status = 1
AND TO_CHAR (birthday, 'mm-dd') BETWEEN TO_CHAR (v_sysdate ,
'mm-dd')
AND TO_CHAR (v_sysdate + 5,
'mm-dd');
/*
对于注册且激活了且生日即将到达的用户（生日前五天），
我们根据其注册时间的长短，送积分，积分数=注册时间月数*100，请大家用PLSQL实现这个功能
*/
BEGIN
FOR user_c IN cur_user
LOOP
v_user_id := user_c.user_id;
v_user_integral := user_c.user_integral;
v_register_date := user_c.register_date; SELECT CEIL (MONTHS_BETWEEN (v_sysdate,v_register_date))
INTO v_months
FROM DUAL; ---update user's integral UPDATE plsqlxunlian_users
SET user_integral = v_user_integral + v_months * 100,
update_on = v_sysdate
WHERE user_id = v_user_id; --可根据需要添加赠送积分的记录表
DBMS_OUTPUT.put_line (user_c.user_id);
END LOOP; COMMIT;
END;
/