PL/SQL 训练01--基础介绍
--开始介绍变量之前，我们先看下怎么在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; /
