plsql 导出数据_开发记录篇数据库内容1

       本篇主要记录数据库相关内容，均以oracle数据库为例，内容如生成表空间、创建用户赋权、编写可重复执行脚本以及创建函数、存过、程序包等等。

       首先oracle的安装就不介绍了，傻瓜式安装直接点击下一步就可以了，具体可以网上百度下，现在默认数据库已经安装完了，注意安装过程中设置的管理员密码可不要忘了，还有默认的实例名一般是orcl，默认端口号是1521。

然后再说明下默认用户，其中sys用户是超级用户，具有最高权限即sysdba角色，有create database的权限，所有oracle的数据字典的基表和视图都存放在sys用户中，这些基表和视图对于oracle的运行是至关重要的，由数据库自己维护，任何用户都不能手动修改。若使用plsql登录时用户权限选择sysdba。

 system用户即普通dba用户角色，用于存放一些内部数据，如oracle的一些特性或工具管理信息。oracle数据库中有三个较大的角色sysdba , sysoper和dba 。system用户只拥有其中的两项：sysoper和dba ,而sys用户拥有全部三种角色。此外：system用户以sysdba身份登录时就是sys。

1、创建表空间

// 打开dos命令窗，使用sqlplus登录管理员账号，创建表空间，执行以下命令sqlplus system/你的密码 as sysdba

//  编写创建表空间的脚本文件// Create tablespace tablespace_name【SIZE min 6144M - unlimited】create tablespace tablespace_name datafile 'D:\oracle\oradata\fund_table_1.dbf' size 2048M autoextend on next 512M maxsize unlimited,'D:\oracle\oradata\fund_table_2.dbf' size 2048M autoextend on next 512M maxsize unlimited,'D:\oracle\oradata\fund_table_3.dbf' size 2048M autoextend on next 512M maxsize unlimited LOGGINGONLINEPERMANENTEXTENT MANAGEMENT LOCAL AUTOALLOCATEBLOCKSIZE 8KSEGMENT SPACE MANAGEMENT AUTO;// 保存为tablespace.sql文件

打开刚才登录进来的sql命令框，输入@，然后将你的脚本文件拖进来，回车执行。

2、创建用户

        同理，编写创建用户的脚本，然后放到命令框中执行。脚本如下：

--1、以DBA身份登录后创建用户create user user_name identified by passworddefault tablespace tablespace_nametemporary tablespace tempprofile default--限制配额quota unlimited on tablespace_name;-- grant/revoke role privileges grant connect to user_name;grant resource to user_name;-- grant/revoke system privileges grant alter session to user_name;grant create procedure to user_name;grant create sequence to user_name;grant create session to user_name;grant create synonym to user_name;grant create table to user_name;grant create trigger to user_name;grant create type to user_name;grant create view to standdb;grant create database link to user_name;grant execute on dbms_lock to user_name;grant execute on dbms_pipe to user_name;grant insert any table to user_name;grant query rewrite to user_name;grant unlimited tablespace to user_name; grant execute any procedure  to user_name;

3、导出dmp文件

        dmp文件是oracle数据库导出的文件，用以备份数据库使用，使用exp命令进行导出，命令如下：

// 注意，这个不是在sql命令窗口里执行的，是在dos命令窗口执行的exp user/password@orcl file=d:\xxx.dmp log=e:\log.txt full=y

4、导入dmp文件

        导入dmp文件时使用imp命令进行导入，命令如下：

// 注意，这个不是在sql命令窗口里执行的，是在dos命令窗口执行的imp user/password@orcl file=d:\xxx.dmp  fromuser=导出此dmp的用户 touser=用户名 buffer=40960000 log=d:\yyy.log

5、创建表(可重复执行脚本)

     通过编写可重复执行脚本的方式创建表，在sqlplus中执行脚本进行创建。

-- 声明变量declare  tCount pls_integer :=0;  sCount pls_integer :=0;begin  /*==============================================================*/  /* Table: TSTUDENT                                              */  /*==============================================================*/  select count(*)    into tCount    from user_tables ut   where ut.TABLE_NAME = 'TSTUDENT';-- 如果已有该表则不再创建，如果没有则执行动态sql创建此表     if tCount = 0 then    execute immediate 'create table TSTUDENT(  l_serialno number not null,  C_STUNO VARCHAR2(10) not null,  C_CLASS VARCHAR2(10) not null,  C_NAME VARCHAR2(10),  C_SEX VARCHAR2(4),  C_CREATOR VARCHAR2(16),  D_INTIME DATE,  D_CREATETIME DATE ,  constraint PK_TSTUDENT primary key(l_serialno) using index tablespace fund_index) tablespace fund_table';  execute immediate 'comment on table tstudent is ''学生信息表''';  execute immediate 'comment on column tstudent.l_serialno is ''序号''';  execute immediate 'comment on column tstudent.c_stuno is ''学号(业务主键)''';  execute immediate 'comment on column tstudent.c_class is ''班级''';  execute immediate 'comment on column tstudent.c_name is ''姓名''';  execute immediate 'comment on column tstudent.c_sex is ''性别''';  execute immediate 'comment on column tstudent.d_intime is ''入学时间''';  execute immediate 'comment on column tstudent.c_creator is ''创建人''';  execute immediate 'comment on column tstudent.d_createtime is ''创建时间''';  end if;  /*==============================================================*/  /* Sequence: SEQ_STUDENT   创建序列                              */  /*==============================================================*/  select count(*) into sCount from user_sequences us where us.sequence_name = 'SEQ_STUDENT';  if sCount = 0 then     execute immediate 'create sequence SEQ_STUDENT minvalue 1 maxvalue 999999999 start with 1 increment by 1';  end if;end;/-- 最后的/ 代表执行的是plsql的脚本

6、表新增字段(可重复执行脚本)

-- 同理，先判断有没有该字段，如果有的话就不新增，没有就新增declare  icount  integer:=0;  begin    select count(1) into icount from user_tab_columns u where lower(u.table_name) = 'tstudent' and lower(u.column_name) = 'c_sex';    if icount = 0 then      execute immediate 'alter table tstudent add c_sex char(1) default ''0''';      execute immediate 'comment on column tstudent.c_sex is ''性别''';    end if;  end;/

7、删除表字段(可重复执行脚本)

// 同理declare  icount  integer:=0;  begin    select count(1) into icount from user_tab_columns u where lower(u.table_name) = 'tstudent' and lower(u.COLUMN_NAME) = 'c_sex';    if icount > 0 then      execute immediate 'alter table tstudent drop column c_sex';    end if;  end;/

8、表调整字段

-- 更改约束ALTER TABLE "user_name"."tstudent" MODIFY ("c_sex" NOT NULL ENABLE);-- 删除字段alter table tstudent drop column c_sex;-- 修改表字段大小alter table tstudent(c_sex varchar2(50));

9、注意点

在sqlplus中输入 . 可终止输入；还有注意commit，好多时候数据不对折腾半天，然后发现是没有commit导致。向表中插数据包含超链接，并且有参数拼接在超链接上，使用'||CHR(38)||'拼接，就是替代'&'

10、常用内置函数

// 获取序列下一位select to_char(SQN_CUSTNO.Nextval) from dual;// 计算string所占的字节长度 :返回字符串的长度，单位是字节lengthb(string);// 计算string所占的字符长度 :返回字符串的长度，单位是字符length(string);// 左边补0到11位select lpad(to_char(SQN_CUSTNO.Nextval),11,'0')  from dual;// 右边补0到11位select rpad(to_char(SQN_CUSTNO.Nextval),11,'0')  from dual;// 去除空格trim(str1);// 转大写upper(str1);// 转小写lower(str1);// 替换指定字符串replace(str_source,str1,str2);

11、创建函数(只是随便瞎写的，没什么逻辑，就看下格式就好)

// 使用function关键字，入参 名、类型，反参 类型create or replace function f_getname(     p_code1 in  varchar2,     p_code2 in  varchar2,     p_code3 in  varchar2)return varchar2 is// %type 的意思是v_param1 这个变量的类型与tstudent表中的c_teacher字段类型保持一致   v_param1   tstudent.c_teacher%type;   v_returnvalue varchar2(500):=null;   v_param2  tstudent.c_class%type; begin   select max(trim(c_name)) into v_param1 from tstudent t where t.l_no = p_code1;   if v_param1 = '1' then     v_returnvalue:=p_code1;   elsif v_param1 = '2' then     v_returnvalue:=p_code2;   elsif v_param1 = '3' then     v_returnvalue:=p_code1||p_code2;   elsif v_param1 = '4' then     v_returnvalue:=p_code1||p_code3;   end if;   return v_returnvalue; end f_getschemeno;

12、创建存过(只是随便瞎写的，没什么逻辑，就看下格式就好)

create or replace procedure sp_getname(     p_code1 in  varchar2,     p_code2 in  varchar2,     p_code3 in  varchar2,     p_returnvalue out varchar2) is   v_param1   tstudent.c_teacher%type;   v_param2  tstudent.c_class%type; begin  select max(trim(c_name)) into v_param1 from tstudent t where t.l_no = p_code1;   if v_param1 = '1' then     p_returnvalue:=p_code1;   elsif v_param1 = '2' then     p_returnvalue:=p_code2;   elsif v_param1 = '3' then     p_returnvalue:=p_code1||p_code2;   elsif v_param1 = '4' then     p_returnvalue:=p_code1||p_code3;   end if;   return p_returnvalue; end sp_getname;

13、函数与存过区别

函数：使用return返回一个变量，可以嵌入sql中和存储过程中使用。存过：没有return但可使用 out 指定多个返回参数，不能嵌入在sql中使用，可以在存过中使用

14、java调用oracle函数

//得到预编译的statement对象CallableStatement statement = connection.prepareCall("{? = call f_getname(?,?,?)}");//给参数赋值statement.setString(2,"code1");statement.setString(3,"code2");statement.setString(4,"code3");statement.registerOutParameter(1, OracleTypes.VARCHAR);//执行数据库操作statement.execute();//输出返回值System.out.println(statement.getObject(1));

15、java调用oracle存过

//得到预编译的statement对象CallableStatement statement = connection.prepareCall("{call f_getname(?,?,?,?)}");//给参数赋值statement.setString(1,"code1");statement.setString(2,"code2");statement.setString(3,"code3");statement.registerOutParameter(4, OracleTypes.VARCHAR);//执行数据库操作statement.execute();//输出返回值System.out.println(statement.getObject(4));

16、oracle创建包头和包体

// oracle中的包就相当与java中的接口和实现类// 其中包头相当于接口，进行方法的定义// 然后包体相当于实现类，进行方法的具体实现// 包中可以有函数和存储过程// 在out参数中引用游标,解决返回值过多的情况.// 包头create or replace package mypackage is// 这句话是自定义一个变量类型为游标类型其实就是游标，返回多条数据时使用  type empcursor is ref cursor;// 下面这句就是包头里有个存储过程,定义一个存储过程,可以定义多个. procedure queryEmpList(dno in number,empList out empcursor);// 包头结束end mypackage;// 包体,用来实现包头里的具体的存储过程或者存储函数create or replace package body mypackage is procedure queryEmpList(dno in number,empList out empcursor)   as   v_param1 varchar2(255);    begin     // 开启游标收集查询出的结果集             open empList for select * from emp where deptno=dno;      end;end mypackage;

17、plsql中查看、操作

        这些函数、存过以及包都可以在plsql中进行修改操作，这样可视化的就简单多了，可以直接开一个窗口，直接写函数名、存过名或者包名直接右键就可以了，查看包头和包体和编辑包头和包体。下边那个debug选中以后，就可以打断点调试函数和存过了，非常方便。

       ok，对数据库内容的整理就暂时先这样吧，后续会持续更新相关内容，拜拜喽