Oracle PL/SQL学习笔记
Oracle PL/SQL 学习笔记
-- 案例create or replace procedure sp_pro2 isbegin-- 执行部分delete from mytest where name ='name1';end;-- 最简单的块begin qdbms_output.put_line('hello world');end;--- 有定义和执行部分的块declare --定义变量 v_ename varchar2(5); v_sal number(7,2);begin -- 执行部分 select ename,sal into v_ename,v_sal from emp where empno = &no; /* select sal into v_sal from emp where empno = &no;*/ dbms_output.put_line('employeer: ' || v_ename || ' salary: ' || v_sal);exception -- 异常处理 when no_data_found then dbms_output.put_line('朋友，请重新输入编号');end;/-- 案例4create procedure sp_pro3(spName varchar2,newSal number) isbegin --执行部分-- 根据用户名去修改工资 update emp set sal = newSal where ename =spName;end;-- 函数案例-- 输入雇员的姓名，返回该雇员的年薪create function sp_fun2(spName varchar2) return number is yearSal number(7,2);begin--执行部分select sal * 12 + nvl(comm,0) * 12 into yearSal from emp where ename = spName;return yearSal;end;-- 创建包-- 创建一个包 sp_package-- 声明该包有一个过程-- 声明该包有一个函数create package sp_package is procedure update_sal(name varchar2,newsal number); function annual_income(name varchar2) return number;end;-- 给包 sp_package 实现包体create or replace package body sp_package isprocedure update_sal(name varchar2,newsal number)isbegin update emp set sal = newsal where ename = name;end;function annual_income(name varchar2)return number isannual_salary number;begin select sal*12+nvl(comm,0) into annual_salary from emp where ename = name; return annual_salary;end;end;-- 案例declare c_tax_rate number(3,2):=0.03; --用户名 v_ename emp.ename%type; v_sal number(7,2); v_tax_sal number(7,2);begin --执行部分 select ename,sal into v_ename,v_sal from emp where empno=&no; --计算所得税 v_tax_sal:=v_sal*c_tax_rate; --输出 dbms_output.put_line('姓名:' || v_ename ||',工资:' || v_sal || '所得税:'|| v_tax_sal);end;--pl/sql记录实例declare --定义一个pl/sql记录类型emp_record_type， -- 类型保护三个数据name，salary，title type emp_record_type is record( name emp.ename%type, salary emp.sal%type, title emp.job%type ); -- 定义了一个sp_record变量，这个变量类型是emp_record_type sp_record emp_record_type;begin select ename,sal,job into sp_record from emp where empno=7788; dbms_output.put_line('员工名:' || sp_record.name || ',工资是' || sp_record.salary);end;-- pl/sql 表实例declare-- 定义一个pl/sql表类型sp_table_type,该类型是用于存放emp.ename%type-- index by binary_integer表示下标是整数type sp_table_type is tableof emp.ename%type index by binary_integer;-- 定义一个sp_table变量，这个变量的类型是sp_table_typesp_table sp_table_type;begin select ename into sp_table(0) from emp where empno=7788; dbms_output.put_line('员工名：' || sp_table(0));end;-- pl/sql cursor实例declare-- 定义游标类型type sp_emp_cursor is ref cursor;-- 定义一个游标变量test_cursor sp_emp_cursor;-- 定义变量v_ename emp.ename%type;v_sal emp.sal%type;begin-- 执行部分-- 把test_cursor 和一个select结合open test_cursor for select ename,sal from emp where deptno=&no;--循环取出loop fetch test_cursor into v_ename,v_sal; -- 判断工资高低，决定是否更新 -- 判断是否test_cursor为空 exit when test_cursor%notfound; dbms_output.put_line('名字：'||v_ename||',工资:'|| v_sal);end loop;end;-- procedure testcreate or replace procedure sp_pro6(spName varchar2) is --定义 v_sal emp.sal%type;begin --执行 select sal into v_sal from emp where ename=spName; --判断 if v_sal <2000 then update emp set sal = sal*1.1 where ename=spName; end if; end;-- procedure test2create or replace procedure sp_pro6(spName varchar2) is --定义 v_comm emp.comm%type;begin --执行 select comm into v_comm from emp where ename=spName; --判断 if v_comm != 0 then update emp set comm =comm + 100 where ename=spName; else update emp set comm = comm + 200 where ename=spName; end if; end;-- procedure if elsifcreate or replace procedure sp_pro6(spNo number) is -- 定义 v_job emp.job%type;begin -- 执行 select job into v_job from emp where empno = spNo; if v_job = 'PRESIDENT' then update emp set sal = sal + 1000 where empno = spNo; elsif v_job = 'MANAGER' then update emp set sal = sal + 500 where empno = spNo; else update emp set sal = sal + 200 where empno = spNo; end if;end;-- loop testcreate or replace procedure sp_pro6(spName varchar2) is -- 定义 :=表示赋值 v_num number:=1;beginloop insert into users values(v_num,spName); -- 判断退出条件 exit when v_num = 10; -- 自增 v_num:= v_num +1;end loop;end;-- while test;create or replace procedure sp_pro6(spName varchar2) is -- 定义 :=表示赋值 v_num number:=11;begin while v_num <=20 loop -- 执行 insert into users values(v_num,spName); v_num:=v_num+1; end loop;end;-- goto testdeclare i int := 1;begin loop dbms_output.put_line('i=' || i); if i = 10 then goto end_loop; end if; i := i + 1; end loop; <<end_loop>> dbms_output.put_line('end loop');end;-- 创建表create table book( bookId number(4,2), bookName varchar2(50), publishHouse varchar2(50));-- 编写过程-- in 表示输入参数, 默认为in-- out 表示输出参数create or replace procedure sp_pro7(spBookId in number, spbookName in varchar2, sppublishHouse in varchar) isbegin insert into book values(spBookId,spbookName,sppublishHouse);end; -- 有输入和输出的存储过程create or replace procedure sp_pro8(spNo in number,spName out varchar2, spSal out number,spJob out varchar2) isbegin select ename,sal,job into spName,spSal,spJob from emp where empno = spNo;end; -- 返回结果集的过程-- 1.创建一个包,在该包中定义一个类型test_cursor,一个游标create or replace package testpackage as type test_cursor is ref cursor;end testpackage;-- 2. 创建存储过程create or replace procedure sp_pro9(spNo in number, p_cursor out testpackage.test_cursor) isbegin open p_cursor for select * from emp where deptno=spNo;end;-- 3. 如何在java中调用-- oracle 的分页select rownum rn,t1.* from (select * from emp) t1select rownum rn,t1.* from (select * from emp) t1 where rownum < 10;-- 在分页时，大家可以把下面的sql语句当成模板使用select * from (select rownum rn,t1.* from (select * from emp) t1 where rownum <= 10)where rn >= 6;-- 开发一个包create or replace package testPackage as type test_cursor is ref cursor;end testPackage;-- 开始编写分页的过程create or replace procedure fenye(tableName in varchar2, pageSize in number, -- 一页现实记录数 pageNow in number, myrows out number, -- 总记录数 myPageCount out number, -- 总页数 p_cursor out Testpackage.test_cursor -- 返回记录集 ) is -- 定义部分 -- 定义sql语句 字符串 v_sql varchar2(1024); -- 定义两个整数 v_begin number:=(pageNow-1)*pageSize + 1; v_end number:=pageNow*pageSize; begin -- 执行部分 v_sql:='select * from (select rownum rn,t1.* from (select * from '|| tableName||' order by sal) t1 where rownum <= '||v_end||') where rn >= '||v_begin||''; -- 把游标和sql语句关联起来 open p_cursor for v_sql; -- 计算myRows和myPageCount -- 组织一个SQL v_sql:='select count(*) from ' || tableName; -- 执行SQL，并把返回的值，赋给myRows execute immediate v_sql into myRows; -- 计算myPageCount if mod(myRows,pageSize)=0 then myPageCount := myRows/pageSize; else myPageCount := myRows/pageSize + 1; end if; -- 关闭游标 -- close p_cursor; end; -- 使用java测试 -- 新的需要，排序 -- 例外案例 declare -- 定义 v_name emp.ename%type; begin select ename into v_name from emp where empno = &no; dbms_output.put_line('name: '|| v_name); exception when no_data_found then dbms_output.put_line('no not exist'); end; -- 自定义例外 create or replace procedure ex_test(spNo number) is -- 定义一个例外 myex exception; begin -- 更新用户sal update emp set sal = sal + 1000 where empno= spNo; -- sql%notfound 这是表示没有update -- raise myex; 触发myex if sql%notfound then raise myex; end if; exception when myex then dbms_output.put_line('not update success'); end; -- 创建视图，把emp表薪水sal<1000 的雇员映射该视图(view) create view myview as select * from emp where sal < 1000; create view myview as select emp.empno,emp.ename,dept.dname from emp,dept where emp.deptno = dept.deptno ?