oracle-plsql总结

最新推荐文章于 2024-01-13 16:09:06 发布

fanflile

最新推荐文章于 2024-01-13 16:09:06 发布

阅读量540

点赞数

文章标签： oracle plsql

本文链接：https://blog.csdn.net/fanflile/article/details/103085078

版权

基础
1. 变量字母开头，最长30字符，不区分大小写
2. &+变量名：视为替代变量，接收用户输入值，变量名不能出现&
3. 基于数据库对象的数据类型：表名%ROWTYPE,表名.字段名%TYPE
4. 常见数据类型：
  1. VARCHAR2:可变长字符数据，数据库列宽最大4000字节，字符数据最大长度32767字节
  2. CHAR: 定长字符类型，空格填充，字符数据最长32767字节，数据库列宽2000字节，默认1字节
  3. NUMBER(p,q):任何大小整数或浮点数(38个十进制位），p数字位数（包括小数），q小数位数（注意会在q位四舍五入）
  4. BINARY_INTEGER: 二进制格式(±2^32-1)
  5. DATE: 日期
  6. TIMESTAMP: 精确到秒的小数位（最高9位小数）
  7. BOOLEAN ：数据库没有BOOLEAN类型，一般用number(1)或char(1)替换。存储过程有BOOLEAN类型(TRUE、FALSE、NULL)
  8. RAW，类似于CHAR，声明方式RAW(L)，L为长度，以字节为单位，作为数据库列最大2000，作为变量最大32767字节。
  9. LONG RAW :二进制变量数据，最大2GB
  10. LOB大型对象：BOLB、CLOB、NLOB如图像、视频，BFILE(存储在数据库之外的二进制文件)，最大4GB
  11. rowid：伪列（64位编码），数据库段+表空间+行数据数据块+该行数据。
5. :=用于赋值给变量
6. 条件中包含变量/字段 <>0 ：结果不包括null

简单语句

select 列名1、列名2 into 变量1,变量2 from 表名 必须唯一，且不能为空，否则报too_many_row和no_data_found

--一般配合max()、nvl函数使用
    SELECT NVL(MAX(列名),'NOTFOUND') INTO 变量  from 表名

条件语句

IF condition1 AND condition3 THEN  
--如果condition1已经为false，还会执行condition3？
  Statement1...
ELSIF condition2 then
  Statement2...
ELSE
  Statement3..
ENF IF;

case when

--每个when条件只会执行一次，一旦匹配到true条件，后续不在执行
    case selector 
        when condition1 then statement1
        when condition2 then statement2
        else statementn
    end case;
    --搜索式：
     case  
        when expression1 then statement1
        when expression2 then statement2
        else statementn
    end case;
    --CASE表达式
    --变量 :=case .....(上述两种方式，但statement为对于类型的value)

循坏语句

--1、LOOP循环
LOOP 
    STATEMENT1;...
    EXIT WHEN CONDITION1;--或使用IF C1 THEN EXIT END IF;语句
END LOOP;
--2、WHILE循环
WHILE CON1 LOOP  
    statement1;....
END LOOP;

--3、FOR循环，配合游标使用很方便
FOR LOOP_conter IN [REVERSE] LOWLIMIT..higerlimit LOOP--双点好(..)表示范围操作
    statement1;
END LOOP;

--4、循环控制 
    EXIT WHEN CONDITION1;--或使用IF C1 THEN EXIT END IF;语句
    CONTINUE WHEN CONDITION1;--或使用IF C1 THEN CONTINUE END IF;语句

异常处理

内置异常

命名的系统异常	错误	产生原因
ZERO_DIVIDE	ORA-01476	除数为 0
NO_DATA_FOUND	ORA_06548	使用 select into 未返回行，或应用索引表未初始化的元素时
TOO_MANY_ROWS	ORA-01422	执行 select into 时，结果集超过一行
SUBSCRIPT_BEYOND_COUNT	ORA-06533	元素下标超过嵌套表或 VARRAY 的最大值
SUBSCRIPT_OUTSIDE_LIMIT	ORA-06532	使用嵌套表或 VARRAY 时，将下标指定为负数
INVALID_CURSOR	ORA-01001	在不合法的游标上进行操作,不允许的游标操作，比如关闭一个已经被关闭的游标，就会引发
INVALID_NUMBER	ORA-01722	内嵌的 SQL 语句不能将字符转换为数字
DUP_VAL_ON_INDEX	ORA-00001	唯一索引对应的列上有重复的值
CURSER_ALREADY_OPEN	ORA-06511	游标已经打开
COLLECTION_IS_NULL	ORA-06531	集合元素未初始化
CASE_NOT_FOUND	ORA-06592	CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时
ACCESS_INTO_NULL	ORA-06530	未定义对象
VALUE_ERROR	ORA-06502	赋值时，变量长度不足以容纳实际数据
ROWTYPE_MISMATCH	ORA-06504	宿主游标变量与 PL/SQL 游标变量的返回类型不兼容
SELF_IS_NULL	ORA-30625	使用对象类型时，在 null 对象上调用对象方法
STORAGE_ERROR	ORA-06500	运行 PL/SQL 时，超出内存空间
SYS_INVALID_ID	ORA-01410	将无效的字符串转化为ROWID的时候引发
TIMEOUT_ON_RESOURCE	ORA-00051	Oracle 在等待资源时超时

处理异常格式

DECLEAR
    VARIABLE;
BEGIN
    EXECUTE STATEMENT;
EXCEPTION
    WHEN 内置异常1 THEN
        Statement1;//相应异常的处理
    WHEN 内置异常2 THEN
        Statement2;//相应异常的处理
END ;

抛出异常

--异常名称一般以e_为前缀
---1、手动抛出异常
DECLARE
    EXCEPTION-NAME EXCEPTION;--声明异常名称
BEGIN
    IF CON1 THEN 
        RAISE EXCEPTION-NAME;
    END IF;
EXCEPTION 
    WHEN EXCEPTION-NAME THEN 
        PROCESSING;
END ;

--2、重新抛出异常
DECLARE
    EXCEPTION-NAME EXCEPTION;--声明异常名称
BEGIN
    STATEMENT1;
    ...
    ...
    DECLARE
        ----声明变量
    BEGIN
        STATEMENT;
        ...
    EXCEPTION 
        WHEN EXCEPTION-NAME THEN 
            RAISE;//重新抛出异常
    END ;
EXCEPTION 
    WHEN EXCEPTION-NAME THEN 
        PROCESSING;
END ;

异常处理(高级)

RAISE_APPLICATION_ERROR(ERR_NUMBER,ERR_MESSAGE,(keee_errors)):可自定义错误代码和错误信息

DECLARE
    --声明变量
BEGIN
    STATEMENT1;
    ...
    IF CON1 THEN 
        RAISE_APPLICATION_ERROR('-200002','THE NUMBER IS TOO BIG');
    END IF;
END ;
--返回类似内置异常的错误格式
ORA-200002: THE NUMBER IS TOO BIG

EXCEPTION_INIT(SLEF-EXCEPTION-NAME,NESTED-ERR-CODE)绑定内置错误编号与自定义名称

--oracle很多错误代码并没有对应得EXCEPTION_NAME,
--使用EXCEPTION_INIT关联，以便于捕获并进行处理
DECLARE
    E_CHILD_EXISTS EXCEPTION;--声明异常名称
    PRAGMA EXCEPTION_INIT(E_CHILD_EXISTS,-2292)
    --  (-2292是删除记录时，该记录存在其他表格外键记录)
BEGIN
    STATEMENT1;
    ...
    ...
EXCEPTION 
    WHEN E_CHILD_EXISTS THEN 
        PROCESSING;
END ;

SQLCODE和SQLERRM

DECLARE
   --声明变量
BEGIN
    statement;
    ...
    ...
EXCEPTION 
    WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE('errcode:'||SQLCODE);
        DBMS_OUTPUT.PUT_LINE('errmessage:'||SQLERRM);
        --DBMS_OUTPUT.PUT_LINE('errmessage:'||SQLERRM(100));输出指定errcode的错误信息
END ;

游标

隐式游标
1. 所有DML语句(UPDATE、DELETE、INSERT)语句
2. SELECT INTO 语句
3. 隐式游标的名字为SQL,SQL%ROWCOUNT获取隐式游标影响行数
显示游标
1. 包括声明，打开，检索，关闭
2. 游标不关闭，游标打开的表会缓存在PGA里，并且连接到系统的每个用户连接都独占了一个PGA，直到用户断开连接或者关闭游标才会释放游标所占的内存。
数据类型

DECLARE
    --STUDENT 表（id number(10),pname varchar2(20),cname varchar2(20))
    --1、直接根据字段的数据类型声明表量
        p_pname1 varchar2(20);
    --2、引用student表对应字段的数据量类型
        p_pname2 STUDENT.pname%TYPE;
    --3、引用数据库对应行的数据作为类型
        p_student STUDENT%ROWTYPE; 
    --4、用户自定义记录类型
        TYPE p_stu_record_type IS RECORD(
            id STUDENT.pname%TYPE,
            name STUDENT.pname%TYPE,
            cname STUDENT.CNAME%TYPE ,
            c_count number (not null);
        );
        --记录可以直接赋值。RECORD1 :=RECORD2； 
        --记录不可以整体比较. 
        --记录不可以整体判断为空
        p_stu_rec p_stu_record_type;//自定义记录类型变量
    --5、基于游标的数据类型
        cursor stu_cur is select id,name from STUDENT;
        stu_row stu_cur%rowtype;
BEGIN 
    STATEMENT1;
END;

游标

    DECLARE
    --STUDENT 表（id number(10),pname varchar2(20),cname varchar2(20))
    cursor stu_name_cur is select name from STUDENT;--声明
BEGIN 
    --1、方式一
    open stu_name_cur;--打开
    --游标属性
        --cusor_nmae%found;
        --cusor_nmae%notfound;
        --cusor_nmae%isopen;（特别注意在代码块中打开游标后，exception有对应游标是否打开的判断，避免出现异常情况下没有关闭游标）
        --cusor_nmae%rowcount;
    loop   --遍历
        fetch stu_name_cur into p_pname2;
        exit when stu_name_cur%notfound;
        dbms_output.put_line(p_pname2);
    end loop;
    close stu_name_cur;--关闭(exception要有对应得关闭游标的语句)
    
    --2、方式二FOR 循坏：自动打开、检索、关闭游标
    FOR REC IN stu_name_cur LOOP
        dbms_output.put_line(REC.name);
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN 
        IF cusor_nmae%isopen THEN 
            CLOSE cusor_nmae;
        END IF;
END;

高级游标
1. 游标中使用参数
2. 复杂的嵌套游标
3. FOR UPDATE 和 WHERE CURRENT OF CURSOR_NAME:对游标执行更新操作

    --声明游标
    CURSOR stu_cur_row(ID_COUNT STUDENT.ID%TYPE) is 
        select ID,NAME,CNAME from STUDENT
    WHERE ID<ID_COUNT;
    --使用游标
    OPEN STU_CUR_ROW(100);
    --或者
    FOR REC IN STU_CUR_ROW(100);

游标与集合

联合数组Associative array（正式说法是索引表）后跟index by binary_integer：

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY key_type;  
type_name：用户自定义数据类型的名字  
element_type：索引表中元素类型  
key_type：索引表元素下标的数据类型（BINARY_INTEGER，PLS_INTEGER,VARCHAR2）

嵌套表（Nested Table）

区别于索引表就是无index by (binary_integer),下标为正整数
使用CREATE TYPE在表列中使用嵌套表，可以实现一对多

变长数组（VARRAY）
TYPE type_name IS VARRAY(size_limit) OF element_type[NOT NULL];
下标1开始，先用构造方法初始化。如果你需要限制集合元素的个数，应该选择VARRAY
多维数组/集合

TYPE varr_type is varray(10) of varchar2(30); 
TYPE varr_type2 is varray(10) of varr_type;
TYPE VARR_TYPE3 IS TABLE OF varr_type;

Associative array只能在PL/SQL块(或Package)中进行定义并使用(即适用范围是PL/SQL Block级别)
Nested table与VARRAY则可以使用CREATE TYPE进行创建(即适用范围是Schema 级别)

DECLARE
    --集合
    --联合数据
    TYPE P_STUDENT_ARRAY_type IS TABLE OF STUDENT.PNAME%TYPE 
    index by binary_integer;
    P_STUDENT_ARRAY P_STUDENT_ARRAY_TYPE;
    --嵌套表
    TYPE P_STUDENT_TABLE_TYEP IS TABLE OF STUDENT.PNAME%ROWTYPE;--nested
    P_STUDENT_TABLE P_STUDENT_TABLE_TYEP :=P_STUDENT_TABLE_TYEP();
    --必须初始化(也可以带值)
    --如果只声明类型比如：P_STUDENT_TABLE P_STUDENT_TABLE_TYEP;
    --P_STUDENT_TABLE被设置为null，无法赋值或引用
    --表列中使用内嵌表
    create type nest_tab_type is table of varchar2(30);     
    create table test_nest_tab(    
        id int,    
        vals nest_tab_type --使用    
    ) nested table vals store as    nest_tab; 
    --vals字段用嵌套表存储，表明nest_tab
    insert into test_nest_tab values
    (1,nest_tab_type('one','two','three','four')); 
    
    
BEGIN 
    Select * into v_id,v_tab from test_nest_tab where id=1; 
    STATEMENT1;
END;

集合常用操作

exists(index) 索引处的元素是否存在  
count 当前集合中的元素总个数(非null) 
limit 集合元素索引的最大值  
  索引表和嵌套表是不限个数的，所以返回null，变长数组返回定义时的最大索引  
first  返回集合第一个元素索引  
last  返回集合最后一个元素索引  
prior 当前元素的前一个  
next 当前元素的后一个  

extend 扩展集合的容量，增加元素 只是用于嵌套表和varry类型  
   x.extend 增加一个null元素  
   x.extend(n) 增加n个null元素  
   x.extend(n,i) 增加n个元素，元素值与第i个元素相同  
trim 从集合的尾部删除元素 只用于NEST TABLE和VARRY类型:裁剪大小，不保留占位符
trim 从集合尾部删除一个元素  
trim(n) 从集合尾部删除n个元素  
delete 按索引删除集合元素:保留占位符
   delete 删除所有  
   delete(index) 删除第index个  
   delete(a,b) 删除a--b之间的所有元素

控制文件

OPTIONS (skip=1,rows=128) -- sqlldr 命令显示的 选项可以写到这里边来,skip=1 用来跳过数据中的第一行  
LOAD DATA  
INFILE "users_data.csv" --指定外部数据文件，可以写多 个 INFILE "another_data_file.csv" 指定多个数据文件  
--这里还可以使 用 BADFILE、DISCARDFILE 来指定坏数据和丢弃数据的文件，  
truncate --操作类型，用 truncate table 来清除表中原有 记录  
INTO TABLE users -- 要插入记录的表  
Fields terminated by "," -- 数据中每行记录用 "," 分隔  
Optionally enclosed by '"' -- 数据中每个字段用 '"' 框起，比如字段中有 "," 分隔符时  ，异常情况（连续两个tab键（tab为分隔符），需去掉这部分，才能识别tab之间为空字段）
trailing nullcols --表的字段没有对应的值时允 许为空  
(  
  virtual_column FILLER, --这是一个虚拟字段，用来跳 过由 PL/SQL Developer 生成的第一列序号  
  user_id number, --字段可以指定类型，否则认 为是 CHARACTER 类型, log 文件中有显示  
  user_name,  
  login_times,  
  last_login DATE "YYYY-MM-DD HH24:MI:SS" -- 指定接受日期的格式，相当用 to_date() 函数转换  
)

常用函数
1. COALESCE(EXP1,EXP2…EXPN)：返回第一个非null表达式，全为null返回null
2. NULLIF(EXP1,EXP2)：EXP1和2值相等时返回NULL，否则返回表达式1
3. substr() 函数格式 (俗称：字符截取函数)：
  1. 格式1： substr(string str, int 起点(0、1都是第一位), int 长度);
  2. 格式2：substr(string str, int 起点)
4. DECODE():
5. sys_guid() :生产32位的随机数，不过中间包括一些大写的英文字母
6. select * from table(dbms_xplan.display());
常用系统表
1. user_sequences：系统中已创建的序列

常见问题

1、DDL锁
select o.owner,o.object_name,s.sid,s.serial#,s.USERNAME,s.STATUS,s.machine,s.PROGRAM,'alter system kill session ' || chr(39) || to_char(s.SID) || ',' || to_char( s.SERIAL# ) || chr(39) || ';' as cmd
from v$locked_object l,dba_objects o ,v$session s
where l.object_id　=　o.object_id
  and l.session_id=s.sid   --and s.STATUS = 'ACTIVE'
union
  SELECT DISTINCT "SYS"."DBA_DDL_LOCKS"."OWNER",
         "SYS"."DBA_DDL_LOCKS"."NAME",
         "SYS"."V_$SESSION"."SID",
         "SYS"."V_$SESSION"."SERIAL#",
         "SYS"."V_$SESSION"."USERNAME",
         "SYS"."V_$SESSION"."STATUS",
         "SYS"."V_$SESSION"."MACHINE",
         "SYS"."V_$SESSION"."PROGRAM",'alter system kill session '|| chr(39) || to_char("SYS"."V_$SESSION"."SID") || ',' || to_char( "SYS"."V_$SESSION"."SERIAL#" )  || chr(39) || ';' as cmd
    FROM "SYS"."DBA_DDL_LOCKS","SYS"."V_$SESSION"
   WHERE ( "SYS"."DBA_DDL_LOCKS"."SESSION_ID" = "SYS"."V_$SESSION"."SID" )
       AND    ( SYS."V_$SESSION"."STATUS" = 'ACTIVE' )
2、找到被锁定的表
SELECT object_name, machine, s.sid, s.serial#
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid;
3、查询行锁语句
select sql_text from v$sql a,v$session b
where a.sql_id=b.sql_id and b.event='enq: TX - row lock contention';
4、cmd sqlplus乱码
set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
echo %NLS_LANG%SIMPLIFIED CHINESE_CHINA.ZHS16GBK
sqlldr 导数据乱码set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
5、sqlldr登陆不上密码特殊符号：
当Oracle数据库用户的密码含特殊字符如@时，直接使用正常的密码输入，由于oracle将@后的字符解析为网络服务名而导致登陆失败 
linux：'wang/"oracle@1"'@sun --1个双引号扩密码，1个单引号扩 用户名+密码，即： '用户名/"密码"'@服务名 
Windows 平台：wang/"""oracle@1"""@sun--3个双引号扩密码，即： 用户名/"""密码"""@服务名
使用sqlldr userid=账号/"""密码"""@IP:端口/数据库
：https://www.jb51.net/article/32206.htm
6、sum()配合使用group by 和having使用问题
select sum(pay_money)from order
group by 某个字段;
//该语句在没有记录，无返回值

select sum(pay_money) from order;
//该语句在无记录下，也会返回null值
//需加上 having count(1)>0
//区别就是做报表是insert 表名 selct 上面的求和语句，会导致报表多了一行null记录
sql语句执行顺序，
1、where筛选满足条件的行
2、group by 按条件分组
3、having 选取满足条件的分组
4、执行聚合函数
5、select

理解两者区别：百度无结果只能意淫一下
虽然两者看似都是对空集合做sum运算但是是有区别的
group by后的null值其实就是不存在的意思，
而直接对order表还是一个空集的概念。
day  pay_amount
20   100
21   200
select sum(pay_money)from order;
select sum(pay_money)from order group by day;
返回都是
day  pay_amount
20   100
21   200
而不会返回
day  pay_amount
20   100
21   200
22   null
这个22 ,null就是一个不存在的概念。当原表或者where条件后的集合为null，则group by后的集合不存在，所有也就无法进行sum运算，无记录返回。对原表做sum运算，sum(null)结果返回null

sqlldr

sqlldr 装载换行符(使用str属性)

select utl_raw.cast_to_raw(chr(13) || chr(10)) from dual;
windows换行符:是两个ASCII，ASCII值分别是CR(13)  和LF(10)
infile xx.dat "str X'0D0A'"  --指定文件的换行符号
linux换行符是一个ASCII,ASCII值是LF(10)
infile xx.dat "str X'0A'"

https://wenku.baidu.com/view/63737c19cc7931b765ce1517.html?sxts=1571724825233
http://blog.itpub.net/29840459/viewspace-1673390/
http://www.360doc.com/content/16/1108/09/37466175_604804409.shtml
2. sqlldr 导入乱码，Oracle客户端字符集问题
https://blog.csdn.net/jly_ang/article/details/51274072