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