oracle笔记12 CASE WHEN plsql编程 存储过程 执行函数 定义变量及赋值.docVIP

oracle笔记12 CASE WHEN plsql编程 存储过程 执行函数 定义变量及赋值 create role htp not identified;创建角色 可以写密码 角色授权grant create session to htp with admin option; 把角色授权给用户grant htp to scott; case when 三种用法 一种select case when to_char(HIREDATE,yyyymmdd)thenelse 没有 end from htp_emp; 二种select (case when substr(data_date,5,2)04 then (substr(data_date,1,4) || 0401) when substr(data_date,5,2)07 then (substr(data_date,1,4) || 0701) when substr(data_date,5,2)10 then (substr(data_date,1,4) || 1001) when substr(data_date,5,2)13 then (substr(data_date,1,4) || 0101) end) data_date from h; 三种sum(case when B.RISK5_FLAG=1 or B.RISK5_FLAG=2 then B.FUNDING_BAL end)/10000 as FUNDING_BAL_NOR, sum(case when B.RISK5_FLAG=1 then B.FUNDING_BAL end)/10000 as FUNDING_BAL_1, sum(case when B.RISK5_FLAG=2 then B.FUNDING_BAL end)/10000 as FUNDING_BAL_2, sum(case when B.RISK5_FLAG=3 or B.RISK5_FLAG=4 or B.RISK5_FLAG=5 then B.FUNDING_BAL end)/10000 as FUNDING_BAL_BAD, sum(case when B.RISK5_FLAG=3 then B.FUNDING_BAL end)/10000 as FUNDING_BAL_3, sum(case when B.RISK5_FLAG=4 then B.FUNDING_BAL end)/10000 as FUNDING_BAL_4, sum(case when B.RISK5_FLAG=5 then B.FUNDING_BAL end)/10000 as FUNDING_BAL_5 查询scott拥有的角色 select * from dba_role_privs where GRANTEE =upper(scott); 查询角色 select * from dba_roles; 用plsql工具编程可以节约时间，安全性高 ，提高性能 数据库不用对sql进行编译了因为他已经打包好了一个过程 plsql缺点 移植性不好 （换数据库了） 节约数据库运行时间 1.sql语句优化尽可能的简练 2.把它写成模块即存储过程 动态调用存储过程 V_PRSQL VARCHAR2(100); --动态调用存储过程SQL V_PRSQL := BEGIN PFS_BUSINESS_CKSET_CTRL. || V_CHECK_PRC || --包名.存储过程名（参数） (:p1,:p2,:p3,:p4,:p5); END;; EXECUTE IMMEDIATE V_PRSQL USING IN P_BRANCH, P_USER, P_DATE, V_CHECK_NO, OUT P_ERRCODE; --执行语句 给多个参数赋值 1.v_sql:=select ..from 表 ; 2.execute immediate v_sql using parm1,parm2,parm3; 如果知道确切的 直接 包名.存储过程名(参数); 就可以 查看当前数据库有多少存储过程 select * from user_objects where object_type=PROCEDURE; 查看当前数据库有多少包