/**
定义:存储过bai程(Stored Procedure )是一组为了完成特定功能的SQL 语句du集,经编译后存储在数据库中zhi。用户dao通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。
存储过程是由流控制和SQL 语句书写的过程,这个过程经编译和优化后存储在数据库服务器中,应用程序使用时只要调用即可。在ORACLE 中,若干个有联系的过程可以组合在一起构成程序包。
优点:
1.存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时(如对多个表进行Update、Insert、Query、Delete时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.存储过程可以重复使用,可减少数据库开发人员的工作量。
4.安全性高,可设定只有某用户才具有对指定存储过程的使用权。
简单说,你在你的机器上写了个存储过程,这个存储过程像那些表里的数据一样被放在遥远的数据库服务器当中,但是它又是可执行的代码,其他能连到数据库服务器的用户,可以调用你写的存储过程
它的作用是隐藏细节,就是说,你写的存储过程代码可能很复杂,但是其他人调用它却很简单,不用具体知道它是如何做的,且一次能完成多个指令
*/
----------------- 函数 ----
create or replace function my_fun ------ 表示无参
return number
is
----- 声明部分
begin
dbms_output.put_line('第一个无参的函数');
return 1;
end;
-------- 函数不会自动执行 必须调用
---- 调用
select my_fun from dual; --- select 语句 调用函数
----------------- 函数 ----
create or replace function my_fun ------ () 里面必须有参数
return boolean
is
----- 声明部分
begin
dbms_output.put_line('第一个无参的函数');
return true;
end;
----------------- 函数 ----
------ () 里面必须有参数
create or replace function my_fun return date is
----- 声明部分
begin
dbms_output.put_line('第一个无参的函数');
return sysdate;
end;
create or replace function my_fun ------ () 里面必须有参数
return emp%rowtype
is
----- 声明部分
v_emp emp%rowtype;
begin
dbms_output.put_line('第一个无参的函数');
select * into v_emp from emp where empno=7369;
return v_emp;
end;
--- 调用
declare
v_emp emp%rowtype;
begin
v_emp:= my_fun();
dbms_output.put_line(v_emp.ename);
end;
create or replace function my_fun ------ () 里面必须有参数
return emp.empno%type
is
----- 声明部分
v_emp emp.empno%type;
begin
dbms_output.put_line('第一个无参的函数');
select empno into v_emp from emp where empno=7369;
return v_emp;
end;
select my_fun from dual; --- 简单类型中 boolean 不能使用 只能返回 单列 单行
-------------- 匿名块的调用方式
declare
v_return number;---接受函数的返回值
begin
dbms_output.put_line('方式1-----------');
v_return:=my_fun();
dbms_output.put_line(v_return);
dbms_output.put_line('方式2---------------');
dbms_output.put_line(my_fun);
dbms_output.put_line('方式3---------------');
select my_fun into v_return from dual;
dbms_output.put_line(v_return);
end;
----------------- 有参的函数
create or replace function my_fun01(pid number)
return number
is
v_id number:=pid;---接受参数
begin
dbms_output.put_line(pid);
--pid:=12; -- 默认是输入参数 不能够修改 只能使用
v_id:=12;
return v_id;
end;
select my_fun01(10) from dual;
------- 根据编号 返回员工的信息
create or replace function get_emp_by_empno (p_empno number)
return emp%rowtype
is
v_empinfo emp%rowtype;
begin
select * into v_empinfo from emp where empno=p_empno;
return v_empinfo;
exception
when no_data_found then
dbms_output.put_line(SQLCODE||SQLERRM);
return v_empinfo;
end;
--执行
declare
v_count number:=&n;
v_empinfo emp%rowtype;
begin
v_empinfo:=get_emp_by_empno(v_count);
dbms_output.put_line('--------'||v_empinfo.empno||v_empinfo.ename);
end;
--------- 给某个部门的所有员工涨工资 返回值 boolean 表示是否涨工资
create or replace function add_sal_by_deptno (p_deptno number,p_sal number)
return boolean
is
--------
begin
update emp set sal=sal+p_sal where deptno=p_deptno;----隐式游标
if sql%rowcount >0 then
return true;
else
return false;
end if;
end;
select *from emp;
----调用
declare
v_flag boolean;------ 默认值是 false
v_deptno number:=&n;
begin
select deptno into v_deptno from dept where deptno=v_deptno;
v_flag:=add_sal_by_deptno(v_deptno,1);
if v_flag then
dbms_output.put_line('修改成功');
else
dbms_output.put_line('修改失败');
end if;
exception
when others then
dbms_output.put_line('部门不存在');
end;
--------------- 参数调用 --定位传参 前提得知道 参数的名字
--调用 add_sal_by_deptno (p_deptno number,p_sal number)
declare
v_flag boolean;------ 默认值是 false
v_deptno number:=&n;
begin
-- v_flag:=add_sal_by_deptno(v_deptno,1);--- 参数传递1
v_flag:=add_sal_by_deptno(p_sal=>1,p_deptno=>v_deptno);--- 参数传递2 定位传参
----混合使用
if v_flag then
dbms_output.put_line('修改成功');
else
dbms_output.put_line('修改失败');
end if;
end;
--- ----混合使用
create or replace function test_p (p_01 varchar2,p_02 number,p_03 date)
return date
is
begin
dbms_output.put_line(p_01||p_02||p_03);
return sysdate;
end;
declare
v_date date;
begin
--v_date:=test_p(p_03=>sysdate,p_01=>'字符串',p_02=>110);
--v_date:=test_p(p_03=>sysdate,110,p_01=>'字符串');
v_date:=test_p('字符串',110,p_03=>sysdate);------ 如果开始使用定位传参 后面的所有参数必须使用 定位传参
v_date:=test_p('字符串',p_03=>sysdate,p_02=>110);
end;
--- 通过一个员工 名的 部分信息 返回含有该信息的 员工 个数
create or replace function empcount(empname varchar2)
return number
is
e_count number;
begin
--select count(*) into e_count from emp where ename like '%'||empname||'%';
select count(*) into e_count from emp where instr(ename,empname)>0;
return e_count;
end;
--调用
declare
emp_count number;
e_name varchar2(10):=&n;
begin
emp_count:=empcount(e_name);
dbms_output.put_line('名字包含'||e_name||'的人数为:'||emp_count);
end;
---- 显示员工的信息(给我一个员工 显示信息) 返回 date
create or replace function show_emp(p_emp emp%rowtype)
return date
is
begin
dbms_output.put_line('员工姓名'||p_emp.ename||'编号'||p_emp.empno);
return sysdate;
end;
declare
v_emprow emp%rowtype;
v_date date;
begin
select * into v_emprow from emp where empno=8888;
v_date:= show_emp(v_emprow);-- 函数 调用 必须接受返回值
end;
------输出所有的员工信息 表
declare
type emp_table_type is table of emp%rowtype ;
v_emp_table emp_table_type;
v_date date;
begin
select * bulk collect into v_emp_table from emp ;
for i in 1..v_emp_table.count loop
v_date:= show_emp(v_emp_table(i));-- 函数 调用 必须接受返回值
end loop;
end;
----输出所有的员工信息 游标
declare
v_date date;
v_emp emp%rowtype;
cursor cur_emp is select *from emp;
begin
for e in cur_emp loop
v_date:=show_emp(e);
end loop;
open cur_emp;
loop
fetch cur_emp into v_emp ;
exit when cur_emp%notfound;
v_date:=show_emp(v_emp);
end loop;
end;
--------------------------- 参数 in 输入 out 输出 in out 输入输出 ------------------
create or replace function test_fun01(p01 number ,p02 out varchar2)
return number
is -------------------- in 输入 out 输出 区别
-------- in 带入参数值 in 只能用于传值 不能被赋值 (final)
--------out 不能带入值 默认是 空 目的就是从函数中带出去值 需要在函数中赋值 (然后带出)
begin
--p01:=110;
dbms_output.put_line('p01 in number'||p01);
dbms_output.put_line('p02 in number'||p02);
p02:='苏大强';
return 1;
end;
declare
v_return number;
v_p02 varchar2(10) :='李强';
begin
v_return :=test_fun01(11,v_p02);
--- 如果参数 是 out 类型 通过 变量传递参数 (不能给定常量)
dbms_output.put_line('v_p02 in number'||v_p02);
end;
----------- 默认 类型是 in 输入参数p03
create or replace function test_fun02(p01 in number ,p02 out varchar2,p03 number)
return number
is
begin
-- p01:=110;
-- p03:=110;
dbms_output.put_line('p01 in number'||p01);
dbms_output.put_line('p02 in number'||p02);
p02:='苏大强';
return 1;
end;
---- in out 可以传入 也可以带出 (重新可以被赋值) 调用时 必须使用变量
create or replace function test_fun03(p01 in number ,p02 out varchar2,p03 in out number)
return number
is
begin
-- p01:=110;
-- p03:=110;
dbms_output.put_line('p01 in number'||p01);
dbms_output.put_line('p02 in number'||p02);
dbms_output.put_line('p03 in number'||p03);
p02:='苏大强';
p03:=11;
return 1;
end;
declare
v_return number;
v_p02 varchar2(10) :='李强';
v_p03 number :=12;
begin
v_return :=test_fun03(11,v_p02,v_p03);
--- 如果参数 是 out 类型 通过 变量传递参数 (不能给定常量)
dbms_output.put_line('v_p02 in number'||v_p02);
dbms_output.put_line('v_p03 in number'||v_p03);
end;
------- 参数的默认值
create or replace function test_default( p01 number ,p02 number default 20 ,p03 number )
--p02 参数 加了 默认值 之后 可以不用传参
--- 一般 放到 最后一个参数 oracle 在中间 也可以 必须使用 定位传参
return number
is
begin
dbms_output.put_line('p01 in number'||p01);
dbms_output.put_line('p02 in number'||p02);
return 1;
end;
select test_default(1 ,p03=>3) from dual;
---- 过程 --- (没有返回值)
create or replace procedure my_pro --- 无参过程
is
begin
dbms_output.put_line('第一个过程');
end;
---调用 过程
begin
my_pro;
end;
---- 传入部门编号 部门名称 部门所在地 添加 部门 告诉我 添加成功 ?
create or replace procedure add_dept(pdept dept%rowtype,pfalg out boolean)
is
begin
insert into dept values(pdept.deptno,pdept.dname,pdept.loc);
pfalg:=true;
exception
when dup_val_on_index then
dbms_output.put_line('违反唯一约束');
pfalg:=false;
end;
declare
v_dept dept%rowtype;
v_flag boolean;
begin
v_dept.deptno:=12;
v_dept.dname:='许愿墙';
v_dept.loc:='中国西安';
add_dept(v_dept,v_flag);
if v_flag then
dbms_output.put_line('部门添加成功');
else
dbms_output.put_line('部门添加失败。。。。。。。。。。');
end if;
end;
---------业务 一个业务 需要操作多次数据库 (分页 总页数 查询当前的数据 )
-- 删除 部门信息 删除 部门下有 员工 得先处理员工 ( set null )
create or replace procedure delete_dept_by_id (pdeptno number)
is
father_find_child exception ;
pragma exception_init(father_find_child,-2292);
begin
delete from dept where deptno= pdeptno;
exception
when father_find_child then
---- 表示 不能删除 关联的员工 设置 set null
update emp set deptno=null where deptno=pdeptno;
--delete from dept where deptno= pdeptno;
delete_dept_by_id(pdeptno);
end;
begin
delete_dept_by_id(40);
end;
select *from emp;
select *from dept;