pl/sql编程基础

我是007

　　PL/SQL
　　1、过程、函数、触发器是pl/sql编写的
　　2、过程、函数、触发器是存放在oracle数据库中的
　　3、pl/sql是非常强大的过程化语言
　　4、过程、函数、触发器可以在java程序中调用
　　pl/sql编写能节省一点时间就是提高了性能（量大），java直接调用数据库存放的过程，解析时间就节省下来了，提高了性能
　　模块化的设计思想-----》存储过程
　　网络传输（java程序中编写的sql语言），直接调用数据库的过程节省了传输量
　　提高安全性（存储过程避免了数据库信息的泄漏）
　　缺点：
　　移植性不好
　　pl/sql编程基本单位是块，通过块可以编写出过程、函数、触发器、包
　　下面进行一个最基本的编程
　　案例：向某表中插入一条数据
　　create or repalce procedure 名称 is :replace表示如果名称已存在，就替换
　　begin
　　insert into test values ('xiaoming','redhat')
　　end;
　　/
　　create procedure创建存储过程关键字
　　or repalce：表示如果名称已存在，就替换
　　is：也是关键字
　　存储过程定义的头和begin之间是定义部分（定义变量常量等等），后面提到
　　begin：关键字
　　end：结束符
　　begin与end结束之间就是执行部分
　　上面一个简单的存储过程是向某表中添加一条数据，现在先创建一张表
　　SQL> create table names(name varchar2(20),password varchar2(30));
　　Table created.
　　然后通过编写一个存储过程向其中添加数据
　　SQL> create or replace procedure sp_pro1 is
　　2  begin
　　3  insert into names values ('xiaoming','redhat');
　　4  end;
　　5  /
　　Procedure created.
　　存储过程已建立，该如何执行呢？使用关键字exec或者call，如下
　　SQL> exec sp_pro1;
　　PL/SQL procedure successfully completed.
　　然后查询表，看是否添加了数据
　　SQL> select * from names;
　　NAME                 PASSWORD
　　-------------------- ------------------------------
　　xiaoming             redhat
　　当若编写的过程有误时，可以通过show error这条命令查看错误具体信息
　　pl/sql编程是由最小单位块组成的，看看块的组成部分
　　pl/sql块由三个部分组成：定义部分、执行部分、例外处理部分
　　declare
　　定义部分，定义常量、变量、游标、例外、复杂数据类型
　　begin
　　执行部分，要执行的pl/sql语句和sql语句
　　exception
　　例外处理部分，处理运行的各种错误
　　定义部分是从declare开始的，可选
　　执行部分从begin开始，必选
　　例外处理部分从exception，可选
　　下面编写一条最简单的块，输出hello world
　　编写之前打开系统的屏幕输出信息，不然看不到效果
　　SQL> set serveroutput on;
　　SQL> begin
　　2  dbms_output.put_line('hello world');
　　3  end;
　　4  /
　　hello world
　　PL/SQL procedure successfully completed.
　　最简单的块编程，只有执行部分，而且只输出了一条信息hello world。
　　相关说明：dbms_output是oracle所提供的包（类似java的开发包），该包包含一些过程，put_line就是
　　dbms_output包的一个过程（包里面的一个过程）
　　实例2：包含定义部分和执行部分
　　SQL> declare
　　2  v_name varchar2(20);    改行表示定义的变量，变量名v_name，数据类型为varchar2
　　3  begin
　　4  select ename into v_name from emp where empno=&empno;  &由键盘输出
　　5  dbms_output.put_line(v_name);
　　6  end;
　　7  /
　　Enter value for empno: 7788
　　old   4: select ename into v_name from emp where empno=&empno;
　　new   4: select ename into v_name from emp where empno=7788;
　　SCOTT        输出到屏幕的信息v_name
　　PL/SQL procedure successfully completed.
　　实例3：包含定义部分、执行部分和例外处理部分
　　为了避免pl/sql程序的运行错误，提高pl/sql的健壮性，应该对可能的错误进行处理
　　相关说明：oracle事先预定义了一些例外，no_data_found就是找不到数据的例外
　　如上述例子，如果输入烦人不是emp表中的empno号码，那么将会报错，报错该如何处理呢，这里就定义例外部分，交给他处理
　　SQL> declare
　　2  v_name varchar2(20);
　　3  v_sal number(7,2);
　　4  begin
　　5  select ename,sal into v_name,v_sal from emp where empno=&empno;
　　6  dbms_output.put_line(v_name||' '||v_sal);
　　7  exception             定义例外关键字exception
　　8  when no_data_found then    当查询不到数据时，采取措施打印error
　　9  dbms_output.put_line('error');
　　10  end;
　　11  /
　　Enter value for empno: 78     78并不在emp表中的empno好中
　　old   5: select ename,sal into v_name,v_sal from emp where empno=&empno;
　　new   5: select ename,sal into v_name,v_sal from emp where empno=78;
　　error       打印error
　　过程
　　过程用于执行特定的操作，当建立过程时，既可以指定输入参数（in），也可以指定输出参数（out）。通过在过程中使用输入参数可以将数据传递带执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。在sqlplus中使用create procedure 命令来建立过程
　　SQL> --过程编写
　　SQL> create or replace procedure sp_pro1(name varchar2,pass varchar2) is
　　2  begin
　　3  insert into names values (name,pass);
　　4  end;
　　5  /
　　Procedure created.
　　sp_pro1(name varchar2,pass varchar2)：这里面的参数相当于编程里面的形参，传递的数据
　　然后调用该存储过程
　　SQL> exec sp_pro1('xiaobai','redhat');
　　PL/SQL procedure successfully completed.
　　然后查询数据是否已经插入了
　　SQL> select * from names;
　　NAME                 PASSWORD
　　-------------------- ------------------------------
　　xiaoming             redhat
　　xiaobai              redhat
　　修改表emp的雇员为smith的薪水，编写存储过程实现
　　SQL> create or replace procedure sp_pro1(name varchar2,v_sal number) is
　　2  begin
　　3  update emp set sal=v_sal where ename=name;
　　4  end;
　　5  /
　　Procedure created.
　　调用存储过程，传递形参数据
　　SQL> exec sp_pro1('SMITH',1200);
　　PL/SQL procedure successfully completed.
　　查询改变结果
　　SQL> select ename,sal from emp where ename='SMITH';
　　ENAME             SAL
　　---------- ----------
　　SMITH            1200
　　过程用于执行特定的操作，当建立过程时，既可以指定输入参数（in），也可以指定输出参数（out）
　　sp_pro1(name varchar2,pass varchar2)这里面加入的参数默认是in，如果要输出有返回值的存储过程必须加上out，看下面例子
　　给定一个empno雇员号，返回雇员名，编写一个存储过程
　　SQL> create or replace procedure sp_pro1(spno in number,spname out varchar2) is
　　2  begin
　　3  select ename into spname from emp where empno=spno;
　　4  end;
　　5  /
　　Procedure created.
　　该如何调用了，这里就不能直接exec procedure_name这样了，调用方式如下
　　SQL> declare
　　2  v_name varchar(20);     定义一个变量，将存储过程返回出来的值存入到这个变量中
　　3  begin
　　4  sp_pro1(7788,v_name);
　　5  dbms_output.put_line(v_name);   打印变量的值，也就是返回出来的值
　　6  end;
　　7  /
　　SCOTT
　　PL/SQL procedure successfully completed.
　　什么情况下用exec调用，什么情况下用PLSQL调用存储过程？
　　exec适合于调用存储过程无返回值
　　plsql适合于调用存储过程有返回值，不管多少个
　　函数
　　过程用于返回特定的数据，当建立函数时，在函数头必须包含return字句，而在函数体内必须包含return语句
　　返回的数据，创建函数用create function
　　SQL> --函数案例
　　结构：create or replace function return ..is，看下面例子
　　SQL> create or replace function sp_fun1(name varchar2) return number is
　　2  yearsal number(7,2);
　　3  begin
　　4  select sal*12 into yearsal from emp where ename=name;
　　5  return yearsal;   定义需要返回的
　　6  end;
　　7  /
　　Function created.
　　函数创建完成，该如何调用呢？
　　SQL> declare
　　2  v_sal number(7,2);     定义一个变量用来接收函数返回的值
　　3  begin
　　4  v_sal:=sp_fun1('SMITH');  将函数返回的值赋值给v_sal，赋值为:=
　　5  dbms_output.put_line(v_sal);
　　6  end;
　　7  /
　　14400
　　PL/SQL procedure successfully completed.