【笔记】oracle练习
程序员文章站
2022-05-13 17:34:51
创建表空间 分配用户权限
/*分为四步 */
/*第1步:创建临时表空间 */
create temporary tablespace user_temp
tem...
创建表空间 分配用户权限 /*分为四步 */ /*第1步:创建临时表空间 */ create temporary tablespace user_temp tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; /*第2步:创建数据表空间 */ create tablespace user_data logging datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; /*第3步:创建用户并指定表空间 */ create user username identified by password default tablespace user_data temporary tablespace user_temp; /*第4步:给用户授予权限 */ grant connect,resource,dba to username;
---frist day create table d_student( --创建学生表 id number(8) constraint d_s_pk primary key, s_name varchar(20), sex varchar(10) constraint sex_chk check(sex in ('man','woman')), dob date ) create table d_clazz( --创建班级表 id number(8) CONSTRAINT d_c_pk PRIMARY KEY, c_name varchar(20) ) alter table d_student add clazz_id number(8) constraint d_c_dl references d_clazz(id); --添加外键 desc d_clazz; alter table d_student drop column clazz_id; --删除列 alter table d_student modify s_name varchar(20); --更改列 Truncate table d_clazz; --清空表数据 insert into d_clazz values (3,'三年级'); insert into d_student values(1,'鸣人','man',to_date('2000-08-01','yyyy-MM-dd'),1); insert into d_student values(2,'佐助','man',to_date('2000-06-21','yyyy-MM-dd'),1); insert into d_student values(3,'小樱','woman',to_date('2000-05-13','yyyy-MM-dd'),1); insert into d_student values(4,'宁次','man',to_date('1999-11-28','yyyy-MM-dd'),2); insert into d_student values(5,'小李','man',to_date('2000-01-01','yyyy-MM-dd'),2); commit; select * from d_student; create view d_v_student as select s.id id, s.s_name name, c.C_NAME clazz_name from d_student s left join d_clazz c on s.CLAZZ_ID = c.ID order by s.id --创建视图 select * from d_v_student order by id desc; create view d_v_clazz as (select a.clazz_id id , c.c_name name, a.s_sum from D_CLAZZ c right join (select clazz_id ,count(*) s_sum from d_student s group by s.CLAZZ_ID ) a on c.id = a.clazz_id) with read only; --创建一个班级只读视图 drop view d_v_clazz; select * from d_v_clazz; --pl/sql 程序 set serveroutput on declare v_student d_v_student%rowtype;--集合类型 v_name varchar(20);--标量类型 v_clazz D_V_STUDENT.CLAZZ_NAME%type;--记录类型 CURSOR v_students is select * from d_v_student;--游标类型 begin select * into v_student from d_v_student where id = 1; dbms_output.put_line(v_student.name); select name into v_name from d_v_student where id = 1; dbms_output.put_line(v_name); select clazz_name into v_clazz from d_v_student where id = 1; dbms_output.put_line(v_clazz); open v_students; --打开游标 loop fetch v_students into v_student; --遍历数据 dbms_output.put_line(v_student.name); -- 游标属性: -- Cursor_name%FOUND 布尔型属性,当最近一次提取游标操作FETCH成功则为 TRUE,否则为FALSE; -- Cursor_name%NOTFOUND 布尔型属性,与%FOUND相反;——注意区别于DO_DATA_FOUND(select into抛出异常) -- Cursor_name%ISOPEN 布尔型属性,当游标已打开时返回 TRUE; -- Cursor_name%ROWCOUNT 数字型属性,返回已从游标中读取的记录数。 EXIT WHEN v_students%NOTFOUND; --退出 end loop; close v_students; --关闭游标 end; / --游标的for循环 --PL/SQL语言提供了游标FOR循环语句,自动执行游标的OPEN、FETCH、CLOSE语句和循环语句的功能; --当进入循环时,游标FOR循环语句自动打开游标,并提取第一行游标数据; --当程序处理完当前所提取的数据而进入下一次循环时,游标FOR循环语句自动提取下一行数据供程序处理; --当提取完结果集合中的所有数据行后结束循环,并自动关闭游标。 --格式: -- FOR index_variable IN cursor_name[(value[, value]…)] LOOP -- -- 游标数据处理代码 -- END LOOP; set serveroutput on declare v_student d_v_student%rowtype;--集合类型 CURSOR v_students(i_id number) is select * from d_v_student where id = i_id; begin for v_student in v_students(1) loop dbms_output.put_line(v_student.name||'所在班级:'||v_student.clazz_name); EXIT WHEN v_students%NOTFOUND; --退出 end loop; end; / --分支结构 if/case set serveroutput on declare v_student d_student%rowtype;--集合类型 v_clazz_name D_CLAZZ.C_NAME%type; CURSOR v_students is select * from d_student; begin for v_student in v_students loop select c_name into v_clazz_name from d_clazz where id = v_student.clazz_id; -- 使用 if if v_student.sex='man' then dbms_output.put_line(v_student.s_name||'性别男 '||'所在班级:'||v_clazz_name); elsif v_student.sex='woman' then dbms_output.put_line(v_student.s_name||'性别女 '||'所在班级:'||v_clazz_name); else dbms_output.put_line(v_student.s_name||'性别不详'); end if; -- 使用 case case v_student.sex when 'woman' then dbms_output.put_line(v_student.s_name||'性别女 '||'所在班级:'||v_clazz_name); when 'man' then dbms_output.put_line(v_student.s_name||'性别男 '||'所在班级:'||v_clazz_name); else dbms_output.put_line(v_student.s_name||'性别不详'); end case; end loop; end; / --循环语句 set serveroutput on declare v_i int :=1; v_sum int :=0; begin -- loop loop exit when v_i>10; v_sum := v_sum+v_i; v_i := v_i+1; end loop; SYS.DBMS_OUTPUT.PUT_LINE(v_sum); -- for v_i := 0; v_sum := 0; for v_i in 0..10 loop v_sum := v_sum+v_i; end loop; SYS.DBMS_OUTPUT.PUT_LINE(v_sum); -- while v_i := 0; v_sum := 0; while v_i<11 loop v_sum := v_sum+v_i; v_i := v_i+1; end loop; SYS.DBMS_OUTPUT.PUT_LINE(v_sum); end; / --异常的捕获及自定义异常 set serveroutput on declare other EXCEPTION; e_clazz exception; e_not_clazz exception; v_clazz d_student.clazz_id%type; begin v_clazz := &clazz; if v_clazz>3 and v_clazz<6 then raise e_not_clazz; end if; insert into d_student values(7,'鸣人','man',to_date('2000-08-01','yyyy-MM-dd'),v_clazz); if v_clazz>2 then raise e_clazz; end if; exception when e_not_clazz then SYS.DBMS_OUTPUT.PUT_LINE('该班级不存在'); when e_clazz then SYS.DBMS_OUTPUT.PUT_LINE('该班级已经满员'); rollback; when other then SYS.DBMS_OUTPUT.PUT_LINE('请核对数据'||sqlcode||sqlerrm); rollback; end; / --返回自定义游标变量 存在问题 set serveroutput on declare type student_r is record( name d_v_student.name%type, clazz_name d_v_student.clazz_name%type ); stu_r student_r; type student_cur is ref cursor return student_r; stu_cur student_cur; begin if not stu_cur%isopen then open stu_cur for select name ,clazz_name from d_v_student; end if; -- loop -- fetch stu_cur into stu_r ; -- exit when stu_cur%notfound; -- SYS.DBMS_OUTPUT.PUT_LINE(stu_r.name); -- end loop; colse stu_cur; end; /
---the next day select * from d_v_student; --参数游标 --游标表达式 语法 cursor(subquery) set serveroutput on DECLARE in_id d_v_student.id%type; stu d_v_student%rowtype; CURSOR stu_c(v_id number) is SELECT * FROM d_v_student where id = v_id; begin in_id := &stu_id; for stu in stu_c(in_id) loop SYS.DBMS_OUTPUT.PUT_LINE(stu.name); end loop; end; / --游标变量 --游标变量是动态的 它与游标的关系就像一般的类型 与变量一样 --游标在打开时 可以取得不同的游标值(可以看作存放不同值得集合或容器) set serveroutout on declare type stu_cur is ref cursor; stu_r stu_cur; stu d_v_student%rowtype; begin if not stu_r%isopen then open stu_r for select * from d_v_student;--打开游标 end if; loop fetch stu_r into stu ; exit when stu_r%notfound; SYS.DBMS_OUTPUT.PUT_LINE(stu.name); end loop; close stu_r;--关闭游标 end; / --游标变量与记录绑定 --1.type ref_type_name is ref cursor [return return_type] --2.cursor_variable ref_type_name --3.open cursor_variable for select_statement set serveroutput on declare type t_stu is RECORD( name varchar(20) ); v_stu t_stu; type t_stu_c is ref cursor return t_stu;--指定返回类型 v_stu_c t_stu_c; begin if not v_stu_c%isopen then open v_stu_c for select name from d_v_student ; end if; loop fetch v_stu_c into v_stu; exit when v_stu_c%notfound; SYS.DBMS_OUTPUT.PUT_LINE(v_stu.name); end loop; end; / --复合数据类型 --记录类型 --s1 student%rowtype; --表记录 (根据现成的表) --cursor student_sur is select * from student where id = 1 ; --s2 student_sur%rowtype--游标定义 (根据查询结果生成的表) --type student_r is record( field_name1 type1); --s3 student_r;--显示定义 (自定义数据结构) --记录表等暂时省略 --定义过程 create procedure d_p_student(v_id d_v_student.id%type) as v_name d_v_student.name%type; begin select name into v_name from d_v_student where id = v_id; dbms_output.put_line(v_name); end d_p_student; / --调用过程 call d_p_student(5); --删除过程 drop procedure d_p_student; -- 测试成功 create or REPLACE procedure d_p_students(v_id d_v_student.id%type, v_name out d_v_student.name%type) as -- v_name d_v_student.name%type; begin select name into v_name from d_v_student where id = v_id; end d_p_students; / --variable v_clazz varchar; variable v_name varchar2(20); execute d_p_students(1,:v_name); print :v_name ; --函数 create or replace function d_f_student(v_id d_v_student.id%type) return d_v_student.name%type as v_name d_v_student.name%type; begin select name into v_name from d_v_student where id = v_id; return v_name; end d_f_student; / --调用自定义函数 set serveroutput on begin SYS.DBMS_OUTPUT.PUT_LINE(d_f_student(1)); end; / --删除函数 drop function d_f_student; create or replace function d_f_student(v_id d_v_student.id%type, v_clazz out D_V_STUDENT.CLAZZ_NAME%type) return d_v_student.name%type as v_name d_v_student.name%type; begin select name ,clazz_name into v_name ,v_clazz from d_v_student where id = v_id; return v_name; end d_f_student; / variable v_clazz varchar2(20); variable v_name varchar2(20); execute :v_name := d_f_student(1,:v_clazz); print :v_name :v_clazz; --包 --触发器
---Third day select * from d_clazz; select * from d_student; select * from d_v_student; select * from d_v_clazz; select * from d_v_teacher; drop view d_v_student; create OR REPLACE view d_v_student as select s.id, s.s_name name , c.c_name clazz from d_student s left join d_clazz c on s.clazz_id = c.id order by s.id; create table d_teacher( id number(5) constraint d_t_pk primary key, t_name varchar(20), course varchar(20), salary number(5), clazz_id number(5) constraint d_t_dl references d_clazz(id) ); desc d_student; insert into d_teacher VALUES (3, '卡卡西1','组长',50,1); insert into d_student values (6,'天天','woman',to_date('2001-08-09','yyyy-MM-dd'),2); commit; --插入多行 insert all into d_teacher VALUES (2, '阿凯','组长',50,2)into d_teacher VALUES (3, '阿凯','组长',50,2) select * from dual; delete d_teacher where id = 3; --创建视图 create or REPLACE view d_v_teacher as select s.id ,s.s_name,s.sex,s.dob,a.c_name,a.t_name,a.course,a.salary from d_student s left join (select * from d_teacher t left join d_clazz c on c.id = t.CLAZZ_ID) a on s.clazz_id=a.clazz_id order by id; drop PROCEDURE d_p_student; ----记录类型 --使用记录表 set serveroutput on declare type stu_tab is table of d_v_student%rowtype index by binary_integer; v_stus stu_tab; v_clazz d_v_student.clazz%type; cursor stu_cur is select * from d_v_student where clazz = v_clazz; i int :=1; begin v_clazz := '一年级'; open stu_cur; loop fetch stu_cur into v_stus(i); exit when stu_cur%notfound; DBMS_OUTPUT.PUT_LINE(v_stus(i).name); i := i+1; end loop; close stu_cur; end; / --联合数组类型 set serveroutput on declare type stu_tab is table of varchar(20) index by binary_integer; v_stus stu_tab; v_clazz d_v_student.clazz%type; cursor stu_cur is select name from d_v_student where clazz = v_clazz; i int :=1; begin v_clazz := '一年级'; open stu_cur; loop fetch stu_cur into v_stus(i); exit when stu_cur%notfound; DBMS_OUTPUT.PUT_LINE(v_stus(i)); i := i+1; end loop; close stu_cur; end; / --嵌套表,变长数组 --过程 create or replace procedure d_p_student(v_id d_student.id%type,v_name out d_student.s_name%type) as v_student d_student%rowtype; begin select * into v_student FROM d_student where id = v_id; -- SYS.DBMS_OUTPUT.PUT_LINE(v_student.s_name); v_name := v_student.s_name; end d_p_student; / set serveroutput on variable v_name varchar2(20); execute d_p_student(1,:v_name); print :v_name; --函数 create or replace function d_f_student(v_id d_v_student.id%type, v_clazz out D_V_STUDENT.clazz%type) return d_v_student.name%type as v_name d_v_student.name%type; begin select name ,clazz into v_name ,v_clazz from d_v_student where id = v_id; return v_name; end d_f_student; / variable v_clazz varchar2(20); variable v_name varchar2(20); execute :v_name := d_f_student(1,:v_clazz); print :v_name :v_clazz; create or replace function d_f_students(v_id d_v_student.id%type, v_name out d_v_student.name%type) return d_v_student.clazz%type as v_clazz d_v_student.clazz%type; begin select name , clazz into v_name, v_clazz from d_v_student where id = v_id; return v_clazz; end d_f_students; / var v_name d_v_student.name%type; var v_clazz d_v_student.clazz%type; execute :v_clazz := d_f_students(1,:v_name); print :v_name :v_clazz; --创建包 create or replace package body d_pack_student as function d_f_student(v_id d_v_student.id%type, v_name out d_v_student.name%type) return d_v_student.clazz%type as v_clazz d_v_student.clazz%type; begin select name , clazz into v_name, v_clazz from d_v_student where id = v_id; return v_clazz; end d_f_student; procedure d_p_student(v_id d_student.id%type,v_name out d_student.s_name%type) as v_student d_student%rowtype; begin select * into v_student FROM d_student where id = v_id; -- SYS.DBMS_OUTPUT.PUT_LINE(v_student.s_name); v_name := v_student.s_name; end d_p_student; end d_pack_student; / --调用包 var v_name d_v_student.name%type; var v_clazz d_v_student.clazz%type; execute :v_clazz := d_pack_student.d_f_students(1,:v_name); print :v_name :v_clazz; --触发器 (有问题) createt or replace trigger change_student before insert or update or delete on d_student begin if(to_char(sysdate,'hh24')not between '8' and '17' ) or (to_char(sysydate,'dy','nls date_langudage = american') in ('sat','sun')) then raise_application_error(-20000,'在非法时间不能改变数据); end if; end change_student;
上一篇: asp存储过程使用
推荐阅读
-
PHP 面向对象程序设计(oop)学习笔记 (四) - 异常处理类Exception
-
.Net Winform开发笔记(四)透过现象看本质
-
.Net WInform开发笔记(五)关于事件Event
-
.Net WInform开发笔记(三)谈谈自制控件(自定义控件)
-
笔记本怎么选购并安装内存条?
-
如何选购新的笔记本电脑?外媒盘点2016最佳电脑产品
-
惠普ENVY 15 as110TU笔记本性能怎么样?
-
Python学习笔记之pandas索引列、过滤、分组、求和功能示例
-
Python学习笔记之抓取某只基金历史净值数据实战案例
-
win7系统鼠标指针乱动怎么办?笔记本win7系统鼠标指针乱动的六种原因及解决方法