Oracle Practice--pl/sql-based code block

A code block for the basic introduction to PL/SQL

PL/sql: Process language (Procedure Language) and structured language (structured Query Language) A combined programming language is an extension to SQL that supports a variety of data types such as large objects and collection types, using control statements such as conditions and loops, creating stored procedures, packages, triggers, and so on, to SQL Statement execution adds program logic, which is tightly integrated with Oracle Server and Oracle Tools for portability, flexibility, and security.

Advantages:

1. support SQL, can use:DML,DCL, cursor control and SQL operator

2. support for object -oriented (OOP).

3. tightly integrated with SQL , simplifies data processing, supports all data types, supports NULL, supports %type and %rowtype

4. You can limit user access to data through stored procedures to improve security.

-- Declaration of variables: commonly used: varchar2 , Number , %rowtype,%type

Select *from emp; DECLARE  v_name varchar2: = ' & name: ';--varchar2 type declaration  v_ename emp.ename%type;--is consistent with the type of the amount ename field in the EMP table  V_emp_rec Emp%rowtype;--consistent with the type of data in the EMP line  v_age number;--number type begin  V_age: = ' & Age: ';  Select Ename to V_ename from EMP where empno =7369;  SELECT * into V_emp_rec from EMP where empno =7369;  Dbms_output.put_line (v_ename);  Dbms_output.put_line (v_emp_rec.job); end;

-- Condition Control (IF) Key Words : If then elsif else End if;

DECLARE v_age number: = ' & Please enter Age: ';-note: When declaring a variable type, ' & Please enter age ' end cannot add English ': ' Begin if V_age <18 then   dbms_ Output.put_line (v_age| | ' Old, teen! '); --' | | ' is a string connector, equivalent to ' + ' in Java elsif v_age <35 then--Note is elsif, not ElseIf    dbms_output.put_line (v_age| | ' years old, Prime! '); else    dbms_output.put_line (v_age| | ' Years old, past the prime! ');  End if;--Notice End If not forget, what matter finish oh end;

-- Condition Control (case) , Keywords : case  When and else end case;

DECLARE V_grade VARCHAR2 (2); begin V_grade: = ' & Please enter rank: '; Case V_grade if   ' A ' then Dbms_output.put_line (' excellent ');--Grammar: when ... then ...;   When the ' B ' then dbms_output.put_line (' good '); -When ... then ...;   When the ' C ' then Dbms_output.put_line (' General ');--when ... then ...;   else dbms_output.put_line (' poor ');--else equivalent to the default end case in Java; --note the end, end Caseend;

-- Loop Control Loop , Keywords: Loop   exit when end Loop

declare v_times number; V_currenttime Number: =1;begin v_times: = ' & input printing helloworld: '; Loop   dbms_output.put_line (' helloworld! ');   V_currenttime: = V_currenttime +1;--here in Java, + = Cannot use   exit when V_currenttime > v_times; end loop;end;

-- Loop Control while , Keywords : while   Loop End Loop

DECLARE v_count number: =1;begin while V_count <=7    loop      dbms_output.put_line (v_count);      V_count: = V_count +1;    End Loop; End

-- Loop Control for , Keywords: for In reverse loop end Loop

Declarebegin for  num in 1..5    loop      dbms_output.put (num);--If you do not want to change the line, use put, but the console will not display the output      end loop;     Dbms_output.new_line ()---the output of a previously non-wrapped line is displayed after a newline for  num in reverse 1..5    loop      dbms_output.put (num);      End Loop;    Dbms_output. New_line ();--written in uppercase, Oracle appears to be a case-insensitive end;

-- for Cycle Practice Multiplication Table

Declarebegin for I in 1..9   loop--each for loop must be matched with a loop end loop     for  J in 1..i       loop         dbms_ Output.put (i| | ' * ' | | j| | ' = ' | | i*j| | '  );       End Loop;       dbms_output.new_line;--Note, brackets can not, but the specification is not written so    end loop; end;

-- sequential control, Keywords: goto NULL

DECLARE v_age number: = ' & input age: '; begin if V_age <18 then   Goto young;--Jump to <<young>>, and execute elsif v_ sequentially Age <30   and goto strong;--jumps to <<strong>>, in order, the Else   goto older;--jumps to <<older>> Executes the end if in order; <<young>>     null; <<strong>>     dbms_output.put_line (' strong '); <<older> >     dbms_output.put_line (' older '); end;

-- Dynamic Execution SQL statement, keyword: Execute Immediate

DECLARE v_sql varchar2 (222); V_emp_rec emp%rowtype;begin V_sql: = ' select * from emp where empno =: num ';-' =: ' As a placeholder, the identifier cannot be used as a keyword, such as number execute immediate V_sql into V_emp_rec using 7369;--to the placeholder binding value dbms_output.put_line (v_emp_rec.ename); end;

-- exceptions, pre-defined exceptions, keywords exception when then

DECLARE v_emp Emp%rowtype; V_sql VARCHAR2 (200); V_ename emp.ename%type;begin V_sql: = ' select * from EMP '; Execute immediate v_sql to  v_emp;--v_sql: = ' select ename from emp where empno = 7396 ';--execute immediate V_sql I Nto v_ename; Exception when   Too_many_rows then-too_many_rows is the exception type, there is also a data     dbms_output.put_line (' Too many lines! ');   --when Data_not_found then     --dbms_output.out_line (' not found! '); End

-- exceptions, custom exceptions, keywords: Exception Raise

DECLARE  v_ageexception exception;--Exception type exception  v_age number: = ' & Age: '; begin if V_age >120   Then Raise v_ageexception;--satisfies the condition, raise throws an exception else   dbms_output.put_line (v_age); End If; exception when   v_ Ageexception     then Dbms_output.put_line (' too old! '); End

Article Source: http://blog.csdn.net/ysjian_pingcx/article/details/25644831