DESC STUDENTTABLE SET SERVEROUTPUT ON; declare v_str1 VARCHAR2(10); v_str2 VARCHAR2(50); begin v_str1:='hello'; select STUDENT_NAME into v_str2 from STUDENTTABLE where STUDENT_NO=1; dbms_output.put_line(v_str1 ||', '|| v_str2); EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('NO DATA FOUND'); end; ------------------- --------------------------------------------------------VIEW----------------------------------------------- --------------------------------------------------------VIEW----------------------------------------------- --------------------------------------------------------VIEW----------------------------------------------- --------------------------------------------------------VIEW----------------------------------------------- ---------CREATE A VIEW CREATE OR REPLACE VIEW TEST_VIEW AS SELECT * FROM STUDENTTABLE WHERE ROWNUM<10; --QUERY DATA FROM TEST_VIEW SELECT * FROM TEST_VIEW; -----DROP VIEW DROP VIEW TEST_VIEW; --------------------------------------------------------SYNONYM----------------------------------------------- --------------------------------------------------------SYNONYM----------------------------------------------- --------------------------------------------------------SYNONYM----------------------------------------------- --------------------------------------------------------SYNONYM----------------------------------------------- CREATE SYNONYM SYN_PHRASES FOR CITIAP_USER; --HERE I LOGIN AS JFPAP USER TO CREATE A SYNONYM FOR CITIAP USER, TABLE WAS CREATED BY CITIAP USER. GRANT ALL ON CITIAP.PHRASES TO JFPAPUSER;-- HERE I LOGIN AS CITIAP USER TO GRANT TABLE PHRASES TO JFPAP USER --------------------------------------------------------SEQUENCE----------------------------------------------- --------------------------------------------------------SEQUENCE----------------------------------------------- --------------------------------------------------------SEQUENCE----------------------------------------------- --------------------------------------------------------SEQUENCE----------------------------------------------- --CREATE A SEQUENCE CREATE SEQUENCE MY_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 10; CREATE SEQUENCE MY_SEQ; --DROP SEQUENCE DROP SEQUENCE MY_SEQ; --QUERY SEQUECE VALUE SELECT MY_SEQ.CURRVAL FROM DUAL;--CURRENT VALUE SELECT MY_SEQ.NEXTVAL FROM DUAL;--NEXT VALUE --------------------------------------------------------PL/SQL BLOCK----------------------------------------------- --------------------------------------------------------PL/SQL BLOCK----------------------------------------------- --------------------------------------------------------PL/SQL BLOCK----------------------------------------------- --------------------------------------------------------PL/SQL BLOCK----------------------------------------------- DECLARE V_STUDENT_NAME VARCHAR2(50); BEGIN select STUDENT_NAME into V_STUDENT_NAME from STUDENTTABLE where STUDENT_NO=&NO; dbms_output.put_line(V_STUDENT_NAME); END; --------------------------------------------------------PROCEDURE----------------------------------------------- --------------------------------------------------------PROCEDURE----------------------------------------------- --------------------------------------------------------PROCEDURE----------------------------------------------- --------------------------------------------------------PROCEDURE----------------------------------------------- CREATE OR REPLACE PROCEDURE UPDATE_NAME(NEWSTUNAME VARCHAR2,STUNO VARCHAR2) IS V_NAME VARCHAR2(10); BEGIN UPDATE STUDENTTABLE SET STUDENT_NAME=NEWSTUNAME WHERE student_no=STUNO; COMMIT; SELECT STUDENT_NAME INTO V_NAME FROM studenttable WHERE student_no=STUNO; dbms_output.put_line('NAME WAS UPDATED, NEW NAME IS '|| V_NAME); END; CALL UPDATE_NAME('??','1'); --------------------------------------------------------FUNCTION----------------------------------------------- --------------------------------------------------------FUNCTION----------------------------------------------- --------------------------------------------------------FUNCTION----------------------------------------------- --------------------------------------------------------FUNCTION----------------------------------------------- CREATE OR REPLACE FUNCTION FINDSTUNAME(STU_NO VARCHAR2)RETURN VARCHAR2 is STU_NAME STUDENTTABLE.STUDENT_NAME%TYPE; BEGIN SELECT STUDENT_NAME INTO STU_NAME FROM STUDENTTABLE WHERE student_no=STU_NO; RETURN STU_NAME; END; --------------------------------------------------------PACKAGE----------------------------------------------- --------------------------------------------------------PACKAGE----------------------------------------------- --------------------------------------------------------PACKAGE----------------------------------------------- --------------------------------------------------------PACKAGE----------------------------------------------- CREATE OR REPLACE PACKAGE STU_PKG AS PROCEDURE PROCEDURE UPDATE_NAME (NEWSTUNAME VARCHAR2 , STUNO VARCHAR2) ; FUNCTION FINDSTUNAME ( STU_NO VARCHAR2 ) RETURN VARCHAR2 ; END ; --PACKAG BODY CREATE PACKAGE BODY STU_PKG_BODY IS PROCEDURE UPDATE_NAME(NEWSTUNAME VARCHAR2,STUNO VARCHAR2) IS V_NAME VARCHAR2(10); BEGIN UPDATE STUDENTTABLE SET STUDENT_NAME=NEWSTUNAME WHERE student_no=STUNO; COMMIT; SELECT STUDENT_NAME INTO V_NAME FROM studenttable WHERE student_no=STUNO; dbms_output.put_line('NAME WAS UPDATED, NEW NAME IS '|| V_NAME); END; FUNCTION FINDSTUNAME(STU_NO VARCHAR2)RETURN VARCHAR2 is STU_NAME VARCHAR2(10) ; BEGIN SELECT STUDENT_NAME INTO STU_NAME FROM STUDENTTABLE WHERE student_no=STU_NO; RETURN STU_NAME; END; END; --------------------------------------------------------TRIGGER----------------------------------------------- --------------------------------------------------------TRIGGER----------------------------------------------- --------------------------------------------------------TRIGGER----------------------------------------------- --------------------------------------------------------TRIGGER----------------------------------------------- CREATE OR REPLACE TRIGGER UPDATE_STU_TRIGGER BEFORE INSERT ON STUDENTTABLE FOR EACH ROW BEGIN SELECT MY_SEQ.NEXTVAL INTO :NEW.STUDENT_NO FROM DUAL; END; insert into STUDENTTABLE(student_name) values('hello'); commit; select * from STUDENTTABLE; --------------------------------------------------------IF----------------------------------------------- --------------------------------------------------------IF----------------------------------------------- --------------------------------------------------------IF----------------------------------------------- --------------------------------------------------------IF----------------------------------------------- SELECT * FROM STUDENTTABLE; CREATE OR REPLACE FUNCTION FINDFLAG(STU_NO IN VARCHAR2) RETURN VARCHAR2 IS v_flag VARCHAR2(10); v_name STUDENTTABLE.STUDENT_NAME%TYPE; BEGIN SELECT STUDENT_NAME INTO v_name FROM STUDENTTABLE WHERE student_no=STU_NO; IF '??'=v_name THEN v_flag:=v_name||' 2'; RETURN v_flag; ELSIF '??'=v_name THEN v_flag:=v_name||' 3'; RETURN v_flag; ELSE v_flag:='NOT FOUND!!!'; RETURN v_flag; END IF; END; --------------------------------------------------------CASE WHEN----------------------------------------------- --------------------------------------------------------CASE WHEN----------------------------------------------- --------------------------------------------------------CASE WHEN----------------------------------------------- --------------------------------------------------------CASE WHEN----------------------------------------------- CREATE OR REPLACE FUNCTION FINDFLAG2(STU_NO IN VARCHAR2) RETURN VARCHAR2 IS v_flag VARCHAR2(10); v_name STUDENTTABLE.STUDENT_NAME%TYPE; BEGIN SELECT STUDENT_NAME INTO v_name FROM STUDENTTABLE WHERE student_no=STU_NO; CASE WHEN '??'=v_name THEN v_flag:=v_name||' 2'; RETURN v_flag; WHEN '??'=v_name THEN v_flag:=v_name||' 3'; RETURN v_flag; ELSE v_flag:='NOT FOUND!!!'; RETURN v_flag; END CASE; END; --------------------------------------------------------LOOP----------------------------------------------- --------------------------------------------------------LOOP----------------------------------------------- --------------------------------------------------------LOOP----------------------------------------------- --------------------------------------------------------LOOP----------------------------------------------- SELECT * FROM STUDENTTABLE; DECLARE i INT:=1; BEGIN LOOP INSERT INTO STUDENTTABLE(STUDENT_NAME) VALUES('STUDENT '|| i); EXIT WHEN i=5; i:=i+1; END LOOP; COMMIT; END; --------------------------------------------------------WHILE----------------------------------------------- --------------------------------------------------------WHILE----------------------------------------------- --------------------------------------------------------WHILE----------------------------------------------- --------------------------------------------------------WHILE----------------------------------------------- DECLARE i INT:=6; BEGIN WHILE i<10 LOOP INSERT INTO STUDENTTABLE(STUDENT_NAME) VALUES('STUDENT '|| i); i:=i+1; END LOOP; END; --------------------------------------------------------FOR----------------------------------------------- --------------------------------------------------------FOR----------------------------------------------- --------------------------------------------------------FOR----------------------------------------------- --------------------------------------------------------FOR----------------------------------------------- BEGIN FOR i IN 10..15 LOOP INSERT INTO STUDENTTABLE(STUDENT_NAME) VALUES('STUDENT '|| i); END LOOP; END; --------------------------------------------------------EXCEPTION----------------------------------------------- --------------------------------------------------------EXCEPTION----------------------------------------------- --------------------------------------------------------EXCEPTION----------------------------------------------- --------------------------------------------------------EXCEPTION----------------------------------------------- DECLARE E_INVALID_INPUT EXCEPTION; V_STU_NAME studenttable.STUDENT_NAME%TYPE; V_STU_NO VARCHAR2(10):=&STU_NO; BEGIN IF V_STU_NO='2' THEN RAISE E_INVALID_INPUT; END IF; SELECT STUDENT_NAME INTO V_STU_NAME FROM studenttable WHERE student_no=V_STU_NO; dbms_output.put_line('HELLO, '||V_STU_NAME); EXCEPTION WHEN E_INVALID_INPUT THEN dbms_output.put_line('INPUT ERROR'); END; --------------------------------------------------------IMPLICIT CURSOR----------------------------------------------- --------------------------------------------------------IMPLICIT CURSOR----------------------------------------------- --------------------------------------------------------IMPLICIT CURSOR----------------------------------------------- --------------------------------------------------------IMPLICIT CURSOR----------------------------------------------- ---SQL%FOUND && SQL%NOTFOUND && SQL%ROWCOUND DECLARE V_STU_NAME studenttable.STUDENT_NAME%TYPE; V_ROW_STU studenttable%ROWTYPE; BEGIN UPDATE studenttable SET STUDENT_NAME='CITI' WHERE student_no=&STU_NO RETURNING STUDENT_NAME INTO V_STU_NAME; --USING RETURNING IF SQL%FOUND THEN dbms_output.put_line('DATA UPDATED, UPDATED ROW COUNT '|| SQL%ROWCOUNT|| ' , NEW NAME IS '|| V_STU_NAME); END IF; IF SQL%NOTFOUND THEN dbms_output.put_line('DATA NOT FOUND...'); END IF; SELECT * INTO V_ROW_STU FROM studenttable WHERE student_NAME=V_STU_NAME; dbms_output.put_line('V_ROW_STU ====> '|| V_ROW_STU.student_no||', '|| V_ROW_STU.STUDENT_NAME); END; SELECT * FROM STUDENTTABLE; --USING IMPLICIT CURSOR ,ORACLE WILL EXECUTE OPEN, FETCH,CLOSE . --BELOW 2 PROCEDURE A ONE EXAMPLE --BELOW IS TO REMOVE ONE RECORD FROM TABLE CREATE OR REPLACE PROCEDURE PRO_REMOVE_STU(V_STU_NO IN studenttable.student_no%TYPE) IS BEGIN DELETE FROM studenttable WHERE STUDENT_NO=V_STU_NO; END; --BELOW IS TRY TO QUERY RECORD COUNT, BUT GET FAIL CREATE OR REPLACE PROCEDURE PRO_QUERY_COUNT1 IS V_COUNT INTEGER; BEGIN SELECT COUNT(*) INTO V_COUNT FROM studenttable; --10 RECORDS IN TABLE PRO_REMOVE_STU('9999'); --CALL PRO_REMOVE_STU(), BUT 9999 WAS NOT IN DB. dbms_output.put_line(SQL%ROWCOUNT ||' RECORDS WAS FOUND IN TABLE'); --WILL PRINT : 0 RECORDS WAS FOUND IN TABLE .BECAUSE SQL%ROWCOUNT WILL ONLY RECORD THE LATEST DML EXECUTION. DUE TO 9999 IS NOT IN DB,SO THE DELETE RETURN 0. END; -- TO FIX PRO_QUERY_COUNT1 ISSUE CREATE OR REPLACE PROCEDURE PRO_QUERY_COUNT2 IS V_COUNT INTEGER; V_FOUND_NUM INTEGER; V_DELETE_COUNT INTEGER; BEGIN SELECT COUNT(*) INTO V_COUNT FROM studenttable; --1 RECORDS IN TABLE V_FOUND_NUM:= SQL%ROWCOUNT; PRO_REMOVE_STU('9999'); --CALL PRO_REMOVE_STU(), BUT 9999 WAS NOT IN DB. V_DELETE_COUNT:=SQL%ROWCOUNT; dbms_output.put_line('TOTAL RECORD NUMBER OF TABLE: '||V_FOUND_NUM || ', REMOVE COUNT NUMBER: '||V_DELETE_COUNT); END; --------------------------------------------------------EXPLICIT CURSOR----------------------------------------------- --------------------------------------------------------EXPLICIT CURSOR----------------------------------------------- --------------------------------------------------------EXPLICIT CURSOR----------------------------------------------- --------------------------------------------------------EXPLICIT CURSOR----------------------------------------------- DECLARE CURSOR CUR_STU IS SELECT STUDENT_NO,STUDENT_NAME FROM studenttable; V_STU_NO studenttable.STUDENT_NO%TYPE; V_STU_NAME studenttable.STUDENT_NAME%TYPE; BEGIN OPEN CUR_STU; LOOP FETCH CUR_STU INTO V_STU_NO,V_STU_NAME; dbms_output.put_line('STU_NO: '|| V_STU_NO||', STU_NAME: '|| V_STU_NAME); EXIT WHEN CUR_STU%NOTFOUND ; END LOOP; CLOSE CUR_STU; END; ----use FOR LOOP TO FETCH CURSOR DATA DECLARE CURSOR CUR_STU IS SELECT STUDENT_NO,STUDENT_NAME FROM studenttable; BEGIN FOR CUR_STU_TEMP IN CUR_STU LOOP dbms_output.put_line('STU_NO: '|| CUR_STU_TEMP.STUDENT_NO ||', STU_NAME: '|| CUR_STU_TEMP.STUDENT_NAME); END LOOP; END; --------------------------------------------------------TRANSACTION----------------------------------------------- --------------------------------------------------------TRANSACTION----------------------------------------------- --------------------------------------------------------TRANSACTION----------------------------------------------- --------------------------------------------------------TRANSACTION----------------------------------------------- --COMMIT --ROLLBACK --SAVEPOINT --LOCK TABLE
相关推荐
Oracle中SQL以及PL/SQL复习专用 SQL复习专用
很全很系统的学习javaEE的好东西,快速复习javaEE。觉得好请帮顶一下
PL/SQL的基本语法、记录类型、流程控制、游标的使用、 异常处理机制、存储函数/存储过程、触发器。 为方便大家跟着我的笔记练习,为此提供数据库表文件给大家下载:点我下载 为了要有输出的结果,在写PL/SQL程序前都...
oracle PL/sql语言复习资料,很全的oracle函数,sql语句,以及游标,存储过程,触发器等复习资料.
pl/sql块习题,里面是关于数据库oracle的pl/sql块相关方面的习题,可以用来复习,pl/sql是数据库里面的重点内容
自考本科 一、单项选择题(本大题共20小题,每小题1分,共20分) 在每小题列出的四个备选项中只有一个是符合题目要求的,请将其代码填写在题后的括号内。错选、多选或未选均无分。 1.C++中注释“∥”的...的 复习资料
总共包括十五章的复习题: 第一章 Oracle 11g 介绍 第二章 ORACLE 11g 的体系结构 ...第十章 PL/SQL基础 第十一章 存储过程与函数 第十二章 触发器 第十三章 游标 第十四章 安全管理 第十五章 数据库备份与恢复
数据库知识点整理,包括数据库和表的创建,增删改查,子查询,关联查询,连接,交并差集,索引,序列,视图,...存储过程,触发器,PL/SQL的基本使用。 每个知识点都有基于ORACLE scott 账户下employee 表的实例代码。
PL/SQL以及DEVELOPER,而这将是一门新的课程。如果你是一名DBA, 请继续。 5、可以根据自己的实际情况,进行有选择的学习,也就是说下面的内 没有特别顺序要求。可以选择深入学习ORACLE的管理、备份与恢复、性能 ...
18 Oracle:PL/SQL 6分 1 XML:xml作用,格式良好的xml 6分 2 XML:dom 7分 3 XML:外部dtd编写 6分 4 XML:dtd和schema的区别 7分 5 XML:db-》xml 6分 6 XML:schema-》xml 8分 7 XML:dtd中的细节 4分 8 XML:内部dtd编写...
50 pl/sql 51 Oracle数据库对象 52 提高数据库性能 53 hibernate总结 54 使用Spring容器管理JavaBean 55 Spring 数据访问支持 56-59 项目实战:JBOA办公自动化管理系统 60 Spring配置优化 61 web service 62-65 项目...
存储过程:由PL/SQL语句书写的过程,这个过程经编译和优化后存储在数据库服务器中,使用时只要调用即可。 动态参数与主变量的区别:动态参数的输入不是编译时完成绑定。而是通过 (prepare)语句准备主变量和执行...
呕心沥血两个礼拜,记录oracle学习点点滴滴 代码经过自己验证 是学习和复习不可多得的好东西 倾情奉献
10.2.5 将子查询因子化应用到PL/SQL中 270 10.3 递归子查询 273 10.3.1 一个CONNECT BY的例子 274 10.3.2 使用RSF的例子 275 10.3.3 RSF的限制条件 276 10.3.4 与CONNECT BY的不同点 276 10.4 复制CONNECT BY...
最近开始学oracle,也不算开始学,以前也学过,只能叫复习了,在csdn上找了半天找到了这个东西,个人觉得还不错,循序渐进,讲知识很详细还有例子给你联系,只可惜是PPT的,PPT嘛,拿来演示还可以,给人看就太不方便了,于是个人...
)完成后,您将拥有三个装载数据的表: 县:2010 年人口普查 PL94 数据。 meat_poultry_inspect :美国农业部关于生产肉类和家禽的植物的数据。 acs_2012_stats :来自 2012 年美国社区调查的衍生数据。基础数学在...