【PL/SQL】开发程序

1.开发PL/SQL过程

· 程序是指被命名的PL/SQL块

·可以存在参数

·可以被其他应用程序调用

·语法：

CREATE [OR REPLACE] PROCEDURE procedure_name

    [(argument1 [mode1] datatype1,

     argument2 [mode2] datatype2,

     . . .)]

   IS | AS

PL/SQL block...

procedure_body;

实例：

①不带参数的存储过程

SQL> CREATE OR REPLACE PROCEDURE proc1 IS

  2     v_ename emp.ename%TYPE;

  3     v_sal  emp.sal%TYPE;

  4  BEGIN

  5    SELECT ename,sal INTO v_ename,v_sal FROM emp WHERE empno=&NO;

  6    dbms_output.put_line('Name is: '||v_ename||','||'salary is :'||v_sal);

  7    EXCEPTION

  8       WHEN no_data_found THEN

  9         dbms_output.put_line('you number is not corrent,please input again!');

 10       WHEN OTHERS THEN

 11         dbms_output.put_line('Others error!');

 12   END;

 13  /

Procedure created    

SQL> exec proc1;            //执行存储过程

Name is: SMITH,salary is :4600

PL/SQL procedure successfully completed

通过数据字典查看procedure信息:

SQL> select OBJECT_NAME,PROCEDURE_NAME,OBJECT_TYPE from user_procedures;

OBJECT_NAME     PROCEDURE_NAME   OBJECT_TYPE

--------------------------------------------------------------------------------

PROC1                               PROCEDURE

②带有参数的存储过程

参数定义中，IN、OUT和IN OUT代表参数的三种不同模式

IN：当调用存储过程时，该模式的形参接收对应实参的值，并且是只读的。默认为IN

OUT：该形参被认为只能写，即只能为其赋值。在存储过程中不能读它的值。返回时，将该形参值传给相应的形参。

IN OUT：都允许

IN：

SQL> CREATE OR REPLACE PROCEDURE ins_dept

  2    (v_deptno IN NUMBER,v_dname VARCHAR2,v_loc IN varchar2) IS

  3    e_dept_err EXCEPTION;

  4    PRAGMA EXCEPTION_INIT(e_dept_err,-0001);

  5    BEGIN

  6      INSERT INTO dept VALUES(v_deptno,v_dname,v_loc);

  7      COMMIT;

  8      EXCEPTION

  9        WHEN e_dept_err THEN

 10          dbms_output.put_line('You deptno is not unique,please input unique deptno number!');

 11        WHEN OTHERS THEN

 12          dbms_output.put_line('Others error!');

 13       END;

 14  /

Procedure created

SQL> exec ins_dept(79,'zhangsan','beijing');

PL/SQL procedure successfully completed

SQL> select * from dept;

DEPTNO DNAME   LOC

------ -------------- -------------

    92 DEV3      

    79 zhangsan beijing

    91 DEV2      

    90 DEV        

    80 caiwu      hongkong

    10 ACCOUNTING HongKong

    20 RESEARCH BEIJING

    30 SALES      CHICAGO

    40 OPERATIONS BOSTON

9 rows selected

OUT：

SQL> CREATE OR REPLACE PROCEDURE proc2

  2    (v_empno IN NUMBER,v_ename OUT VARCHAR2,v_sal OUT NUMBER)

  3  IS

  4  BEGIN

  5    SELECT ename,sal INTO v_ename,v_sal FROM emp WHERE empno=v_empno;

  6    dbms_output.put_line('Employee name is:'|| v_ename);

  7    dbms_output.put_line('Employee salary is: '||v_sal);

  8  EXCEPTION

  9    WHEN no_data_found THEN

 10      dbms_output.put_line('Employee ID is error!');

 11     WHEN OTHERS THEN

 12       dbms_output.put_line('Others error!');

 13   END;

 14  /

Procedure created

在系统下运行：

SQL> var name varchar2(10);

SQL> var sal number;

SQL> exec proc2(7369,:name,:sal);

PL/SQL procedure successfully completed

name

---------

SMITH

sal

---------

4600

通过PLSQL块运行：

SQL> DECLARE

  2    v_name emp.ename%TYPE;

  3    v_sal emp.sal%TYPE;

  4  BEGIN

  5    proc2(7369,v_name,v_sal);

  6    END;

  7  /

Employee name is:SMITH

Employee salary is: 4600

PL/SQL procedure successfully completed

IN-OUT:

SQL> CREATE OR REPLACE PROCEDURE proc3

  2    (v_empno IN OUT NUMBER,v_ename OUT VARCHAR2,v_sal OUT NUMBER)

  3  AS

  4  BEGIN

  5    SELECT empno,ename,sal INTO v_empno,v_ename,v_sal FROM emp WHERE

  6    empno=v_empno;

  7    dbms_output.put_line('Employee ID is: '||v_empno);

  8    dbms_output.put_line('Employee name is: '||v_ename);

  9    dbms_output.put_line('Employee salary is: '||v_sal);

 10  EXCEPTION

 11    WHEN no_data_found THEN

 12      dbms_output.put_line('Employee ID is error');

 13    WHEN OTHERS THEN

 14      dbms_output.put_line('Others error!');

 15    END;

 16  /

Procedure created

执行存储过程：

SQL> DECLARE

  2    v_empno emp.empno%TYPE;

  3    v_ename emp.ename%TYPE;

  4    v_sal emp.sal%TYPE;

  5  BEGIN

  6    v_empno :=&n;

  7    proc3(v_empno,v_ename,v_sal);

  8  END;

  9  /

Employee ID is: 7369

Employee name is: SMITH

Employee salary is: 4600

PL/SQL procedure successfully completed

③查看存储过程代码

Select text from user_source where name=’xx’;

④删除存储过程

DROP PROCEDURE procedure_name;

2.PL/SQL函数

·函数用于返回特定数据

·函数作为表达式的一部分被调用

·函数可以简化客户端应用程序的开发

·提高应用程序的执行性能

函数的使用限制：

①函数可以在SQL语句的一些部分：

—SELECT命令的选择列表

—WHERE和HAVING子句中

—INSERT命令的VALUES子句中

—UPDATE命令的SET子句中

—CONNECT BY 子句中

—START WHTN 子句中

—ORDER BY 子句中

—GROUP BY 子句中

②函数不可以在如下场景使用：

—函数中不能包含INSERT、UPDATE、DELETE语句

—在SQL语句中只能调用服务端存储函数，不能调用客户端函数

—在SQL语句中调用的函数只能使用标准数据类型，不能使用PL/SQL特有的数据类型，如BOOLEAN、TABLE、RECORD等。

语法：

CREATE [OR REPLACE] FUNCTION function_name

  [(argument1 [mode1] datatype1,

    argument2 [mode2] datatype2,

    ....) ]

RETURN datatpe

IS | AS

        function_body;

①不带参数的函数：

SQL> CREATE OR REPLACE FUNCTION fun1

  2    RETURN NUMBER

  3    IS

  4    v_sum_sal emp.sal%TYPE;

  5    BEGIN

  6      SELECT SUM(sal) INTO v_sum_sal FROM emp WHERE deptno=10;

  7    RETURN v_sum_sal;

  8    END;

  9  /

Function created

执行函数：

SQL> DECLARE

  2    v_sumsal emp.sal%TYPE;

  3  BEGIN

  4    v_sumsal :=fun1;

  5    dbms_output.put_line(v_sumsal);

  6  END;

  7  /

8750

PL/SQL procedure successfully completed

②带有参数的函数：

IN：

SQL> CREATE OR REPLACE FUNCTION fun2

  2    (v_deptno IN NUMBER)

  3    RETURN NUMBER

  4    IS

  5     v_sum_sal emp.sal%TYPE;

  6     BEGIN

  7       SELECT SUM(sal) INTO v_sum_sal FROM emp WHERE deptno=v_deptno;

  8       RETURN v_sum_sal;

  9     END;

 10  /

Function created

执行函数：

SQL> DECLARE

  2    v_sumsal emp.sal%TYPE;

  3    BEGIN

  4      v_sumsal :=fun2(10);

  5      dbms_output.put_line(v_sumsal);

  6    END;

  7  /

8750

PL/SQL procedure successfully completed

OUT：

SQL> CREATE OR REPLACE FUNCTION fun3

  2    (v_empno NUMBER,v_ename OUT VARCHAR2,v_sal OUT NUMBER)

  3    RETURN NUMBER

  4    IS

  5    BEGIN

  6      SELECT ename,sal INTO v_ename,v_sal FROM emp WHERE empno=v_empno;

  7      RETURN v_sal;

  8    END;

  9  /

Function created

执行函数：

SQL> DECLARE

  2    v_salary emp.sal%TYPE;

  3    v_ename emp.ename%TYPE;

  4    v_sal emp.sal%TYPE;

  5  BEGIN

  6     v_salary :=fun3(7369,v_ename,v_sal);

  7     dbms_output.put_line('Salary is: '||v_salary);

  8  END;

  9  /

Salary is: 4600

PL/SQL procedure successfully completed