shuaisam

摘要： 简单的用户权限管理--赋予会话权限，这样就可以登录了grant create session to myuser;--赋予修改goods2表的权限grant alter on goods2 to myuser;--收回修改goods2表的权限revoke alter on goods2 from myuser;--查看系统权限select * from dba_sys_privs where grantee='MYUSER'--查看对象权限select owner,table_name,privilege from dba_tab_privs where grantee= 阅读全文

摘要： 创建数据库用户--创建概要文件create profile pro_tbspec limitpassword_life_time 30;--创建表空间create tablespace tbspec datafile 'dbfile.dbf' size 10m;--创建用户create user myuseridentified by myuserdefault tablespace tbspecquota 5m on tbspectemporary tablespace tempprofile pro_tbspec;　修改用户密码alter user myuseridenti 阅读全文

摘要： 预定义异常 查看预定义异常select * from dba_source where name='STANDARD' and text like '%EXCEPTION_INIT%' 预定义异常的使用declare v_mynum number(4);begin v_mynum :=1/0; dbms_output.put_line('除数为零的操作结果：'|| v_mynum); exception when ZERO_DIVIDE then dbms_output.put_line('除数为零！该参数非法，转入以下操作：'. 阅读全文

摘要： 级联修改触发器create trigger mutli_tgr after update of goodsid on goodsfor each rowbegin update sa set goodsid = :new.goodsid where goodsid = :old.goodsid; dbms_output.put_line('数据已经级联修改……');end;语句级触发器create trigger stat_tgr before delete on goods2begin if to_char(sysdate,'dd')='20' 阅读全文

摘要： 行级DML触发器的创建create or replace trigger tri_goods after insert or update or deleteon goodsfor each rowbegin if inserting then insert into g_modify (oper_type,oper_time,goodsid,goodsname,remark,g_modify_id) values('添加的数据',sysdate,:new.goodsid,:new.goodsname,:new.remark,g_modify_id... 阅读全文

摘要： 无参函数的创建create function myuser return varchar2is v_qnty varchar2(20);begin select sys.login_user into v_qnty from dual; return v_qnty;end; select myuser() from dual有参数的函数创建create or replace function fun_goodsinfo (g_id in number,g_name out varchar2,g_remark out varchar2)return varchar2 is ... 阅读全文

摘要： 带有输入参数的存储过程create or replace procedure pro_goods_in (g_id in number default 5)as type goods_info is record (v_goodsid goods.goodsid%type, v_goodsname goods.goodsname%type, v_goodsremark goods.remark%type); v_goods_info goods_info; cursor cur_goods is select... 阅读全文

摘要： 不带参数的存储过程 定义create procedure pro_goodsasbegin update goods set remark = 'sss' where goodsid = 1;end; 执行begin pro_goods;end;　使用游标的存储过程select * from goods where goodsid = 1create or replace procedure pro_goods_cursoras v_goodsinfo goods%rowtype; cursor cursor_goods is s... 阅读全文

摘要： 显式游标declare cursor goods_cursor --创建游标 is select * from goods where goodsid < 5; cur_goods_cursor goods%rowtype; --声明记录类型begin open goods_cursor; --打开游标 loop fetch goods_cursor into cur_goods_cursor; exit when goods_cursor %notfound; dbms_output.put_... 阅读全文

摘要： 变量常量的声明v_var varchar2(20) :='变量';v_var2 varchar2(10) not null default '非空变量2';v_constant constant varchar2(100) := '常量';流程控制语句 1.if....else...条件选择结构 declare v_number number(3,2) := 2.2;begin if v_number > 2 then dbms_output.put_line('v_number 大于 2'); end if; dbms_o 阅读全文