SQL之PL/SQL

2019-03-17 本文已影响0人白璞1024

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

猜你喜欢

热点阅读