PL/SQL in Oracle Data

What is Pl/SQL:

• -- Declare/* Statement of PL/SQL: declare the variables, types, and cursors used by PL/SQL, as well as local stored procedures and functions */str varchar2 (20 ); I number; begin/* execution part: process and SQL statement, that is, the main part of the Program */str: = 'Hello Oracle '; I: = 'a '; dbms_output.put_line (str); exception/* execution exception section: Error Handling */when others then dbms_output.put_line ('error'); end;

  
  

• Declare str varchar2 (10): = 'hello';/* value */str2 varchar2 (10); I constant number: = 100;/* defines constants, however, the value */begin str2: = 'oracle 'must be assigned to the statement./* assign the value */dbms_output.put_line (str | str2) to the begin statement ); /* \ is the Oracle connector */dbms_output.put_line (I); exception when others then dbms_output.put_line ('error error'); end;

  
  
• • Declare v_stu_id number; v_stu_name varchar2 (10); v_stu_age number; begin select stu_id, stu_name, stu_age into v_stu_id, v_stu_name, v_stu_age/* The variable */from tab_stu is assigned to the query result; end;

    
    

  • declare v_stu_id number; v_stu_name varchar2(10); v_stu_age number;begin select stu_id,stu_name,stu_age   into v_stu_id,v_stu_name,v_stu_age from tab_stu where stu_id=1; dbms_output.put_line('stu_id:'||v_stu_id); dbms_output.put_line('stu_name:'||v_stu_name); dbms_output.put_line('stu_age:'||v_stu_age); end;

    Stu_id: 1stu_name: Zhang San stu_age: 21

    
    

  • -- Insert declare begin insert into tab_stu (stu_id, stu_name, stu_age) values (4, 'zhao liu', '22'); commit;/* Remember to submit */end;

    
    

  • • -- % Type: Use Limit limit % type; v_stu_name tab_stu.stu_name % type; v_stu_age limit % type; begin select stu_id, stu_name, stu_age into v_stu_id, v_stu_name, v_stu_age from tab_stu where stu_id = 1; values ('stu _ id: '| v_stu_id); dbms_output.put_line ('stu _ name:' | v_stu_name); dbms_output.put_line ('stu _ age: '| v_stu_age ); end;

      
      

    • -- % Rowtype use declarev_tab_stu_row tab_stu % rowtype; begin select stu_id, stu_name, stu_age into partition, v_tab_stu_row.stu_name, partition from tab_stu where stu_id = 2; partition ('stu _ id: '| v_tab_stu_row.stu_id); dbms_output.put_line ('stu _ name:' | v_tab_stu_row.stu_name); dbms_output.put_line ('stu _ age: '| begin); end;

      Second usage

      -- % Rowtype use declare v_tab_stu_row tab_stu % rowtype; begin select * into v_tab_stu_row from tab_stu where stu_id = 3; struct ('stu _ id: '| v_tab_stu_row.stu_id ); dbms_output.put_line ('stu _ name: '| v_tab_stu_row.stu_name); dbms_output.put_line ('stu _ age:' | v_tab_stu_row.stu_age); end;

      
      

• -- Varraydeclare type strings is varray (6) of varchar (10); -- this is actually like defining an object var_array strings: = strings ('zhang san ', 'Sun Ba ', 'Sun 7', 'zhao 6', 'wang 5', 'Li si'); -- here is the specific implementation of this object, and define begin inline (var_array (1); dbms_output.put_line (var_array (2); dbms_output.put_line (var_array (3); dbms_output.put_line (var_array (4 )); dbms_output.put_line (var_array (5); dbms_output.put_line (var_array (6); end;

  Output

  Zhang sansun, 8 sun, 7 Zhao liuwang, 5 Li Si

  
  

• -- Variable data type tabledeclare type tableStrings is table of varchar2 (10) index by binary_integer; -- defines the subscript of the table type as the infinitely increasing var_table tableStrings of the binary type; -- For the defined type, define a variable begin var_table (1): = 'hello'; -- assign var_table (999): = 'word' to the variable index '; -- assign dbms_output.put_line (var_table (1) | ''| var_table (999); end to the index of the variable;

  Output: Hello Word

• -- Variable data type table use 2 declare type table_row is table of tab_stu % rowtype index by binary_integer; var_tab_stu table_row; begin select stu_id, stu_name into var_tab_stu (100 ). stu_id, var_tab_stu (100 ). stu_name from tab_stu where stu_id = 2; dbms_output.put_line (var_tab_stu (100 ). stu_id | ''| var_tab_stu (100 ). stu_name); end;

  Output: 2 Li Si

• -- Record is interpreted as the set declare type v_record is record (re_id number, -- it can be a common variable re_name tab_stu.stu_name % type, -- it can be the r_tab_stu tab_stu % rowtype variable of a field in the table -- or it can be a row in the table); v_r v_record; -- it must be assigned to the begin select stu_id, stu_name, stu_age into v_r.re_id, v_r.re_name, direction from tab_stu where stu_id = 1; dbms_output.put_line (v_r.re_id); values (v_r.re_name); dbms_output.put_line (Limit); end;