请问有熟悉pl/sql的吗?可以帮我写个存储过程:
1: KB_QA 表中qa_id,order_no列保存到临时表(test[])
2:alter table KB_QA drop column order_no;
3:alter table KB_QA add order_no Number(6,3) default 0.0;
4:通过test id插入相对应的kb_qa中的order_no列
谢谢.
1: KB_QA 表中qa_id,order_no列保存到临时表(test[])
2:alter table KB_QA drop column order_no;
3:alter table KB_QA add order_no Number(6,3) default 0.0;
4:通过test id插入相对应的kb_qa中的order_no列
谢谢.
select distinct Test_id,Order_No from KB_QA ;update A set A.order_No=isnull(cast(B.Order_No as decimal(6,3)),default)
from KB_QA as A inner join #Test as B on A.Test_Id=B.Test_Id
CREATE TABLE KB_QA
(qa_id INT,
order_no Number(6,2)
);
-------- 测试数据
INSERT INTO KB_QA VALUES(1,11.11);
INSERT INTO KB_QA VALUES(2,12.12);
INSERT INTO KB_QA VALUES(3,13.13);
INSERT INTO KB_QA VALUES(4,14.14);
COMMIT;
------ ALTER前
QA_ID ORDER_NO
----- --------
1 11.11
2 12.12
3 13.13
4 14.14
------------ sp
CREATE OR REPLACE PROCEDURE ALTER_KB_QA AS
V_SQL VARCHAR2(1000);BEGIN
----- 1
V_SQL := 'CREATE TABLE TEST AS
SELECT QA_ID,ORDER_NO FROM KB_QA';
EXECUTE IMMEDIATE V_SQL;
----- 2
V_SQL := 'ALTER TABLE KB_QA DROP COLUMN ORDER_NO';
EXECUTE IMMEDIATE V_SQL;
----- 3
V_SQL := 'ALTER TABLE KB_QA ADD ORDER_NO NUMBER(6,3) DEFAULT 0.0';
EXECUTE IMMEDIATE V_SQL;
----- 4
V_SQL := 'UPDATE KB_QA T SET T.ORDER_NO = (SELECT ORDER_NO FROM TEST T1 WHERE T.QA_ID = T1.QA_ID)';
EXECUTE IMMEDIATE V_SQL;
COMMIT;
EXECUTE IMMEDIATE 'DROP TABLE TEST';
END;
/-------- 运行sp
SQL> EXEC ALTER_KB_QA; -------- 结果
QA_ID ORDER_NO
------ --------
1 11.110
2 12.120
3 13.130
4 14.140
v_result varchar2(200);
v_objid number;
v_owner varchar2(30);
v_file_id number;
v_object_name varchar2(30);
v_block_id number;
begin
select
dbms_rowid.rowid_relative_fno(rowidstr) || '==' ||
dbms_rowid.rowid_block_number(rowidstr) || '==' ||
dbms_rowid.rowid_object(rowidstr),
dbms_rowid.rowid_object(rowidstr),
dbms_rowid.rowid_relative_fno(rowidstr),
dbms_rowid.rowid_block_number(rowidstr)
into v_result, v_objid, v_file_id, v_block_id
from dual ;select
v_result || '==' ||
owner || '==' ||
object_name,
object_name,
owner
into v_result, v_object_name, v_owner
from dba_objects
where data_object_id=v_objid;
select
v_result || '==' ||extent_id into v_result
from dba_extents
where owner=v_owner
and segment_name=v_object_name
and file_id=v_file_id
and v_block_id between block_id and block_id+blocks-1;
return v_result;
end rowidinfo;
/获取rowid里的物理存储的信息的function。