pl/sql编程

最新推荐文章于 2024-09-14 19:00:09 发布

tianshi10928

最新推荐文章于 2024-09-14 19:00:09 发布

阅读量594

点赞数 1

分类专栏： oracle学习文章标签：存储 sql table oracle 数据库 exception

本文链接：https://blog.csdn.net/tianshi10928/article/details/5694561

版权

oracle学习专栏收录该内容

8 篇文章 0 订阅

订阅专栏

创建存储过程：create procedure 存储过程名 is

begin

执行部分

end;

/系统编译过程

若存储过程已存在，则可以create or replace procedure 存储过程名，这样就可以替代已存在存储过程，若不存在再新建存储过程。

查看错误信息：show error

调用存储过程：1，exec 存储过程名(参数值)；2，call 存储过程名(参数值)。

call调用的存储过程必须有参数，否则不能执行。exec可以执行任意存储过程。

编写规范：单行注释 --

多行注释 /*.....*/

定义变量建议用v做前缀

定义常量建议用c做前缀

定义游标建议用_cursor做后缀

定义例外建议用e做前缀

pl/sql块由三个部分构成：定义部分、执行部分、例外处理部分

declare

/*定义部分------定义常量、变量、游标、例外、复杂数据类型*/（可选）

begin

/*执行部分------要执行的pl/sql语句和sql语句*/（必选）

exception

/*例外处理部分-------处理运行的各种错误*/（可选）

end;

创建函数：create function 函数名(参数值)

return 类型 is

返回值类型;

begin

执行部分;

return 返回名;

end;

在sql/plus中调用函数：定义变量 var 变量名变量类型

调用函数 call 函数名(参数值) into:变量名;

打印返回值 print 变量名

创建包：创建的包内有一个存储过程和一个函数的声明

create package 包名称 is

procedure 存储过程名(参数值);

function 函数名(参数值) return 返回类型;

end;

创建包体：创建包体，包括存储过程和函数的具体实现过程

create package body 包体名 is

procedure 存储过程名(参数值) is

begin

执行部分;

end;

funtion 函数名(参数值) return 类型

is 返回值类型;

begin

执行部分;

return 返回值;

end;

调用包内的存储过程：exec/call 包名称.存储过程名(要传入的值);

调用包内的函数：var 变量名类型;

exec/call 包名称.函数名(要传入的值) into:变量名;

print 变量名

pl/sql中的定义类型：标量类型 scalar、复合类型composite、参照类型reference、lob(large object)

在写oracle存储过程或函数的时候，若提示错误为“ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小”，如果你定义的变量类型为 varchar2(100); 那么该字符串缓冲区的大小就是100。

    %type类型：将该变量的类型为与某表某字段的类型一致，例如：emp.ename%type
    pl/sql记录类型:declare
                          type 类型名称1 is record(变量名1 表名.列名%type....);
                          变量名类型名称1;
                          begin
                          执行部分
                          dbms_output.put_line(变量名.变量名1);
                          end;
           给人感觉是先定义一个大的父变量，其中包括多个子变量，如果需要该子变量时则需要这样调用：父变量.子变量，类似于块内存储过程或函数的调用。
    pl/sql表变量:declare
                       type 表变量类型 is table of 表名.列名%type index by binary_integer;
                       变量名表变量类型;
                       begin
                       执行部分;
                       end;
           若执行部分定义的是变量名(0),则输出部分也应该是变量名(0),前后对应。
    set serveroutput on：设置查阅结果可见。
    输入员工号，显示雇员姓名、工资、个人所得税(0.03)
           declare
           v_ename varchar2(10);
           v_sal number(7,2);
           c_tax_rate number(3,2):=0.03;
           v_tax_sal number(7,2);
           begin
           select ename,sal,sal-sal*c_tax_rate into v_ename,v_sal,v_tax_sal from emp where empno=&no;
           dbms_output.put_line('员工姓名是：'||v_ename ||' 工资是：'||v_sal||' 税后工资是：'||v_tax_sal );
           end;

复合类型

    pl/sql记录类型
           declare
           type emp_record_type is record(name emp.ename%type,sal emp.sal%type,job emp.job%type);
           sp_record emp_record_type;
          begin
          select ename,sal,job into sp_record from emp where empno=&no;
           dbms_output.put_line('用户名是：'||sp_record.name||' 工资是：'||sp_record.sal||' 职位是：'||sp_record.job);
           end;

    pl/sql表类型
             declare
             type sp_table_type is table of emp.ename%type index by binary_integer;
             table_record sp_table_type;
             begin
             select ename,job,comm into table_record(0) from emp where empno=&no;
             dbms_output.put_line('用户名是：'||table_record(0));
             end;

     游标变量
             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 empno=&no;
             loop
             fetch test_cursor into v_ename,v_sal;
             exit when test_cursor%notfound;
             dbms_output.put_line('用户名是：'||v_ename||' 薪水是:'||v_sal);
             end loop;
             close test_cursor;
             end;
    今天早上登录pl/sql时发现提示“ora-12547:TNS:丢失连接”，在网上找了找发现别人都是重装或者打补丁之类修改了主机名导致不能连接，可是我的什么也没做，，于是自己把listener和需要用的数据库实例重启了一下就可以了，呵呵！
    控制结构:if...then；if...then...else;if...then...elsif(中间一定不要有e)...else.
    1，if...then案例：输入雇员名，若该雇员工资低于2000则加薪10%
     create or replace procedure myspro2(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;
     2，
     create or replace procedure myspro3(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=200 where ename=spname;
     end if;
     end;
     3，
     create or replace procedure myspro4(spempno number) is
     v_job emp.job%type;
     v_sal emp.sal%type;
     begin
     select job into v_job from emp where empno=spempno;
     if v_job ='PRESIDENT' then
     update emp set sal=sal+1000 where empno=spempno;
     elsif v_job ='MANAGER' then
     update emp set sal=sal+500 where empno=spempno;
     else
     update emp set sal=sal+200 where empno=spempno;
     end if;
     end;
    4，
create or replace procedure myspro5(username varchar2) is
c_num number:=1;
begin
loop
insert into users values(c_num,username);
exit when c_num=10;
c_num:=c_num+1;
end loop;
end;
5，
declare
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;

Java中oracle数据库驱动的加载：class.forname("oracle.jdbc.driver.oracledriver");
connection ct=drivermanager.getconnection("jdbc:oracle:thin:@服务器地址:端口号:数据库实例名称","scott",密码);

输入员工号得到员工姓名工资和岗位信息
create or replace procedure myspro1(spno number) is
v_ename emp.ename%type;
v_sal emp.sal%type;
v_job emp.job%type;
begin
select ename,sal,job into v_ename,v_sal,v_job from emp where empno=spno;
dbms_output.put_line('用户名是：'||v_ename||' 工资是：'||v_sal||'岗位是：'||v_job);
end;

在程序中调用的存储过程
create or replace procedure myspro1(spno in number,v_ename out varchar2,v_sal out number,v_job out varchar2) is
begin
select ename,sal,job into v_ename,v_sal,v_job from emp where empno=spno;
dbms_output.put_line('用户名是：'||v_ename||' 工资是：'||v_sal||'岗位是：'||v_job);
end;

返回结果集的过程
1，创建一个包，在该包中定义类型test_cursor游标
create or replace package 包名称 as
type test_cursor is ref cursor;
end 包名称;

2，创建过程
create or replace procedure 过程名
(spno in number,p_cursor out 包名称.test_cursor) is
begin
open p_cursor for select * from emp where deptno=spno;
end;

3，在程序中调用

分页
--在分页时，sql语句模板
select * from (select t1.*,rownum rn from (select * from emp) t1 where rownum <=10) where rn>6;
--编写分页过程
例子：输入表名、每页显示记录数、当前页，返回总记录数、总页数和返回的结果集。
create or replace procedure fenye
(tableName in varchar2,--需要传入的表名称
pagesize in number,--每页显示记录数
pageNow in number,--当前页数
myrows out number,--总记录数
mypageCount out number,--总页数
p_cursor out tespackage.test_cursor --返回的记录集
) is
--定义部分
--定义sql语句字符串
v_sql varchar2(1000);
--定义两个整数
v_begin number:=(pageNow-1)*pagesize+1;
v_end number:=pageNow*pagesize;
begin
--执行部分
v_sql:='select * from (select t1.*,rownum rn 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;