oracle PL、SQL（二）

  1 --1，参数   in：表示输入类型，可以省略 ；out：输出类型不能省略----------
  2 
  3 ----案例1：编写一个过程，可以输入雇员的编号，返回该雇员的姓名。 
  4 create or replace procedure  proc_getEnameByEmpno( v_empno in number,v_ename out varchar2 )
  5 is
  6 begin
  7 --根据输入的编号查找出名字赋值给输出的变量
  8  select ename into v_ename from emp where empno=v_empno;
  9 end;
 10 /
 11 ----调用形式------打印输出----前提: set serveroutput on---------
 12 declare
 13 vename  varchar2(30);
 14 begin  
 15 proc_getEnameByEmpno(9903,vename);
 16 dbms_output.put_line(vename);  
 17 end;
 18 
 19 
 20 ----案例2：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。-----------
 21 create or replace procedure  proc_getEnameByEmpno( v_empno in number,v_ename out varchar2,v_job out varchar2 )
 22 is
 23 begin
 24  select ename,job into v_ename,v_job from emp where empno=v_empno;
 25 end;
 26 /
 27 
 28 ----调用形式---------------------
 29 declare
 30 v_ename  varchar2(30);
 31 v_job varchar2(30);
 32 begin  
 33 proc_getEnameByEmpno(9903,v_ename,v_job);
 34 dbms_output.put_line('姓名:'||v_ename|| '  岗位 ：'||v_job);
 35 end;
 36 
 37 
 38 
 39 ----案例2的。。。。JAVA程序调用--------------------------
 40 /*
 41 try {
 42     Class.forName("oracle.jdbc.OracleDriver");
 43     String url="jdbc:oracle:thin:@127.0.0.1:1521:orcl";
 44 Connection conn=DriverManager.getConnection(url, "scott", "tiger");
 45 
 46 CallableStatement  cs=conn.prepareCall("{call proc_getEnameByEmpno(?,?,?)}"); //调用存储过程
 47 
 48     cs.setInt(1, 9903);//将第一个参数赋值
 49     //注册操作
 50     cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);//将第二个参数注册为ORACLE的varchar 说明是一个out类型的数据
 51 cs.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR);//将第三个参数注册为ORACLE的varchar 说明是一个out类型的数据
 52 
 53     cs.execute();//执行存储过程
 54     String ename=cs.getString(2);//取出第二个参数的值
 55     String job=cs.getString(3);//取出第三个参数的值
 56 System.out.println("姓名："+ename+" , 岗位:"+job);
 57 
 58     cs.close();
 59     conn.close();
 60         } catch (Exception e) {
 61         }
 62 */
 63 
 64 
 65 
 66 --2，创建视图-view-------------------------------------------- 
 67 
 68 --2.1、as  用法 ：  用在三个地方:视图 、栏目别名 、 包  ------------
 69 /*
 70 create view view_emp 
 71 as 
 72 select empno,ename,sal from emp;
 73 -------------------------------------------------------------
 74 select ename as 姓名 , sal as 工资 from emp;
 75 */
 76 
 77 
 78 --2.2、包：package --游标：cursor-------------------------
 79 
 80 create or replace package package_emp ---创建包
 81 as
 82 type emp_cursor is ref cursor;  ---定义游标
 83 end package_emp;
 84 
 85 
 86 ----案例3：------------------------------
 87 create or replace procedure  proc_getResut(v_deptno  in  number ,v_cursor out package_emp.emp_cursor   )
 88 is
 89 begin
 90    open  v_cursor for --打开游标，将结果放进去
 91    select * from emp where deptno=v_deptno;
 92 end;
 93 /
 94 /* ------Java 调用包---------------------
 95 try {
 96     Class.forName("oracle.jdbc.OracleDriver");
 97 String url="jdbc:oracle:thin:@127.0.0.1:1521:orcl";
 98 Connection conn=DriverManager.getConnection(url, "scott", "tiger");
 99 
100    //调用存储过程
101 CallableStatement  cs=conn.prepareCall("{call proc_getResut(?,?)}"); 
102 
103       cs.setInt(1, 30);//将第一个参数赋值
104       //注册操作
105 //将第二个参数注册为ORACLE的varchar 说明是一个out类型的数据
106      cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);  
107     cs.execute();//执行存储过程
108 //getObject() 取游标所对应的结果集      
109    ResultSet rs=(ResultSet) cs.getObject(2);       
110 while (rs.next()) {
111     System.out.println( 
112 "   姓名："+rs.getString("ename")+
113 "  工资："+rs.getDouble("sal")+
114 "  部门号："+rs.getInt("deptno"));
115     }
116   cs.close();
117   conn.close();
118 } catch (Exception e) {
119 }
120 */
121 
122 
123 --3，分页------ 行号 ：rownum------------------------
124 
125 ----案例4：在员工表信息后面加上行号
126 select e.* , rownum as num  from emp e;
127 
128 ----案例5：将 加了行号的结果 看成新的表----查询9至12行的数据
129 select * from (select e.*  ,rownum as num  from emp e)  where num between  9  and 12  
130 
131 ----案例6：
132 ------（1）建立包 和游标 用来指向结果集
133  create or replace package   pagesPackage 
134  as
135  type  pages_cursor is ref cursor;
136  end pagesPackage;
137  
138 ------（2.1）建立存储过程
139 create or replace procedure  fenye(
140 tablename in varchar2,---表名
141 pagesize  in number,-----每页条数
142 pageNow   in number,-----当前页
143 totalNum     out number,----总条数
144 totalPage    out number,-----总页数
145 result_cursor out pagesPackage.pages_cursor ---查询结果游标
146 )
147 is
148 v_sql varchar2(500);---拼装SQL语句
149 v_begin  number:=(pageNow-1)*pagesize+1;---开始的位置
150 v_end    number:=pageNow*pagesize;---结束的位置
151 begin
152 v_sql:='select * from 
153 (select t.*,rownum as num from 
154 (select * from '|| tablename ||') t) 
155 where num between'||v_begin||' and '||v_end;
156 open result_cursor for v_sql;---将查询的结果存入游标中
157 v_sql:='select count(*) from  '||tablename;---查询总条数的SQL语句
158 execute immediate v_sql into totalNum;--立即执行 将结果赋值给 toalNum  的到总条数
159   if  mod(totalNum,pagesize)=0 then  
160      totalPage:=totalNum/pagesize;
161    else
162       totalPage:=totalNum/pagesize+1;
163    end if;
164 end;
165 /
166 
167 
168 ----案例7：按照员工编号降序的方式 找到30号部门中  第4到第6条的数据
169 select *  from (select t.*,rownum as num from (select * from emp where  deptno=30  order by empno desc) t) where  num between  4  and 6 ;
170 
171 
172 ---（2.2） 建立存储过程  增加 where 和  order by 条件
173 create or replace procedure  fenye(
174 tablename in varchar2,---表名
175 pagesize  in number,-----每页条数
176 pageNow   in number,-----当前页
177 wheres    in varchar2,---查询条件
178 orderby   in varchar2,---排序
179 totalNum     out number,----总条数
180 totalPage    out number,-----总页数
181 result_cursor out pagesPackage.pages_cursor ---查询结果游标
182 )
183 is
184 v_sql varchar2(500);---拼装SQL语句
185 v_begin  number:=(pageNow-1)*pagesize+1;---开始的位置
186 v_end    number:=pageNow*pagesize;---结束的位置
187 v_where varchar2(30):='';
188 v_orderby varchar2(30):='';
189 begin
190   if wheres is not null then
191     v_where:=' where  ' ||wheres;
192   end if;
193 
194    if orderby  is not null then
195      v_orderby:=' order by  '||orderby;
196    end if;
197 v_sql:='select *   from    (select t.*,rownum as num from (select * from '||tablename||' '||v_where||'  '||v_orderby ||') t) where   num between '||v_begin||'  and  '||v_end;
198 open result_cursor for v_sql;---将查询的结果存入游标中
199 v_sql:='select count(*) from '||tablename||v_where ;---查询总条数的SQL语句
200 execute immediate v_sql into totalNum;--立即执行 将结果赋值给 toalNum  的到总条数
201   if  mod(totalNum,pagesize)=0 then  
202      totalPage:=totalNum/pagesize;
203    else
204       totalPage:=totalNum/pagesize+1;
205    end if;
206 end;
207 /
208 /*
209 try {
210     Class.forName("oracle.jdbc.OracleDriver");
211 String url="jdbc:oracle:thin:@127.0.0.1:1521:orcl";
212 Connection conn=DriverManager.getConnection(url, "scott", "tiger");
213   CallableStatement  cs=conn.prepareCall("{call fenye(?,?,?,?,?,?,?,?)}"); //调用存储过程
214     cs.setString(1, "emp");//填入表名
215     cs.setInt(2, 3);//每页4条
216     cs.setInt(3, 2);//第三页
217     cs.setString(4, " deptno=30 ");//where 条件
218     cs.setString(5, " sal  desc  ");//排序
219     cs.registerOutParameter(6, oracle.jdbc.OracleTypes.INTEGER); // 注册  输出总条数
220     cs.registerOutParameter(7, oracle.jdbc.OracleTypes.INTEGER); //注册  输出总页数
221     cs.registerOutParameter(8, oracle.jdbc.OracleTypes.CURSOR);  //注册  输出结果集
222     cs.execute();//执行存储过程
223   System.out.println("总条数:"+cs.getInt(6));
224   System.out.println("总数页:"+cs.getInt(7));
225   System.out.println("打印第"+2+"页的数据");
226    ResultSet rs=(ResultSet) cs.getObject(8);    //getObject() 取游标所对应的结果集      
227    while (rs.next()) {
228     System.out.println( "姓名：  "+rs.getString("ename")+"  工资："+rs.getDouble("sal")+"  部门号:"+rs.getInt("deptno"));
229     }
230   cs.close();
231   conn.close();
232 } catch (Exception e) {
233 }
234 */