精通ORACLE PLSQL学习笔记

最新推荐文章于 2024-09-14 19:55:29 发布

cuanyileng9226

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量150

点赞数

文章标签：数据库

--建会员表
CREATE TABLE EMP
   ( ENO NUMBER primary key,
ENAME VARCHAR2(200),
DEPT VARCHAR2(200),
PAY NUMBER(6,2)
)

--建部门表
create table dept(deptno number not null,
                  deptname varchar2(100 char),
                  constraint dept_pk primary key (deptno)
)

declare v_ename varchar2(200 char);
      begin
      select ename into v_ename from emp where eno=&no;
      dbms_output.put_line('雇员名是：'||v_ename);
      EXCEPTION
             when no_data_found then
             dbms_output.put_line('请输入正确的雇员号');
      end;

--匿名块
declare avlpay number(6,2);
begin
        select avg(pay) into avlpay from emp where eno=&no;
        dbms_output.put_line('平均工资为：'||avlpay);
      EXCEPTION
        when no_data_found then
        dbms_output.put_line('请输入正确的员工号');

end;

--命名块
<>
declare
v_pay number(6,2);
v_eno number;
begin
<>
begin
select eno into v_eno from emp where ename=&name;
end;--<>
select pay into v_pay from emp where eno=v_eno;
dbms_output.put_line(vpay);
end;--<>

----------------------------------------------------------------------------------------------过程
create or replace procedure upemp(e_name varchar2,e_pay number)
is
begin
update emp set pay=e_pay where lower(ename)=lower(e_name);
end;

----------------------------------------------------------------------------------------------函数
create or replace function annual_income(name varchar2)
return number is
       aunual_salary number(6,2);
       begin
       select pay into aunual_salary from emp where ename=name;
       return aunual_salary;
       end;

----------------------------------------------------------------------------------------------包
--纯度级别：WNDS限制函数不能修改数据库数据；WNPS用于限制函数不能修改包变量；RNDS限制函数不能读取数据库数据；RNPS限制函数不能读取包变量
--语法：PRAGMA RESTRICT_REFERENCES(function_name,WNDS[,WNPS][,RNDS][,RNPS])
--包规范，只包含了过程和函数的说明
create package emp_pkg is
procedure upemp(e_name varchar2,e_pay number);
function     annual_income(name varchar2) return number;
--PRAGMA RESTRICT_REFERENCES(annual_income,WNPS);
end;
--包体，用于实现包规范中的过程和函数
create package body emp_pkg is
procedure upemp(e_name varchar2,e_pay number)
          is
          begin
            update emp set pay=e_pay where lower(ename)=lower(e_name);
          end;
function annual_income(name varchar2)
         return number is allpay number(6,2);
         begin
                select pay*12 into allpay from emp where lower(ename)=lower(name);
                return allpay;
         end;
end;

--------------------------------------------------------------------------------------------触发器
create or replace trigger update_cascade after update of deptno on dept for each row
begin
update emp set deptno=:new.deptno where deptno=:old.deptno;
end;
/*
-- 定义标量变量语法
--identifier [CONSTANT] datatype [NOT NULL] [:= |DEFAULT expr]
--constant用于指定常量，如 abc constant number(3,2):=5.5
--default用于指定默认值
--为减少工作量，使用%type属性
*/

------------------------------------------------------------------------------------------PLSQL记录
declare
type emp_record_type is record(
        name emp.ename%type,
        salary emp.pay%type,
        title emp.dept%type);
        emp_record emp_record_type;
     begin
        select ename,pay,dept into emp_record from emp where eno=0;
        dbms_output.put_line('名字是：'||emp_record.name);
        EXCEPTION
         when no_data_found then
         dbms_output.put_line('不存在的哦');
     end;

--------------------------------------------------------------------------------------PLSQL索引表
declare
         type ename_table_type is table of emp.ename%type index by binary_integer;
         ename_table ename_table_type;
         begin
               select ename into ename_table(-1) from emp where eno=7788;
               dbms_output.put_line('雇员名为：'||ename_table(-1));
               EXCEPTION
                      WHERE NO_DATA_FOUND THEN
                      dbms_output.put_line('此用户不存在');
         end;


-----------------------------------------------------------------------------------------嵌套表
--emp_type用于存储雇员信息
create or replace type emp_type as object(
        name varchar2(20),
        salary number(6,2),
        hiredate date
        );
--emp_array基于emp_type,用于存储多个雇员信息 is table of指定是嵌套表的
create or replace type emp_array is table of emp_type;

/*
* VARRAY集合（数组）使用,注意数组元素的最大个数是有限制的，且一定要使用其构造方法进行初始化语法如下：
* declare type ename_table_type is varray(20) of emp.ename%type;
*        ename_table ename_table_type:=ename_table_type('test');
*/
--plsql块中使用varray------------------------------------------------------------------------------
declare type ename_table_type is varray(20) of emp.ename%type;
        ename_table ename_table_type:=ename_table_type('test');
        begin
              select ename into ename_table(1) from emp where eno='1';
              dbms_output.put_line(ename_table(1));
              EXCEPTION
                     when no_data_found then
                     dbms_output.put_line('没数据得');
        end;

--在表列中使用VARRAY
create type phone_type is varray(20) of varchar2(20);
/
create table employee(
                       id number(4),
                       phone phone_type,
                       constraint EMPLOYEE_PK primary key (id)
)

--PLSQL记录表，PLSQL变量用于处理单行单列数据，PLSQL记录用于处理单行多列数据，PLSQL集合用于处理多行单列数据，PLSQL记录表用于处理多行多列数据
declare
       type emp_table_type is table of emp%rowtype index by binary_integer;
       emp_table emp_table_type;
       begin
             select * into emp_table(1) from emp where eno=1;
             dbms_output.put_line(emp_table(1).ename);
              EXCEPTION
                    WHEN NO_DATA_FOUND THEN
                    DBMS_OUTPUT.put_line('还是没球得数据');
       end;

--使用多级集合-------------------------------------------------------------------------二维数组使用
declare
       --定义一维集合
       type al_varray_type is varray(10) of int;
       --定义二维集合
       type nal_varray_type is varray(10) of al_varray_type;
       --初始化二维集合变量
       nvl nal_varray_type:=nal_varray_type(
                                             al_varray_type(1,2,3),
                                             al_varray_type(24,12),
                                             al_varray_type(4,3,5,6)
                                             );
        begin
              dbms_output.put_line('显示二维数组所有元素');
              for i in 1..nvl.count loop
                  for j in 1..nvl(i).count loop
                      dbms_output.put_line('nvl('||i||','||j||')='||nvl(i)(j));
                      end loop;
              end loop;
        end;

--使用多级嵌套表----------------------------------------------------------------------二维数组使用
declare
        --定义一维嵌套表
        type al_table_type is table of int;
        --定义二维嵌套表集合
        type nal_table_type is table of al_table_type;
        --初始化二维集合变量
        nvl nal_table_type:=nal_table_type(
                                           al_table_type(1,2),
                                           al_table_type(3,4)
                                           );
        begin
              dbms_output.put_line('显示二维数组所有元素');
              for i in 1..nvl.count loop
                  for j in 1..nvl(i).count loop
                       dbms_output.put_line('nvl('||i||','||j||')='||nvl(i)(j));
                   end loop;
                end loop;
         end;

----------------------------------------------------------------------------------------------------
/*
*     集合方法是ORACLE用于操纵集合变量的内置函数或过程
*     函数包括：EXISTS ,COUNT,LIMIT,FIRST,NEXT,PRIOR
*     过程包括：EXTEND,TRIM,DELETE
*     调用方法：collection_name.method_name[(parameters)]
*/
--EXISTS验证集合元素是否存在 ename_table.exists(1)
--COUNT返回集合变量中元素个数 ename_table.count
--LIMIT返回集合元素的最大个数 ename_table.limit
--FIRST返回集合变量第一个元素下标 ename_table.first
--LAST返回集合最后一个元素下标 ename_table.last
--PRIOR返回当前元素的前一个元素的下标 ename_table.prior(5)
--NEXT返回当前元素的后一个元素的下标 ename_table.next(5)
--EXTEND用于扩展集合变更的尺寸并增加元素。只适用于嵌套表和VARRAY，有三种格式EXTEND,EXTEND(n),EXTEND(n,i)   ename_table.extend(1,2)
--TRIM从集合尾部删除元素 ename_table.trim(2) ２为删除元素个数
--DELETE 用于删除集合元素，只适用于嵌套表和索引表   ename_table.delete(333) 删除指定元素

-------------------------------------------------------------------------------------------------------
--集合赋值注意集合类型要一致
-------------------------------------------------批量绑定，让你的速度飞起来----------------------------
/*
*      批量绑定的语法：
*      一，FORALL index IN lower_bound..uper_bound
*      二，FORALL index IN INDICES OF collection [BETWEEN lower_bound AND upper_bound]
*      三，FORALL index IN VALUES OF index_collection
*/

create table demo(
                    id number(6) primary key,
                    name varchar2(10)
                                );
--1,不使用批量绑定，耗时1.016秒
declare
        type id_table_type is table of number(6) index by binary_integer;
        type name_table_type is table of varchar2(10) index by binary_integer;
        id_table id_table_type;
        name_table name_table_type;
        start_time number(10);
        end_time   number(10);
        begin
                   for i in 1..5000
                       loop
                       id_table(i):=i;
                       name_table(i):='name'||to_char(i);
                       end loop;
                    start_time:=dbms_utility.get_time;
                    for i in 1..id_table.count
                          loop
                             insert into demo values(id_table(i),name_table(i));
                           end loop;
                           end_time:=dbms_utility.get_time;
                           dbms_output.put_line((end_time-start_time)/100) ;
         end;

--2,使用批量绑定,执行一条SQL语句插入５０００行数据,耗时0.047秒

    declare
        type id_table_type is table of number(6) index by binary_integer;
        type name_table_type is table of varchar2(10) index by binary_integer;
        id_table id_table_type;
        name_table name_table_type;
        start_time number(10);
        end_time   number(10);
        begin
                   for i in 1..5000
                       loop
                       id_table(i):=i;
                       name_table(i):='name'||to_char(i);
                       end loop;
                    start_time:=dbms_utility.get_time;
                    forall i in 1..id_table.count                       --此处使用批量绑定
                           insert into demo values(id_table(i),name_table(i));
                           end_time:=dbms_utility.get_time;
                           dbms_output.put_line((end_time-start_time)/100) ;
         end;


--------------------------------游标--------------------------------------------------------------------------
/*
*       游标属性：%ISOPEN %FOUND %NOTFOUND %ROWCOUNT
*       使用游标更新数据方法：UPDATE table_name SET column=.. WHERE CURRENT OF cursor_name;
*       使用游标删除数据方法: DELETE FROM table_name WHERE CURRENT OF cursor_name;
*/
--1,显示游标使用FETCH INTO 获取数据，每次只能获取一行
declare
         cursor emp_cursor is
                select ename,pay from emp where dept=10;
         v_ename emp.ename%type;
         v_sal   emp.pay%type;
         begin
              open emp_cursor;
              loop
                   fetch emp_cursor into v_ename,v_sal;
                   exit when emp_cursor%notfound;
                   dbms_output.put_line(v_ename||':'||v_sal);
              end loop;
              close emp_cursor;
          end;

--2,显示游标使用FETCH..BULK COLLECT INTO 一次获取所有数据

declare
         cursor emp_cursor is
                select ename from emp ;
         type ename_table_type is table of varchar2(10);
         ename_table ename_table_type;
         begin
              open emp_cursor;
              fetch emp_cursor bulk collect into ename_table;
              for i in 1..ename_table.count
                  loop
                   dbms_output.put_line(ename_table(i));
                   end loop;
               close emp_cursor;
         end;

--3，显示游标使用FETCH..BULK COLLECT INTO ..LIMIT一次获取部分数据
declare
         cursor emp_cursor is select ename from emp ;
         type name_array_type is varray(5) of varchar2(10);--此集合已限制最多只能装五条数据
         name_array name_array_type;
         rows int:=5;                                      --设定限制值为５
         v_count int:=0;
         begin
                 open emp_cursor;
                 loop
                      fetch emp_cursor bulk collect into name_array limit rows;
                      dbms_output.put('名字：');
                      for i in 1..(emp_cursor%rowcount-v_count)
                          loop
                            dbms_output.put(name_array(i)||',');
                          end loop;
                          dbms_output.new_line;
                          v_count:=emp_cursor%rowcount;
                          exit when emp_cursor%notfound;
                 end loop;
                 close emp_cursor;
          end;

--当游标中存放了多列数据时，还是用记录变量比较好，避免定义过多变量
declare
       cursor emp_cursor is select * from emp;
       emp_record emp_cursor%rowtype;
       begin
            open emp_cursor;
            loop
                 fetch emp_cursor into emp_record;
                 exit when emp_cursor%notfound;
                 dbms_output.put_line(emp_record.ename||':'||emp_record.pay);
            end loop;
            close emp_cursor;
       end;

--游标for循环
declare
       cursor emp_cursor is select * from emp;
       begin
              for emp_record in emp_cursor
                  loop
                      dbms_output.put_line('第'||emp_cursor%rowcount||'个人：'||emp_record.ename);
                  end loop;
       end;

--使用游标变量，在使用显示游标时，需要指定静态的select语句，而使用游标变量，可以在打开游标变量时指定select语句
/*
*      语法：TYPE ref_type_name IS REF CURSOR [RETURN return_type];
*      coursor_variable ref_type_name;
*      打开游标变量：OPEN cursor_variable FOR select_statement;
*      关闭游标变量：CLOSE cursor_variable;
*/

DECLARE
       TYPE ref_type_cursor IS REF CURSOR;
       ref_cursor ref_type_cursor;
       emp_record emp%ROWTYPE;
BEGIN
       OPEN ref_cursor FOR select * from emp ;
       LOOP
            FETCH ref_cursor into emp_record;
            EXIT WHEN ref_cursor%NOTFOUND;
            dbms_output.put_line('第'||ref_cursor%ROWCOUNT||'个人：'||emp_record.ename||' 工资：'||emp_record.pay);
       END LOOP;
       CLOSE ref_cursor;
END;

--------------------------------------------------EXCEPTION------------------------------------------------------------------------------------------
--ACCESS_INTO_NULL 开发对象类型前，需要初始化对象    如：emp_table emp_table_type 而后者并未定义
--CASE_NOT_FOUND   WHEN语句里缺少必须包含的条件分支如：SAL里有大于5000的值，而WHEN语句里却没有WHEN SAL<5000 分支语句
--COLLECTION_IS_NULL 在给集合（VARRAY和嵌套表）元素赋值前，必须首先初始化元素
--CURSOR_ALREADY_OPEN 游标重复打开
--DUP_VAL_ON_INDEX   在惟一索引所对应列上键入重复值如：updata dept set depno=&new_no where deptno=&oldno;
--INVALID_CURSOR     在不合法游标上执行操作：如，从未打开的游标中进取数据，或者关闭未打开的游标时
--INVALID_NUMBER     SQL语句中，不能有效地将字符转变成数字时，如数字'100'被写成了'１００'
--NO_DATA_FOUND      执行select into 未返回行，或者索引表未初始化数据时
--TOO_MANY_ROWS      当执行select into语句时，如果返回超过一行时
--ZERO_DIVIDE        PLSQL块中，分母为0的操作时
--SUBSCRIPT_BEYOND_COUNT 当使用嵌套表或者VARRAY元素时，如果元素下标超出了范围，则会引起此异常
--SUBSCRIPT_OUTSIDE_LIMIT 当使用嵌套表或者VARRAY元素时，如果元素下标为负值
--VALUE_ERROR             当PLSQL块中的赋值操作，如果变量长度不足以容纳实际数据时
--自定义异常
/*
*           DECLARE e_integrity EXCEPTION;
*           PRAGMA EXCEPTION_INIT(e_interity,-2291)
*           处理的是ORA-02291
*/
--使用例外函数，SQLCODE SQLERRM 取得与之相关的错误消息
DECLARE
        v_ename emp.ename%TYPE;
        BEGIN
             SELECT ename INTO v_ename FROM emp WHERE pay=12;
             EXCEPTION
                      WHEN NO_DATA_FOUND THEN
                      dbms_output.put_line('不存在此社员');
                      WHEN OTHERS THEN
                      dbms_output.put_line('错误号为：'||SQLCODE);
                      dbms_output.put_line('错误为：'||SQLERRM);
          END;
--使用RAISE_APPLICATION_ERROR 自定义错误消息 RAISE_APPLICATION_ERROR(error_number,message)
----------------------------------------------------------------------------------------------------------------------编译警告
--SERVER:用于检查可能出现的不可的结果或错误，如参数的别名问题
--PERFORMACE:检查可能的性能问题，如INSERT时NUMBER列提供的VARCHAR2的数据
--INFORMATIONAL:用于警告用于检查子程序中的死代码
--ALL:检查所有警告
ALTER SYSTEM SET PLSQL_WARNINGS='ENABLE:ALL';
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE';
ALTER PROCEDURE HELLO COMPILE PLSQL_WARNINGS='ENABLE:PERFORMANCE';
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';

--实现数据审计 AUDIT INSERT,UPDATE,DELETE ON EMP BY ACCESS;
-------------------------------------------------------------------------------------------------------------------常用事件属性函数
--ora_client_ip_address: 用于返回客户端IP地址
--ora_database_name:      用于返回当前数据库名
--ora_des_encrypted_password:用于返回ＤＥＳ加密后的用户口令
--ora_dict_obj_name:      用于返回DDL操作所对应的数据库对象名
--ora_dict_obj_owner:     用于返回DDL操作所对应对象的所有者名
--ora_dict_obj_type:      用于返回DDL操作对应的数据库对象的类型
--ora_instance_num:       用于返回例程号
--ora_login_user:         用于返回登陆用户名
--ora_sysevent:           用于返回触发触发器的系统事件名
-- 激活或者禁用触发器：             ALTER TRIGGER tr_login ENABLE[DISABLE];
--禁用表的所有触发器：              ALTER TABLE tablename DISABLE[ENABLE] ALL TRIGGERS;
--重新编译触发器：                ALTER TRIGGER tr_login COMPILE:
create table log_table(username varchar2(300),
                       log_time date,
                       address varchar2(200));


CREATE OR REPLACE TRIGGER tr_login
AFTER LOGON ON DATABASE
BEGIN
      INSERT INTO log_table(username,log_time,address)
      VALUES(ora_login_user,SYSDATE,ora_client_ip_address);
END;

-----------------------------------------------------------------------------------------------------------------面向对象
--建立对象类型规范： CREATE OR REPLACE TYPE type_name AS OBJECT(atrribute datatype,MEMBER[STATIC] method1 spec)
--建立对象类型体：   CREATE OR REPLACE TYPE BODY type_name AS (MEMBER[STATIC] method1 body);
--包含的方法：     构造方法、MEMBER、STATIC、MAP、ORDER
CREATE OR REPLACE TYPE person_type AS OBJECT(
                                             name varchar2(100),
                                             gender varchar2(100),
                                             birthday date,
                                             address varchar2(100),
                                             MEMBER PROCEDURE change_address(new_addr varchar2),
                                             MEMBER FUNCTION get_info RETURN VARCHAR2,
                                             STATIC FUNCTION get_date RETURN DATE)
                                             NOT FINAL; --如果要作为父类型使用，就必须指定NOT FINAL选项
CREATE OR REPLACE TYPE BODY person_type IS
MEMBER PROCEDURE change_address(new_addr,varchar2)
       IS
       BEGIN
            address:=new_addr;
       END;
MEMBER FUNCTION get_info RETURN VARCHAR2
       IS
       v_info varchar2(100);
       BEGIN
            v_info:='姓名：'||name||'，出生日期：'||birthday;
            RETURN v_info;
       END;
STATIC FUNCTION get_date RETURN DATE
       IS
       BEGIN
            RETURN SYSDATE;
       END;

END;

--如何调用
DECLARE
       v_person person_type;
       BEGIN
            SELECT person INTO v_person FROM table_name WHERE id=1; --person是一个含有多个字段的OBJECT
            v_person.change_address('成都');
            UPDATE table_name SET person=v_person WHERE id=1;
            dbms_output.putline(v_person.get_info);
       END;

--对象类型继承
CREATE OR REPLACE TYPE person_type_son UNDER person_type(
                                             name varchar2(100),
                                             gender varchar2(100),
                                             birthday date,
                                             address varchar2(100),
                                             MEMBER PROCEDURE change_address(new_addr varchar2),
                                             MEMBER FUNCTION get_info RETURN VARCHAR2,
                                             STATIC FUNCTION get_date RETURN DATE);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21158541/viewspace-627008/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21158541/viewspace-627008/