???SQL?????????

???????????? ???????[ 2013/4/17 9:57:26 ] ????????

???????SQL?????????к??????????????綯?SQL????β????????÷??(;)???????PL/SQL??β???????÷??(;)?????????????б???β(/)?????shcema???????????????????????????????SQL?????????

?????????????SQL?????

?????????????У??????????SQL????scott.emp??????tb2???????????????SQL???±??л??????????????????????????????????PL/SQL????????????????????????????SQL???±???и????

DECLARE                               --?????????????????趨????
         sql_stmt         VARCHAR2(100);
         plsql_block      VARCHAR2(300);
         v_deptno         NUMBER := 30;
         v_count          NUMBER;
         v_new_sal        VARCHAR2(5);
         v_empno          NUMBER := 7900;
       BEGIN
         sql_stmt := 'CREATE TABLE tb_emp ' ||        --????????????????SQL???
                    'AS SELECT * FROM scott.emp WHERE deptno = ' || v_deptno;
         EXECUTE IMMEDIATE sql_stmt;                  --??ж??SQL???

         EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM tb_emp'??--??????EXECUTE IMMEDIATE??????SQL??????±??????
           INTO v_count;
         DBMS_OUTPUT.PUT_LINE('The employee count is : ' || v_count);

         plsql_block := 'DECLARE ' ||             --???????PL/SQL?飬????????plsql_block??
                      ' v_date DATE; ' ||
                      'BEGIN ' ||
                      ' SELECT SYSDATE INTO v_date FROM DUAL; ' ||
                      ' DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_date??''DD-MON-YYYY''));' ||
                      'END;';
         EXECUTE IMMEDIATE plsql_block;           --??ж????PL/SQL??

         sql_stmt := 'UPDATE tb_emp SET sal = sal + 100 WHERE empno =:eno ' || --?????±????????
                    'RETURNING sal INTO :sal';                         --???SQL????а???RETURNING???????o????
         EXECUTE IMMEDIATE sql_stmt               --??ж??SQL??
           USING v_empno
           RETURNING INTO v_new_sal;              --???RETURNING??佫???????????v_new_sal??
         DBMS_OUTPUT.PUT_LINE('New salary is: ' || v_new_sal);
       END;

       The employee count is : 6
       04-JAN-2011
       New salary is: 1050

???????????SQL?????????

????1???????DDL?????????e?????

?????????????У?????????????????????:dno??????е?????????????????

DECLARE
         sql_stmt         VARCHAR2(100);
         v_deptno         VARCHAR2(5) := '30';
         v_count          NUMBER;
       BEGIN
         sql_stmt := 'CREATE TABLE tb_tmp ' || 'AS SELECT * FROM scott.emp ' ||
                    'WHERE deptno = :dno';
         EXECUTE IMMEDIATE sql_stmt
           USING v_deptno;

         EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM tb_tmp'
           INTO v_count;
         DBMS_OUTPUT.PUT_LINE('The temp table count is ' || v_count);
       END;

       DECLARE
       *
       ERROR at line 1:
       ORA-01027: bind variables not allowed for data definition operations
       ORA-06512: at line 8