Oracle数据库之PLSQL过程

--============PLSQL==子程序=====
----===========匿名子程序=
--匿名块不存在数据库中
--每次使用时都会进行编译
--不能再其他块中相互调用

--匿名子程序语法格式如下(总结)
declare
  cursor is select .. -- 游标
  type (is record  ,is table of .. index by) --record类型 ,关联数组
  variable type --变量
begin
  execute immediate 'create table'; --动态sql执行ddl
  insert into  --静态SQL执行DML
  execute immediate 'insert into..' --动态sql执行DML
  TCL (commit ;rollback) -- 静态SQL执行TCL
  select (select into 单行) --  0,单行，
  cursor  (loop while for fetch into) --  多行
  select ....bulk collect into  -- bulk collect，减少内存空间交互数据的次数
  curosr (fetch bulk collect into）
  cursor （while fetch bulk collect into 。。。limit n）--Limit限制抓取的记录
   --关联数组的处理
   --声明，赋值，引用，方法：first，last，next，exists，count
  -- 循环结构 
  -- Loop exit; for i in 1..10 loop ; while  xxx LOOP;  
exception
	--- oracle预定义异常(直接使用)
	--- 非oracle预定义异常(绑定，使用)
	--- 用户自定义异常(声明,触发,使用)
end


----==========有名子程序==========
--命名的PL/SQL块，编译时并存储在数据库中,可以在任何需要的地方调用 
--子程序的组成部分:
-----子程序头
-----声明部分
-----可执行部分
-----异常处理部分(可选)

-----------有名子程序的分类
--procedure 过程
--function 函数
--package 包
--trigger 触发器

--===========有名子程序的优点:
--模块化 : 将程序分解成逻辑模块
--可重用性:可以被任意数目的程序调用
--可维护性 : 简化维护操作
--安全性 : 通过设置权限,使数据库更安全


--=======过程 procedure=============
create or replace procedure 过程名字
		[arg_name in | out| int out (argument-type)] --参数都是可选
is / as 
	变量声明
begin
	执行部分
exception 
	异常部分
end;

--指定员工编号(in 参数) ,打印员工信息
create or replace procedure p_emp(id in number)	 --参数不需要指定大小范围
	is
	-- print_emp(id number) is 参数不需要指定大小范围,默认in
	v_emp emp%rowtype; --变量
begin	
	select * into v_emp from emp where empno = id;		
	dbms_output.put_line(v_emp.ename);
	Exception
		when NO_DATA_FOUND
			then dbms_output.put_line('not exists '||id);
end;
--========调用存储过程:
--1. 用匿名块调用
  begin
    call p_emp(7900);
 end;

--2.在sql工作表中直接调用
 call p_emp(7900);--正常输出
 exec p_emp(7900);
 
 ---==============存储过程中的参数===
 create or replace procedure proc1
		(p_c1 varchar2,   p_c2 out varchar2,p_c3 in out varchar2)
is
 v_c1 varchar2(10);
begin 
  --  in ,参数的缺省模式,在过程内部，形参只是可读的.
  -- p_c1 :=p_c1||'d'; 错误的
	v_c1 :=p_c1;
	dbms_output.put_line('in : p_c1-->'||p_c1||':::v_c1-->'||v_c1);
 
		-- out: 在调用过程中，任何的实参将被忽略，即值不会赋给对应的形参，
	dbms_output.put_line('out :p_c2的值-->:'||p_c2); --不会输出任何内容
 
	p_c2 :=p_c2 ||'d';
		--在过程内部，形参可以被读出也可以被写入,
	dbms_output.put_line('out :p_c2 赋值后的值-->:'||p_c2);--输出 'd';
	--过程执行完成后形参的当前值被赋给对应的实参.

	--in 和 out 的结合
	p_c3 :=p_c3 ||'d';--可以被赋值,可以传入值,可以传出值
	dbms_output.put_line('int  out : p_c2赋值后的值-->:'||p_c3);--'ccd';
end;

 call proc1('a','abc',p_c2);-- error , 第3个参数是输出参数,不能赋值
--利用匿名块调用 
declare
	v_c2 varchar2(10) :='bb';
	v_c3 varchar2(10) :='cc';
begin
	proc1('aa',v_c2,v_c3);
	dbms_output.put_line('v_c2:'||v_c2);--在plsql赋值的:d
	dbms_output.put_line('v_c3:'||v_c3);--ccd
end;

--关于参数的再次分析
create or replace proc1(p1 in number) 
		--p1 形式参数
	is
 p2 number(4) :=0; -- p2局部变量
begin
	p2 :=p1; --in 参数赋值给过程中的局部变量
	dbms_output.put_line('p2:'||p2);
end;

--调用
declare
	v_n1 number(3) :=1;
	proc1(v_n1); --实参
	proc1(2); --实参
end;

--总结:
-- 模式为in时：形参对应的实际参数可以是常量或变量.
--模式为in out / out的形参对应的实际参数必须是变量，
--用于存储返回的值，所以不能是常量或表达式.


--============带参数的过程调用:=========


-- 指定一行记录record,增加该记录的薪水salary，返回改emp的id
create or replace procedure proc1(salary in number,
	id out number, v_emp emp%rowtype)
--形式参数不能声明长度,但可使用%type来限制.
	is
begin
	dbms_output.put_line('--修改前的薪水--'||v_emp.sal);
	update emp set sal = (sal+salary) where empno=v_emp.empno;
	id :=v_emp.empno;--out参数赋值
	dbms_output.put_line('--修改的员工id--'||id);
end;

--1.位置标示法
---调用时填入所有的参数，实参跟形参顺序一一对应
declare
	v_emp emp%Rowtype;
	v_id number(4);
begin
	select * into v_emp from emp where empno = 7900;
	proc1(1000,v_id,v_emp);
end;

--2.名字表示法
---调用时给出形参名字，并给出实参
declare
	v_emp emp%Rowtype;
	v_id number(4);
begin
	select * into v_emp from emp where empno = 7900;
	-- 名字必须对应 
	--proc1(salary=>1000,a=>v_id,v_emp=>v_emp); error
	proc1(salary=>1000,id=>v_id,v_emp=>v_emp);
end;


--3,混用,第一个参数必须通过位置来指定
--procname(12,p_outparm=>v_var1, p_inout=>10);
declare
	v_emp emp%Rowtype;
	v_id number(4);
begin
	select * into v_emp from emp where empno = 7900;
	--proc1(salary=>1000,v_id,v_emp=>v_emp); error
	proc1(1000,id=>v_id,v_emp=>v_emp); --ok
end;

--=====存储过程中的DDL语句------------

create or replace procedure proc1
is
begin
 execute immediate 'create table test(c1 number)';
end;

--======Notice
--调用过程所需的权限
----调用过程时，所有角色都是disable的,即角色中包含的所有权都不能生效
----调用其他用户的过程，必须由过程的属主授予执行权限
----在过程中进行ddl操作，所需的权限必须通过直接授予的方式,不能通过角色授予

--查看Proc1过程的源文件
select text from user_source where name = 'PROC1';

--查看Proc1的状态
select object_type,status from user_objects where object_name = 'PROC1';

--重新编译proc1;
alter procedure proc1 compile;