`
tanglei198577
  • 浏览: 60264 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
社区版块
存档分类

plsql learning - one:debug and track

阅读更多

All these codes test on oracle9.2.First,give the all the base tables and datas for learning pl/sql.
They will be all used in the following testing.

create sequence student_sequence
start with 10000
increment by 1;

create sequence room_sequence
start with 20000
increment by 1;

create table students(
  id number(5) primary key,
  first_name varchar2(20),
  last_name varchar2(20),
  major varchar2(30),
  current_credits number(3)
);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'Scott','Smith','Computer Science',11);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'Margaret','Mason','History',4);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'Joanne','Junebug','Computer Science',8);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'Manish','Murgratroid','Economics',8);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'Patrick','Poll','History',4);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'Timothy','Taller','History',4);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'Barbara','Blues','Economics',7);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'David','Dinsmore','Music',4);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'Ester','Elegant','Nutrition',8);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'Rose','Riznit','Music',7);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'Rita','Razmataz','Nutrition',8);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'Shay','Shariatpanahy','Computer Science',3);

create table major_stats(
  major varchar2(30),
  total_credits number,
  total_students number
)

insert into major_stats(major,total_credits,total_students)
values('Computer Science',22,3);

insert into major_stats(major,total_credits,total_students)
values('History',12,3);

insert into major_stats(major,total_credits,total_students)
values('Economics',15,2);

insert into major_stats(major,total_credits,total_students)
values('Music',11,2);

insert into major_stats(major,total_credits,total_students)
values('Nutrition',16,2);

create table rooms(
  room_id number(5) primary key,
  building varchar2(15),
  room_number number(4),
  number_seats number(4),
  description varchar2(50)
);

insert into rooms(room_id,building,room_number,number_seats,description)
values(room_sequence.nextval,'Building 7',201,1000,'Large Lecture Hall');

insert into rooms(room_id,building,room_number,number_seats,description)
values(room_sequence.nextval,'Building 6',101,500,'Small Lecture Hall');

insert into rooms(room_id,building,room_number,number_seats,description)
values(room_sequence.nextval,'Building 6',150,50,'Discussion Room A');

insert into rooms(room_id,building,room_number,number_seats,description)
values(room_sequence.nextval,'Building 6',160,50,'Discussion Room B');

insert into rooms(room_id,building,room_number,number_seats,description)
values(room_sequence.nextval,'Building 6',170,50,'Discussion Room C');

insert into rooms(room_id,building,room_number,number_seats,description)
values(room_sequence.nextval,'Music Building',100,10,'Music Practise Room');

insert into rooms(room_id,building,room_number,number_seats,description)
values(room_sequence.nextval,'Music Building',200,1000,'Concert Room');

insert into rooms(room_id,building,room_number,number_seats,description)
values(room_sequence.nextval,'Building 7',300,75,'Discussion Room D');

insert into rooms(room_id,building,room_number,number_seats,description)
values(room_sequence.nextval,'Building 7',310,50,'Discussion Room E');

create table classes(
  department char(3),
  course number(3),
  description varchar2(2000),
  max_students number(3),
  current_students number(3),
  num_credits number(1),
  room_id number(5),
  constraint classes_department_course primary key (department,course),
  constraint classes_room_id foreign key (room_id) references rooms(room_id)
);

insert into classes(department,course,description,max_students,current_students,num_credits,room_id)
values('HIS',101,'History 101',30,11,4,20000);

insert into classes(department,course,description,max_students,current_students,num_credits,room_id)
values('HIS',301,'History 301',30,0,4,20004);

insert into classes(department,course,description,max_students,current_students,num_credits,room_id)
values('CS',101,'Computer Science 101',50,0,4,20001);

insert into classes(department,course,description,max_students,current_students,num_credits,room_id)
values('ESN',203,'Economics 203',15,0,3,20002);

insert into classes(department,course,description,max_students,current_students,num_credits,room_id)
values('CS',102,'Computer Science 102',35,3,4,20003);

insert into classes(department,course,description,max_students,current_students,num_credits,room_id)
values('MUS',410,'Music 410',5,4,3,20005);

insert into classes(department,course,description,max_students,current_students,num_credits,room_id)
values('ECN',101,'Economics 101',50,0,4,20007);

insert into classes(department,course,description,max_students,current_students,num_credits,room_id)
values('NUT',307,'Nutrition 307',20,2,4,20008);

insert into classes(department,course,description,max_students,current_students,num_credits,room_id)
values('NUS',100,'Music 100',100,0,3,null);

create table registered_students(
  student_id number(5) not null,
  department char(3) not null,
  course number(3) not null,
  grade char(1),
  constraint rs_grade check(grade in('A','B','C','D','E')),
  constraint rs_student_di foreign key (student_id) references students(id),
  constraint rs_department_course foreign key(department,course) references classes(department,course)
);

insert into registered_students(student_id,department,course,grade)
values(10000,'CS',102,'A');

insert into registered_students(student_id,department,course,grade)
values(10002,'CS',102,'B');

insert into registered_students(student_id,department,course,grade)
values(10003,'CS',102,'C');

insert into registered_students(student_id,department,course,grade)
values(10000,'HIS',101,'A');

insert into registered_students(student_id,department,course,grade)
values(10001,'HIS',101,'B');

insert into registered_students(student_id,department,course,grade)
values(10002,'HIS',101,'B');

insert into registered_students(student_id,department,course,grade)
values(10003,'HIS',101,'A');

insert into registered_students(student_id,department,course,grade)
values(10004,'HIS',101,'C');

insert into registered_students(student_id,department,course,grade)
values(10005,'HIS',101,'C');

insert into registered_students(student_id,department,course,grade)
values(10006,'HIS',101,'E');

insert into registered_students(student_id,department,course,grade)
values(10007,'HIS',101,'B');

insert into registered_students(student_id,department,course,grade)
values(10008,'HIS',101,'A');

insert into registered_students(student_id,department,course,grade)
values(10009,'HIS',101,'D');

insert into registered_students(student_id,department,course,grade)
values(10010,'HIS',101,'A');

insert into registered_students(student_id,department,course,grade)
values(10008,'NUT',307,'A');

insert into registered_students(student_id,department,course,grade)
values(10010,'NUT',307,'A');

insert into registered_students(student_id,department,course,grade)
values(10009,'MUS',410,'B');

insert into registered_students(student_id,department,course,grade)
values(10006,'MUS',410,'E');

insert into registered_students(student_id,department,course,grade)
values(10011,'MUS',410,'B');

insert into registered_students(student_id,department,course,grade)
values(10010,'MUS',410,'B');

create table rs_audit(
  change_type char(1) not null,
  changed_by varchar2(8) not null,
  timestamp date not null,
  old_student_id number(5),
  old_department char(3),
  old_course number(3),
  old_grade char(1),
  new_student_id number(5),
  new_department char(3),
  new_course number(3),
  new_grade char(1)
);

create table log_table(
  code number,
  message varchar2(200),
  info varchar2(100)
);

create table temp_table(
  num_col number,
  char_col varchar2(60)
);

create table connect_audit(
  user_name varchar2(30),
  operation varchar2(30),
  timestamp date
);

create table debug_table(
  linecount number,
  debug_str varchar2(100)
);

create table source(
  key number(5),
  value varchar2(50)
);

create table destination(
 key number(5),
 value number
);

 Then to create the function and procdure to show how to debug when the error raised up.

create or replace function AverageGrade(
  p_Department in registered_students.department%TYPE,
  p_Course in registered_students.course%TYPE) return char as
  v_AverageGrade char(1);
  v_NumericGrade number;
  v_NumberStudents number;
  
  --cursor c_Grades is
    --select grade from registered_students
    --where department = p_Department and course = p_Course;
  begin
    Debug.Reset;
    Debug.Debug('p_Department',''''||p_Department||'''');
    Debug.Debug('p_Course',''''||p_Course||'''');
    --Check the number of student that queried.
    select count(*) into v_NumberStudents
    from registered_students
    where department = p_Department and course = p_Course;
    Debug.Debug('After select,the number is: ',v_NumberStudents);
    
    if v_NumberStudents = 0 then
      raise_application_error(-20001,'No students registered for '||p_Department||' '||p_Course);
    end if;
    
    select avg(decode(grade,'A',5,'B',4,'C',3,'D',2,'E',1)) into v_NumericGrade
    from registered_students
    where department = p_Department and course = p_Course;
    
    select decode(round(v_NumericGrade),5,'A',4,'B',3,'C',2,'D',1,'E')
    into v_AverageGrade from dual;
    return v_AverageGrade;
 end AverageGrade;
   
create or replace package Debug as
  --select debug_str from debug_table order by linecount;
    procedure Debug(p_Description in varchar2,p_Value in varchar2);
    procedure Reset;
  end Debug;
  
create or replace package body Debug as
  v_LineCount number;
  
  --procedure debug
  procedure Debug(p_Description in varchar2,p_Value in varchar2) is
  begin 
    insert into debug_table(linecount,debug_str)
      values(v_LineCount,p_Description||':'||p_Value);
    commit;
    v_LineCount := v_LineCount+1;
  end Debug;
  --procedure reset
  procedure Reset is
  begin
    v_LineCount:=1;
    delete from debug_table;
  end Reset;
  
  begin
    reset;
  end Debug;

 Then we can execute this function to test,the log will be show when:

select * from debug_table

 This mode donesnot good enough for us,it may caused some errors.Use "dbms_output.put_line" is a better way .

0
0
分享到:
评论

相关推荐

    oracle12c ORA-01017: 用户名/口令无效; 登录被拒绝 解决方案

    oracle12c程序连接时异常: ORA-01017: 用户名/口令无效; 登录被拒绝 的解决方案。

    IMP-00010:不是有效的导出文件,头部验证失败

    标题“IMP-00010:不是有效的导出文件,头部验证失败”涉及的是Oracle数据库导入(IMP)过程中遇到的一个常见错误。这个错误通常发生在尝试使用IMP工具从.dmp文件导入数据时,表明导入文件可能已损坏或不完整,导致...

    oracle远程连接服务器出现 ORA-12170 TNS:连接超时 解决办法

    您可能感兴趣的文章:常用的Oracle doc命令(收藏)Oracle 多行记录合并/连接/聚合字符串的几种方法Oracle中字符串连接的实现方法php连接oracle数据库及查询数据的方法plsql连接oracle数据库报ora 12154错

    plsql-parser:用js编写的Oracle PLSQL解析器

    PL / SQL解析器 欢迎捐款,我对每一个帮助都很满意:) 概念 这将是一个多阶段解析器。这意味着,第一级仅解析一般结构。例如,它发现变量声明,但不检查定义的varchar2变量是否确实具有有效的字符串值。...

    Mybatis出现ORA-00911: invalid character的解决办法

    今天在项目中,使用Mybatis对oracle数据库进行操作的时候,报出ORA-00911: invalid character的错误,检查了一下SQL,发现都书写正确啊,复制到plsql上执行也都没问题,这什么原因呢? 注意:这里说的是用navicat...

    PL/SQL登录Oracle数据库报错ORA-12154:TNS:无法解析指定的连接标识符已解决(本地未安装Oracle需要连接服务器上的)

    ORA-12154:TNS:无法解析指定的连接标识符 ORA-12154:TNS:无监听程序 ...E:\PLSQL Developer(64)\instantclient_11_2 配置环境变量就是为了让PL/SQL Developer打开时加载到tnsnames.ora文件的内容 配置pl/sql的Datab

    plsql-vf-dbfodbc.rar

    标题 "plsql-vf-dbfodbc.rar" 暗示了这个压缩包与PL/SQL(Oracle数据库的编程环境)以及Visual FoxPro(VFP)数据库系统有关,特别是涉及到在PL/SQL环境中通过ODBC(Open Database Connectivity)驱动程序访问DBF...

    plsql8.0.3.1510绿色破解版(内含DBAtools)

    此工具的主要功能有:绿色,已破解,中文操作文档,附加dbatools插件(可用于扩展数据库表空间等,此功能经常使用,完全可保证),是学习oracle进行提高的必备工具,希望对学习oracle数据的朋友有所帮助。

    ORA-01480STR 绑定值的结尾 Null字符缺失 的问题原因及解决办法

    在Oracle数据库操作中,"ORA-01480: STR 绑定值的结尾 Null 字符缺失"是一个常见的错误,通常与数据插入或更新时的格式问题有关。这个错误通常意味着在处理字符串数据时,Oracle无法找到预期的NULL终止符,这可能是...

    plsqldev-trivadis-plugin:Trivadis PLSQL Cop的PLSQL Developer插件

    plsqldev-trivadis-插件 Trivadis PL / SQL Cop的PL / SQL开发人员插件 如果您的PL / SQL Developer是x86,则需要将插件编译为x86,或者如果它是x64,则编译为x64。 您需要将插件放置在C:\ Program Files \ PLSQL ...

    plsqldev-9.0

    plsqldev-9.0.zip

    PLSQL Developer-dba

    PLSQL Developer-dba

    oracle中表空间、权限、角色、用户的总结

    oracle中表空间、权限、角色、用户的总结,其中包括表空间的建立,权限的配置,角色的生成和用户的挂接

    plsql资料--plsql资料

    PLSQL,全称为Procedural Language/SQL,是Oracle数据库提供的一个编程环境,结合了SQL(结构化查询语言)和过程式编程语言的特性。它主要用于编写与Oracle数据库交互的应用程序,尤其是数据库的后台处理部分。这个...

    plsqldev-12.汉化版

    plsqldev-12.汉化版 Product Code:4t46t6vydkvsxekkvf3fjnpzy5wbuhphqz Serial Number:601769 password:xs374ca

    TSQL与PLSQL比较

    在数据库编程领域,TSQL(Transact-SQL)和PLSQL(Procedural Language/SQL)是两种广泛使用的SQL方言,分别由微软的SQL Server和甲骨文的Oracle数据库系统支持。它们都提供了丰富的数学函数和操作来处理数值计算,...

    plsql-unit-test:猴子补丁测试

    PLSQL单元测试 该gem旨在用于测试PLSQL代码。 大多数测试框架是由Test :: Unit提供的,而这个gem仅添加了一些方便的方法,这些方法在单元测试PLSQL代码时非常有用。 依存关系 此宝石取决于: 为Oracle提供JDBC接口 ...

    PLSQL10-GGS(20131030).zip

    PLSQL,全称为Procedural Language/SQL,是Oracle数据库附带的一种编程语言,它将SQL语句嵌入到一种过程式的语言中,使得开发者能够更有效地管理和操作Oracle数据库。这个"PLSQL10-GGS(20131030).zip"文件是一个绿色...

    PLSQL Developer-12.rar

    PLSQL Developer是一款强大的Oracle数据库开发工具,专为数据库管理员、开发者和分析师设计,提供了一站式的解决方案,用于编写、测试和调试PL/SQL代码。它简化了与Oracle数据库的交互,提高了开发效率,使得数据库...

    plsql-cop-sonar:用于SonarQube的PLSQL Cop

    用于SonarQube的db * CODECOP 介绍 DB * CODECOP为SonarQube是一个插件 。 该插件分析SQL和PL / SQL代码并计算各种指标,并检查该代码是否符合 。 静态代码分析通常作为连续集成设置的一部分启动,例如在Jenkins或...

Global site tag (gtag.js) - Google Analytics