脚本开发

编写自己的数据库执行代码

1.PL(procedural language)/SQL的概念——是Oracle对关系型数据库的标准语言SQL的过程化语言扩展。

    1.1：应用于服务器的数据库触发器；客户端……；浏览器的应用程序。

    1.2：PL/SQL程序的最基本组成单元是PL/SQL块；块分为匿名块和命名块【子程序(过程+函数)+包+触发器】。

             *子程序实现模块化

             *子程序实现可重用性

             *子程序易于维护

             *子程序性能更高

             *子程序的提高开发速度

    1.3：匿名块在客户端；命名块在服务器端。

    1.4：匿名块调用方便，随写随用；不能重用。

 

2.运行机制——PL/SQL引擎+SQL引擎

    2.1：PL/SQL块中的PL/SQL语句交给PL/SQL引擎处理；SQL语句交给始终在服务器端的SQL引擎处理。

    2.2：匿名块在交给引擎处理时编译，命名快在创建、变更时编译。

    2.3：编译包括——语法检查+绑定(分配内存)+伪代码生成

    2.4：伪代码——存储在数据库中；对PL/SQL引擎的指令列表；编译成功命名快PL/SQL的状态设置为valid；

 

3.特点与好处

    3.1：特点——变量定义；控制结构；数据封装；异常处理；信息隐藏；面向对象；自定义子程序。

    3.2：好处

           3.2.1：提高应用程序性能；

           3.2.2：模块化程序开发能力——面向对象；

           3.2.3：良好的兼容性；

           3.2.4：允许自定义标示符——变量定义；

           3.2.5：提供程序控制结构——控制结构

           3.2.6：异常处理

 

4.常用指令

    4.1：dbms_output.put_line()【执行部分内部】+set serveroutput on【执行部分前面】——将信息在数据库管理系统那个里以一行输出;必须将环境变量serveroutput打开。

            *put_line()——占位一行；直接输出

            *put（）——不换行；输出到缓冲区，遇见put_line时输出显示。

    4.2：sqlplus username/password @server——在DOS下启动工具SQL*PLUS

    4.3：/(脚本结束)；execute；call——执行脚本。

    4.4：show errors——显示错误信息(原因+位置)

    4.5：describe  表名——显示表结构信息

    4.6：&——替代变量

    4.7：sql*plus:var<—>print;procedure builder:create<—>text_to;pro*c/c++:——跨平台

 

5.PL/SQL语法

    5.1：块结构模板

          <<块名称>>——匿名块无该行

          declare——1.申明部分

          ……定义变量、常量、复杂数据类型、游标、例解……

         begin——2.执行部分（不可缺少的）

         ……SQL和PL/SQL语句……

         exception——3.异常部分

         ……处理运行错误……

         end；——块结束

   5.2：过程——修改(DDL)

             5.2.1：格式——create procedure 过程名(形参列表)

                                    is/as

                                    …PL/SQL块…

             5.2.2：形参

                            *in——只能接受外来的实参，不能在子程序内部赋值。

                            *out—— 能在子程序内部得到值，不能将值传给外部变量。

                            *in out——in和out的联姻。

                                       

             5.2.3：调用

                      5.2.3.1：PL/SQL块中——过程名(实参);

                      5.2.3.2：SQL命令中——call/execute 过程名

                                *call是执行方法所以无论有没参数都需要()，execute是命令。

                                *调用方式必须用变量来接受输出变量——变量用做实参传入子程序。

   5.3：函数——用于计算，提供返回值；可直接用于SQL语句(未修改数据的)。

            5.3.1：格式——create function 函数名(形参列表)

                                     return 返回数据类型 is 返回变量申明（变量名 数据类型）；——返回子句

                                      …PL/SQL块…——函数体【必须包含return语句返回数据】 

            5.2.2：形参

                         *指定默认值的 in型 形参后不能有未指定值的in型变量。

   5.4：包——用于逻辑组合相关的过程和函数，管理过程和函数；由包规范和包体2部分组成

           5.4.1：格式

                          *包规范（申明）——create package 包名 is

                                                       …定义公用的变量、常量、过程、函数…

                                                      end；

                          *包体（实现）——createpackage body 包名 is

                                                   …过程、函数的实现…

                                                   end；

           5.4.2：调用方法——方案名.包名.子程序名

   5.5：触发器——隐式的存储过程；由触发事件(执行DDL)和触发操作组成。

          5.5.1：格式——create trigger 触发器名

                                 …触发事件=增、删、改…

                                begin

                                  …触发操作=PL/SQL块…

                                 end；

  5.6：数据类型

 

     5.6.2:数据类型=标准类型(单个值)+复合类型(多个值)+参考类型(指针)+大数据类型

     5.6.3：CLOB，NCLOB，BLOB都是内部的LOB(Large Object)类型，最长4G；LONG只能有一列的限制

5.7：常量和变量

        5.7.1：定义的格式

                  5.7.1.1：标量变量——引用名  constant(常量才有)   数据类型  not null(强制初始化)   ：=/default   初始值(表达式等) 

                  5.7.1.2：复合变量——一次性将某表的一条/所有记录读到内存中。

                            5.7.1.2.1：记录——type 实例化复合类型 is record(列定义的列表)；

                                                         变量名   实例化复合类型；

                                          *引用名 emp(表或视图)%rowtype——成员的个数、名称、类型需要一致。

                            5.7.1.2.2：PL/SQL表——type 实例化复合类型 is table of  ？？index by binary_integer/pls_integer(索引类型); 

                                                                 引用名 实例化复合类型；

                                          *pls_integer效率更高，会溢出并报异常。

                                          *PL/SQL表——相当于map集合；小标可以为负值、没有上下限；元素个数没有限制；高效准确的取值

                            5.7.1.2.3：嵌套表和varray——create or replace type 实例化复合类型1 as object(列定义的列表)

                                                                         create or replace type 实例化复合类型 is table of 实例化复合类型1;                               

                                          *Oracle特有，不做研究；下标不能为负值。

                  5.7.1.3：参考变量——参考变量里存放的是指针；通过共享对象，降低内存占有率。

                              5.7.1.3.1:游标——  type 引用名 is REF CURSOR；

                                 *定义游标时与selecte语句绑定称为静态

                                 *打开游标时与select语句绑定称为动态——open emp_cursor for select  ename,sal from emp where deptno=10;

                               5.7.1.3.2：对象类型——create or replace type  引用类型 as object(列定义的列表)；

                                                                  create table 引用名 of 引用类型；

                  5.7.1.4：

        5.7.2：引用名 emp.ename(数据库的列名)/v_sal(变量名)%type ——根据数据库的列或其他变量来确定新变量的数据类型和长度。

        5.7.3：log变量=内部(存于数据库【blog(二进制)+clog(字符)+nclog(utf-8字符)】)+外部(存于OS【bfile】)。

   5.8：词汇单元

         5.8.1：分隔符——特殊含义的字符

                    *四则运算符、关系运算符；幂操作——**

                    *属性提示符——%；字符串分隔符—— ；组件分隔符—— ；非PL/SQL变量提示符——：；项分割符—— ；变量分隔符——“；远程数据库访问——@

                    *赋值——：=  ；关联操作——->；  串连接——||；标号——《》；

 

SQL> select ename||' ia a '||job as"employee detail"from emp;

employee detail
-------------------------
SMITH ia a CLERK
ALLEN ia a SALESMAN
WARD ia a SALESMAN
JONES ia a MANAGER
MARTIN ia a SALESMAN
BLAKE ia a MANAGER

         5.8.2：标识符——每行只能定义一个标示符；只能用字母、数字、_#、$组成，字母开头；其它情况用”“包围；最长为30字符。

         5.8.3：字面量

         5.8.4：注释——单行——--……；多行/*……*/       

    5.9：控制语句

             5.9.1：顺序

                    5.9.1.1：null语句——占位，提高数据可读性。

 

             5.9.2：条件

                    5.9.2.1：if;if-else；if-elsif

 

                    5.9.2.2：case——多重分支

                                    case 选择符

                                            when 表达式 then 执行语句；

                                            ……

                                            when 表达式n then 执行语句n；

                                             else 执行语句；

                                    end case;

create table emp2
as
select empno,ename,deptno,sal,
       (case 
        when sal>2000 then '男'
        else '女'
        end) sex
from emp;

 

                    5.9.2.3：decode函数

                               *map也具有分支语句的功能。                   

 

             5.9.3：循环

                    5.9.3.1:loop——先执行，后判断。

                                      create table temp(col int);

                                      declare

                                             i int:=1;

                                      begin

                                             loop

                                                   insert into temp values(i);

                                                   exit when i=10;--循环控制

                                                   i:=i+1;--修改循环控制变量

                                             end loop;

                                     end;

                                     /

                    5.9..3.2：while——先判断后执行

                                      create table temp(col int);

                                      declare

                                             i int:=1;

                                      begin

                                            while i<=10 loop——TRUE的时候执行循环；FALSE、null跳过循环。

                                                   insert into temp values(i);

                                                   i:=i+1;--修改循环控制变量

                                             end loop;

                                     end;

                                     /

                    5.9..3.3：for

                                      create table temp(col int);

                                       begin

                                             for i in reverse start ..end loop——在数值start~end之间的时候循环；reverse表示i反序变化。

                                                    insert into temp values(i);

                                             end loop;

                                     end;

                                     /

 

 

     5.10：例外(异常)

              4.10.1：功能——控制事务；跳转；传值(利用异常传值的业务流程不同、事务回滚、)。

              4.10.2：分类

                    4.10.2.1：预编译例外(隐式)——处理常见oracle例外

               ORA-06530——ACCESS_INTO_NULL                   ORA-06592——CASE_NOT_FOUND

               ORA-06531——COLLECTION_IS_NULL               1

               ORA-06511——CURSOR_ALREADY_OPEN               ORA-01001——INVALID_CURSOR

               ORA-00001——DUP_VAL_ON_INDEX

               ORA-01722——INVALID_NUMBER

               ORA-01403——NO_DATA_FOUND               ORA-06502——VALUE_ERROR

               ORA-014722——TOO_MANY_ROWS

               PRA-01476——ZERO_DIVIDE

                    4.10.2.2：非预编译例外(隐式)——处理预编译例外不能处理的oracle错误。例如：约束错误。

                               *DECLARE

                                        e_integrity EXCEPTION;——1.申明例外

                                        PRAGMA EXCEPTION_INIT(e_integrity,错误编号);——2.关联例外和错误

                                 EXCEPTION

                                        WHEN e_integrity THEN——3.引用

                                        dbms_output.put_line('该雇员不存在');

                                *错误编号范围——不小于-1000000的非-1403负数；0；100

                    4.10.2.3：自定义例外(显式)——处理非oracle错误

                               *DECLARE

                                         e_no_employee EXCEPTION;——1.申明例外

                                 BEGIN

                                        IF SQL%NOTFOUND THEN

                                                RAISE e_no_employee;——2.显示触发例外

                                        END IF;

                                 EXCEPTION

                                        WHEN e_no_employee THEN——3.引用

                                        dbms_output.put_line('该雇员不存在');

              4.10.3：处理方式

                    4.10.3.1：抛出：传递例外(中止)——将例外传给调用环境

                    4.10.3.2：(终止)捕获并处理——exception

                                                                       when 例外的编码常量 then

                                                                        需要执行的语句；

                                                                        ……

                                                                        when others then——捕获未预计到的所有异常

                                                                        需要执行的语句；

              4.10.4：例外函数

                     4.10.1：RAISE_APPLICATION_ERROR(错误编码,错误信息,Boolean)——在(函数、包、过程、触发器)数据库端子程序中自定义错误号和错误信息。

                                   *错误编号——在-20000~-20999

                                   *错误信息——最多2048字节

                                   *Boolean——TRUE则追加，FALSE则覆盖默认。

 

                     4.10.2：SQLCODE——获得oracle错误的编号

 

                     4.10.3：SQLERRM——获得oracle错误的信息

 

declare 
begin
DBMS_OUTPUT.PUT_LINE( sqlerrm(-1438));
end; 

 

 

6.编程规范

    6.1：标示符

            *变量：v_为前缀；常量：c_为前缀；游标：_cursor为后缀；例外：e_为前缀；

            *复合变量：_复合变量类型为后缀；复合变量类型：_复合变量类型_type为后缀；

            *

            *

 

    6.2：大小写

          *大写——SQL、PL/SQL关键字、数据类型

          *小写——标示符，参数，数据库对象

  

 

    6.3：代码缩进——阶梯

    6.4：注释