SQL之PL/SQL

作者: 白璞1024 | 来源:发表于2019-03-17 18:38 被阅读0次

[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的名字

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查看、修改、删除函数

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读