上海网站推广服务,北京企业网站怎么建设,wordpress响应式主题下载,如何免费制作简历注意: raise notice是高斯的输出语句; DBMS_OUT_PUT.PUT_LINE是oracle的输出语句
存储过程 Stored Procedure
存储过程可以封装数据访问逻辑#xff0c;使得应用程序可以通过调用存储过程来执行这些逻辑#xff0c;而不是直接执行SQL语句。这有助于提高代码的可重用性、可…注意: raise notice是高斯的输出语句; DBMS_OUT_PUT.PUT_LINE是oracle的输出语句
存储过程 Stored Procedure
存储过程可以封装数据访问逻辑使得应用程序可以通过调用存储过程来执行这些逻辑而不是直接执行SQL语句。这有助于提高代码的可重用性、可维护性和安全性
命名规范
使用下划线分隔单词
使用前缀 sp_或 p_等
可读性: 使用有意义的名称
语法: 创建存储过程
CREATE OR REPLACE PROCEDURE procedure_name [pramaters]
IS/AS -- 变量声明
BEGIN-- 存储过程的主体-- 可以包含SQL语句和PL/SQL代码
exception--异常处理
END procedure_name;
CREATE OR REPLACE创建或者替换当存储过程修改的时候编译后会覆盖掉原来的同名的存储过程
参数 参数类型不需要写长度参数名不要和存储过程当中定义的变量重名 IN从外边调用存储过程的地方把值传到存储过程内部默认的参数类型 [in] 参数名 参数类型 命名规范p_或者i_开头 在存储过程中对 IN 类型参数的值进行修改新修改的值 只在存储过程内部有效 OUT把存储过程当中计算获取到的值传到外边调用存储过程的地方 OUT 参数名 参数类型 命名规范o_ 开头 INOUT可以通过 INOUT 参数把 值传到 存储过程中也可以把值从存储过程传出来不常使用 INOUT 参数名 参数类型 -- 存储过程在创建的时候只是编译不会执行调用存储过程的时候才会执行 -- 调用存储过程从数据库中将存储过程调用出来并且会执行里面的代码 -- 无参存储过程
-- 案例1编写存储过程获取员工编号为7788的员工的姓名和工资并打印出来
CREATE OR REPLACE PROCEDURE sp_output_emp
IS
DECLAREv_ename emp.ename%TYPE;v_sal emp.sal%TYPE;
BEGINSELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno7788;RAISE NOTICE 7788的姓名是:%工资是:%, v_ename, v_sal;
EXCEPTIONWHEN OTHERS THENRAISE NOTICE 发生异常;RAISE NOTICE 异常代码:%, SQLSTATE;RAISE NOTICE 异常信息:%, SQLERRM;
END;-- 简单调用 CALL sp_output_emp(); -- 调用存储过程的名字( ); CALL sp_output_emp1(5566); 有参存储过程(in)
-- 案例2对案例1进行修改增加传入参数查询指定传入员工编号的员工姓名和工资并打印
CREATE OR REPLACE PROCEDURE sp_output_emp1 (i_empno int)
IS
DECLAREv_ename emp.ename%TYPE;v_sal emp.sal%TYPE;
BEGINSELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empnoi_empno;RAISE NOTICE 7788的姓名是:%工资是:%, v_ename, v_sal;
EXCEPTIONWHEN OTHERS THENRAISE NOTICE 发生异常;RAISE NOTICE 异常代码:%, SQLSTATE;RAISE NOTICE 异常信息:%, SQLERRM;
END;
调用存储参数: 如上和无参调用完全想同
有参存储过程(in 和 out)
例3在案例2的基础上继续进行修改增加传出参数将查询出来的员工姓名和工资向外传递
CREATE OR REPLACE PROCEDURE sp_output_emp2(IN i_empno int,OUT o_ename emp.ename%TYPE,
OUT o_sal emp.sal%type)
IS
DECLAREv_ename emp.ename%TYPE;v_sal emp.sal%TYPE;
BEGINSELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empnoi_empno;o_sal : v_sal;o_ename : v_ename;i_empno : 99666;--RAISE NOTICE 7788的姓名是:%工资是:%, v_ename, v_sal;
EXCEPTIONWHEN OTHERS THENRAISE NOTICE 发生异常;RAISE NOTICE 异常代码:%, SQLSTATE;RAISE NOTICE 异常信息:%, SQLERRM;
END; 使用匿名块调用存储过程时需要输出语句的原因在于存储过程的输出参数OUT参数不会自动显示其值它们是“静默”的即不会像函数返回值那样直接返回。因此为了查看存储过程的执行结果需要在调用存储过程的匿名块中使用输出语句来显示这些值。
如果 RAISE NOTICE 7788的姓名是:%, 工资是:%, v_ename, v_sal; 没有被注释掉那么在使用匿名块调用存储过程时不需要额外的输出语句来显示这些信息。因为 RAISE NOTICE 语句会直接将信息输出到数据库的日志或控制台。
有参存储过程(in out)
CREATE OR REPLACE PROCEDURE update_value (in out p_value NUMBER
) AS
BEGIN-- 对传入的值进行操作例如增加10p_value : p_value 10;
END;
/
调用存储过程 输出: 10 输出: 15
注意: raise notice是高斯的输出语句; DBMS_OUT_PUT.PUT_LINE是oracle的输出语句
有参存储过程(out) 函数function
函数的分类 用户自定义函数
它允许用户根据需要定义新的操作和计算, 通常使用PL/SQL编写可以用于执行各种任务
语法
CREATE OR REPLACE FUNCTION function_name (parameter_list)
RETURN return_datatype
IS/AS-- 变量声明
BEGIN-- 函数体RETURN return_value;
EXCEPTIONWHEN OTHERS THEN--异常处理
END;
parameter_list 的格式 : 参数类型 参数1 数据类型; 参数类型 参数2 数据类型......
注意: oracle中自定义函数不需要declare function_name函数的名称由用户自定义。常用函数名前缀: fun_ parameter_list函数的参数列表参数可以有IN、OUT、IN OUT等模式。一般只用 in, 默认in return_datatype函数返回值的数据类型。 IS/AS开始函数体的关键字。 BEGIN ... END函数的主体包含执行的PL/SQL代码。 返回值
函数通过RETURN语句返回一个值。如果函数声明为返回某个数据类型那么必须通过RETURN语句返回相应类型的值。
调用方式 自定义函数和存储过程的区别
1.自定义函数性能较差, 使用场景比存储过程少
2.自定义函数必须有返回值,存储过程没有,存储过程通过out参数向外传递数据
3.自定义函数的参数一般只使用in类型, 不使用out类型
4.自定义函数可以用一个值调用, 存储过程不可以
5.自定义函数在return处结束
6.自定义函数可以在sql中调用, 存储过程不可以 示例: 根据员工编号 获取姓名
CREATE OR REPLACE FUNCTION fun_get_ename(p_empno emp.empno%type)
RETURN varchar2
AS
v_ename varchar(30); --员工姓名
BEGINSELECT ename INTO v_ename FROM emp WHERE empnop_empno;RAISE NOTICE v_ename:%, v_ename;RETURN v_ename;
EXCEPTIONWHEN OTHERS THENRAISE NOTICE 发生异常了;RAISE NOTICE SQLSTATE:%, SQLSTATE;RAISE NOTICE SQLERRM:%, SQLERRM;
END;输出语句
oracle : DBMS_OUTPUT.PUT_LINE, 变量使用 和字符串使用 || 拼接, 注意单引号需要使用转义字符
高斯: RAISE NOTICE, 变量使用站位符%(示例是高斯输出)
函数调用示例
1.使用PLSQL调用
DECLAREv_name varchar(30);
BEGINv_name : fun_get_ename(7788);RAISE NOTICE 7788的姓名是%, fun_get_ename(7788);
END;2.在SQL中调用
SELECT emp.*, fun_get_ename(empno) FROM emp;
输出语句和return 输出语句的作用: 在函数执行过程中向客户端发送通知消息这对于调试和日志记录非常有用。示例输出语句的作用是将变量v_ename的值打印出来以便调试和查看 RETURN用于返回函数的结果是函数调用时返回给调用者的值。
return和out参数
return : 用于从函数中返回一个值。函数必须通过 RETURN 语句返回一个值且返回值的数据类型在函数定义时指定 out 参数:用于从函数中返回额外的值。OUT 参数允许函数返回多个值 函数可以有多个 OUT 参数。 只能在 PL/SQL 块中调用 示例 调用示例 IF v_status THEN 是合法的因为 v_status 是一个布尔变量IF 语句会根据它的值TRUE 或 FALSE来决定执行哪个分支
管理存储过程/自定义函数
修改存储过程/自定义函数
可以直接替换现有的存储过程/自定义函数 查看对象 查看源代码 删除存储过程/自定义函数 自定义函数也是
检查依赖关系(oracle) USER_DEPENDENCIES 视图显示了当前用户下对象的依赖关系
检查其他用户的依赖关系可以使用 ALL_DEPENDENCIES 视图(可以显示对象所有者: owner)
有DBA权限可以使用 DBA_DEPENDENCIES 视图检查整个数据库中的依赖关系
使用时, UPDATE_VALUE替换为需要检查依赖关系的对象名称
如果查询结果为空说明没有其他对象依赖于该存储过程。
删除存储过程的权限 存储过程的所有者。 具有 DROP ANY PROCEDURE 权限的用户。 如果返回结果不为空这意味着当前用户具有删除任何用户创建的存储过程和函数的权限
DROP ANY PROCEDURE 权限主要与存储过程相关但它也涵盖了对函数的删除权限。 因为在Oracle的权限体系中函数FUNCTION和存储过程PROCEDURE在很多情况下被视为类似的对象尤其是在权限管理方面。
