Oracle数据库之PLSQL过程

6qanmwnb0x

--============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;