oracle
阿文PIONEER
傲慢
展开
-
oracle notes(uc)
Datafile: [.ora/.dbf]Physical exist. Physical storage unit. Stored inside tablespace.A datafile cannot delete, once added into a tablespace. (delete the tablespace who belongs to)Relation:One data...原创 2019-04-11 17:20:48 · 306 阅读 · 0 评论 -
【PL/SQL】 INSERT IN PROCEDURE Analysis & Example | oracle数据库: 用procedure插入数据的案例分析
/* procedure practice */--display original table select * from regions;--test sequence before declare the proceduredrop sequence s_regions;create sequence s_regions start with 12 increme...原创 2019-06-20 04:28:50 · 209 阅读 · 0 评论 -
【PL/SQL】 UPDATE IN PROCEDURE Analysis & Example | oracle数据库: 用procedure修改数据的案例分析
/* procedure practice */--display original table select * from regions;--ceshi procedurecreate or replace procedure add_ceshi( p_id regions.region_id%type, p_name regions.region_name%type...原创 2019-06-20 05:23:09 · 158 阅读 · 0 评论 -
【PL/SQL】 ROLLBACK WHEN EXECUTE A PROCEDURE Analysis & Output | oracle数据库: 回滚案例的输出与分析
/* procedure practice */--display original table select * from regions;--ceshi procedurecreate or replace procedure add_ceshi( p_id regions.region_id%type, p_name regions.region_name%type...原创 2019-06-20 05:38:07 · 121 阅读 · 0 评论 -
PL/SQL】DELETE IN PROCEDURE Analysis & Example | oracle数据库: 用procedure删除数据的案例分析
/* procedure practice */--display original table select * from regions;drop sequence s_regions;--ceshi procedurecreate or replace procedure add_ceshi( p_id regions.region_id%type, p_name...原创 2019-06-20 15:07:44 · 155 阅读 · 0 评论 -
【PL/SQL】ME IN PROCEDURE Analysis & Example | oracle数据库: 用procedure合并数据的案例分析GE
set pagesize 120;/* procedure practice */--display original table create table tempid ASselect employee_id, last_name, salary from employeeswhere employee_id > 100 and employee_id < 110;...原创 2019-06-20 17:01:59 · 136 阅读 · 0 评论 -
【PL/SQL】CURSOR
set pagesize 120set serveroutput ondeclarec_id employees.employee_id%type;c_ln employees.last_name%type;c_s employees.salary%type;cursor cc isselect employee_id, last_name, salaryfrom employee...原创 2019-06-20 17:36:46 · 239 阅读 · 0 评论 -
【PL/SQL】 Oracle procedure: Hello world
set serveroutput on/* practice 9 *//* 1 */create or replace procedure greet ISbegin dbms_output.put_line('hello world'); dbms_output.put_line('today is : ' || to_char(sysdate, 'dd-MON-y...原创 2019-06-20 22:12:42 · 171 阅读 · 0 评论 -
【PL/SQL】Composite Data Type II
Composite Data Types-can hold multiple data-have logical connection-record:-Must contain one or more component( field/ column )-similar to structure-attribute &ROWTYPE:-declare a variable a...原创 2019-06-05 00:00:38 · 103 阅读 · 0 评论 -
【PL/SQL】ORACLE学习心路历程
原创 2019-06-09 11:58:26 · 111 阅读 · 0 评论 -
【PL/SQL】Cursor
Explicit cursor functions:-Can perform row-by-row processing beyond the first row returned by a query-Keep track of the row that is currently being processed-Enable the programmer to manually contr...原创 2019-05-31 18:27:33 · 99 阅读 · 0 评论 -
【PL/SQL】PL/SQL和SQL的区别
PL/SQL:-Procedural Language extension to SQL-Oracle Corporation’s standard data access language for relational databases-Seamlessly integrates procedural constructs with SQL-Block structure to exc...原创 2019-05-30 16:29:00 · 385 阅读 · 0 评论 -
【PL/SQL】类型和变量 | Data Type & Variables
Block type:Anonymous:declare??Subprograms are complementary to anonymous blocks.Procedure:procedure name isFunction: function name return datatype isvariable:Temporary storage of dataManipulati...原创 2019-05-31 08:04:46 · 266 阅读 · 0 评论 -
【PL/SQL】常见print out格式
DisplayDBMS_OUTPUT.PUT_LINE()Set serveroutput onprocedure allows you to write data to flat file or to direct your PL/SQL output to a screen.PRINTset autoprint onBind VariableHeadingset heading...原创 2019-05-31 08:07:55 · 776 阅读 · 0 评论 -
【PL/SQL】expression & common function
Lexical units:-Are building blocks of any PL/SQL block-Are sequences of characters including letters, numerals, tabs, spaces, returns, and symbols-Can be classified as:Identifiers: v_fname, c_perc...原创 2019-05-31 14:46:42 · 90 阅读 · 0 评论 -
【PL/SQL】Insert/ update/ delete/ merge
Insert:BEGININSERT INTO employees(employee_id, first_name, last_name, email, hire_date, job_id, salary)VALUES(employees_seq.NEXTVAL, ‘Ruth’, ‘Cores’, ‘RCORES’,CURRENT_DATE, ‘AD_ASST’, 4000);END; ...原创 2019-05-31 15:35:54 · 177 阅读 · 0 评论 -
【PL/SQL】Control structure
IF:IF condition THEN statements;[ELSIF condition THEN statements;][ELSE statements;]END IF;Case expressionappraisal := CASE v_gradeappraisal := CASECASE v_mngidFor:Do not reference the coun...原创 2019-05-31 17:05:37 · 152 阅读 · 0 评论 -
【PL/SQL】Control structure example
Loop example1)SELECT NVL(ROUND(salary/1000), 0) INTO v_salFROM emp WHERE employee_id = v_empno;FOR i IN 1…v_salLOOPv_asterisk := v_asterisk ||’*’;END LOOP;UPDATE emp SET stars = v_asteriskW...原创 2019-05-31 17:06:28 · 84 阅读 · 0 评论 -
【PL/SQL】Composite Data Type
Scalar data type only holds one valueComposite Data TypesCan hold multiple values (unlike scalar types)Are of two types:– PL/SQL recordsused to treat related but dissimilar data as a logica...原创 2019-05-31 18:13:26 · 185 阅读 · 0 评论 -
Conditionals/ loops/ case selectors(case expressions) UC
Conditionals/ loops/ case selectors(case expressions)IF condition THENStatement;ELSEIF condition THENStatement;ELSEStatement;END IF;IF statement only reflect to true(false || null)case select...原创 2019-06-05 00:02:30 · 106 阅读 · 0 评论