pl/sql 笔记

1.创建存储过程:

create or replace procedure dys is

begin

--执行部分   这行是注释

insert into ding values('ding','dys');

end;

查看存储过程的错误可以用show error;replace表示如果以此名为存储过程的要替换;

2.执行存储过程:

execute dys;或

3.一个简单的块:

begin

dbms_output.put_line('Hello World');--dbms_output是包,put_line()是包下的函数

end;

/

set serveroutput on;开启输出;

4.带有变量声明的块:

declare

v_ename varchar2(5);

v_sal number(7,2);

begin

select ename,sal into v_ename,v_sal from emp where empno='&aa';

dbms_output.put_line('雇员名'||v_ename||'工资'||v_sal);

end;

/

5.带有例外的块:

declare v_ename varchar2(5); v_sal number(7,2); begin select ename,sal into v_ename,v_sal from emp where empno=&aa; dbms_output.put_line('雇员名:'||v_ename||'工资:'||v_sal); exception   when no_data_found then  --表示异常     dbms_output.put_line('编号不存在'); end;

6.带参数存储过程:

create procedure dys(sname varchar2,newSal number) is begin   update emp set sal=newSal where ename=sname; end;

执行它:exec dys('scott',4500);

7.函数:

create function sp_fun(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;

函数的调用:

var abc number;

call sp_fun2('SCOTT') into:abc;

8.创建一个包:

create package sp_package is

procedure update_sal(name varchar2,newSal number);

function annual_income(name varchar2) return number;

end;

7创建包体:

create or replace package body sp_package is

procedure update_sal(name varchar2,newSal number)

is

begin

update emp set sal=newSal where ename=name;

end;

function annual_income(name varchar2)

return number is

annual_salary number;

begin

select sal*12+nvl(comm,0) into annual_salary from emp where ename=name;

return annual_salary;

end;

end;

8.调用包中的过程和函数

exec sp_package.update_sal('SCOTT',1200);

9.标量(scalar)变量的使用:

declare c_tax_rate number(3,2):=0.03; v_ename emp.ename%type;--意思为和emp.ename字段相同的数据类型

v_sal emp.sal%type;

v_tax_sal number(7,2); begin select ename,sal into v_ename,v_sal from emp where empno='&aa'; v_tax_sal:=v_sal*c_tax_rate; dbms_output.put_line('姓名:'||v_ename||'工资:'||v_sal||'税:'||v_tax_sal); end;

10.复合(composite变量的使用,它用于存放多个值;

declare type emp_record_type is record(name emp.ename%type,salary emp.sal%type,title emp.job%type);--建立一个pl\sql记录类型，是复合变量的一种 sp_record emp_record_type; begin   select ename,sal,job intosp_record   from emp where empno=7788; dbms_output.put_line('员工名:'||sp_record.name); end;

11.复合(composite变量的使用,它用于存放多个值;

declare type sp_table_type is table of emp.ename%type index by binary_integer;--pl\sql表类型数据，红色部分表示table存放的是emp.ename类型的数据 sp_table sp_table_type; begin   select ename into sp_table(0) from emp where empno=7788; dbms_output.put_line('员工名:'||sp_table(0)); end;

12.参照变量,其分为游标变量和对象类型变量

declare type sp_emp_cursor is ref cursor; test_cursor  sp_emp_cursor; v_ename emp.ename%type; v_sal emp.sal%type; begin   open test_cursor for select ename,sal from emp where deptno=&a;--对查询出来的结果集打开游标   loop     fetch test_cursor into v_ename,v_sal;--取出游标的值     exit when test_cursor%notfound;--退出游标的条件     dbms_output.put_line('名e字:'||v_ename||'工资'||v_sal);    end loop; end;

13.if then

create 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+sal*10% where ename=spName;   end if; end;

执行:exec sp_pro6('SCOTT');

14.if then else

create or replace procedure sp_pro6(spName varchar2) is v_sal emp.comm%type; begin   select sal into v_sal 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;

15.if then elsif then else

create 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;

执行exec sp_pro6(7788);

16.loop循环:这种循环至少执行一次

create or replace procedure sp_pro6(spName varchar2) is v__num number:=1; begin   loop     insert into users values(v_num,spName);     exit when v_num=10;     v_num:=v_num+1;   end loop; end;

执行:exec sp_pro6('dys');循环十次

17.while循环

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;

18.go to

declare i int :=1; begin   loop     dbms_output.put_line('i='||i);     if i10 then       goto end_loop;       end if;       i:=i+1;    end loop;       <<end loop>> dbms_output.put_line('循环结束'); end;

19.有返回值的存储过程

create or replace procedure sp_pro8 (spNo in number,spName out varchar2) is begin   select ename into spName form emp where empno=spno; end;

20.有多个返回值的存储过程

create or replace procedure sp_pro8 (spNo in number,spName out varchar2,spSal out number,spJob out varchar2) is begin   select ename,sal,job into spName,spSal,spJob form emp where empno=spno; end;

21.返回值为结果集的存储过程:

新建一个包:

create or replace package testPackage as type test_cursor is ref cursor; end testPackage;

创建过程:

create or replace procedure sp_pro9(spNo in number,p_cursor out testPackage.test_cursor) is begin   open p_cursor for select * from emp where deptno=spNo; end;

22.分页的存储过程

create or replace procedure fenYe( tableName in varchar2, myPageSize in number, pageNow in number, myRowCount out number, myPageCount out number, p_cursor out testPackage.test_cursor) is v_sql varchar2(1000); v_begin number:=(pageNow-1)*myPageSize+1; v_end number:=pageNow*myPageSize; begin   v_sal:='select * from (select a1.*,rownum rn from (select * from '||tableName||' order by sal) a1 where rownum<='||v_end||')where rn>='||v_begin; open p_cursor for v_sql; v_sql:='select count(*) from '||tableName; execute immediate v_sql into myRowCount; if mod(myRowCount,myPageSize)=0 then   myPageCount:=myRowCount/myPageSize; else   myPageCount:=myRowCount/myPageSize+1; end if; --close p_cursor; end;

23.例外

create or replace procedure sp_pro6(spNo number) is v_sal emp.sal%type; begin   select sa into v_sal from emp where empno=spNo;   case     when v_sal<1000 then       update emp set sal=sal+100 where empno=spNo;     when v_sal<2000 then       update emp set sal=sal+200 where empno=spNo;    end case; exception   when case_not_found then     dbms_output.put_line('case语句没有和'||v_sal||'匹配'); end;

 

 

declare    cursor emp_cursor is select ename,sal from emp; begin   open emp_cursor;   for emp_record1 in emp_cursor loop;   dbms_output.put_line(emp_record1.ename);   end loop;   exception     when cursor_already_open then       dbms_output.put_line('游标已经打开'); end;

 

 

begin   insert into dept values(10,'公关部','北京'); exception   when dup_val_on_index then     dbms_output.put_line('deptno不能有重复值'); end;

 

declare cursor emp_cursor is select ename sal from emp; emp_record emp_cursor%rowtype; begin   fetch emp_cursor into emp_record;   dbms_output.put_line(emp_record.ename);   close emp_cursor;   exception     when invalid_cursor then       dbms_output.put_line('请检查游标是否打开'); end;  

 

 

begin   update emp set sal=sal+'loo'--loop exception   when invalid_number then     dbms_output.put_line('请输入正大的数字'); end;

declare v_enam emp.ename%type; begin   select ename into v_ename from emp; exception   when too_many_rows then     dbms_output.put_line('返回了多行'); end;

 

2/0

会发出zero_divide异常

declare v_ename varchar2(5); begin   select ename into v_ename from emp where empno=&nol;   dbms_output.put_line(v_ename);   exception     when value_error then       dbms_output.put_line('变量大小不匹配'); end;

 

 

自定义例外:

create or replace procedure ex_test(spNo number) is myex exception begin   update emp set sal=sal+1000 where empno=spNo;   if sql%notfound then  --表示没有更新     raise myex;  --触发例外   end if;   exception     whe myex then     dbms_output.put_line('没有更新任何用户'); end;