第八章PLSQL应用

第8章PL/SQL应用8.1存储过程8.1.1存储过程概念存储过程PROCEDURE是Oracle的对象，其内容是一个可执行的PL/SQL程序块，其内容保存在系统表空间的数据字典中。

是一个命名的程序块，可以接受多个参数，无返回值（函数有返回值），但可以通过参数返回简单值。

存储过程存储在在服务器端，减少数据的传送和网络流量，存储过程一般在客户端程序中调用或在服务器中作为定时作业运行。

8.1.2存储过程操作1.存储过程建立：CREATE[OR REPLACE]PROCEDURE procedurename[(parameter1[IN|OUT|IN OUT]datatype[{:=|DEFAULT}expression][,parameter2[IN|OUT|IN OUT]datatype[{:=|DEFAULT}expression],...])][AUTHID{CURRENT_USER|DESIGNER}]{IS|AS}pragram block;（1）说明：①OR REPLACE如果存在同名的存储过程，则替换。

②procedurename：存储过程名。

③parameter：参数，可以没有参数。

④IN|OUT|IN OUT：参数的输入输出类型：IN参数将作为一个输入变量,在过程体中不允许被赋值(默认)；OUT参数将作为过程的返回值，在过程体中不允许使用它的值，只能对它赋值；IN OUT参数既可以作为输入参数，也可以作为输出参数。

⑤datatype：参数的数据类型，类型不能做宽度限制。

⑥{:=|DEFAULT}expression：参数的缺省值。

⑦AUTHID：存储过程中访问对象的权限方式：CURRENT_USER：以调用存储过程的用户权限访问存储过程中访问的对象。

DESIGNER：以存储过程拥有者的权限访问存储过程中访问的对象。

⑧pragram block：PL/SQL程序块，如果有声明部分，不写DECLARE关键字。

（2）存储过程举例：CREATE OR REPLACE PROCEDURE cacluavg(pno varchar,pscoreavg number)--不用DECLARE关键字ISv_maths score.maths%TYPE;--不用DECLARE关键字v_chinese score.chinese%TYPE;v_english score.english%TYPE;BEGINSELECT maths,chinese,english INTO v_maths,v_chinese,V_englishFROM score WHERE no=pno;pscoreavg:=(v_maths+v_chinese+V_english)/3;END;说明：参数的数据类型不能有宽度限制；说明部分不用DECLARE关键字。

2.存储过程调用（1）在SQL*plus中的调用：SQL>EXECUTE过程名(参数)；（2）在PL/SQL程序块中的调用：过程名(参数)；PL/SQL中调用存储过程举例：DECLAREv_scoreavg number;BEGINcacluavg(‘101’,vscoreavg);--顺序调用方式cacluavg(psoreavg=>vscoreavg,pno=’101’);--名字调用方式END;3．应用举例：创建：Create or replace procedure upr_in(add_sum in number)Issum_total number(6)default10;BeginSum_total:=sum_total+add_sum;Dbms_output.Put_line('过程执行的结果是：'||to_char(sum_total));End;调用：Execute upr_in(10);输出emp表的记录的条数Create or replace procedure upr_out(add_sum out number)IsBeginSelect count(*)into add_sum from emp;End;Declaren_count number(6);Beginupr_out(n_count);dbms_output.put_line('过程执行的结果是：'||n_count);End;Create or replace procedure upr_in_out(c_chars in out varchar2)IsBeginC_chars:=c_chars||'one';End;Declarec_name varchar2(20);Beginc_name:='Hello Every';upr_in_out(c_name);dbms_output.put_line(c_name);End;删除emp表中指定编号的员工信息Create or replace procedure delemp(v_empno in emp.Empno%type)asNo_result exception;BeginDelete from emp where empno=v_empno;If sql%notfound thenRaise no_result;End if;Dbms_output.Put_line('编码为'||v_empno||'的员工已被除名!'); ExceptionWhen no_result thendbms_output.Put_line('你需要的数据不存在!');When others thenDbms_output.Put_line('发生其它错误!');End delemp;Execute delemp('0005');计算emp指定部门的工资总和，并统计其中的职工数量Create or replace procedure proc_demo(Dept_no number default10,Sal_sum out number,Emp_count out number)IsBeginSelect sum(sal),count(*)into sal_sum,emp_countFrom emp where deptno=dept_no;ExceptionWhen no_data_found thendbms_output.Put_line('你需要的数据不存在!');When others thendbms_output.Put_line('发生其它错误!');End proc_demo;DeclareV_num number;V_sum number(8,2);BeginProc_demo(30,v_sum,v_num);Dbms_output.Put_line('30号部门工资总和：'||v_sum||'，人数：'||v_num); End;用户连接登记记录Create table logtable(userid varchar2(10),logdate date);Create or replace procedure logexecution isBeginInsert into logtable(userid,logdate)values(user,sysdate);End;EXECUTE logexecution;Select*from logtable;8.2存储函数存储函数FUNCTION，与存储过程类似，函数有返回值，并且程序块中有RETURN语句。

8.2.1定义语法：CREATE[OR REPLACE]FUNCTION function[(parameter1[IN|OUT|IN OUT]datatype[:=[DEFAULT]expression][,parameter2[IN|OUT|IN OUT]datatype[:=[DEFAULT]expression]…])]RETURN datatype[AUTHID{CURRENT_USER|DESIGNER}]{IS|AS}pragram block;存储函数举例：CREATE OR REPLACE FUNCTION totalsal(v_empno emp.empno%TYPE)RETURN NUMBERIStotalsal1NUMBER;BEGINSELECT sal+comm INTO totalsal1FROM emp WHERE empno=v_empno;RETURN totalsal1;END;8.2.2函数的调用：函数名(实参)。

函数调用作为操作值使用，如出现在赋值语句的右值等等。

8.2.3应用计算emp表中某部门的工资总和：CREATE OR REPLACE FUNCTION get_salary(Dept_no NUMBER,Emp_count OUT NUMBER)RETURN NUMBER ISV_sum NUMBER;BEGINSELECT SUM(sal),count(*)INTO V_sum,emp_countFROM emp WHERE deptno=dept_no;RETURN v_sum;EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('发生其它错误!');END get_salary;DECLAREV_num NUMBER;V_sum NUMBER;BEGINV_sum:=get_salary(30,v_num);DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||'，人数：'||v_num);END;DECLAREV_num NUMBER;V_sum NUMBER;BEGINV_sum:=get_salary(emp_count=>v_num,dept_no=>30);DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||'，人数：'||v_num);END;8．3触发器8.3.1触发器的概念触发器：是特定事件出现的时候，自动执行的代码块。