oracle数据库----笔记1---PL/SQL基础1

PL／SQL提供了分支结构，循环结构等,可以创建过程和函数，以加速模块代码的开发

PL／SQL程序中可执行SQL语句，SQL语句中可使用PL／SQL函数，使SQL 的数据操纵功能与过程语言数据处理功能结合起来。

PL／SQL是模块化编程，将一组SQL语句作 
 为一块集中处理，减少应用对ORACLER 调用

不使用PL／SQL，ORACLE每次只处理一个SQL 
 语句, 每个SQL语句导致对ORACLE的调用,尤其是在网络情况下,这种开销变得很重要。
用户很多或频繁的SQL语句调用,可能会引起网络超负荷运行产生数据阻塞

对库的复杂操作置于块内，实现块移植，只需考虑块间数据交换关系

将一些公用的处理定义成内嵌函数,过程或程序
包,与系统集成。

PL／SQL的基本结构:

PL／SQL是一种块结构语言，即构成一个PL／SQL
   
程序的基本单位(过程、函数和无名块）是逻辑块。
程序块可以嵌套，该程序结构支持自上而下逐步求精的方法解决问题。
一个块（或子块）将逻辑上相关的说明和语句组合在一起。

DECLARE
            
．．．        －－ 说明
            
BEGIN
            
．．．        －－ 语句序列
            
EXCEPTION
            
．．．        －－ 例外处理程序    
            
END；

说明部分：
    
以DECLARE关键字开始，在此可对当前块执行部分所用
变量、常量、光标，异常处理名进行说明。还可说明记录和 PL/SQL表。

执行部分：
    
以BEGIN开始，是当前块运行时被执行的代码，  
    
可使用SQL的DML语句，事务控制语句，PL／SQL
的控制结构，
如：条件控制、循环控制及顺序控制
构造，
在PL/SQL中引入光标结构，利用它命名一专用的SQL工作区，可存取它所存储的信息。


例外处理部分：(异常处理）
异常处理部分以EXCEPTION关键字开始，处理执行过程中发生的异常。
仅当发生了错误时才执行异常部分的代码。
ORACLE提供了一些系统错误，用户可以自己定义例外。对未做处理的异常会导致

    PL／SQL程序块非正常终止

块结束：END；

DECLARE
   X number(3);
BEGIN
   X:=3;
EXCPTION
   WHEN OTHERS THEN NULL;
END;

在PL／SQL中，使用的操作符:

算术运算符：＋，－，* ，／， **
关系运算符：＝，< ，> ，!= （或< >）,>＝，<＝
 IN，IS NULL，LIKE，BETWEEN

另外用
 （关系符号），
．．（范围运算符），
||字符串连接符（并置），
:＝ （赋值运算符）

注释：
 
 ―― 用于单行注释，从“――“开始，至本行结束。
／* … *／  用于多行注释，可跨多行，PL／SQL编译器忽略注释内容。

运算符优先级 
 ** ，NOT，（＋，－ 单目运算），*，／，＋，－，|| ,关系运算符,AND,OR,

例1：
  
向 EMP  表插入一条记录，雇员号是8000，雇员名是 
‘WUCHEN’, 
其他字段值同‘SMITH’,然后对所有雇
 员工资增加$500。
 
 用一个PL/SQL 块实现。

DECLARE 
   myrecord emp%ROWTYPE;
   myempno number(4) not null:=8000;
   myename emp.ename%TYPE;
   addsal CONSTANT number(4):=500;
BEGIN
   select * into myrecord
   from emp
   where ename='SMITH';
   myename:='WUCHEN';
   insert into emp(empno,ename,sal,comm,job,hiredate,deptno)
   values(myempno,myename,myrecord.sal,myrecord.comm,myrecord.job,myrecord.hiredate,myrecord.deptno)l
   update emp set sal=sal+addsal;
   commit;
END;

PL/SQL 过程已成功完成。

DECLARE
   Order_no number(3);
   Cust_name  varchar2(20);
   Order_date  date;
   Emp_no integer:=25; - -default value of 25
   PI CONSTANT  number:=3.1416;
  
BEGIN
   NULL;
END;

DECLARE
    
   标识符[CONSTANT]数据类型[NOT  NULL] [:=缺省值或PL／SQL表达式]

BEGIN
   
   在PL／SQL中，用标识符命名程序对象和单元,但不能与保留字相同,标识符的命名同SQL.
   对说明的一个常量和变量都有其数据类型.
   一般标识符是以字母开头字母数字串,其长度不超过30个字符,大小写均可
注意：

   1. 定义常量，必须有保留字CONSTANT 作标志，先于类型说明符，必须赋初值。

   2. 若说明的变量不许为空，必须有NOT NULL 约束且必须其后跟有初始化子句。 
  
   3. 可利用保留字DEFAULT 代替赋值操作符（：=）初始化变量或常量。
   4. 每行只能定义一个变量，用分号(;)表示结束。

1．简单数据类型:
number：  数值型 
  
   其中: integer 或 number(n)，int 整数子类型
         
         real 或 number(m ,n) 实数子类型
boolean:  布尔型 
   该类型变量可存储3种值 true,false, null。
   仅可将三种值赋给一个布尔变量.但不能将true，false布尔常量值插入到数据库表的一列中,
   也不能从数据库表的一列中选择或获取列值到 BOOLEAN 变量

char   字符型      varchar2   可变长字符型

date   日期型

DECLARE
    emp_count integer:=0;
    acct_id varchar2(5) NOT NULL:=’APOO1’;

    today  date :=sysdate;
    PI  CONSTANT real:=3.14159;
    redius  real:=1;
    area real:=PI*redius**2;
    birthdate  date;

    valid  boolean  DEFAULT  FALSE;
BEGIN

    NULL;
END;


2.%TYPE  %ROWTYYPE

在说明变量类型时,可利用%TYPE属性提供一个现有变量,
常量或数据库表列的数据类型,来定义一个简单变量类型.

cred number(7,2);
dred cred%TYPE;

my_dname [scott.]dept.dname%type;
说明形式：变量名  基表名.列名%TYPE;

使用%TYPE定义变量的优点:
(1）不需知道数据库表列的真正数据类型
(2）当数据库表列定义改变时，在运行时自动地修改变量的数据类型

 使用%TYPE 定义变量的数据类型后，对变量不能
   使用 NOT  NULL约束

3.组合数据类型

简单类型提供一组单值的集合,每个值不可再分解,而组合型数据是由若干个简单型的元素构成
在PL／SQL中，组合类型有记录和表

记录组合型变量说明:

(1）  用％ROWTYPE属性说明一个记录类型变量,用来表示 一个表（或视图）中的一行，
      该组合记录型变量可存储由表中所选择的一整行或者由一游标所获取的一
 整行。


ey:emp_rec emp%rowtype;
说明形式是：
  变量名   表名％ROWTYPE；

定义的变量中的成员与表的记录行中列有相同的名字和数据类型，但该定义不能包含初始化子句。

select  * INTO  emp_rec  from  emp
  where   ename=’SMITH’;

引用组合变量中的成员：

记录变量名.列名    - - 数据库表的列名

emp_rec.ename:=’JOHNSON’; - - 赋常量值
emp_rec.sal:=1.15*emp_rec .sal;

组合变量是用％ROWTYPE属性说明,其分量要用点记法
表示,进行访问,存取或赋值。

对组合记录变量全部成员的赋值：

当两个记录型变量的定义是使用同一表名（或同 一光标）则可以互相赋值。
DECLARE
  
     dept_rec1 dept%ROWTYPE;
     dept_rec2 dept%ROWTYPE;
  
     …                                  
    
则:  dept_rec2:=dept_rec1; 

通过查询给记录变量赋值:

sql>Select  deptno, dname, loc
 
    INTO  dept_rec2
 
    from  dept
 
    where   deptno=30;

注意：

1.不能将一组列值一次赋给一个记录变量。

     记录变量名：=（列值1，列值2，…）

2.不能将整个记录插入到数据库的基表中，
     
如： insert  into  dept  values (dept_rec2)    %错误


（2）用户定义记录

使用％ROWTYPE属性定义一个记录，该记录变量可表示表中的一行或由光标所获取的行。
这样不必为存放表中各列值而为每个字段定义一个临时变量；也不必一定要知道数据库表中列的个数及类型，
变量可随表列的定义改变而自动改变。

但是在该记录变量中不能指定字段的数据类
型或定义自己的字段名。用户可利用TYPE  RECORD实现

用户定义记录分两步说明:
    

 a.   定义一记录（RECORD）类型
   
 b.  用定义的记录类型说明记录变量

记录类型定义形式：
  TYPE  类型名    IS  RECORD
 
   （字段名1{字段类型| 变量名%TYPE | 表名．列名％TYPE
 | 表名%ROWTYPE}
    [NOT  NULL],…）；

DECLARE
 
    TYPE  deprectyp  is  RECORD 
    (depno  number(4)  NOT  NULL:=20,

     dnam   dept.dname%TYPE，
     loc    dept.loc%type) ;

说明记录变量：

dept_rec1   deprectyp;
  
dept_rec2   deprectyp;


引用记录：

记录的值可用查询插入或同一记录类型变量可互相赋值

引用记录中的单个字段，表示法：
   记录名．字段名    可将LP/SQL表达式的值赋给指定字段
   记录名．字段名：＝＜PL／SQL表达式＞


(3)    定义PL／SQL表：

 表是PL／SQL中另一种用户定义的组合数据类型

PL/SQL 表与 C 语言中的一维数组类似

表类型 定义形式：

TYPE    类型名   IS    TABLE  OF
 
  {列类型 |变量名%TYPE |表名.列名％TYPE| }  [NOT  NULL]
   INDEX  BY  BINARY_INTEGER;

在花括号中指出列可以选用的数据类型,即表中元素类型

可为任何简单类型, 创建表时需要指出子句
 

INDEX   BY   BINARY_INTEGER

 ps:因为BINARY_INTEGER 
是当前支持的唯一索引类型。



PL／SQL表只能有一列因此与一维数组非常类似

但PL/SQL
表的大小无约束, 即表中的行数可动态地增加

 PL/SQL表由一列和一个主码组成,它们不能命名。列可为任
 何简单类型, 其主码必须为BINARY_INTEGER类型

主码用于存储有正、负号的整数类型(－231-1 ～ 231-1 )以带正
负号的二进制数表示。

DECLARE
 
   TYPE  enametabtyp  IS  TABLE   OF
   emp.ename% type
  
   INDEX  BY  BINARY_INTEGER;


说明 PL/SQL 表： <LP/SQL表名>    <表类型名>
  
例：  ename_tab  enametabtyp ;

利用主码值可引用PL/SQL表中的一行, 其格式为:

   plsql表名（主码值）


引用PL／SQL表  ename_tab的第三行可表示为:

      ename_tab(3)

plsql表名（主码值）：＝plsql表达式；
例如：sal_tab(5):=sal +常量; 

可利用循环给表中的每行赋值。对于同一类型的表可以相互 
 赋值 

declare
    type  numtabtyp  IS  TABLE  OF number
  index   by  binary_integer ;
 
    sal_tab  numtabtyp;
    empty_tab  numtabtyp;

set serveroutput on

declare

  type bb is table of emp%rowtype index by binary_integer;

  bbb bb;

Begin
 
  select * bulk collect into bbb from emp ;

  for i in 1..bbb.count loop

  dbms_output.put_line(bbb(i).empno);

  end loop;

end;

bulk colleck用法：

通过bulk collect减少loop处理的开销
采用bulk collect可以将查询结果一次性地加载到collections中
而不是通过cursor一条一条地处理

可以在select into,fetch into,returning into语句使用bulkcollect。

注意在使用bulk collect时，所有的into变量都必须是collections. 

在select into语句中使用bulk collect：

DECLARE
TYPE SalList IS TABLE OF emp.sal%TYPE;
sals SalList;
BEGIN
-- Limit the number of rows to 100.
SELECT sal BULK COLLECT INTO sals FROM emp
WHERE ROWNUM <= 100;
-- Retrieve 10% (approximately) of the rows in the table.
SELECT sal BULK COLLECT INTO sals FROM emp SAMPLE 10;
END;

/--在fetch into中使用bulk collectDECLARE
TYPE DeptRecTab IS TABLE OF dept%ROWTYPE;
dept_recs DeptRecTab;
CURSOR c1 IS
SELECT deptno, dname, loc FROM dept WHERE deptno >10;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO dept_recs;
END;
/--在returning into中使用bulk collectCREATE TABLE emp2 AS SELECT * FROM employees;
DECLARE
TYPE NumList IS TABLE OF employees.employee_id%TYPE;
enums NumList;
TYPE NameList IS TABLE OF employees.last_name%TYPE;
names NameList;
BEGIN
DELETE FROM emp2 WHERE department_id = 30
RETURNING employee_id, last_name BULK COLLECT INTO enums,names;
dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN enums.FIRST .. enums.LAST
LOOP
dbms_output.put_line('Employee #' || enums(i) || ': ' ||names(i));
END LOOP;
END;
/


要删除表中某行时,就是将该行置空即可

ename_tab(3):=NULL;

这样当引用该行数据时, 不会出现无数据发现的例外

3.变量的输入和输出：

输入：
SQL＞ACCEPT  newdept  [number|char]  PROMPT  ‘dept:’

SQL> select  dname  from  dept    Where  deptno=&newdept

输出：

先用SQL*PLUS   VARIABLE  命令定义一个SQL*PLUS
   的全局变量,
在PL／SQL块中,需输出的值赋给全局变量。

例：SQL ＞VARIABLE    n    number
 
    SQL >   BEGIN 
            :n:=1;
               
            END;
 
    SQL >  PRINT    n
 


另外,在PL／SQL块中,可调用一个内嵌包,DBMS_OUTPUT 
 中的put_Line过程
要用SQL*PLUS的  SET  命令把系统变量 serveroutput 的值
 置ON, 该变量在SQL*PLUS中控制DBMS＿OUTPUT包的输出
 语句。


 SQL＞set  serveroutput  on  

调用dmbs_output.put_Line 过程产生输出结果在 SQL*PLUS
 环境中显示输出结果。 


SQL>BEGIN
  
DBMS_OUTPUT.PUT_LINE(‘output  from  the  put_line  function:’);
 END;


 将字符串参数传送到put_line过程中显示
若是显示其它类型数据,需用 TO_CHAR( )函数转换。

　　

转载于:https://www.cnblogs.com/wust221/archive/2013/05/08/PL-SQL.html