pl/sql编程

1,sql语言的缺陷:
     ①     不能进行模块化的编程:只能一句句执行,有些复杂需求需要多条sql语句才行。
     ②     执行速度慢:Java程序中编译好的sql语句,交给Oracle以后,Oracle还要重新编译。
     ③     安全性问题:比如说会暴露列名这样的关乎表结构的信息,有时候会造成安全问题。
     ④     浪费带宽:客户端和服务器端不一样的情况下。
     pl/sql可以解决上述问题。但是pl/sql只能在Oracle中使用。

2,快速入门案例:
     ①     create procedure procedure_myemp1 is
   begin
   insert into myemp(empno,ename,sal) values(8002,'YGJ',750);
   end procedure_myemp1;              
     ②     如何调用过程。     
          Ⅰ控制台:execute procedure_myemp1
          ⅡJava 程序:

3,块的开发:
    [ declare]
          declaration_statements
     begin
          executable_statements
     [exception]
          exception_handing_statements
     end;
     块是没有名字的,是lp/sql的最小单位。如果要让dbms_output.put_line('');显示到控制台上,先要设置:set serveroutput on

4,在pl/sql developer这个软件中默认是不会输出具体的错误的,可用show error;显示出当前错误。
     create procedure procedure_myemp1(in_ename in varchar,in_new_sal in number) is
     begin
           update myemp set sal=in_new_sal where ename=in_ename;
      end procedure_myemp1;
     在过程中变量可以是输入变量(in),也可以是输出变量(out)。作为参数的变量不能指定大小。
     在创建“过程(procedure)”的过程中,即使编译不成功,还是会生成这个过程,这个过程名就不能再用,如果要覆盖,请在create后面加上or                                                   replace。     
5,第33讲中用Java程序调用过程。学完以后统一处理。

6,创建函数。
     create function function_myemp1(v_ename varchar2)
       return number is
       v_sal number;
       begin
         select sal into v_sal from myemp where ename=v_ename;
         return v_sal;
       end;

7,使用函数。
     select function_myemp1('SCOTT') from dual;

8,包的基本语法。
     ①     使用包的目的:使用包可以更好地管理自己写的过程和函数。
     ②     包的基本语法:
               create [or replace] package package_name is
                    procedure procedure_name(v v_type,...);
                    function function_name(v v_type,...) return return_type;
                    --只声明不创建,创建要靠他们自己另外的语句。
               end;

9,写一个包。
     create or replace package package_myemp1 is
          procedure procedure_myemp2(in_empno in number,in_sal in number);
          function function_myemp1(v_name varchar2) return number;
     end;

10,写一个包体。实现包中声明的过程和函数。包中声明过的必须要实现。没有声明的却可以直接实现(但是调不出来,写了也白写)。
   create or replace package body package_myemp1 is

   procedure procedure_myemp2(in_empno in number,in_sal in number) is
   v_ename varchar2(32);
   begin
   select ename into v_ename from emp where empno=in_empno;
   update myemp set sal=in_sal where empno=in_empno;
   exception
   when NO_DATA_FOUND then
   dbms_output.put_line('这个号码不代表任何员工!');
   end procedure_myemp2;

   function function_myemp1(v_ename varchar2)
   return number is
   v_sal number;
   begin
     select sal into v_sal from myemp where ename=v_ename;
     return v_sal;
   end function_myemp1;

   end;
     注意三点:①过程和函数的实现不要create关键字②包和包体中的参数名一定要一样,光是类型一样是不够的。③在不同的包中可以有相同名称的过程名或相同名称的函数名,而不产生冲突。
     包就像Java中的借口,包体就像Java中的实现类。

11,触发器。
     触发器是当特定的SQL DML语句在特定的数据表上运行时,由数据库自动运行的过程。(触发器是过程)

12,pl/sql复合变量record
     ①     基本语法:
               type record_myemp1 is record(
               v     v_type,
               ......
               )
     ②     注意,符合变量只能在过程(或函数或包或代码块)中声明,只能作用于本过程(或函数或包或代码块)。
          create or replace procedure procedure_emp4(v_in_empno in myemp.empno%type) is

type record_myemp1 is record(
  v_ename myemp.ename%type,
  v_sal myemp.sal%type,
  v_job myemp.job%type      --注意这里没有逗号
);                                       --注意这里有分号
v_record_myemp1 record_myemp1;

begin
  select ename,sal,job into v_record_myemp1 from myemp where empno=v_in_empno;
  dbms_output.put_line('姓名:'||v_record_myemp1.v_ename||', 工资:'||v_record_myemp1.v_sal);
end;

13,参照变量
          游标

14,关于goto语句。
     declare 
     i number:=1;
     begin 
          <<a>>
          loop
          dbms_output.put_line(i);
          if i=12 then
          goto b;
          end if;
          i:=i+1;
          if i=10 then
          goto a;
          end if;
          end loop;
          <<b>>
      dbms_output.put_line('循环结束');
     end;

     <<label_name>>指的是标签名。goto语句其实就是汇编语言中的跳转语句。
     会造成可读性差,提高程序复杂性,不得已而为之吧。

15,null语句,不执行任何操作。又有点像汇编。

16,视图会影响表,表也会影响视图。视图是动态生成的。
          个人理解:视图可以看成是查询语句的存储变量。一个查询结果随时拿出来看。
          基本语法:create view view_name as 查询语句。
          对视图的DML操作和对表的DML操作差不多。

17,触发器
     ①     触发器分类:dml触发器,ddl触发器,系统触发器。
     ②     行级触发器和语句级触发器。
     ③     触发器名如果在一张表中起作用,那么在另一张表中不能用同名触发器,用or replace也不行。
     ④     raise_application_error(-20000,'提示性语言');错误号的范围是-20000到-20999
     ⑤     一个例子(dml触发器)面向某个数据库对象:
          create table students(id number,name varchar2(32),class_id number);
          create table classes(class_id number,nums number);

          CREATE OR REPLACE TRIGGER trigger_students1 AFTER
DELETE OR INSERT ON students
FOR EACH ROW
BEGIN
  IF deleting THEN
    UPDATE classes SET nums=nums-1 WHERE :old.class_id=classes.class_id;
  ELSIF inserting THEN
    UPDATE classes SET nums=nums+1 WHERE :new.class_id=classes.class_id;
  END IF;
END;
     ⑥     常用属性函数
             ora_client_ip_address//返回客户端的ip
             ora_database_name//返回数据库名
             ora_login_user//返回登录用户名
             ora_sysevent//返回触发事件名
             ora_des_encrypted_password//返回用户加密后的密码
     ⑦     ddl触发器。注意on后面的对象写法。面向整个方案:
             create or replace trigger trigger_scott after ddl on scott.schema 
             begin
               insert into my_ddl_record values(ora_sysevent,ora_login_user,sysdate);
             end;
     ⑧     禁用触发器:alter trigger trigger_name disable;
             激活触发器:alter trigger trigger_name disable;
             删除触发器:drop trigger trigger_name;