开发者博客:www.developsearch.com
查询Oracle游标使用情况的方法
select * from v$open_cursor where user_name = 'TRAFFIC';
查询Oracle会话的方法
select * from v$session
dba 用户登陆sqlplus的方法 :
一般用户登陆之后conn sys/“密码” as sysdba
alter table CUSTOMER add is_valid NUMBER(2);
comment on column CUSTOMER.is_valid is '测试字段';
when not matched then insert values(src.id,src.data);
case...when...的使用
declare
v_sal number:=2000;
v_tax number;
begin
case
when v_sal<1500 then
v_tax:=v_sal*0.03;
when v_sal<2500 then
v_tax:=v_sal*0.04;
when v_sal<3500 then
v_tax:=v_sal*0.05;
when v_sal< 8000 then
v_tax:=v_sal*0.08;
else
v_tax:=v_sal*0.1;
end case;
end;
执行完成之后,变量v_tax的值是多少?
cmd的sqlplus连接上数据库,执行set serveroutput on;打开控制台的输出显示,
再拷贝上面的语句,在end;之前加一句 :dbms_output.put_line(v_tax);
再回车,
控制台输出:80
单引号处理
不仅要在分隔符前后加单引号,还要带有前缀q
例:I'm a String!
string_var:=q'[I''m a String!]';
查询表和字段的备注
追加表字段备注
COMMENT ON TABLE "MR_DEPT" IS '部门表';
查询表的备注信息
SELECT
TABLE_NAME,
TABLE_TYPE,
COMMENTS
FROM
USER_TAB_COMMENTS
WHERE
TABLE_NAME = 'MR_DEPT;
查询字段的备注信息
SELECT
TABLE_NAME,
COLUMN_NAME,
COMMENTS
FROM
USER_COL_COMMENTS
WHERE
TABLE_NAME = 'MR_DEPT;
使用游标加行共享锁更新或删除数据
declare
cursor emp_cursor is
--加行共享锁 3种方式
select t.name,t.english_name from communitytype t for update;
--select t.name,t.english_name from communitytype t for update of t.name; --使用of子句在特定表上加行共享锁
--select t.name,t.english_name from communitytype t for update nowait; --默认情况下当前会话要一直等待对方释放锁,使用nowait子句可以避免等待锁
--定义变量
v_name communitytype.name%type;
v_enname communitytype.english_name%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_name,v_enname;
exit when emp_cursor%notfound;
if v_name = '电子图书' then
update communitytype c
set c.english_name = 'ebook'
where current of emp_cursor;
end if;
end loop;
close emp_cursor;
commit;
end;
for循环
declare
dabing varchar2(100);
dabing2 varchar2(100);
ls_sql Varchar2(4000)
i number;
begin
for i in 1..8 loop
dabing:='macc.xhy_all_sum_20090'||i;
dabing2:='dabing_100'||i;
ls_sql:='Create table '||dabing2||' As select * from '||dabing;
Execute Immediate ls_sql;
end loop;
end;
WHILE J < LEN LOOP
IF J = 0 THEN
EXIT;
ELSE
...
END IF;
END LOOP;
如果检测引用OBJ_DIST_CODE的对象ARR_OBJ_DIST_CODE 将他删除 保证该脚本可以重复执行
DECLARE V_COUNT NUMBER;
BEGIN
SELECT COUNT(*)
INTO V_COUNT
FROM USER_TYPES T WHERE T.TYPE_NAME=UPPER('ARR_OBJ_DIST_CODE');
IF V_COUNT > 0 THEN
EXECUTE IMMEDIATE 'DROP TYPE ARR_OBJ_DIST_CODE';
END IF;
END;
开发者博客:www.developsearch.com