Oracle学习操作（6）函数与存储过程
一、oracle自定义函数
1.不带参数的函数：
返回t_book表的总条数：
SQL> create function getBookCount return number as 2 begin 3 declare book_count number; 4 begin 5 select count(*) into book_count from t_book; 6 return book_count; 7 end; 8 end getBookCount; 9 / 函数已创建。 //sys dba给当前用户授权创建函数、创建存储过程的权限，function 跟 procedure 原来是两位一体的。 //SQL> grant create any procedure to c##chengyu;
调用函数：
SQL> set serveroutput on; SQL> begin 2 dbms_output.put_line('表t_book有'||getBookCount()||'条记录'); 3 end; 4 / 表t_book有3条记录 PL/SQL 过程已成功完成。
2.函数，带参数
例1.查某个表的记录数：
SQL> create function getTableRecord(table_name varchar2) return number as 2 begin 3 declare table_count number; 4 query_sql varchar2(200); 5 begin 6 query_sql:='select count(*) from '|| table_name; 7 execute immediate query_sql into table_count; 8 return table_count; 9 end; 10 end getTableRecord; 11 / 函数已创建。 //execute immediate 立即执行；
调用：
SQL> begin 2 dbms_output.put_line('表有'|| getTableRecord('emp') ||'条数据'); 3 end; 4 / 表有14条数据 PL/SQL 过程已成功完成。
例2：显示emp表的sal的税收情况：
SQL> create or replace function sal_tax(v_sal number) return number as 2 begin 3 if v_sal < 2000 then 4 return 0.10; 5 else if v_sal < 2750 then 6 return 0.15; 7 else 8 return 0.20; 9 end if; 10 end if; 11 end sal_tax; 12 / 函数已创建。 SQL> select lower(ename), sal_tax(sal) from emp; LOWER(ENAM SAL_TAX(SAL) ---------- ------------ smith .1 allen .1 ward .1 jones .2 martin .1 blake .2 clark .15 scott .2 king .2.... 已选择 14 行。
二、存储过程
1.不带参数的存储过程：
SQL> create or replace procedure p as 2 begin 3 declare cursor c is select * from emp2 for update; 4 begin 5 for v_emp in c loop 6 if(v_emp.deptno = 10) then 7 update emp2 set sal = sal+1 where current of c; 8 else if(v_emp.deptno = 20) then 9 update emp2 set sal = sal+2 where current of c; 10 else 11 update emp2 set sal = sal+5 where current of c; 12 end if; 13 end if; 14 end loop; 15 commit; 16 end; 17 end p; 18 / 过程已创建。
执行存储过程p：
SQL> execute p; PL/SQL 过程已成功完成。 SQL> select ename, sal from emp2; ENAME SAL ---------- ---------- SMITH 802 ALLEN 1605 WARD 1255 JONES 2977 MARTIN 1255 BLAKE 2855 CLARK 2451 SCOTT 3002 KING 5001... 已选择 14 行。
2.带参数的存储过程：
1）需求：添加记录到t_book，如果bookname存在，则不执行插入操作：
SQL> create procedure addBook(book_name in varchar2, typeId in number) as 2 begin 3 declare maxId number; 4 n number; 5 begin 6 select count(*) into n from t_book where bookname = book_name; 7 if (n>0) then 8 return; 9 end if; 10 select max(id) into maxId from t_book; 11 insert into t_book values (maxId+1, book_name, typeId); 12 commit; 13 end; 14 end addBook; 15 / 过程已创建。
执行存储过程：
SQL> execute addBook('java好东西',1); PL/SQL 过程已成功完成。 SQL> select * from t_book; ID BOOKNAME TYPEID ---------- ---------- ---------- 1 java编程思 1 2 一头扎进ja 1 3 生物起源 2 4 java好东西 1 SQL> execute addBook('java好东西',1); PL/SQL 过程已成功完成。 SQL> select * from t_book; ID BOOKNAME TYPEID ---------- ---------- ---------- 1 java编程思 1 2 一头扎进ja 1 3 生物起源 2 4 java好东西 1
2）out：只出不进：相当于一个返回值;
需求：对t_book执行插入操作，将操作前表的记录数、操作后表的记录数返回：
n1:操作前表的记录数；
n2：操作后表的记录数；
execute是执行单句存储过程的；
SQL> create or replace procedure addBook2(book_name in varchar2, typeId in numbe r, n1 out number, n2 out number) as 2 begin 3 declare maxId number; 4 n number; 5 begin 6 select count(*) into n1 from t_book; 7 select count(*) into n from t_book where bookname = book_name; 8 if (n>0) then 9 return; 10 end if; 11 select max(id) into maxId from t_book; 12 insert into t_book values (maxId+1, book_name, typeId); 13 select count(*) into n2 from t_book; 14 commit; 15 end; 16 end addBook2; 17 / 过程已创建。
执行：
SQL> declare n1 number; 2 n2 number; 3 begin 4 addBook2('jaas阿斯达',2,n1,n2); 5 dbms_output.put_line('n1='||n1); 6 dbms_output.put_line('n2='||n2); 7 end; 8 / n1=4 n2=5 PL/SQL 过程已成功完成。
out的另外例子：
SQL> create or replace procedure p_inout(a in number, b number, ret out number, temp in out number) as 2 begin 3 if(a > b) then 4 ret := a; 5 else 6 ret := b; 7 end if; 8 temp := temp + 1; 9 end p_inout; 10 / 过程已创建。
执行p_inout:
SQL> declare a number:=3; 2 b number:=4; 3 ret number; 4 temp number:=5; 5 begin 6 p_inout(a, b, ret, temp); 7 dbms_output.put_line(ret); 8 dbms_output.put_line(temp); 9 end; 10 / 4 6 PL/SQL 过程已成功完成。 //存储过程中参数分为不同的类型： // in传入参数，谁调用这个存储过程，谁负责给a赋值； //out传出参数， 将ret传出到调用环境中去； //什么都没写，b默认是传入参数； //in out temp既可以接收，又可以传出
三、程序包
当项目中模块很多的时候，用程序包管理下面的函数和存储过程，这样就能包.函数 或者 包.存储过程，方便管理了；
变量在包下面的 函数和存储过程 是共享的变量；
创建程序包：
SQL> create or replace package pkg_book as 2 function getBookCount return number; 3 function getTableRecord(table_name varchar2) return number; 4 procedure addBook(book_name in varchar2, typeId in number); 5 end pkg_book; 6 / 程序包已创建。
创建包体：
SQL> create package body pkg_book as 2 3 function getBookCount return number as 4 begin 5 declare book_count number; 6 begin 7 select count(*) into book_count from t_book; 8 return book_count; 9 end; 10 end getBookCount; 11 12 function getTableRecord(table_name varchar2) return number as 13 begin 14 declare table_count number; 15 query_sql varchar2(200); 16 begin 17 query_sql:='select count(*) from '|| table_name; 18 execute immediate query_sql into table_count; 19 return table_count; 20 end; 21 end getTableRecord; 22 23 procedure addBook(book_name in varchar2, typeId in number) as 24 begin 25 declare maxId number; 26 n number; 27 begin 28 select count(*) into n from t_book where bookname = book_name; 29 if (n>0) then 30 return; 31 end if; 32 select max(id) into maxId from t_book; 33 insert into t_book values (maxId+1, book_name, typeId); 34 commit; 35 end; 36 end addBook; 37 38 end pkg_book; 39 / 程序包体已创建。
调用：使用程序包.函数名来调用；
SQL> set serveroutput on; SQL> begin 2 dbms_output.put_line('表t_book有'||pkg_book.getBookCount()||'条记录'); 3 end; 4 / 表t_book有5条记录 PL/SQL 过程已成功完成。
