《oracle笔记12 case when plsql编程 存储过程 执行函数 定义变量及赋值》由会员分享，可在线阅读，更多相关《oracle笔记12 case when plsql编程 存储过程 执行函数 定义变量及赋值（7页珍藏版）》请在金锄头文库上搜索。
1、oracleoracle 笔记笔记 1212 CASECASE WHENWHEN plsqlplsql 编程编程 存储过程存储过程 执行函数执行函数 定义变定义变量及赋值量及赋值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 没有
2、 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
3、_datefrom 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.RIS
4、K5_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_B
5、AL_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); -动态调用存储过程 SQLV_PRSQL := BEGIN PFS_BUSINESS_C
6、KSET_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 * fro
7、m 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 参数值在包里建存储过程或函数的时候包和包体中的存储过程参数一定要一致创建存储过程cre
8、ate proceduce htp1 isbegin-执行部分insert into emp values(1,1)end;/或者 create or replace proceduce 可以写这个重名的就替换了有错误 show error调用存储过程1.exec 过程名（参数）2.call 过程名（参数）plsql 编程基础单位位块 blockdeclear定义常量 变量 游标 例外begin执行部分 sql 语句exception例外处理部分 处理各种错误过程函数触发器包单行注释-多行注释/./规范 变量 v_ 常量 c_ 游标_cursor 定义例外 e_-案例一set serverou
9、tput on/off 打开关闭输出选项 管是否输出值 dbms_output.put_line(hello,word)begindbms_output.put_line(hello,word); -dbms_output 是包 点后面是过程或函数这里是过程end;/-案例二declare v_ename varchar2(5);-定义字符串变量beginselect ename into v_ename from emp where empno= -into v_ename 表示把查出来的 ename 赋值给了变量dbms_output.put_line(雇员名:|v_ename);-异常处
10、理exception when no_data_found then dbms_output.put_line(朋友，你的身份编号输入有误！);end;/-案例三create procedure sp_pro1(spName varchar2,newSal number) isbeginupdate emp set sal=newSal where ename=spName;end;/执行 exec sp_pro1(SCOTT,3333) 或者 call在 java 中执行存储过程package com.htp.dao;import java.sql.DriverManager;import
11、java.sql.*;public class TestOraclePro /* param args*/public static void main(String args) / TODO Auto-generated method stubtry/1.加载驱动Class.forName(“oracle.jdbc.driver.OracleDriver“);/2.得到连接Connection ct=DriverManager.getConnection(“jdbc:oracle:thin:127.0.0.1:1521:orcl“,“scott“,“tiger“);/3.创建 callabl
12、eStatemenetCallableStatement 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
13、) returnnumber is spSal number(7,2);beginselect sal*12+nvl(comm,0)*12 into spSal from emp where ename=spName;return spSal;end;var aaa number 定义个变量call test（SCOTT） into:aaacall hu_sequence() into:aaa;没参数的函数定义变量(NULL 值不可赋值给绑定变量 都只在当前有效)define aaa=111 类型都是 char 只是在当前 session 环境中有效 set define on 和 set define off define 定义之后 aaa 就不用再手动输入了 UNDEFINE 清除给绑定变量赋值1.begin:ax:=111; -前提 ax 被 var ax numberend;/2.exec :ax:=111;查看变量值 print ax查看变量类型 var ax
