读取Oracle的Schema,放这里记录一下

Question

Create table 这里输入结果表名 as 
SELECT cols.owner, cols.table_name, cols.column_name AS NAME, 
data_type AS TYPE, 
DECODE (data_type, 
'NUMBER ', data_precision + data_scale, 
data_length 
) LENGTH, 
data_precision PRECISION, data_scale scale, comments 
FROM SYS.all_col_comments coms, SYS.all_tab_columns cols 
WHERE coms.table_name = cols.table_name 
AND coms.column_name = cols.column_name 
AND cols.owner = '这里输入用户名 ' 
ORDER BY cols.owner, cols.table_name, column_id;如果还想读出这个字段是否是主键，那就添点东西：SELECT cols.owner, cols.table_name, cols.column_name AS NAME, 
data_type AS TYPE, 
DECODE (data_type, 
'NUMBER ', data_precision + data_scale, 
data_length 
) LENGTH, 
data_precision PRECISION, data_scale scale, comments, 
NVL2 (cons.column_name, 'PK ', NULL) 是否PK 
FROM SYS.all_col_comments coms, 
(SELECT cc.owner, cc.column_name, cc.table_name 
FROM all_constraints con, dba_cons_columns cc 
WHERE con.owner = '这里输入用户名 ' 
AND con.owner = cc.owner 
AND con.table_name = cc.table_name 
AND con.constraint_type = 'P ' 
AND con.constraint_name = cc.constraint_name) cons, 
SYS.all_tab_columns cols 
WHERE coms.table_name = cols.table_name 
AND coms.column_name = cols.column_name 
AND cols.owner = '这里输入用户名 ' 
AND coms.column_name = cons.column_name(+) 
AND coms.table_name = cons.table_name(+) 
AND coms.owner = cons.owner(+) 
ORDER BY cols.owner, cols.table_name, column_id; 
　　话说回来了，你要是实在很懒的话，那就用工具：TOAD，都能看到，还不要写代码，呵呵。查看oracle某个表的所有字段名 
sql1：select   column_name   from   all_tab_columns   where   table_name='table1'; 
sql2：select   column_name   from   user_tab_columns   where   table_name='table1'; 
我在运行sql1的时候碰到，如果数据库中有相同用户的话，会检索出重复的字段名。
后来把用户的dba权限去掉了就好了。
revoke dba from user1;运行sql2，虽然不出现重复的，但是客户说最终工程所用的DB用户和表的所有者不是一个，所以不能用user_table_columns。
oracle知识太缺乏，不明白客户说的意思关于sql1重复的问题，还是不清楚，具体的参照权限应该怎么取消？取消dba太大了吧。 

Answer 1

plsql和tsql常用函数比对
数学函数 
　　1.绝对值 
　　S:select abs(-1) value 
　　O:select abs(-1) value from dual 　　2.取整(大) 
　　S:select ceiling(-1.001) value 
　　O:select ceil(-1.001) value from dual 　　3.取整（小） 
　　S:select floor(-1.001) value 
　　O:select floor(-1.001) value from dual 　　4.取整（截取） 
　　S:select cast(-1.002 as int) value 
　　O:select trunc(-1.002) value from dual 　　5.四舍五入 
　　S:select round(1.23456,4) value 1.23460 
　　O:select round(1.23456,4) value from dual 1.2346 　　6.e为底的幂 
　　S:select Exp(1) value 2.7182818284590451 
　　O:select Exp(1) value from dual 2.71828182 　　7.取e为底的对数 
　　S:select log(2.7182818284590451) value 1 
　　O:select ln(2.7182818284590451) value from dual; 1 　　8.取10为底对数 
　　S:select log10(10) value 1 
　　O:select log(10,10) value from dual; 1 　　9.取平方 
　　S:select SQUARE(4) value 16 
　　O:select power(4,2) value from dual 16 　　10.取平方根 
　　S:select SQRT(4) value 2 
　　O:select SQRT(4) value from dual 2 　　11.求任意数为底的幂 
　　S:select power(3,4) value 81 
　　O:select power(3,4) value from dual 81 　　12.取随机数 
　　S:select rand() value 
　　O:select sys.dbms_random.value(0,1) value from dual; 　　13.取符号 
　　S:select sign(-8) value -1 
　　O:select sign(-8) value from dual -1 　　14.圆周率 
　　S:SELECT PI() value 3.1415926535897931 
　　O:不知道 　　15.sin,cos,tan 参数都以弧度为单位 
　　例如：select sin(PI()/2) value 得到1（SQLServer） 　　16.Asin,Acos,Atan,Atan2 返回弧度 　　17.弧度角度互换(SQLServer，Oracle不知道) 
　　DEGREES：弧度-〉角度 
　　RADIANS：角度-〉弧度 数值间比较 　　18. 求集合最大值 
　　S:select max(value) value from 
　　(select 1 value 
　　union 
　　select -2 value 
　　union 
　　select 4 value 
　　union 
　　select 3 value)a 　　O:select greatest(1,-2,4,3) value from dual 　　19. 求集合最小值 
　　S:select min(value) value from 
　　(select 1 value 
　　union 
　　select -2 value 
　　union 
　　select 4 value 
　　union 
　　select 3 value)a 　　O:select least(1,-2,4,3) value from dual 　　20.如何处理null值(F2中的null以10代替) 
　　S:select F1,IsNull(F2,10) value from Tbl 
　　O:select F1,nvl(F2,10) value from Tbl 　　21.求字符序号 
　　S:select ascii('a') value 
　　O:select ascii('a') value from dual 　　22.从序号求字符 
　　S:select char(97) value 
　　O:select chr(97) value from dual 　　23.连接 
　　S:select '11'+'22'+'33' value 
　　O:select CONCAT('11','22')　　33 value from dual 23.子串位置 --返回3 
　　S:select CHARINDEX('s','sdsq',2) value 
　　O:select INSTR('sdsq','s',2) value from dual 　　23.模糊子串的位置 --返回2,参数去掉中间%则返回7 
　　S:select patindex('%d%q%','sdsfasdqe') value 
　　O:oracle没发现，但是instr可以通过第四个参数控制出现次数 
　　select INSTR('sdsfasdqe','sd',1,2) value from dual 返回6 　　24.求子串 
　　S:select substring('abcd',2,2) value 
　　O:select substr('abcd',2,2) value from dual 　　25.子串代替 返回aijklmnef 
　　S:SELECT STUFF('abcdef', 2, 3, 'ijklmn') value 
　　O:SELECT Replace('abcdef', 'bcd', 'ijklmn') value from dual 　　26.子串全部替换 
　　S:没发现 
　　O:select Translate('fasdbfasegas','fa','我' ) value from dual 　　27.长度 
　　S:len,datalength 
　　O:length 　　28.大小写转换 lower,upper 　　29.单词首字母大写 
　　S:没发现 
　　O:select INITCAP('abcd dsaf df') value from dual 　　30.左补空格（LPAD的第一个参数为空格则同space函数） 
　　S:select space(10)+'abcd' value 
　　O:select LPAD('abcd',14) value from dual 　　31.右补空格（RPAD的第一个参数为空格则同space函数） 
　　S:select 'abcd'+space(10) value 
　　O:select RPAD('abcd',14) value from dual 　　32.删除空格 
　　S:ltrim,rtrim 
　　O:ltrim,rtrim,trim 　　33. 重复字符串 
　　S:select REPLICATE('abcd',2) value 
　　O:没发现 　　34.发音相似性比较(这两个单词返回值一样，发音相同) 
　　S:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') 
　　O:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') from dual 
　　SQLServer中用SELECT DIFFERENCE('Smithers', 'Smythers') 比较soundex的差 
　　返回0-4，4为同音，1最高 日期函数 
　　35.系统时间 
　　S:select getdate() value 
　　O:select sysdate value from dual 　　36.前后几日 
　　直接与整数相加减 　　37.求日期 
　　S:select convert(char(10),getdate(),20) value 
　　O:select trunc(sysdate) value from dual 
　　select to_char(sysdate,'yyyy-mm-dd') value from dual 　　38.求时间 
　　S:select convert(char(8),getdate(),108) value 
　　O:select to_char(sysdate,'hh24:mm:ss') value from dual 39.取日期时间的其他部分 
　　S:DATEPART 和 DATENAME 函数 （第一个参数决定） 
　　O:to_char函数 第二个参数决定 　　参数---------------------------------下表需要补充 
　　year yy, yyyy 
　　quarter qq, q (季度) 
　　month mm, m (m O无效) 
　　dayofyear dy, y (O表星期) 
　　day dd, d (d O无效) 
　　week wk, ww (wk O无效) 
　　weekday dw (O不清楚) 
　　Hour hh,hh12,hh24 (hh12,hh24 S无效) 
　　minute mi, n (n O无效) 
　　second ss, s (s O无效) 
　　millisecond ms (O无效) 
　　---------------------------------------------- 　　40.当月最后一天 
　　S:不知道 
　　O:select LAST_DAY(sysdate) value from dual 　　41.本星期的某一天（比如星期日） 
　　S:不知道 
　　O:SELECT Next_day(sysdate,7) vaule FROM DUAL; 　　42.字符串转时间 
　　S:可以直接转或者select cast('2004-09-08'as datetime) value 
　　O:SELECT To_date('2004-01-05 22:09:38','yyyy-mm-dd hh24-mi-ss') vaule FROM DUAL; 　　43.求两日期某一部分的差（比如秒） 
　　S:select datediff(ss,getdate(),getdate()+12.3) value 
　　O:直接用两个日期相减（比如d1-d2=12.3） 
　　SELECT (d1-d2)*24*60*60 vaule FROM DUAL; 　　44.根据差值求新的日期（比如分钟） 
　　S:select dateadd(mi,8,getdate()) value 
　　O:SELECT sysdate+8/60/24 vaule FROM DUAL; 　　45.求不同时区时间 
　　S:不知道 
　　O:SELECT New_time(sysdate,'ydt','gmt' ) vaule FROM DUAL; 　　-----时区参数,北京在东8区应该是Ydt------- 
　　AST ADT 大西洋标准时间 
　　BST BDT 白令海标准时间 
　　CST CDT 中部标准时间 
　　EST EDT 东部标准时间 
　　GMT 格林尼治标准时间 
　　HST HDT 阿拉斯加?夏威夷标准时间 
　　MST MDT 山区标准时间 
　　NST 纽芬兰标准时间 
　　PST PDT 太平洋标准时间 
　　YST YDT YUKON标准时间 
 -----Oracle支持的字符函数和它们的Microsoft SQL Server等价函数:函数 Oracle Microsoft SQL　Server
把字符转换为ASCII ASCII ASCII
字串连接 CONCAT (expression + expression)
把ASCII转换为字符 CHR CHAR
返回字符串中的开始字符（左起） INSTR CHARINDEX
把字符转换为小写 LOWER LOWER
把字符转换为大写 UPPER UPPER
填充字符串的左边 LPAD N/A
清除开始的空白 LTRIM LTRIM
清除尾部的空白 RTRIM RTRIM
字符串中的起始模式（pattern） INSTR PATINDEX
多次重复字符串 RPAD REPLICATE
字符串的语音表示 SOUNDEX SOUNDEX
重复空格的字串 RPAD SPACE
从数字数据转换为字符数据 TO_CHAR STR
子串 SUBSTR SUBSTRING
替换字符 REPLACE STUFF
将字串中的每个词首字母大写 INITCAP N/A
翻译字符串 TRANSLATE N/A
字符串长度 LENGTH DATELENGTH or LEN
列表中最大的字符串 GREATEST N/A
列表中最小的字符串 LEAST N/A
如果为NULL则转换字串 NVL ISNULL日期函数函数 Oracle Microsoft SQL　Server
日期相加 (date column +/- value) or
ADD_MONTHS DATEADD
两个日期的差 (date column +/- value) or
MONTHS_BETWEEN DATEDIFF
当前日期和时间 SYSDATE GETDATE()
一个月的最后一天 LAST_DAY N/A
时区转换 NEW_TIME N/A
日期后的第一个周日 NEXT_DAY N/A
代表日期的字符串 TO_CHAR DATENAME
代表日期的整数 TO_NUMBER
(TO_CHAR)) DATEPART
日期舍入 ROUND CONVERT
日期截断 TRUNC CONVERT
字符串转换为日期 TO_DATE CONVERT
如果为NULL则转换日期 NVL ISNULL转换函数函数 Oracle Microsoft SQL　Server
数字转换为字符 TO_CHAR CONVERT
字符转换为数字 TO_NUMBER CONVERT
日期转换为字符 TO_CHAR CONVERT
字符转换为日期 TO_DATE CONVERT
16进制转换为2进制 HEX_TO_RAW CONVERT
2进制转换为16进制 RAW_TO_HEX CONVERT其它行级别的函数函数 Oracle Microsoft SQL　Server
返回第一个非空表达式 DECODE COALESCE
当前序列值 CURRVAL N/A
下一个序列值 NEXTVAL N/A
如果exp1 = exp2, 返回null DECODE NULLIF
用户登录账号ID数字 UID SUSER_ID
用户登录名 USER SUSER_NAME
用户数据库ID数字 UID USER_ID
用户数据库名 USER USER_NAME
当前用户 CURRENT_USER CURRENT_USER
用户环境(audit trail) USERENV N/A
在CONNECT BY子句中的级别 LEVEL N/A合计函数函数 Oracle Microsoft SQL　Server
Average AVG AVG
Count COUNT COUNT
Maximum MAX MAX
Minimum MIN MIN
Standard deviation STDDEV STDEV or STDEVP
Summation SUM SUM
Variance VARIANCE VAR or VARPOracle还有一个有用的函数EXTRACT,提取并且返回日期时间或时间间隔表达式中特定的时间域:
EXTRACT(YEAR FROM 日期)

Answer 2

insert into testtable(recordnumber,currentdate) values (i,sysdate);
print '问世间情为何物？一物降一物';
select @i=@i+1;
end;比较一下就可以看出来到底那里不一样了plsql里面命令的结构为
delacre
    定义语句段
begin
    执行语句段
exception
    异常处理语句段
end
这就是plsql程序总体结构图
定义变量与mssql的不同
基本方法
变量名 类型标识符【notnull】：=值
例 age number（8）：=26
多了定义复合数据类型变量的功能
1.多了%type 变量
declare
    mydate user。testtable.currentdate%type；
还有 %rowtype类型的变量可以识变量获得字段的数据类型，使用%rowtype可以识变量获得整个记录的数据类型。
变量名 数据表.列名%type
变量名 数据表%rowtype
declare
mytable testtbale%rowtype 包含了testtable 所有字段 只不过在输出时候可以选择输出那个
begin
shelect * into mytable
from temuuser.tedttbale
where recordnumber=88
dbms_output.put_line(mytable.currentdate);
end;
还有就是有了定义符合变量
格式
type 复合变量名 is record（
     变量 类型， 可以有好几个）；
     变量名 复合变量名 这个变量名就好像java中类的对象一样而复合变量名就是类名可以这样理解 个人观点
begin
    select * into 变量名 from 表名 where 条件
   dbms_output.put_line(变量名.表中的值)
end另外还可以定义一维数组
type 表类型 is table of 类型 index by binary_integer
表变量名 表类型
index by binary_integer子句代表以符号整数为索引，
这样访问表类型变量中的数据方法就是“表变量名（索引符号整数）”
Declare
type tabletype1 is table of varchar2(4) index by binary_integer;
type tabletype2 is table of tempuser.testtable.recordnumber%type index by
binary_integer;
table1 tabletype1;
table2 tabletype2;
begin
table1(1):='大学';
table1(2):='大专';
table2(1):=88;
table2(2):=55;
dbms_output.put_line(table1(1)||table2(1));
dbms_output.put_line(table1(2)||table2(2));
end;
一个标准的一维数组

Answer 3

定义多维表类型变量
定义了名为 tabletype1 的多维表类型，相当于多维数组，table1 是多维表类型变量，将数据表 tempuser.testtable 中
recordnumber为 60 的记录提取出来存放在 table1 中并显示。type tabletype1 is table of testtable%rowtype index by binary_integer;
table1 tabletype1;
begin
select * into table1(60)
from tempuser.testtable
where recordnumber=60;
dbms_output.put_line(table1(60).recordnumber||table1(60).currentdate);
end;在来看下面的这个程序
set serveroutput on
Declare
   result integer; 
begin
result:=10+3*4-20+5**2;
dbms_output.put_line('运算结果是：'||to_char(result));
end;|| 这个符号是连接语句
to_char(result) dbms_output.put_line函数输出只能是字符串，因此利用 to_char函数将数值型结果转换为字符型。
To_char：将其他类型数据转换为字符型。 To_date：将其他类型数据转换为日期型。 To_number：将其他类型数据转换为数值型。再说下plsql中的控制语句组合有哪几种1. if..then..end if条件控制
if   条件 then
   语句段;
end if;2. if..then..else..end if条件控制
if   条件 then
语句段1;
else
语句段2;
end if;3. if 嵌套条件控制
if   条件1 then
   if 条件2 then
     语句段1;
   else
     语句段2;
   end if；
else
   语句段3;
end if;4.loop..exit..end loop 循环控制
loop
    循环语句段；
    if 条件语句 then
       exit;
    else
       退出循环的处理语句段
    end if;
end loop;5. loop..exit..when..end loop 循环控制
采用 loop..exit..when..end loop 循环控制的语法结构与loop..exit..end loop 循环控制类似
exit when 实际上就相当于
if 条件 then
   exit;
end if;
    
6.while..loop..end loop 循环控制
while 条件 loop
     执行语句段
end loop;
7.for..in..loop..end 循环控制
for 循环变量 in [reverse] 循环下界..循环上界 loop
    循环处理语句段;
end loop;
最后一个出个例子
set serveroutput on
declare
   number1 integer:=80;
   number2 integer:=90;
   i integer:=0;
begin
   for i in 1..10 loop
     number1:=number1+1; 在mssql里是 sclect @number=@number+1
   end loop;
dbms_output.put_line('number1的值:'||to_char(number1)); 
end;   
本人学java 的 对plsql一看觉的很简单 和java比起来简单多了但是oracle 命令只是一部分更多的东西需要我去学习 自夸一下 哈哈
在plsql 多了事务处理命令commit命令
commit事务提交命令。在oracle中为了保证数据的一致性在内存中将为每个客户机建立工作区，就是说在用commit命令之前的操作都在这个工作群里完成，只有在用commit命令之后才会把你写的命令写入到数据库中。
有个自动进行事务提交的命令
set auto on
关闭为 set auto offrollback命令
rollback是事务回滚命令，在没有提交commit命令千，如果发现delete insert update等操需要恢复的话，可以用rollback命令会滚到上次commit时的状态。
set auto off 要先关闭自动提交
select * from scott.emp;
delete form scott.emp;
rollback
这个时候就可以看到 scott.emp还是以前的没有变化savepoint命令
这个命令时保存点命令。事务通常由多个命令组成，可以将每个事务划分成若干个部分进行保存，这样回滚每个保存点，就不必回滚整个事务。
创建保存点 savepoint 保存点名
回滚保存点 rollback to 保存点名
来个例子
insert into scott.emp(empno,ename,sal) values(9000,'wang',2500); 先插入一个值
savepoint insertpoint; 创建一个还原点，名字叫insertpoint
rollback to insertpoint; 还原到那个还原点下面开始说游标
这个东西在mssql里没有吧 我没有印象
游标不是c里面的指针，我一看到这个词就想到了指针可惜何c里面的指针大不一样 不要弄混了 估计没人会弄混。
游标可以说是一个临时的数据存放的地方
要用游标先要定义
cursor 游标名 is select 语句
cursor这是游标的关键字 selcet建立游标的查询命令
看个例子
set serveroutput on
declare
tempsal scott.emp.sal%type 定义了一个变量他是scott.emp.sal同一个类型
cursor mycursor is   定义一个游标mycursor
select * from scott.emp
where sal>tempsal;
begin
tempsal：=800；
open mycursor;   打开这个游标
end;
晕忘了 只是打开游标没有用 还要提取游标的数据
用fetch命令
fetch 游标名 into 变量1，变量2，。；
或者
fetch 游标名 into 记录型变量名；
上面那个程序要改一下set serveroutput on
declare
tempsal scott.emp.sal%type 定义了一个变量他是scott.emp.sal同一个类型
cursor mycursor is   定义一个游标mycursor
select * from scott.emp
where sal>tempsal
new scott.emp%rowtype； 有定义了一个新的变量
begin
tempsal：=800；
open mycursor;   打开这个游标
fetch mycursor into new; 读取游标数据把他添加到new中
dbms_output._line(to_char(new.sal)); 显示结果
close mysursor; close关闭这个游标
end;游标的属性
1.%isopen属性
就是判断游标是否打开，如果没有打开就是用fetch语句提示错误
2.%found属性
就是测试前一个fetch语句是否有值，有就返回true 没有就返回false
3.%notfound属性 和上面的相反
4.%rowcount属性 判断游标的数据行数就是有多少数据下面说下过程的概念 sql里没有
完整的过程的结构如下
create or replace 过程名 as
     声明语句段；
begin
     执行语句段；
exception
     异常处理语句段；
end；
过程是有名称的程序块，as关键词代替了无名块的declare
创建实例的过程
创建一个名为tempprocdeure的过程，create是创建过程的标识符，replace表示如果又同名的过程将覆盖原过程。定义了一个变量，其类型何testtable数据表中的currentdate字段的类型相同，都是日期型，将数据表中的recordnumber字段为88的currentdate字段内容送入变量中，然后输出结果。set serveroutput on
creat or replace procedure tempuser.tempprocedure as
tempdate tempuser.testtable.currentdate%type;
begin
select currentdate
into   tempdate
from   testtable
where recordnumber=88;
dbms_output.put_line(to_char(tempdate));
end;
使用过程
set serveroutput on
begin
tempprocedure;
end;
下面说下带参数的过程
1.参数类型
in 读入参数 程序向过程传递数值
out 读出参数 过程向程序传递数值
in out 双向参数 程序过程互相传递数值
定义带参数的过程
set serveroutput on
creat or replace procedure scott.tempprocedure(
      tempdeptno in scott.dept.deptno%type,/*定义了一个in类型的变量*/
      tempdname out scott.dept.danme%type,/*定义了一个out类型的变量*/
      temploc in out scott.dept.loc%type)as /*定义了一个inout型的变量*/
      loc1 scott.dept.doc%type;
      dname1 scott.dept.dname%type;
begin
     select loc into loc1
     from scott.dept
     where deptno=tempdeptno;
     select danme into danme1
     from scott.dept
     where deptno=tempdeptno;
     temploc:='地址'||loc1;
     tempdname:='姓名'||dname1;
end;定义好了 下面开始用了
set serveroutput on
declare
   myno scott.dept.deptno%type;
   mydname scott.dept.dname%type;
   myloc   scott.dept.loc%type;
begin
   myno:=10;
   mydname:='';
   myloc:='';
   scott.tempprocedure(myno,mydname,myloc);
   dbms_output.put_line(myno);
   dbms_output.put_line(mydname);
   dbms_output.put_line(myloc);
end;
搞定了
就是说用重新定义的三个变量当参数传递给上面定义的过程过程里带参数的变量可以接受这三个变量的值
用java语言来解释就是那个过程就是类 带三个参数
这三个变量就是数据 当然没有对象了哈哈毕竟不是java么哈哈今天写到这里了 我要下班了 7.3
异常处理
就是程序中要处理特殊情况的办法1. 定义异常处理
定义异常处理的语法如下：
declare
    异常名 exception;
2. 触发异常处理
触发异常处理的语法如下：
raise   异常名;
3. 处理异常
触发异常处理后，可以定义异常处理部分，语法如下：
Exception
When 异常名 1 then
     异常处理语句段 1;
When 异常名 2 then
     异常处理语句段 2;下面的 PL/SQL 程序包含了完整的异常处理定义、触发、处理的过程。定义名为 salaryerror
的异常，在 scott.emp 数据表中查找 empno=7566 的记录，将其值放入变量 tempsal 中，判断
tempsal 值若不在 900 和2600 之间，说明该员工的薪水有问题，将激活异常处理，提示信息。set serveroutput on 
declare
   salaryerror exception; 
   tempsal scott.emp.sal%type;
begin
   select sal into tempsal 
   from scott.emp 
   where empno=7566;
   if tempsal<900 or tempsal>2600 then
      raise salaryerror;
   end if;    
   exception
   when salaryerror then
     dbms_output.put_line('薪水超出范围');
end;