Skip to content

Instantly share code, notes, and snippets.

@amartinezg

amartinezg/gist:c870777ae3c8cfdf20cce4b0cb04a26b

Created October 11, 2016 02:38
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save amartinezg/c870777ae3c8cfdf20cce4b0cb04a26b to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save amartinezg/c870777ae3c8cfdf20cce4b0cb04a26b to your computer and use it in GitHub Desktop.
Download ZIP
Clase 2 procedimientos, PL/SQL, funciones, cursores implícitos y explícitos.
Raw
gistfile1.txt
/*
Take an integer n (n >= 0) and a digit d (0 <= d <= 9) as an integer.
Square all numbers k (0 <= k <= n) between 0 and n.
Count the numbers of digits d used in the writing of all the k**2
n = 10, d = 1, the k*k are 0, 1, 4, 9, 16, 25, 36, 49, 64, 81, 100
We are using the digit 1 in 1, 16, 81, 100. The total count is then 4.
n = 25, d=1 there are 11 digits `1` for the squares of numbers between 0 and 25.
*/
DECLARE
/*
* Declaración de variables y cursores
*/
SUBTYPE my_type_of_variable IS NUMBER(38,0);
message varchar2(20):= 'Hello, World!';
-- Defecto 'DD-MON-YY'
date_pl date:='10-OCT-16';
counter my_type_of_variable:=23423;
BEGIN
-- Cuerpo del PLSQL
dbms_output.put_line(message);
dbms_output.put_line('Hola: ' || date_pl);
dbms_output.put_line('counter: ' || counter);
END;
DECLARE
-- Global variables
var1 number := 1;
start_time number := 0;
BEGIN
start_time := DBMS_UTILITY.get_time;
dbms_output.put_line('Global var1: ' || var1);
DECLARE
-- Local variables
var1 number := 15;
BEGIN
dbms_output.put_line('Local var1: ' || var1);
END;
dbms_output.put_line('Time elapsed: ' || (DBMS_UTILITY.get_time - start_time) || ' milliseconds');
END;
declare
i number;
val1 date;
val2 date;
val3 number;
begin
SELECT SYSDATE, LAST_DAY (SYSDATE) "Last", LAST_DAY (SYSDATE) - SYSDATE "Days left"
INTO val1, val2, val3
FROM DUAL;
i := sql%rowcount;
dbms_output.put_line('Total records: ' || i || '. current day: ' || val1 || '. Last: ' || val2 || '. Days left: ' || val3);
end;
select mod(price, 1), price from products;
DECLARE
i number(1);
j number(1);
BEGIN
<< external_loop >>
FOR i IN 1..4 LOOP
<< internal_loop >>
FOR j IN 1..3 LOOP
dbms_output.put_line('i is: '|| i || ' and j is: ' || j);
END loop internal_loop;
END loop external_loop;
END;
DECLARE
i number;
j number := 12346333333;
BEGIN
i := regexp_count('SSSRNNSRSSR', 'R');
dbms_output.put_line('Ocurrences: '|| i);
i := regexp_count(TO_CHAR(j), '3');
dbms_output.put_line('Ocurrences: '|| i);
END;
CREATE OR REPLACE FUNCTION totalProducts
RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM products;
RETURN total;
END;
DECLARE
BEGIN
dbms_output.put_line('Total number of products: ' || totalProducts());
END;
CREATE OR REPLACE FUNCTION findMin(x IN number, y IN number)
RETURN number IS
z number := 0;
BEGIN
IF X > Y then
z := Y;
ELSE
z := X;
END IF;
RETURN z;
END;
DECLARE
BEGIN
dbms_output.put_line('Min between 15 - 20: ' || findMin(20, 15));
END;
DECLARE
rows number(2);
product_id number := 3;
BEGIN
UPDATE products SET price = price + 20 WHERE category_id = product_id;
IF sql%notfound THEN
dbms_output.put_line('no products were updated');
ELSIF sql%found THEN
rows := sql%rowcount;
dbms_output.put_line( rows || ' products updated ');
END IF;
END;
DECLARE
c_id products.id%type;
c_name products.name%type;
c_price products.price%type;
CURSOR get_products_cursor IS
Select id, name, price FROM PRODUCTS;
BEGIN
OPEN get_products_cursor;
LOOP
FETCH get_products_cursor into c_id, c_name, c_price;
EXIT WHEN get_products_cursor%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_price);
END LOOP;
CLOSE get_products_cursor;
END;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment