oracle 整理导入sql

建立一个bat 文件  内容如下：

sqlplus name/name@DatabaseName @d:\sql.sql

 

--建表--
@c:\sql\createSQL\createTable.sql

--存储过程--
@c:\sql\createSQL\createProc.sql 注意：createProc.sql 中的sql 的内容要如下 注意“/”没有的话会报错

 

drop procedure FLTDEMO.GETNEWCODE
/

drop procedure FLTDEMO.GETNEWCODEMON
/


create or replace procedure FLTDEMO.GETNEWCODE(PARFLAG IN VARCHAR2,PARA1 IN VARCHAR2,PARA2 OUT NUMBER)
is
   Dt VARCHAR2(6);
   TotalId NUMBER;
   YM VARCHAR2(6);
BEGIN
SELECT to_char(SCQZRQ,'yyMMdd') SCQZRQ INTO Dt FROM APPSYS_JSQB WHERE JSQDM = PARA1;
SELECT DRLJ INTO PARA2 FROM APPSYS_JSQB WHERE JSQDM = PARA1;
SELECT LSLJ INTO TotalId FROM APPSYS_JSQB WHERE JSQDM = PARA1;

--编号年月部分

SELECT to_char(sysdate,'yyMMdd') INTO YM FROM dual;

IF Dt = YM THEN
   BEGIN
    PARA2:= PARA2 + 1;
    TotalId:= TotalId +1;
    UPDATE APPSYS_JSQB SET DRLJ=PARA2,LSLJ=TotalId where JSQDM = PARA1;
   END;
END IF;
if not(Dt=YM) THEN
  BEGIN
    PARA2:= 1;
    TotalId:=TotalId+1;
    update APPSYS_JSQB set DRLJ=PARA2,LSLJ=TotalId,SCQZRQ=sysdate where JSQDM = PARA1;
  END;
end if;
IF PARFLAG='2' THEN
   PARA2:= TotalId;
END IF;
--commit
END GETNEWCODE;
/

  

 

 

创建表空间、用户的例子

drop user FLTDEMO cascade ;

drop tablespace FLTDEMO_TS INCLUDING CONTENTS CASCADE CONSTRAINTS;

--创建表空间
create tablespace FLTDEMO_TS datafile 'E:\ORACLE\ORADATA\FLTDEMO_TS.dbf' size 10M
DEFAULT STORAGE ( INITIAL 64K NEXT 64K MAXEXTENTS UNLIMITED PCTINCREASE 50 );

--创建用户,并指向使用的表空间
create user FLTDEMO identified by FLTDEMO default tablespace FLTDEMO_TS;

--授权
grant resource,connect,create table,create view,drop any table to FLTDEMO;
---增加用户对系统表user_tab_columns的权限
grant select on user_tab_columns to FLTDEMO;

--连接--
connect FLTDEMO/FLTDEMO;