Oracle创建表数据
说明： 我们需要通用的实验数据，emp表 与 dept表 但是数据库中有没有。 这时，我们可以手动创建。
Oracle数据库sql语句练习【emp和dept的连表查询由浅入深】
-- 创建表与数据
CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
);
INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902,
TO_DATE('2022-04-11', 'yyyy-mm-dd'), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698,
TO_DATE('2022-04-12', 'yyyy-mm-dd'), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698,
TO_DATE('2022-03-12', 'yyyy-mm-dd'), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839,
TO_DATE('2022-03-13', 'yyyy-mm-dd'), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,
TO_DATE('2022-03-14', 'yyyy-mm-dd'), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,
TO_DATE('2022-03-15', 'yyyy-mm-dd'), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,
TO_DATE('2022-03-16', 'yyyy-mm-dd'), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,
TO_DATE('2022-03-17', 'yyyy-mm-dd'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,
TO_DATE('2022-03-18', 'yyyy-mm-dd'), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,
TO_DATE('2022-03-19', 'yyyy-mm-dd'), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,
TO_DATE('2022-03-20', 'yyyy-mm-dd'), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,
TO_DATE('2022-03-21', 'yyyy-mm-dd'), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,
TO_DATE('2022-03-22', 'yyyy-mm-dd'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,
TO_DATE('2022-03-23', 'yyyy-mm-dd'), 1300, NULL, 10);
CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
);
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON ');
select *from emp
select *from dept
-- 添加约束（可选）
alter table emp add constraint emp_pk primary key(empno);
alter table dept add constraint dept_pk primary key(deptno);
alter table dept add constraint emp_fk_dept foreign key(deptno) references dept;
alter table emp add constraint emp_fk_emp foreign key(mgr) references emp;
VsCode新建项目
如果不知道自己的oracle安装路径，就看附录中的查看方法
项目配置
配置包含目录(头文件)
配置属性–>VC++目录–>包含目录(头文件)–》添加路径（注意最上方的一行 配置根据实际情况选，如果不知道按照图片上的选）
E:\oracle11g\product\11.2.0\dbhome_1\OCI\include
配置库目录（库目录）
配置属性–>VC++目录–>库目录–》添加路径（注意最上方的一行 配置根据实际情况选，如果不知道按照图片上的选）
E:\oracle11g\product\11.2.0\dbhome_1\OCI\lib\MSVC
配置依赖项
配置属性–>链接器–>输入–>附加依赖项–>oci.lib（注意最上方的一行 配置根据实际情况选，如果不知道按照图片上的选）
E:\oracle11g\product\11.2.0\dbhome_1\OCI\lib\MSVC
关闭SDL检查
配置属性–>C/C+±->SDL检查–>否（注意最上方的一行 配置根据实际情况选，如果不知道按照图片上的选）
代码部分
引入头文件
右键头文件-点击添加-选择现有项。将OCI\include的oci.h引入进来。
C代码
#define _CRT_SECURE_NO_WARNINGS //这个宏定义最好要放到.c文件的第一行
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>
static text *username = (text *) "scott";
static text *password = (text *) "oracle";
/* Define SQL statements to be used in program. */
static text *insert = (text *)"INSERT INTO emp(empno, ename, job, sal, deptno)\
VALUES (:empno, :ename, :job, :sal, :deptno)";
static text *seldept = (text *)"SELECT dname FROM dept WHERE deptno = :1";
static text *maxemp = (text *)"SELECT NVL(MAX(empno), 0) FROM emp";
static text *selemp = (text *)"SELECT ename, job FROM emp";
static OCIEnv *envhp;
static OCIError *errhp;
static void checkerr(/*_ OCIError *errhp, sword status _*/);
static void cleanup(/*_ void _*/);
static void myfflush(/*_ void _*/);
int main(/*_ int argc, char *argv[] _*/);
static sword status;
int main()
{
sword empno, sal, deptno;
sword len, len2, rv, dsize, dsize2;
sb4 enamelen = 10;
sb4 joblen = 9;
sb4 deptlen = 14;
sb2 sal_ind, job_ind;
sb2 db_type, db2_type;
sb1 name_buf[20], name2_buf[20];
text *cp, *ename, *job, *dept;
sb2 ind[2]; /* indicator */
ub2 alen[2]; /* actual length */
ub2 rlen[2]; /* return length */
OCIDescribe *dschndl1 = (OCIDescribe *)0,
*dschndl2 = (OCIDescribe *)0,
*dschndl3 = (OCIDescribe *)0;
OCISession *authp = (OCISession *)0; /* 用户会话句柄 */
OCIServer *srvhp; /* 服务器句柄 */
OCISvcCtx *svchp; /* 服务句柄 */
OCIStmt *inserthp,
*stmthp,
*stmthp1;
OCIDefine *defnp = (OCIDefine *)0;
OCIBind *bnd1p = (OCIBind *)0; /* the first bind handle */
OCIBind *bnd2p = (OCIBind *)0; /* the second bind handle */
OCIBind *bnd3p = (OCIBind *)0; /* the third bind handle */
OCIBind *bnd4p = (OCIBind *)0; /* the fourth bind handle */
OCIBind *bnd5p = (OCIBind *)0; /* the fifth bind handle */
OCIBind *bnd6p = (OCIBind *)0; /* the sixth bind handle */
sword errcode = 0;
/* 将模式初始化为线程和对象环境 */
errcode = OCIEnvCreate((OCIEnv **)&envhp, (ub4)OCI_DEFAULT,
(dvoid *)0, (dvoid * (*)(dvoid *, size_t)) 0,
(dvoid * (*)(dvoid *, dvoid *, size_t)) 0,
(void(*)(dvoid *, dvoid *)) 0, (size_t)0, (dvoid **)0);
if (errcode != 0) {
(void)printf("OCIEnvCreate failed with errcode = %d.\n", errcode);
exit(1);
}
/* 分配一个错误句柄 */
(void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&errhp, OCI_HTYPE_ERROR,
(size_t)0, (dvoid **)0);
/* 分配一个服务器句柄 */
(void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&srvhp, OCI_HTYPE_SERVER,
(size_t)0, (dvoid **)0);
/* 分配一个服务句柄 */
(void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&svchp, OCI_HTYPE_SVCCTX,
(size_t)0, (dvoid **)0);
/* 创建服务器上下文 指定要使用的数据库服务器。该参数指向一个字符串，该字符串指定一个连接字符串或一个服务点。
如果连接字符串是NULL，则此调用附加到默认主机。
字符串本身可能处于UTF-16编码模式，也可能不处于编码模式，这取决于mode应用程序环境句柄中的 或设置的长度在dblink中指定dblink_len。
调用者可以在dblink返回时释放指针*/
(void)OCIServerAttach(srvhp, errhp, (text *)"", strlen(""), 0);
/* 在服务上下文句柄中设置服务器属性*/
(void)OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp,
(ub4)0, OCI_ATTR_SERVER, (OCIError *)errhp);
/* 分配一个用户会话句柄 */
(void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&authp,
(ub4)OCI_HTYPE_SESSION, (size_t)0, (dvoid **)0);
/* 在用户会话句柄中设置用户名属性 */
(void)OCIAttrSet((dvoid *)authp, (ub4)OCI_HTYPE_SESSION,
(dvoid *)username, (ub4)strlen((char *)username),
(ub4)OCI_ATTR_USERNAME, errhp);
/* 在用户会话句柄中设置密码属性 */
(void)OCIAttrSet((dvoid *)authp, (ub4)OCI_HTYPE_SESSION,
(dvoid *)password, (ub4)strlen((char *)password),
(ub4)OCI_ATTR_PASSWORD, errhp);
checkerr(errhp, OCISessionBegin(svchp, errhp, authp, OCI_CRED_RDBMS,
(ub4)OCI_DEFAULT));
/* 在服务上下文句柄中设置用户会话属性*/
(void)OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX,
(dvoid *)authp, (ub4)0,
(ub4)OCI_ATTR_SESSION, errhp);
checkerr(errhp, OCIHandleAlloc((dvoid *)envhp, (dvoid **)&stmthp,
OCI_HTYPE_STMT, (size_t)0, (dvoid **)0));
checkerr(errhp, OCIHandleAlloc((dvoid *)envhp, (dvoid **)&stmthp1,
OCI_HTYPE_STMT, (size_t)0, (dvoid **)0));
/* Retrieve the current maximum employee number. */
checkerr(errhp, OCIStmtPrepare(stmthp, errhp, maxemp,
(ub4)strlen((char *)maxemp),
(ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));
/* bind the input variable */
checkerr(errhp, OCIDefineByPos(stmthp, &defnp, errhp, 1, (dvoid *)&empno,
(sword) sizeof(sword), SQLT_INT, (dvoid *)0, (ub2 *)0,
(ub2 *)0, OCI_DEFAULT));
/* execute and fetch */
/*将应用程序请求与服务器相关联。*/
if (status = OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0,
(CONST OCISnapshot *) NULL, (OCISnapshot *)NULL, OCI_DEFAULT))
{
if (status == OCI_NO_DATA)
empno = 10;
else
{
checkerr(errhp, status);
cleanup();
return OCI_ERROR;
}
}
/*
* When we bind the insert statement we also need to allocate the storage
* of the employee name and the job description.
* Since the lifetime of these buffers are the same as the statement, we
* will allocate it at the time when the statement handle is allocated; this
* will get freed when the statement disappears and there is less
* fragmentation.
*
* sizes required are enamelen+2 and joblen+2 to allow for \n and \0
*
*/
checkerr(errhp, OCIHandleAlloc((dvoid *)envhp, (dvoid **)&inserthp,
OCI_HTYPE_STMT, (size_t)enamelen + 2 + joblen + 2,
(dvoid **)&ename));
job = (text *)(ename + enamelen + 2);
checkerr(errhp, OCIStmtPrepare(stmthp, errhp, insert,
(ub4)strlen((char *)insert),
(ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));
checkerr(errhp, OCIStmtPrepare(stmthp1, errhp, seldept,
(ub4)strlen((char *)seldept),
(ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));
/* Bind the placeholders in the INSERT statement. */
if ((status = OCIBindByName(stmthp, &bnd1p, errhp, (text *) ":ENAME",
-1, (dvoid *)ename,
enamelen + 1, SQLT_STR, (dvoid *)0,
(ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT)) ||
(status = OCIBindByName(stmthp, &bnd2p, errhp, (text *) ":JOB",
-1, (dvoid *)job,
joblen + 1, SQLT_STR, (dvoid *)&job_ind,
(ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT)) ||
(status = OCIBindByName(stmthp, &bnd3p, errhp, (text *) ":SAL",
-1, (dvoid *)&sal,
(sword) sizeof(sal), SQLT_INT, (dvoid *)&sal_ind,
(ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT)) ||
(status = OCIBindByName(stmthp, &bnd4p, errhp, (text *) ":DEPTNO",
-1, (dvoid *)&deptno,
(sword) sizeof(deptno), SQLT_INT, (dvoid *)0,
(ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT)) ||
(status = OCIBindByName(stmthp, &bnd5p, errhp, (text *) ":EMPNO",
-1, (dvoid *)&empno,
(sword) sizeof(empno), SQLT_INT, (dvoid *)0,
(ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT)))
{
checkerr(errhp, status);
cleanup();
return OCI_ERROR;
}
/* Bind the placeholder in the "seldept" statement. */
if (status = OCIBindByPos(stmthp1, &bnd6p, errhp, 1,
(dvoid *)&deptno, (sword) sizeof(deptno), SQLT_INT,
(dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT))
{
checkerr(errhp, status);
cleanup();
return OCI_ERROR;
}
/* Allocate the dept buffer now that you have length. */
/* the deptlen should eventually get from dschndl3. */
deptlen = 14;
dept = (text *)malloc((size_t)deptlen + 1);
/* Define the output variable for the select-list. */
if (status = OCIDefineByPos(stmthp1, &defnp, errhp, 1,
(dvoid *)dept, deptlen + 1, SQLT_STR,
(dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT))
{
checkerr(errhp, status);
cleanup();
return OCI_ERROR;
}
for (;;)
{
/* Prompt for employee name. Break on no name. */
printf("\nEnter employee name (or CR to EXIT): ");
fgets((char *)ename, (int)enamelen + 1, stdin);
cp = (text *)strchr((char *)ename, '\n');
if (cp == ename)
{
printf("Exiting... ");
cleanup();
return OCI_SUCCESS;
}
if (cp)
*cp = '\0';
else
{
printf("Employee name may be truncated.\n");
myfflush();
}
/* Prompt for the employee's job and salary. */
printf("Enter employee job: ");
job_ind = 0;
fgets((char *)job, (int)joblen + 1, stdin);
cp = (text *)strchr((char *)job, '\n');
if (cp == job)
{
job_ind = -1; /* make it NULL in table */
printf("Job is NULL.\n");/* using indicator variable */
}
else if (cp == 0)
{
printf("Job description may be truncated.\n");
myfflush();
}
else
*cp = '\0';
printf("Enter employee salary: ");
scanf("%d", &sal);
myfflush();
sal_ind = (sal <= 0) ? -2 : 0; /* set indicator variable */
/*
* Prompt for the employee's department number, and verify
* that the entered department number is valid
* by executing and fetching.
*/
do
{
printf("Enter employee dept: ");
scanf("%d", &deptno);
myfflush();
if ((status = OCIStmtExecute(svchp, stmthp1, errhp, (ub4)1, (ub4)0,
(CONST OCISnapshot *) NULL, (OCISnapshot *)NULL, OCI_DEFAULT))
&& (status != OCI_NO_DATA))
{
checkerr(errhp, status);
cleanup();
return OCI_ERROR;
}
if (status == OCI_NO_DATA)
printf("The dept you entered doesn't exist.\n");
} while (status == OCI_NO_DATA);
/*
* Increment empno by 10, and execute the INSERT
* statement. If the return code is 1 (duplicate
* value in index), then generate the next
* employee number.
*/
empno += 10;
if ((status = OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0,
(CONST OCISnapshot *) NULL, (OCISnapshot *)NULL, OCI_DEFAULT))
&& status != 1)
{
checkerr(errhp, status);
cleanup();
return OCI_ERROR;
}
while (status == 1)
{
empno += 10;
if ((status = OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0,
(CONST OCISnapshot *) NULL, (OCISnapshot *)NULL, OCI_DEFAULT))
&& status != 1)
{
checkerr(errhp, status);
cleanup();
return OCI_ERROR;
}
} /* end for (;;) */
/* Commit the change. */
if (status = OCITransCommit(svchp, errhp, 0))
{
checkerr(errhp, status);
cleanup();
return OCI_ERROR;
}
printf("\n\n%s added to the %s department as employee number %d\n",
ename, dept, empno);
}
}
void checkerr(errhp, status)
OCIError *errhp;
sword status;
{
text errbuf[512];
sb4 errcode = 0;
switch (status)
{
case OCI_SUCCESS:
break;
case OCI_SUCCESS_WITH_INFO:
(void)printf("Error - OCI_SUCCESS_WITH_INFO\n");
break;
case OCI_NEED_DATA:
(void)printf("Error - OCI_NEED_DATA\n");
break;
case OCI_NO_DATA:
(void)printf("Error - OCI_NODATA\n");
break;
case OCI_ERROR:
(void)OCIErrorGet((dvoid *)errhp, (ub4)1, (text *)NULL, &errcode,
errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
(void)printf("Error - %.*s\n", 512, errbuf);
break;
case OCI_INVALID_HANDLE:
(void)printf("Error - OCI_INVALID_HANDLE\n");
break;
case OCI_STILL_EXECUTING:
(void)printf("Error - OCI_STILL_EXECUTE\n");
break;
case OCI_CONTINUE:
(void)printf("Error - OCI_CONTINUE\n");
break;
default:
break;
}
}
/*
* Exit program with an exit code.
*/
void cleanup()
{
if (envhp)
(void)OCIHandleFree((dvoid *)envhp, OCI_HTYPE_ENV);
return;
}
void myfflush()
{
eb1 buf[50];
fgets((char *)buf, 50, stdin);
}
/* end of file cdemo81.c */
Q&A
windows服务器下oracle数据库查看客户端安装位置
win+R键调出运行界面，输入 services.msc
bin 前面的路径就是 oracle 的根目录。
库计算机类型“x64”与目标计算机类型“x86”冲突
E:\oracle11g\product\11.2.0\dbhome_1\OCI\lib\MSVC\oci.lib : warning LNK4272: 库计算机类型“x64”与目标计算机类型“x86”冲突
两种解决方式
- 调整编译程序的类型
- 重新引入依赖项引入ociw32.lib的文件
配置属性–>链接器–>输入–>附加依赖项–>ociw32.lib（注意最上方的一行 配置根据实际情况选，如果不知道按照图片上的选）
附录
oracle的OCI目录下没有samples包
如果装的oracle不是完整版，oci目录下就没有samples包，这时就需要单独安装一下。我这里的版本是oracle11g，配套的版本在官网链接如下，里面win64_11gR2_examples.zip
https://www.oracle.com/cn/database/technologies/microsoft-windows.html
下载下来以后，双击安装就会在oracle的OCI目录下生成samples目录，如果安装失败了，就直接去资料包中网盘中去下载samples包吧。
samples文件夹中 每个c文件的Demo含义，参考官方文档https://docs.oracle.com/cd/E11882_01/appdev.112/e10646/ociabdem.htm
oci的手册下载
https://docs.oracle.com/cd/E11882_01/nav/portal_5.htm
参考
11g oci的手册
https://docs.oracle.com/cd/E11882_01/appdev.112/e10646/oci02bas.htm
11g oci的simple的demo列表
https://docs.oracle.com/cd/E11882_01/appdev.112/e10646/ociabdem.htm
21 oci的手册
https://docs.oracle.com/en/database/oracle/oracle-database/21/lnoci/oci-programming-basics.html
21 oci的simple的demo列表
https://docs.oracle.com/en/database/oracle/oracle-database/21/lnoci/oci-demo-programs.html
资料包
链接：https://pan.baidu.com/s/1ICPj3eHMDCxpi5zdWcsdag
提取码：jgeg
–来自百度网盘超级会员V4的分享
4863
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
