如何分记录?最好的方法? 还有一个办法是用游标取得要分的记录数后输出insert语句,然后批量执行,呵呵 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 SQL> create table table1 (a number,b number);Table createdSQL> insert into table1 values(11,3);1 row insertedSQL> insert into table1 values(22,-5);1 row insertedSQL> commit;Commit completeSQL> create or replace type MyType as object ( 2 f1 NUMBER, 3 f2 NUMBER 4 );Type createdSQL> create type MyTypeList as table of MyType;Type createdSQL> CREATE OR REPLACE FUNCTION recodeDivied RETURN MyTypeList 2 PIPELINED AS 3 v_MyType MyType; 4 CURSOR c IS 5 SELECT * FROM table1; 6 7 v_a NUMBER; 8 v_b NUMBER; 9 v_b_sign NUMBER; 10 11 BEGIN 12 FOR r_c IN c 13 LOOP 14 v_a := r_c.a; 15 v_b := r_c.b; 16 v_b_sign := abs(v_b) / v_b; 17 18 LOOP 19 EXIT WHEN v_b = 0; 20 v_MyType := MyType(v_a, v_b_sign); 21 PIPE ROW(v_MyType); 22 v_b := v_b - v_b_sign; 23 END LOOP; 24 END LOOP; 25 RETURN; 26 END recodeDivied; 27 /Function createdSQL> select * from table(recodeDivied); F1 F2---------- ---------- 11 1 11 1 11 1 22 -1 22 -1 22 -1 22 -1 22 -18 rows selectedSQL> Oracle SQL执行过程 如何用toad导出多个表结构,我的toad是9.6版本把用户下面的多个表结构和存储过程导出 四表关联oracle 查询sql语句,求高手赐教 求助:MSSQL转Oracle问题(自定义表,返回结果集问题),在线等,谢谢了。 定义的变量值为null SQLServer向ORACLE移植问题 Domains 为什么ORACLE空空如也? 菜鸟问个SQL问题 求教,数据库存储过程如何批量执行truncate语句?? 如何登录到sql*plus? 大家推荐下ORACLE学习资料
SQL> create table table1 (a number,b number);Table createdSQL> insert into table1 values(11,3);1 row insertedSQL> insert into table1 values(22,-5);1 row insertedSQL> commit;Commit completeSQL> create or replace type MyType as object (
2 f1 NUMBER,
3 f2 NUMBER
4 );Type createdSQL> create type MyTypeList as table of MyType;Type createdSQL> CREATE OR REPLACE FUNCTION recodeDivied RETURN MyTypeList
2 PIPELINED AS
3 v_MyType MyType;
4 CURSOR c IS
5 SELECT * FROM table1;
6
7 v_a NUMBER;
8 v_b NUMBER;
9 v_b_sign NUMBER;
10
11 BEGIN
12 FOR r_c IN c
13 LOOP
14 v_a := r_c.a;
15 v_b := r_c.b;
16 v_b_sign := abs(v_b) / v_b;
17
18 LOOP
19 EXIT WHEN v_b = 0;
20 v_MyType := MyType(v_a, v_b_sign);
21 PIPE ROW(v_MyType);
22 v_b := v_b - v_b_sign;
23 END LOOP;
24 END LOOP;
25 RETURN;
26 END recodeDivied;
27 /Function createdSQL> select * from table(recodeDivied); F1 F2
---------- ----------
11 1
11 1
11 1
22 -1
22 -1
22 -1
22 -1
22 -18 rows selectedSQL>