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')='19900101' then '19900101' else '没有' 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';
查看当前数据库有多少包 select * from user_objects where object_type='PACKAGE';
存储过程的参数中有三种参数类型 in out inout
执行有OUT参数才存过程 out参数必须得给一个变量 var v1 varchar2(100); exec htp_1.htp_p1('SMITH',:v1); 注意大小写 也可以给变量先赋值 exec :v1:=1; 成功后返回out参数值
在包里建存储过程或函数的时候包和包体中的存储过程参数一定要一致

创建存储过程
create proceduce htp1 is
begin
--执行部分
insert into emp values('1','1

')
end;
/
或者create or replace proceduce 可以写这个重名的就替换了
有错误 show error
调用存储过程
1.exec 过程名（参数）
2.call 过程名（参数）

plsql编程基础单位位块 block
declear
定义常量 变量 游标 例外
begin
执行部分 sql语句
exception
例外处理部分 处理各种错误

过程
函数
触发器
包
单行注释--
多行注释/..../

规范 变量v_ 常量c_ 游标_cursor 定义例外 e_

--案例一
set serveroutput on/off 打开关闭输出选项 管是否输出值 dbms_output.put_line('hello,word')
begin
dbms_output.put_line('hello,word'); --dbms_output是包 点后面是过程或函数这里是过程
end;
/

--案例二
declare
v_ename varchar2(5);--定义字符串变量
begin
select ename into v_ename from emp where empno=&no; --into v_ename 表示把查出来的ename赋值给了变量
dbms_output.put_line('雇员名:'||v_ename);
--异常处理
exception
when no_data_found then
dbms_output.put_line('朋友，你的身份编号输入有误！');
end;
/

--案例三
create procedure sp_pro1(spName varchar2,newSal number) is
begin
update emp set sal=newSal where ename=spName;
end;
/
执行 exec sp_pro1('SCOTT',3333) 或者call


在java中执行存储过程
package com.htp.dao;

import java.sql.DriverManager;
import java.sql.*;
public class TestOraclePro {

/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
try{
//1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.得到连接
Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
//3.创建callableStatemenet
CallableStatement cs=ct.prepareCall("{call sp_pro1(?,?)}");
//4.执行
cs.setString(1,"SMITH");
cs.setInt(2,11);
//5.执行
cs.execute();
//关闭资源
cs.close();
ct.close();
}
catch(Exception e){
e.printStackTrace();
}

}

}

存储过程没有返回值 但可以通过参数传返回值 函数有返回值
函数 过程 包 包体 触发器


变量的定义及赋值
show error 显示错误信息
create function test(spName varchar2) return
number is spSal number(7,2);
begin
select sal*12+nvl(comm,0)*12 into spSal from emp where ename=spName;
return spSal;
end;

var aaa number 定义个变量
call test（'SCOTT'） into:aaa
call hu_sequence() into:aaa;没参数的函数

定义变量(NULL值不可赋值给绑定变量 都只在当前有效)
&aaa
var aaa number;
define aaa=111 类型都是char 只是在当前session环境中有效 set define on和set define off define定义之后 @aaa就不用再手动输入了 UNDEFINE清除

给绑定变量赋值
1.begin
:ax:=111; --前提ax被 var ax number
end;
/
2.exec :ax:=111;

查看变量值 print ax

查看变量类型 var ax