SQL之PL/SQL

[TOC]

1.1 标量类型的变量

    DECLARE 
    -- 这里申明变量
        v_productid productInfo.productid%TYPE; --这个字段的类型和表格中productid的类型保持一致
        v_productname varchar2(20);
        v_desperation CONSTANT v_productid%TYPE :='测试' ；--CONSTANT 表示常量 常量需要赋一个默认值 ：=赋值的方法
        v_date DATE :=SYSDATE 给的默认值
    BEGIN
        -- 开始语句
        SELECT PRDUCTID ,productname,productname 
        INFO v_productid ,v_productname
        -- 将查询回来的结果赋值给变量如上所示
        FROM productinfo
        WHERE productid = '0240040001'；
        -- 对应的sql语句编写完毕
        DBMS_OUTPUT.PUT_LINE('v_productid = '  || v_productid);
        DBMS_OUTPUT.PUT_LINE('v_productname = '  || v_productname);
    END;
    --注意end后头是有分号的

注意：

申明部分采用右对齐
productInfo.productid%TYPE表示同类型表productInfo中的productid字段的数据类型
v_desperation CONSTANT v_productid%TYPE :='测试';引用上边的变量的变量类型，申明伟一个常量，给一个默认值
DBMS_OUTPUT.PUT_LINE('v_productid = ' || v_productid);表示将结果输出到屏幕上

1.2 复合类型的变量

1.2.1记录类型

    DECLARE 
    TYPE product_rec IS RECORD --除了product_rec都表示关键字
    (
        v_productid productinfo.productid%TYPE; --类型和那张表里的那个字段的类型保持一致
        v_productname VARCHAR2(20);
        v_productprice NUMBER(8,2);
    )   
    --上边自己定义了一个类型，这个类型里有三个值
    v_product product_rec; --使用上边自己定义的变量
    
    BEGIN
        SELECT productid,productname,productprice
        FROM productInfo
        WHERE productid = '02040040001';
        
        DBMS_OUTPUT.PUT_LINE('productid = ' || v_product_rec.v_productid);
        DBMS_OUTPUT.PUT_LINE('productname = ' || v_product_rec.v_productname);
    END;

注意：

一开始先定义一个类型语法就是 TYPE type_name IS RECORD()括号里的内容和第一节的时候设置一样
v_product product_rec;使用设置好的类型
按照申明记录类型时候里边的成员顺序进行赋值

上述过程可以利用**%ROWTYPE简化代码

    DECLARE 

    v_product product_rec productInfo%ROWTYPE; --使用%ROWTYPE进行定义类型
    
    BEGIN
        SELECT *
        --这里的查询方式直接用*表示就可以
        FROM productInfo
        WHERE productid = '02040040001';
        
        DBMS_OUTPUT.PUT_LINE('productid = ' || v_product_rec.v_productid);
        DBMS_OUTPUT.PUT_LINE('productname = ' || v_product_rec.v_productname);
    END;

定义变量的方式使用productInfo%ROWTYPE; 这里要格外注意%ROWTYPE;的使用方式
我们科i直接查询到所有的记录都打包放到productInfo中

1.2.2 索引类型

利用键值查找对应的值，索引表中下标允许使用字符串,和我们常见的数组比较类似

DECLARE
TYPE prodt_tab_fat IS TABLE OF productinfo%ROWTYPE 
   INDEX BY BINARY_INTEGER;
   -- BINARY_INTEGER PLS_INTEGER 表示的是-21478348到+21478348之间 类型通常认为是一样的唯一的区别BIN溢出时候，能为其指派一个NUMBER类型而不发生异常，PLS会发生异常，建议使用PLS_INTEGER
TYPE peodt_tab_sec IS TABLE OF VARCHAR2(8)
   INDEX BY PLS_INTEGER;
   -- 申明一个索引，名称是peodt_tab_sec、prodt_tab_fat 这个索引中的元素都是productinfo的行记录。BINARY_INTEGER、PLS_INTEGER表示索引类型

TYPE prodt_tab_thd IS TABLE OF NUMBER(8)
   INDEX BY VARCHAR2(20)
   --字符串类型作为键值

v_prt_row prodt_tab_fst;
   v_prt prodt_tab_sec;
   v_prt_chr  prodt_tab_thd;
   
BEGIN
   v_prt(1) := '正数';
   v_prt(-1) := '负数';
   v_prt_chr('test') := 123;
   v_prt_chr('test1') := 0;
       --赋值
   
   
   SELECT * INTO v_prt_row(1) 
   FROM productInfo 
   WHERE productid = '0240040001';
   
   DBMS_OUTPUT.PUT_LINE('行数据-v_port_row(1) = ' ||v_port_row(1).productid||'---'||v_port_row(1).productname );
   --正常索引的查询方式
   DBMS_OUTPUT.PUT_LINE('v_prt_chr(123) = ' ||v_prt_chr('test')); --123  上边赋过值了
                        
   DBMS_OUTPUT.PUT_LINE('v_prt_chr(000) = ' ||v_prt_chr('test1')); --0   上边赋值了
                        
   DBMS_OUTPUT.PUT_LINE('v_prt_chr(000) = ' ||v_prt_chr.first); --test   第一个键值
                        
   DBMS_OUTPUT.PUT_LINE('v_prt_chr(000) = ' ||v_prt_chr(v_prt_chr.first)); -- 123 通过键值拿到val
   
END;

相当于prodt_tab_fat就是一个数组，然后每次插叙你的结果就都放到里边，数组的下表就相当于BINARY_INTEGER这个玩意儿
存放的范式是 SELECT * INTO v_prt_row(1) 获取的方式是v_port_row(1).productid
INDEX BY VARCHAR2(20)索引类型可以是可变字符串
v_prt_chr.first除了通过赋值的方式去取值，还有这种方式，是不是很惊喜

1.2.3 VARRAY变长数组

DECLARE     
    TYPE VARR IS VARRAY(100) OF VARCHAR2(20); --申明一个长度为100的数组
    v_product varr:=varr('1','2'); 
BEGIN
    v_product(1):='THIS IS A';
    v_product(2):='TEST';
    DBMS_OUTPUT.PUT_LINE('v_product(1) = ' || v_product(1); --THIS IS A   第一个键值
    DBMS_OUTPUT.PUT_LINE('v_product(2) = ' || v_product(2); --TEST   第二个键值
    
END;

-- 看起来不难的样子，之间看案例吧

1.3 结构控制

1.3.1 IF条件控制语句

三种控制语句

IF....
IF...ELSE...

IF...ELSIF...

DECLARE
  v_product productinfo%ROWTYPE;
BEGIN
  SELECT * INTO v_product
  FROM productinfo
  WHERE productid = '0240040001';
  
  IF v_product.productprice > 3000 THEN 
      DBMS_OUTPUT.PUT_LINE('产品属于高价格产品');
      IF v_product.QUANTITY > 50 THEN
          DBMS_OUTPUT.PUT_LINE('产品不缺货');
      ELSE 
          DBMS_OUTPUT.PUT_LINE('产品需要补货');
      END IF;
  ELSIF  v_product.productprice < 3000 AND  v_product.productprice > 1000 THEN
      DBMS_OUTPUT.PUT_LINE('产品属于中间价格产品');
  ELSE    
      DBMS_OUTPUT.PUT_LINE('产品需要补货');
      
END;

看起来很简单，自己根据DMEO来查看吧

1.3.2 CASE条件控制语句

简单的CASE语句

DECLARE 
    v_categoryid VARCHAR2(12);

BEIGN
    SELECT category INTO v_categoryid
    FROM productinfo
    WHERE productinfo='02030030001';

    CASE v_categoryid
    WHEN '010001000'|| '1' THEN
        DBMS_OUTPUT.PUT_LINE(v_cateforyid||'对应雨具');
    WHEN '010003001' THEN
        DBMS_OUTPUT.PUT_LINE(v_cateforyid||'对应电视');
    WHEN '010001002' THEN
        DBMS_OUTPUT.PUT_LINE(v_cateforyid||'对应路由器');
    ELSE 
        DBMS_OUTPUT.PUT_LINE('没有对应的产品类型');
    END CASE;
    
    DBMS_OUTPUT.PUT_LINE('CASE结构已经完成');
END;

WHEN语句执行后，CASE语句就会结束，而不会接着走下边的when

搜索式的CASE语句

DECLARE 
    v_productprice NUMBER(8,2);

BEIGN
    SELECT productprice INTO v_productprice
    FROM productinfo
    WHERE productinfo='02030030001';

    CASE
    WHEN v_productprice <= 1000 THEN
        DBMS_OUTPUT.PUT_LINE('低价产品价格是'||v_productprice);
    WHEN v_productprice <= 3000 AND  v_productprice > 1000 THEN
        DBMS_OUTPUT.PUT_LINE('高价位价产品价格是'||v_productprice);
    ELSE 
        DBMS_OUTPUT.PUT_LINE('错误价格，价格是'||v_productprice);
    END CASE;
    
    DBMS_OUTPUT.PUT_LINE('CASE结构已经完成');
END;

和上边唯一的区别就是 CASE后头是没有值的，可以直接进行

1.3.3 LOOP循环语句

LOOP

    [<<label_name>>]   -- 定义循环名
    LOOP
    --------------------------------方式1 IF跳出循环
        IF boolean_exressuion
            EXIT label_name;--跳出循环
        END IF;
    --------------------------------方式2 WHEN跳出循环
        statement...
        EXIT label_name WHEN extr2 >0 ;--通过when的方式跳出循环
    END LOOP [label_name]  --循环结束

WHILE...LOOP
```
[<<label_name>>]
WHILE boolean_exressuion
LOOP
    statement...
END LOOP [label_name]
```
- 这个执行顺序是先判断，然后循环，所以可能一次执行的机会都没有

FOR...LOOP

[<<label_name>>]
FOR index_name IN
[REFERSE]
LOOP
    statement...
END LOOP [label_name]

--------------------------eg------------------------------------
DECLARE 
    v_num NUMBER(8) ：=0;
BEIGN
    DBMS_OUTPUT.PUT_LINE('1-20之间整数和');
    <<for_loop>>
    FOR inx IN 1..20 LOOP
        v_num := v_num + inx; 
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(v_num);
END;

1.4 函数编写

1.4.1 函数编写

1.4.1 函数

CREATE [OR REPLACE] FUNCTION [schema.] function_name
[
    (parameter_declaration [,parameter_declaration])
]
RETURN datatype
{IS|AS}
[declare_section]
BEGIN
    statement[statement|pragma]...
END [name];

----------------------------------eg--------------------------------------------

-----声明一个函数----
CREATE FUNCTION AVG_PRIC(V_CTGRY IN VARCHAR2,V_PRICE IN OUT VARCHAR2) RETURN NUMBER IS 
V_QNTY NUMBER;

BEGIN 
    IF V_PRICE IS NULL THEN 
        V_PRICE :=0;
    END IF;
    
    SELECT AVG(PRODUCTPRICE),MIN(QUANTITY)
    INTO V_PRICE,V_QNTYv   --因为v-price 有out属性，所以可以赋值进去
    FROM PRODUCTINFO
    WHERE CATEGORY = V_CTGRY
        AND PRODUCTPRICE > V_PRIC;
    
    RETURN V_QNTY;
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('没有对应数据！');
    WHEN TOO_MANY_ROWS THEN 
        DBMS_OUTPUT.PUT_LINE('对应数据过多，请确认！');
END;
-----使用这个函数----
DECLARE
    V_CTGRY VARCHAR2(10) :='01000040001';
    V_PRICE VARCHAR2(20) :=1500;
    V_QNTY VARCHAR2(20);
BEGIN
    V_QNTY := AVG_PRIC(V_CTGRY,V_PRICE);
    DBMS_OUTPUT.PUT_LINE('平均价格：'|| V_PRICE); -- 因为上边对v_price定义的时候是IN OUT所以V_PRICE变量也随之改变了
    DBMS_OUTPUT.PUT_LINE('最低产品数量是：' || V_QNTY);
ENDL

创建函数AVG_PRIC两个参数，其中v_pric是一个输入输出参数

1.4.2查看、修改、删除函数

COL  OBJECT_NAME FORMAT A60

SELECT OBJECT_NAME,OBJECT_ID ,OBJECT_TYPE FROM USER_PROCESURES ORDER BY OBJECT_TYPE
-- 从 USER_PROCESURES表中查询现在的函数
COL  NAME FORMAT A15
COL  NAME FORMAT A80
SELECT NAME,LINE,TEXT FROM USER_SOURCE WHERE NAME = 'AVG_PRICE';
--从USER_SOURCE表中查询现在的函数

DROP FUNCTION [schema.] function--最后加的是function的名字

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 216,163评论 6赞 498
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 92,301评论 3赞 392
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 162,089评论 0赞 352
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 58,093评论 1赞 292
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 67,110评论 6赞 388
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,079评论 1赞 295
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,005评论 3赞 417
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,840评论 0赞 273
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,278评论 1赞 310
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,497评论 2赞 332
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,667评论 1赞 348
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,394评论 5赞 343
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,980评论 3赞 325
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,628评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,796评论 1赞 268
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,649评论 2赞 368
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,548评论 2赞 352

SQL之PL/SQL

1.1 标量类型的变量

1.2 复合类型的变量

1.2.1记录类型

1.2.2 索引类型

1.2.3 VARRAY变长数组

1.3 结构控制

1.3.1 IF条件控制语句

1.3.2 CASE条件控制语句

1.3.3 LOOP循环语句

1.4 函数编写

1.4.1 函数编写

1.4.1 函数

1.4.2查看、修改、删除函数

推荐阅读更多精彩内容