PL/SQL程序设计_基本语法

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;
/