Oracle--sql Grammar Summary

--Define the statement block declare v_name varchar2: = ' Jack '--Define variable begin select V_name from Dual;exception when others then Dbms_o  Utput.put_line (' with exception '); end;--if judgment declare V_NUM1 number;  V_NUM2 number;  V_result VARCHAR2 (ten); begin if V_NUM1 is null or v_num2 are null then v_result: = ' Undefined ';  elsif v_num1 > v_num2 then v_result: = ' num1 is big ';  else V_result: = ' num2 is big ';  End if;end--Case statement declare grade char: = ' A ';      Remark VARCHAR2, begin case Grade "A" then Remark = "is excellent";  When the ' B ' then remark = ' is good ';  End case;end;--for loop declare total integer: = 0;begin for i in 1..19 loop total: = all + 1;    End loop;end;--Loop loop declare v_count integer: = 1;begin loop V_count: = V_count + 1;    If V_count >= exit;  End If;  End loop;end;--While loop loop declare v_num1: = 10;  While V_num1 >1 loop v_num1: = V_num1 + 1;  End Loop;end; --Dynamic SQL--Execute IMMEDIATE statement execute immediate dynamic_sql [into {Define_variable [, Define_variable2] ... |  Recode}] [using [in | out | of] bind_argument [, [in | out |] bind_argument2] ...]      [{returning | return}] into bind_argument [, Bind_argument2] ...];  Dynamic_sql: Represents an SQL statement or a PL/SQL statement block string expression define_variable: Represents a variable value that stores the selected column Recode<span style= "Font-family:arial, Helvetica, Sans-serif; " : </span><span style= "font-family:arial, Helvetica, Sans-serif;" > represents a user-defined or%rowtype-type record stored in the selected row%</span><span style= "font-family:arial, Helvetica, Sans-serif;" ></span> bind_argument: The input bind_argument parameter is an expression whose value is passed to the dynamic SQL statement, output bind_argument parameter, and a variable that stores the return value of the dynamic SQL statement in To: When making a single-row query, the specified value is assigned to the variable or record of the column, and for each value retrieved by the query, the INTO clause must have a variable or field compatible with its type returing: only for DML operations, returning into a variable or record that specifies the value to be retrieved, and each return value by a DML statement must have a corresponding type-compatible variable or field in the returing into clause using: A using clause is used to bind a parameter in a dynamic SQL statement, specifying in means only input, out represents Output, in-out represents the parameter input and output, by default in for DML, an out parameter is placed in the returning into clause, and if it is used with a using clause and a return into sentence, the using clause can only contain inThe parameter execute immediate statement can only use a data query that processes a single row, and cannot handle multiple rows of data queries-executing the DDL begin execute immediate ' CREATE TABLE temp_table (ID  Integer, name varchar2 (20)) ';  End DECLARE plsql VARCHAR2 (n); begin Plsql: = ' Declare systime varchar2 (20); "Begin select To_char (sysdate," Dd-mm-yyyy Day ") into the systime from dual; Dbms_output.put_line (' The current date is: ' | |  systime) end; ';  Execute immediate plsql;end;  --binding variables, performing dynamic Sqldeclare plsql varchar2 (200);  T_name varchar2 (): = ' Jock ';  t_id integer: = ' 1002 '; begin plsql: = ' INSERT INTO temp_table values (: 1,:2) '; Execute immediate plsql using t_name, t_id;end;--PL/SQL exception handling declare exception_name//definition exception raise Exception_name//Trigger Exception E  Xception//Handling exception when exception_name and then statements;  declare TEMP_EX exception;  T_num Integer;begin SELECT count (ID) into the t_num from temp_table where id = ' 1031 ';  If T_num >= 1 then raise TEMP_EX;  End If; Dbms_output.  Put_Line (' The user does not exist '); Exception when TEMP_EX and then Dbms_output. Put_Line (' The user has savedIn '); end; --Declaring cursor cursor_name [{Parameter[,parameter] ...}] [Return return_type] is Selectsqlopen cursor_name//Open cursor fetch cursor_name into variable_list;  Extract cursor close cursor_name//close cursor--normal cursor value declare fname VARCHAR2 (20);  LName VARCHAR2 (20);  Cursor C_student is a select Firstname,lastname from student where id = ' 1001 '; begin open c_student;  If C_student%notfound then Dbms_output.put_line (' No records found ');    else fetch c_student into firstname,lastname; Dbms_output.put_line (fname| | "| |  lname);  End If;  Close c_student;end;  --Loop/while/for Cycle value declare fname VARCHAR2 (20);  LName VARCHAR2 (20); Cursor T_student is select Firstname,lastname from student where ID < 1001;begin for stus in T_student loop fname:    = T_student.firstname;    LName: = T_student.lastname; Dbms_output.put_line (' Name: ' | | fname| | ' | |  lname); End Loop; end;--stored procedure create proc | Procedure Pro_name [{@ parameter data type} [= default] [output], {@ parameter data type} [= default] [Output], ....] As Sql_statements--CreateNo parameter stored procedure Create or Replace procedure Showinfo as SELECT * FROM student begin Showinfo (' Jock ');  --Execute the stored procedure end; --Creating a stored procedure with parameters Create or replace procedure Showinfo (Major in varchar2) AS//Declare an input parameter select * FROM student where Major = M Ajor;begin showinfo (' Jock '); --Execute stored procedure end;drop showinfo--Delete stored procedure--function syntax create [or replace] function name [(parameter 1 [{in|out|in out} type parameter [{in|out|in out} Type ...]) return type {is | as}]function _body;--define function Create or Replace function GetCount (Major in VARCHAR2) return number as F_cou  NT number; Declares the return type begin SELECT COUNT (*) into F_count from students where major = ' Magor ' return f_count;  Returns the return statement end;--using the function declare v_count number;begin v_count: = GetCount (' Music '); Dbms_output.put_line (v_count); End;drop function GetCount--delete functions--create Baotou Create or replace package emp_package as--declares a storage    Process procedure My_proc (Lend_nun varchar2;    Lend_name varchar2;    Ledn_sex varchar2;  Major VARCHAR2; The end emp_package;--creates the package body create or replace PAckage body Emp_package AS--the implementation of the stored procedure procedure my_proc (Lend_num varchar2;    Lend_name varchar2;    Lend_sex varchar2;  Major VARCHAR2;  ) is a BEGIN INSERT into EMP (lnum,lname,lsex,major) values (lend_num,lend_name,lend_sex,major); End My_proc;end emp_package;--Call package Package_name.type_name;begin emp_package.my_proc (' 1001 ', ' Jock ', ' Male ', ' music ');  end;--definition View Create or replace view v_student as SELECT * from Student;select * from V_student; Query view Drop view v_student; Delete View--sequence create sequence seq_name[increment by N][start with N][maxvalue n | nomaxvalue]//nomaxvalue: Specify a maximum value of 1027 for ascending order, descending Up to -1[minvalue n | mominvalue]//nominvalue: Specifies a minimum value of 1 for ascending order and a descending minimum of -1026--modify sequence alter sequence seq_name[increment by n][ MaxValue N | Nomaxvalue] [minvalue n | mominvalue]--delete sequence drop sequence seq_name;create sequence seq_id MinValue 1 MaxValue star T with 1 increment by 1 cache 20;--database chain create [public] datebase link link_name connect to username identified by PASSW Ord using ' Servername/seRverurl '; select * FROM [email protected]_name;create database link Link_goods Connect to Scott identified Scott Usi Ng ' (description = (Address_list = (address = (protocol = TCP) (host = 10.0.0.34) (port = 1521))) (Connect_data = (service_na  me = ORCL)) '; SELECT * FROM [email protected]_goods;--index create [unique] index [schema.]  Index_name on table_name (col_name) [tablespace ts] [storage s] [Pctfree PF] [nosort NS] Schema: Indicates Oracle mode, default current account Tablespace: Index storage tablespace storage: Storage parameter Pctfree: Percentage of index block free space nosort: not sorted (stored in ascending order, no need to sort) Create unique index i_id on student (ID);--Modify the index alter [unique] index index_name [initrans n] [Maxtrans n] rebuild [Storage<storage>] Initrans: a block within the same The number of entries that are accessed at the initial transaction, n is the decimal integer maxtrans: The maximum number of transaction entries that are accessed concurrently within a block, and n is a decimal integer rebuild: Re-indexed based on the original index structure, that is, the index data is created after a full table scan of the table storage: Store data,   Same as the CREATE INDEX, ALTER index I_ID rebuild storage (initial 1M next 512k)--Delete index drop indexes schema.index_name;

Oracle--sql Grammar Summary