投稿
  1. 首页
  2. C

code•2022-4-19•C•阅读211

第1张

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 
#include 
#include 
#include 
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 checkerr
	 *
	 */


	(,errhpOCIHandleAlloc ((*dvoid ),envhp( *dvoid *)&,inserthp,
		OCI_HTYPE_STMT( size_t)+enamelen 2 + + joblen 2 ,(
		*dvoid *)&)ename);=
	job ( *text )(+ename + enamelen 2 );checkerr


	(,errhpOCIStmtPrepare (,stmthp, errhp, insert(
		)ub4strlen((char* ))insert,(
		)ub4,OCI_NTV_SYNTAX( )ub4)OCI_DEFAULT);checkerr

	(,errhpOCIStmtPrepare (,stmthp1, errhp, seldept(
		)ub4strlen((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,( )ub40,( *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,( )ub40,( *ub4 )0,) OCI_DEFAULT)|| (
			=status OCIBindByName (,stmthp& ,bnd3p, errhp( *text )":SAL" ,-
				1,( *dvoid )&,sal(
				)swordsizeof ()sal,, SQLT_INT( *dvoid )&,sal_ind(
				*ub2 )0,( *ub2 )0,( )ub40,( *ub4 )0,) OCI_DEFAULT)|| (
				=status OCIBindByName (,stmthp& ,bnd4p, errhp( *text )":DEPTNO" ,-
					1,( *dvoid )&,deptno(
					)swordsizeof ()deptno,, SQLT_INT( *dvoid )0,(
					*ub2 )0,( *ub2 )0,( )ub40,( *ub4 )0,) OCI_DEFAULT)|| (
					=status OCIBindByName (,stmthp& ,bnd5p, errhp( *text )":EMPNO" ,-
						1,( *dvoid )&,empno(
						)swordsizeof ()empno,, SQLT_INT( *dvoid )0,(
						*ub2 )0,( *ub2 )0,( )ub40,( *ub4 )0,) OCI_DEFAULT))checkerr
	{
		(,errhp) status;cleanup
		();return
		; OCI_ERROR}
	/*  Bind the placeholder in the "seldept" statement. */

	if
	( =status OCIBindByPos (,stmthp1& ,bnd6p, errhp1 ,(
		*dvoid )&,deptno( )swordsizeof ()deptno,, SQLT_INT(
		*dvoid )0,( *ub2 )0,( *ub2 )0,( )ub40,( *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, errhp1 ,(
		*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 ) enameprintf
		{
			("Exiting... ");cleanup
			();return
			; OCI_SUCCESS}
		if
		( )cp*
			=cp ';' elseprintf
		(
		{
			"Employee name may be truncated.\n");myfflush(
			);}/*  Prompt for the employee's job and salary. */
		printf
		(
		"Enter employee job: ");=0
		job_ind ; fgets(
		(char*) ,(jobint )+1joblen , stdin) ;=(
		cp * )text strchr((char*) ,'\n'job) ;if(
		== )cp = job-
		{
			job_ind 1 ;/* make it NULL in table */printf            (
			"Job is NULL.\n");/* using indicator variable */}else
		if
		( == 0cp ) printf(
		{
			"Job description may be truncated.\n");myfflush(
			);}else
		*
		=
			';'cp printf ("Enter employee salary: "

		);scanf("%d"
		,&); myfflushsal()
		;=(<=
		sal_ind 0 )sal ? -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"
			,&); myfflushdeptno()
			;if((
			= OCIStmtExecute(status , ,,svchp( stmthp1) errhp1 ,ub4()0 ,ub4(*)
				NULLCONST OCISnapshot ,( *) NULLOCISnapshot ,))&& OCI_DEFAULT(!=
				) )status checkerr OCI_NO_DATA(,
			{
				);errhpcleanup status()
				;return;}
				if OCI_ERROR(
			==
			) printfstatus ( OCI_NO_DATA"The dept you entered doesn't exist.\n"
				);}while(
		== ) ;status /*
		 *  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.
		 */ OCI_NO_DATA+=10

		;
		empno if ((
		= OCIStmtExecute(status , ,,svchp( stmthp) errhp1 ,ub4()0 ,ub4(*)
			NULLCONST OCISnapshot ,( *) NULLOCISnapshot ,))&& OCI_DEFAULT!=1
			) status checkerr (,
		{
			);errhpcleanup status()
			;return;}
			while OCI_ERROR(
		==
		1 )status += 10;
		{
			empno if ((
			= OCIStmtExecute(status , ,,svchp( stmthp) errhp1 ,ub4()0 ,ub4(*)
				NULLCONST OCISnapshot ,( *) NULLOCISnapshot ,))&& OCI_DEFAULT!=1
				) status checkerr (,
			{
				);errhpcleanup status()
				;return;}
				} OCI_ERROR/* end for (;;) */
			/* Commit the change. */
		if  (

		=
		OCITransCommit (status , ,0svchp) errhp) checkerr(,
		{
			);errhpcleanup status()
			;return;}
			printf OCI_ERROR(
		"\n\n%s added to the %s department as employee number %d\n"
		,,,)
			ename; dept} empno}void
	checkerr
(


, )*errhp; status;
OCIError [errhp512
sword status]
{
	text errbuf;=0;
	sb4 errcode switch ()

	case :statusbreak
	{
	; OCI_SUCCESScase
		:(
	void OCI_SUCCESS_WITH_INFO)
		printf("Error - OCI_SUCCESS_WITH_INFO\n");break;case
		:(
	void OCI_NEED_DATA)
		printf("Error - OCI_NEED_DATA\n");break;case
		:(
	void OCI_NO_DATA)
		printf("Error - OCI_NODATA\n");break;case
		:(
	void OCI_ERROR)
		OCIErrorGet((*),dvoid ()errhp1 ,ub4(*) NULLtext ,&,, (errcode)
			errbufsizeof (ub4) ,)errbuf;( OCI_HTYPE_ERRORvoid)
		printf("Error - %.*s\n",512,) ;break errbuf;case
		:(
	void OCI_INVALID_HANDLE)
		printf("Error - OCI_INVALID_HANDLE\n");break;case
		:(
	void OCI_STILL_EXECUTING)
		printf("Error - OCI_STILL_EXECUTE\n");break;case
		:(
	void OCI_CONTINUE)
		printf("Error - OCI_CONTINUE\n");break;default
		:break
	;}
		}/*
 *  Exit program with an exit code.
 */
	void
cleanup


(
) if()
{
	( voidenvhp)
		OCIHandleFree((*),dvoid );envhpreturn OCI_HTYPE_ENV;}
	voidmyfflush
(


) [50]
{
	eb1 buf;fgets((

	char*), 50,bufstdin ); }/* end of file cdemo81.c */
  • 调整编译程序的类型
    • 

  • 重新引入依赖项引入ociw32.lib的文件
    •
    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（注意最上方的一行 配置根据实际情况选，如果不知道按照图片上的选）

        附录 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的分享

        欢迎分享，转载请注明来源：内存溢出

        原文地址:https://54852.com/langs/674048.html

        赞 (0)
        打赏 微信扫一扫微信扫一扫 支付宝扫一扫支付宝扫一扫
        codecode管理员组
        00
        上一篇 2022-04-19
        下一篇2022-04-19

        发表评论

        登录后才能评论

        评论列表（0条）

          保存