建立测试数据库表STUDENT,COURSE,SC和视图mysc
这里我写了个脚本t1.sql 放在$HOME/oraprg目录下
-- NAME
-- t1.sql
--
-- DESCRIPTION
-- This script creates the SQL*Plus demonstration tables in the
-- current schema. It should be STARTed by each user wishing to
-- access the tables. To remove the tables use the demodrop.sql
-- script.
--
-- USAGE
-- SQL> START t1.sql
--
--
SET TERMOUT ON
PROMPT Building demonstration tables. Please wait.
SET TERMOUT OFF
DROP TABLE STUDENT;
DROP TABLE COURSE;
DROP TABLE SC;
DROP view mysc;
CREATE TABLE STUDENT(
"SNO" CHAR(7) NOT NULL PRIMARY KEY,
"SNAME" CHAR(8) NOT NULL ,
"SEX" CHAR(2) NOT NULL ,
"BDATE" DATE ,
"DIR" CHAR(16) ) ;
insert into student values('9309203','王小1','女',TO_DATE('17-DEC-1977','DD-MON-YYYY'),'计算机科学');
insert into student values('9302203','王小2','男',TO_DATE('22-APR-1976','DD-MON-YYYY'),'计算机软件');
insert into student values('9402203','王小3','男',TO_DATE('17-JUL-1975','DD-MON-YYYY'),'理论物理');
insert into student values('9302303','王小4','男',TO_DATE('12-APR-1975','DD-MON-YYYY'),'基础数学');
insert into student values('9402208','王小5','男',TO_DATE('17-APR-1975','DD-MON-YYYY'),'空间物理');
CREATE TABLE COURSE (
"CNO" CHAR(6) NOT NULL PRIMARY KEY,
"CNAME" CHAR(12) NOT NULL ,
"TEACHER" CHAR(8) NOT NULL ,
"TIME" SMALLINT NOT NULL) ;
insert into course values('000001','数理逻辑','王元元',120);
insert into course values('000002','数据库基础','王能斌',30);
insert into course values('000003','数据库设计','王能斌',60);
insert into course values('000004','数据库实现','王能斌',120);
insert into course values('000005','数据结构','苏运霖',120);
insert into course values('000006','代数拓扑','江泽涵',80);
insert into course values('000007','理论力学','钱伟长',80);
CREATE TABLE SC (
"SNO" CHAR(7) NOT NULL ,
"CNO" CHAR(6) NOT NULL ,
"GRADE" DEC(6,2),
PRIMARY KEY(SNO,CNO),
FOREIGN KEY(SNO)
REFERENCES student
ON DELETE CASCADE,
FOREIGN KEY(CNO)
REFERENCES course
ON DELETE CASCADE
);
insert into sc values('9309203','000001',88);
insert into sc values('9309203','000002',98);
insert into sc values('9309203','000003',68);
insert into sc values('9302203','000001',98);
insert into sc values('9302203','000002',99);
insert into sc values('9302203','000003',89);
insert into sc values('9302203','000004',99);
insert into sc values('9402203','000001',99);
insert into sc values('9402203','000005',88);
insert into sc values('9402203','000006',86);
insert into sc values('9402203','000007',98);
insert into sc values('9302303','000001',100);
insert into sc values('9302303','000006',100);
insert into sc values('9302303','000007',100);
insert into sc values('9402208','000006',84);
insert into sc values('9402208','000007',85);
create view mysc as select sc.sno,sc.cno,sc.grade,student.sname,course.cname
from sc,student,course where sc.sno=student.sno and sc.cno=course.cno;
COMMIT;
SET TERMOUT ON
PROMPT Demonstration table build is complete.
exit
运行该脚本:
启动数据库实例。
$cd oraprg
$sqlplus scott/tiger
SQL>start t1(or @t1)
在$HOME/oraprg目录下建立以下三个文件
1、config.mk
#===========================================================
# Config.mk -- Configurations for all subdirectory
# Time-stamp: <2006-08-10 15:13:40 当当717>
# Copyright (c) 当当717 All rights reserved.
#===========================================================
# Edit the following for your installation
CC = gcc
X11INC = /usr/X11R6/include
X11LIB = /usr/X11R6/lib
MOTIFINC= /usr/X11R6/include
MOTIFLIB= /usr/X11R6/lib
ORACLEINC= -I. -I/home/oracle/OraHome1/precomp/public -I/home/oracle/OraHome1/rdbms/public -I/home/oracle/OraHome1/rdbms/demo -I/home/oracle/OraHome1/plsql/public -I/home/oracle/OraHome1/network/public
LIBS = -L/home/oracle/OraHome1/lib/ -lclntsh `cat /home/oracle/OraHome1/lib/sysliblist` -ldl -lm -lXm -lXt -lX11 -lXext
DFLAGS= -DLINUX -D_GNU_SOURCE -D_SVID_GETTOD -DSLTS_ENABLE -DSLMXMX_ENABLE -D_REENTRANT -DREENTRANT -DNS_THREADS
#===========================================================
# Compiler and linker flags
CFLAGS = -O3 -I$(X11INC) -I$(MOTIFINC) $(ORACLEINC) $(DFLAGS)
LFLAGS = -O -L$(X11LIB) -L$(MOTIFLIB) $(LIBS)
注意:这里有些库和INCLUDE目录是为了支持motif和xwindow,如果是字符环境下完全可以去掉。
2、makefile
#===========================================================
# Makefile -- Make file for Xwelcome program
# Time-stamp: <2006-08-10 15:13:40 当当717>
# Copyright (c) 当当717 All rights reserved.
#===========================================================
CONFIG = ./config.mk
include $(CONFIG)
.SUFFIXES: .c
.c.o:
$(CC) $(CFLAGS) -c $<
#===========================================================
# This program's object code files
OBJS = 1.o
#===========================================================
# File dependencies and rules
all: 1
objs: $(OBJS)
clean:
rm -f $(OBJS)
rm -f 1.c
rm -f *.lis
rm -f *~
rm -f tp*
rm -f 1
student: $(OBJS)
$(CC) -o $@ $(LFLAGS) $(OBJS) $(LIBS)
1.o: 1.c
1.c: 1.pc
proc parse=no iname=1.pc USER=scott/tiger include=$(ORACLE_HOME)/precomp/public
3、1.pc(源程序)
/*本orcale pro*c测试程序根据IBM-DB2V7.2中列子改写而得*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlca.h>
#include <sqlcpr.h>
void sql_error(msg)
char *msg;
{
char err_msg[512];
size_t buf_len, msg_len;
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("/n%s/n", msg);
/* Call sqlglm() to get the complete text of the
* error message.
*/
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);
printf("%.*s/n", msg_len, err_msg);
EXEC SQL ROLLBACK RELEASE;
exit(EXIT_FAILURE);
}
EXEC SQL BEGIN DECLARE SECTION;
char *userid="scott";
char *passwd="tiger";
char sno[8];
char sname[9];
char cname[13];
float grade ;
short dept;
short ind1;
char qstring[80];
EXEC SQL END DECLARE SECTION;
/*可能定义了多余的主变量*/
int main()
{
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--");
EXEC SQL CONNECT :userid IDENTIFIED BY :passwd;
if (sqlca.sqlcode == 0)
printf("连接成功/n/n");
else{
printf("连接失败,exit(0)");
getchar();
exit(0);
}
EXEC SQL DECLARE c2 CURSOR FOR
SELECT SNO,SNAME FROM STUDENT;
printf( "%-13s%-8s/n","学生姓名","学号");
printf( "%-13s%-8s/n","--------","------");
EXEC SQL OPEN c2;
do {
EXEC SQL FETCH c2 INTO :sname,:sno; /* :rk.3:erk. */
if (sqlca.sqlcode!= 0) break;
printf( "%-13s%-8s/n", sname,sno);
} while ( 1 );
printf("查询学生成绩,请输入学生号/n");
scanf("%7s",&sno);
strcpy(qstring,"SELECT sname,cname,grade FROM MYSC WHERE SNO=:para_sno");
/*oracle8i已经支持动态游标的实名参数(这里是para_sno),
这个在IBM-DB2V7中还是不行,必须要用?来代替para_sno*/
EXEC SQL PREPARE q1 FROM :qstring;
EXEC SQL DECLARE c1 CURSOR FOR q1;
EXEC SQL OPEN c1 USING :sno;
printf( "%-13s%-15s%-6s/n", "学生姓名","课程名", "成绩");
printf( "%-13s%-15s%-6s/n", "--------","----------", "-----");
do {
EXEC SQL FETCH c1 INTO :sname,:cname,:grade; /* :rk.3:erk. */
if (sqlca.sqlcode != 0) break;
printf( "%-13s%-15s%-6.2f/n", sname, cname, grade);
} while ( 1 );
return 0;
}
当然在编译之前必须要配置$ORACLE_HOME/precomp/admin/pcscfg.cfg文件,这个要根据不同的环境来配置,在bluepoint2.0下可以这样写:
sys_include=(/usr/lib/gcc-lib/i386-bluepoint-linux/egcs-2.91.66/include,$ORACLE_HOME/precomp/public,/usr/include,/usr/include/sys)
编译并运行
$cd $HOME/oraprg
$make
$./1
这里我写了个脚本t1.sql 放在$HOME/oraprg目录下
-- NAME
-- t1.sql
--
-- DESCRIPTION
-- This script creates the SQL*Plus demonstration tables in the
-- current schema. It should be STARTed by each user wishing to
-- access the tables. To remove the tables use the demodrop.sql
-- script.
--
-- USAGE
-- SQL> START t1.sql
--
--
SET TERMOUT ON
PROMPT Building demonstration tables. Please wait.
SET TERMOUT OFF
DROP TABLE STUDENT;
DROP TABLE COURSE;
DROP TABLE SC;
DROP view mysc;
CREATE TABLE STUDENT(
"SNO" CHAR(7) NOT NULL PRIMARY KEY,
"SNAME" CHAR(8) NOT NULL ,
"SEX" CHAR(2) NOT NULL ,
"BDATE" DATE ,
"DIR" CHAR(16) ) ;
insert into student values('9309203','王小1','女',TO_DATE('17-DEC-1977','DD-MON-YYYY'),'计算机科学');
insert into student values('9302203','王小2','男',TO_DATE('22-APR-1976','DD-MON-YYYY'),'计算机软件');
insert into student values('9402203','王小3','男',TO_DATE('17-JUL-1975','DD-MON-YYYY'),'理论物理');
insert into student values('9302303','王小4','男',TO_DATE('12-APR-1975','DD-MON-YYYY'),'基础数学');
insert into student values('9402208','王小5','男',TO_DATE('17-APR-1975','DD-MON-YYYY'),'空间物理');
CREATE TABLE COURSE (
"CNO" CHAR(6) NOT NULL PRIMARY KEY,
"CNAME" CHAR(12) NOT NULL ,
"TEACHER" CHAR(8) NOT NULL ,
"TIME" SMALLINT NOT NULL) ;
insert into course values('000001','数理逻辑','王元元',120);
insert into course values('000002','数据库基础','王能斌',30);
insert into course values('000003','数据库设计','王能斌',60);
insert into course values('000004','数据库实现','王能斌',120);
insert into course values('000005','数据结构','苏运霖',120);
insert into course values('000006','代数拓扑','江泽涵',80);
insert into course values('000007','理论力学','钱伟长',80);
CREATE TABLE SC (
"SNO" CHAR(7) NOT NULL ,
"CNO" CHAR(6) NOT NULL ,
"GRADE" DEC(6,2),
PRIMARY KEY(SNO,CNO),
FOREIGN KEY(SNO)
REFERENCES student
ON DELETE CASCADE,
FOREIGN KEY(CNO)
REFERENCES course
ON DELETE CASCADE
);
insert into sc values('9309203','000001',88);
insert into sc values('9309203','000002',98);
insert into sc values('9309203','000003',68);
insert into sc values('9302203','000001',98);
insert into sc values('9302203','000002',99);
insert into sc values('9302203','000003',89);
insert into sc values('9302203','000004',99);
insert into sc values('9402203','000001',99);
insert into sc values('9402203','000005',88);
insert into sc values('9402203','000006',86);
insert into sc values('9402203','000007',98);
insert into sc values('9302303','000001',100);
insert into sc values('9302303','000006',100);
insert into sc values('9302303','000007',100);
insert into sc values('9402208','000006',84);
insert into sc values('9402208','000007',85);
create view mysc as select sc.sno,sc.cno,sc.grade,student.sname,course.cname
from sc,student,course where sc.sno=student.sno and sc.cno=course.cno;
COMMIT;
SET TERMOUT ON
PROMPT Demonstration table build is complete.
exit
运行该脚本:
启动数据库实例。
$cd oraprg
$sqlplus scott/tiger
SQL>start t1(or @t1)
在$HOME/oraprg目录下建立以下三个文件
1、config.mk
#===========================================================
# Config.mk -- Configurations for all subdirectory
# Time-stamp: <2006-08-10 15:13:40 当当717>
# Copyright (c) 当当717 All rights reserved.
#===========================================================
# Edit the following for your installation
CC = gcc
X11INC = /usr/X11R6/include
X11LIB = /usr/X11R6/lib
MOTIFINC= /usr/X11R6/include
MOTIFLIB= /usr/X11R6/lib
ORACLEINC= -I. -I/home/oracle/OraHome1/precomp/public -I/home/oracle/OraHome1/rdbms/public -I/home/oracle/OraHome1/rdbms/demo -I/home/oracle/OraHome1/plsql/public -I/home/oracle/OraHome1/network/public
LIBS = -L/home/oracle/OraHome1/lib/ -lclntsh `cat /home/oracle/OraHome1/lib/sysliblist` -ldl -lm -lXm -lXt -lX11 -lXext
DFLAGS= -DLINUX -D_GNU_SOURCE -D_SVID_GETTOD -DSLTS_ENABLE -DSLMXMX_ENABLE -D_REENTRANT -DREENTRANT -DNS_THREADS
#===========================================================
# Compiler and linker flags
CFLAGS = -O3 -I$(X11INC) -I$(MOTIFINC) $(ORACLEINC) $(DFLAGS)
LFLAGS = -O -L$(X11LIB) -L$(MOTIFLIB) $(LIBS)
注意:这里有些库和INCLUDE目录是为了支持motif和xwindow,如果是字符环境下完全可以去掉。
2、makefile
#===========================================================
# Makefile -- Make file for Xwelcome program
# Time-stamp: <2006-08-10 15:13:40 当当717>
# Copyright (c) 当当717 All rights reserved.
#===========================================================
CONFIG = ./config.mk
include $(CONFIG)
.SUFFIXES: .c
.c.o:
$(CC) $(CFLAGS) -c $<
#===========================================================
# This program's object code files
OBJS = 1.o
#===========================================================
# File dependencies and rules
all: 1
objs: $(OBJS)
clean:
rm -f $(OBJS)
rm -f 1.c
rm -f *.lis
rm -f *~
rm -f tp*
rm -f 1
student: $(OBJS)
$(CC) -o $@ $(LFLAGS) $(OBJS) $(LIBS)
1.o: 1.c
1.c: 1.pc
proc parse=no iname=1.pc USER=scott/tiger include=$(ORACLE_HOME)/precomp/public
3、1.pc(源程序)
/*本orcale pro*c测试程序根据IBM-DB2V7.2中列子改写而得*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlca.h>
#include <sqlcpr.h>
void sql_error(msg)
char *msg;
{
char err_msg[512];
size_t buf_len, msg_len;
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("/n%s/n", msg);
/* Call sqlglm() to get the complete text of the
* error message.
*/
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);
printf("%.*s/n", msg_len, err_msg);
EXEC SQL ROLLBACK RELEASE;
exit(EXIT_FAILURE);
}
EXEC SQL BEGIN DECLARE SECTION;
char *userid="scott";
char *passwd="tiger";
char sno[8];
char sname[9];
char cname[13];
float grade ;
short dept;
short ind1;
char qstring[80];
EXEC SQL END DECLARE SECTION;
/*可能定义了多余的主变量*/
int main()
{
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--");
EXEC SQL CONNECT :userid IDENTIFIED BY :passwd;
if (sqlca.sqlcode == 0)
printf("连接成功/n/n");
else{
printf("连接失败,exit(0)");
getchar();
exit(0);
}
EXEC SQL DECLARE c2 CURSOR FOR
SELECT SNO,SNAME FROM STUDENT;
printf( "%-13s%-8s/n","学生姓名","学号");
printf( "%-13s%-8s/n","--------","------");
EXEC SQL OPEN c2;
do {
EXEC SQL FETCH c2 INTO :sname,:sno; /* :rk.3:erk. */
if (sqlca.sqlcode!= 0) break;
printf( "%-13s%-8s/n", sname,sno);
} while ( 1 );
printf("查询学生成绩,请输入学生号/n");
scanf("%7s",&sno);
strcpy(qstring,"SELECT sname,cname,grade FROM MYSC WHERE SNO=:para_sno");
/*oracle8i已经支持动态游标的实名参数(这里是para_sno),
这个在IBM-DB2V7中还是不行,必须要用?来代替para_sno*/
EXEC SQL PREPARE q1 FROM :qstring;
EXEC SQL DECLARE c1 CURSOR FOR q1;
EXEC SQL OPEN c1 USING :sno;
printf( "%-13s%-15s%-6s/n", "学生姓名","课程名", "成绩");
printf( "%-13s%-15s%-6s/n", "--------","----------", "-----");
do {
EXEC SQL FETCH c1 INTO :sname,:cname,:grade; /* :rk.3:erk. */
if (sqlca.sqlcode != 0) break;
printf( "%-13s%-15s%-6.2f/n", sname, cname, grade);
} while ( 1 );
return 0;
}
当然在编译之前必须要配置$ORACLE_HOME/precomp/admin/pcscfg.cfg文件,这个要根据不同的环境来配置,在bluepoint2.0下可以这样写:
sys_include=(/usr/lib/gcc-lib/i386-bluepoint-linux/egcs-2.91.66/include,$ORACLE_HOME/precomp/public,/usr/include,/usr/include/sys)
编译并运行
$cd $HOME/oraprg
$make
$./1