- 浏览: 59972 次
- 性别:
- 来自: 深圳
文章分类
最新评论
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 .
发表评论
-
Use the batch file to execute the sql
2010-06-23 11:18 1088REM ********** REM Modify the ... -
plsql learning - six:DML trigger
2009-12-29 14:10 797The trigger triggered when DML ... -
oracle datatype
2009-12-29 13:39 1176Oracle 9i共提供了16种标量数据类 ... -
plsql learning - five:trigger
2009-12-28 18:46 780When we want to update the info ... -
plsql learning - four:procedure
2009-12-25 16:38 876There are three type of paramet ... -
plsql learning - three:debug
2009-12-24 18:16 1066The debug method three: we can ... -
plsql learning - two:debug
2009-12-23 09:01 1009In order to use dbms_output.put ... -
Oracle functions
2009-12-18 11:32 1020Examples of function in oracle ... -
Oracle function : to_date and to_char
2009-12-17 17:35 977First,here are some defination ... -
The source of get connection of oracle and call procedure of oracle
2009-09-15 16:44 937For get the connection of oracl ... -
procedure example of oracle procedure
2009-09-14 18:12 693Some example for memory. creat ... -
Query the prime number by oracle procedure
2009-09-14 18:05 827Get the prime number between 10 ... -
Some examples of procudure of Sqlserver 2000
2009-09-07 18:18 828I have read SQL server 2000 sto ...
相关推荐
oracle12c程序连接时异常: ORA-01017: 用户名/口令无效; 登录被拒绝 的解决方案。
标题“IMP-00010:不是有效的导出文件,头部验证失败”涉及的是Oracle数据库导入(IMP)过程中遇到的一个常见错误。这个错误通常发生在尝试使用IMP工具从.dmp文件导入数据时,表明导入文件可能已损坏或不完整,导致...
您可能感兴趣的文章:常用的Oracle doc命令(收藏)Oracle 多行记录合并/连接/聚合字符串的几种方法Oracle中字符串连接的实现方法php连接oracle数据库及查询数据的方法plsql连接oracle数据库报ora 12154错
PL / SQL解析器 欢迎捐款,我对每一个帮助都很满意:) 概念 这将是一个多阶段解析器。这意味着,第一级仅解析一般结构。例如,它发现变量声明,但不检查定义的varchar2变量是否确实具有有效的字符串值。...
今天在项目中,使用Mybatis对oracle数据库进行操作的时候,报出ORA-00911: invalid character的错误,检查了一下SQL,发现都书写正确啊,复制到plsql上执行也都没问题,这什么原因呢? 注意:这里说的是用navicat...
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" 暗示了这个压缩包与PL/SQL(Oracle数据库的编程环境)以及Visual FoxPro(VFP)数据库系统有关,特别是涉及到在PL/SQL环境中通过ODBC(Open Database Connectivity)驱动程序访问DBF...
此工具的主要功能有:绿色,已破解,中文操作文档,附加dbatools插件(可用于扩展数据库表空间等,此功能经常使用,完全可保证),是学习oracle进行提高的必备工具,希望对学习oracle数据的朋友有所帮助。
plsqldev-trivadis-插件 Trivadis PL / SQL Cop的PL / SQL开发人员插件 如果您的PL / SQL Developer是x86,则需要将插件编译为x86,或者如果它是x64,则编译为x64。 您需要将插件放置在C:\ Program Files \ PLSQL ...
在Oracle数据库操作中,"ORA-01480: STR 绑定值的结尾 Null 字符缺失"是一个常见的错误,通常与数据插入或更新时的格式问题有关。这个错误通常意味着在处理字符串数据时,Oracle无法找到预期的NULL终止符,这可能是...
plsqldev-9.0.zip
PLSQL Developer-dba
oracle中表空间、权限、角色、用户的总结,其中包括表空间的建立,权限的配置,角色的生成和用户的挂接
PLSQL,全称为Procedural Language/SQL,是Oracle数据库提供的一个编程环境,结合了SQL(结构化查询语言)和过程式编程语言的特性。它主要用于编写与Oracle数据库交互的应用程序,尤其是数据库的后台处理部分。这个...
plsqldev-12.汉化版 Product Code:4t46t6vydkvsxekkvf3fjnpzy5wbuhphqz Serial Number:601769 password:xs374ca
在数据库编程领域,TSQL(Transact-SQL)和PLSQL(Procedural Language/SQL)是两种广泛使用的SQL方言,分别由微软的SQL Server和甲骨文的Oracle数据库系统支持。它们都提供了丰富的数学函数和操作来处理数值计算,...
PLSQL Developer是一款强大的Oracle数据库管理工具,主要用于编写、调试、执行和管理PL/SQL代码。这个"plsql1504-x64"安装包是针对64位操作系统的版本,1504可能指的是软件的特定版本号,通常包含了各种性能改进和新...
PLSQL单元测试 该gem旨在用于测试PLSQL代码。 大多数测试框架是由Test :: Unit提供的,而这个gem仅添加了一些方便的方法,这些方法在单元测试PLSQL代码时非常有用。 依存关系 此宝石取决于: 为Oracle提供JDBC接口 ...
PLSQL,全称为Procedural Language/SQL,是Oracle数据库附带的一种编程语言,它将SQL语句嵌入到一种过程式的语言中,使得开发者能够更有效地管理和操作Oracle数据库。这个"PLSQL10-GGS(20131030).zip"文件是一个绿色...
PLSQL Developer是一款强大的Oracle数据库开发工具,专为数据库管理员、开发者和分析师设计,提供了一站式的解决方案,用于编写、测试和调试PL/SQL代码。它简化了与Oracle数据库的交互,提高了开发效率,使得数据库...