DECLARE
V_DATE DATE := SYSDATE;
v_var VARCHAR2(20);
V_VALID BOOLEAN;
BEGIN
IF V_VAR IS NULL THEN
DBMS_OUTPUT.PUT_LINE('V_VAR IS NULL');
END IF;
INSERT INTO T_TEST VALUES (1,'zizhu');
COMMIT;
V_VAR := 'HELLO,WORLD';
V_VALID := 'hello,world' LIKE V_VAR;--LIKE 比较字符串是否相等
DBMS_OUTPUT.PUT_LINE(V_VAR);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_DATE,'YYYY-MM-DD HH:MI:SS'));
DBMS_OUTPUT.PUT_LINE('连接' || '两个' || '字符串');
END;
/
--使用dml语句
DECLARE
V_ID BINARY_INTEGER := 23;
V_NAME VARCHAR2(30) := 'zizhu8';
BEGIN
DELETE FROM T_TEST;
INSERT INTO T_TEST VALUES (V_ID,V_NAME);
COMMIT;
END;
/
--使用select语句
DECLARE
V_COUNT NUMBER(4);
V_ID BINARY_INTEGER;
V_NAME VARCHAR2(30);
BEGIN
SELECT COUNT(*)
INTO V_COUNT
FROM T_TEST;
DBMS_OUTPUT.PUT_LINE('T_TEST表中的数据共有:' || V_COUNT || '条');
SELECT ID,NAME
INTO V_ID,V_NAME
FROM T_TEST;
DBMS_OUTPUT.PUT_LINE(V_ID || '对应' || V_NAME);
END;
/
--sql语句的基本使用
DECLARE
V_COUNT BINARY_INTEGER;--专门计数使用的类型,效率比较高
V_ID NUMBER(4);
V_NAME VARCHAR2(30);
BEGIN
SELECT COUNT(*)
INTO V_COUNT
FROM T_TEST;
IF V_COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('T_TEST表中没有数据');
DBMS_OUTPUT.PUT_LINE('向T_TEST表中插入一条数据后....');
INSERT INTO T_TEST SELECT * FROM T_TEST2;
END IF;
SELECT COUNT(*)
INTO V_COUNT
FROM T_TEST;
DBMS_OUTPUT.PUT_LINE('T_TEST表中共有' || V_COUNT || '条数据');
IF V_COUNT = 1 THEN
SELECT ID,NAME
INTO V_ID,V_NAME
FROM T_TEST;
DBMS_OUTPUT.PUT_LINE('T_TEST表中的数据有:' || V_ID || '--' || V_NAME);
END IF;
V_ID := 1;
V_NAME := 'zizhu8';
DBMS_OUTPUT.PUT_LINE('修改这条数据' || V_ID || '--' || V_NAME);
UPDATE T_TEST SET ID = V_ID,NAME = V_NAME WHERE ID = (SELECT ID FROM T_TEST WHERE ROWNUM <= 1);
SELECT ID,NAME
INTO V_ID,V_NAME
FROM T_TEST;
DBMS_OUTPUT.PUT_LINE('修改T_TEST表中的数据后:' || V_ID || '--' || V_NAME);
DELETE FROM T_TEST WHERE ID = (SELECT ID FROM T_TEST WHERE ROWNUM <= 1);
DBMS_OUTPUT.PUT_LINE('删除第一条记录后....');
SELECT COUNT(*)
INTO V_COUNT
FROM T_TEST;
DBMS_OUTPUT.PUT_LINE('T_TEST表中共有' || V_COUNT || '条数据');
COMMIT;
END;
/