--开发子程序
--一.开发过程
--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;
