PL/SQL开发子程序

--开发子程序
--一.开发过程
--1.语法
CREATE [OR REPLACE] PROCEDURE procedure_name
(arg1 model1 datatype1,arg2 model2 datatype2,...)
IS[AS]
PL/SQL block;

--2.建立不带参数的过程
SQL> CREATE OR REPLACE PROCEDURE demo1
  2  IS
  3  BEGIN
  4    dbms_output.put_line(systimestamp);
  5  END;
  6  /

过程已创建。

SQL>
SQL> set serveroutput on;
SQL> exec demo1
10-9月 -12 04.24.39.446000000 下午 +08:00

PL/SQL 过程已成功完成。

SQL> call demo1();
10-9月 -12 04.24.40.865000000 下午 +08:00

调用完成。

--3.创建带参数的过程
--创建带IN,OUT参数的过程
SQL> SET SERVEROUTPUT ON;
SQL> CREATE OR REPLACE PROCEDURE query_employee
  2  (eno NUMBER,name OUT varchar2,salary OUT NUMBER)
  3  IS
  4  BEGIN
  5    SELECT ename,sal INTO name,salary FROM emp
  6    WHERE empno=eno;
  7  EXCEPTION
  8    WHEN NO_DATA_FOUND THEN
  9      RAISE_APPLICATION_ERROR(-20000,'雇员不存在');
 10  END;
 11  /

过程已创建。

SQL> var name VARCHAR2(10)
SQL> var salary NUMBER
SQL> exec query_employee(7788,:name,:salary);

PL/SQL 过程已成功完成。

SQL> PRINT name salary

NAME      SALARY
--------------------------------
SCOTT      1200


--创建IN,OUT都有的过程
SQL> CREATE OR REPLACE PROCEDURE demo3
  2  (num1 IN OUT NUMBER,num2 IN OUT NUMBER)
  3  IS
  4    v1 NUMBER;
  5    v2 NUMBER;
  6  BEGIN
  7    v1:=num1/num2;
  8    v2:=MOD(num1,num2);
  9    num1:=v1;
 10    num2:=v2;
 11  END;
 12  /

过程已创建。

SQL> var n1 NUMBER
SQL> var n2 NUMBER
SQL> exec :n1:=100

PL/SQL 过程已成功完成。

SQL> exec :n2:=30

PL/SQL 过程已成功完成。

SQL> exec demo3(:n1,:n2)

PL/SQL 过程已成功完成。

SQL> PRINT n1 n2

        N1  N2
--------------------
3.33333333  10

--传值方式：位置传递,名称传递,组合传递。

--过程查看:
SELECT text FROM user_source WHERE name='DEMO3';

--二.开发函数
--语法：
CREATE OR REPLACE FUNCTION funname
(arg1 mode1 datatype1,
...)
IS/AS
PL/SQL BLOCk;

--无参
SQL> CREATE OR REPLACE FUNCTION demo1
  2  RETURN VARCHAR2
  3  IS
  4    v_user VARCHAR2(100);
  5  BEGIN
  6    SELECT username INTO v_user FROM user_users;
  7    RETURN v_user;
  8  END;
  9  /

函数已创建。

--打印
SQL> var v1 VARCHAR2(100)
SQL> exec :v1:=demo1

PL/SQL 过程已成功完成。
SQL> PRINT v1

V1
--------------------------------------------------------------------------------
SCOTT

--SQL语句调用
SQL> SELECT demo1 FROM dual;

DEMO1
--------------------------------------------------------------------------------
SCOTT

--包调用
SQL> SET SERVEROUTPUT ON;
SQL> exec dbms_output.put_line(demo1);
SCOTT

--参数IN
SQL> CREATE OR REPLACE FUNCTION demo2
  2  (name IN VARCHAR2)
  3  RETURN NUMBER
  4  IS
  5    v_sal emp.sal%TYPE;
  6  BEGIN
  7    SELECT sal INTO v_sal FROM emp
  8    WHERE UPPER(ename)=UPPER(name);
  9    RETURN v_sal;
 10  EXCEPTION
 11    WHEN NO_DATA_FOUND THEN
 12      RAISE_APPLICATION_ERROR(-20000,'雇员名不存在');
 13  END;
 14  /

函数已创建。

SQL> var sal number
SQL> exec :sal:=demo2('scott')

PL/SQL 过程已成功完成。

SQL> print sal

       SAL
----------
      1200

SQL> exec :sal:=demo2('sco')
BEGIN :sal:=demo2('sco'); END;

*
第 1 行出现错误:
ORA-20000: 雇员名不存在
ORA-06512: 在 "SCOTT.DEMO2", line 12
ORA-06512: 在 line 1

--参数OUT
SQL> CREATE OR REPLACE FUNCTION demo3
  2  (title OUT VARCHAR2,name VARCHAR2)
  3  RETURN VARCHAR2
  4  IS
  5    v_deptName emp.ename%TYPE;
  6  BEGIN
  7    SELECT t2.dname,t1.job INTO v_deptName,title
  8      FROM emp t1,dept t2
  9      WHERE t1.deptno=t2.deptno
 10      AND UPPER(t1.ename)=UPPER(name);
 11    RETURN v_deptName;
 12  EXCEPTION
 13    WHEN NO_DATA_FOUND THEN
 14      RAISE_APPLICATION_ERROR(-20001,'雇员未找到');
 15  END;
 16  /

函数已创建。

SQL> var title VARCHAR2(20)
SQL> var deptname VARCHAR2(20)
SQL> exec :deptname:=demo3(:title,'scott')

PL/SQL 过程已成功完成。

SQL> print title deptname

TITLE
--------------------------------
ANALYST


DEPTNAME
--------------------------------
RESEARCH

--参数OUT
SQL> CREATE OR REPLACE FUNCTION demo4
  2  (num1 NUMBER,num2 IN OUT NUMBER)
  3  RETURN NUMBER
  4  IS
  5    v_result NUMBER(6);
  6    v_remainder NUMBER;
  7  BEGIN
  8    v_result:=num1/num2;
  9    v_remainder:=MOD(num1,num2);
 10    num2:=v_result;
 11    RETURN v_remainder;
 12  EXCEPTION
 13    WHEN ZERO_DIVIDE THEN
 14      RAISE_APPLICATION_ERROR(-20002,'除0错误');
 15  END;
 16  /

函数已创建。

SQL> var result number
SQL> var remainder number
SQL> exec :result:=30

PL/SQL 过程已成功完成。

SQL> exec :remainder:=demo4(100,:result)

PL/SQL 过程已成功完成。

SQL> print result remainder

    RESULT       REMAINDER
----------------------------------------
      3              10

--函数调用限制
--函数可以在以下几个部分中进行调用：
--1.SELECT命令的列表中
--2.WHERE和HAVING子句
--3.CONNECT BY,STAT WITH,ORDER BY,GROUP BY子句
--4.INSERT命令的VALUES子句中
--5.UPDATE命令的SET子句中
--SQL中调用函数有以下的一些限制：
--1.在SQL子句中只能调用存储过程（服务器端）,而不能调用客户端的函数。
--2.函数不能带OUT参数
--3.函数中返回的数据类型为SQL所支持的类型
--4.SQL中调用的函数不能包括INSERT,UPDATE,DELETE语句。

--查看函数
SELECT text FROM user_source WHERE name='DEMO4';

--删除函数

--三.管理子程序
--1.列出当前用的子程序
SQL> col object_name format a20
SQL> SELECT object_name,created,status FROM user_objects 
SQL>   WHERE object_type IN('PROCEDURE','FUNCTION');

OBJECT_NAME          CREATED        STATUS
-------------------- -------------- -------
RAISE_COMM           31-8月 -12     VALID
DEAD_CODE            31-8月 -12     VALID
UPDATE_SAL           31-8月 -12     VALID
DEMO1                17-9月 -12     VALID
DEMO2                17-9月 -12     VALID
ADD_EMPLOYEE         11-9月 -12     INVALID
QUERY_EMPLOYEE       17-9月 -12     VALID
COMPUTE              17-9月 -12     INVALID
DEMO3                17-9月 -12     VALID
DEMO4                17-9月 -12     VALID

已选择10行。

--2.查看子程序源代码
SELECT text FROM user_source WHERE name='';

--3.列出子程序编译错误
SHOW ERRORS FUNCTION demo1;
SHOW ERRORS PROCEDURE demo2;

--4.列出对象依赖关系
SELECT * FROM user_dependencies WHERE referenced_name='EMP';

--5.重新编译
ALTER FUNCTION[VIEW][PROCEDURE] name COMPILE;

 

转载于:https://www.cnblogs.com/wean/archive/2012/09/17/2689068.html