这是很早以前写在javaeye的,现在搬过来。
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