笔试面试-数据库总结

数据:1T-- 早期的数据-----文海量 程序帮你删除增加(管理)----数据库管理系 数据在文 名字员工号----表 nan----行 记二维 二维表对应的最小单 一条记录的最小单 程序数据 netIP()net6login:openlabsqlplusopenlab/open123sqlplusscott/tiger SQL>desc表名SQL>desc ID(在表中数据的唯一编号 NOTNULL NOTNULL START_DATE(入职日期 COMMENTS(说明 MANAGER_ID( SQL>spool文件 ----退出之后不在到文如果想继续存入敲入令和结果-----SQL>spool文件 varchar2---字符串类型 sqlAselectselect{*，from select*from表名;selectidfroms_emp;selectid,last_name,salaryfroms_emp; summit2.sqlnetsqlplusselect[distinct]{*，字段名[alias],[字段名，字段名]}from表名; selecttitlefromselectdistinct titlefroms_emp; selecttitlezhiweifrom默认显示成大写------在别名上加双引 selecttitle"zhiwei"from 把所有人的拼接之后显selectlast_name,first_namefrom selectlast_name||first_namefrom+-*selectsalaryfroms_emp;selectsalary*12froms_emp;select fromNULL--------cNULL--------未知值不同于空格空字符串NULLNULLselectfirst_name,salary*12froms_emp; //logicerrororacle提供一个函数nvl p1p2p1p2p1NULLp2如果p1p1selectfirst_name,"yearfromselectfirst_name,"yearfroms_emp;selectfirst_name,"yearsalary"from oracleselectsysdatefrom selectsysdatefrom selectnvl(sysdate,'01-AUG-11')fromdual;selectnvl(NULL,'01-AUG-11')fromdual;Bwhere(条件限制记录 选salary1000select*froms_empwhere selectlast_name,salaryfroms_emp; selectlast_name,salaryfroms_empwhere注意:*能代表所有的列慎用字面值------12 oracleselectlast_name||''''||first_namefroms_emp;selectlast_name||123||first_namefroms_emp; 小写特殊字符last_namemyselectlast_name"myname"froms_emp;c中使用相同注意等号注意等于号用一个=id1selectlast_name||first_namenamefroms_empwhereid=1;sqlselectlast_name||first_namenamefroms_empwhereidlike'aa'like'aaa'true like'aab' 1wherenamelike'wang%'last_name带aselectlast_namefroms_empwherelast_namelikeNULLismanager_idselectlast_name,manager_idfroms_empwheremanager_id isnull;between and 8001500selectlast_name,salaryfroms_empwheresalarybetween 800and1500;注意：1.in中的数据排放1 10%把概率高的放前null aandblike应用于模糊查询 user_tables的表 selecttable_namefromuser_tableswheretable_namelike'%\_%'escape'\';转 s_empselect*fromuser_tableswheretable_namelike'S\_EMP'escape'\'; or工资8001500selectlast_name,salaryfroms_emp selectlast_name,salaryfroms_emp salary>=800andsalary<=1500; null----isnotnull ----in(list)----notinlikebetween <>selectidfroms_empwhereid!=1;selectidfroms_empwhere注意:notin------如果有null值则不能用notin COMMISSION_PCTfroms_empwhereCOMMISSION_PCTin(10); COMMISSION_PCTfroms_empwhereCOMMISSION_PCTnotin(10);selectCOMMISSION_PCTfromwherenvl(COMMISSION_PCT,0)not4142421000提示如果想表达的更清楚可以使用小括号selectlast_name,salaryfromwheredept_id=41or(dept_id=42and----这条选择出来的数据可能一点10004142selectlast_name,salaryfromwheresalary>1000and(dept_id=42ordept_id=41);selectsalary*12+100from select from Corderby(默认的排序----自然顺序字典顺序升序selectlast_name,salaryfroms_emporder last_nameselectlast_name,salaryfrom orderbysalaryselectlast_name,salaryfrom orderbysalaryselectlast_name,salaryfroms_emp orderby1;selectlast_name,salaryfroms_emp orderby2desc;(1,2selecttitle,salaryfroms_emporderbytitle,salary;selecttitle,salaryfroms_emporderbytitle,salaryselecttitle,salaryfroms_emporderbytitle注意：1.orderbyselect2.注意 mission_pctfroms_emporderby Dfunction selectnvl(manager_id,0)froms_emp;selectupper('abc')fromselectinitcap('oneworldonedream')fromdual;selectconcat('o','world')fromdual;select'o'||'world'fromdual;selectsubstr('o',1,4)fromdual;substr1selectlength(last_name),last_namefromselectlast_name,substr(last_name,1,2)fromselectlast_name,substr(last_name,length(last_name)-1,2)fromselectlast_name,substr(last_name,-2,2)fromlast_name5first_nameCarmenSELECTFIRST_NAMEFROMS_EMPwhereSELECTFIRST_NAMEFROMS_EMPwhereselecttitlefroms_empwherefirst_name='Carmen';selecttitle,first_namefrom whereand1000 last_name,salaryfroms_empwheresalary>1000orderbysalarydesc,last_namedesc;id1,3,5selectlast_name,idfroms_empwhereid=1orid=3or找出last_name长度是4并且工资大于1500的员工按工资升序selectlast_name,salaryfroms_empwherelength(last_name)=4andsalary>1500 orderbysalary;selectcount(nvl(manager_id,0))fromround四舍五入round(11.4,0)--- round(11.5)---trunc截 trunc(11.5)---0selectto_char(salary,'fm$099,999,999.99')froms_emp;selectto_char(salary,'fmL099,999,999.99')from环境变 selectuserenv('language')from NLS_LANG='SIMPLIFIED shsetenvNLS_LANG'SIMPLIFIED selectlast_namefroms_empwhereid='1';selectlast_namefroms_empwhereid=1;to_char可以不跟后面的格 就是简单的把对应的类型加上单引last_namemanager_id first_name,manager_idfrom first_name,nvl(manager_id,'BOSS')from first_name,nvl(to_char(manager_id),'BOSS')from集 selectlast_name,dept_id,namefrom selectlast_name,dept_id,namefrom wherewhere*Emutitableselect内连 selectlast_name,dept_id,namefrom whereselectlast_name,dept_id,namefroms_empe,s_deptdwheredept_id=d.id; last_name froms_empe,s_deptd,s_regionrwheree.dept_id=d.idandcolnamefor collast_namefor betweenlowsaland 12345 andlast_name,salaryselecte.last_name,e.salary,g.gradefroms_empe,salgradegwheree.salarybetweeng.losalands_emp121314253idmanager_id distinctm.last_name,m.id,e.manager_id s_empe,s_empmwhere8个25-8=17个普通员外连接（outerjoin）=内连接+匹配不上 如果是外连接的语法加号对面的表中的数据全都查询出来selectdistinctm.last_name,m.id,e.manager_id s_empe,s_empwhere distinctm.last_name,m.id,e.manager_id s_empe,s_empmwherem.id=e.manager_id(+) e.manager_idisnull;updates_empsetdept_id=nullwheredept_id=50;last_name,dept_idselectlast_name,dept_id,froms_empe,s_deptdwheree.dept_id=d.id;23显示所有员工对应的部门号和部门名称如果没有部门名称则显示'nodepartment'selectlast_name,dept_id,froms_empe,s_deptdwhere'nodepartment'selectlast_name,nvl(to_char(dept_id),'nodepartment')froms_empwheredept_idisselectlast_name,dept_id,nvl(,'nodepartment')froms_empe,s_deptdwheree.dept_id=d.id(+)andd.idisnull;要所有的普通员工（那些人不是） 显示所有级别对应的员工的last_name 'noemployee'要级别表中所有的数据那加号应该加在那边？selecte.last_name,e.salary,g.gradefroms_empe,salgradewheree.salarybetweeng.losalandg.hisal;selectnvl(e.last_name,'noemployee'),e.salary,g.gradefroms_empe,salgradewheree.salary(+)betweeng.losaland50updates_empsetsalary=0wheresql如下加号怎么加？selecte.first_name,e.salary,g.gradefroms_empe,salgradegwheree.salarybetweeng.losal(+)and外连接selectdistinctm.last_name,m.id,e.manager_id s_empe,s_empmwhereoracleselectdistinctm.last_name,m.id,e.manager_id s_empe,s_empmwherem.id=e.manager_id(+)ande.manager_idisnull;leftouterjoin/rightouterwhereon distinctm.last_name,m.id,e.manager_id s_emperightouterjoins_empmonwhereselectdistinctm.last_name,m.id,e.manager_id s_emperightouterjoins_empmonm.id=e.manager_idwheree.manager_idisnull;sqlselecte.first_name,e.salary,g.gradefroms_empe,salgradegwheree.salarybetweeng.losal(+)andselectfroms_empeleftouterjoinsalgradeone.salarybetweeng.losaland 等值连 between >=<=inlike外连接等值连 非等值连 between >=<=inlikesql99leftouterjoin /rightouterjoinfullouterjoin=左外+右外-重复的union/uoionall(union和并结果集时排重unionall)selectidfroms_empunionselectidfromselectidfroms_empunionallselectidfroms_emp;左外 右外selectfroms_empefullouterjoinsalgradeone.salarybetweeng.losalandg.hisal;---27selecte.first_name,e.salary,g.gradefroms_empeleftouterjoinsalgradeone.salarybetweeng.losalandg.hisalunionselecte.first_name,e.salary,g.gradefroms_emperightouterjoinsalgradeone.salarybetweeng.losalandg.hisal;26Fgroupby（分组组函数） selectsum(salary)froms_emp;selectmax(salary)froms_emp;groupbyorderdept_idselectdept_idfrom groupbyselectsalary,dept_idfrom groupbyselectsalary,dept_idfrom groupbyselectmax(salary),dept_idfrom groupbyselectsum(salary),dept_idfroms_emp groupbydept_idorderbysum(salary);selectsum(salary)s,dept_idfroms_empgroupbydept_idorderbys; selectsum(salary)s,dept_idfroms_empwheredept_id=41groupbyorderbys;selectsum(salary)s,dept_idfroms_empgroupbydept_idhaving orderbywheregroupby之前的groupbyordergroupbyselectgroupbyG 那些人是selectdistinctmanager_idfroms_emp;(得到所有的id和NULL)selectlast_name,id,manager_idfroms_empwhereidin(1,2,3,6,7,8,9,10);selectlast_name,id,manager_idfroms_empwherein(selectdistinctmanager_idfromselecttitlefroms_empselectfirst_name,titlefroms_empwheretitle=(selecttitlefroms_empwhereandhavingselectsum(salary)froms_empwheredept_id=42;selectsum(salary),dept_id froms_empgroupby sum(salary)>(selectfroms_empwherewherehavingfromselectsum(salary)s,dept_idfroms_empgroupbydept_idhaving orderbyselects,dept_id,nfrom(selectsum(salary)s,dept_id,max(name)nfroms_emp,s_deptwheredept_id=s_dept.idgroupbydept_idorderbys)where25125selectlast_name,froms_empe,s_deptd,s_regionwheree.dept_id=d.id 查询那些人是用外连接做和子查询(oracle/sql99)selectdistinctm.last_name,m.id,e.manager_id s_empe,s_empwherem.id=e.manager_id(+)ande.manager_idisnotselectlast_name,id,manager_idfroms_empwhereidin(select manager_idfromselectlast_name,id,manager_idfromwhereidnotin(select manager_idfromselectlast_name,id,manager_idfromwhereidnotin(select froms_empwheremanager_idisnotnotinselectdept_id,sum(salary),max()froms_empe,s_deptdwheree.dept_id=d.id groupbydept_idhavingsum(salary)>=(selectsum(salary)froms_empwheredept_id=10)anddept_id!=10;selectsum(salary)ss,dept_id froms_empgroupbydept_id ss>(selectsum(salary)froms_empwheredept_id=42); select groupby orderbydept_idselectmax(salary),min(last_name),dept_id,min() s_empe,s_deptdwheree.dept_id=d.idgroupbydept_id;selectsum(salary)s,dept_id,max(name)nfroms_emp,s_deptwheredept_id=s_dept.idgroupbydept_idorderby #唯一的*o一对 丈夫妻 一对 顾客订 多对 学生课 一个学生可以选多门课一个课程可不可以被多学生选如果这两个人的信息成双入对 你需要一个人信息的时并不需另一个 可以两张如何来表达一对多 1,2,3,4是11121314 拆1 112131411111c22334561 121314151621c222324252631现323334353 c 1111116222222633333361所有的字段不可再分面积(width/length)2第二范式3 1 zhangsan 2 zhangsan ( 保留字1 112 213141主键---------唯一标示一条记录的字段---------非 ---------一个表可以用多个非空唯一的字段但主键只有一个外键---------外键的值受限于的字段---------外键的值要么是的字段所拥有的值要么是E-R实体 表属 字类 字段类主 一个员 有多个顾 主 外学 课 关系表中外键外键 写成一个sql createtabletablename( fnamevarchar2(10),snamechar(10)insertintotablenamevalues(1,'abc','abc');select fromselect*fromtablenamewherefname='abcselect*fromtablenamewheresname='abc当这个字段的长度是不变的号码当这个字段的长度是变化 都做成定长 ----存取效率不18----255--- 255 select*fromtablenamewherefname=trim('abcnumber数字类型10e-13010e127number(p,s)-----p有效位sblob-----大二进制类型4gbclob-----大字符类型4gb droptable createtabletablename( hirdateinsertintotablenamevalues(1,'02-AUG-selectto_date('2009-08-08','yyyy-mm-dd')fromdual; hh----小时默认是12 to_charselectto_char(start_date,'yyyy-mm-ddhh24:mi:ssday')froms_emp;selectto_char(start_date,'yyyy')fromselectlast_name,start_datefroms_empwherestart_date selectlast_name,start_datefroms_empwhereto_char(start_date,'mm')=3;selectlast_name,start_datefroms_empwhereto_char(start_date,'mm')='03';sysdate----2011-08-29trunc(sysdate,'dd')------2011-08-29selectto_char(trunc(sysdate,'dd'),'yyyy-mm-ddhh24:mi:ss')fromdual;round(sysdate,'dd')------2011-08-30selectto_char(round(sysdate,'dd'),'yyyy-mm-ddhh24:mi:ss')fromdual;selectto_char(round(sysdate,'yy'),'yyyy-mm-ddhh24:mi:ss')fromdual; 12 11months_between(d1,d2)如果正好是整月selectmonths_between(sysdate,start_date)froms_emp;selectmonths_between('28-FEB-11','30-JUN-11')fromdual;next_day(sysdate)selectto_char(next_day(sysdate,'friday'),'yyyy-mm-ddhh24:mi:ss')from1okselectto_char(sysdate+1,'yyyy-mm-ddhh24:mi:ss')fromdual;时分秒信 selectto_char(last_day(sysdate),'yyyy-mm-ddhh24:mi:ss')fromdual;91了时分秒信息但默认显示只有日-月的缩写-两位年 如何来表达关系如何来设置约束createtable字段名类型 default值,字段名类型 default值主键------primary外键------foregin非空------not唯 检 create idnumber primarykey,tidnumberinsertintotestcreatetable idnumber,tidnumber,primarykey(id,pid)注意:notnull约束只有列级约束没有表级约束idnumber varchar2(30) char(30)唯一salarynumber numberconstraint test_pkprimarykey, notnull, constrainttest_ukunique,salarynumbernotnullselectfromuser_constraintswherealtertabletestdropconstraintSYS_C check约束 numberconstraint test_pkprimarykey,salarynumbercheck(salary>1000)insertintotest insertintotest numberconstraint test1_pkprimarykey,salarynumber,check外键某张表唯一性字段 主表(父表 顾 cno--顾客编 建 createtable numberprimarykey, cnamevarchar2(30)insertintocustomercreatetable numberprimarykey, numberreferencesinsertintocordervalues(1,'001',null);insertintocordervalues(2,'001',1);insertintocordervalues(3,'001',1);1 selectfromcustomerc,corderwhere andnull事务------原子性一致性性持久性(AUCD)AB000 setsalary=salary-50000whereid=1;update set where性insertintocorder sqlserver自动提 drop createtablet(idnumberprimarykey,salarynumber); insertintotvalues(1,1000); insertintotvalues(2,1000); rollbacktoa; commitcreatesequenceselecttests.nextvalfromdual;(-pow(10,26),pow(10,27))insertintocustomercreatesequenceincrementbyn,步进默认是1 withn,起始值默认1maxvaluen/nomaxvalue,nomaxvaluepow(10,27)minvaluen/nominvalue,cachen/nocache20注：如果cycle的序列到最大值后回到默认值1createsequence incrementbystartwithcache索引从头找到尾全表扫描 索引查每次查找的数据量2%- 主 唯一约束自动建立索create customer_indexonsetautotrace select*fromcustomer;select*fromcustomer whereid=1;select*fromcustomer wherecno='abc';create customer_index1on 视图,分页？rowid视图限制数据库的select(selectsum(salary)s,dept_idfroms_empgroupbydept_id)select*(selectsum(salary)s,dept_idfroms_empgroupbydept_id)wheres=(selectmax(ss)from(selectsum(salary)ssfroms_empgroupbydept_id));createorreplaceviewcviewasselectid,last_name froms_emp;select*from 复杂的selectsalary,last_namefroms_emporderbysalary; selectrownum,salary,last_namefromselectrownum,salary,last_namefroms_empwhere如果按工资排序后取前5selectrownum,salary,last_namefroms_empwhererownum<6orderbysalary;//error1selectsalary,last_namefroms_emporderby2rownumwhereselectrownum,salary,last_namefrom(selectsalary,last_namefroms_emporderbysalary)whererownum<11;selectrownum,salary,last_namefrom(selectsalary,last_namefroms_emporderbysalary)whererownum<11andrownum>5;//norowsselect*from(selectrownumn,salary,last_namefrom(selectsalary,last_namefroms_emporderbysalary)whererownum<11)wheren>5;select*from(selectrownumn,salary,last_namefrom(selectsalary,last_namefroms_emporderbysalary)) n>5and有一张表里面有重复的数据 12345id, name idname1.数据库的技 ----数据库内部技proc/c++----针对cC++ ----oracle底层的通dbms_output.put_line('oworld');setserveroutput 赋 比 v_namevarchar2(30);selectlast_nameintov_namefroms_empwhereid=v_id; v_namevarchar2(30);selectlast_nameintov_namefroms_empwhereid=v_id;标示 给变量游标子程序变量的语 [CONSTANT]type[NOTNULL][:=value];const代表常量如果你想变量必须有初始值可以加notnullnull constantnumber:=10;v_nu:=100;--error 字符类 v_names_emp.last_name%type; selectlast_nameintov_namefroms_empwhereid=v_id;record isrecord( namevar_rectype_rectype_recvar_recvar_rec2type_rec;--selectid,last_nameintovar_recfrom where--selectidintovar_recfroms_empwhereid=1;//errorselectid,last_name intovar_rec.id,var_from whereid=1; isrecord( name为了写的简洁一 取表的一行作为类 selectid,last_nameintovar_rec.id,var_rec.last_namefroms_empwhereid=1;talbeTYPEtabletype(tableISTABLEtype（表中的值的数据类型）INDEXBYTYPEtable_charISTABLEOFchar(30)INDEXBY var_t(1):='os_empid1,3,5tableTYPEtable_empISTABLEOFs_emp%rowtypeINDEXBYBINARY_INTEGER; select*intovar_t(1)froms_empwhereid=1;select*intovar_t(2)froms_empwhereid=3;select*intovar_t(3)froms_empwhere<<outer>> dbms_output.put_line('inner:'||outer.var_n);--外部变量if 表达式thenelsif表达式thenend number;--没有赋初始值都是null var_n1=var_n2thenelsifvar_n1>var_n2thenelsifvar_n1<var_n2thenelsifvar_n1isnullandvar_n2isnullthendbms_output.put_line('var_n1andvar_n2isnull');dbms_output.put_line('var_n1isvar_n2');endif; number;--没有赋初始值都是null var_n1=var_n2thenelsifvar_n1>var_n2thenelsifvar_n1<var_n2thenelsifvar_n1isnullandvar_n2isnullthendbms_output.put_line('var_n1andvar_n2isnull');null;endif; 表达式thenend ifvar_n>10thenendif;endloop;s_emptableidsalarylast_nameidwhilewhile表达式loopendloop;110 whilevar_n<10loopendloop; 100条数据agefor110forain1..10loopendloop;a不用在declare中就可以直接使 goto名110 ifvar_n<11gotolabe_go;endif;--selectidfroms_empwhereid=1;insertintotestvalues('abc',sysdate);只有dml和dcl可以直接在plsql块中使用 如selectddlsql有专门的语法a变 selectlast_name,replace(last_name,'a','y')fromidtabletypetable_t istableofs_emp%rowtypeindexby select*intovar_temp(1)froms_empwhereid=1;select*intovar_temp(2)froms_empwhereid=2;select*intovar_temp(3)froms_empwhereforain1..3loopendloop;typetable_t istableofs_emp%rowtypeindexby select*intovar_temp(1)froms_empwhereid=1;select*intovar_temp(100)froms_empwhereid=2;select*intovar_temp(205)froms_empwhereforain1..3endloop; 得到ntypetable_t istableofs_emp%rowtypeindexby select*intovar_temp(1)froms_empwhereid=1;select*intovar_temp(100)froms_empwhereid=2;select*intovar_temp(205)froms_empwhereid=3;if(var_n=var_temp.last())thenendif;endloop;如果下标连续用其中的两个函数typetable_t istableofs_emp%rowtypeindexby select*intovar_temp(1)froms_empwhereid=1;select*intovar_temp(2)froms_empwhereid=2;select*intovar_temp(3)froms_empwhereforainvar_temp.first()..var_temp.last()loopendloop; 100条数据agecreatetablestudent123idnumberprimarykey,namevarchar(30),age ain1..100insertintostudent123values(test_students.nextval,'abc',23);endloop;游 4 emp_cursorisselect*from fetchemp_cursorintofetchclosecursor isselect*froms_emp;--游 openemp_cursorfetchemp_cursorintovar_emp;--提取数据fetchemp_cursorintovar_emp;closeemp_cursor;--关闭游标cursor isselect*froms_emp;--游 var_nnumber;selectcount(*)intovar_nfroms_emp;openemp_cursor;--打开游标forain1..var_nfetchemp_cursorintovar_emp;--提取数据endcloseemp_cursor;--关闭游标forcursor isselect*froms_emp;--游 forvarinemp_cursorloop--varendloop;如果我们使用loop 或者while 循环把游标的数据遍历出来cursor isselect*froms_emp;--游 openemp_cursor;--打开游标fetchemp_cursorintovar_emp;exitwhenemp_cursor%notfound;--当发现不了数据就退出endcloseemp_cursor;whilecursor isselect*froms_emp;--游 openemp_cursorfetchemp_cursorintovar_emp;--提取数据whileemp_cursor%foundloopfetchemp_cursorintovar_emp;--提取数据endcloseemp_cursor;0122 endselect*froms_empforupdate into单个变量，单个变量; intorecord变量; intorecord.idcursoremp_cursor(cpar isselect*froms_empwhereid>cpar;--游标 openemp_cursor(10)fetchemp_cursorintovar_emp;--提取数据whileemp_cursor%foundloopfetchemp_cursorintovar_emp;--提取数据endcloseemp_cursor;var_nnumber;selectid intovar_nfroms_empwhereid>5;whenTOO_MANY_ROWSthencursoremp_cursor(cpar isselect*froms_empwhereid>cpar;--游标 openemp_cursor(10)fetchemp_cursorintovar_emp;--提取数据whileemp_cursor%foundloopfetchemp_cursorintovar_emp;--提取数据endcloseemp_cursor;closewheninvalid_cursor whento_many_rows 系统自定义异常sql selectcount(*)into fromsql v_n>1then endif;select*intov_empfroms_emp; to_many_empsdbms_output.put_line('myexceptionto_many_emps'); selectcount(*)into fromsql v_n>1then endif;select*intov_empfroms_emp;如果你想捕获所有的异常用othersothers应该出现在异常处理的最后 selectcount(*)into fromsql v_n>1then endif;select*intov_empfroms_emp;when dbms_output.put_line('myexcepitonto_many_emps');whenothers plsq的异常处理1要异常2根据业务的不同情况抛出异3othersplsql中最重要的过程和函数createorreplaceprocedureo100forain1..100loopdbms_output.put_line('oworld');endloop;exec --调用o100这个过callcreateorreplaceprocedureo100(var_pvarchar2)forain1..100loopendloop;写一个有名字的过程带两个参数 age--问题关键在于拼接sqlcreatesequence createorreplaceaddstudent(var_namevarchar2,var_ageinsertintostudentsqlsqlsqlcreateorreplaceprocedureaddstudent(var_namevarchar2,var_agesqlstrsqlstr:='insertintostudentexecuteimmediatesqlstr;ddl31张表只有名字不同而结构完全相同day1..day31executeimmediate'createtable(idnumberprimary 写一个过程建立31张结构相同表名不同的表在写一个过程删除这31张表createorreplaceaddstudent(var_namevarchar2,var_ageinsertintostudent参数的默认类型是 负责输入的参out-----------负责输出的参数inout voidadd(int*age){}intage=0; 这个过程是得到s_emp表中记录createorreplaceproceduregetstudentcount(var_cinoutnumber)selectcount(*)intovar_c froms_emp;out只能在别的过程或者块中调用 number;--就是为调用得到人数的过程准备的变outinout查看过程令是descin desc 名字赋值不能出现在位置赋值左 createorreplaceaddstudent(var_namevarchar2,var_ageinsertintostudentcall函 过这个过程是得到s_emp表中记录createorreplacefunction selectcount(*)intovar_c froms_emp;returnvar_c;var_nnumber;var_pshowerrors写一个过程得到s_emp写一个函数得到s_emp createorreplacepackage pack_testis addstudent(agenumber); getempcountreturnnumber;createorreplacepackagebodypack_testis addstudent(agenumber)is getempcountreturnnumberis--函数的实现 returnvar_c;selectpack_test.getempcountfromdualdual作业:写一个包里面有一个函数一个过程过程和函数如上一个作业中再写一个块测试所写的函数和过ondeletecascadeondeletesetid=100更好的数据的完整createorreplacetriggers_student_tafterinsert onstudentdbms_output.put_line('triggercallsuccess');insertintostudentvalues(1,'aa',21); 不管成功不成功都会触发after必须是成功执行之后触发delete0createorreplacetriggerafterupdate ons_empforeachrowdbms_output.put_line('triggercallsuccess');createorreplacetriggers_emp_tafterupdate ons_empdbms_output.put_line('triggercallsuccess');dmlforeachrowPRO*netIP 为了使用c/c++去数据库要开发proc/c++的程序proc把.pc.cproc编译gccfirst.c- #include<stdio.h>intmain(){ EXECSQLINCLUDEEXECSQLEXECSQLselectlast_nameinto:last_namefroms_empwhereid=1;EXECSQLCOMMITWORKRELEASE;}判断一下数据库是否成sqlcaoracle sql0是成功-1 sqlsqlca.sqlerrd[2]sql#include<stdio.h>int EXECSQLINCLUDEEXECSQLCONNECT:namepasswd;{printf("connect{printf("connect}EXECSQLselectlast_nameinto:last_namefroms_empwhereid=1;EXECSQLCOMMITWORKRELEASE;}c/c++cc++的变量sql语句中使用的变量叫sqlcc+sqlccsqlc++中宿主变量使用也是要加:定义必须在申明区EXEC BEGINDECLARESECTION;EXEC DECLARE#include<stdio.h>intmain(){EXECSQLBEGINDECLARE EXECSQL DECLARESECTION;EXECSQLINCLUDESQLCA;EXECSQLCONNECT:namepasswd;{printf("connect{printf("connect}EXECSQLselectlast_nameinto:last_namefroms_empwhereid=1;EXECSQLCOMMITWORKRELEASE;}proc指令输出的文件名默认总是.c 预处理时prociname=section.pconame=section.cpp g++section.cpplclntshproc中选项iname输入的文件名 oname输出文件名 默认xx.pc 默认是c风格 c++需要用cppparse默认是c的FULL none和partia 默认是oracle c++推荐用ansi 用用户名/去检测过程是否合指示变 组函#include<stdio.h>intmain(){execsqlincludeexecsqlbegindeclarecharuserpasswd[30]="openlab/open123";charlast_name[30]; execsqlenddeclaresection;execsqlconnectexecsqlselectlast_nameinto:last_name:desc_varfroms_empwhereid=1;execsqlcommitwork}除字符串外只支持到一维数组最大到32767#include<stdio.h>intexecsqlincludeexecsqlbegindeclarecharuserpasswd[30]="openlab/open123";charlast_name[25][30];execsqlenddeclaresection;execsqlconnectexecsqlselectlast_nameinto:last_namefroms_emp;inti;}execsqlcommitwork} 如果想反映每个名字的赋值情况怎么办？一个指示变量肯定搞不定需要n个#include<stdio.h>intmain(){execsqlincludeexecsqlbegindeclarecharuserpasswd[30]="openlab/open123";charlast_name[25][30];shortexecsqlenddeclareexecsqlconnectexecsqlselectfirst_nameinto:last_name:descfroms_emp;inti;}execsqlcommitwork}sqlcaexecsqlincludeoracaexec oracasqlorastxtf#include<stdio.h>intexecsqlincludesqlca; includeoraca;execoracleoption(oraca=yes);execsqlbegindeclarecharuserpasswd[30]="openlab/open123";charlast_name[25][30]; execsqlenddeclaresection;execsqlconnectexecsqlselectfirst_nameinto:last_name:descfroms_emp;inti;}execsqlcommitwork}plsql建立一个函数一个过程以备调createorreplace proceduregetempcount(emp_coutnumber)isselectcount(*)intoemp_cfroms_emp;createorreplace functiongetempcountfunreturnnumberisemp_cnumber;selectcount(*)intoemp_cfroms_emp;returnemp_c;plsqlexecsqlbegin在这里调用注意函数的调用 proc要加 gcccallplsql.c-lclntsh#include<stdio.h>intexecsqlincludeexecsqlbegindeclarecharuserpasswd[30]="openlab/open123"; intexecsqlenddeclaresection;execsqlconnect:userpasswd;execsqlexecuteprintf("pro:%d\nfun:%d",emp_pro,emp_fun);execsqlcommitworkrelease;}oracle charuserp[30]="openlab/open123";charuserp2[30]="scott/tiger";execsqlconnect:userp;execsqlsqlstr;execsqlexecsqlsqlstr;#include<stdio.h>intmain(){execsqlincludeexecsqlbegindeclarecharuserpasswd[30]="openlab/open123";charuserpasswd2[30]="scott/tiger";charcharexecsqlenddeclareexecsqlconnect:userpasswdexecsqlat:db1selectfirst_namefroms_empwhereid=1;execsqlconnect:userpasswdat:db2;execsqlat:db2selectfirst_namefroms_empwhereid=1;printf("pro:%d\nfun:%d",emp_pro,emp_fun);execsqlat:db1commitworkrelease;execsqlat:db2commitwork}如何数据 cdtnsnames.oraCAH_6=(DESCRIPTION=(ADDRESS_LIST(ADDRESS=(PROTOCOL=TCP)(HOST=)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=)) databaselinkCONNECTTOopenlabIDENTIFIEDBYopen123Using'CAH_6';createdatabase CONNECTTOopenlabIDENTIFIEDBYopen123Using'(DESCRIPTION=(ADDRESS_LIST(ADDRESS=(PROTOCOL=TCP)(HOST=6)(PORT=)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=)注 #include<stdio.h>intmain(){execsqlincludeexecsqlbegindeclarecharuserpasswd[30]="openlab/open123";charlast_name[30];execsqlenddeclaresection;execsqlconnectexecsql selectfirst_nameinto:last_namefroms_emp@mylink3whereid=2;execsql commitworkrelease;}1.net到一个服务器这个服务器上必须装有oracle如果登录一个数据库的情况下想多个就需要link 上面的是先登录一个数据库连接多个数据#include<stdio.h>intmain(){execsqlincludeexecsqlbegindeclarecharuserpasswd[30]="openlab/open123";charuserpasswd2[30]="scott/tiger";char execsqlenddeclareexecsqlconnect:userpasswdat:db26using'CAH_6';execsqlconnect:userpasswd2at:db20using'CAH_0';execsqlat:db26selectfirst_nameinto:last_namefroms_empwhereid=2;execsqlat:db20selectfirst_nameinto:last_namefroms_empwhereid=2;execsqlat:db26commit