oracle sql常见问题集

[jiezhi]

－－－－－－－－－－－－－
1、DDL, DML和DCL 的不同
－－－－－－－－－－－－－
(1)DDL is Data Definition Language statements. Some examples:
CREATE,
ALTER,
DROP,
RUNCATE (emove all records from a table, including all spaces allocated for the records are removed),
COMMENT(dd comments to the data dictionary ),
GRANT,
EVOKE(ithdraw access privileges given with the GRANT command )
(2)DML is Data Manipulation Language statements. Some examples:
SELECT,
INSERT,
UPDATE,
DELETE(deletes all records from a table, the space for the records remain),
CALL(call a PL/SQL or Java subprogram),
EXPLAIN PLAN (explain access path to data ),
LOCK TABLE (control concurrency )

(3)DCL is Data Control Language statements. Some examples:
COMMIT - save work done
SAVEPOINT - identify a point in a transaction to which you can later roll back
ROLLBACK - restore database to original since the last COMMIT
SET TRANSACTION - Change transaction options like what rollback segment to use

[jiezhi]

我这里看着是好的，怎么回事？

[jiezhi]

－－－－－－－－－－－－－－
4、得到一个表所有的索引信息
－－－－－－－－－－－－－－－
select * from ml_tindex('mytable');


－－－－－－－－－－－－－－－－－－－－－－－
5、generate primary key values for a table
－－－－－－－－－－－－－－－－－－－－－－－
CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;
UPDATE table_name SET seqno = sequence_name.NEXTVAL;

****************************************************
大家多多补充呀！

[zpwly]

关注

[glmcglmc]

gz

[jiezhi]

------------------------------------－－－－－－－－－
6、add a day/hour/minute/second to a date value
－－－－－－－－－－－－－－－－－－－－－－－－－－－
select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;


－－－－－－－－－－－－－－－－－－－－－－－－－－－－
7、对一列的不同值作统计
－－－－－－－－－－－－－－－－－－－－－－－－－－－
select jgbh 机构编号, sum( decode(sex,'男',1,0)) 男,
sum(decode(sex,'女',1,0)) 女
from t_yg
group by 机构编号;

---------------------------------------------------
8、对一列的值作范围内的统计
－－－－－－－－－－－－－－－－－－－－－－－－－－－－
select jgbh,
sum(decode(greatest(age,59), least(age,100), 1, 0)) "年龄60-100",
sum(decode(greatest(age,30), least(age, 59), 1, 0)) "年龄30-59",
sum(decode(greatest(age, 0), least(age, 29), 1, 0)) "年龄0-29"
from pepole
group by jgbh;

[oldwain]

感觉上, ORACLE把CALL和EXPLAIN PLAN归入DML很勉强.

除了这两个语句与DML一样不会自动提交事务以外, 简直与数据处理(Data Manipulation )不沾边.

[xbin999]

select * from ml_tindex('mytable');
是什么用法？

[jiezhi]

oracle一样还有不少不完善的地方。

[cn_afei]

gz

[jackjonse]

还有呢,能不能贴点常用的,,,,,,譬如,权限回收,显示所有用户什么的!

越多越好.

[jiezhi]

有环境的兄弟测试一下：
select * from ml_tindex('mytable');
我这里只有db2没有oracle

[RedFire]

好象不行的说。Windows 2000 Server；Oracle 8i

[ws9300]

select * from ml_tindex('mytable');
没错吗？
如何用！？

[我本来就叫顾影]

问:如何在游标中产生动态的SQL语句
答:
包头:
create or replace package getstu is
type MY_refcur is ref cursor; --定义引用型游标
--定义返回值为引用型游标
function doget(stuname varchar2) return my_REFCUR;
end;
/
包体:
create or replace package body getstu as
function doget(stuname varchar2) return my_REFCUR
is
var_cur my_refcur;
begin
open var_cur for select xh,xm from stu where xm like '%'||stuname||'%';
return var_cur;
end;
end;
----------------------------
现学现卖，今天才弄明白的！：）

[jiezhi]

很不错。

[dongquestion]

我要苦学oracle了，不行了，以前从来没有用过oracle，连sql plus都不太会用啊

[april_jia]

鼓励！

[z5wjz]

学习

[jiezhi]

大家把自己的好东西都拿出来吧！

[hobo_boy]

刚学ORACLE
顶一下!