以下面两个过程为例。两者的功能相似。
1.MSSQL脚本
/** 更改表名 **/
Begin
declare@tempPoTableName varchar(5
0) --性能对象表名
declare@tempPoSpName varchar(5
0) --性能过程名
declare@errorInfo varchar(200) -
-错误信息
declare@cnt int-
-计数器
declare@tempSQL varchar(1000)
--定义表名、同步表名和存储过程游标
set@tempSQL=' declare allValues_Cursor curso
r for '+CHAR(13) +CHAR(10)
set@tempSQL=@tempSQL+' select POTABLENAME,
POSPNAME from PM_NEPODEF_TABLE
WHERE POID>110499 and POID<110580'
EXEC (@tempSQL)
OPEN allValues_Cursor
--判断是否由符合游标条件的行，如果没有则关闭和释放游标，异常
返回
IF(@@CURSOR_ROWS=0 )
BEGIN
CLOSE allValues_Cursor
DEALLOCATE allValues_Cursor
set@errorInfo='没有指定表名或存储过程名！'
print@errorInfo
return
END
print'开始更改原有表名……'
FETCH NEXT FROM allValues_Cursor INTO@temp PoTableName,@tempPoSpName
--根据给定的表名、存储过程名创建相应的数据存储存储过程
WHILE (@@FETCH_STATUS<>-1)
BEGIN
print@tempPoTableName
IF (EXISTS (SELECT name from sysobject
s WHERE name=@tempPoTableName))
BEGIN
set@tempSQL='ALTER TABLE '+@tempPoTab leName+' DROP
constraint PK_'+@tempPoTableName
EXEC (@tempSQL)
set@tempSQL=@tempPoTableName+'_TMP'
EXEC Sp_rename @tempPoTableName,@tempSQL
END
ELSE
BEGIN
print'没有找到表'+@tempPoTableName;
END
IF (EXISTS (SELECT name from sysobjects WHERE nam e=@tempPoSpName))
BEGIN
set@tempSQL='DROP PROCEDURE '+@tempPo SpName;
EXEC (@tempSQL)
END
ELSE
BEGIN
print'没有找到过程'+@tempPoSpName;
END
FETCH NEXT FROM allValues_Cursor INTO@tempPoTab
leName,@tempPoSpName
END
CLOSE allValues_Cursor
DEALLOCATE allValues_Cursor
print'结束更改原有表名……'
print'------------------------'
END
GO
2.ORACLE脚本
BEGIN
DECLARE
tempPoTableName varchar2(50); --性能对象表名
tempPoSpName varchar2(50); --性能过程名
errorInfo varchar2(200); --错误信息
tempSQL varchar2(1000);
cnt1 number(1);
cnt2 number(2);
--定义表名、同步表名和存储过程游标
Cursor allValues_Cursor is
select UPPER(TRIM(POTABLENAME)),UPPER(TRIM(POSPNAME)) from
PM_NEPODEF_TABLE WHERE POID>110499and POID<110580;
BEGIN
OPEN allValues_Cursor;
--判断是否由符合游标条件的行，如果没有则关闭和释放游标，异常返回
DBMS_OUTPUT.PUT_LINE('开始更改原有表名……');
FETCH allValues_Cursor INTO tempPoTableName,tempPoSpName;
--根据给定的表名、存储过程名创建相应的数据存储存储过程
WHILE allValues_Cursor%found LOOP
cnt1:=0;
cnt2:=0;
BEGIN
SELECT1INTO cnt1 FROM dual WHERE exists(SELECT table_name
FROM user_tables WHERE table_name = tempPoTableName);
SELECT1INTO cnt2 FROM dual WHERE exists(SELECT OBJECT_NAME
FROM user_procedures WHERE OBJECT_NAME= tempPoSpName);
exception
WHEN no_data_found THEN
null;
END;
IF cnt1 =1THEN
DBMS_OUTPUT.PUT_LINE(tempPoTableName);
tempSQL :='ALTER TABLE '||tempPoTableName||' DROP constraint
PK_'||tempPoTableName;
EXECUTE IMMEDIATE tempSQL;
tempSQL :='ALTER TABLE '||tempPoTableName||' RENAME
TO '||tempPoTableName||'_TMP';
EXECUTE IMMEDIATE tempSQL;
ELSE
DBMS_OUTPUT.PUT_LINE('没有找到表'||tempPoTableName);
END IF;
IF cnt2 =1THEN
tempSQL :='DROP PROCEDURE '||tempPoSpName;
EXECUTE IMMEDIATE tempSQL;
ELSE
DBMS_OUTPUT.PUT_LINE('没有找到过程'||tempPoSpName);
END IF;
FETCH allValues_Cursor INTO tempPoTableName,tempPoSpName;
END LOOP;
CLOSE allValues_Cursor;
DBMS_OUTPUT.PUT_LINE('结束更改原有表名……');
DBMS_OUTPUT.PUT_LINE('------------------------');
END;
END;
/
上面两个是无名存储过程，不需要考虑是否已经存在该过程。对于有名的过程需要考虑对象是否已经存在。
我是从MSSQL向Oracle转化的。
第一步，修改整体结构。
MSSQL的总体结构如下，只需要一个begin和end，中间加入变量声明。
Begin
declare --变量
--过程
END
GO
Oralce的总体结构如下，需要两个begin和end，一个是整个过程，一个是除去申明之外的过程。
BEGIN
DECLARE
--变量
BEGIN
--过程
END;
END;
/
第二步，修改声明变量。
MSSQL需要在每个变量前面加 declare标示，Oracle只需要一个declare标示。此外注意修改各自的数据类型。
第三步，修改游标。复杂的过程中离不开游标。因此更改游标结构经常用到。
MSSQL的游标是全局的，需要建立之后再清空。而Oracle的游标类似于局部变量，使用完之后，自动清除。
MSSQL游标结构如下：
set@tempSQL=' declare allValues_Cursor cursor for '+CHAR(13) +CHAR(10)
set@tempSQL=@tempSQL+' select POTABLENAME,
POSPNAME from PM_NEPODEF_TABLE WHERE POID>110499 and POID<110580'
--游标语句
EXEC (@tempSQL)
--1.创建游标
OPEN allValues_Cursor
--2.打开游标
--判断是否由符合游标条件的行，如果没有则关闭和释放游标，异常返回
IF(@@CURSOR_ROWS=0 )
BEGIN
CLOSE allValues_Cursor
DEALLOCATE allValues_Cursor
set@errorInfo='没有指定表名或存储过程名！'
print@errorInfo
return
END
WHILE (@@FETCH_STATUS<>-1)
BEGIN
FETCH NEXT FROM allValues_Cursor INTO@tempPoTableName,@tempPoSpName --3进行数据处理
END
CLOSE allValues_Cursor
--4.关闭游标
DEALLOCATE allValues_Cursor
--5.注销游标
Oracle的游标是在变量中声明定义的，然后在过程中使用。其结构如下：
--声明中
Cursor allValues_Cursor is
select UPPER(TRIM(POTABLENAME)),UPPER(TRIM(POSPNAME))
from PM_NEPODEF_TABLE WHERE POID>110499and POID<110580;
--1.声明游标
--过程中
OPEN allValues_Cursor;
--2.打开游标
WHILE allValues_Cursor%found LOOP
FETCH allValues_Cursor INTO tempPoTableName,tempPoSpName;
--3.处理数据
END LOOP;
CLOSE allValues_Cursor;
--4.关闭游标
第四步修改赋值语句和比较语句。MSSQL中使用Set语句来赋值，Oracle中使用:=来赋值。此外MSSQL中的变量习惯前面增加一个@字符，在Oracle中可以删除。
第五步修改逻辑结构。MSSQL中使用IF()....ELSE....，结构体之间都要用BEGIN和END框起来。而Oracle则使用IF...THEN...ELSE..END IF结构，中间不必使用BEGIN和END。此外While结构差别也类似。
第六步修改各自的调用方法和函数。常见的是MSSQL的EXEC (@tempSQL)，对应Oracle的EXECUTE IMMEDIATE tempSQL。MSSQL的print函数，对应Oracle的DBMS_OUTPUT.PUT_LINE('')函数。此外还有各自使用的数据表，有所不同。例如MSSQL中所有的对象都在sysobjects表中，而Oracle中的表在user_tables中，过程在user_procedures中等。这些需要积累一些经验。最后不要忘了检查，Oracle的所有句子，必须要有分号表示结束。而MSSQL中不需要，即使加了也不错。几步下来，MSSQL过程就转化成Oracle。
来源：网络编辑：联动北方技术论坛
oracle自定义函数和存储过程 oracle自定义函数和存储过程（转）2008-07-23 10:43--过程(PROCEDURE)--------------------------------------------------// --创建表 CREATE TABLE user_info ( id VARCHAR2(4), name VARCHAR2(15), pwd VARCHAR2(15), address VARCHAR2(30) ); --插入数据 INSERT INTO user_info VALUES('u001','zhao','zhao','shanghai'); --如要经常执行插入，Oracle每次都要进行编译，并判断语法正确性，因此执行速度可想而知， --所以我们要创建一个过程来实现 CREATE OR REPLACE PROCEDURE AddNewUser ( n_id user_info.id%TYPE, n_name user_https://www.doczj.com/doc/af17523052.html,%TYPE, n_pwd user_info.pwd%TYPE, n_address user_info.address%TYPE ) AS BEGIN --向表中插入数据 INSERT INTO user_info(id,name,pwd,address) VALUES(n_id,n_name,n_pwd,n_address); END AddNewUser; / --下面我们利用PL/SQL匿名块调用该过程 DECLARE --描述新用户的变量 v_id user_info.id%TYPE := 'u002'; v_name user_https://www.doczj.com/doc/af17523052.html,%TYPE := 'wish'; v_pwd user_info.pwd%TYPE := 'history'; v_add user_info.address%TYPE := 'shanghai'; BEGIN --调用过程，添加wish用户到数据库
Oracle列转行和行转列的几种用法 栏到 栏主要讨论sys_connect_by_path的用法 1，具有分层关系 SQL > createtabledept(deptnononumber，deptname varchar2 (20)，mgrnononumber)； table created . SQL >插入deptvalues (1，“总部”，空)； 1 row created . SQL >插入deptvalues (2，’浙江分公司’，1)； 1 row created . SQL > insert into dept values(3，’杭州分公司’，2)；已创建 1行。 SQL >提交； 提交完成。 SQL >从部门连接中选择最大值(子串(sys_connect_by_path(deptname，’，’)，2))由先前部门连接= mgrno 最大值(SUBSTER(SYS _ CONNECT _ BY _ PATH(DEPTNAME)，’)，2) -总部，浙江分行，杭州分行 2，行-列转换 如果一个表的所有列都连接到一行，用逗号分隔:
SQL >选择最大值(SUBSTER(SYS _ CONNECT _ BY _ PATH(column _ name，’，’)，2)) MAX(SUBSTRA(SYS _ CONNECT _ BY _ PATH(COLUMN _ NAME，’，’)，2)) - DEPTNO，DEPTNAME，MGRNO 3，ListAgg(Oracle 11g) SQL >选择DEPTNO， 2 ListAgg(NAME，’；’) 3在组 4内(由搪瓷订购)搪瓷 5来自emp 6组由deptno 7由deptno 8 / DEPTNO搪瓷 - - 10 CLARK。国王；米勒 20亚当斯；福特。琼斯； SCOTT。史密斯 30艾伦；布莱克； JAMES；马丁； TURNER；下面的W ARD
1.Oracle 工具:sqlplus Sqlplus / as sysdba Shutdown immediate(关闭数据库) Startup(启动数据库) 注意：数据库开启才可以进行操作 Select username,account_status from dba_users;(查询数据库中所有的用户名称与用户状态) Alter user scott identified by tiger account unlock;(scott用户解锁) Sqlplus scott/tiger(使用SCOTT 密码为tiger登陆ORACLE数据库) Connect scott/tiger (在SQL>中直接使用SCOTT用户连接数据库) Show all;(看所有变量) Set sqlprompt “_user>”(设置sqlplus环境下面的提示符用自身用户显示) 注意：如要变量下次重启生效必须把变量写入 /u01/app/oracle/product/10.2.0/db_1/sqlplus/admin/glogin.sql中 Host(回到系统) Help index（查看所有命令） Help+命令(类似于LINUX中的MAN,查看命令的格式与用法) Disconnect(关闭SQLPLUS工具但是不离开数据库) Describe emp(查看EMP表) Define(定义变量,常用与写脚本用) 如：DEFINE _EDITOR =”vi” (CHAR) (定义VI编辑器用于保存最后一条执行的SQL语句) Save /u01/app/oracle/aa.sql (保存SQL语句) Get /u01/app/oracle/aa.sql (调用保存的SQL语句) 2.select（select 查询语句是ORACLE中最常用的语句） DML语句包括（insert 写入，update改变，delect删除，merge两张表同步） 注意：merge常用在ETL（数据仓库）底下 DDL数据定义语句包括（create创建对象，alter修改，drop删除对象，rename改名，truncate 删除整个表） TCL事务处理语句（commit提交立刻生效，rollback回滚，savepoint保存点，savepoint(保存点) DCL权限语句（grant赋予权限，revoke(收回权限) ORACLE的表称之为堆表（keep table） SELECT (1) writing basic SQL select statemanes 1. selecting all columns SCOTT>select * from dept; SCOTT>select dname,deptno,loc from dept; Basic SELECT Statement SELECT *|{[DISTINCT] column|expression [alias],...} FROM table;
存储过程 1 CREATE OR REPLACE PROCEDURE 存储过程名 2 IS 3 BEGIN 4 NULL; 5 END; 行1: CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程, 如果存在就覆盖它; 行2: IS关键词表明后面将跟随一个PL/SQL体。 行3: BEGIN关键词表明PL/SQL体的开始。 行4: NULL PL/SQL语句表明什么事都不做，这句不能删去，因为PL/SQL体中至少需要有一句; 行5: END关键词表明PL/SQL体的结束
存储过程创建语法： create or replace procedure 存储过程名（param1 in type，param2 out type） as 变量1 类型（值范围）; --vs_msg VARCHAR2(4000); 变量2 类型（值范围）; Begin Select count(*) into 变量1 from 表A where列名 =param1； If (判断条件) then Select 列名into 变量2 from 表A where列名 =param1； Dbms_output。Put_line(‘打印信息’); Elsif (判断条件) then Dbms_output。Put_line(‘打印信息’); Else Raise 异常名（NO_DATA_FOUND）; End if; Exception When others then Rollback;
End; 注意事项： 1，存储过程参数不带取值范围，in表示传入，out表示输出 类型可以使用任意Oracle中的合法类型。 2，变量带取值范围，后面接分号 3，在判断语句前最好先用count（*）函数判断是否存在该条操作记录 4，用select 。。。into。。。给变量赋值 5，在代码中抛异常用 raise+异常名 CREATE OR REPLACE PROCEDURE存储过程名 ( --定义参数 is_ym IN CHAR(6) ,
sqlserver到oracle数据无损迁移 编者：liuli10@https://www.doczj.com/doc/af17523052.html, 版本：V1.7 最后修订日期：2015-11-21
第一章简介 1.1数据迁移 随着时代发展数据越来越被重视，而很多时候，当系统需要更新换代的时候，升级后系统所是有的数据库与当前系统的数据库并不一致，此时不仅需要数据割接，最重要的是：如何能将老系统中的数据无损的割接到新系统、新数据库中。因此，结合项目实战经验，针对从windows平台下数据库sqlserver到linux平台下oracle数据库的数据无损迁移进行总结。 1.2数据库简介 一般此处会有很多数据库以及出品公司的历史以及发展历程，在编者看来然而并没有什么大用途，百度百科都可以搜索的到，因此本章结束，直接进入实战总结环节。
第二章sqlserver数据导出 2.1sqlserver数据导出命令 当然不可否认windows为sqlserver提供了强大的图形化平台，导出数据变得只需要点一点就能完成，然而这样的数据导出对于大批量有要求的操作，是极其劳神伤财的，因此，必须要通过命令行进行格式化导出，因此，这里介绍sqlserver 本机数据库导出命令。 2.1.1bcp命令以及参数介绍 https://www.doczj.com/doc/af17523052.html,/liyanmingkong/article/details/6087674 https://www.doczj.com/doc/af17523052.html,/uid-25472509-id-4304562.html https://www.doczj.com/doc/af17523052.html,/link?url=WV2JJM4JHxR7Qct8rr_-499zPc3aP_7E5rOt5l yEnG_Mj_tE9_-ZN1JPE2Vc2wRpkO8QkNGNLVznDfMgniCOnxXhK5jQppNpZk8 Jo1x8o23 为了将文档尽可能精简，bcp命令的参数以及介绍请自行去以上任意网址查询。或者自行baidu或者google搜索。 2.2实战语句解析 实战语句为： bcp"select*from gwbnboss.dbo.ACCOUNT_BUSINESS"queryout "C:\Users\liuli9\Desktop\sqlserverdata_mov\textfile\ACCOUNT_BUSINESS.txt"-c -r"{#$&}"-t"{@#$}"-S"127.0.0.1"-U"数据库用户名"-P"密码" 最终导出的结果存在于 C:\Users\liuli9\Desktop\sqlserverdata_mov\textfile\ACCOUNT_BUSINESS.txt 文件中，当出现“{#$&}”时表示接下来是下一行数据，出现“{@#$}”时表示接下来是下一列数据。将查询结果集完整导出，不对数据做任何格式化或者修改操作，保证数据的原生无损。
1、在开发环境中连接到数据库，进行基本的select查询操作； 2、熟悉plsql的使用； 3、熟悉sqlplus相关命令（登录、查询、导入导出等） 登录sqlplus： 第一：使用dos窗口登录sqlplus Sqlplus 用户名/密码@数据库实例名 Sqlplus system/密码@数据库实例名as sysdba 第二：使用oracle自带的一个sqlplus登录，提供界面，显得更简单一些。 显示当前用户名：show user; 创建一个用户：create user 用户名identified by 密码； 给用户赋予登录的权限：grant connect to 用户名；（此时才可以使用这个用户来登录这个数据库。）给其赋予dba的权限。 修改用户的密码：alter user 用户名identified by 新密码； 查询： Select * from t_user; Select id from t_user; Select name from t_user; Select birthday from t_user; Select id,name from t_user; 导入导出： 导出表: （注意，导出表的exp命令不是在sqlplus下使用的，是在dos窗口下使用的命令。） exp userid=test/sa@test tables=(qx) file=d:\e1223.dmp exp userid=test/sa@test tables=(t_user,qx) file=f:\test.dmp 导出方案： Exp userid=test/sa@test owner=test file=f:\test2.dmp 导出数据库： Exp userid=test/sa@test full=y inctype=complete file=f:\all.dmp 导入表： 下面以一个例子来说明： 看下面的图，我的用户名test，密码sa，数据库实例名test，所有的表都在这里 现在我执行导出表JD的操作：exp userid=test/sa@test tables=(jd) file=f:\jd.dmp 在我的f盘下就出现了这么一个.dmp文件
1．创建用户kaifa（密码亦为kaifa），并分配connect,create table,resource权限。 CREATE user KAIFA IDENTIFIED BY KAIFA DEFAULT TABLESPACE HOSDATA TEMPOARY TABLESPACE TEMPDATA; GRANT CONNECT , CREATE TABLE , RESOURCE TO KAIFA 2．在做报表统计时，需要根据报表日期和币种从概要表中查询本期余额。 概要表（CCB_GYB）信息如下： --RMB 人民币 --CNY 本位币 --USD 外币折美元 如果币种为RMB，则取出人民币余额作为本期余额；为CNY，则取本位币余额；为USD 则取外币折美元余额。 请编写一个函数GetCurrBal( qrp_rq IN VARCHAR2, --报表日期 qrp_code IN VARCHAR2--币种 ) CREATE OR REPLACE FUNCTION GetCurrBal( Vqrp_rq Date , --报表日期 Vqrp_code VARCHAR2--币种 ) RETURN NUMBER IS VAMOUNT NUMBER ; VDATE Date; BEGIN SELECT ACCOUNTING_DATE INTO VDATE FROM CCB_GYB Where ACCOUNTING_DATE = Vqrp_rq; IF Vqrp_code = 'RMB'THEN SELECT RMB_YTD_BALANCE INTO VAMOUNT FROM CCB_GYB WHERE Vqrp_code= 'RMB' AND ACCOUNTING_DATE= VDATE; ELSE IF Vqrp_code = 'CNY'THEN SELECT CNY_YTD_BALANCE INTO VAMOUNT FROM CCB_GYB WHERE Vqrp_code= 'CNY';
--当期时间贷款时间 SELECT DK_ID, max(substr(activeDate, 2)) activeDate FROM (SELECT DK_ID, sys_connect_by_path(activeDate, ',') activeDate FROM (SELECT DK_ID, activeDate, DK_ID || rn rchild, DK_ID || (rn - 1) rfather FROM (SELECT TEMP.DK_ID, --查询项目所在地树形结构全名 SELECT t.area_id, substr(sys_connect_by_path(t.area_name, '-'), 2) as allname , connect_by_root t.area_name as root, --是单一操作符，返回当前层的最顶层节点connect_by_isleaf as IsLeaf, --是伪列，判断当前层是否为叶子节点，1代表是，0代表否 level as lel --是伪列，显示当前节点层所处的层数 FROM dk_project_area_info t START WITH t.area_name = '项目所在地' CONNECT BY PRIOR t.area_id = t.area_pid SYS_CONNECT_BY_PATH 学习2008-09-08 10:59SELECT ename FROM scott.emp START WITH ename = 'KING' CONNECT BY PRIOR empno = mgr; 得到结果为：KING JONES SCOTT ADAMS FORD SMITH BLAKE ALLEN WARD MARTIN TURNER JAMES
oracle学习心得 一、sqlserver的理解 sqlserver服务器就像一栋大楼，大楼里的机房就像服务器的数据库，机房里的电脑如同数据库里的表 1、登录用户可以登录服务器——可以进大楼 2、登录用户成为数据库用户才能进指定的数据库——进入大楼的人给了某个机房的钥匙 才能进入机房 3、登录用户有权限使用表——进入机房的人有电脑的密码才能使用电脑 二、oracle的理解 oracle服务器（全局数据库）就像一个商场，商场的每一家公司是表空间，公司的业务是表 1、数据库由多个表空间组成——商场里有多家公司组成 2、表空间由段组成——公司要有自己的经营业务，可以只有一个业务，就是一个表空 间中只有一个段，可以有多个业务，就是一个表空间有多个段 3、段由区组成——单个业务的细分类别。例如有家公司经营三个业务，卖书，卖家电，
卖衣服，则每个业务就是一个段。而每个业务又有细分，比如卖书的话要进行分类了。计算机区，人文区，小说区等，每一区都要放上书架存放书籍，则书架就是oracle块，存放数据的 三、数据库，表空间，用户（指定默认表空间），表统统由管理员管理 四、在oem中管理数据库的步骤 1、创建 1)存储——表空间——创建表空间(tomspace)（类似于在sql中创建数据库， 通常可以省略，使用默认表空间为users,,临时表空间为temp） 2)首先展开安全性——创建新用户（tom，指定表空间）（类似于在sql中指定 数据库用户） 3)创建表——指定方案（用户）和表空间（列名不要带<>） 4)设置约束 5)输入信息：方案——用户名——表——右击——查看/编辑目录… 2、修改 1）方案——用户名——表 2）修改表结构，添加约束
实验八存储过程的使用 一、实验目的 1、熟练掌握存储过程的定义及使用 二、实验要求 1、实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成 实验内容的预习准备工作； 2、能认真独立完成实验内容； 3、实验后做好实验总结，根据实验情况完成实验报告。 三、实验内容 创建图书管理库的图书、读者和借阅三个基本表的表结构： 图书表： BOOK ( BOOK_ID NUMBER(10), SORT V ARCHAR2(10), BOOK_NAME V ARCHAR2(50), WRITER V ARCHAR2(10), OUTPUT V ARCHAR2(50), PRICE NUMBER(3)); 读者表 READER ( READER_ID NUMBER(3), COMPANY V ARCHAR2(10), NAME V ARCHAR2(10), SEX V ARCHAR2(2), GRADE V ARCHAR2(10), ADDR V ARCHAR2(50)); 借阅表 BORROW ( READER_ID NUMBER(3),
BOOK_ID NUMBER(10), BORROW_DA TE DA TE); 插入数据: BOOK表: insert into book values(445501,'TP3/12','数据库导论','王强','科学出版社',17.90); insert into book values(445502,'TP3/12','数据库导论','王强','科学出版社',17.90); insert into book values(445503,'TP3/12','数据库导论','王强','科学出版社',17.90); insert into book values(332211,'TP5/10','计算机基础','李伟','高等教育出版社',18.00); insert into book values(112266,'TP3/12','FoxBASE','张三','电子工业出版社',23.60); insert into book values(665544,'TS7/21','高等数学','刘明','高等教育出版社',20.00); insert into book values(114455,'TR9/12','线性代数','孙业','北京大学出版社',20.80); insert into book values(113388,'TR7/90','大学英语','胡玲','清华大学出版社',12.50); insert into book values(446601,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50); insert into book values(446602,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50); insert into book values(446603,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50); insert into book values(449901,'TP4/14','FoxPro大全','周虹','科学出版社',32.70); insert into book values(449902,'TP4/14','FoxPro大全','周虹','科学出版社',32.70); insert into book values(118801,'TP4/15','计算机网络','黄力钧','高等教育出版社',21.80); insert into book values(118802,'TP4/15','计算机网络','黄力钧','高等教育出版社',21.80); READER表: insert into reader values(111,'信息系','王维利','女','教授','1号楼424'); insert into reader values(112,'财会系','李立','男','副教授','2号楼316'); insert into reader values(113,'经济系','张三','男','讲师','3号楼105'); insert into reader values(114,'信息系','周华发','男','讲师','1号楼316'); insert into reader values(115,'信息系','赵正义','男','工程师','1号楼224'); insert into reader values(116,'信息系','李明','男','副教授','1号楼318'); insert into reader values(117,'计算机系','李小峰','男','助教','1号楼214'); insert into reader values(118,'计算机系','许鹏飞','男','助工','1号楼216'); insert into reader values(119,'计算机系','刘大龙','男','教授','1号楼318'); insert into reader values(120,'国际贸易','李雪','男','副教授','4号楼506'); insert into reader values(121,'国际贸易','李爽','女','讲师','4号楼510'); insert into reader values(122,'国际贸易','王纯','女','讲师','4号楼512'); insert into reader values(123,'财会系','沈小霞','女','助教','2号楼202'); insert into reader values(124,'财会系','朱海','男','讲师','2号楼210'); insert into reader values(125,'财会系','马英明','男','副教授','2号楼212'); BORROW表:
表结构和数据如下（表名Test）： NO V ALUE NAME 1 a 测试1 1 b 测试2 1 c 测试3 1 d 测试4 2 e 测试5 4 f 测试6 4 g 测试7 Sql语句： select No, ltrim(max(sys_connect_by_path(Value, ';')), ';') as Value, ltrim(max(sys_connect_by_path(Name, ';')), ';') as Name from (select No, Value, Name, rnFirst, lead(rnFirst) over(partition by No order by rnFirst) rnNext from (select a.No, a.Value, https://www.doczj.com/doc/af17523052.html,, row_number() over(order by a.No, a.V alue desc) rnFirst from Test a) tmpTable1) tmpTable2 start with rnNext is null connect by rnNext = prior rnFirst group by No; 检索结果如下： NO V ALUE NAME 1 a;b;c;d 测试1;测试2;测试3;测试4 2 e 测试5 4 f;g 测试6;测试7 简单解释一下那个Sql吧： 1、最内层的Sql（即表tmpTable1），按No和Value排序，并列出行号：select a.No, a.Value, https://www.doczj.com/doc/af17523052.html,, row_number() over(order by a.No, a.V alue desc) rnFirst
存储过程和存储函数： 指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。语法： create [or replace] PROCEDURE 过程名(参数列表) AS PLSQL子程序体； 存储过程实例： 存储过程的调用： 方法一： set serveroutput on begin raisesalary(7369); end; / 方法二： set serveroutput on exec raisesalary(7369);
函数（Function）为一命名的存储程序，可带参数，并返回一计算值。函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型等。 语法： CREATE [OR REPLACE] FUNCTION 函数名(参数列表) RETURN 函数值类型 AS PLSQL子程序体； 示例： 函数的调用： declare v_sal number; begin v_sal:=queryEmpSalary(7934); dbms_output.put_line('salary is:' || v_sal); end; / 在java语言中调用存储过程： 存储过程：
什么时候使用存储过程/存储函数？ 如果只有一个返回值，用存储函数； 否则，就用存储过程。 在out参数中使用游标： 声明包结构： 创建包体：
在Java语言中访问游标类型的out参数： 触发器： 数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。 触发器的类型 语句级触发器 在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行。 行级触发器（FOR EACH ROW） 触发语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量, 识别值的状态。 创建触发器： CREATE [or REPLACE] TRIGGER 触发器名 {BEFORE | AFTER} {DELETE | INSERT | UPDATE [OF 列名]} ON 表名 [FOR EACH ROW [WHEN(条件) ] ] PLSQL 块
关于动态SQL的使用-----摘录 内容摘要：在PL/SQL开发过程中，使用SQL，PL/SQL可以实现大部份的需求，但是在某些特殊的情况下，在PL/SQL中使用标准的SQL语句或DML语句不能实现自己的需求，比如需要动态建表或某个不确定的操作需要动态执行。这就需要使用动态SQL来实现。本文通过几个实例来详细的讲解动态SQL的使用。 本文适宜读者范围：Oracle初级，中级 系统环境： OS：windows2000Professional(英文版) Oracle：8.1.7.1.0 正文： 一般的PL/SQL程序设计中，在DML和事务控制的语句中可以直接使用SQL，但是DDL语句及系统控制语句却不能在PL/SQL中直接使用，要想实现在PL/SQL中使用DDL语句及系统控制语句，可以通过使用动态SQL来实现。 首先我们应该了解什么是动态SQL，在Oracle数据库开发PL/SQL块中我们使用的SQL分为：静态SQL语句和动态SQL语句。所谓静态SQL指在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象。而动态SQL是指在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。 Oracle中动态SQL可以通过本地动态SQL来执行，也可以通过DBMS_SQL包来执行。下面就这两种情况分别进行说明： 一、本地动态SQL 本地动态SQL是使用EXECUTE IMMEDIATE语句来实现的。 1、本地动态SQL执行DDL语句： 需求：根据用户输入的表名及字段名等参数动态建表。 create or replace procedure proc_test ( table_name in varchar2,--表名 field1in varchar2,--字段名 datatype1in varchar2,--字段类型 field2in varchar2,--字段名 datatype2in varchar2--字段类型 )as str_sql varchar2(500); begin str_sql:=create table||table_name||(||field1||||datatype1||,||field2|| ||datatype2||); execute immediate str_sql;--动态执行DDL语句 exception when others then null; end; 以上是编译通过的存储过程代码。下面执行存储过程动态建表。
基础知识 表3.2 Oracle数据类型
表3.3 XSB的表结构
操作表 创建表 CREATE TABLE [schema.] table_name ( column_namedatatype [DEFAULT expression] [column_constraint][,…n]
先来个简单的用法 列转行 Create table test (name char(10),km char(10),cj int) insert test values('张三','语文',80) insert test values('张三','数学',86) insert test values('张三','英语',75) insert test values('李四','语文',78) insert test values('李四','数学',85) insert test values('李四','英语',78) select name, sum(decode(km,'语文',CJ,0)) 语文, sum(decode(km,'数学',cj,0)) 数学, sum(decode(km,'英语',cj,0)) 英语 from test1 group by name 姓名语文数学英语 张三80 86 75 李四78 85 78 行转列 with x as( selectname, sum(decode(km,'语文',CJ,0)) 语文 , sum(decode(km,'数学',cj,0)) 数学, sum(decode(km,'英语',cj,0)) 英语 fromtest groupbyname) selectname,decode(rn,1, '语文', 2, '数学', 3,'英语') 课程, decode(rn, 1, 语文, 2, 数学, 3,英语) 分数 from x, (selectlevel rn from dual connectby1=1andlevel<=3) (from 后面接两个表,是笛卡尔积)
select* |字段表名 from 表名 where 布尔表达式【条件】 externalcandidate 职员相关信息 contractrecruiter 猎头公司 查看表里所有字段 describe[描述] contractrecruiter select cname,nperecentagecharge from contractrecruiter 不显示原来名字，显示现在名字【用AS】 select cname as "Recruiter Name",nperecentagecharge as "Hire Fees" from contractrecruiter 不显示原来名字，显示现在名字【可不用AS】 select cname "Recruiter Name",nperecentagecharge "Hire Fees" from contractrecruiter 【双引号是否写】 双引号是为了区分大小写 职位表 position select *from position 缺额运算:[想减] select VDESCRIPTION , nbudgetedstrength, NBUDGETEDSTRENGTH -NCURRENTSTRENGTH , NYEAR from position
select VDESCRIPTION "Potion", nbudgetedstrength "Budgeted Strength" , NBUDGETEDSTRENGTH -NCURRENTSTRENGTH "Vacancies", NYEAR "Year" from position 显示非重复运行 查询来源地的人数 describe externalcandidate 查看职员城市名字 select ccity from externalcandidate 查看职员城市名字【名字不重复，插入一个关键字：distinct】select distinct ccity,cstate from externalcandidate 运算符： 两列 select vfirstname,vlastname from externalcandidate 字段拼接 select vfirstname||vlastname from externalcandidate select vfirstname||' '||vlastname from externalcandidate 学校 describe college 查看所有学校 select *from college 查看只是加利福尼亚的学校 select * from college where cstate='California' 一个条件 select vfirstname,vlastname,dbirthdate,ntestscore from externalcandidate where dbirthdate>='01-1月-70'
oracle存储过程学习过程 一直没有使用过存储过程 今天特意学习一下oracle的存储过程 一步一步学习，今天学习如下： 建立一个最简单的存储过程 create or replace procedure test_xg_p1 is begin dbms_output.put_line('hello world! this is the first procedure'); end; 建立一个带输入输出参数的存储过程：把输入的数据传给输出参数 create or replace procedure test_xg_p2(a in number,x out number) is begin x:=a; end test_xg_p2; 建立一个逻辑判断的存储过程，并包含输入输出参数：近似分数的登记判断create or replace procedure test_xg_p3(a in number,x out varchar2) is begin if a>=90 then begin x := 'A'; end; end if; if a<90 then begin x:='B'; end; end if; if a<80 then begin x:='C'; end; end if; if a<70 then begin x:='D'; end; end if; if a<60 then begin x:='E'; end; end if; end test_xg_p3; 建立一个带循环逻辑的存储过程：近似累加函数
create or replace procedure test_xg_p4(a in number,x out varchar2) is tempresult number(16); begin tempresult :=0; for tempa in 0..a loop begin tempresult := tempresult + tempa; end; end loop; x:=tempresult; end test_xg_p4; 建立一个能从数据库中特定表中返回数据的存储过程： create or replace procedure test_xg_p5(x out varchar2) is tempresult varchar2(1024); begin tempresult := 'start->'; select hotelid||hotelname into tempresult from hotel where hotelid =10041764; x:=tempresult; end test_xg_p5; 建立一个能使用游标的带循环的存储过程: create or replace procedure test_xg_p6(x out varchar2) is tempresult varchar2(10240); cursor cursor1 is select * from hotel where hotelname like '浙江%'; begin tempresult := 'start->'; for cursor_result in cursor1 loop begin tempresult :=tempresult||cursor_result.hotelid||cursor_result.hotelname; end; end loop; x:=tempresult; end test_xg_p6;