摘要:
select e.salary,--case 语句开始 case when salary between 12000 and 15000 then salary + 6000 else salary end new_salary--case 语句结束,可见也和存储过程等结束方式一样用end关键字,且这个case语句的作用只是用来计算一个新的列 from t_employee e;输出结果如下: 阅读全文
摘要:
--本存储过程的功能:把test_tbl2中与test_tbl1中ID相同但salary不同的记录中的salary的值更新为test_tbl1中的salary的值--创建存储过程create or replace procedure p_update_test_tbl2 is--定义游标 cursor c_test_tbl2 is select t1.id, t1.salary from test_tbl2 t2, test_tbl1 t1 where t2.id = t1.id and t2.salary t1.salary; c_row c_test_tbl2%rowtype;begin. 阅读全文
摘要:
declare cursor c_test_tbl2 is select t2.id, t2.salary from test_tbl2 t2, test_tbl1 t1 where t2.id = t1.id and t2.salary t1.salary; c_row c_test_tbl2%rowtype;begin for c_row in c_test_tbl2 loop dbms_output.put_line(c_row.id || '-' || c_row.salary); end loop;end;PLsql中,输出结果如下: 阅读全文
摘要:
/**如何删除重复记录?*//*1. 先按重复字段分组 2. 在每组里找出最小的rowid 3. 把整个表中与上面查询出来的rowid不相等的记录删除掉*/delete from test_tbl2where rowid not in (select min(rowid) from test_tbl2 group by id); 阅读全文
摘要:
/**1. 用select 创建相同表结构的表*/create table test_tbl2 as select * from test_tbl1 where 11;/** 2. 用insert into .. select 插入*/insert into test_tbl2(id,name,salary) select id,name,salary from test_tbl1 where id<6; 阅读全文